[Yandex Cloud documentation](../../index.md) > [Yandex Query](../index.md) > [Tutorials](index.md) > Federated data queries

# Federated queries

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](../../managed-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](../../managed-postgresql/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](../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](../concepts/glossary.md#connection), and a data [binding](../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](../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](../../managed-postgresql/operations/cluster-delete.md).
* [Delete the project](../../datasphere/operations/projects/delete.md).

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