‘clickhouse-client’ is ClickHouse's built-in command-line interface (CLI) used to run SQL queries directly on a ClickHouse server. It works in two modes:
· Interactive mode: for real-time querying with immediate results
· Batch mode: for running scripts or automating tasks
You can view query results directly in the terminal or export them to a file. The client supports various output formats, such as Pretty, CSV, JSON, and others.
It also offers real-time feedback while queries are running, including a progress bar, row count, data volume processed, and total execution time. You can customize its behavior using both command-line flags and configuration files.
1. Steps to Install and Use clickhouse-client
To download ClickHouse, run the following command:
curl https://clickhouse.com/ | sh
In previous posts, we started the ClickHouse server and client using:
./clickhouse server ./clickhouse client
However, to fully install the ClickHouse binaries (including clickhouse-client and clickhouse-server) into your system paths, run:
sudo ./clickhouse install
$ sudo ./clickhouse install Password: Copying ClickHouse binary to /usr/local/bin/clickhouse.new Renaming /usr/local/bin/clickhouse.new to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-server to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-client to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-local to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-benchmark to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-obfuscator to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-git-import to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-compressor to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-format to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-extract-from-config to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-keeper to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-keeper-converter to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/clickhouse-disks to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/ch to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/chl to /usr/local/bin/clickhouse. Creating symlink /usr/local/bin/chc to /usr/local/bin/clickhouse. Will not create a dedicated clickhouse group. Will not create a dedicated clickhouse user. Creating config directory /etc/clickhouse-server. Creating config directory /etc/clickhouse-server/config.d that is used for tweaks of main server configuration. Creating config directory /etc/clickhouse-server/users.d that is used for tweaks of users configuration. Data path configuration override is saved to file /etc/clickhouse-server/config.d/data-paths.xml. Log path configuration override is saved to file /etc/clickhouse-server/config.d/logger.xml. User directory path configuration override is saved to file /etc/clickhouse-server/config.d/user-directories.xml. OpenSSL path configuration override is saved to file /etc/clickhouse-server/config.d/openssl.xml. Creating log directory /var/log/clickhouse-server. Creating data directory /var/lib/clickhouse. Creating pid directory /var/run/clickhouse-server. Set up the password for the default user: Password for the default user is saved in file /etc/clickhouse-server/users.d/default-password.xml. Allow server to accept connections from the network (default is localhost only), [y/N]: y The choice is saved in file /etc/clickhouse-server/config.d/listen.xml. ClickHouse has been successfully installed. Start clickhouse-server with: sudo clickhouse start Start clickhouse-client with: clickhouse-client --password
While installing the Clickhouse, it prompt you for the password, set one, it is used while connecting to the server.
1.1 Start the Clickhouse server by executing below command.
clickhouse server
1.2 Connect to the server by executing below command.
clickhouse-client --host 127.0.0.1 --port 9000 --user default
$ clickhouse-client --host 127.0.0.1 --port 9000 --user default ClickHouse client version 25.5.1.1919 (official build). Connecting to 127.0.0.1:9000 as user default. Connected to ClickHouse server version 25.5.1. Warnings: * Maximum number of threads is lower than 30000. There could be problems with handling a lot of simultaneous queries.
Execute the statement SHOW DATABASES; to list all the databases.
krishna :) SHOW DATABASES; SHOW DATABASES Query id: 52b6ee1d-11c1-487a-90f0-554b8abb17a5 ┌─name───────────────┐ 1. │ INFORMATION_SCHEMA │ 2. │ default │ 3. │ information_schema │ 4. │ system │ └────────────────────┘ 4 rows in set. Elapsed: 0.002 sec.
2. How to write multiline queries in clickhouse-client
You can write multi-line queries in clickhouse-client by using the backslash (\) character at the end of each line. This allows the shell to treat the entire query as a single logical line, which is especially useful when writing queries directly in the terminal or in shell scripts.
clickhouse-client --query "SELECT * FROM \ system.aggregate_function_combinators \ LIMIT 100;"
:) SELECT * FROM \ :-] "system"."aggregate_function_combinators" \ :-] LIMIT 100; SELECT * FROM system.aggregate_function_combinators LIMIT 100 Query id: 5cbb2f2f-8fc1-4854-ab4d-56d2d566bed4 ┌─name────────┬─is_internal─┐ 1. │ SimpleState │ 0 │ 2. │ OrDefault │ 0 │ 3. │ Distinct │ 0 │ 4. │ Resample │ 0 │ 5. │ ForEach │ 0 │ 6. │ ArgMax │ 0 │ 7. │ ArgMin │ 0 │ 8. │ OrNull │ 0 │ 9. │ Merge │ 0 │ 10. │ State │ 0 │ 11. │ Array │ 0 │ 12. │ Null │ 1 │ 13. │ Map │ 0 │ 14. │ If │ 0 │ └─────────────┴─────────────┘ 14 rows in set. Elapsed: 0.002 sec.
If you prefer writing multi-line queries in a clean and readable format without using backslashes (\), you can start the ClickHouse client with the --multiline option.
clickhouse-client --host 127.0.0.1 --port 9000 --user default --multiline
This enables an interactive mode where you can write SQL queries across multiple lines, like this.
SELECT * FROM "system"."aggregate_function_combinators" LIMIT 100;
krishna :) SELECT * FROM "system"."aggregate_function_combinators" LIMIT 100; SELECT * FROM system.aggregate_function_combinators LIMIT 100 Query id: 859005ec-9182-45e5-9bc5-02c791835c06 ┌─name────────┬─is_internal─┐ 1. │ SimpleState │ 0 │ 2. │ OrDefault │ 0 │ 3. │ Distinct │ 0 │ 4. │ Resample │ 0 │ 5. │ ForEach │ 0 │ 6. │ ArgMax │ 0 │ 7. │ ArgMin │ 0 │ 8. │ OrNull │ 0 │ 9. │ Merge │ 0 │ 10. │ State │ 0 │ 11. │ Array │ 0 │ 12. │ Null │ 1 │ 13. │ Map │ 0 │ 14. │ If │ 0 │ └─────────────┴─────────────┘ 14 rows in set. Elapsed: 0.002 sec.
3. Execute the query in non-interactive mode
To execute a query in non-interactive mode, use the --query option with clickhouse-client. This allows you to run a SQL statement directly from the command line without entering the interactive shell.
clickhouse-client --query "SELECT * FROM system.aggregate_function_combinators LIMIT 100;"
$ clickhouse-client --query "SELECT * FROM "system"."aggregate_function_combinators" LIMIT 100;" SimpleState 0 OrDefault 0 Distinct 0 Resample 0 ForEach 0 ArgMax 0 ArgMin 0 OrNull 0 Merge 0 State 0 Array 0 Null 1 Map 0 If 0
4. Specify output format using --format option
You can use the --format option with clickhouse-client to specify the output format of your query results. This is especially useful when you need the results in a structured format for processing, integration, or readability.
ClickHouse supports a variety of output formats such as CSV, JSON, XML, TabSeparated, Pretty, and more.
Output in CSV format
clickhouse-client --query "SELECT * FROM system.aggregate_function_combinators LIMIT 100;" --format "CSV"
Output in XML format
clickhouse-client --query "SELECT * FROM system.aggregate_function_combinators LIMIT 100;" --format "XML"
Output in JSON format
clickhouse-client --query "SELECT * FROM system.aggregate_function_combinators LIMIT 100;" --format "JSON"
Note
Although different versions of the client and server are generally compatible, using mismatched versions may limit access to newer features. It's recommended to use the same version for both the client and the server.
Previous Next Home
No comments:
Post a Comment