# Connect to Managed ClickHouse® with clickhouse-client

It's recommended to connect to a ClickHouse® cluster with the ClickHouse® client.

## Use the ClickHouse® client

To use the ClickHouse® client across different operating systems, we
recommend utilizing [Docker](https://www.docker.com/). You can get the
latest image of the ClickHouse server which contains the most recent
ClickHouse client directly from [the dedicated page in Docker
hub](https://hub.docker.com/r/clickhouse/clickhouse-server).

> [!NOTE]
> There are other installation options available for ClickHouse clients
> for different operating systems. See them in [ClickHouse
> local](https://clickhouse.com/docs/concepts/features/tools-and-utilities/clickhouse-local)
> and [Install ClickHouse](https://clickhouse.com/docs/get-started/setup/install) in the
> official ClickHouse documentation.

## Connection properties

You will need to know the following properties to establish a secure
connection with your Managed ClickHouse service: **Host**, **Port**,
**User** and **Password**.

Read the host and the port of the native protocol endpoint, the one the ClickHouse
client uses, from the service components. A service also exposes an HTTPS, a MySQL and
an Arrow Flight endpoint, each on its own port:

```bash
exo x get-dbaas-service-clickhouse my-clickhouse -z ch-gva-2 \
  -q "components[?component=='clickhouse']"
```

Rather than passing the password on the command line, where it lands in your shell
history and in the process list, read it into an environment variable. The `--raw` flag
returns the bare value, without JSON quotes or a trailing newline:

```bash
export CH_PASS=$(exo x reveal-dbaas-clickhouse-user-password my-clickhouse avnadmin \
  -z ch-gva-2 -q 'password' --raw)
```

Export the host and the native port the same way, so the examples below can reuse them:

```bash
export CH_HOST=$(exo x get-dbaas-service-clickhouse my-clickhouse -z ch-gva-2 \
  -q "components[?component=='clickhouse'] | [0].host" --raw)
export CH_PORT=$(exo x get-dbaas-service-clickhouse my-clickhouse -z ch-gva-2 \
  -q "components[?component=='clickhouse'] | [0].port" --raw)
```

## Command template

The command to connect to the service reuses the three variables exported above,
so nothing sensitive lands in your shell history.

### With a locally installed client

If `clickhouse-client` is installed on your machine, connect to the native TLS port
directly:

```bash
clickhouse-client \
--user avnadmin \
--password "$CH_PASS" \
--host "$CH_HOST" \
--port "$CH_PORT" \
--secure
```

The `--secure` flag is required: the native endpoint only accepts TLS connections.

### With Docker

Docker avoids installing anything and works the same way on every operating system:

```bash
docker run -it \
--rm clickhouse/clickhouse-server clickhouse-client \
--user avnadmin \
--password "$CH_PASS" \
--host "$CH_HOST" \
--port "$CH_PORT" \
--secure
```

This example includes the `-it` option (a combination of `--interactive`
and `--tty`) to take you inside the container and the `--rm` option to
automatically remove the container after exiting.

> [!TIP]
> Wrap the whole command in a shell function to avoid repeating it. Every example below
> then becomes a short `ch …` call:
>
> ```bash
> ch() { docker run --rm --interactive clickhouse/clickhouse-server clickhouse-client --host "$CH_HOST" --port "$CH_PORT" --user avnadmin --password "$CH_PASS" --secure "$@"; }
> ```
>
> With it, `ch --query "SHOW DATABASES"` runs a single query and `ch < queries.sql` runs a
> whole file of statements.

The other parameters, such as `--user`, `--password`, `--host`,
`--port`, `--secure`, and `--query` are arguments accepted by the
ClickHouse client. You can see the full list of command line options in
[the ClickHouse client
documentation](https://clickhouse.com/docs/interfaces/client#command-line-options).

> [!NOTE]
> If this first command hangs and then fails with
> `Code: 209. DB::NetException: Timeout: connect timed out … (SOCKET_TIMEOUT)`, your source
> address is not allowed by the service `ip-filter`. The filter drops the packets rather
> than rejecting them, hence a timeout rather than a connection refused. See
> [Connection times out](/product/dbaas/service-specific/clickhouse/quick-start/#connection-times-out).

Once you're connected to the server, you can type queries directly
within the client, for example, to see the list of existing databases,
run

```sql
SHOW DATABASES
```

> [!NOTE]
> The `clickhouse/clickhouse-server` image tracks the latest ClickHouse release, so its
> client is often newer than your service. When it is, every query prints a warning such
> as `Unknown settings: 'allow_non_default_profile', skipping`. The client sends a
> setting the server does not know, the server ignores it, and the query runs normally.
> Pull an image tag matching your service version to silence it.

Alternatively, sometimes you might want to run individual queries and be
able to access the command prompt outside the docker container. In this
case you can set `--interactive` and use `--query` parameter without
entering the docker container:

```bash
docker run --interactive            \
--rm clickhouse/clickhouse-server clickhouse-client \
--user avnadmin                     \
--password "$CH_PASS"               \
--host "$CH_HOST"                   \
--port "$CH_PORT"                   \
--secure                            \
--query="YOUR SQL QUERY GOES HERE"
```

Similar to above example, you can request the list of present databases
directly:

```bash
docker run --interactive            \
--rm clickhouse/clickhouse-server clickhouse-client \
--user avnadmin                     \
--password "$CH_PASS"               \
--host "$CH_HOST"                   \
--port "$CH_PORT"                   \
--secure                            \
--query="SHOW DATABASES"
```