clickhouse

Connect to clickhouse-client Open interactive ClickHouse CLI session clickhouse-client -h clickhouse -u default --password <pass> --database mydb #clickhouse Show databases List all databases in ClickHouse clickhouse-client -q 'SHOW DATABASES' #clickhouse Show tables List all tables in a database clickhouse-client -q 'SHOW TABLES FROM mydb' #clickhouse Table size Show disk usage per table in a database clickhouse-client -q "SELECT table, formatReadableSize(sum(bytes)) AS size FROM system.parts WHERE active AND database='mydb' GROUP BY table ORDER BY sum(bytes) DESC" #clickhouse#monitoring Running queries List all currently running queries with elapsed time clickhouse-client -q 'SELECT query_id, user, elapsed, query FROM system.processes ORDER BY elapsed DESC' #clickhouse#monitoring#debug Kill query Terminate a long-running or stuck query clickhouse-client -q "KILL QUERY WHERE query_id='<id>'" #clickhouse#debug Drop caches Flush ClickHouse caches to free memory clickhouse-client -q 'SYSTEM DROP MARK CACHE; SYSTEM DROP UNCOMPRESSED CACHE' #clickhouse Sync replica Force replica to sync with ZooKeeper state clickhouse-client -q 'SYSTEM SYNC REPLICA mydb.myTable' #clickhouse#monitoring Connect and run a query Execute a one-shot query via clickhouse-client clickhouse-client --host ch.example.com --user default --password secret -q 'SELECT version()' #clickhouse Check running queries List longest running queries in the system clickhouse-client -q 'SELECT query_id, elapsed, query FROM system.processes ORDER BY elapsed DESC LIMIT 10' #clickhouse#debug#monitoring Kill a running query Force-stop a specific query by its query_id clickhouse-client -q "KILL QUERY WHERE query_id = 'abc-123'" #clickhouse#debug Table disk usage Top 20 tables by on-disk size clickhouse-client -q 'SELECT database, table, formatReadableSize(sum(bytes_on_disk)) AS size FROM system.parts GROUP BY database, table ORDER BY sum(bytes_on_disk) DESC LIMIT 20' #clickhouse#monitoring Optimize table (force merge) Force merging of all parts (use with caution on large tables) clickhouse-client -q 'OPTIMIZE TABLE mydb.mytable FINAL' #clickhouse Ingest CSV from file Bulk-load a CSV file into a ClickHouse table clickhouse-client --query 'INSERT INTO mydb.events FORMAT CSVWithNames' < events.csv #clickhouse Check replication status Show replication lag on delayed replicas clickhouse-client -q 'SELECT database, table, is_leader, queue_size, absolute_delay FROM system.replicas WHERE absolute_delay > 0' #clickhouse#monitoring Slowest recent queries Top slow finished queries last hour with memory; type=2 = QueryFinish clickhouse-client -q "SELECT query_duration_ms, formatReadableSize(memory_usage) mem, substring(query,1,80) FROM system.query_log WHERE type=2 AND event_time>now()-3600 ORDER BY query_duration_ms DESC LIMIT 10" #clickhouse#performance#troubleshooting#observability Pending mutations Find stuck ALTER/DELETE mutations with failure reason; blocks schema changes clickhouse-client -q "SELECT database, table, mutation_id, latest_fail_reason FROM system.mutations WHERE is_done=0" #clickhouse#troubleshooting#debug#monitoring