Clickhouse Metrics and Logs

Overview

This integration helps you monitor key Clickhouse metrics and logs, view them with an out-of-the-box dashboard, and collect query logs for better querying and aggregation.

Prerequisites

Before you begin, ensure you have:

A Clickhouse server running version 23 or newer
An OpenTelemetry (OTEL) Collector with access to the Clickhouse server
- Install the SigNoz OTEL Collector (v0.88.0 or newer, v0.88.23+ for query log collection) if not done already
- Ensure you can provide config files and set environment variables for the collector
- Ensure the OTEL collector has access to the Clickhouse server for metrics collection and can read the Clickhouse server log file for logs collection

Preparing Clickhouse Server

Check Clickhouse Version

Ensure your Clickhouse server is running a supported version (v23 or newer):

SELECT version();

Configure Prometheus Metrics Export

If collecting metrics, ensure that Clickhouse is configured to export Prometheus metrics. Follow the Clickhouse Prometheus configuration guide if needed.

Create Monitoring User

If collecting query logs, create a monitoring user with required permissions:

CREATE USER monitoring IDENTIFIED BY 'monitoring_password';
GRANT SELECT ON system.query_log to monitoring;
-- If monitoring a clustered deployment, also grant privilege for executing remote queries
GRANT REMOTE ON *.* TO 'monitoring' on CLUSTER 'cluster_name';

Collecting Clickhouse Metrics

Step 1: Create the Collector Config File

Create a file named clickhouse-metrics-collection-config.yaml with the following content:

receivers:
  prometheus/clickhouse:
    config:
      global:
        scrape_interval: 60s
      scrape_configs:
        - job_name: clickhouse
          static_configs:
            - targets:
                - ${env:CLICKHOUSE_PROM_METRICS_ENDPOINT}
          metrics_path: ${env:CLICKHOUSE_PROM_METRICS_PATH}

processors:
  # enriches the data with additional host information
  # see https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/processor/resourcedetectionprocessor#resource-detection-processor
  resourcedetection/system:
    # add additional detectors if needed
    detectors: ["system"]
    system:
      hostname_sources: ["os"]

exporters:
  # export to SigNoz cloud
  otlp/clickhouse:
    endpoint: "${env:OTLP_DESTINATION_ENDPOINT}"
    tls:
      insecure: false
    headers:
      "signoz-access-token": "${env:SIGNOZ_INGESTION_KEY}"

  # export to local collector
  # otlp/clickhouse:
  #   endpoint: "localhost:4317"
  #   tls:
  #     insecure: true

service:
  pipelines:
    metrics/clickhouse:
      receivers: [prometheus/clickhouse]
      # note: remove this processor if the collector host is not running on the same host as the clickhouse instance
      processors: [resourcedetection/system]
      exporters: [otlp/clickhouse]

Step 2: Set Environment Variables

Set the following environment variables:

# Prometheus metrics endpoint on the clickhouse server
export CLICKHOUSE_PROM_METRICS_ENDPOINT="clickhouse:9363"

# Prometheus metrics path on the clickhouse server
export CLICKHOUSE_PROM_METRICS_PATH="/metrics"

# region specific SigNoz cloud ingestion endpoint
export OTLP_DESTINATION_ENDPOINT="ingest.us.signoz.cloud:443"

# your SigNoz ingestion key
export SIGNOZ_INGESTION_KEY="signoz-ingestion-key"

You can find more details about ingestion keys and Regions here

Step 3: Use the Collector Config File

Add the following flag to your collector run command:

--config clickhouse-metrics-collection-config.yaml

Note: The collector can use multiple config files by specifying multiple --config flags.

Collecting Clickhouse Server Logs

You can configure Clickhouse server logs collection by providing the required collector config to your collector.

Step 1: Create the Collector Config File

Create a file named clickhouse-logs-collection-config.yaml with the following content:

receivers:
  filelog/clickhouse:
    include: ["${env:CLICKHOUSE_LOG_FILE}"]
    operators:
      # Parse default clickhouse text log format.
      # See https://github.com/ClickHouse/ClickHouse/blob/master/src/Loggers/OwnPatternFormatter.cpp
      - type: recombine
        source_identifier: attributes["log.file.name"]
        is_first_entry: body matches '^\\d{4}\\.\\d{2}\\.\\d{2}\\s+'
        combine_field: body
        overwrite_with: oldest
      - type: regex_parser
        parse_from: body
        if: body matches '^(?P<ts>\\d{4}\\.\\d{2}\\.\\d{2} \\d{2}:\\d{2}:\\d{2}.?[0-9]*)\\s+\\[\\s+(\\x1b.*?m)?(?P<thread_id>\\d*)(\\x1b.*?m)?\\s+\\]\\s+{((\\x1b.*?m)?(?P<query_id>[0-9a-zA-Z-_]*)(\\x1b.*?m)?)?}\\s+<(\\x1b.*?m)?(?P<log_level>\\w*)(\\x1b.*?m)?>\\s+((\\x1b.*?m)?(?P<clickhouse_component>[a-zA-Z0-9_]+)(\\x1b.*?m)?:)?\\s+(?s)(?P<message>.*)$'
        regex: '^(?P<ts>\d{4}\.\d{2}\.\d{2} \d{2}:\d{2}:\d{2}.?[0-9]*)\s+\[\s+(\x1b.*?m)?(?P<thread_id>\d*)(\x1b.*?m)?\s+\]\s+{((\x1b.*?m)?(?P<query_id>[0-9a-zA-Z-_]*)(\x1b.*?m)?)?}\s+<(\x1b.*?m)?(?P<log_level>\w*)(\x1b.*?m)?>\s+((\x1b.*?m)?(?P<clickhouse_component>[a-zA-Z0-9_]+)(\x1b.*?m)?:)?\s+(?s)(?P<message>.*)$'
      - type: time_parser
        if: attributes.ts != nil
        parse_from: attributes.ts
        layout_type: gotime
        layout: 2006.01.02 15:04:05.999999
        location: ${env:CLICKHOUSE_TIMEZONE}
      - type: remove
        if: attributes.ts != nil
        field: attributes.ts
      - type: severity_parser
        if: attributes.log_level != nil
        parse_from: attributes.log_level
        overwrite_text: true
        # For mapping details, see getPriorityName defined in https://github.com/ClickHouse/ClickHouse/blob/master/src/Interpreters/InternalTextLogsQueue.cpp
        mapping:
          trace:
            - Trace
            - Test
          debug: Debug
          info:
            - Information
            - Notice
          warn: Warning
          error: Error
          fatal:
            - Fatal
            - Critical
      - type: remove
        if: attributes.log_level != nil
        field: attributes.log_level
      - type: move
        if: attributes.message != nil
        from: attributes.message
        to: body
      - type: add
        field: attributes.source
        value: clickhouse

processors:
  batch:
    send_batch_size: 10000
    send_batch_max_size: 11000
    timeout: 10s

exporters:
  # export to SigNoz cloud
  otlp/clickhouse-logs:
    endpoint: "${env:OTLP_DESTINATION_ENDPOINT}"
    tls:
      insecure: false
    headers:
      "signoz-access-token": "${env:SIGNOZ_INGESTION_KEY}"

  # export to local collector
  # otlp/clickhouse-logs:
  #   endpoint: "localhost:4317"
  #   tls:
  #     insecure: true

service:
  pipelines:
    logs/clickhouse:
      receivers: [filelog/clickhouse]
      processors: [batch]
      exporters: [otlp/clickhouse-logs]

Step 2: Set Environment Variables

Set the following environment variables:

# Path of Clickhouse server log file
export CLICKHOUSE_LOG_FILE="/var/log/clickhouse-server/server.log"

# Timezone of the Clickhouse server
export CLICKHOUSE_TIMEZONE="Etc/UTC"

# Region specific SigNoz cloud ingestion endpoint
export OTLP_DESTINATION_ENDPOINT="ingest.{REGION}.signoz.cloud:443"

# Your SigNoz ingestion key
export SIGNOZ_INGESTION_KEY="signoz-ingestion-key"

You can find more details about ingestion keys and Regions here

Step 3: Use the Collector Config File

Add the following flag to your collector run command:

--config clickhouse-logs-collection-config.yaml

Note: The collector can use multiple config files by specifying multiple --config flags.

Collecting Clickhouse Query Logs

Step 1: Create the Collector Config File

Create a file named clickhouse-query-logs-collection-config.yaml with the following content:

receivers:
  clickhousesystemtablesreceiver/query_log:
    dsn: "${env:CLICKHOUSE_MONITORING_DSN}"
    cluster_name: "${env:CLICKHOUSE_CLUSTER_NAME}"
    query_log_scrape_config:
      scrape_interval_seconds: ${env:QUERY_LOG_SCRAPE_INTERVAL_SECONDS}
      min_scrape_delay_seconds: ${env:QUERY_LOG_SCRAPE_DELAY_SECONDS}

exporters:
  # export to SigNoz cloud
  otlp/clickhouse-query-logs:
    endpoint: "${env:OTLP_DESTINATION_ENDPOINT}"
    tls:
      insecure: false
    headers:
      "signoz-access-token": "${env:SIGNOZ_INGESTION_KEY}"

  # export to local collector
  # otlp/clickhouse-query-logs:
  #   endpoint: "localhost:4317"
  #   tls:
  #     insecure: true

service:
  pipelines:
    logs/clickhouse-query-logs:
      receivers: [clickhousesystemtablesreceiver/query_log]
      processors: []
      exporters: [otlp/clickhouse-query-logs]

Step 2: Set Environment Variables

Set the following environment variables:

# DSN for connecting to clickhouse with the monitoring user
export CLICKHOUSE_MONITORING_DSN="tcp://monitoring:monitoring_password@clickhouse:9000/"

# If collecting query logs from a clustered deployment, specify a non-empty cluster name
export CLICKHOUSE_CLUSTER_NAME=""

# Rows from query_log table will be collected periodically based on this setting
export QUERY_LOG_SCRAPE_INTERVAL_SECONDS=20

# Must be greater than flush_interval_milliseconds setting for query_log
export QUERY_LOG_SCRAPE_DELAY_SECONDS=8

# region specific SigNoz cloud ingestion endpoint
export OTLP_DESTINATION_ENDPOINT="ingest.{REGION}.signoz.cloud:443"

# your SigNoz ingestion key
export SIGNOZ_INGESTION_KEY="signoz-ingestion-key"

You can find more details about ingestion keys and Regions here

Step 3: Use the Collector Config File

Add the following flag to your collector run command:

--config clickhouse-query-logs-collection-config.yaml

Note:

Only one collector instance should be configured to collect query_logs. Using multiple instances or replicas will lead to duplicate logs.
The collector can use multiple config files by specifying multiple --config flags.

Connect Clickhouse

Once you've set up Clickhouse for collecting metrics and logs, go to the integrations tab in SigNoz and search for the Clickhouse integration.

*Search for Clickhouse in Integrations tab*

Click on the Connect Clickhouse Button, and select I have already configured. This will start listening for data from your Clickhouse instance. To stop this, you can select the Remove from SigNoz button.

Clickhouse dashboard

Once SigNoz has started listening to your Clickhouse data, go to the Dashboards tab and search for Clickhouse. This will show you a newly created dashboard which displays various Clickhouse metrics and query log information.

*Dashboard for monitoring Clickhouse Metrics*

Dashboard asset

You can also manually create the above Clickhouse Dashboard by importing the JSON file available here. To learn how to create Dashboards, checkout this documentation.

Data Collected

When you switch to the Data Collected tab of your Clickhouse Integration, it shows you details about the different metrics types, server log attributes, and query log attributes that you can monitor for your Clickhouse instance.

*Log attributes and metrics details for Clickhouse*

Clickhouse log attributes

Name: The name of the log attribute.
Path: The specific location or attribute within a log entry where the corresponding data can be found.
Type: The data type of the log attribute.

Name	Path	Type
Timestamp	timestamp	timestamp
Severity Text	severity_text	string
Severity Number	severity_number	number
Thread ID	attributes.thread_id	string
Query ID	attributes.query_id	string
Clickhouse Component	attributes.clickhouse_component	string

Clickhouse metrics

Name: The name of the metric.
Type: The type of the metric (e.g., Sum, Gauge).
Unit: The unit of measurement for the metric.
Description: A brief description of what the metric represents.

To find a complete list of metrics you can checkout this link or the Data Collected tab.

Was this page helpful?

MongoDB

AWS RDS PostgreSQL

Try SigNoz Cloud for FREE

Instant setup, predictable pricing, and advanced features without infrastructure hassles.

Get Started - Free