Use the native ClickHouse command-line client — clickhouse-client

Elena Dvoryadkina

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.

Create tables to run the examples below

 
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 the clickhouse-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 an INSERT query, you can pass the beginning of the query to the query 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.

Found a mistake? Seleсt text and press Ctrl+Enter to report it