USE DATABASE DEMO; ALTER TABLE customers SET CHANGE_TRACKING = TRUE; CREATE or REPLACE TABLE customers_entities( "entity_id" VARCHAR, "customer_id" VARCHAR ); CREATE OR REPLACE TABLE TILORES_CONFIG ( name varchar(255) NOT NULL, value varchar(255) default NULL ); INSERT INTO TILORES_CONFIG VALUES ('last_sync', CURRENT_TIMESTAMP()::string), ('fetch_on_next_run', 'true'); SELECT * FROM TILORES_CONFIG; CREATE OR REPLACE TASK tilores_sync SCHEDULE = '1 MINUTE' AS DECLARE now STRING; last_sync STRING; fetch_on_next_run STRING; BEGIN now := (SELECT CURRENT_TIMESTAMP()); last_sync := (SELECT value FROM TILORES_CONFIG WHERE name='last_sync'); fetch_on_next_run := (SELECT value FROM TILORES_CONFIG WHERE name='fetch_on_next_run'); SELECT tilores_ingest(OBJECT_CONSTRUCT(*)) AS ingested FROM ( SELECT * EXCLUDE(METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID) FROM customers CHANGES(INFORMATION => DEFAULT) AT(TIMESTAMP => to_timestamp_tz(:last_sync)) WHERE METADATA$ACTION='INSERT'); IF (fetch_on_next_run = 'true') THEN MERGE INTO customers_entities TARGET USING (SELECT tilores_entity_by_record_id("id"):id AS "entity_id", "id" FROM customers) SOURCE ON TARGET."customer_id" = SOURCE."id" WHEN MATCHED THEN UPDATE SET TARGET."entity_id" = SOURCE."entity_id" WHEN NOT MATCHED THEN INSERT ("entity_id", "customer_id") VALUES (SOURCE."entity_id", SOURCE."id"); END IF; UPDATE TILORES_CONFIG SET value = :now WHERE name = 'last_sync'; UPDATE TILORES_CONFIG SET value = (SELECT CASE WHEN count(*) > 0 THEN 'true' ELSE 'false' END AS fetch_on_next_run FROM customers CHANGES(INFORMATION => DEFAULT) AT(TIMESTAMP => to_timestamp_tz(:last_sync)) WHERE METADATA$ACTION='INSERT') WHERE name = 'fetch_on_next_run'; END; ALTER TASK tilores_sync RESUME; SELECT * FROM TABLE(information_schema.task_history()) ORDER BY scheduled_time;
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter