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

# Federated data queries 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.

You can process data from these systems either individually or as part of one common query, referred to as a _federated_ query.

In this tutorial, you will create three dedicated data storages: customers, purchased items, and purchase dates. You can get data from all storages at the same time using a federated query from a notebook cell.

1. [Set up the infrastructure](#infra).
1. [Get started in Query](#yq-begin).
1. [Connect to the Object Storage data](#storage-connect).
1. [Connect to the Managed Service for ClickHouse® data](#ch-connect).
1. [Connect to the Managed Service for PostgreSQL data](#pg-connect).
1. [Run a federated query](#federated_query).

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: 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)).
* Managed Service for ClickHouse® cluster: computing resources allocated to hosts, storage and backup size (see [Managed Service for ClickHouse® pricing](../../managed-clickhouse/pricing.md)).
* Object Storage bucket: use of storage, data operations (see [Object Storage pricing](../../storage/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. 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. 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.
     * `storage.viewer` to view the contents of the Object Storage bucket and objects.
     * `managed-clickhouse.viewer` to view the contents of the Managed Service for ClickHouse® cluster.
     * `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).

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

## Connect to the Object Storage data {#storage-connect}

For operations with Object Storage data, you will need a bucket with a table, a Query [connection](../../query/concepts/glossary.md#connection), and a data [binding](../../query/concepts/glossary.md#binding).

### Create a data bucket {#storage-data}

The Object Storage [bucket](../../storage/concepts/bucket.md) will contain the purchase dates.

{% 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 **Object Storage**.
  1. At the top right, click **Create bucket**.
  1. In the ** Name** field, enter a name for the bucket.
  1. In the **Read objects**, **Read object list**, and **Read settings** fields, select **With authorization**.
  1. Click **Create bucket**.
  1. Create a file named `visits.csv` and add a table with purchase dates to it:

     ```text
     date|persons_id|item_id
     2024-05-14 12:12:00|1|2
     2024-05-15 13:13:00|2|1
     ```

  1. Navigate to the bucket you created and click **Upload**.
  1. In the window that opens, select `visits.csv` and click **Open**.
  1. Click **Upload**.

{% endlist %}

### Create a connection to Object Storage {#yq-storage}

{% 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 **Yandex Query**.
  1. In the left-hand panel, select **Connections**.
  1. Click ![info](../../_assets/console-icons/plus.svg)**Create new**.
  1. Enter a name for the connection, e.g., `storage-connection`.
  1. Select the **Object Storage** connection type and specify the **Connection type parameters**.
  1. In the **Bucket auth** field, select `Private` and set these properties:

     * **Cloud and Folder**: `data-folder`.
     * **Bucket**: Select the bucket you created.
     * **Service account**: `yq-sa`.
  
  1. Click **Create**.

{% endlist %}

### Create a data binding {#create-binding}

A data binding contains information about file formats and locations in the bucket, as well as a list of data fields and their types.

To create a data binding:

{% 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 **Bindings**.
  1. Click ![info](../../_assets/console-icons/plus.svg) **Create**.
  1. Under **Connection parameters**:

     * **Type**: Select **Object Storage**.
     * **Connection**: Select `storage-connection`.

  1. Under **Binding parameters**:
  
     1. Enter a name for the binding, e.g., `visits`.
     1. In the **Path** field, specify the [path to the table](../../query/sources-and-sinks/object-storage.md#path_format) in the bucket: `/visits.csv`.
  
  1. Under **Format settings**:
     
     * **Format**: Select `csv_with_names`.
     * **Delimiter {%secondary%}(optional){%secondary%}**: `|`.

  1. Under **Columns**, add three columns with the following names and data types:

     * `date`: `DATETIME`
     * `person_id`: `INT32`
     * `items_id`: `INT32`
  
  1. To verify the data you specified is correct, click **Preview**. Your table should appear below.
  1. Click **Create**.

{% endlist %}

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

```sql
%yq SELECT * FROM visits LIMIT 100;
```

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

For operations with Managed Service for ClickHouse® data, you will need a cluster with a table and a Query connection.

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

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 {#ch-data}

The Managed Service for ClickHouse® table will contain the item names.

{% 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, run the following SQL queries one by one:

     ```sql
     CREATE TABLE items(id INT, description VARCHAR) ENGINE=MergeTree ORDER BY id;
     ```
 
     ```sql
     INSERT INTO items(id, description) VALUES(1, 'Refrigerator')
     ```
 
     ```sql
     INSERT INTO items(id, description) VALUES(2, 'TV')
     ```

  1. Click **Execute**.

{% endlist %}

### Create a connection to Managed Service for ClickHouse® {#yq-ch}

{% 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, run a query in the notebook cell. For example:

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

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

For operations with Managed Service for PostgreSQL data, you will need a cluster with a table and a Query connection.

### Create a Managed Service for PostgreSQL cluster {#cluster-pg}

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 {#table-pg}

The Managed Service for PostgreSQL table will contain the names of the customers.

{% 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, run the following SQL queries one by one:

     ```sql
     CREATE TABLE persons(person_id INT, name VARCHAR);
     ```
 
     ```sql
     INSERT INTO persons(person_id, name) values(1, 'Ivan')
     ```
 
     ```sql
     INSERT INTO persons(person_id, name) values(2, 'Peter')
     ```

{% endlist %}

### Create a connection to Managed Service for PostgreSQL {#yq-pg}

{% 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, run a query in the notebook cell. For example:

```sql
%yq SELECT * FROM postgresql.persons
```

## Run a federated query {#federate-query}

Operations with federated data sources (cross-service analytics) are no different than those with regular data sources. You can simultaneously access external data sources, such as tables in the DB and data in Object Storage, from a query and perform any allowed YQL operations on them.

To combine and get data from all the three tables, run a federated query in a notebook cell:

```sql
%%yq SELECT
    v.person_id,
    date,
    p.name,
    c.description
FROM visits AS v
    INNER JOIN postgresql.persons AS p ON
        v.person_id = p.person_id
    INNER JOIN clickhouse.items AS c ON
        v.item_id = c.id
```

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

To stop paying for the resources you created:

* [Delete the bucket](../../storage/operations/buckets/delete.md).
* [Delete the Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-delete.md).
* [Delete the Managed Service for PostgreSQL cluster](../operations/cluster-delete.md).
* [Delete the project](../../datasphere/operations/projects/delete.md).

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