Table of Contents

MetricsHub
MetricsHub Enterprise 2.1.00

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.

If the datatabase to be monitored is not currently supported by MetricsHub[1] (e.g., ClickHouse, Sybase, etc.), you need to download and install the appropriate JDBC driver.

In the example below, we configured MetricsHub to:

  • monitor the clickhouse-server[2] resource using JDBC
  • connect to a ClickHouse database
  • execute a custom SQL query
  • extract and expose database server metrics.

Procedure

Install the ClickHouse JDBC driver

  1. Download the ClickHouse JDBC driver: clickhouse-jdbc-0.8.6-shaded-all.jar[3]

  2. Copy the downloaded .jar file in the extensions/ directory of your MetricsHub installation.

  3. Update the service configuration file to include the driver in the classpath:

    • Community Edition:

      • Windows: MetricsHub/app/MetricsHubServiceManager.cfg
      • Linux: metricshub/lib/app/service.cfg

    • Enterprise Edition:

      • Windows: MetricsHub/app/MetricsHubEnterpriseService.cfg
      • Linux: metricshub/lib/app/enterprise-service.cfg

    Example:

    [Application]
...
app.classpath=$APPDIR\..\extensions\clickhouse-jdbc-0.8.6-shaded-all.jar

Configure MetricsHub

To achieve this use case, we:

  • 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
  • Configure the JDBC protocol
    protocols:
      jdbc:
        url: jdbc:ch://clickhouse-server:18123/system
        username: default
        password: changeme
  • Define a monitor job (clickhouse) to extract server metrics
    monitors:
      clickhouse:
        simple:
  • 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'
                );
  • Map the query result 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
    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

Supporting Resources

custom database sql jdbc monitoring observability opentelemetry otel
Links:
  • [1] https://metricshub.com/docs/latest/connectors/tags/database.html
  • [2] https://clickhouse.com/
  • [3] https://repo1.maven.org/maven2/com/clickhouse/clickhouse-jdbc/0.8.6/clickhouse-jdbc-0.8.6-shaded-all.jar
  • [4] ../configuration/configure-monitoring.html#step-3-configure-resources
  • [5] ../configuration/configure-monitoring.html#resource-attributes
  • [6] ../configuration/configure-monitoring.html#jdbc
  • [7] ../configuration/configure-monitoring.html#customize-resource-monitoring
No results.