Contact UsGet started
© 2024 Iron Hive LLC Belgrade

Working with data in Yandex Managed Service for ClickHouse®

Written by
Updated at July 8, 2024

Yandex Query is an interactive service for serverless data analysis. It enables you to process information from different storages without the need to create a dedicated cluster. The service supports working with Yandex Object Storage, Yandex Managed Service for PostgreSQL, and Yandex Managed Service for ClickHouse® data storages.

In this tutorial, you will connect to the Managed Service for ClickHouse® database and run queries against it from the JupyterLab notebook using Query.

  1. Prepare your infrastructure.
  2. Get started in Query.
  3. Create a Managed Service for ClickHouse® cluster.
  4. Connect to the Managed Service for ClickHouse® data.

If you no longer need the resources you created, delete them.

Getting started

Before getting started, register in Yandex Cloud, set up a community, and link your billing account to it.

  1. On the DataSphere home page, click Try for free and select an account to log in with: Yandex ID or your working account in the identity federation (SSO).
  2. Select the Yandex Cloud Organization organization you are going to use in Yandex Cloud.
  3. Create a community.
  4. Link your billing account to the DataSphere community you are going to work in. Make sure that you have a billing account linked and its status is ACTIVE or TRIAL_ACTIVE. If you do not have a billing account yet, create one in the DataSphere interface.

For working with Managed Service for ClickHouse® data, the cost of infrastructure support includes:

Prepare the infrastructure

Log in to the Yandex Cloud management console and select the organization you use to access DataSphere. On the Yandex Cloud Billing page, make sure you have a billing account linked.

If you have an active billing account, you can create or select a folder to deploy your infrastructure in, on the cloud page.

Note

If you use an identity federation to access Yandex Cloud, billing details might be unavailable to you. In this case, contact your Yandex Cloud organization administrator.

Create a folder

  1. In the management console, select a cloud and click Create folder.
  2. Give your folder a name, e.g., data-folder.
  3. Click Create.

Create a service account for the DataSphere project

  1. Go to the data-folder folder.
  2. In the Service accounts tab, click Create service account.
  3. Enter a name for the service account, e.g., yq-sa.
  4. Click Add role and assign the following roles to the service account:
    • datasphere.community-project.editor: To run DataSphere computations.
    • yq.editor: To send Query queries.
    • managed-clickhouse.viewer: To view the contents of the Managed Service for ClickHouse® cluster.
  5. Click Create.

Add the service account to a project

To enable the service account to run a DataSphere project, add it to the list of project members.

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

  2. In the Members tab, click Add member.
  3. Select the yq-sa account and click Add.

Create an authorized key for a service account

To allow the service account to send Query queries, create an authorized key.

Note

Authorized keys do not expire, but you can always get new authorized keys and authenticate again if something goes wrong.

  1. In the management console, go to data-folder.
  2. At the top of the screen, go to the Service accounts tab.
  3. Select the yq-sa service account.
  4. Click Create new key in the top panel and select Create authorized key.
  5. Select the encryption algorithm and click Create.
  6. Click Download file with keys.

Create a secret

To get an authorized key from the notebook, create a secret with the contents of the authorized key file.

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

  2. Under Project resources, click Secret.
  3. Click Create.
  4. In the Name field, enter the secret name: yq_access_key.
  5. In the Value field, paste the full contents of the downloaded file with the authorized key.
  6. Click Create.

Create a notebook

Queries to the Managed Service for ClickHouse® database through Query will be sent from the notebook.

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

  2. Click Open project in JupyterLab and wait for the loading to complete.
  3. In the top panel, click File and select New ⟶ Notebook.
  4. Select a kernel and click Select.

Getting started with Query

The yandex_query_magic package provides magic commands for working in Jupyter. Install it to send queries to Query. Paste the code into the yq-storage.ipynb notebook cells.

  1. Open the DataSphere project:

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

    2. Click Open project in JupyterLab and wait for the loading to complete.
    3. Open the notebook tab.

  2. Install the yandex_query_magic package:

    %pip install yandex_query_magic

  3. Once the installation is complete, from the top panel, select Kernel ⟶ Restart kernel....

  4. Upload the extension:

    %load_ext yandex_query_magic

  5. Configure the connection by specifying the data-folder ID and the name of the authorized key secret:

    %yq_settings --folder-id <folder_ID> --env-auth yq_access_key

  6. Run a test query to Query:

    %yq select "Hello, world!"

Create a Managed Service for ClickHouse® cluster

Any running Managed Service for ClickHouse® cluster with the Yandex Query access option enabled is suitable for sending queries.

  1. In the management console, select data-folder.

  2. Select Managed Service for ClickHouse.

  3. Click Create cluster.

  4. In the Cluster name field, enter a name for the cluster, e.g., clickhouse.

  5. Under DBMS settings:

    • In the User management via SQL field, select Enabled from the drop-down list.
    • Specify Username and Password.

  6. Under Service settings:

    • Select the yq-sa service account.
    • Enable the Yandex Query access and Access from the management console options.

  7. You may leave default values for the other settings.

  8. Click Create cluster.

Create a table

In this step, you will create a test table with numbers from 0 to 100.

  1. In the management console, open the clickhouse cluster page, and go to the SQL tab.

  2. Enter Username and Password you specified when creating the cluster.

  3. In the input window on the right, paste an SQL query:

    CREATE TABLE test(col1 int)
    ENGINE = MergeTree
        ORDER BY col1;

INSERT INTO test
SELECT
    *
FROM numbers(100)

  4. Click Execute.

Connect to data in Managed Service for ClickHouse®

To create a Query connection:

  1. In the management console, select data-folder.

  2. In the list of services, select Yandex Query.

  3. In the left-hand panel, select Connections.

  4. Click Create new.

  5. Enter a name for the connection, e.g., clickhouse.

  6. Select the Managed Service for ClickHouse connection type.

  7. Under Connection type parameters:

    • Cluster: Select the previously created clickhouse cluster.
    • Service account: Select the yq-sa service account.
    • Enter Login and Password specified when you created the cluster.

  8. Click Create.

To check the connection, get the table data from the notebook cell:

%yq SELECT * FROM clickhouse.test

How to delete the resources you created

To stop paying for the resources you created:

ClickHouse® is a registered trademark of ClickHouse, Inc.

Previous
Working with data in Yandex Object Storage
Next
Working with data in Yandex Managed Service for PostgreSQL