# Exchanging data with Yandex Managed Service for ClickHouse®

# 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).


## Required paid resources {#paid-resources}

The support cost for this solution includes:

* Yandex Data Processing cluster fee: Covers the use of VM computing resources, Compute Cloud network disks, and Cloud Logging for log management (see [Yandex Data Processing pricing](../pricing.md)).
* Managed Service for ClickHouse® cluster fee: Covers the use of computing resources allocated to hosts (including ZooKeeper hosts) and disk space (see [Managed Service for ClickHouse® pricing](../../managed-clickhouse/pricing.md)).
* Fee for a NAT gateway (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).
* Object Storage bucket fee, which covers data storage and data operations (see [Object Storage pricing](../../storage/pricing.md)).
* Fee for public IP addresses assigned to cluster hosts (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-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` (`Any`)
            * **Source**/**Destination name**: `Security group`
            * **Security group**: `Current` (`Self`)

        * 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](../operations/cluster-create.md) with the [host configuration of your choice](../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](../../managed-clickhouse/operations/cluster-create.md) of any suitable [configuration](../../managed-clickhouse/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 using security groups, make sure they are [configured correctly](../../managed-clickhouse/operations/connect/index.md#configuring-security-groups) and allow connections to your Managed Service for ClickHouse® cluster.


- 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](../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 %}

## 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](../../managed-clickhouse/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](../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](../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](../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](../../managed-clickhouse/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](../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](../../managed-clickhouse/operations/cluster-delete.md)
        1. [Yandex Data Processing cluster](../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)._