ClickHouse Client

## Connection string[​](#connection_string "Direct link to Connection string")

### Usage[​](#connection-string-usage "Direct link to Usage")

ClickHouse Client alternatively supports connecting to a ClickHouse server using a connection string similar to [MongoDB](https://www.mongodb.com/docs/manual/reference/connection-string/), [PostgreSQL](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING), [MySQL](https://dev.mysql.com/doc/refman/8.0/en/connecting-using-uri-or-key-value-pairs.html#connecting-using-uri). It has the following syntax:

```
clickhouse:[//[user[:password]@][hosts_and_ports]][/database][?query_parameters]
```

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Component (all optional) Description Default|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `user` Database username. `default`| `password` Database user password. If `:` is specified and the password is blank, the client will prompt for the user's password. -|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `hosts_and_ports` List of hosts and optional ports `host[:port] [, host:[port]], ...`. `localhost:9000`| `database` Database name. `default`| `query_parameters` List of key-value pairs `param1=value1[,&param2=value2], ...`. For some parameters, no value is required. Parameter names and values are case-sensitive. - | | | | | | | | | | | | | | | | | |

### Notes[​](#connection-string-notes "Direct link to Notes")

If the username, password or database was specified in the connection string, it cannot be specified using `--user`, `--password` or `--database` (and vice versa).

The host component can either be a hostname or an IPv4 or IPv6 address.
IPv6 addresses should be in `[]`:

```
clickhouse://[2001:db8::1234]
```

Connection strings can contain multiple hosts.
ClickHouse Client will try to connect to these hosts in order (from left to right).
After the connection is established, no attempt to connect to the remaining hosts is made.

The connection string must be specified as the first argument of `clickHouse-client`.
The connection string can be combined with an arbitrary number of other [command-line options](#command-line-options) except `--host` and `--port`.

The following keys are allowed for `query_parameters`:

|  |  |  |  |
| --- | --- | --- | --- |
| Key Description|  |  | | --- | --- | | `secure` (or `s`) If specified, the client will connect to the server over a secure connection (TLS). See `--secure` in the [command-line options](#command-line-options). | | | |

**Percent encoding**

Non-US ASCII, spaces and special characters in the following parameters must be [percent-encoded](https://en.wikipedia.org/wiki/URL_encoding):

- `user`
- `password`
- `hosts`
- `database`
- `query parameters`

### Examples[​](#connection_string_examples "Direct link to Examples")

Connect to `localhost` on port 9000 and execute the query `SELECT 1`.

```
clickhouse-client clickhouse://localhost:9000 --query "SELECT 1"
```

Connect to `localhost` as user `john` with password `secret`, host `127.0.0.1` and port `9000`

```
clickhouse-client clickhouse://john:[email protected]:9000
```

Connect to `localhost` as the `default` user, host with IPV6 address `[::1]` and port `9000`.

```
clickhouse-client clickhouse://[::1]:9000
```

Connect to `localhost` on port 9000 in multiline mode.

```
clickhouse-client clickhouse://localhost:9000 '-m'
```

Connect to `localhost` using port 9000 as the user `default`.

```
clickhouse-client clickhouse://default@localhost:9000

# equivalent to:
clickhouse-client clickhouse://localhost:9000 --user default
```

Connect to `localhost` on port 9000 and default to the `my_database` database.

```
clickhouse-client clickhouse://localhost:9000/my_database

# equivalent to:
clickhouse-client clickhouse://localhost:9000 --database my_database
```

Connect to `localhost` on port 9000 and default to the `my_database` database specified in the connection string and a secure connection using the shorthanded `s` parameter.

```
clickhouse-client clickhouse://localhost/my_database?s

# equivalent to:
clickhouse-client clickhouse://localhost/my_database -s
```

Connect to the default host using the default port, the default user, and the default database.

```
clickhouse-client clickhouse:
```

Connect to the default host using the default port, as the user `my_user` and no password.

```
clickhouse-client clickhouse://my_user@

# Using a blank password between : and @ means to asking the user to enter the password before starting the connection.
clickhouse-client clickhouse://my_user:@
```

Connect to `localhost` using the email as the user name. `@` symbol is percent encoded to `%40`.

```
clickhouse-client clickhouse://some_user%40some_mail.com@localhost:9000
```

Connect to one of two hosts: `192.168.1.15`, `192.168.1.25`.

```
clickhouse-client clickhouse://192.168.1.15,192.168.1.25
```

## Query ID format[​](#query-id-format "Direct link to Query ID format")

In interactive mode ClickHouse Client shows the query ID for every query. By default, the ID is formatted like this:

```
Query id: 927f137d-00f1-4175-8914-0dd066365e96
```

A custom format may be specified in a configuration file inside a `query_id_formats` tag. The `{query_id}` placeholder in the format string is replaced with the query ID. Several format strings are allowed inside the tag.
This feature can be used to generate URLs to facilitate profiling of queries.

**Example**

```
<config>
  <query_id_formats>
    <speedscope>http://speedscope-host/#profileURL=qp%3Fid%3D{query_id}</speedscope>
  </query_id_formats>
</config>
```

With the configuration above, the ID of a query is shown in the following format:

```
speedscope:http://speedscope-host/#profileURL=qp%3Fid%3Dc8ecc783-e753-4b38-97f1-42cddfb98b7d
```

## Configuration files[​](#configuration_files "Direct link to Configuration files")

ClickHouse Client uses the first existing file of the following:

- A file that is defined with the `-c [ -C, --config, --config-file ]` parameter.
- `./clickhouse-client.[xml|yaml|yml]`
- `$XDG_CONFIG_HOME/clickhouse/config.[xml|yaml|yml]` (or `~/.config/clickhouse/config.[xml|yaml|yml]` if `XDG_CONFIG_HOME` is not set)
- `~/.clickhouse-client/config.[xml|yaml|yml]`
- `/etc/clickhouse-client/config.[xml|yaml|yml]`

See the sample configuration file in the ClickHouse repository: [`clickhouse-client.xml`](https://github.com/ClickHouse/ClickHouse/blob/master/programs/client/clickhouse-client.xml)

- XML- YAML

```
<config>
    <user>username</user>
    <password>password</password>
    <secure>true</secure>
    <openSSL>
      <client>
        <caConfig>/etc/ssl/cert.pem</caConfig>
      </client>
    </openSSL>
</config>
```

```
user: username
password: 'password'
secure: true
openSSL:
  client:
    caConfig: '/etc/ssl/cert.pem'
```

## Environment variable options[​](#environment-variable-options "Direct link to Environment variable options")

The user name, password and host can be set via environment variables `CLICKHOUSE_USER`, `CLICKHOUSE_PASSWORD` and `CLICKHOUSE_HOST`.
Command line arguments `--user`, `--password` or `--host`, or a [connection string](#connection_string) (if specified) take precedence over environment variables.

## Command-line options[​](#command-line-options "Direct link to Command-line options")

All command-line options can be specified directly on the command line or as defaults in the [configuration file](#configuration_files).

### General options[​](#command-line-options-general "Direct link to General options")