Accessing Logged Message Data

Logged messages are stored in an event table you set up to support logging. You can access the logged messages by executing the SELECT command on the event table.

Note

Before you can begin logging messages, you must set up an event table. For more information, refer to Setting up an Event Table.

An event table has a set of predefined columns that capture information about the logged messages, including:

  • The timestamp when the message was ingested.

  • The scope of the log event, such as the name of the class where the log event was created.

  • The log event source, including the database, schema, user, warehouse, and so on.

  • The severity level of the log.

  • The log message.

For reference information about event table columns, refer to Event table columns.

Log Data Query Example

The following sections illustrate with example data how you can query the event table for log message data.

Collected Data

Output in the following example shows content from a selected subset of columns from an event table after log messages have been captured for two separate handlers, one written in Scala and the other in Python.

For reference information on event table columns that collect log message data, refer to Data for logs.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| TIMESTAMP           | SCOPE                             | RESOURCE_ATTRIBUTES   | RECORD_TYPE | RECORD                       | VALUE                                                      |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2023-04-19 22:00:49 | { "name": "python_logger" }       | **See excerpt below** | LOG         | { "severity_text": "INFO" }  | Logging from Python module.                                |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2023-04-19 22:00:49 | { "name": "python_logger" }       |                       | LOG         | { "severity_text": "INFO" }  | Logging from Python function start.                        |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2023-04-19 22:00:49 | { "name": "python_logger" }       |                       | LOG         | { "severity_text": "ERROR" } | Logging an error from Python handler.                      |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2023-04-19 22:12:55 | { "name": "ScalaLoggingHandler" } |                       | LOG         | { "severity_text": "INFO" }  | Logging from within the Scala constructor.                 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2023-04-19 22:12:56 | { "name": "ScalaLoggingHandler" } |                       | LOG         | { "severity_text": "INFO" }  | Logging from Scala method start.                           |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2023-04-19 22:12:56 | { "name": "ScalaLoggingHandler" } |                       | LOG         | { "severity_text": "ERROR" } | Logging an error from Scala handler: Something went wrong. |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

RESOURCE_ATTRIBUTES Excerpts

The following JSON excerpts contains a RESOURCE_ATTRIBUTES column attribute whose data is included in the preceding output. The SELECT query code following these excerpts selects the value from this attribute.

The RESOURCE_ATTRIBUTES column contains data about the event’s source. For reference information, refer to RESOURCE_ATTRIBUTES column.

{
  ...
  "snow.executable.name": "ADD_TWO_NUMBERS(A FLOAT, B FLOAT):FLOAT"
  ...
}

{
  ...
  "snow.executable.name": "ADD_TWO_NUMBERS(A FLOAT, B FLOAT):FLOAT"
  ...
}

{
  ...
  "snow.executable.name": "ADD_TWO_NUMBERS(A FLOAT, B FLOAT):FLOAT"
  ...
}

{
  ...
  "snow.executable.name": "DO_LOGGING():VARCHAR(16777216)"
  ...
}

{
  ...
  "snow.executable.name": "DO_LOGGING():VARCHAR(16777216)"
  ...
}

{
  ...
  "snow.executable.name": "DO_LOGGING():VARCHAR(16777216)"
  ...
}
Copy

Query with SELECT Statement

When querying for message data, you can select attribute values within a column by using bracket notation, as in the following form:

COLUMN_NAME['attribute_name']
Copy

Code in the example below queries the preceding table with the intention of isolating data related to the Python handler’s log messages. The query selects the severity_text attribute for the log entry severity. It selects the VALUE column’s content for the log message.

The procedure containing the handler is called do_logging. Note that you must specify the procedure name in all capital letters in order for the query to work.

SET event_table_name='my_db.public.my_event_table';

SELECT
  TIMESTAMP as time,
  RESOURCE_ATTRIBUTES['snow.executable.name'] as executable,
  RECORD['severity_text'] as severity,
  VALUE as message
FROM
  IDENTIFIER($event_table_name)
WHERE
  SCOPE['name'] = 'python_logger'
  AND RESOURCE_ATTRIBUTES['snow.executable.name'] LIKE '%DO_LOGGING%'
  AND RECORD_TYPE = 'LOG';
Copy

Query Results

Output in the following example illustrates the query’s result.

----------------------------------------------------------------------------------------------------------------
| TIME                | EXECUTABLE                       | SEVERITY   | MESSAGE                                |
----------------------------------------------------------------------------------------------------------------
| 2023-04-19 22:00:49 | "DO_LOGGING():VARCHAR(16777216)" | "INFO"     | "Logging from Python module."          |
----------------------------------------------------------------------------------------------------------------
| 2023-04-19 22:00:49 | "DO_LOGGING():VARCHAR(16777216)" | "INFO"     | "Logging from Python function start."  |
----------------------------------------------------------------------------------------------------------------
| 2023-04-19 22:00:49 | "DO_LOGGING():VARCHAR(16777216)" | "ERROR"    | "Logging an error from Python handler" |
----------------------------------------------------------------------------------------------------------------