Skip to content

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:

  1. Go to your Snowflake console.

  2. 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:

<account_locator>.<cloud_region_id>.<cloud>.snowflakecomputing.com

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>:<password>@<account_identifier>/SNOWFLAKE/ACCOUNT_USAGE

  • <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). Remember to attach the sqlquery receiver to a log pipeline:

exporters:
  coralogix:
    application_name: 'otel'
    application_name_attributes:
    - aws.ecs.task.family
    - service.namespace
    domain: ${CORALOGIX_DOMAIN}
    logs:
      headers:
        X-Coralogix-Distribution: ecs-fargate-integration/0.0.1
    metrics:
      headers:
        X-Coralogix-Distribution: ecs-fargate-integration/0.0.1
    private_key: ${PRIVATE_KEY}
    subsystem_name: 'integration'
    subsystem_name_attributes:
    - service.name
    - aws.ecs.docker.name
    - container_name
    timeout: 30s
    traces:
      headers:
        X-Coralogix-Distribution: ecs-fargate-integration/0.0.1
receivers: 
  sqlquery:
    collection_interval: 21600s
    driver: snowflake
    datasource: "<username>:<password>@<ACCOUNT_IDENTIFIER>.snowflakecomputing.com/SNOWFLAKE/ACCOUNT_USAGE"
    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,
              'EVENT_ID', EVENT_ID
          ) log,
          EXTRACT(EPOCH FROM EVENT_TIMESTAMP) AS EPOCH_TIMESTAMP
          FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
          WHERE EXTRACT(EPOCH FROM EVENT_TIMESTAMP) > ?
          ORDER BY EPOCH_TIMESTAMP ASC
        tracking_start_value: "0"
        tracking_column: EPOCH_TIMESTAMP
        logs:
          - body_column: LOG
service:
  pipelines:
    logs:
      receivers:
        - sqlquery
      exporters:
        - coralogix

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. The tracking value is passed as a parameter using the ? placeholder.

    • tracking_start_value and tracking_column: Settings for tracking query progress using EPOCH_TIMESTAMP.

  • logs: Defines how query results are processed as logs, utilizing LOG as the body column.

Note

We recommend to create a new column called EPOCH_TIMESTAMP derived from EXTRACT(EPOCH FROM EVENT_TIMESTAMP). Using the table's EVENT_ID does not show a continuous sequence, and tracking_column only works on numberic value_type (e.g. int or double). Also with ascending order (ORDER BY EPOCH_TIMESTAMP ASC), the collector uses the last result's row tracking_column value as the parameterized value for the next query. Additionally when the collector is restarted, the tracking value will be reset to the tracking_start_value, thus re-querying all the data. You can additional conditional logic to the query to prevent this (e.g. only pull from 6hrs ago: EVENT_TIMESTAMP >= DATEADD(hour, -6, CURRENT_TIMESTAMP())).

STEP 4: Deployment and validation

  • Add sqlquery receiver to OpenTelemetry Collector Config: Integrate sqlquery receiver into deployment configurations (e.g., Kubernetes, ECS, EC2).

  • 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.

ECS task definition

{
  "containerDefinitions": [
      {
          "name": "otel-collector",
          "image": "otel/opentelemetry-collector-contrib",
          "cpu": 0,
          "portMappings": [
              {
                  "name": "otel-collector-4317-tcp",
                  "containerPort": 4317,
                  "hostPort": 4317,
                  "protocol": "tcp",
                  "appProtocol": "grpc"
              },
              {
                  "name": "otel-collector-4318-tcp",
                  "containerPort": 4318,
                  "hostPort": 4318,
                  "protocol": "tcp",
                  "appProtocol": "grpc"
              }
          ],
          "essential": true,
          "command": [
              "--config",
              "env:SSM_CONFIG"
          ],
          "environment": [
              {
                  "name": "CORALOGIX_DOMAIN",
                  "value": "us2.coralogix.com"
              }
          ],
          "mountPoints": [],
          "volumesFrom": [],
          "secrets": [
              {
                  "name": "SSM_CONFIG",
                  "valueFrom": "<ARN_TO_SECRETS_MANAGER>"
              },
              {
                  "name": "PRIVATE_KEY",
                  "valueFrom": "<ARN_TO_SECRETS_MANAGER>"
              }
          ],
          "user": "0",
          "logConfiguration": {
              "logDriver": "awslogs",
              "options": {
                  "awslogs-group": "<ENV>-coralogix-open-telemetry",
                  "awslogs-create-group": "true",
                  "awslogs-region": "<REGION>",
                  "awslogs-stream-prefix": "snowflake"
              }
          },
          "systemControls": []
      }
  ],
  "family": "staging-coralogix-open-telemetry",
  "networkMode": "awsvpc",
  "revision": 22,
  "volumes": [],
  "status": "ACTIVE",
  "requiresAttributes": [
      {
          "name": "com.amazonaws.ecs.capability.logging-driver.awslogs"
      },
      {
          "name": "ecs.capability.execution-role-awslogs"
      },
      {
          "name": "com.amazonaws.ecs.capability.docker-remote-api.1.19"
      },
      {
          "name": "ecs.capability.secrets.asm.environment-variables"
      },
      {
          "name": "com.amazonaws.ecs.capability.docker-remote-api.1.17"
      },
      {
          "name": "com.amazonaws.ecs.capability.task-iam-role"
      },
      {
          "name": "com.amazonaws.ecs.capability.docker-remote-api.1.18"
      },
      {
          "name": "ecs.capability.task-eni"
      },
      {
          "name": "com.amazonaws.ecs.capability.docker-remote-api.1.29"
      }
  ],
  "placementConstraints": [],
  "compatibilities": [
      "EC2",
      "FARGATE"
  ],
  "requiresCompatibilities": [
      "FARGATE"
  ],
  "cpu": "1024",
  "memory": "3072",
  "runtimePlatform": {
      "cpuArchitecture": "X86_64",
      "operatingSystemFamily": "LINUX"
  },
  "tags": [
      {
          "key": "project",
          "value": "<PROJECT_NAME>"
      }
  ]
}

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 support@coralogix.com.

Previous SentinelOne (Syslog)
Next Snyk Vulnerability Monitoring with Coralogix
Home
User Guides
DataPrime
Integrations
OpenTelemetry
Developer Portal