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.
Hi Sachin,
I am unable to compile the procedure.
Could you please help me?
CREATE OR REPLACE PROCEDURE Remove_Stage_Files1(stage_name VARCHAR)
RETURNS varchar
LANGUAGE SQL
AS
$$
DECLARE
file_count INT;
st_date timestamp := current_timestamp();
counts int := 0;
ListFiles RESULTSET;
QUERY varchar;
remove_stmt varchar;
TotalCount NUMBER;
actualent INTEGER;
BEGIN
SYSTEM$LOG(‘info’,’Remove_Stage_Files1 Process started’);
SYSTEM$LOG(‘info’,’Procedure execution started at: ‘|| TO_CHAR(st_date, ‘YYYY-MM-DD HH24:mi:ss’));
SELECT count(*) INTO :actualent From INFORMATION_SCHEMA.STAGES WHERE STAGE_NAME = :stage_name;
IF (actualcnt = 0) THEN
SYSTEM$LOG(‘ERROR’, ‘Stage does’|| :stage_name || ‘not exist’);
END IF;
IF (actualent > 0) THEN
ListFiles:= (EXECUTE IMMEDIATE ‘LS @’ || stage_name );
LET C1 CURSOR FOR ListFiles;
FOR files IN C1 DO
TotalCount = TotalCount + 1;
remove_stmt := ‘RM @’|| stage_name;
EXECUTE IMMEDIATE remove_stmt;
END FOR;
end if;
SYSTEM$LOG(‘INFO’,’Total files ‘ ||:TotalCount || ‘to be deleted’);
SYSTEM$LOG(‘info’, ‘Remove_Stage_Files1 Process completed’);
let end_date timestamp := current_timestamp();
SYSTEM$LOG(‘info’, ‘Procedure execution completed at: ‘|| TO_CHAR(end_date, ‘YYYY-MM-DD HH24:mi:ss’));
end;
$$;