Connecting to a ClickHouse database
Written by
In the Yandex Cloud infrastructure, ClickHouse server clusters are deployed and supported using Managed Service for ClickHouse.
Before you begin
- Create a new Managed Service for ClickHouse cluster and enable public access to it from the host. You can also use an existing cluster with publicly available hosts.
- Configure cluster security groups.
-
Open the project DataSphere:
-
Select the desired project in your community or on the DataSphere homepage in the Recent projects tab.
- Click Open project in JupyterLab and wait for it to load.
- Open the notebook tab.
-
Connecting to a host
To connect to Managed Service for ClickHouse cluster hosts:
-
Get an SSL certificate: To do this, enter the following command in a notebook cell:
#!:bash mkdir ~/.clickhouse-client wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" -O ~/.clickhouse-client/root.crt && \ chmod 0600 ~/.clickhouse-client/root.crt
-
Establish a connection to the database. To do this, enter the following command in a notebook cell:
Using the requests libraryUsing the clickhouse-driver libraryimport requests url = 'https://{host}:8443/?database={db}&query={query}'.format( host='<FQDN of ClickHouse host>', db='<DB name>', query='SELECT version()') auth = { 'X-ClickHouse-User': '<DB username>', 'X-ClickHouse-Key': '<DB user password>', } cacert = '/home/jupyter/.clickhouse-client/root.crt' rs = requests.get(url, headers=auth, verify=cacert) rs.raise_for_status() print(rs.text)
A successful cluster connection and test query will display the ClickHouse version:
21.3.13.9
from clickhouse_driver import Client client = Client(host='<FQDN of ClickHouse host>', user='<DB username>', password='<DB user password>', database='<DB name>', secure=True) client.execute('SELECT version()')
A successful cluster connection and test query will display the ClickHouse version:
[('21.3.13.9',)]