Connect to a ClickHouse® cluster

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

You can only connect to your cluster hosts using an SSL connection.

Connect with WebSQL

WebSQL is a DoubleCloud service that allows you to connect to your Managed ClickHouse® clusters from your browser tab. It provides a full-fledged SQL editor that you can use to run SQL queries. Learn more about WebSQL

To connect to a Managed ClickHouse® cluster with WebSQL:

1. On the Clusters page in the console, select the Managed ClickHouse® cluster you want to connect to.

2. Click WebSQL at the top right:

   

3. After you've connected to a cluster, select a database in the connection manager on the left:

   

4. Enter a query in the query editor and click Execute:

   

Connect with IDEs

We tested the connections in the following environments:

• Windows 10 Enterprise 1909:

  • JetBrains DataGrip: 2021.2

  • DBeaver Community Edition: 21.2.0

DataGrip

To connect to a Managed ClickHouse® cluster with DataGrip, take the following steps:

1. Open File → New → Data Source → ClickHouse®.

2. In the General tab:

   1. In Connection type, select 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

To connect to a Managed ClickHouse® cluster with DBeaver, take the following steps:

1. Open Database → New 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 tools

We tested the connections in the following environments:

• Ubuntu 20.04 LTS:

  • Bash: 5.0.17

    • clickhouse-client (DEB): 18.16.1.
    • clickhouse-client (RPM): 18.16.1.

  • Docker: 20.10.12, build: e91ed57

• Windows 10 Enterprise 1909:

  • PowerShell: 5.1.18362

  • curl.exe: 7.55.1 WinSSL

Bash

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++: 9.4.0

  • C#: dotnet: 6.0.201

  • Go: 2:1.13

  • Java: 3.6.3-1 maven: 5.4.0

  • NodeJS: 10.19.0 npm: 6.14.4

  • PHP: 7.0

  • Python: 3.8.10, pip: 20.0.2

  • Ruby: 2.7

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.

Select an available host automatically

If the current host is unavailable, you can use the following URL 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. <cluster_id> is the CNAME value of one of the available hosts.

• <shard_name>.<cluster_id>.at.double.cloud to connect to an Alive host in a specific shard.

You can find <cluster_id> in the ID field in the Overview tab on your cluster information page or copy the HTTPS connection string from the Hosts tab.

You can 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:

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