Connecting to a ClickHouse database

In the Yandex.Cloud infrastructure, ClickHouse server clusters are deployed and maintained using the Managed Service for ClickHouse service.

To utilize a Managed Service for ClickHouse cluster host as a data source for DataSphere:

1. Create a new Managed Service for ClickHouse cluster with public access for the host. You can also use an existing cluster with publicly available hosts.
2. Configure cluster security groups.

Before you start

If a project is already open, open the tab with a notebook.

If not, open the project:

1. In the management console, open the DataSphere section in the folder where you work with DataSphere projects.

2. Go to the Projects tab.
3. Select the project you want to open and click .
4. Choose Open and wait for the project to open.

Connecting to a host

To connect to Managed Service for ClickHouse cluster hosts:

1. 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
   

2. Establish a connection to the database. To do this, enter the following command in a notebook cell:

   Using the requests library

   Using the clickhouse-driver library

   import requests
   url = 'https://{host}:8443/?database={db}&query={query}'.format(
           host='<ClickHouse host FQDN >',
           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='<ClickHouse host FQDN>', 
                   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',)]