[Yandex Cloud documentation](../../index.md) > [Tutorials](../index.md) > [Building a data platform](index.md) > Importing, processing, and exporting Object Storage data to Managed Service for ClickHouse®

# Importing data from Yandex Object Storage, processing and exporting to Yandex Managed Service for ClickHouse®

#|
||This tutorial is based on a [Data Stories](https://data-stories.ru/) use case of building an analytical stack powered by Yandex Cloud services. The use case involved uploading data to storage, processing it, and transforming it into a single data mart for visualization.
|
<br>![datastories logo](../../_assets/logos/datastories_logo.png =300x)||
|#

This example uses two CSV tables. We will merge them into a single table, convert that table into Parquet format, and transfer it to Managed Service for ClickHouse®.


## Required paid resources {#paid-resources}

The support cost for this solution includes:

* Managed Service for ClickHouse® cluster fee: use of computing resources allocated to hosts (including ZooKeeper hosts) and disk space (see [Managed Service for ClickHouse® pricing](../../managed-clickhouse/pricing.md)).
* Yandex Data Processing cluster fee: use of VM computing resources and Compute Cloud network disks, as well as Cloud Logging for log management (see [Yandex Data Processing pricing](../../data-proc/pricing.md)).
* Fee for the use of public IP addresses for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).
* Object Storage bucket fee: data storage and data operations (see [Object Storage pricing](../../storage/pricing.md)).
* Fee for a NAT gateway (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).


## Getting started {#before-you-begin}

Set up your infrastructure:

{% list tabs group=instructions %}

- Manually {#manual}

    1. [Create a service account](../../iam/operations/sa/create.md) named `dataproc-s3-sa` and assign the `dataproc.agent` and `dataproc.provisioner` roles to it.
    1. In Object Storage, [create buckets](../../storage/operations/buckets/create.md) and [configure access](../../storage/operations/buckets/edit-acl.md) to them:
       
       1. Create a bucket for the input data and grant the `READ` permission for this bucket to the cluster service account.
       1. Create a bucket for the processing output and grant the cluster service account `READ and WRITE` permissions for this bucket.
    1. [Create a cloud network](../../vpc/operations/network-create.md) named `dataproc-network`.
    1. Within the `dataproc-network`, [create a subnet](../../vpc/operations/subnet-create.md) in any availability zone.
    1. [Set up a NAT gateway](../../vpc/operations/create-nat-gateway.md) for your new subnet.
    1. In `dataproc-network`, [create a security group](../../vpc/operations/security-group-create.md) named `dataproc-sg` with the following rules:

        * One inbound and one outbound rule for service traffic:

            * **Port range**: `0-65535`.
            * **Protocol**: `Any`.
            * **Source**/**Destination name**: `Security group`.
            * **Security group**: `Current`.

        * Rule for outgoing HTTPS traffic:

            * **Port range**: `443`.
            * **Protocol**: `TCP`.
            * **Destination name**: `CIDR`.
            * **CIDR blocks**: `0.0.0.0/0`.

        * Egress rule to allow TCP access to ClickHouse® on port 8443:

            * **Port range**: `8443`.
            * **Protocol**: `TCP.`
            * **Destination name**: `CIDR`.
            * **CIDR blocks**: `0.0.0.0/0`.

    1. [Create a Yandex Data Processing cluster](../../data-proc/operations/cluster-create.md) with the [host configuration of your choice](../../data-proc/concepts/instance-types.md) and the following settings:

        * **Environment**: `PRODUCTION`.
        * **Services**:
            * `SPARK`
            * `YARN`
            * `HDFS`
        * **Service account**: `dataproc-sa`.
        * **Bucket name**: Bucket you created for the output data.
        * **Network**: `dataproc-network`.
        * **Security groups**: `dataproc-sg`.
        * **UI Proxy**: Enabled.

    1. [Create a Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-create.md) of any suitable [configuration](../../managed-clickhouse/concepts/instance-types.md) with the following settings:

        * **DB name**: `db1`.
        * **Username**: `user1`.
        * Public access to cluster hosts: Enabled

            {% 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 %}

- Terraform {#tf}

    1. If you do not have Terraform yet, [install it](../infrastructure-management/terraform-quickstart.md#install-terraform).
    1. [Get the authentication credentials](../infrastructure-management/terraform-quickstart.md#get-credentials). You can add them to environment variables or specify them later in the provider configuration file.
    1. [Configure and initialize a provider](../infrastructure-management/terraform-quickstart.md#configure-provider). There is no need to create a provider configuration file manually, you can [download it](https://github.com/yandex-cloud-examples/yc-terraform-provider-settings/blob/main/provider.tf).
    1. Place the configuration file in a separate working directory and [specify the parameter values](../infrastructure-management/terraform-quickstart.md#configure-provider). If you did not add the authentication credentials to environment variables, specify them in the configuration file.

    1. Download the [s3-dataproc-ch.tf](https://github.com/yandex-cloud-examples/yc-data-proc-clickhouse-data-export/blob/main/s3-dataproc-ch.tf) configuration file to your current working directory.

        This file describes:

        * [Network](../../vpc/concepts/network.md#network).
        * [Subnet](../../vpc/concepts/network.md#subnet).
        * NAT gateway and route table for Yandex Data Processing.
        * [Security groups](../../vpc/concepts/security-groups.md) for the Yandex Data Processing and Managed Service for ClickHouse® clusters.
        * Service account for the Yandex Data Processing cluster.
        * Service account required to create buckets in Object Storage.
        * Buckets for input and output data.
        * Yandex Data Processing cluster.
        * Managed Service for ClickHouse® cluster.

    1. In the `s3-dataproc-ch.tf` file, specify the following:

        * `folder_id`: Cloud folder ID, same as in the provider settings.
        * `input-bucket`: Input data bucket name.
        * `output-bucket`: Output data bucket name.
        * `dp_ssh_key`: Absolute path to the public key for the Yandex Data Processing cluster. Learn more about connecting to a Yandex Data Processing host over SSH [here](../../data-proc/operations/connect-ssh.md).
        * `ch_password`: ClickHouse® password.

    1. Validate your Terraform configuration files using this command:

        ```bash
        terraform validate
        ```

        Terraform will display any configuration errors detected in your files.

    1. Create the required infrastructure:

        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.

        All the required resources will be created in the specified folder. You can check resource availability and their settings in the [management console](https://console.yandex.cloud).

{% endlist %}

## Prepare your test data {#prepare-data}

In this example, we will use two CSV tables:

* `coords.csv` contains the vehicle’s geographic coordinates.
* `sensors.csv` contains vehicle speed and operating parameters.

To prepare the test data:

1. Copy the contents of the sample files below and save them locally in CSV format:

    * {% cut "coords.csv" %}
        ```csv
        vehicle_id,latitude,longitude,altitude
        iv9a94th6rztooxh5ur2,55.70329032,37.65472196,427.5
        022wsiz48h2ljxuz04x8,56.96149325,38.46541766,423.6
        a7fbbqjws4zqw85f6jue,54.99296663,36.79063999,426.2
        l7731117m6r6on4m633n,55.34740545,37.13175678,422.5
        6f9q6ienc4qfpdwd9nef,56.69752218,38.38871530,428.3
        ```
      {% endcut %}
    * {% cut "sensors.csv" %}
        ```csv
        vehicle_id,speed,battery_voltage,cabin_temperature,fuel_level
        iv9a94th6rztooxh5ur2,0.0,25.5,17,5
        022wsiz48h2ljxuz04x8,55.5,54.5,21,22
        a7fbbqjws4zqw85f6jue,80.6,22.1,19,73
        l7731117m6r6on4m633n,40.9,76.0,25,23
        6f9q6ienc4qfpdwd9nef,64.8,90.8,21,32
        ```
      {% endcut %}

1. In the input bucket, create a folder named `csv` and [upload](../../storage/operations/objects/upload.md#simple) your CSV files to it.

## Run data processing in Yandex Data Processing {#process-data}

Merge two tables into one and upload the resulting table as a Parquet file to the processing results bucket you created earlier :

1. Prepare a script file:

    1. Create a local file named `join-tables.py` and paste the following script into it:

        {% cut "join-tables.py" %}
        ```python
        from pyspark.sql import SparkSession

        # Creating a Spark session
        spark = SparkSession.builder.appName("JoinExample").getOrCreate()

        # Reading a table from coords.csv
        coords_df = spark.read.csv("s3a://<input_bucket_name>/csv/coords.csv", header=True)

        # Reading a table from sensors.csv
        sensors_df = spark.read.csv("s3a://<input_bucket_name>/csv/sensors.csv", header=True)

        # Joining tables on the vehicle_id column
        joined_df = coords_df.join(sensors_df, on="vehicle_id", how="inner")

        # Saving the joined table to a bucket in Parquet format
        joined_df.write.parquet("s3a://<output_bucket_name>/parquet/")
        ```
        {% endcut %}

    1. In your script, specify the following:

        * Name of the input bucket containing original CSV tables.
        * Name of the output bucket for the Parquet file containing the joined tables.

    1. Create a folder named `scripts` in the input bucket and [upload](../../storage/operations/objects/upload.md#simple) the `join-tables.py` file to it.

1. [Create a PySpark job](../../data-proc/operations/jobs-pyspark.md#create) with the file path to your script specified in the **Main python file** field: `s3a://<input_bucket_name>/scripts/join-tables.py`.

1. Wait for the job to complete and make sure the output bucket's `parquet` folder contains the `part-00000-***` Parquet file.

{% note info %}

You can view the job logs and search data in them using [Yandex Cloud Logging](../../logging/index.md). For more information, see [Working with logs](../../data-proc/operations/logging.md).

{% endnote %}

## Export your data to ClickHouse® {#export-data}

Transfer the joined table from Object Storage to ClickHouse®:

1. Prepare a script file:

    1. Create a local file named `parquet-to-ch.py` and paste the following script into it:

        {% cut "parquet-to-ch.py" %}
        ```python
        from pyspark.sql import SparkSession

        # Creating a Spark session
        spark = SparkSession.builder.appName("ParquetClickhouse").getOrCreate()

        # Reading data from the Parquet file
        parquetFile = spark.read.parquet("s3a://<output_bucket_name>/parquet/*.parquet")

        # Specifying the port and other ClickHouse® cluster settings
        jdbcPort = 8443
        jdbcHostname = "c-<cluster_ID>.rw.mdb.yandexcloud.net"
        jdbcDatabase = "db1"
        jdbcUrl = f"jdbc:clickhouse://{jdbcHostname}:{jdbcPort}/{jdbcDatabase}?ssl=true"

        # Loading the table from your Parquet file into the ClickHouse® `measurements` table
        parquetFile.write.format("jdbc") \
        .mode("error") \
        .option("url", jdbcUrl) \
        .option("dbtable", "measurements") \
        .option("createTableOptions", "ENGINE = MergeTree() ORDER BY vehicle_id") \
        .option("user","user1") \
        .option("password","<ClickHouse®_user_password>") \
        .save()
        ```
        {% endcut %}

    1. In your script, specify the following:

        * Name of the bucket containing your Parquet file.
        * Managed Service for ClickHouse® cluster ID.
        * ClickHouse® password.

    1. [Upload](../../storage/operations/objects/upload.md#simple) the `parquet-to-ch.py` file to the input data bucket’s `scripts` folder.

1. [Create a PySpark job](../../data-proc/operations/jobs-pyspark.md#create) with the file path to your script specified in the **Main python file** field: `s3a://<input_bucket_name>/scripts/parquet-to-ch.py`.
1. Wait for the job to complete, then verify that the joined table has been transferred to the cluster:

    1. [Connect](../../managed-clickhouse/operations/connect/clients.md) to the `db1` database in the Managed Service for ClickHouse® cluster as `user1`.
    1. Run this query:

        ```sql
        SELECT * FROM measurements;
        ```

    If the data export is successful, you will receive the joined table in response.

## Delete the resources you created {#clear-out}

Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them:

1. [Delete all objects](../../storage/operations/objects/delete.md) from the buckets.
1. Delete the other resources depending on how you created them:

    {% list tabs group=instructions %}

    - Manually {#manual}

        1. [Managed Service for ClickHouse® cluster](../../managed-clickhouse/operations/cluster-delete.md).
        1. [Yandex Data Processing cluster](../../data-proc/operations/cluster-delete.md).
        1. [Object Storage buckets](../../storage/operations/buckets/delete.md).
        1. [Subnet](../../vpc/operations/subnet-delete.md).
        1. [Route table](../../vpc/operations/delete-route-table.md).
        1. [NAT gateway](../../vpc/operations/delete-nat-gateway.md).
        1. [Cloud network](../../vpc/operations/network-delete.md).
        1. [Service account](../../iam/operations/sa/delete.md).

    - Terraform {#tf}

        1. In the terminal window, go to the directory containing the infrastructure plan.
        
            {% note warning %}
        
            Make sure the directory has no Terraform manifests with the resources you want to keep. Terraform deletes all resources that were created using the manifests in the current directory.
        
            {% endnote %}
        
        1. Delete resources:
        
            1. Run this command:
        
                ```bash
                terraform destroy
                ```
        
            1. Confirm deleting the resources and wait for the operation to complete.
        
            All the resources described in the Terraform manifests will be deleted.

    {% endlist %}

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