ClickHouse Client (clickhouse-client): The Native CLI Explained

What is clickhouse-client?

clickhouse-client is the official command-line client for ClickHouse, distributed as part of the clickhouse-common-static package. It connects to a ClickHouse server over the native TCP protocol on port 9000 (or 9440 when TLS is enabled) and supports interactive sessions, single-query execution, and script piping. The client streams results in any supported format, including TabSeparated, CSV, JSON, Pretty, and Native, and it is the preferred interface for benchmarks, bulk inserts, and administrative work because the native protocol is more efficient than HTTP.

How clickhouse-client Works

The binary clickhouse client (or the symlink clickhouse-client) opens a TCP connection to the server and exchanges columnar blocks of data, with optional LZ4 or ZSTD compression negotiated during the handshake. In interactive mode it reads from a readline-style prompt, supports multi-line queries terminated by ;, and writes per-row counts and elapsed time to stderr. In non-interactive mode it reads SQL from stdin or --queries-file and streams data through stdout, which makes it well suited for shell pipelines such as clickhouse-client --query="SELECT ..." | gzip > export.tsv.gz.

The client also fronts clickhouse-local when invoked as clickhouse local, allowing the same SQL surface against local files without a running server. That makes the same binary usable for both server queries and one-off file processing.

Key Flags and Options

A typical secure connection looks like:

# Connect to a remote ClickHouse over TLS with password prompt
clickhouse-client --secure --host clickhouse.example.com --port 9440 \
  --user analytics --password --database events

Common Pitfalls with clickhouse-client

1. Using HTTP-style URLs with --host - --host expects a hostname, not a URL. For HTTP endpoints use curl against port 8123 or --secure with the native port.
2. Forgetting --multiquery when running a script with multiple statements via --query. Without it, only the first statement is executed and subsequent ones are reported as a syntax error.
3. Pasting large INSERT ... VALUES payloads in interactive mode. Use INSERT ... FORMAT CSV (or another streaming format) with --queries-file or piped stdin to avoid client-side memory blowups.
4. Mixing clickhouse-client and clickhouse-local - they share a binary but talk to different things. clickhouse-local runs an embedded engine; it ignores --host.
5. Relying on default timeouts for long-running ETL. Override --receive_timeout, or you may hit a read timeout on multi-hour queries.

Operating clickhouse-client in Production

For automated workflows, prefer --queries-file with a static SQL file checked into version control. Capture exit codes - a non-zero exit signals an error such as memory limit exceeded, too many simultaneous connections, or a syntax issue. Use --format JSONEachRow for downstream tools that expect newline-delimited JSON; use Native when piping between ClickHouse servers because it preserves types and is the most compact format.

Operators monitoring a fleet of clusters often pair clickhouse-client with Pulse, which performs proactive monitoring and AI-driven root-cause analysis on ClickHouse - so when a script fails with a memory or quorum error, Pulse correlates the failed session with cluster-wide signals and surfaces the underlying cause without manual log digging.

Frequently Asked Questions

Q: What is the difference between clickhouse-client and clickhouse-local?
A: clickhouse-client talks to a remote (or local) ClickHouse server over TCP. clickhouse-local runs an embedded ClickHouse engine inside the CLI process to query files (CSV, Parquet, JSON) directly from disk without a server. Both ship in the same binary; the mode is selected by the subcommand.

Q: How do I run a SQL script file with clickhouse-client?
A: Use clickhouse-client --queries-file path/to/script.sql, or pipe via stdin: clickhouse-client < script.sql. For multiple statements passed inline, add --multiquery: clickhouse-client -n --query="CREATE TABLE ...; INSERT INTO ...".

Q: How do I export query results to a file with clickhouse-client?
A: Redirect stdout: clickhouse-client --query="SELECT * FROM events" --format CSVWithNames > events.csv. Use Native when re-importing into another ClickHouse server, and Parquet for downstream analytics tools.

Q: Can clickhouse-client connect over TLS?
A: Yes - pass --secure (or -s) and connect to the secure port (default 9440). Verify the server has TLS configured in config.xml under <tcp_port_secure>.

Q: How does clickhouse-client authenticate?
A: It uses the credentials configured in users.xml or the SQL access control system. Supported methods include plaintext password, SHA256 hash, double-SHA1 hash, LDAP, Kerberos, and SSL client certificate (via --secure plus a certificate configured server-side).

Q: Why does my INSERT through clickhouse-client fail with "Memory limit exceeded"?
A: Large INSERT VALUES blocks are parsed in memory before being committed. Use a streaming format (INSERT INTO t FORMAT CSV) read from stdin or a file, increase max_memory_usage, or split the insert into batches. See Memory limit exceeded.

Q: How do I change the output format in clickhouse-client?
A: Use --format (e.g., --format Pretty, --format JSONEachRow, --format CSVWithNames). In interactive mode you can also append FORMAT <name> to any query: SELECT * FROM t FORMAT JSON.

Related Reading

• ClickHouse Documentation Hub - index of all ClickHouse KB pages
• ClickHouse Memory Management - controlling per-query memory in scripts driven by clickhouse-client
• Memory Limit Exceeded - common error when piping large inserts
• Too Many Simultaneous Connections - hit when many clickhouse-client sessions open at once
• Read Timeout Error - when long queries hit receive_timeout
• ClickHouse Benchmark - sibling CLI for load testing