Connecting to a database in a ClickHouse® cluster
You can connect to Managed Service for ClickHouse® cluster hosts:
-
Over the internet, if you configured public access for the appropriate host. You can only connect to such hosts over an SSL connection.
-
From Yandex Cloud virtual machines located in the same cloud network. If there is no public access to a host, connections from this type of virtual machine don't need to be over SSL.
You can connect to a cluster both using encryption via ports 9440
for clickhouse-client8443
for the HTTP interface9000
and 8123
, respectively.
Configuring security groups
To connect to a cluster, security groups must include rules allowing traffic from certain ports, IP addresses, or from other security groups.
Rule settings depend on the connection method you select:
Configure all security groups in a cluster to allow incoming traffic on ports 8443 and 9440 from any IP address. To do this, create the following rules for incoming traffic:
- Port range:
8443
,9440
- Protocol:
TCP
- Source:
CIDR
- CIDR blocks:
0.0.0.0/0
A separate rule is created for each port.
-
Configure all the security groups of your cluster to allow incoming traffic on ports 8123, 8443, 9000, and 9440 from the security group where your VM is located. To do this, create the following rules for incoming traffic in these security groups:
- Port range:
8123
(or any of the other ports listed). - Protocol:
TCP
. - Source:
Security group
. - Security group: If your cluster and VM are in the same security group, select
Current
(Self
) as the value. Otherwise, specify the VM security group.
A separate rule is created for each port.
- Port range:
-
Configure the security group where the VM is located to enable connections to the VM and traffic between the VM and the cluster hosts.
For example, you can set the following rules for a VM:
-
For incoming traffic:
- Port range:
22
- Protocol:
TCP
- Source:
CIDR
- CIDR blocks:
0.0.0.0/0
This rule allows you to connect to the VM over SSH.
- Port range:
-
For outgoing traffic:
- Port range:
0-65535
- Protocol:
Any
(Any
) - Destination name:
CIDR
- CIDR blocks:
0.0.0.0/0
This rule allows all outgoing traffic, which enables you to both connect to the cluster and install the certificates and utilities the VMs need to connect to the cluster.
- Port range:
-
Note
You can set more detailed rules for security groups, such as allowing traffic in only specific subnets.
Security groups must be configured correctly for all subnets that will include cluster hosts. If the security group settings are incomplete or incorrect, you might lose access the cluster.
For more information about security groups, see DB network and clusters.
Getting your SSL certificates
To use an encrypted connection, get your SSL certificates:
sudo mkdir --parents /usr/local/share/ca-certificates/Yandex/ && \
sudo wget "https://storage.yandexcloud.net/cloud-certs/RootCA.pem" \
--output-document /usr/local/share/ca-certificates/Yandex/RootCA.crt && \
sudo wget "https://storage.yandexcloud.net/cloud-certs/IntermediateCA.pem" \
--output-document /usr/local/share/ca-certificates/Yandex/IntermediateCA.crt && \
sudo chmod 655 \
/usr/local/share/ca-certificates/Yandex/RootCA.crt \
/usr/local/share/ca-certificates/Yandex/IntermediateCA.crt && \
sudo update-ca-certificates
The certificates will be saved to the following files:
/usr/local/share/ca-certificates/Yandex/RootCA.crt
/usr/local/share/ca-certificates/Yandex/IntermediateCA.crt
sudo mkdir -p /usr/local/share/ca-certificates/Yandex/ && \
sudo wget "https://storage.yandexcloud.net/cloud-certs/RootCA.pem" \
--output-document /usr/local/share/ca-certificates/Yandex/RootCA.crt && \
sudo wget "https://storage.yandexcloud.net/cloud-certs/IntermediateCA.pem" \
--output-document /usr/local/share/ca-certificates/Yandex/IntermediateCA.crt && \
sudo chmod 655 \
/usr/local/share/ca-certificates/Yandex/RootCA.crt \
/usr/local/share/ca-certificates/Yandex/IntermediateCA.crt && \
security import /usr/local/share/ca-certificates/Yandex/RootCA.crt -k ~/Library/Keychains/login.keychain; \
security import /usr/local/share/ca-certificates/Yandex/IntermediateCA.crt -k ~/Library/Keychains/login.keychain
The certificates will be saved to the following files:
/usr/local/share/ca-certificates/Yandex/RootCA.crt
/usr/local/share/ca-certificates/Yandex/IntermediateCA.crt
-
Download and import the certificates:
mkdir -Force $HOME\.yandex; ` curl.exe https://storage.yandexcloud.net/cloud-certs/RootCA.pem ` --output $HOME\.yandex\RootCA.crt; ` curl.exe https://storage.yandexcloud.net/cloud-certs/IntermediateCA.pem ` --output $HOME\.yandex\IntermediateCA.crt; ` Import-Certificate ` -FilePath $HOME\.yandex\RootCA.crt ` -CertStoreLocation cert:\CurrentUser\Root; ` Import-Certificate ` -FilePath $HOME\.yandex\IntermediateCA.crt ` -CertStoreLocation cert:\CurrentUser\Root
-
Agree to install the certificates in the
Trusted Root Certification Authorities
store.
The certificates will be saved to the following files:
$HOME\.yandex\RootCA.crt
$HOME\.yandex\IntermediateCA.crt
To use graphical IDEs, save a certificate
ClickHouse® host FQDN
To connect to a host, you need its fully qualified domain name (FQDN). You can obtain it in one of the following ways:
-
In the management console
, copy the command for connecting to the cluster. This command contains the host FQDN. To get the command, go to the cluster page and click Connect. -
Look up the FQDN in the management console:
- Go to the cluster page.
- Go to Hosts.
- Copy the Host FQDN column value.
Cluster hosts also use special FQDNs.
Selecting an available host automatically
If you do not want to manually connect to another host in case the current one becomes unavailable, use a special FQDN. It can be in one of the following formats:
-
c-<cluster_ID>.rw.mdb.yandexcloud.net
: To connect to an available cluster host. -
<shard_name>.c-<cluster_ID>.rw.mdb.yandexcloud.net
: To connect to an available shard host.
If the host an FQDN points to becomes unavailable, there may be a slight delay before the FQDN starts pointing to another available host.
Warning
If, under cluster maintenance, a special FQDN points to a host with no public access enabled, the cluster cannot be connected to from the internet. To avoid this, enable public access for all cluster hosts.
Connecting from graphical IDEs
Connections were tested in the following environment:
- Ubuntu 20.04, DBeaver:
22.2.4
- MacOS Monterey 12.7
- JetBrains DataGrip:
2023.3.4
- DBeaver Community:
24.0.0
- JetBrains DataGrip:
- Windows 10 Pro 21H1 with SQL Server Management Studio
18.9.1
You can only use graphical IDEs to connect to public cluster hosts using SSL certificates.
To avoid connection errors, save the certificate
- Create a data source:
- Select File → New → Data Source → ClickHouse®.
- On the General tab:
- Specify the connection parameters:
- Host: Any ClickHouse® host FQDN or a special FQDN.
- Port:
8443
. - User, Password: DB user's name and password.
- Database: Name of the DB to connect to.
- Click Download to download the connection driver.
- Specify the connection parameters:
- On the SSH/SSL tab:
- Enable the Use SSL setting.
- Specify the path to the directory that contains the file with the downloaded SSL certificate for the connection.
- Click Test Connection to test the connection. If the connection is successful, you will see the connection status and information about the DBMS and driver.
- Click OK to save the data source.
- Create a new DB connection:
- In the Database menu, select New connection.
- Select ClickHouse® from the DB list.
- Click Next.
- Specify the connection parameters on the Main tab:
- Host: FQDN of any ClickHouse® host or a special FQDN.
- Port:
8443
. - DB/Schema: Name of the DB to connect to.
- Under Authentication, specify the DB user's name and password.
- On the Driver properties tab:
- Click Download in a new window with an invitation to download the driver files.
- Specify the SSL connection parameters in the driver property list:
ssl:true
sslrootcert:<path_to_SSL_certificate_file_saved>
- Click Test connection ... to test the connection. If the connection is successful, you will see the connection status and information about the DBMS and driver.
- Click Ready to save the database connection settings.
Before you connect from a Docker container
To connect to a Managed Service for ClickHouse® cluster from a Docker container, add the following lines to the Dockerfile:
# Connect the DEB repository.
RUN apt-get update && \
apt-get install wget --yes apt-transport-https ca-certificates dirmngr && \
apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD******** && \
echo "deb https://packages.clickhouse.com/deb stable main" | tee \
/etc/apt/sources.list.d/clickhouse.list && \
# Install dependencies.
apt-get update && \
apt-get install wget clickhouse-client --yes && \
# Upload a configuration file for clickhouse-client.
mkdir --parents ~/.clickhouse-client && \
wget "https://storage.yandexcloud.net/doc-files/clickhouse-client.conf.example" \
--output-document ~/.clickhouse-client/config.xml
# Connect the DEB repository.
RUN apt-get update && \
apt-get install wget --yes apt-transport-https ca-certificates dirmngr && \
apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD******** && \
echo "deb https://packages.clickhouse.com/deb stable main" | tee \
/etc/apt/sources.list.d/clickhouse.list && \
# Install dependencies.
apt-get update && \
apt-get install wget clickhouse-client --yes && \
# Upload a configuration file for clickhouse-client.
mkdir --parents ~/.clickhouse-client && \
wget "https://storage.yandexcloud.net/doc-files/clickhouse-client.conf.example" \
--output-document ~/.clickhouse-client/config.xml && \
# Get SSL certificates.
mkdir --parents /usr/local/share/ca-certificates/Yandex/ && \
wget "https://storage.yandexcloud.net/cloud-certs/RootCA.pem" \
--output-document /usr/local/share/ca-certificates/Yandex/RootCA.crt && \
wget "https://storage.yandexcloud.net/cloud-certs/IntermediateCA.pem" \
--output-document /usr/local/share/ca-certificates/Yandex/IntermediateCA.crt && \
chmod 655 \
/usr/local/share/ca-certificates/Yandex/RootCA.crt \
/usr/local/share/ca-certificates/Yandex/IntermediateCA.crt && \
update-ca-certificates
Connecting to a cluster from your browser
To run SQL queries from your browser, use:
When connecting from the browser, SQL queries are executed separately, without creating a session shared with the ClickHouse® server. Therefore, queries running within a session (for example, USE
or SET
) have no impact.
Management console
Warning
The SQL editor built into the management console is designed for a narrow range of data editing tasks. Some parameter values can be replaced by the editor's defaults.
For working with databases, we recommend third-party editors or Yandex WebSQL. To use Yandex WebSQL, click Open on a promo banner and connect to a DB cluster.
To connect to a Managed Service for ClickHouse® cluster, log in to the management console
To allow connections, activate the Access from the management console option when creating a cluster or changing its settings.
For more information, see SQL queries.
Built-in SQL editor
To connect to a cluster host from the built-in SQL editor, specify the following in the browser address bar:
https://<FQDN_of_any_ClickHouse®_host>:8443/play
You can only connect to publicly accessible cluster hosts. To learn how to get a host FQDN, see this guide.
To connect to a cluster by selecting an available host automatically, use the following URL:
https://c-<cluster_ID>.rw.mdb.yandexcloud.net:8443/play
to connect to the available cluster host.https://<shard_name>.c-<cluster_ID>.rw.mdb.yandexcloud.net:8443/play
to connect to the available shard host.
To make a query to the database, specify the username and password in the upper-right corner.
Yandex WebSQL service
Note
This feature is in the Preview stage.
In Yandex WebSQL, create a connection to a ClickHouse® cluster in Yandex Cloud.
Warning
You can establish connections to managed DB clusters from your Yandex Cloud organization's clouds and folders. If you switch to a different organization, a set of clusters from other clouds and folders will be available to you.
- Make sure the Access from the management console option is enabled in the PostgreSQL, MySQL, or ClickHouse® cluster settings.
- In Yandex WebSQL, go to the Connection manager section and click
- Specify the connection parameters:
- Cloud: Select a cloud.
- Folder: Select the folder where you created the database cluster.
- Cluster: Select the cluster.
- Click Connect to connect to the selected cluster.
Imported connections will be available while you are working in the current Yandex Cloud organization.
To build a query:
-
In the Connection manager, select a database, table, or image.
-
In the data panel, you will see a form with the default query:
SELECT * FROM <table_name> LIMIT 10;
-
Use this query or edit it. The interface will suggest relevant parts of the SQL query and highlight errors.
-
Click Run.
In the results panel, you will see a table with the query results.
When editing or running a query, you can use the following keyboard shortcuts (click
to see the full list):- Show suggestions: Ctrl + I , Ctrl + Space.
- Comment out a line: Ctrl + /.
- Run a query: Ctrl + Enter.
- Show suggestions: ⌘ + I , ⌃ + Space.
- Comment out a line: ⌘ + /.
- Run a query: ⌘ + ⏎.
Click Open Command Palette (
Sample connection strings
The Linux examples 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 Windows examples were tested in the following environment:
- A local machine with Windows 10 Pro build
19042.1052
. - PowerShell:
5.1.19041
. - cURL:
7.55.1 WinSSL
.
You can only connect to public ClickHouse® cluster hosts with your SSL certificates. Before connecting, prepare your certificates.
In the examples below, it is assumed that the RootCA.crt
and IntermediateCA.crt
certificates:
- Are located in the
/usr/local/share/ca-certificates/Yandex/
directory (for Ubuntu). - Are imported to the Trusted Root Certificate store (for Windows).
Connecting without any SSL certificates is only supported for hosts that are not publicly accessible. For connections to the database, traffic inside the virtual network is not encrypted in this case.
To view an example of the command with the host FQDN filled in, open the cluster page in the management console
If the connection to the cluster and the test query are successful, the ClickHouse® version is output.
clickhouse-client
Before connecting:
-
Connect the DEB repository
ClickHouse®:sudo apt update && sudo apt install --yes apt-transport-https ca-certificates dirmngr && \ sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD******** && \ echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee \ /etc/apt/sources.list.d/clickhouse.list
-
Install the dependencies:
sudo apt update && sudo apt install --yes clickhouse-client
-
Download the configuration file for
clickhouse-client
:mkdir -p ~/.clickhouse-client && \ wget "https://storage.yandexcloud.net/doc-files/clickhouse-client.conf.example" \ --output-document ~/.clickhouse-client/config.xml
clickhouse-client --host <FQDN_of_any_ClickHouse®_host> \
--user <username> \
--database <DB_name> \
--port 9000 \
--ask-password
clickhouse-client --host <FQDN_of_any_ClickHouse®_host> \
--secure \
--user <username> \
--database <DB_name> \
--port 9440 \
--ask-password
To learn how to get a host FQDN, see this guide.
After running the command, enter the user password to complete the connection process.
Once connected to the DBMS, run SELECT @@version;
.
cURL
curl --header "X-ClickHouse-User: <DB_username>" \
--header "X-ClickHouse-Key: <DB_user_password>" \
'http://<Any_ClickHouse®_host_FQDN>:8123/?database=<DB_name>&query=SELECT%20version()'
curl --cacert /usr/local/share/ca-certificates/Yandex/RootCA.crt \
--header "X-ClickHouse-User: <DB_username>" \
--header "X-ClickHouse-Key: <DB_user_password>" \
'https://<FQDN_of_any_ClickHouse®_host>:8443/?database=<DB_name>&query=SELECT%20version()'
To learn how to get a host FQDN, see this guide.
Go
Before connecting, install the dependencies:
sudo apt update && sudo apt install --yes golang git
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:
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
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/
-
Register the ClickHouse® ODBC driver by adding the following lines to the
odbcinst.ini
file (sample file ):/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
/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 appropriate bit depth. For example, if you are using a 32-bit application, install the driver with the same number of bits to connect through ODBC. -
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 SSLConnecting 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 toOn
in the PHP settings:cat /etc/php/7.4/apache2/php.ini | grep "allow_url_fopen"
Otherwise, set this value and restart Apache:
sudo sed -i 's/\(^allow_url_fopen = \).*/\1On/' /etc/php/7.4/apache2/php.ini && \ sudo systemctl restart apache2
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.
Connection:
php connect.php
PowerShell
curl.exe `
-H "X-ClickHouse-User: <DB_user_name>" `
-H "X-ClickHouse-Key: <DB_user_password>" `
'http://<FQDN_of_any_ClickHouse®_host>:8123/?database=<DB_name>&query=SELECT+version()'
curl.exe `
-H "X-ClickHouse-User: <DB_user_name>" `
-H "X-ClickHouse-Key: <DB_user_password>" `
'https://<FQDN_of_any_ClickHouse®_host>:8443/?database=<DB_name>&query=SELECT+version()'
To learn how to get a host FQDN, see this guide.
Python (clickhouse-driver)
Before connecting, install the dependencies:
sudo apt update && sudo apt install --yes python3 python3-pip && \
pip3 install clickhouse-driver
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
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
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