[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for ClickHouse®](../index.md) > [Tutorials](index.md) > Exchanging data with Yandex Data Processing

# Exchanging data with Yandex Data Processing

# Exchanging data between Yandex Managed Service for ClickHouse® and Yandex Data Processing


With Yandex Data Processing, you can:

* [Upload data from Managed Service for ClickHouse® to Spark DataFrame](#export-from-mch).
* [Export data from Spark DataFrame to Managed Service for ClickHouse®](#import-to-mch).

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

## Getting started {#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}

* Yandex Data Processing cluster: use of computing resources with a Yandex Data Processing markup, use of network drives, retrieval and storage of logs, amount of outgoing traffic (see [Yandex Data Processing pricing](../../data-proc/pricing.md)).
* Managed Service for ClickHouse® cluster: use of computing resources allocated to hosts, storage and backup size (see [Managed Service for ClickHouse® pricing](../pricing.md)).
* Public IP addresses if public access is enabled for cluster hosts (see [Yandex Virtual Private Cloud pricing](../../vpc/pricing.md)).
* NAT gateway: hourly use of the gateway and its outgoing traffic (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).
* Yandex Object Storage buckets: use of storage, data operations (see [Object Storage pricing](../../storage/pricing.md)).

## Set up your infrastructure {#infra}

{% list tabs group=instructions %}

- Manually {#manual}

    1. [Create a service account](../../iam/operations/sa/create.md) named `dataproc-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. If you are using security groups, [create one](../../vpc/operations/security-group-create.md) named `dataproc-sg` in the `dataproc-network` and add 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:

        * Components:
            * **SPARK**
            * **YARN**
            * **HDFS**
        * **Service account**: `dataproc-sa`.
        * **Bucket name**: Bucket you created for the output data.
        * **Network**: `dataproc-network`.
        * **Security groups**: `dataproc-sg`.

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

        * Database: `db1`.
        * User: `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 %}

    
    1. If you are using security groups in a Managed Service for ClickHouse® cluster, make sure they are [configured correctly](../operations/connect/index.md#configuring-security-groups) and allow connections to it.


- Terraform {#tf}

    1. If you do not have Terraform yet, [install it](../../tutorials/infrastructure-management/terraform-quickstart.md#install-terraform).
    1. [Get the authentication credentials](../../tutorials/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](../../tutorials/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](../../tutorials/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 [data-proc-data-exchange-with-mch.tf](https://github.com/yandex-cloud-examples/yc-data-proc-clickhouse-data-exchange/blob/main/data-proc-data-exchange-with-mch.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 `data-proc-data-exchange-with-mch.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. Make sure the Terraform configuration files are correct 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 %}

## Export data from Managed Service for ClickHouse® {#export-from-mch}

### Create a table in the Managed Service for ClickHouse® cluster {#prepare-mch}

1. [Connect to the `db1` database](../operations/connect/clients.md) in the Managed Service for ClickHouse® cluster as `user1`.
1. Populate the database with test data. In this example, we will use a simple table containing people's names and ages.

    1. Create a table:

        ```sql
        CREATE TABLE persons (
            `name` String,
            `age` UInt8) ENGINE = MergeTree ()
        ORDER BY
            `name`;
        ```

    1. Populate the table with data:

        ```sql
        INSERT INTO persons VALUES
           ('Anna', 19),
           ('Michael', 65),
           ('Alvar', 28),
           ('Lilith', 50),
           ('Max', 27),
           ('Jaimey', 34),
           ('Dmitry', 42),
           ('Qiang', 19),
           ('Augustyna', 20),
           ('Maria', 28);
        ```

    1. Check the result:

        ```sql
        SELECT * FROM persons;
        ```

### Export the table from Managed Service for ClickHouse® {#start-mch-export}

1. Prepare a script file:

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

        {% cut "ch-to-dataproc.py" %}

        ```python
        from pyspark.sql import SparkSession

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

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

        # Transferring the persons table from ClickHouse® to DataFrame
        df = spark.read.format("jdbc") \
        .option("url", jdbcUrl) \
        .option("user","user1") \
        .option("password","<user1_password>") \
        .option("dbtable","persons") \
        .load()

        # Transferring DataFrame to the bucket for validation
        df.repartition(1).write.mode("overwrite") \
        .csv(path='s3a://<output_bucket_name>/csv', header=True, sep=',')
        ```

        {% endcut %}

    1. In your script, specify the following:

        * Managed Service for ClickHouse® cluster ID.
        * `user1` password.
        * Output bucket name.

    1. In the input bucket, create a folder named `scripts` and [upload](../../storage/operations/objects/upload.md#simple) the `ch-to-dataproc.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/ch-to-dataproc.py`.

1. Wait for the job to complete and verify that the output bucket's `csv` folder contains the exported table.

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

## Import data to Managed Service for ClickHouse® {#import-to-mch}

1. Prepare a script file:

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

        {% cut "dataproc-to-ch.py" %}

        ```python
        from pyspark.sql import SparkSession
        from pyspark.sql.types import *

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

        # Creating a data schema
        schema = StructType([StructField('name', StringType(), True),
        StructField('age', IntegerType(), True)])

        # Creating DataFrame
        df = spark.createDataFrame([('Alim', 19),
                                    ('Fred' ,65),
                                    ('Guanmin' , 28),
                                    ('Till', 60),
                                    ('Almagul', 27),
                                    ('Mary', 34),
                                    ('Dmitry', 42)], schema)

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

        # Transferring DataFrame to ClickHouse®
        df.write.format("jdbc") \
        .mode("error") \
        .option("url", jdbcUrl) \
        .option("dbtable", "people") \
        .option("createTableOptions", "ENGINE = MergeTree() ORDER BY age") \
        .option("user","user1") \
        .option("password","<ClickHouse®_database_password>") \
        .save()
        ```

        {% endcut %}

    1. In your script, specify the following:

        * Managed Service for ClickHouse® cluster ID.
        * `user1` password.

    1. In the input bucket, create a folder named `scripts` and [upload](../../storage/operations/objects/upload.md#simple) the `dataproc-to-ch.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/dataproc-to-ch.py`.

1. Wait for the job to complete and verify that the data has been transferred to Managed Service for ClickHouse®:

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

        ```sql
        SELECT * FROM people;
        ```

    If the import is successful, the query will return the table contents.

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

## 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. Delete the other resources depending on how you created them:

    {% list tabs group=instructions %}

    - Manually {#manual}

        1. [Managed Service for ClickHouse® cluster](../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)._