Shipping Snowflake Logs and Audit Data to Coralogix with OpenTelemetry
This tutorial demonstrates how to centralize logging for Snowflake by sending logs to Coralogix via OpenTelemetry.
Overview
Snowflake is a cloud-based data warehousing platform known for efficiently storing, processing, and analyzing vast amounts of data. It provides scalable data warehousing services that enable organizations to manage and retrieve data seamlessly in a cloud environment.
Snowflake does not directly integrate with Coralogix for exporting logs and audit-related data. However, you can follow this detailed guide to leverage the OpenTelemetry Collector. Using the OpenTelemetry Collector, you can extract Snowflake logs and audit information and transmit them to Coralogix. Coralogix processes this data for streamlined visualization and analysis within its platform.
Prerequisites
OpenTelemetry Collector installed and configured
Active Snowflake account with appropriate access (e.g., ACCOUNTADMIN)
Configuring the OpenTelemetry Collector
The OpenTelemetry Collector includes observability components that support Snowflake, such as the Snowflake Receiver for monitoring key metrics and the SQL Query Receiver for executing custom queries via a built-in Snowflake database driver. These tools provide insights into Snowflake's performance and resource utilization.
Snowflake’s built-in capabilities track and log user activities. Logs and audit data can be obtained using queries, integrated with the OpenTelemetry Collector’s SQL Query Receiver to enhance observability and auditing.
STEP 1: Obtaining your Snowflake account identifier
The account identifier uniquely identifies your Snowflake account within your organization. It is required for the connection string and is used to declare cloud platforms and regions.
To obtain your account identifier:
Go to your Snowflake console.
In the bottom left corner, copy the account URL.
Extract the account identifier from the account URL (e.g., xy12345.us-east-2.aws.snowflakecomputing.com
), following this format:
Refer to the Snowflake Account Identifiers Guide for more details.
STEP 2: Creating a connection string
The connection string is used to establish a connection to your Snowflake database. Format it as follows:
<username>
: Your Snowflake username.<password>
: Your Snowflake password.<account_identifier>
: Your Snowflake account identifier.
STEP 3: Writing the SQL query receiver configuration
Create the configuration for the SQL Query Receiver in your OpenTelemetry Collector configuration file (otel-collector-config.yaml
):
receivers:
sqlquery:
driver: snowflake
datasource: "<username>:<password>@<account_identifier>/SNOWFLAKE/ACCOUNT_USAGE"
collection_interval: 60s
queries:
- sql: |
SELECT
OBJECT_CONSTRUCT(
'application', 'snowflake',
'environment', 'debug',
'log_type', 'login_history',
'EVENT_TIMESTAMP', EVENT_TIMESTAMP,
'EVENT_TYPE', EVENT_TYPE,
'USER_NAME', USER_NAME,
'CLIENT_IP', CLIENT_IP,
'REPORTED_CLIENT_TYPE', REPORTED_CLIENT_TYPE,
'FIRST_AUTHENTICATION_FACTOR', FIRST_AUTHENTICATION_FACTOR,
'IS_SUCCESS', IS_SUCCESS,
'ERROR_CODE', ERROR_CODE,
'ERROR_MESSAGE', ERROR_MESSAGE
) log,
EVENT_ID
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
tracking_start_value: "0"
tracking_column: EVENT_ID
logs:
- body_column: LOG
service:
pipelines:
logs:
receivers:
- sqlquery
Configuration breakdown
The sqlquery
receiver configuration executes custom SQL queries against a specified database and collects the results. Key configurations include:
driver
: Specifies the database driver (snowflake
).datasource
: Connection string including username, password, and account identifier.collection_interval
: Frequency of query execution, change it to your preference (e.g., every 60 seconds).queries
: List of SQL queries to execute, defining:sql
: The query constructing a JSON object from LOGIN_HISTORY columns.tracking_start_value
andtracking_column
: Settings for tracking query progress usingEVENT_ID
.
logs
: Defines how query results are processed as logs, utilizingLOG
as the body column.
Example SQL query:
The OBJECT_CONSTRUCT
function creates a JSON object from selected LOGIN_HISTORY columns, enhancing log structure for OpenTelemetry Collector processing.
SELECT OBJECT_CONSTRUCT('application', 'snowflake', 'environment', 'debug', 'log_type', 'login_history', 'EVENT_TIMESTAMP', EVENT_TIMESTAMP, 'EVENT_TYPE', EVENT_TYPE, 'USER_NAME', USER_NAME, 'CLIENT_IP', CLIENT_IP, 'REPORTED_CLIENT_TYPE', REPORTED_CLIENT_TYPE, 'FIRST_AUTHENTICATION_FACTOR', FIRST_AUTHENTICATION_FACTOR, 'IS_SUCCESS', IS_SUCCESS, 'ERROR_CODE', ERROR_CODE, 'ERROR_MESSAGE', ERROR_MESSAGE) log, EVENT_ID FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
STEP 4: Deployment and validation
Add
sqlquery
receiver to OpenTelemetry Collector Config: Integratesqlquery
receiver into deployment configurations (e.g., Kubernetes, ECS).Deploy OpenTelemetry Collector: Apply configuration changes and deploy OpenTelemetry Collector.
Validate Configuration: Ensure correct log collection, confirming single
sqlquery
receiver instance to prevent duplicate records.
STEP 5: [Optional] Expanding SQL configurations
Repeat SQL configurations (sqlquery.queries
) for all Snowflake tables storing logs and audit data.
Validation
Navigate to Explore > Logs in Coralogix to view your centralized Snowflake logs.
Support
Need help?
Our customer success team is available 24/7 via in-app chat or email at [email protected].