3
0
Unload table to the staging area: Recently a requirement came up where a truncate process was define which is use to truncate the table inside the snowflake on hourly basis. After the Truncate , the source system ingest the new data into the table . This lead to the loss of older records as there was no track of the data which was present in the table before the Truncate load. After the discussion with in the team, it was finalize before the truncate we will export or unload table to the staging area. Also we need to ensure that no files would be overwrite as part of unload process. At the downstream side , team would be consuming all the files at the end of day and perform the processing or operations at their end.
The whole process would be schedule by the TASK and would be execute only in business hours i.e. 8:00 AM EST – 8:00 PM EST.
Also Note, Though the data was present in relational form in table but downstream teams wants the data to be unloaded in the form of JSON format i.e. Key-value pair. So at the time of unloading we need to convert it into JSON and we have used OBJECT_CONSTRUCT(*) function.
OBJECT_CONSTRUCT(*):
OBJECT_CONSTRUCT(*): you can turn any row or result set into a JSON Document with OBJECT_CONSTRUCT(*).When invoked with an asterisk, the object is constructed using the attribute names as keys and the associated tuple values as values.
CREATE or REPLACE FILE FORMAT JSON
TYPE = ‘JSON’
COMPRESSION = ‘AUTO’
ENABLE_OCTAL = FALSE
ALLOW_DUPLICATE = TRUE
STRIP_OUTER_ARRAY = FALSE
STRIP_NULL_VALUES = FALSE
IGNORE_UTF8_ERRORS = FALSE;
CREATE OR REPLACE STAGE UNLOAD_INVOICE
file_format=JSON
Now call the procedure which will upload the data from Invoice table to the staging area.
call SP_INVOICE_UNLOAD();
Now truncate the table again and insert new data, Will run the Procedure to verify if Files are appended to the staging area or overwriting the previous one.
Now schedule the procedure to run every hour by TASK.
CREATE TASK mytask_hour
WAREHOUSE = COMPUTE_WH
SCHEDULE = ‘USING CRON 0 8-20 * * 0-5 America/Los_Angeles’
TIMESTAMP_INPUT_FORMAT = ‘YYYY-MM-DD HH24’
AS
call SP_INVOICE_UNLOAD();
