Connecting to a ClickHouse database
In the Yandex Cloud infrastructure, ClickHouse server clusters are deployed and supported using Managed Service for ClickHouse.
To utilize a Managed Service for ClickHouse cluster host as a data source for DataSphere:
- 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.
Before you start
If a project is already open, open the tab with a notebook.
If not, open the project:
-
In the management console, open the DataSphere section in the folder where you work with DataSphere projects.
- Go to the Projects tab.
- Select the project you want to open and click .
- Choose Open and wait for the project to open.
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',)]