Skip to main content

Database Query

You can configure MetricsHub to periodically poll any SQL-compatible database using JDBC, execute custom queries, retrieve tabular results, and push OpenTelemetry metrics with the extracted values.

In the example below, we configured MetricsHub to:

  • monitor the clickhouse-server resource using an external JDBC driver
  • connect to a ClickHouse database
  • execute a custom SQL query
  • extract and expose database server metrics.

Procedure for ClickHouse​

Install the ClickHouse JDBC driver​

  1. Download the clickhouse-jdbc-0.8.6-shaded-all.jar JDBC driver
  2. Copy the downloaded .jar file to a local directory on the machine running MetricsHub (e.g., /opt/db/drivers).

Configure MetricsHub​

  1. Declare the resource to be monitored (clickhouse-server) and its attributes (host.name, host.type)
resources:
  clickhouse-server:
    attributes:
      host.name: clickhouse-server
      host.type: linux
  1. Configure the JDBC protocol with the connection URL and declare the ClickHouse driver as an external driver
    protocols:
      jdbc:
        url: jdbc:ch://clickhouse-server:18123/system
        username: default
        password: changeme
        driver:
          className: com.clickhouse.jdbc.ClickHouseDriver
          jarPath: /opt/db/drivers/clickhouse-jdbc-0.8.6-shaded-all.jar
  1. Define a monitor job (clickhouse) to extract server metrics
    monitors:
      clickhouse:
        simple:
  1. Set up the SQL source (clickhouseMetrics) with a ClickHouse query returning multiple metrics
          sources:
            clickhouseMetrics:
              type: sql
              query: |
                SELECT
                    currentDatabase() AS db_namespace,
                    hostName() AS db_server_name,
                    MAX(IF(metric = 'Query', value, NULL)) AS db_server_queries,
                    MAX(IF(metric = 'HTTPConnection', value, NULL)) AS db_server_current_connections,
                    MAX(IF(metric = 'OpenFileForRead', value, NULL)) AS db_server_storage_files,
                    MAX(IF(metric = 'MemoryTracking', value, NULL)) AS db_server_cache_usage
                FROM system.metrics
                WHERE metric IN (
                    'Query',
                    'HTTPConnection',
                    'OpenFileForRead',
                    'MemoryTracking'
                );
  1. Map results to OpenTelemetry attributes and metrics
          mapping:
            source: ${source::clickhouseMetrics}
            attributes:
              db.system: clickhouse
              id: $1
              db.server.namespace: $1
              db.server.name: $2
            metrics:
              db.server.queries: $3
              db.server.current_connections: $4
              db.server.storage.files: $5
              db.server.cache.usage: $6

Here is the complete YAML configuration:

resources:
  clickhouse-server:
    attributes:
      host.name: clickhouse-server
      host.type: linux
    protocols:
      jdbc:
        url: jdbc:ch://clickhouse-server:18123/system
        username: default
        password: changeme
        driver:
          className: com.clickhouse.jdbc.ClickHouseDriver
          jarPath: /opt/db/drivers/clickhouse-jdbc-0.8.6-shaded-all.jar
    monitors:
      clickhouse:
        simple:
          sources:
            clickhouseMetrics:
              type: sql
              query: |
                SELECT
                    currentDatabase() AS db_namespace,
                    hostName() AS db_server_name,
                    MAX(IF(metric = 'Query', value, NULL)) AS db_server_queries,
                    MAX(IF(metric = 'HTTPConnection', value, NULL)) AS db_server_current_connections,
                    MAX(IF(metric = 'OpenFileForRead', value, NULL)) AS db_server_storage_files,
                    MAX(IF(metric = 'MemoryTracking', value, NULL)) AS db_server_cache_usage
                FROM system.metrics
                WHERE metric IN (
                    'Query',
                    'HTTPConnection',
                    'OpenFileForRead',
                    'MemoryTracking'
                );
          mapping:
            source: ${source::clickhouseMetrics}
            attributes:
              db.system: clickhouse
              id: $1
              db.server.namespace: $1
              db.server.name: $2
            metrics:
              db.server.queries: $3
              db.server.current_connections: $4
              db.server.storage.files: $5
              db.server.cache.usage: $6

Once the configuration is saved, restart MetricsHub to start monitoring the database immediately.

Supporting Resources​