Connecting to a database in a cluster ClickHouse

Important

If there is no public access to the DB cluster, you can only connect to it from the Yandex.Cloud virtual machines running in the same cloud network.

A ClickHouse cluster can be accessed using the command-line client (port 9440) or HTTP interface (port 8443). All connections to DB clusters are encrypted.

Getting an SSL certificate

To use an encrypted connection, you should get an SSL certificate:

wget "https://storage.yandexcloud.net/cloud-certs/CA.pem"

How to connect via ClickHouse CLI

To connect to a cluster using the command-line client, specify the path to the SSL certificate in the configuration file in the <caConfig> element:

<config>
  <openSSL>
    <client>
      <loadDefaultCAFile>true</loadDefaultCAFile>
      <caConfig>[path to the SSL certificate]</caConfig>
      <cacheSessions>true</cacheSessions>
      <disableProtocols>sslv2,sslv3</disableProtocols>
      <preferServerCiphers>true</preferServerCiphers>
      <invalidCertificateHandler>
        <name>RejectCertificateHandler</name>
      </invalidCertificateHandler>
    </client>
  </openSSL>
</config>

Then run the ClickHouse CLI with the following parameters:

clickhouse-client --host <host FQDN> \
                  -s \
                  --user <DB user name> \
                  --password <DB user password> \
                  -q "<DB query>"
                  --port 9440

To view an example of the command with the host FQDN filled in, open the cluster page in the management console and click Connect.

How to connect via HTTP

Send a request specifying the path to the received SSL certificate, database attributes, and the request text in urlencoded format:

curl --cacert <path to the SSL certificate> \
     -H "X-ClickHouse-User: <DB user name>" \
     -H "X-ClickHouse-Key: <DB user password>" \
     'https://<host FQDN>:8443/?database=<DB name>&query=SELECT%20now ()'

When using the HTTP GET method, only read operations are allowed. A GET request for a write operation will always cause an error, like when using thereadonly=1 connection parameter. Always use the POST method for write operations:

curl -X POST \
     --cacert <path to the SSL certificate> \
     -H "X-ClickHouse-User: <DB user name>" \
     -H "X-ClickHouse-Key: <DB user password>" \
     'https://<host FQDN>:8443/?database=<DB name>&query=INSERT%20INTO%20Customers%20%28CustomerName%2C%20Address%29%20VALUES%20%28%27Example%20Exampleson%27%2C%20%27Moscow%2C%20Lva%20Tolstogo%2C%2016%27%29%3B'