[Yandex Cloud documentation](../../index.md) > [Yandex Query](../index.md) > [Tutorials](index.md) > Working with data in Object Storage

# Working with data in Yandex Object Storage

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. The service 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.

In this tutorial, you will connect to an Object Storage data source and run queries against it from the JupyterLab notebook using Query.

1. [Set up your infrastructure](#infra).
1. [Get started in Query](#yq-begin).
1. [Connect to Object Storage data](#storage-connect).
1. [Configure data partitioning in Object Storage](#partitioning).

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 Object Storage data includes:

* Fee for [DataSphere computing resource usage](../../datasphere/pricing.md).
* Fee for [storing data in a bucket](../../storage/pricing.md#prices-storage).
* Fee for the amount of data read 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.
     * `storage.viewer` to view the contents of the Object Storage bucket and objects.
  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}

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 Object Storage data {#storage-connect}

As an example, let's use data on New York Yellow Taxi rides. The data was pre-uploaded to a public Object Storage bucket named `yq-sample-data`, in the `nyc_taxi_csv` folder.

{% note info %}

Yandex Cloud provides the **New York City taxi trips** dataset as is. Yandex Cloud makes no representations, explicit or implicit, warranties, or conditions pertaining to your use of the specified dataset. To the extent allowed by your local laws, Yandex Cloud shall not be liable for any loss or damage, including direct, consequential, special, indirect, incidental, or exemplary, resulting from your use of the dataset.

NYC Taxi and Limousine Commission (TLC):

The data was collected and provided to the NYC Taxi and Limousine Commission (TLC) by technology providers authorized under the Taxicab & Livery Passenger Enhancement Programs (TPEP/LPEP). The taxi trip data is not generated by the TLC, and the TLC makes no representations whatsoever about the accuracy of this data.

Take a look at the [dataset source](https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page) and its [terms of use](https://www1.nyc.gov/home/terms-of-use.page).

{% endnote %}

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. Enter a name for the connection, e.g., `storage-connection`.
  1. Select the **Object Storage** connection type and specify the **Connection type parameters**:
  
     * In the **Bucket auth** field, select `Public` and specify the bucket name.
     * Name the bucket: `yq-sample-data`.
  
  1. Click **Create**.

{% endlist %}

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

A [data binding](../concepts/glossary.md#binding) contains information about file formats and locations in the bucket as well as about the list of data fields and their types. Query can process data in CSV, TSV, JSON, and Parquet formats. For data in CSV, TSV, and Json formats, you can additionally use an external archiver: `gzip`, `zstd`, and others. For data in Parquet format, you can use Snappy, LZ4, ZSTD, and other compression 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., `yq_binding`.
     1. In the **Path** field, specify the [path to the data](../sources-and-sinks/object-storage.md#path_format): `nyc_taxi_sample/data/`.
     1. Select the compression type: `gzip`.
  
  1. Under **Format settings**, select `csv_with_names` in the **Format** field.

  1. Under **Columns**, add two columns with these names and data types:

     * `tpep_pickup_datetime`: `DATETIME`
     * `trip_distance`: `DOUBLE`
  
  1. To verify the data you specified is correct, click **Preview**. A table should appear below.
  1. Click **Create**.

{% endlist %}

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

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

## Configure partitioning in Object Storage {#partitioning}

In Query, you can significantly reduce query time by setting up rules for [partitioning](../concepts/partitioning.md) (placing) data in Object Storage to only read the required data. For example, if files in a bucket are sorted in folders by year, Query will only look for data in the specified folders when queried.

Query supports two options for specifying data placement rules: [Hive partitioning](../concepts/partitioning.md#formats) and [Partition projection](../concepts/partition-projection.md).

### Hive partitioning {#hive}

In Apache Hive™, data is placed in a folder structure in this format: `key=value/key2=value2/...`. This format is used in systems originally built around the Apache Hadoop™ ecosystem, such as Apache Spark™.

For example, data from the Yellow Taxi dataset is placed in folders according to the Hive partitioning rules:

* `year=2021/month=1/...`
* `year=2021/month=2/...`
* ...
* `year=2022/month=1/...`
* `year=2022/month=2/...`

Configure Hive partitioning:

1. Clone the `yq_binding` 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, click **Bindings**.
     1. In the row with `yq_binding`, click ![ellipsis](../../_assets/console-icons/ellipsis.svg) and select **Clone**.
     1. Enter a name for the binding: `yq_tutorial_hive_partitioned`.
     1. Under **Partitioned columns**:

        1. Select **Basic**.
        1. Add a column named `year` with the `UINT32` data type.
        1. Add a column named `month` with the `UINT32` data type.

     1. Click **Modify**.
   
   {% endlist %}

1. To check the result, run this query:

   ```sql
   %yq SELECT * FROM yq_tutorial_hive_partitioned WHERE year=2021
   ```

### Partition projection {#explicit}

Sometimes data in Object Storage is placed according to unique rules. In such cases, you can use [partition projection](../concepts/partition-projection.md) to specify your own data path template.

When using partition projection, you need to describe each component of the `year` and `month` path separately, specifying the ranges of their values. Then you need to specify the path template in the Object Storage bucket that stores the data.

Configure partition projection:

1. Clone the `yq_binding` 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, click **Bindings**.
     1. In the row with `yq_binding`, click ![ellipsis](../../_assets/console-icons/ellipsis.svg) and select **Clone**.
     1. Enter a name for the binding: `yq_tutorial_partition_projection`.
     1. Under **Partitioned columns**:
        
        1. Select **Extended**.
        1. Add a column named `year` with the `UINT32` data type:
           * **Format type**: `integer`
           * **Interval**:
             * **Min**: `2019`
             * **Max**: `2024`
             * **Step**: 1
           * **Fixed width**: 2
        1. Add a column named `month` with the `UINT32` data type:
           * **Format type**: `integer`
           * **Interval**:
             * **Min**: `1`
             * **Max**: `12`
             * **Step**: 1
           * **Fixed width**: 2

     1. Click **Modify**.
   
   {% endlist %}

1. To check the result, run this query:

   ```sql
   %yq SELECT * FROM yq_tutorial_partition_projection WHERE year=2021
   ```

## 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 project](../../datasphere/operations/projects/delete.md).