Connect to a ClickHouse® cluster

You can connect to the hosts of DoubleCloud Managed Service for ClickHouse® via GUI IDEs, CLI software, or sample connection scripts for different languages. You can also connect to your cluster directly in your browser using the HTTPS connection.

Note

Note that it's only possible to connect to your cluster hosts using an SSL connection.

Connect with GUI-based IDEs

We tested the connections in the following environments:

DataGrip

  1. Open FileNewData SourceClickHouse®.

  2. In the General tab:

    1. Select Connection type - URL only

    2. Install the driver.

    3. In the Authentication drop-down menu, select User & Password.

    4. Copy the User and Password from the Overview tab on your cluster information page.

    5. Copy the JDBC connection string from the Overview tab on your cluster information page into the URL field.

  3. In the SSH/SSL tab:

    1. Check the Use SSL box.

    2. In the Mode drop-down menu, select Verify CA.

  4. Click Test connection. If the connection is successful, you will see a green icon.

  5. Click OK.

DBeaver

  1. Open DatabaseNew Database Connection.

  2. Select ClickHouse® from the list of database types.

  3. Click Next.

  4. On the Main tab:

    1. Host - paste the host's domain name (FQDN) from the Hosts tab on your cluster information page.

    2. Port - 8443 by default.

    3. Database/Schema - enter the name of the database to connect to.

  5. Copy Username and Password for your cluster from the Overview tab on your cluster information page.

  6. On the Driver Properties tab:

    1. Find ssl on the list of properties and change its value to true.

    2. Specify the socket_timeout. The recommended value is 300000 or higher. Enter a larger value if you are going to perform longer queries.

  7. Click Test Connection.... If the connection is successful, you will see a window with the connection status, information on the DBMS, and the driver.

  8. Click Finish.

Connect with CLI-based tools

We tested the connections in the following environments:

DoubleCloud Managed Service for ClickHouse® ports

You can connect to DoubleCloud Managed Service for ClickHouse® clusters via the following ports:

  • 9440 - the Native interface port, use it to connect with the clickhouse-client and other CLI tools.
  • 8443, 443 - TLS ports to send requests to the HTTP interface.
  • 9363 - the metrics port to connect Prometheus or other third-party solutions.

All the above ports are SSL-encrypted.

Bash

  1. Install and configure the software:

    1. Connect to the ClickHouse® official DEB repository from your Linux system:

      sudo apt update && sudo apt install -y apt-transport-https ca-certificates dirmngr && \
      sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754 && \
      echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee \
      /etc/apt/sources.list.d/clickhouse.list
      
    2. Refresh the package list and install the clickhouse-client :

      sudo apt update && sudo apt install -y clickhouse-client
      
  2. Connect to your ClickHouse® host. You can copy the value of the Native Interface field from the Overview tab on your cluster information page. It has the following structure:

    clickhouse-client --host <FQDN of the host> \
                      --secure \
                      --user <Cluster user name> \
                      --password <Cluster user password> \
                      --port 9440 
    
  1. Install and configure the software:

    1. Connect to a ClickHouse® official RPM repository from your Linux system:

      sudo yum install -y yum-utils
      sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
      
    2. Install the clickhouse-client :

      sudo yum install -y clickhouse-client
      

      Warning

      If you run a RedHat 7-based Linux distribution, including Cent OS 7, Oracle Linux 7 and others, you need to download and install trusted certificates, and manually add the path to them in the clickhouse-client configuration file as follows:

      1. Install the root certificate:

        curl https://letsencrypt.org/certs/isrg-root-x2-cross-signed.pem > \ 
        /etc/pki/ca-trust/source/anchors/isrg-root-x2-cross-signed.pem
        
      2. Install the intermediate certificate:

        curl https://letsencrypt.org/certs/lets-encrypt-r3-cross-signed.pem > \
        /etc/pki/ca-trust/source/anchors/lets-encrypt-r3-cross-signed.pem
        
      3. Update the list of trusted certificates:

        sudo update-ca-trust
        
      4. Locate your clickhouse-client configuration file (by default, you can find it at /etc/clickhouse-client/config.xml) and add the path to the certificates into the <openSSL> section:

        <client> <!-- Used for connection to server's secure tcp port -->
           <loadDefaultCAFile>true</loadDefaultCAFile>
           <cacheSessions>true</cacheSessions>
           <disableProtocols>sslv2,sslv3</disableProtocols>
           <preferServerCiphers>true</preferServerCiphers>
           <caConfig>/etc/ssl/certs/ca-bundle.crt</caConfig>
           <!-- Use for self-signed: <verificationMode>none</verificationMode> -->
           <invalidCertificateHandler>
           <!-- Use for self-signed: <name>AcceptCertificateHandler</name> -->
           <name>RejectCertificateHandler</name>
           </invalidCertificateHandler>
        </client>
        
  2. Connect to your ClickHouse® host. You can copy the value of the Native Interface field from the Overview tab on your cluster information page. It has the following structure:

    clickhouse-client --host <FQDN of the host> \
                      --secure \
                      --user <Cluster user name> \
                      --password <Cluster user password> \
                      --port 9440 
    

cURL

Type the following in your terminal:

curl -H "X-ClickHouse-User: <Cluster user name>" \
   -H "X-ClickHouse-Key: <Cluster user password>" \
'https://<FQDN of any ClickHouse® host in your cluster>:8443/?database=<Database name>&query=SELECT%20version()'

You will see the current ClickHouse® version in the output if your query is successful.

Docker

  1. (Optional) Start Docker if needed:

    service docker start
    
  2. Pull the clickhouse-client Docker image:

    docker pull clickhouse/clickhouse-client
    
  3. In a container, run a command that contains a connection string. Use the value of the Native Interface field from the Overview tab on your cluster information page as part of the Docker connection string. The command has the following structure:

    docker run --network host --rm -i -t clickhouse/clickhouse-client \
                      --host <FQDN of the host> \
                      --secure \
                      --user <Cluster user name> \
                      --password <Cluster user password> \
                      --port 9440 
    

Powershell

Type the following in your Powershell:

curl.exe \
-H "X-ClickHouse-User: <Cluster user name>" \
-H "X-ClickHouse-Key: <Cluster user password>" \
'https://<FQDN of any ClickHouse® host on your cluster>:8443/?database=<Database name>query=SELECT+version()'

You will see the current ClickHouse® version in the output if your query is successful.

Sample connection scripts

We tested the connections in the following environments:

  • Ubuntu 20.04 LTS:

C++

  1. Write the script to connect to your database:

    main.cpp

    #include <iostream>
    
    #include <clickhouse/client.h>
    
    int main() {
       clickhouse::Client client(clickhouse::ClientOptions()
                                     .SetHost("<FQDN of any ClickHouse® host on your cluster>")
                                     .SetPort(9440)
                                     .SetUser("<Cluster user name>")
                                     .SetPassword("<Cluster user password>")
                                     .SetSSLOptions(clickhouse::ClientOptions::SSLOptions())
       );
    
       client.Select("SELECT version();", [](const clickhouse::Block &block) {
          for (size_t i = 0; i < block.GetRowCount(); ++i) {
             std::cout << block[0]->As<clickhouse::ColumnString>()->At(i) << std::endl;
          }
       });
    }
    
  2. In the same folder, create a CMakeLists.txt file to compile your script:

    cmake_minimum_required(VERSION 3.16)
    project(junk)
    
    set(CMAKE_CXX_STANDARD 20)
    SET(WITH_OPENSSL ON)
    
    add_subdirectory(clickhouse-cpp)
    
    INCLUDE_DIRECTORIES (clickhouse-cpp)
    INCLUDE_DIRECTORIES (clickhouse-cpp/contrib)
    
    add_executable(junk main.cpp)
    target_link_libraries (junk pthread clickhouse-cpp-lib)
    
  3. Build your C++ environment, then compile your script:

    git clone https://github.com/ClickHouse/clickhouse-cpp \
    mkdir build && cd biuld \
    cmake ../ \
    make 
    
  4. Connect to your cluster:

    ./junk
    

If your script works, you'll see the current ClickHouse® version in the output.

C#

  1. Create a directory for your .NET project: ClickHouseTest.

  2. Go to the project directory, install the template and dependencies for your environment:

    cd ClickHouseTest \
    dotnet new console -o ClickHouseTest -f net6.0 \
    dotnet add ClickHouseTest package ClickHouse.Ado --version 1.4.3
    
  3. Write the script to connect to your database:

    Program.cs

    using ClickHouse.Ado;
    string cstr = "Encrypt=True;"+
                  "Host=<FQDN of any ClickHouse® host on your cluster>;"+
                  "Port=9440;"+
                  "Database=default;"+
                  "User=<Cluster user name>;"+
                  "Password=<Cluster user password>";
    var settings = new ClickHouseConnectionSettings(cstr);
    var conn = new ClickHouseConnection(settings);
    conn.Open();
    
    var cmd = conn.CreateCommand("SELECT version()");
    var reader = cmd.ExecuteReader();
    do {
         while (reader.Read()) {
                 Console.Write(reader.GetValue(0));
                 Console.WriteLine();
         }
    } while (reader.NextResult());
    
    conn.Close();
    
  4. Run your program:

    dotnet run
    

If your script works, you'll see the current ClickHouse® version in the output.

Go

  1. Install the software:

    1. Install the Go language interpreter:

      sudo apt update && sudo apt install -y golang-go
      
    2. Install Git:

      sudo apt update && sudo apt install -y git
      
    3. Install clickhouse-go with the necessary packages:

      go get -v -u github.com/ClickHouse/clickhouse-go/v2 \ 
                   github.com/google/uuid \
                   github.com/pierrec/lz4/v4 \ 
                   github.com/ClickHouse/clickhouse-go/lib/proto/query.go:25:2
      
  2. Write the script to connect to your database:

    dc_connect.go

    package main
    
    import (
       "database/sql"
       "fmt"
       "log"
    
       _ "github.com/ClickHouse/clickhouse-go/v2"
    )
    
    func main() {
       host := "<FQDN of any ClickHouse® host on your cluster>"
       user := "<Cluster user name>"
       password := "<Cluster user password>"
    
       dsn := fmt.Sprintf("clickhouse://%s:%s@%s:9440?username=%s&password=%s&secure=true", user, password, host)
       connect, err := sql.Open("clickhouse", dsn)
       if err != nil {
          log.Fatal(err)
       }
       if err := connect.Ping(); err != nil {
          panic(err)
       }
    
       rows, err := connect.Query("SELECT version()")
       if err != nil {
          panic(err)
       }
       defer rows.Close()
    
       for rows.Next() {
          var version string
          if err := rows.Scan(&version); err != nil {
             panic(err)
          }
          log.Printf("version: %s", version)
       }
    
       if err := rows.Err(); err != nil {
          panic(err)
       }
    }
    
  3. Connect to your cluster:

    go run dc_connect.go
    

If your query is successful, you'll see the current ClickHouse® version in the output.

Java

  1. Install maven and its dependencies:

    sudo apt update && sudo apt install -y maven
    
  2. Write the script to connect to your cluster:

    dc-connect/src/main/java/dc_connect.java

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    class clickhouse_example {
    
       public static void main(String[] args) throws SQLException{
          String url = "<JDBC connection string>";
    
          try (Connection conn = DriverManager.getConnection(url);
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery("select version()")) {
             while (rs.next()) {
                System.out.println(rs.getString(1));
             }
         }
       }
    }
    
  3. Add the following dependencies to your pom.xml configuration file:

    <dependencies>
       <dependency>
          <groupId>com.clickhouse</groupId>
          <artifactId>clickhouse-jdbc</artifactId>
          <version>0.3.2-patch7</version>
          <classifier>http</classifier>
          <exclusions>
             <exclusion>
                <groupId>*</groupId>
                <artifactId>*</artifactId>
             </exclusion>
          </exclusions>
       </dependency>
    </dependencies>
    
  4. Compile your code:

    mvn compile && mvn exec:java -Dexec.mainClass="<class name>"
    

If your script works, you'll see the current ClickHouse® version in the output.

Node.js

  1. Install the software:

    sudo apt update && sudo apt install -y nodejs npm && \
    npm install querystring
    
  2. Write a script to connect to your database:

    dc_connect.js

    "use strict"
    
    const http = require('https');
    const querystring = require('querystring');
    const fs = require('fs');
    
    const DB_HOST = "<FQDN of any ClickHouse® host on your cluster>";
    const DB_NAME = "<Database name>";
    const DB_USER = "<Cluster user name>";
    const DB_KEY = "<Ckuster user password>";
    
    const options = {
       'method': 'GET',
       'path': '/?' + querystring.stringify({
          'database': DB_NAME,
          'query': 'SELECT version()',
       }),
       'port': 8443,
       'hostname': DB_HOST,
       'headers': {
          'X-ClickHouse-User': DB_USER,
          'X-ClickHouse-Key': DB_KEY,
       },
    };
    
    const rs = http.request(options, (res) => {
       res.setEncoding('utf8');
       res.on('data', (chunk) => {
          console.log(chunk);
       });
    });
    
    rs.end();
    
  3. Connect to your cluster:

    node dc_connect.js
    

You'll see the current ClickHouse® version in the output if your query is successful.

PHP

  1. Install the software and dependencies:

    sudo apt update && sudo apt install -y php
    
  2. Write a script to connect to your database:

    dc_connect.php

    <?php
    $host = '<FQDN of any ClickHouse® host on your cluster>';
    $db = '<Database name>';
    $query = 'SELECT version()';
    
    $auth = [
             'X-ClickHouse-User: <Cluster user name>',
             'X-ClickHouse-Key: <Cluster user password>',
    ];
    
    $ssl = [
             'verify_peer' => true,
    ];
    
    $context = stream_context_create([
             'http' => [
                   'method' => 'GET',
                   'protocol_version' => 1.1,
                   'header' => $auth
             ],
    'ssl' => $ssl
    ]);
    
    $url = sprintf('https://%s:8443/?database=%s&query=%s', $host, $db, urlencode($query));
    
    $rs = file_get_contents($url, false, $context);
    print_r($rs);
    ?>
    
  3. Connect to your cluster:

    php dc_connect.php
    

If your query is successful, you'll see the current ClickHouse® version in the output.

Python

  1. Install the software and dependencies:

    sudo apt update && sudo apt install -y python3 python3-pip && pip3 install requests
    
  2. Write a script to connect to your database:

    dc_connect.py

    import requests
    
    url = 'https://{host}:8443/?database={db}&query={query}'.format(
    host='<FQDN of any ClickHouse® host on your cluster>',
    db='<Database name>',
    query='SELECT version()')
    
    auth = {
             'X-ClickHouse-User': '<Cluster user name>',
             'X-ClickHouse-Key': '<Cluster user password>',
          }
    
    rs = requests.get(url, headers=auth, verify=cacert)
    rs.raise_for_status()
    
    print(rs.text)
    
  3. Connect to your cluster:

    python3 dc_connect.py
    

If your query is successful, you'll see the current ClickHouse® version in the output.

Ruby

  1. Install the software:

    sudo apt update && sudo apt install -y ruby
    
  2. Write a script to connect to your database:

    dc_connect.rb

    require "net/http"
    require "openssl"
    require "uri"
    
    DB_HOST = "<FQDN of any ClickHouse® host on your cluster>"
    DB_NAME = "<Database name>"
    DB_USER = "<Cluster user name>"
    DB_PASS = "<Cluster user password>"
    
    QUERYSTRING = { :database => DB_NAME, :query => "SELECT version()" }
    
    uri = URI("https://" + DB_HOST + "/")
    uri.port = 8443
    uri.query = URI.encode_www_form(QUERYSTRING)
    
    req = Net::HTTP::Get.new(uri)
    req.add_field("X-ClickHouse-User", DB_USER)
    req.add_field("X-ClickHouse-Key", DB_PASS)
    
    conn = Net::HTTP.new(uri.host, uri.port)
    conn.use_ssl = true
    conn.verify_mode = OpenSSL::SSL::VERIFY_PEER
    
    rs = conn.request(req)
    puts rs.body
    
  3. Connect to your cluster:

    ruby dc_connect.rb
    

If your query is successful, you'll see the current ClickHouse® version in the output.

Connect using HTTPS

You can run a query console in your browser in cases you need to test your cluster's operation or content. Note that this method requires that you enter your cluster credentials in the browser. Despite the HTTPS protection, you should only use this method on your machine for security reasons.

  1. Go to the Clusters overview .

  2. Select your cluster from the list.

  3. On the Overview tab, find the Connection section.

  4. On the SQL console line, click Open.

    The console will open in a new tab.

1.To get access to the cluster, enter your username and password in the top right corner:

https-credentials

  1. Now you can send a query to test the connection. Let's start with the SHOW DATABASES query that will work on every cluster with default configuration. The response should look like this:

    connected-with-https

Select an available host automatically

If the current host is unavailable, you can use the following FQDN address line to connect to any available host on the cluster:

  • rw.<cluster id>.at.double.cloud to connect to one of the cluster's available hosts, where <cluster id> is CNAME of one of the available hosts.

  • <shard name>.<cluster id>.at.double.cloud to an Alive host in the specified shard.

Copy <cluster id> from the Id field in the Overview tab on your cluster information page or copy the HTTPS connection string from the Hosts tab.

Find <shard name> in the Hosts tab on your cluster information page.

If the host from the above address line becomes unavailable, you might encounter a short lag before the address leads to another available host.

Connect with public and private connection strings

When you connect to a cluster via a peering connection from VPC, you need to use a private address instead of the normally used public address.

To obtain a cluster's private connection string, go to the cluster overview page. Under Connection strings, switch to the Private tab:

connection strings tabs

You can also connect to a certain host on your cluster. The structures of a cluster and a host connection string differ as follows:

  • Public address:

    rw.<cluster id>.at.double.cloud
    # or 
    <host name>.<cluster id>.at.double.cloud
    
  • Private address:

    rw.<cluster id>.private.at.double.cloud
    # or 
    <host name>.<cluster id>.private.at.double.cloud