Use the native ClickHouse command-line client — clickhouse-client
To connect to ADQM, you can use clickhouse-client — a standard ClickHouse command-line client that allows you to run SQL queries and view their results in your terminal application. Once ADQM is installed, clickhouse-client is available on each server of the ADQM cluster.
Run the following command to start working with clickhouse-client:
$ clickhouse-client
This command connects to the local ADQM server (localhost:9000
) as the default
user without a password.
$ clickhouse-client ClickHouse client version 22.8.4.7. Connecting to localhost:9000 as user default. Connected to ClickHouse server version 22.8.4 revision 54460. :)
To connect to a remote ADQM server, or to specify a username, password, and database other than the defaults, you can use the following parameters of the clickhouse-client
command:
-
--host, -h
— name of a host where an ADQM server is located; -
--port
— port to connect to; -
--user, -u
— username; -
--password
— password; -
--ask-password
— prompt for a password; -
--database, -d
— current database. Default value is the default database from ADQM configuration parameters (ADQMDB → Other → Default database).
You can use clickhouse-client in interactive and batch (non-interactive) mode.
To reproduce the examples below, first prepare the following tables:
-
test_table1
CREATE TABLE test_table1 (a Int32, b String, c Int32) ENGINE = MergeTree ORDER BY a;
INSERT INTO test_table1 VALUES (1, 'aaa', 100), (2, 'bbb', 200), (3, 'ccc', 300), (4, 'ddd', 400), (5, 'eee', 500);
-
test_table2
CREATE TABLE test_table2 (a Int32, b String, c Int32) ENGINE = MergeTree ORDER BY a;
INSERT INTO test_table2 VALUES (1, 'a', 0), (2, 'b', 0), (3, 'c', 0);
Interactive mode
After the clickhouse-client
command is executed, the terminal connects to the ADQM server and switches to the interactive mode — you can enter a query after the :)
prompt. To run a query, press Enter
(a semicolon is not necessary at the end of a query).
The clickhouse-client --multiline
command activates the multiline query input mode. In this case, to run a query, end it with a semicolon and press Enter
. If there is no semicolon at the end of the entered string, pressing Enter
prompts you to enter the next line of the query.
Below is an example of entering and executing a query in the interactive mode of the command-line client.
Query:
SELECT * FROM test_table1;
The output should be similar to:
SELECT * FROM test_table1 Query id: 917d68af-6989-409e-a30f-89a657487e22 --a---b-------c-- │ 1 │ aaa │ 100 │ │ 2 │ bbb │ 200 │ │ 3 │ ccc │ 300 │ │ 4 │ ddd │ 400 │ │ 5 │ eee │ 500 │ ----------------- 5 rows in set. Elapsed: 0.001 sec.
When processing a query, clickhouse-client displays:
-
Progress (it might not have time to be displayed for quick queries).
-
Formatted query after parsing.
-
Query ID.
-
Result in the specified format. In the interactive mode, the client uses the
PrettyCompact
format by default. -
Number of lines in the result and execution time.
To exit the interactive mode, press Ctrl+D
or type exit
, quit
, q
, or logout
instead of a query.
Batch mode
You can also use clickhouse-client in the batch (non-interactive) mode as follows:
-
Pass a query to the
--query
parameter of theclickhouse-client
command:$ clickhouse-client --query "SELECT * FROM test_table2"
The output can look like:
1 a 0 2 b 0 3 c 0
-
Send a query to stdin:
$ echo "SELECT * FROM test_table2" | clickhouse-client
The output can look like:
1 a 0 2 b 0 3 c 0
-
Use both the
--query
parameter and stdin. For example, for anINSERT
query, you can pass the beginning of the query to thequery
parameter and send data to be inserted to stdin:$ echo "(4,'d',4)" | clickhouse-client --query "INSERT INTO test_table2 VALUES" $ clickhouse-client --query "SELECT * FROM test_table2"
The output can look like:
1 a 0 2 b 0 3 c 0 4 d 4
By default, a single query only can be processed in the batch mode. To allow executing multiple queries, use the --multiquery
parameter (it works for all queries except INSERT
). In this case, the --query
parameter can accept a string that includes multiple queries separated by semicolons. For example:
$ clickhouse-client --multiquery --query "select * from test_table1;select * from test_table2;"
In the batch mode, the default format for displaying query results is TabSeparated. When running multiple queries with the --multiquery
option enabled, query results are output one after another without additional separators. The following section describes how to change the output format.
Query output format
You can specify the format to be applied to query results in the following ways:
-
Specify the desired format using the
FORMAT
clause in a query (the specified format will be applied to this query results only) or the--format
parameter in the command line (the format will be default for all queries). -
Enter
\G
at the end of a query, or use the--vertical
parameter in the command line to use the Vertical format for a single query or all queries by default, respectively. -
Specify the output format in the configuration file of clickhouse-client (/etc/clickhouse-client/config.xml).
Below is an example of changing the data output format for the batch mode:
$ clickhouse-client --multiquery --query "select * from test_table1;select * from test_table2;" --format PrettyCompact
The output should be similar to:
--a---b-------c-- │ 1 │ aaa │ 100 │ │ 2 │ bbb │ 200 │ │ 3 │ ccc │ 300 │ │ 4 │ ddd │ 400 │ │ 5 │ eee │ 500 │ ----------------- --a---b---c-- │ 1 │ a │ 0 │ │ 2 │ b │ 0 │ │ 3 │ c │ 0 │ │ 4 │ d │ 4 │ -------------
For more details on using clickhouse-client read the clickhouse-client article of the ClickHouse documentation.