Configure ODP ClickHouse

To enable Pulse to monitor and collect query-level and performance metrics from ClickHouse, ensure the following configurations are applied in Ambari.

Expose ClickHouse Prometheus Metrics

Expose ClickHouse Prometheus metrics so Pulse can collect real-time operational data.

1. In the Ambari UI, go to Service > ClickHouse > Configs > Advanced clickhouse-server-config > ClickHouse Server Template.
2. In the ClickHouse Server Template, add the following configuration:

<prometheus>

  <endpoint>/metrics</endpoint>

  <port>9363</port>

  <metrics>true</metrics>

  <events>true</events>

  <asynchronous_metrics>true</asynchronous_metrics>

</prometheus>

3. Save the changes. and restart the ClickHouse service for the changes to take effect.

Purpose:

• Exposes metrics on port 9363. This is the default port, but you can configure it to use any other port as needed. Ensure that the same port number is configured in both Ambari and Pulse for successful communication.
• Enables collection of events and asynchronous metrics.
• Allows Pulse to scrape Prometheus data for ClickHouse performance insights.

Enable Query Logging and Tracing

Enable ClickHouse query logging and tracing so that Pulse can collect and analyze real-time data from all queries and trace events.

1. In the Ambari UI, go to Services > ClickHouse > Configs > Advanced ClickHouse Users > Clickhouse Users Template.
2. In Clickhouse Users Template, add the following details to the ClickHouse configuration file (if it doesn’t exist already).

<clickhouse>

  <profiles>

    <default>

      <!-- Query logs -->

      <log_queries>1</log_queries>

      <log_query_threads>1</log_query_threads>

​

      <!-- Optional: log settings applied per query -->

      <log_query_settings>1</log_query_settings>

​

      <!-- Internal tracing -->

      <!-- 0..1, probability to start a trace for a query -->

      <opentelemetry_start_trace_probability>1</opentelemetry_start_trace_probability>

​

      <!-- Include internal processors/stages -->

      <opentelemetry_trace_processors>1</opentelemetry_trace_processors>

    </default>

  </profiles>

</clickhouse>

3. Save the changes.

Purpose:

• Captures all query executions and thread-level logs.
• Enables OpenTelemetry tracing for all queries.
• Provides full visibility into query performance for Pulse.

Query Listing Latency in Pulse

The Query visibility latency in Pulse depends on the flush interval configuration of query_log and opentelemetry_span_log.

1. In the Amabari UI, go to ClickHouse.
2. In the ClickHouse page, select Configs.
3. In Advanced clickhouse-server-config, update the changes in ClickHouse Server Template.

Create Materialized Views for NATS Integration

Create materialized views in ClickHouse to publish query logs and span events to NATS.

This setup enables real-time streaming of ClickHouse activity to Pulse for monitoring and analysis.

Before You Begin

• Obtain the Pulse IP address:

  • In case of Docker: Use the IP address of the Pulse core node.
  • In case of Kubernetes: Use the IP address of the Pulse database node.

• Verify network connectivity: Ensure that the ClickHouse node can connect to the Pulse node on port 19009.

• Confirm ClickHouse configuration: Verify that the following settings are enabled in the ClickHouse configuration file:

  • log_queries=1
  • opentelemetry_start_trace_probability=1

These settings ensure that query logs and OpenTelemetry span logs are captured and forwarded to Pulse.

Run the Command

Run the following commands in the ClickHouse client (clickhouse-client) on a ClickHouse server node.

For 4.0.x:

CREATE DATABASE IF NOT EXISTS pulse_stream;

​

-- Query Events → NATS --

CREATE TABLE IF NOT EXISTS pulse_stream.query_log_to_nats

AS system.query_log

ENGINE = NATS

SETTINGS

    nats_url = '<pulse_node>:19009',

    nats_subjects = 'clickhouse.query_log',

    nats_format = 'JSONEachRow',

    nats_max_rows_per_message = 1;

​

CREATE MATERIALIZED VIEW IF NOT EXISTS pulse_stream.mv_query_log_to_nats

TO pulse_stream.query_log_to_nats AS

SELECT *

FROM system.query_log;

​

-- Traces (Spans) → NATS --

CREATE TABLE IF NOT EXISTS pulse_stream.spans_to_nats

AS system.opentelemetry_span_log

ENGINE = NATS

SETTINGS

    nats_url = '<pulse_node>:19009',

    nats_subjects = 'clickhouse.spans',

    nats_format = 'JSONEachRow',

    nats_max_rows_per_message = 1;

CREATE MATERIALIZED VIEW IF NOT EXISTS pulse_stream.mv_spans_to_nats

TO pulse_stream.spans_to_nats AS

SELECT *

FROM system.opentelemetry_span_log;

For 4.1.x:

CREATE DATABASE IF NOT EXISTS pulse_stream;

​

​

-- Query events → NATS --

CREATE TABLE IF NOT EXISTS pulse_stream.query_log_to_nats

AS system.query_log

ENGINE = NATS

SETTINGS

    nats_url = '<pulse_node>:19009',

    nats_subjects = 'clickhouse_events_<cluster_name>.0',

    nats_format = 'JSONEachRow',

    nats_max_rows_per_message = 1;

​

CREATE MATERIALIZED VIEW IF NOT EXISTS pulse_stream.mv_query_log_to_nats

TO pulse_stream.query_log_to_nats AS

SELECT *

FROM system.query_log;

​

-- Traces (spans) → NATS --

CREATE TABLE IF NOT EXISTS pulse_stream.spans_to_nats

AS system.opentelemetry_span_log

ENGINE = NATS

SETTINGS

    nats_url = '<pulse_node>:19009',

    nats_subjects = 'clickhouse_events_<cluster_name>.0',

    nats_format = 'JSONEachRow',

    nats_max_rows_per_message = 1;

​

CREATE MATERIALIZED VIEW IF NOT EXISTS pulse_stream.mv_spans_to_nats

TO pulse_stream.spans_to_nats AS

SELECT *

FROM system.opentelemetry_span_log;

Replace <pulse_node> with the IP obtained.