[Yandex Cloud documentation](../../index.md) > [Yandex Object Storage](../index.md) > [Tutorials](index.md) > Getting statistics on object queries using Yandex Query

# Getting statistics on queries to Object Storage objects using Yandex Query

# Getting statistics on queries to Yandex Object Storage objects using Yandex Query


In this tutorial, you will learn how to get statistics on queries to [Yandex Object Storage](../index.md) objects using [Yandex Query](../../query/index.md). You will create a bucket and configure logging in Object Storage, set up a connection in Query, and get statistics using SQL queries.

To get statistics:

1. [Get your cloud ready](#before-you-begin).
1. [Create buckets](#create-buckets).
1. [Enable logging](#enable-logging).
1. [Configure connections in Query](#create-connection).
1. [Get statistics on queries](#get-statistics).

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

## Get your cloud ready {#before-you-begin}

Sign up for Yandex Cloud and create a [billing account](../../billing/concepts/billing-account.md):
1. Navigate to the [management console](https://console.yandex.cloud) and log in to Yandex Cloud or create a new account.
1. On the **[Yandex Cloud Billing](https://center.yandex.cloud/billing/accounts)** page, make sure you have a billing account linked and it has the `ACTIVE` or `TRIAL_ACTIVE` [status](../../billing/concepts/billing-account-statuses.md). If you do not have a billing account, [create one](../../billing/quickstart/index.md) and [link](../../billing/operations/pin-cloud.md) a cloud to it.

If you have an active billing account, you can create or select a [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) for your infrastructure on the [cloud page](https://console.yandex.cloud/cloud).

[Learn more about clouds and folders here](../../resource-manager/concepts/resources-hierarchy.md).

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

The infrastructure support cost includes:

* Fee for data storage  (see [Yandex Object Storage pricing](../pricing.md)).
* Fee for the amount of read data (see [Yandex Query pricing](../../query/pricing.md)).

### Create a service account {#create-sa}

With a service account, Query will be able to send queries to Object Storage.

[Create](../../iam/operations/sa/create.md) a service account named `yq-sa` and [assign](../../iam/operations/roles/grant.md) the `storage.viewer` and `yq.editor` roles to it.

## Create buckets {#create-buckets}

Create two buckets: `object-bucket` and `logs-bucket`. One bucket will serve as a data source, and the other, as a log storage. 

To create a bucket:

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the folder where you want to create a bucket.
  1. Navigate to **Object Storage**.
  1. At the top right, click **Create bucket**.
  1. In the ** Name** field, enter a name for the bucket.
  1. Click **Create bucket**.

  Similarly, create a bucket named `logs-bucket`.

{% endlist %}

## Enable logging {#enable-logging}

To get information on queries to objects, enable [logging actions on the bucket](../concepts/server-logs.md):

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the folder where you created the bucket.
  1. Navigate to **Object Storage**.
  1. Select the bucket.
  1. [Enable logging](../operations/buckets/enable-logging.md#enable):

      1. In the left-hand panel, select ![image](../../_assets/console-icons/wrench.svg) **Settings**.
      1. Navigate to the **Logging** tab.
      1. Enable **Write logs**.
      1. In the **Bucket for log storage** field, select `logs-bucket`.
      1. Click **Save**.

  1. In the left-hand panel, select ![image](../../_assets/console-icons/folder-tree.svg) **Objects** and [upload](../operations/objects/upload.md) your objects. For example, you can upload a couple of simple text files.

{% endlist %}

## Configure connections in Query {#create-connection}

To get data from Object Storage, create a [connection](../../query/concepts/glossary.md#connection) and [binding](../../query/concepts/glossary.md#binding):

{% list tabs group=instructions %}

- Yandex Query interface {#console}

  1. Go to [Query](https://yq.yandex.cloud/).
  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., `bucket-logs-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**: Select the cloud and folder where you created your buckets.
      * **Bucket**: `logs-bucket`.
      * **Service account**: `yq-sa`.

  1. Click **Check**. After a successful test, click **Create**.
  1. Click **Automatically fill settings for** and select **Object Storage Access Logs** from the drop-down list.

      1. Enter a name for the binding, e.g., `bucket-logs-binding`.
      1. In the **Path** field, specify the path to the statistics within the bucket. If the statistics are stored in the bucket root directory, specify `/`.
      1. Click **Preview** to verify the settings are correct.
      1. Click **Create** to complete creating the binding.

{% endlist %}

## Get statistics on queries {#get-statistics}

Use a connection to create SQL queries and get statistics on queries to Object Storage objects:

{% list tabs group=instructions %}

- Yandex Query interface {#console}

  1. Go to [Query](https://yq.yandex.cloud/).
  1. In the left-hand panel, select **Connections**.
  1. Select `bucket-logs-connection`.
  1. In the editor on the right, enter this query:

      ```sql
      SELECT `timestamp`, request_id, handler, object_key, status, request_time
      FROM `bucket-logs-binding`
      LIMIT 100;
      ```

      This query will return 100 records of statistics on queries to Object Storage objects. You can remove the record limit and filter results using `WHERE`.

  1. Click ![image](../../_assets/console-icons/play-fill.svg) **Run** and check the results.

{% endlist %}

### Query examples {#examples}

#### Searching for queries by response code {#request-code}

```sql
SELECT `timestamp`, request_id, handler, object_key, status, request_time
FROM `bucket-logs-binding`
WHERE status >= 400
```

#### Searching for long-running queries {#request-long}

```sql
SELECT `timestamp`, request_id, handler, object_key, status, request_time
FROM `bucket-logs-binding`
WHERE request_time >= 1000
```

#### Average query processing time {#request-average}

This tutorial features the `AVG` aggregate function.

```sql
SELECT AVG(request_time) AS `avg` FROM `bucket-logs-binding`
```

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

To shut down the infrastructure and stop paying for the resources you created:

1. [Delete](../../query/operations/binding.md#delete) the binding.
1. [Delete](../../query/operations/connection.md#delete) the connection.
1. [Delete](../operations/objects/delete.md) the objects from the buckets.
1. [Delete](../operations/buckets/delete.md) the buckets.