Code examples for connecting to a ClickHouse® cluster

Written by

Updated at June 11, 2024

Go
Java
Node.js
ODBC
- Linux
- Windows
PHP
Python (clickhouse-driver)
Python (requests)
Ruby

You can connect to public ClickHouse® cluster hosts only if you use SSL certificates. The examples below assume that RootCA.crt and IntermediateCA.crt certificates are:

Located in the /usr/local/share/ca-certificates/Yandex/ folder (for Ubuntu).
Imported to the trusted root certificate store (for Windows).

Connecting without SSL certificates is only supported for non-public hosts. For connections to the database, traffic inside the virtual network is not encrypted in this case.

Before connecting, configure security groups for the cluster, if required.

If the connection to the cluster and the test query are successful, the ClickHouse® version is output.

To see code examples with the host FQDN filled in, open the cluster page in the management console and click Connect.

The examples for Linux were tested in the following environment:

Yandex Cloud virtual machine running Ubuntu 20.04 LTS
Bash: 5.0.16
Python: 3.8.2; pip3: 20.0.2
PHP: 7.4.3
OpenJDK: 11.0.8; Maven: 3.6.3
Node.JS: 10.19.0, npm: 6.14.4
Go: 1.13.8
Ruby: 2.7.0p0
unixODBC: 2.3.6

The examples for Windows were tested in the following environment:

A local machine with Windows 10 Pro build 19042.1052
PowerShell: 5.1.19041

Go

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes golang git

Connecting without SSL

Connecting via SSL

connect.go

package main

import (
    "fmt"
    "net/http"
    "io/ioutil"
)

func main() {

    const DB_HOST = "<FQDN_of_any_ClickHouse®_host>"
    const DB_NAME = "<DB_name>"
    const DB_USER = "<DB_user_name>"
    const DB_PASS = "<DB_user_password>"

    conn := &http.Client{
        Transport: &http.Transport{},
    }

    req, _ := http.NewRequest("GET", fmt.Sprintf("http://%s:8123/", DB_HOST), nil)
    query := req.URL.Query()
    query.Add("database", DB_NAME)
    query.Add("query", "SELECT version()")

    req.URL.RawQuery = query.Encode()

    req.Header.Add("X-ClickHouse-User", DB_USER)
    req.Header.Add("X-ClickHouse-Key", DB_PASS)

    resp, err := conn.Do(req)
    if err != nil {
        panic(err)
    }

    defer resp.Body.Close()

    data, _ := ioutil.ReadAll(resp.Body)
    fmt.Println(string(data))
}

connect.go

package main

import (
    "fmt"
    "net/http"
    "io/ioutil"
    "crypto/x509"
    "crypto/tls"
)

func main() {

    const DB_HOST = "<FQDN_of_any_ClickHouse®_host>"
    const DB_NAME = "<DB_name>"
    const DB_USER = "<DB_user_name>"
    const DB_PASS = "<DB_user_password>"

    const CACERT = "/usr/local/share/ca-certificates/Yandex/RootCA.crt"

    caCert, err := ioutil.ReadFile(CACERT)
    if err != nil {
        panic(err)
    }
    caCertPool := x509.NewCertPool()
    caCertPool.AppendCertsFromPEM(caCert)
    conn := &http.Client{
        Transport: &http.Transport{
        TLSClientConfig: &tls.Config{
            RootCAs: caCertPool,
        },
    },
    }

    req, _ := http.NewRequest("GET", fmt.Sprintf("https://%s:8443/", DB_HOST), nil)
    query := req.URL.Query()
    query.Add("database", DB_NAME)
    query.Add("query", "SELECT version()")

    req.URL.RawQuery = query.Encode()

    req.Header.Add("X-ClickHouse-User", DB_USER)
    req.Header.Add("X-ClickHouse-Key", DB_PASS)

    resp, err := conn.Do(req)
    if err != nil {
        panic(err)
    }

    defer resp.Body.Close()

    data, _ := ioutil.ReadAll(resp.Body)
    fmt.Println(string(data))
}

To learn how to get a host FQDN, see this guide.

Connection:

go run connect.go

Java

Before connecting:

Install the dependencies:

sudo apt update && sudo apt install --yes default-jdk maven

Create a folder for the Maven project:

cd ~/ && mkdir --parents project/src/java/com/example && cd project/

Create a configuration file for Maven:

pom.xml

<?xml version="1.0" encoding="utf-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>
  <groupId>com.example</groupId>
  <artifactId>app</artifactId>
  <packaging>jar</packaging>
  <version>0.1.0</version>
  <properties>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>
  <dependencies>
    <dependency>
      <groupId>com.clickhouse</groupId>
      <artifactId>clickhouse-jdbc</artifactId>
      <version>0.2.4</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-simple</artifactId>
      <version>1.7.30</version>
    </dependency>
  </dependencies>
  <build>
    <finalName>${project.artifactId}-${project.version}</finalName>
    <sourceDirectory>src</sourceDirectory>
    <resources>
      <resource>
        <directory>src</directory>
      </resource>
    </resources>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-assembly-plugin</artifactId>
        <executions>
          <execution>
            <goals>
              <goal>attached</goal>
            </goals>
            <phase>package</phase>
            <configuration>
              <descriptorRefs>
                <descriptorRef>
                jar-with-dependencies</descriptorRef>
              </descriptorRefs>
              <archive>
                <manifest>
                  <mainClass>com.example.App</mainClass>
                </manifest>
              </archive>
            </configuration>
          </execution>
        </executions>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-jar-plugin</artifactId>
        <version>3.1.0</version>
        <configuration>
          <archive>
            <manifest>
              <mainClass>com.example.App</mainClass>
            </manifest>
          </archive>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

Up-to-date versions of dependencies for Maven:

Connecting without SSL

Connecting via SSL

src/java/com/example/App.java

package com.example;

import java.sql.*;

public class App {
  public static void main(String[] args) {
    String DB_HOST    = "<FQDN_of_any_ClickHouse®_host>";
    String DB_NAME    = "<DB_name>";
    String DB_USER    = "<DB_user_name>";
    String DB_PASS    = "<DB_user_password>";

    String DB_URL = String.format("jdbc:clickhouse://%s:8123/%s", DB_HOST, DB_NAME);

    try {
        Class.forName("com.clickhouse.jdbc.ClickHouseDriver");

        Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
        ResultSet rs = conn.createStatement().executeQuery("SELECT version()");
        if(rs.next()) {System.out.println(rs.getString(1));}

        conn.close();
    }
    catch(Exception ex) {ex.printStackTrace();}
  }
}

src/java/com/example/App.java

package com.example;

import java.sql.*;

public class App {
  public static void main(String[] args) {
    String DB_HOST    = "<FQDN_of_any_ClickHouse®_host>";
    String DB_NAME    = "<DB_name>";
    String DB_USER    = "<DB_user_name>";
    String DB_PASS    = "<DB_user_password>";

    String CACERT     = "/usr/local/share/ca-certificates/Yandex/RootCA.crt";

    String DB_URL = String.format("jdbc:clickhouse://%s:8443/%s?ssl=1&sslmode=strict&sslrootcert=%s", DB_HOST, DB_NAME, CACERT);

    try {
        Class.forName("com.clickhouse.jdbc.ClickHouseDriver");
        Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
        ResultSet rs = conn.createStatement().executeQuery("SELECT version()");
        if(rs.next()) {System.out.println(rs.getString(1));}

        conn.close();
    }
    catch(Exception ex) {ex.printStackTrace();}
  }
}

To learn how to get a host FQDN, see this guide.

Connection:

mvn clean package && \
java -jar target/app-0.1.0-jar-with-dependencies.jar

Node.js

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes nodejs npm && \
npm install querystring

Connecting without SSL

Connecting via SSL

app.js

"use strict"
const http = require('http');
const querystring = require('querystring');
const fs = require('fs');

const DB_HOST = "<FQDN_of_any_ClickHouse®_host>";
const DB_NAME = "<DB_name>";
const DB_USER = "<DB_user_name>";
const DB_PASS = "<DB_user_password>";

const options = {
    'method': 'GET',
    'path': '/?' + querystring.stringify({
        'database': DB_NAME,
        'query': 'SELECT version()',
    }),
    'port': 8123,
    'hostname': DB_HOST,
    'headers': {
        'X-ClickHouse-User': DB_USER,
        'X-ClickHouse-Key': DB_PASS,
    },
};

const rs = http.request(options, (res) => {
    res.setEncoding('utf8');
    res.on('data', (chunk) => {
        console.log(chunk);
    });
});

rs.end();

app.js

"use strict"
const https = require('https');
const querystring = require('querystring');
const fs = require('fs');

const DB_HOST = "<FQDN_of_any_ClickHouse®_host>";
const DB_NAME = "<DB_name>";
const DB_USER = "<DB_user_name>";
const DB_PASS = "<DB_user_password>";

const CACERT = "/usr/local/share/ca-certificates/Yandex/RootCA.crt";

const options = {
    'method': 'GET',
    'ca': fs.readFileSync(CACERT),
    'path': '/?' + querystring.stringify({
        'database': DB_NAME,
        'query': 'SELECT version()',
    }),
    'port': 8443,
    'hostname': DB_HOST,
    'headers': {
        'X-ClickHouse-User': DB_USER,
        'X-ClickHouse-Key': DB_PASS,
    },
};

const rs = https.request(options, (res) => {
    res.setEncoding('utf8');
    res.on('data', (chunk) => {
        console.log(chunk);
    });
});

rs.end();

To learn how to get a host FQDN, see this guide.

Connecting:

node app.js

ODBC

Setup methods for Linux and Windows are different.

Linux

Before connecting:

Install the dependencies:

sudo apt update && sudo apt install --yes git unixodbc build-essential cmake \
libpoco-dev libssl-dev libicu-dev unixodbc-dev && \
cd ~/ && git clone https://github.com/ClickHouse/clickhouse-odbc.git && \
cd clickhouse-odbc/ && git submodule update --init

Build the driver from the source files:

cd ~/clickhouse-odbc/ && mkdir build && cd build && \
cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo .. && \
cmake --build . --config RelWithDebInfo

After the build process is complete, copy the driver files to the /usr/local/lib64/ directory:
```
sudo mkdir --parents /usr/local/lib64 && sudo cp driver/*.so /usr/local/lib64/
```

/etc/odbcinst.ini

[ODBC Drivers]
ClickHouse ODBC Driver (ANSI)    = Installed
ClickHouse ODBC Driver (Unicode) = Installed

[ClickHouse ODBC Driver (ANSI)]
Description = ODBC Driver (ANSI) for ClickHouse
Driver      = /usr/local/lib64/libclickhouseodbc.so
Setup       = /usr/local/lib64/libclickhouseodbc.so
UsageCount  = 1

[ClickHouse ODBC Driver (Unicode)]
Description = ODBC Driver (Unicode) for ClickHouse
Driver      = /usr/local/lib64/libclickhouseodbcw.so
Setup       = /usr/local/lib64/libclickhouseodbcw.so
UsageCount  = 1

Connecting without SSL

Connecting via SSL

/etc/odbc.ini

[ClickHouse]
Driver = ClickHouse ODBC Driver (Unicode)
Server = <FQDN_of_any_ClickHouse®_host>
Database = <DB_name>
UID = <DB_user_name>
PWD = <DB_user_password>
Port = 8123
Proto = http

/etc/odbc.ini

[ClickHouse]
Driver = ClickHouse ODBC Driver (Unicode)
Server = <FQDN_of_any_ClickHouse®_host>
Database = <DB_name>
UID = <DB_user_name>
PWD = <DB_user_password>
Port = 8443
Proto = https
SSLMode = allow
CertificateFile = /usr/local/share/ca-certificates/Yandex/RootCA.crt
CALocation = /etc/ssl/certs/ca-certificates.crt

To learn how to get a host FQDN, see this guide.

Connection:

isql -v ClickHouse

Once connected to the DBMS, run SELECT version();.

Windows

Install the clickhouse-odbc driver with the right bit depth. For example, if you are using a 32-bit application, you need a driver with the same bit depth to connect through ODBC.
Run the "ODBC Data Source Administrator".
In the User DSN tab, click Add...
Select the ClickHouse® driver with suitable encoding and click Done.
Specify the parameters for connecting to the ClickHouse® cluster:
Connecting without SSL

Connecting via SSL
- Name: Name for the connection
- Host: FQDN of any ClickHouse® host
- Port: 8443
- Database: DB name
- User: DB user name
- Password: DB user password
- Name: Name for the connection
- Host: FQDN of any ClickHouse® host
- Port: 8443
- Database: DB name
- SSLMode: Allow
- User: DB user name
- Password: DB user password
Click OK.
Connect to the ClickHouse® cluster through ODBC, for example, using Microsoft Excel.

PHP

Before connecting:

Install the dependencies:

sudo apt update && sudo apt install --yes php

Make sure that the allow_url_fopen parameter is set to On in the PHP settings:

cat /etc/php/7.4/apache2/php.ini | grep "allow_url_fopen"

If it is not the case, set the required value and restart Apache:

sudo sed -i 's/\(^allow_url_fopen = \).*/\1On/' /etc/php/7.4/apache2/php.ini && \
sudo systemctl restart apache2

Connecting without SSL

Connecting via SSL

connect.php

<?php
    $host = '<FQDN_of_any_ClickHouse®_host>';
    $db = '<DB_name>';
    $query = 'SELECT version()';

    $auth = [
        'X-ClickHouse-User: <DB_user_name>',
        'X-ClickHouse-Key: <DB_user_password>',
    ];

    $context = stream_context_create([
        'http' => [
            'method' => 'GET',
            'protocol_version' => 1.1,
            'header' => $auth
        ]
    ]);

    $url = sprintf('http://%s:8123/?database=%s&query=%s', $host, $db, urlencode($query));

    $rs = file_get_contents($url, false, $context);
    print_r($rs);
?>

connect.php

<?php
    $host = '<FQDN_of_any_ClickHouse®_host>';
    $db = '<DB_name>';
    $query = 'SELECT version()';

    $auth = [
        'X-ClickHouse-User: <DB_user_name>',
        'X-ClickHouse-Key: <DB_user_password>',
    ];

    $ssl = [
        'cafile' => '/usr/local/share/ca-certificates/Yandex/RootCA.crt',
        '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);
?>

To learn how to get a host FQDN, see this guide.

Connecting:

php connect.php

Python (clickhouse-driver)

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes python3 python3-pip && \
pip3 install clickhouse-driver

Connecting without SSL

Connecting via SSL

connect.py

from clickhouse_driver import Client

client = Client(host='<FQDN_of_any_ClickHouse®_host>',
                user='<DB_user_name>',
                password='<DB_user_password>',
                port=9000)

print(client.execute('SELECT version()'))

connect.py

from clickhouse_driver import Client

client = Client(host='<FQDN_of_any_ClickHouse®_host>',
                user='<DB_user_name>',
                password='<DB_user_password>',
                port=9440,
                secure=True,
                verify=True,
                ca_certs='/usr/local/share/ca-certificates/Yandex/RootCA.crt')

print(client.execute('SELECT version()'))

To learn how to get a host FQDN, see this guide.

Connection:

python3 connect.py

Python (requests)

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes python3 python3-pip && \
pip3 install requests

Connecting without SSL

Connecting via SSL

connect.py

import requests

response = requests.get(
    'http://{0}:8123'.format('<FQDN_of_any_ClickHouse®_host>'),
    params={
        'query': 'SELECT version()',
    },
    headers={
        'X-ClickHouse-User': '<DB_user_name>',
        'X-ClickHouse-Key': '<DB_user_password>',
    })

response.raise_for_status()
print(response.text)

connect.py

import requests

response = requests.get(
    'https://{0}:8443'.format('<FQDN_of_any_ClickHouse®_host>'),
    params={
        'query': 'SELECT version()',
    },
    verify='/usr/local/share/ca-certificates/Yandex/RootCA.crt',
    headers={
        'X-ClickHouse-User': '<DB_user_name>',
        'X-ClickHouse-Key': '<DB_user_password>',
    })

response.raise_for_status()
print(response.text)

To learn how to get a host FQDN, see this guide.

Connection:

python3 connect.py

Ruby

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes ruby

Connecting without SSL

Connecting via SSL

connect.rb

require "net/http"
require "uri"

DB_HOST = "<FQDN_of_any_ClickHouse®_host>"
DB_NAME = "<DB_name>"
DB_USER = "<DB_user_name>"
DB_PASS = "<DB_user_password>"

QUERYSTRING = { :database => DB_NAME, :query => "SELECT version()" }

uri = URI("http://" + DB_HOST + "/")
uri.port = 8123
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)

rs = conn.request(req)
puts rs.body

connect.rb

require "net/http"
require "openssl"
require "uri"

DB_HOST = "<FQDN_of_any_ClickHouse®_host>"
DB_NAME = "<DB_name>"
DB_USER = "<DB_user_name>"
DB_PASS = "<DB_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.ca_file = "/usr/local/share/ca-certificates/Yandex/RootCA.crt"
conn.use_ssl = true
conn.verify_mode = OpenSSL::SSL::VERIFY_PEER

rs = conn.request(req)
puts rs.body

To learn how to get a host FQDN, see this guide.

Connection:

ruby connect.rb

ClickHouse® is a registered trademark of ClickHouse, Inc.

Code examples for connecting to a ClickHouse® cluster

GoGo

JavaJava

Node.jsNode.js

ODBCODBC

LinuxLinux

WindowsWindows

PHPPHP

Python (clickhouse-driver)Python (clickhouse-driver)

Python (requests)Python (requests)

RubyRuby

Was the article helpful?

Go

Java

Node.js

ODBC

Linux

Windows

PHP

Python (clickhouse-driver)

Python (requests)

Ruby