Clickhouse Query

chcli — ClickHouse CLI

chcli is a lightweight ClickHouse command-line client. Use it to run SQL queries, explore schemas, and extract data from ClickHouse databases.

Running chcli

Prefer bunx if Bun is available, otherwise use npx:

bunx @obsessiondb/chcli -q "SELECT 1"
npx @obsessiondb/chcli -q "SELECT 1"

Or install globally:

bun install -g chcli
chcli -q "SELECT 1"

Connection

Set connection details via named environments (recommended), environment variables, or CLI flags.

Named Environments

Named environments store connection profiles in ~/.config/chcli/config.json. This is the recommended approach when working with multiple ClickHouse instances.

# Save a named environment
chcli env add prod --host ch.prod.com --port 8443 --secure -u admin --password secret

# Use it for a query
chcli -e prod -q "SELECT count() FROM events"

# Or set it as the default for the current directory
chcli env use prod
chcli -q "SELECT count() FROM events"  # uses prod automatically

Manage environments with chcli env:

Environment Variables

CLICKHOUSE_URL accepts a full URL (e.g. https://host:8443) and is parsed into host, port, secure, and password as a fallback when the individual env vars are not set.

Resolution Order

CLI flag > Named environment (--env or folder default) > Individual env var > CLICKHOUSE_URL (parsed) > Default value

For agent workflows, prefer setting env vars in a .env file (Bun loads .env automatically), using named environments, or a secrets manager like Doppler so every invocation uses the same connection without repeating flags.

See references/connection.md for detailed connection examples.

Query Patterns

Inline query (most common for agents):

bunx @obsessiondb/chcli -q "SELECT count() FROM events"

From a SQL file:

bunx @obsessiondb/chcli -f query.sql

Via stdin pipe:

echo "SELECT 1" | bunx @obsessiondb/chcli

Output Formats

Always use -F json or -F csv when the output will be parsed by an agent. The default format (pretty) is for human display and is difficult to parse programmatically.

# JSON — best for structured parsing
bunx @obsessiondb/chcli -q "SELECT * FROM events LIMIT 5" -F json

# CSV — good for tabular data
bunx @obsessiondb/chcli -q "SELECT * FROM events LIMIT 5" -F csv

# JSONL (one JSON object per line) — good for streaming/large results
bunx @obsessiondb/chcli -q "SELECT * FROM events LIMIT 100" -F jsonl

Available format aliases: json, jsonl/ndjson, jsoncompact, csv, tsv, pretty, vertical, markdown, sql. Any native ClickHouse format name also works.

See references/formats.md for the full format reference.

Common Workflows

Schema Discovery

# List all databases
bunx @obsessiondb/chcli -q "SHOW DATABASES" -F json

# List tables in current database
bunx @obsessiondb/chcli -q "SHOW TABLES" -F json

# List tables in a specific database
bunx @obsessiondb/chcli -q "SHOW TABLES FROM analytics" -F json

# Describe table schema
bunx @obsessiondb/chcli -q "DESCRIBE TABLE events" -F json

# Show CREATE TABLE statement
bunx @obsessiondb/chcli -q "SHOW CREATE TABLE events"

Data Exploration

# Row count
bunx @obsessiondb/chcli -q "SELECT count() FROM events" -F json

# Sample rows
bunx @obsessiondb/chcli -q "SELECT * FROM events LIMIT 10" -F json

# Column statistics
bunx @obsessiondb/chcli -q "SELECT uniq(user_id), min(created_at), max(created_at) FROM events" -F json

Data Extraction

# Extract to CSV file
bunx @obsessiondb/chcli -q "SELECT * FROM events WHERE date = '2024-01-01'" -F csv > export.csv

# Extract as JSON
bunx @obsessiondb/chcli -q "SELECT * FROM events LIMIT 1000" -F json > export.json

Additional Flags

Best Practices for Agents

1. Always specify -F json or -F csv — never rely on the default format, which varies by TTY context.
2. Always use LIMIT on SELECT queries unless you know the table is small. ClickHouse tables can contain billions of rows.
3. Start with schema discovery — run SHOW TABLES and DESCRIBE TABLE before querying unfamiliar databases.
4. Use -t for timing — helps gauge whether queries are efficient.
5. Prefer env vars for connection — set them once in .env or via a secrets manager like Doppler rather than repeating flags on every command.
6. Use count() first — before extracting data, check how many rows match to avoid overwhelming output.