ClickHouse

Edit config.xml or Add to config.d/

<!-- /etc/clickhouse-server/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>

    <!-- Enable query thread log for detailed analysis -->
    <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>
</clickhouse>

Verify Query Logging is Active

-- Check if query_log table exists
SHOW TABLES FROM system LIKE 'query_log';

-- Verify recent queries are being logged
SELECT
    count() as query_count,
    max(event_time) as last_logged_query
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR;

Restart ClickHouse Server

sudo systemctl restart clickhouse-server

Create Read-Only User

-- Create monitoring user with password
CREATE USER db24x7_monitor IDENTIFIED WITH sha256_password BY 'your_secure_password';

-- Grant SELECT privileges on system tables
GRANT SELECT ON system.* TO db24x7_monitor;

-- Grant SELECT on your databases (repeat for each database)
GRANT SELECT ON your_database.* TO db24x7_monitor;

-- Grant cluster access for distributed queries
GRANT SHOW ON *.* TO db24x7_monitor;

Alternative: Using XML Configuration

<!-- /etc/clickhouse-server/users.d/db24x7_monitor.xml -->
<clickhouse>
    <users>
        <db24x7_monitor>
            <password_sha256_hex>SHA256_HASH_HERE</password_sha256_hex>
            <networks>
                <ip>DB24X7_IP_ADDRESS/32</ip>
            </networks>
            <profile>readonly</profile>
            <quota>default</quota>
            <allow_databases>
                <database>system</database>
                <database>your_database</database>
            </allow_databases>
        </db24x7_monitor>
    </users>
</clickhouse>

Verify User Creation

-- List all users
SELECT name FROM system.users;

-- Check user grants
SHOW GRANTS FOR db24x7_monitor;

HTTP Interface (Recommended)

clickhouse://db24x7_monitor:your_secure_password@hostname:8123/default

HTTPS Interface

clickhouse://db24x7_monitor:password@hostname:8443/default?secure=true

Native Protocol

clickhouse://db24x7_monitor:password@hostname:9000/default?protocol=native

Cluster Connection

clickhouse://db24x7_monitor:password@hostname:8123/default?cluster=my_cluster

Connection Parameters

Connection Refused

Common connection issues:

• •Verify ClickHouse is listening on the correct interface (check listen_host in config.xml)
• •Ensure firewall allows ports 8123 (HTTP) or 9000 (native)
• •Check if ClickHouse server is running

# Check if ClickHouse is running
sudo systemctl status clickhouse-server

# Test HTTP interface
curl http://localhost:8123/ping

Authentication Failed

Verify user credentials and permissions:

-- Check if user exists
SELECT name FROM system.users WHERE name = 'db24x7_monitor';

-- Verify user can query system tables
SELECT count() FROM system.query_log LIMIT 1;

-- Check network restrictions
SELECT * FROM system.users WHERE name = 'db24x7_monitor'
FORMAT Vertical;

Query Log Not Available

Verify query logging configuration:

-- Check if query_log is enabled
SELECT
    database,
    table,
    engine
FROM system.tables
WHERE database = 'system' AND table = 'query_log';

-- If empty, check server logs
sudo tail -f /var/log/clickhouse-server/clickhouse-server.log

Cluster Connection Issues

Verify cluster configuration:

-- List available clusters
SELECT cluster FROM system.clusters;

-- Check cluster nodes
SELECT * FROM system.clusters WHERE cluster = 'my_cluster';

-- Verify inter-node connectivity
SELECT * FROM system.replication_queue;