Connecting to a ClickHouse database

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

Before you begin

1. 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.
2. Configure cluster security groups.

3. Open the project DataSphere:

   1. Select the desired project in your community or on the DataSphere homepage in the Recent projects tab.

   2. Click Open project in JupyterLab and wait for it to load.
   3. Open the notebook tab.

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='<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',)]