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

# Working with data using Yandex Query

Yandex Query is an interactive service for serverless data analysis. You can use it to process information from various storages without the need to create a dedicated cluster. Yandex Query supports [Yandex Object Storage](../../storage/index.md), [Yandex Managed Service for 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 PostgreSQL database and query it from a JupyterLab notebook with the help of Query.

1. [Set up your infrastructure](#infra).
1. [Get started in Query](#yq-begin).
1. [Create a Managed Service for PostgreSQL cluster](#create-cluster).
1. [Connect to the Managed Service for PostgreSQL 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}

* DataSphere project: Use of computing resources and storage (see [DataSphere pricing](../../datasphere/pricing.md)).
* Managed Service for PostgreSQL cluster: computing resources allocated to hosts, storage and backup size (see [Managed Service for PostgreSQL pricing](../pricing.md)).
* Query: Amount of data read when running queries (see [Query pricing](../../query/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-postgresql.viewer` to view the contents of the Managed Service for PostgreSQL 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).

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!"
   ```

{% 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 PostgreSQL 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**.

## Get started in 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 PostgreSQL cluster {#create-cluster}

Any running Managed Service for PostgreSQL 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;PostgreSQL**.
  1. Click **Create cluster**.
  1. In the **Cluster name** field, enter the cluster name, e.g., `postgresql`.
  1. Under **Database**:
     * Specify **DB name**, e.g., `db1`.
     * Specify **Username** and **Password**.
  1. Under **Service settings**, 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 random numbers from 0 to 100.

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), open the `postgresql` 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 (
         id SERIAL PRIMARY KEY,
         number INT
     );
 
     INSERT INTO test (number)
     SELECT random() * 100
     FROM generate_series(1, 100);
     ```

  1. Click **Execute**.

{% endlist %}

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

To create a Query [connection](../../query/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., `postgresql`.
  1. Select the **Managed Service for PostgreSQL** connection type.
  1. Under **Connection type parameters**:

     * **Cluster**: Select the `postgresql` cluster you created earlier.
     * **Service account**: `yq-sa`.
     * **Database**: `db1`.
     * 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 postgresql.test
```

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

To stop paying for the resources you created:

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