How to Log Messages and Errors in Snowflake

Logging plays a crucial role in troubleshooting unexpected issues and keeping track of important events. When there are no logging records and a program crashes, the chances of identifying the cause of the problem become significantly low.

Python provides a built-in module called logging, which enables the ability to write status messages to various outputs, such as files or other streams. The log() method of the logger class in Java is utilized to record and log a message. Can you log and store messages from functions or procedures in Snowflake Data Cloud? Indeed, it is possible.

In this blog, we’ll talk about event tables, how they differ from regular tables, and how to utilize them for logging and accessing messages.

What are Event Tables?

Event tables are designed to log and capture events, such as messages or activity records, generated from functions and procedure handler code. An account can have only one active event table. We can perform only a subset of table operations on an event table.

The following operations can be performed on event tables:

SHOW EVENT TABLES
DESCRIBE EVENT TABLE
DROP TABLE
UNDROP TABLE
TRUNCATE TABLE
DELETE
ALTER TABLE

Snowflake Functions to Log Messages

Python provides the logging module to log messages to files or streams. Similarly, Snowflake also offers system functions to log messages from functions or procedures written in Snowflake Scripting. By leveraging these functions, we can store the logged messages in an event table and later access them for in-depth analysis. Here is the list of functions that helps with logging messages.

				
					SYSTEM$LOG('<level>,' <message>); --to log messages at each of the supported levels.

SYSTEM$LOG_TRACE(<message>); -- log TRACE messages
SYSTEM$LOG_DEBUG(<message>); --log DEBUG messages
SYSTEM$LOG_INFO(<message>); -- log INFO messages.
SYSTEM$LOG_WARN(<message>); -- log WARNING messages.
SYSTEM$LOG_ERROR(<message>); --log ERROR messages.
SYSTEM$LOG_FATAL(<message>); -- log FATAL messages

SYSTEM$LOG('info', <message>); -- log INFO messages.

Let's Get Started

Below is the procedure for logging and capturing messages from Snowflake Scripting stored procedures with Event tables:

Setting up an Event Table to capture the logged messages.
Associate the Event Table with your Snowflake Account.
Create a SQL Stored Procedure and set the desired log level.
Log the messages from your procedures using the specified log event level.
Access the logged message data for further analysis and review.

Setting up an Event Table

To start logging or tracking in Snowflake, it is necessary to set up an event table to gather the message and event data produced by the handler code. The table has predefined columns for capturing log entries and trace events from function and procedure handlers. Event Table has the below-predefined columns

Column	Description
TIMESTAMP	timestamp when the event record was added
START_TIMESTAMP	event period starting timestamp for metrics and spans
OBSERVED_TIMESTAMP	used when capturing logs that do not have an accompanying timestamp
TRACE	tracing context
RESOURCE	for future use
RESOURCE_ATTRIBUTES	attributes that identify the source of an event
SCOPE	scope for signals
SCOPE_ATTRIBUTES	for future use
RECORD_TYPE	type of the value in the RECORD field
RECORD	fixed fields for each signal type
RECORD_ATTRIBUTES	variable attributes for each signal type
VALUE	primary event value
EXEMPLARS	exemplars for metrics

Create an Event Table

To create an event table for storing log and trace event data, use the CREATE EVENT TABLE command and provide a name for the table. This table will capture logs generated by stored procedures, UDFs, and UDTFs within your account.

Associate the Event Table with Snowflake Account

To capture log entries and trace events, associate the event table with the Snowflake account. Only one event table can be associated with an account at a time, and the table is known as the active event table. To specify the active event table for your account, use the ALTER ACCOUNT command and set the EVENT_TABLE parameter to the desired name of your event table.

It is necessary to provide the fully-qualified name of the event table, as shown above.

You can verify the value of the EVENT_TABLE parameter by using the SHOW PARAMETERS command.

Note:
To execute this command, it is necessary to have the ACCOUNT ADMIN role assigned to your user. Furthermore, you must have the following privileges:

OWNERSHIP privilege for the account.
OWNERSHIP or INSERT privileges for the event table.

Create a Snowflake Scripting Stored Procedure

Create a stored procedure and use Snowflake system functions to emit log messages. Now set the log level on the stored procedure to ingest messages at this level into the event table. By setting the LOG_LEVEL parameter to a specific level, only messages at that level or more severe levels are captured and stored in the event table.

For instance, if the LOG_LEVEL parameter is set to WARN, messages at the WARN, ERROR, and FATAL levels will be included in the event table. If the LOG_LEVEL parameter is set to INFO, messages at the INFO, WARN, ERROR, and FATAL levels will be included in the event table. For more information about log levels, please refer to LOG_LEVEL.

				
					CREATE OR REPLACE PROCEDURE EVENT_PROC(message VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
select * from SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER limit 10;
  SYSTEM$LOG_INFO('Processed ' || :message);
  SYSTEM$LOG_ERROR('Failed ' || :message);
  SYSTEM$LOG_WARN('Warning  ' || :message);
END;



call EVENT_PROC('test message');

Query the Event Table to Analyze the Collected Logs

The logged messages are stored in an event table that we have configured for logging purposes. To access the logged messages, you can execute a SELECT command on the event table.

Conclusion

Snowflake system log functions with event tables are utilized when we need to capture, store, and analyze specific events or logs for various purposes such as monitoring, auditing, performance analysis, debugging, etc. By creating an event table and associating it with the account, messages are logged through log functions recorded within the event tables, then you can query the event table for analysis.

If you’re looking to log messages and errors in Snowflake but have questions on where to start, don’t hesitate to reach out!