[Yandex Cloud documentation](../../index.md) > [Yandex Query](../index.md) > [Tutorials](index.md) > Working with data in Managed Service for ClickHouse®

# Working with data in Yandex Managed Service for ClickHouse®

Yandex Query is an interactive service for serverless data analysis. You can use it to process information from various storages without having to to create a dedicated cluster. Yandex Query supports [Yandex Object Storage](../../storage/index.md), [Yandex Managed Service for PostgreSQL](../../managed-postgresql/index.md), and [Yandex Managed Service for ClickHouse®](../../managed-clickhouse/index.md) data storages.

Follow this tutorial to connect to a Managed Service for ClickHouse® database and query it from a JupyterLab notebook with the help of Query.

1. [Set up the infrastructure](#infra).
1. [Get started in Query](#yq-begin).
1. [Create a Managed Service for ClickHouse® cluster](#create-cluster).
1. [Connect to the Managed Service for ClickHouse® data](#mch-connect).

If you no longer need the resources you created, [delete them](#clear-out).


## Getting started {#before-you-begin}

Before getting started, register in Yandex Cloud, set up a [community](../../datasphere/concepts/community.md), and link your [billing account](../../billing/concepts/billing-account.md) to it.
1. [On the DataSphere home page](https://datasphere.yandex.cloud), click **Try for free** and select an account to log in with: Yandex ID or your working account with the identity federation (SSO).
1. Select the [Yandex Identity Hub organization](../../organization/index.md) you are going to use in Yandex Cloud.
1. [Create a community](../../datasphere/operations/community/create.md).
1. [Link your billing account](../../datasphere/operations/community/link-ba.md) to the DataSphere community you are going to work in. Make sure you have a linked billing account and its [status](../../billing/concepts/billing-account-statuses.md) is `ACTIVE` or `TRIAL_ACTIVE`. If you do not have a billing account yet, create one in the DataSphere interface.

### Required paid resources {#paid-resources}

The cost of infrastructure support for working with Managed Service for ClickHouse® data includes:

* Fee for using [DataSphere computing resources](../../datasphere/pricing.md).
* Fee for a running [Managed Service for ClickHouse® cluster](../../managed-clickhouse/pricing.md).
* Fee for the amount of read data when running [Query queries](../pricing.md).

## Set up your infrastructure {#infra}

Log in to the Yandex Cloud [management console](https://console.yandex.cloud) and select the organization you use to access DataSphere. On the [**Yandex Cloud Billing**](https://center.yandex.cloud/billing/accounts) page, make sure you have a billing account linked.

If you have an active billing account, you can go to the [cloud page](https://console.yandex.cloud/cloud) to create or select a folder to run your infrastructure.

{% note info %}

If you are using an [identity federation](../../organization/concepts/add-federation.md) to work with Yandex Cloud, you might not have access to billing details. In this case, contact your Yandex Cloud organization administrator.

{% endnote %}

### Create a folder {#create-folder}

{% list tabs group=instructions %}

- Management console {#console}

   1. In the [management console](https://console.yandex.cloud), select a cloud and click ![create](../../_assets/console-icons/plus.svg)**Create folder**.
   1. Name your folder, e.g., `data-folder`.
   1. Click **Create**.

{% endlist %}

### Create a service account for the DataSphere project {#create-sa}

{% list tabs group=instructions %}

- Management console {#console}

  1. Navigate to `data-folder`.
  1. [Go](../../console/operations/select-service.md#select-service) to **Identity and Access Management**.
  1. Click **Create service account**.
  1. Name the [service account](../../iam/concepts/users/service-accounts.md), e.g., `yq-sa`.
  1. Click **Add role** and assign the following roles to the service account:
     * `yq.editor` to run Query queries.
     * `managed-clickhouse.viewer` to view the contents of the Managed Service for ClickHouse® cluster.
  1. Click **Create**.

{% endlist %}

### Add the service account to the project {#sa-to-project}

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

1. Select the project in your community or on the DataSphere [home page](https://datasphere.yandex.cloud) in the **Recent projects** tab.
1. In the **Members** tab, click **Add member**.
1. Select the `yq-sa` account and click **Add**.
1. Switch your service account role to **Editor**.

### Create an authorized key for a service account {#create-key}

To allow the service account to send queries using Query, create an [authorized key](../../iam/concepts/authorization/key.md).

{% note info %}

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

{% endnote %}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), navigate to `data-folder`.
  1. [Go](../../console/operations/select-service.md#select-service) to **Identity and Access Management**.
  1. In the left-hand panel, select ![FaceRobot](../../_assets/console-icons/face-robot.svg) **Service accounts**.
  1. In the list that opens, select `yq-sa`.
  1. Click **Create new key** in the top panel and select **Create authorized key**.
  1. Select the encryption algorithm and click **Create**.
  1. Click **Download file with keys**.

{% endlist %}

### Create a secret {#create-secret}

To get an authorized key from the notebook, create a [secret](../../datasphere/concepts/secrets.md) with the contents of the authorized key file.

1. Select the project in your community or on the DataSphere [home page](https://datasphere.yandex.cloud) in the **Recent projects** tab.
1. Under **Project resources**, click ![secret](../../_assets/console-icons/shield-check.svg)**Secret**.
1. Click **Create**.
1. In the **Name** field, enter a name for the secret: `yq_access_key`.
1. In the **Value** field, paste the full contents of the authorized key file you downloaded.
1. Click **Create**.

### Create a notebook {#create-notebook}

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

1. Select the project in your community or on the DataSphere [home page](https://datasphere.yandex.cloud) in the **Recent projects** tab.
1. Click **Open project in JupyterLab** and wait for the loading to complete.
1. In the top panel, click **File** and select **New** ⟶ **Notebook**.
1. Select a kernel and click **Select**.

## Getting started with Query {#yq-begin}

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 project in your community or on the DataSphere [home page](https://datasphere.yandex.cloud) in the **Recent projects** tab.
   1. Click **Open project in JupyterLab** and wait for the loading to complete.
   1. Open the notebook tab.

1. Install the `yandex_query_magic` package:

   ```python
   %pip install yandex_query_magic
   ```

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

1. Upload the extension:

   ```python
   %load_ext yandex_query_magic
   ```

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

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

1. Run a test query to Query:

   ```sql
   %yq select "Hello, world!"
   ```

## Create a Managed Service for ClickHouse® cluster {#create-cluster}

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

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select `data-folder`.
  1. [Go](../../console/operations/select-service.md#select-service) to **Managed Service for&nbsp;ClickHouse**.
  1. Click **Create cluster**.
  1. In the **Cluster name** field, enter the cluster name, e.g., `clickhouse`.

  1. Under **DBMS settings**:

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

  1. Under **Service settings**:

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

  1. You can leave the other settings at their defaults.
  1. Click **Create cluster**.

{% endlist %}

### Create a table {#create-table}

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

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), open the `clickhouse` cluster page and navigate to the **SQL** tab.
  1. Enter **Username** and **Password** you specified when creating the cluster.
  1. In the input window on the right, paste an SQL query:

     ```sql
     CREATE TABLE test(col1 int)
         ENGINE = MergeTree
             ORDER BY col1;
   
     INSERT INTO test
     SELECT
         *
     FROM numbers(100)
     ```

  1. Click **Execute**.

{% endlist %}

## Connect to Managed Service for ClickHouse® data {#mch-connect}

To create a Query [connection](../concepts/glossary.md#connection):

{% list tabs group=instructions %}

- Management console {#console}
  
  1. In the [management console](https://console.yandex.cloud), select `data-folder`.
  1. [Go](../../console/operations/select-service.md#select-service) to **Yandex Query**.
  1. In the left-hand panel, select **Connections**.
  1. Click ![info](../../_assets/console-icons/plus.svg)**Create new**.
  1. Name the connection, e.g., `clickhouse`.
  1. Select the **Managed Service for ClickHouse** connection type.
  1. Under **Connection type parameters**:

     * **Cluster**: Select the `clickhouse` cluster you created earlier.
     * **Service account**: Select the `yq-sa` service account.
     * Enter **Login** and **Password** you specified when creating the cluster.

  1. Click **Create**.

{% endlist %}

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

```sql
%yq SELECT * FROM clickhouse.test
```

## How to delete the resources you created {#clear-out}

To stop paying for the resources you created:

* [Delete the database cluster](../../managed-clickhouse/operations/cluster-delete.md).
* [Delete the project](../../datasphere/operations/projects/delete.md).

_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._