JS Proc with Event Tables

Read Time:2 Minute, 13 Second

During the last post we discussed about the Event tables and how these are used for Error logging in your process. The scenario we discussed was related to the Python User Defined Tabular function(UDTF). The UDTF was responsible of parsing JSON data in a tabular format and identify any erroneous records and log into an Event table. Now in extension to it we will see how we can utilize the Event tables with Javascript procedure. We have seen JS procedures are extensively use in any project and it would be a great help if we can leverage EVENT tables with Stored procedure.

We are going to discuss an interesting stored procedure scenario w.r.t Event tables. Say we have stage table or Metadata table that stores table name, Snowflake stage name and corresponding File Format details. As part of the requirement we have to develop a COPY process which will take input from stage table. Process will generate the COPY command dynamically and load the data into respective table. During the execution process it can be the possibility that command gets fail because of any error say invalid file format. Here the EVENT tables comes into the picture and capture any error occurs during the data load and log them for further analysis. Moreover we have also captures the Process start time and completion time along with the COPY statement generated by Code dynamically.

We have developed the below stored procedure using EVENT tables. “snowflake.log()” is used to log the message in Event tables.

Technical Implementation:

Technical implementation:

Stage table stores the relevant details.

Stored Procedure to implement Event tables.

Call the Procedure and Query the Event table:

call COPY_validate();

SELECT TIMESTAMP,RESOURCE_ATTRIBUTES,RECORD,VALUE FROM demo_db.public.logging_event_table order by timestamp desc;

Analyze the output inside the Event table.

As we can see one of the COPY command to load data into Parquet table gets fail. Error details along with the statement is available in Event table and we can analyze the reason of failure. Also the successful COPY commands are capture inside the table. We can also verify the process start and completion time as well.