[Yandex Cloud documentation](../../index.md) > [Yandex DataLens](../index.md) > [Tutorials](index.md) > Service analytics > Analyzing Object Storage logs using DataLens # Analyzing Object Storage logs using DataLens For a Yandex Object Storage bucket, you can enable [action logging](../../storage/concepts/server-logs.md). Logs store information on operations with a [bucket](../../storage/concepts/bucket.md) and the [objects](../../storage/concepts/object.md) in it. For example, analyzing bucket logs can reveal what caused a load spike or show the overall traffic distribution. You can create visualizations for your analysis using [Yandex DataLens](../index.md). First, transfer the logs you saved to the ClickHouse® database that will be used as a source for DataLens. To analyze logs and output the results as interactive charts, follow these steps: 1. [Get your cloud ready](#before-you-begin). 1. [Create a bucket for storing logs](#create-bucket). 1. [Enable log export](#logs-export). 1. [Get the data source ready](#prepare-origin). 1. [Create a connection in DataLens](#create-connection). 1. [Create a dataset in DataLens](#create-dataset). 1. [Create charts in DataLens](#create-charts). 1. [Create a dashboard in DataLens](#create-dashboard). 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 cost includes: * Fee for data storage in Object Storage, data operations, and outbound traffic (see [Object Storage pricing](../../storage/pricing.md)). * Fee for a continuously running Managed Service for ClickHouse® cluster (see [Managed Service for ClickHouse® pricing](../../managed-clickhouse/pricing.md)). ## Create a bucket for storing logs {#create-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. Click **Create bucket**. 1. In the ** Name** field, enter a name for the bucket. 1. In the **Read objects** and **Read object list** fields, select **With authorization**. 1. Click **Create bucket**. - AWS CLI {#cli} 1. If you do not have the AWS CLI yet, [install and configure it](../../storage/tools/aws-cli.md). 1. Create a bucket: ```bash aws --endpoint-url https://storage.yandexcloud.net \ s3 mb s3:// ``` Result: ```text make_bucket: ``` - Terraform {#tf} {% note info %} If you access Object Storage via Terraform under a [service account](../../iam/concepts/users/service-accounts.md), [assign](../../iam/operations/sa/assign-role-for-sa.md) to the service account the relevant [role](../../storage/security/index.md#roles-list), e.g., `storage.admin`, for the folder you are going to create the resources in. {% endnote %} With [Terraform](https://www.terraform.io/), you can quickly create a cloud infrastructure in Yandex Cloud and manage it using configuration files. These files store the infrastructure description written in HashiCorp Configuration Language (HCL). If you change the configuration files, Terraform automatically detects which part of your configuration is already deployed, and what should be added or removed. Terraform is distributed under the [Business Source License](https://github.com/hashicorp/terraform/blob/main/LICENSE). The [Yandex Cloud provider for Terraform](https://github.com/yandex-cloud/terraform-provider-yandex) is distributed under the [MPL-2.0](https://www.mozilla.org/en-US/MPL/2.0/) license. For more information about the provider resources, see the guides on the [Terraform](https://www.terraform.io/docs/providers/yandex/index.html) website or [its mirror](../../terraform/index.md). If you do not have Terraform yet, [install it and configure the Yandex Cloud provider](../../tutorials/infrastructure-management/terraform-quickstart.md#install-terraform). To manage infrastructure using Terraform under a service account or user accounts (a Yandex account, a federated account, or a local user), [authenticate](../../terraform/authentication.md) using the appropriate method. 1. Describe the properties for creating a service account and access key in the configuration file: ```hcl ... // Creating a service account resource "yandex_iam_service_account" "sa" { name = "" } // Assigning a role to a service account resource "yandex_resourcemanager_folder_iam_member" "sa-admin" { folder_id = "" role = "storage.admin" member = "serviceAccount:${yandex_iam_service_account.sa.id}" } // Creating a static access key resource "yandex_iam_service_account_static_access_key" "sa-static-key" { service_account_id = yandex_iam_service_account.sa.id description = "static access key for object storage" } ``` 1. Add the bucket properties to the configuration file: ```hcl resource "yandex_storage_bucket" "bucket-logs" { access_key = yandex_iam_service_account_static_access_key.sa-static-key.access_key secret_key = yandex_iam_service_account_static_access_key.sa-static-key.secret_key bucket = "" } ``` For more information about the `yandex_storage_bucket` resource, see [this Terraform provider guide](../../terraform/resources/storage_bucket.md). 1. Make sure the settings are correct. 1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure. 1. Run this command: ```bash terraform validate ``` Terraform will show any errors found in your configuration files. 1. Create a bucket. 1. Run this command to view the planned changes: ```bash terraform plan ``` If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources. 1. If everything looks correct, apply the changes: 1. Run this command: ```bash terraform apply ``` 1. Confirm updating the resources. 1. Wait for the operation to complete. - API {#api} Use the [create](../../storage/s3/api-ref/bucket/create.md) REST API method. {% endlist %} ## Enable log export {#logs-export} {% list tabs group=instructions %} - Management console {#console} 1. In the [management console](https://console.yandex.cloud), select the bucket you want to enable logging for. 1. In the left-hand panel, select **Settings**. 1. Open the **Logging** tab. 1. Enable **Write logs**. 1. Select **Bucket for log storage**. 1. In the **Prefix** field, specify the `s3-logs/` prefix. 1. Click **Save**. - AWS CLI {#cli} 1. Create a file named `log-config.json` with the following contents: ```json { "LoggingEnabled": { "TargetBucket": "", "TargetPrefix": "s3-logs/" } } ``` 1. Run this command: ```bash aws s3api put-bucket-logging \ --endpoint-url https://storage.yandexcloud.net \ --bucket \ --bucket-logging-status file://log-config.json ``` Where `--bucket` is the name of the bucket to enable action logging for. - Terraform {#tf} To enable logging for a bucket you want to track: 1. Open the Terraform configuration file and add the `logging` section to the bucket description. ```hcl resource "yandex_storage_bucket" "bucket-logs" { access_key = "" secret_key = "" bucket = "" } resource "yandex_storage_bucket" "bucket" { access_key = "" secret_key = "" bucket = "" acl = "private" logging { target_bucket = yandex_storage_bucket.bucket-logs.id target_prefix = "s3-logs/" } } ``` Where: * `access_key`: Static access key ID. * `secret_key`: Secret access key value. * `target_bucket`: Bucket for storing logs. * `target_prefix`: [Key prefix](../../storage/concepts/server-logs.md#key-prefix) for objects with logs. For more information about the `yandex_storage_bucket` properties in Terraform, see [this provider guide](../../terraform/resources/storage_bucket.md#enable-logging). 1. In the terminal, navigate to the configuration file directory. 1. Make sure the configuration is correct using this command: ```bash terraform validate ``` If the configuration is valid, you will get this message: ```bash Success! The configuration is valid. ``` 1. Run this command: ```bash terraform plan ``` You will see a list of resources and their properties. No changes will be made at this step. Terraform will show any errors in the configuration. 1. Apply the configuration changes: ```bash terraform apply ``` 1. Type `yes` and press **Enter** to confirm the changes. This will create all the resources you need in the specified folder. You can check the new resources and their settings using the [management console](https://console.yandex.cloud). - API {#api} Use the [putBucketLogging](../../storage/s3/api-ref/bucket/putBucketLogging.md) REST API method. {% endlist %} ## Get the data source ready {#prepare-origin} ### Create a ClickHouse® cluster {#create-ch-cluster} To create a Managed Service for ClickHouse® cluster, you need the [vpc.user](../../vpc/security/index.md#vpc-user) role along with the [managed-clickhouse.editor role or higher](../../managed-clickhouse/security.md#roles-list). For more information on assigning roles, see [this Identity and Access Management guide](../../iam/operations/roles/grant.md). {% 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 cluster. 1. Navigate to **Managed Service for ClickHouse**. 1. In the window that opens, click **Create cluster**. 1. Configure your ClickHouse® cluster: 1. Under **Basic parameters**, specify `s3-logs` in the **Cluster name** field. 1. Under **Resources**, select `burstable` in the **Type** field. 1. Under **Hosts**, click ![image](../../_assets/console-icons/pencil.svg) and enable the **Public access** option. Click **Save**. {% note info %} Public access to cluster hosts is required if you plan to connect to the cluster via the internet. This connection option is simpler and is recommended for the purposes of this guide. You can connect to non-public hosts as well but only from Yandex Cloud virtual machines located in the same cloud network as the cluster. {% endnote %} 1. Under **DBMS settings**: * In the **User management via SQL** field, select `Disabled`. * In the **Username** field, specify `user`. * In the **Password** field, set a password. * In the **DB name** field, specify `s3_data`. Memorize the database name. 1. Under **Service settings**, enable these options: * **DataLens access**. * **Access from the management console**. 1. Click **Create cluster**. - Yandex Cloud CLI {#cli} If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install). The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id ` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions. To create a cluster: 1. Check whether the folder has any subnets for cluster hosts: ```bash yc vpc subnet list ``` If there are no subnets in the folder, [create them](../../vpc/operations/subnet-create.md) in VPC. 1. Specify the cluster properties in the creation command: ```bash yc managed-clickhouse cluster create \ --name s3-logs \ --environment production \ --network-name \ --host type=clickhouse,zone-id=,subnet-id= \ --clickhouse-resource-preset b2.medium \ --clickhouse-disk-type network-ssd \ --clickhouse-disk-size 10 \ --user name=user,password= \ --database name=s3_data \ --datalens-access=true \ --websql-access=true ``` - Terraform {#tf} 1. Add descriptions of your cluster, database, and user to the configuration file: ```hcl resource "yandex_mdb_clickhouse_cluster_v2" "s3-logs" { name = "s3-logs" environment = "PRODUCTION" network_id = yandex_vpc_network..id clickhouse = { resources = { resource_preset_id = "b2.medium" disk_type_id = "network-ssd" disk_size = 10 } } hosts = { "ch-host1" = { type = "CLICKHOUSE" zone = "" subnet_id = yandex_vpc_subnet..id shard_name = "shard1" } } shards = { "shard1" = {} } access = { data_lens = true web_sql = true } maintenance_window { type = "ANYTIME" } } resource "yandex_mdb_clickhouse_database" "s3-data" { cluster_id = yandex_mdb_clickhouse_cluster_v2.s3-logs.id name = "s3_data" } resource "yandex_mdb_clickhouse_user" "user1" { cluster_id = yandex_mdb_clickhouse_cluster_v2.s3-logs.id name = "user" password = "" permission { database_name = yandex_mdb_clickhouse_database.s3-data.name } } ``` For more information about the resources you can create with Terraform, see [this provider guide](../../terraform/resources/mdb_clickhouse_cluster.md). 1. Make sure the settings are correct. 1. In the command line, navigate to the directory that contains the current Terraform configuration files defining the infrastructure. 1. Run this command: ```bash terraform validate ``` Terraform will show any errors found in your configuration files. 1. Create a cluster: 1. Run this command to view the planned changes: ```bash terraform plan ``` If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources. 1. If everything looks correct, apply the changes: 1. Run this command: ```bash terraform apply ``` 1. Confirm updating the resources. 1. Wait for the operation to complete. - API {#api} Use the [create](../../managed-clickhouse/api-ref/Cluster/create.md) REST API method. {% endlist %} After creating the cluster, you will be automatically redirected to the **Clusters** page. Wait until the cluster status switches to `Alive`. ### Change the user settings {#user-settings} {% list tabs group=instructions %} - Management console {#console} 1. Select the `s3-logs` cluster. 1. Navigate to the **Users** tab. 1. Click ![image](../../_assets/console-icons/ellipsis.svg) and select **Configure**. 1. Click **Additional settings** → **Settings**. 1. In the **Date time input format** field, select `best_effort`. 1. Click **Save**. {% endlist %} ### Create a static key {#create-static-key} You need a static key to create a table with access to Object Storage. [Create one](../../iam/operations/authentication/manage-access-keys.md#create-access-key) and save its ID and secret part. ### Create a table in the database {#create-table} {% list tabs group=instructions %} - Management console {#console} 1. Select the `s3-logs` cluster. 1. Navigate to the **SQL** tab. 1. In the **Password** field, enter the password. 1. Click **Connect**. 1. In the window on the right, write this SQL query: ```sql CREATE TABLE s3_data.s3logs ( bucket String, -- Bucket name. bytes_received Int64, -- Query size in bytes. bytes_send Int64, -- Response size in bytes. handler String, -- Query method in this format: REST... http_referer String, -- Query source URL. ip String, -- User IP address. method String, -- HTTP query method. object_key String, -- Object key in URL-encoded format. protocol String, -- Data transfer protocol version. range String, -- HTTP header defining the byte range to load from the object. requester String, -- User ID. request_args String, -- Arguments of the URL query. request_id String, -- Query ID. request_path String, -- Full query path. request_time Int64, -- Query processing time in milliseconds. scheme String, -- Data transfer protocol type. -- The possible values are as follows: -- * http: Application layer protocol. -- * https: Application layer protocol with encryption support. ssl_protocol String, -- Security protocol. status Int64, -- HTTP response code. storage_class String, -- Object storage class. timestamp DateTime, -- Date and time of the bucket operation in YYYY-MM-DDTHH:MM:SSZ format. user_agent String, -- Client app (user agent) that run the query. version_id String, -- Object version. vhost String -- Virtual host of the query. -- The possible values are as follows: -- * storage.yandexcloud.net. -- * .storage.yandexcloud.net. -- * website.yandexcloud.net. -- * .website.yandexcloud.net. ) ENGINE = S3( 'https://storage.yandexcloud.net//s3-logs/*', '', '', 'JSONEachRow' ) SETTINGS date_time_input_format='best_effort'; ``` 1. Click **Execute**. {% endlist %} ## Create a connection in DataLens {#create-connection} {% list tabs group=instructions %} - Management console {#console} 1. Select the `s3-logs` cluster. 1. Navigate to the **DataLens** tab. 1. In the window that opens, click **Create connection**. 1. Set up your connection: 1. Add a connection name: `s3-logs-con`. 1. In the **Cluster** field, select `s3-logs`. 1. In the **Host name** field, select the ClickHouse® host from the drop-down list. 1. Enter the database username and password. 1. Click **Confirm connection**. 1. After checking the connection, click **Create connection**. 1. In the window that opens, enter a name for the connection and click **Create**. {% endlist %} ## Create a dataset in DataLens {#create-dataset} 1. Click **Create dataset**. 1. In the new dataset, move the `s3_data.s3logs` table to the workspace. 1. Navigate to the **Fields** tab. 1. Click ![image](../../_assets/console-icons/plus.svg) **Add field**. 1. Create a calculated field with the file type: * Field name: `object_type` * Formula: `SPLIT([object_key], '.', -1)` 1. Click **Create**. 1. In the top-right corner, click **Save**. 1. Enter `s3-dataset` for the dataset name and click **Create**. 1. Once the dataset is saved, click **Create chart** in the top-right corner. ## Create charts in DataLens {#create-charts} ### Create the first chart {#create-pie-chart} To visualize the number of queries against a bucket via different methods, create a pie chart: 1. Select `Pie chart` as the visualization type. 1. Drag the `method` field from the **Dimensions** section to the **Colors** section. 1. Drag the `request_id` field from the **Dimensions** section to the **Measures** section. 1. In the top-right corner, click **Save**. 1. In the window that opens, enter `S3 - Method pie` for the new chart name and click **Save**. ### Create the second chart {#create-column-chart} To visualize the number of queries ratio by object type, create a bar chart: 1. Copy the chart from the previous step: 1. In the top-right corner, click the check mark next to the **Save** button. 1. Click **Save as**. 1. In the window that opens, enter `S3 - Object type bars` for the new chart name and click **Save**. 1. Select **Bar chart** as the visualization type. The `method` and `request_id` fields will automatically appear in the **X** and **Y** sections, respectively. 1. Delete the `method` field from the **X** section and drag the `object_type` field there. 1. In the top-right corner, click **Save**. ### Create the third chart {#create-column-chart-2} To visualize the distribution of outbound traffic by day, create a bar chart: 1. Copy the chart from the previous step: 1. In the top-right corner, click the check mark next to the **Save** button. 1. Click **Save as**. 1. In the window that opens, enter `S3 - Traffic generated by days` for the new chart name and click **Save**. 1. Drag the `object_type` field from the **X** section to the **Filters** section. 1. In the window that opens, select the types of objects to display in the chart and click **Apply filters**. 1. Drag the `timestamp` field from the **Dimensions** section to the **X** section. 1. Delete the `request_id` field from the **Y** section and drag the `bytes_send` field there. 1. In the top-right corner, click **Save**. ## Create a dashboard in DataLens and add charts to it {#create-dashboard} 1. In the left-hand panel, click ![image](../../_assets/console-icons/layout-cells-large.svg) **Dashboards**. 1. Click **Create dashboard**. 1. Enter `S3 Logs Analysis` for the dashboard name and click **Create**. 1. In the top-right corner, click **Add** and select `Chart`. 1. In the **Chart** field, click **Select** and select the `S3 - Method pie` chart from the list. 1. Click **Add**. You will now see the chart on the dashboard. 1. Repeat the previous steps for the `S3 - Object type bars` and `S3 - Traffic generated by days` charts. ## How to delete the resources you created {#clear-out} Delete the resources you no longer need to avoid paying for them: * [Delete the bucket](../../storage/operations/buckets/delete.md). * [Delete the `s3-logs` cluster](../../managed-clickhouse/operations/cluster-delete.md). _ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._