ClickHouse - How to connect to a ClickHouse cluster

Objective

ClickHouse is an open-source, columnar analytical database system designed for real-time processing of massive data volumes, providing high performance, scalability, and low latency.

This guide explains how to connect to a ClickHouse cluster using the native CLI, HTTPS, and MySQL protocols.

Requirements

• Access to the OVHcloud Control Panel.
• A Public Cloud project in your OVHcloud account.
• A ClickHouse cluster running on OVHcloud Public Cloud and accepting incoming connections.

Instructions

Downloading server and user certificates

To connect to the ClickHouse service, you need server and user certificates.

• Server certificate: The server Certificate Authority (CA) certificate can be downloaded from the Dashboard tab.
• User certificate and access key: The user certificate and the user access key can be downloaded from the Users tab.

Connection methods

You can connect to a ClickHouse cluster using different protocols and ports:

Connecting using the ClickHouse native protocol (port 20184)

Connect using the official clickhouse-client CLI. For installation details, see the official ClickHouse Client documentation.

clickhouse-client --user avnadmin \
                  --password <password> \
                  --host clickhouse-01234567-89abcdef.database.cloud.ovh.net \
                  --port 20184 \
                  --secure

Replace <password> with your actual password and the hostname with the one from your cluster dashboard.

ClickHouse Client configuration file

You can configure the ClickHouse Client using an XML or YAML file to simplify connection. The client searches for configuration files in the following order:

1. A file specified with -c / --config / --config-file.
2. ./clickhouse-client.[xml|yaml|yml].
3. $XDG_CONFIG_HOME/clickhouse/config.[xml|yaml|yml] (or ~/.config/clickhouse/config.[xml|yaml|yml] if XDG_CONFIG_HOME is not set).
4. ~/.clickhouse-client/config.[xml|yaml|yml].
5. /etc/clickhouse-client/config.[xml|yaml|yml].

<config>
    <user>avnadmin</user>
    <password>your_password</password>
    <secure>true</secure>

    <openSSL>
        <client>
            <caConfig>/home/user/clickhouse/ca.pem</caConfig>
        </client>
    </openSSL>

    <connections_credentials>
        <connection>
            <name>default</name>
            <hostname>clickhouse-01234567-89abcdef.database.cloud.ovh.net</hostname>
            <port>20184</port>
        </connection>
    </connections_credentials>
</config>

user: avnadmin
password: 'your_password'
secure: true
openSSL:
  client:
    caConfig: '/home/user/clickhouse/ca.pem'

Change these values according to your own cluster configuration.

Inserting data into ClickHouse using the ClickHouse native CLI

For this first example, let's insert a test row into the my_table table in the test_db database.

clickhouse-client --query "INSERT INTO test_db.my_table (id, message) VALUES (1, 'test-message-content')"

Querying data from ClickHouse using the ClickHouse native CLI

Retrieve all the data from the my_table table in the test_db database:

clickhouse-client --query "SELECT * FROM test_db.my_table"

Connecting using HTTPS (port 20185)

Connect using curl or any HTTP client over HTTPS:

curl https://clickhouse-01234567-89abcdef.database.cloud.ovh.net:20185 \
  --user avnadmin:<password> \
  -d "SELECT 1"

Replace <password> with your actual password and the hostname with the one from your cluster dashboard.

Connecting using the MySQL protocol (port 20186)

Connect using the standard mysql client over the MySQL-compatible interface:

mysql --host=clickhouse-01234567-89abcdef.database.cloud.ovh.net \
      --port=20186 \
      --user=avnadmin \
      --password=<password> \
      --ssl-mode=REQUIRED

Replace <password> with your actual password and the hostname with the one from your cluster dashboard.

Go further

We would love to help answer questions and appreciate any feedback you may have.

If you need training or technical assistance to implement our solutions, contact your sales representative or click on this link to get a quote and ask our Professional Services experts for a custom analysis of your project.

Are you on Discord? Connect to our channel at https://discord.gg/ovhcloud and interact directly with the team that builds our Analytics service!

Join our community of users.