2
0
During the previous post we discussed the usage of EVENT table with Python and Javascript procedure. In continuation of the same we will explore how we can leverage the EVENT tables in SQL scripting. We will use the EVENT tables to log the error and other details through SQL scripting procedure.
Consider the scenario that we have external stage defined in our account. These stages can have multiple files feed by respective source systems. Now as part of the Purge policy, Business has asked that to remove the files from the certain stages. In addition to it the business has asked if removal process can be designed in an automated way. The process should be flexible enough that it should accept the Stage name as an input argument, and procedure would remove all the files without manual intervention.
Please note, it can be the additional check that we have to remove only those files which are 45 days older or need to remove specific extension files as well. But f0r this POC we are simply purging the files from stage via SQL scripting procedure. Also we will implement the EVENT tables with SQL procedure to track the process flow.
SYSTEM$LOG is used to log into the EVENT tables in SQL Scripting procedure.
Technical implementation:
- Say we have below external stage having one file present.
- We can verify the respective files count as well in snowflake using information schema.
- Develop the below Stored procedure with Event table.
- Call the procedure;
CALL Remove_Stage_Files1('EVENT_CSV_STAGE');
- Verify the Output inside the EVENT table.
- Verify the file in stage.
