OLAP Database

ClickHouse Connector

Comprehensive audit logging for ClickHouse analytical databases. Monitor all queries, track user access, and capture performance metrics from your high-speed OLAP workloads.

Native Deep Integration

DB Audit leverages ClickHouse's built-in query_log tables for zero-overhead audit collection with full query details, performance metrics, and resource usage statistics.

system.query_log Performance Metrics Resource Tracking

Start Free Trial View Configuration

High-Speed Auditing

Capture millions of queries per second without impacting ClickHouse performance.

Resource Metrics

Track memory usage, CPU time, and data read/written for every query.

Table-Level Tracking

Monitor which databases, tables, and columns are accessed in each query.

Prerequisites

Supported Versions

ClickHouse 21.8 and later
ClickHouse Cloud (all tiers)
Altinity.Cloud deployments
Self-managed ClickHouse clusters

Network Requirements

Port 8443 (HTTPS) - Recommended
Port 9440 (Native TLS) - Alternative
Port 8123 (HTTP) - Non-SSL
Port 9000 (Native) - Non-SSL

Configuration Reference

1 Basic Connection

Field	Type	Required	Default	Description
name	string	Yes	-	A unique, descriptive name for this connection (e.g., "clickhouse-analytics-prod")
db_type	select	Yes	clickhouse	Database type - select "ClickHouse"
host	string	Yes	-	ClickHouse server hostname (e.g., "clickhouse.example.com" or ClickHouse Cloud hostname)
port	number	Yes	8443	ClickHouse HTTPS port (8443) or HTTP port (8123) or native port (9440/9000)
database_name	string	Yes	default	Target database name (e.g., "default", "analytics")
username	string	Yes	-	ClickHouse user with audit privileges (e.g., "dbaudit_reader")
password	password	Yes	-	Password for the ClickHouse user

2 SSL/TLS Configuration

Field	Type	Required	Default	Description
use_ssl	boolean	No	true	Enable SSL/TLS encryption for connections
ssl_ca_cert	textarea	No	-	CA certificate for verifying the ClickHouse server certificate
ssl_client_cert	textarea	No	-	Client certificate for certificate-based authentication
ssl_client_key	password	No	-	Client private key for certificate-based authentication
verify_ssl	boolean	No	true	Verify server SSL certificate

3 Cloud Configuration

Field	Type	Required	Description
cloud_provider	select	No	Cloud provider: clickhouse_cloud, aws, azure, gcp
cloud_region	string	No	Cloud region for ClickHouse Cloud deployments
organization_id	string	No	ClickHouse Cloud organization ID
service_id	string	No	ClickHouse Cloud service ID

4 Log Collection Configuration

Field	Type	Required	Default	Description
log_collection_type	select	Yes	native_audit	Log collection method: native_audit (query_log tables), custom_query
use_query_log	boolean	No	true	Enable collection from system.query_log table
use_query_thread_log	boolean	No	false	Include thread-level query details
poll_interval	number	No	30	How often to poll for new audit events (in seconds)
query_log_retention_days	number	No	30	Days to retain query log data in ClickHouse

Log Collection Methods

Query Log Tables

Recommended

ClickHouse native system.query_log for comprehensive query auditing

Advantages:

Complete query history
Performance metrics included
Memory and CPU stats
No external dependencies

Setup: Enable query_log in ClickHouse server configuration

Query Thread Log

Thread-level query execution details from system.query_thread_log

Advantages:

Detailed thread metrics
Memory usage per thread
Profile events

Setup: Enable query_thread_log in server config

Part Log

Track data part operations via system.part_log

Advantages:

Data mutation tracking
Merge operations
Part lifecycle

Setup: Enable part_log in server config

Setup Instructions

Create Audit User

Create a dedicated ClickHouse user for DB Audit with read access to system tables.

                -- Create a dedicated audit user
CREATE USER dbaudit_reader IDENTIFIED BY 'secure_password';

-- Grant necessary privileges
GRANT SELECT ON system.query_log TO dbaudit_reader;
GRANT SELECT ON system.query_thread_log TO dbaudit_reader;
GRANT SELECT ON system.part_log TO dbaudit_reader;
GRANT SELECT ON system.processes TO dbaudit_reader;
GRANT SELECT ON system.users TO dbaudit_reader;
GRANT SELECT ON system.roles TO dbaudit_reader;
GRANT SELECT ON system.grants TO dbaudit_reader;
GRANT SELECT ON system.quota_usage TO dbaudit_reader;

-- Grant read access to specific databases (optional)
GRANT SELECT ON analytics.* TO dbaudit_reader;

Enable Query Logging

Configure ClickHouse to log queries to system tables (usually enabled by default).

                <!-- config.xml or config.d/query_log.xml -->
<clickhouse>
    <query_log>
        <database>system</database>
        <table>query_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_log>

    <query_thread_log>
        <database>system</database>
        <table>query_thread_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_thread_log>

    <part_log>
        <database>system</database>
        <table>part_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </part_log>
</clickhouse>

Tip: On ClickHouse Cloud, query_log is enabled by default. No additional configuration is needed.

Verify Query Log Access

Test that the audit user can read from the query_log table.

                -- Query recent queries from query_log
SELECT
    event_time,
    user,
    query_id,
    query_kind,
    query,
    type,
    databases,
    tables,
    columns,
    read_rows,
    written_rows,
    result_rows,
    memory_usage,
    query_duration_ms,
    client_hostname,
    client_name,
    exception_code,
    exception
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR
  AND type IN ('QueryFinish', 'ExceptionWhileProcessing')
ORDER BY event_time DESC
LIMIT 100;

-- Query currently running queries
SELECT
    query_id,
    user,
    query,
    elapsed,
    memory_usage,
    read_rows,
    written_rows
FROM system.processes;

Test Connection

Verify connectivity using clickhouse-client or curl.

                # Test connection using clickhouse-client
clickhouse-client --host localhost --port 9000 \
    --user dbaudit_reader --password 'secure_password' \
    --query "SELECT 1"

# Test with HTTPS
clickhouse-client --host localhost --port 8443 \
    --secure --user dbaudit_reader \
    --query "SELECT * FROM system.query_log LIMIT 1"

# Verify audit user permissions
clickhouse-client --user dbaudit_reader \
    --query "SELECT count() FROM system.query_log"

Configure in DB Audit

Add the ClickHouse connection in the DB Audit dashboard.

Navigate to Connections in DB Audit
Click Add Connection
Select ClickHouse as the database type
Enter your connection details and credentials
Enable SSL if using secure ports (8443 or 9440)
Test the connection and save

Data Collected

All Queries

SELECT, INSERT, CREATE, ALTER, and all query types

Performance Metrics

Duration, rows read/written, memory usage

Table Access

Databases, tables, and columns accessed

User Identity

User, client host, application name

Exceptions

Query errors, exception codes and messages

Data Parts

Merge operations, mutations, part lifecycle

Settings Changes

Query-level settings and session config

Thread Info

Per-thread execution metrics and profile events

Troubleshooting

Connection refused on port 8443/9440

Ensure SSL is enabled on the ClickHouse server and the secure ports are open.

            # Check if HTTPS is enabled
curl -k https://localhost:8443/ping

Access denied to system.query_log

Grant SELECT privileges on system tables to the audit user.

            GRANT SELECT ON system.query_log TO dbaudit_reader;

Query log table is empty

Verify query_log is enabled and flushed. Wait for the flush interval or check configuration.

            SELECT * FROM system.query_log LIMIT 1;
SYSTEM FLUSH LOGS;

SSL certificate verification failed

For self-signed certificates, either provide the CA cert or disable verification (not recommended for production).

Compliance Support

SOC 2

Access logging and change tracking

HIPAA

PHI access auditing

PCI DSS

Cardholder data monitoring

GDPR

Personal data access trails

Ready to Audit Your ClickHouse Databases?

Start monitoring your analytical databases in minutes with comprehensive query logging.

Start Free Trial Contact Sales

ClickHouse Connector

Native Deep Integration

High-Speed Auditing

Resource Metrics

Table-Level Tracking

Prerequisites

Supported Versions

Network Requirements

Configuration Reference

1 Basic Connection

2 SSL/TLS Configuration

3 Cloud Configuration

4 Log Collection Configuration

Log Collection Methods

Query Log Tables

Advantages:

Query Thread Log

Advantages:

Part Log

Advantages:

Setup Instructions

Create Audit User

Enable Query Logging

Verify Query Log Access

Test Connection

Configure in DB Audit

Data Collected

Troubleshooting

Connection refused on port 8443/9440

Access denied to system.query_log

Query log table is empty

SSL certificate verification failed

Compliance Support

Next Steps

Configure Alerts

Create Policies

SIEM Integration

Ready to Audit Your ClickHouse Databases?

ClickHouse Connector

Native Deep Integration

High-Speed Auditing

Resource Metrics

Table-Level Tracking

Prerequisites

Supported Versions

Network Requirements

Configuration Reference

1 Basic Connection

2 SSL/TLS Configuration

3 Cloud Configuration

4 Log Collection Configuration

Log Collection Methods

Query Log Tables

Advantages:

Query Thread Log

Advantages:

Part Log

Advantages:

Setup Instructions

Create Audit User

Enable Query Logging

Verify Query Log Access

Test Connection

Configure in DB Audit

Data Collected

Troubleshooting

Connection refused on port 8443/9440

Access denied to system.query_log

Query log table is empty

SSL certificate verification failed

Compliance Support

Next Steps

Configure Alerts

Create Policies

SIEM Integration

Ready to Audit Your ClickHouse Databases?

Start Your Free Trial

Thank You!