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 data from Spark DataFrame to Managed Service for ClickHouse®.

If you no longer need the resources you created, delete them.

Getting startedGetting started

Prepare the infrastructure:

Upload data from Managed Service for ClickHouse®Upload data from Managed Service for ClickHouse®

Create a table in the Managed Service for ClickHouse® clusterCreate a table in the Managed Service for ClickHouse® cluster

1. Connect to the Managed Service for ClickHouse® cluster's database named db1 as user1.

2. Add test data to the database. As an example, a simple table is used with people's names and ages.

   1. Create a table:

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

   2. Populate the table with data:

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

   3. Check the result:

      SELECT * FROM persons;
      

Transfer the table from Managed Service for ClickHouse®Transfer the table from Managed Service for ClickHouse®

1. Prepare a script file:

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

      ch-to-dataproc.py

      from pyspark.sql import SparkSession
      
      # Creating a Spark session
      spark = SparkSession.builder.appName("ClickhouseDataproc").getOrCreate()
      
      # Specifying the port and ClickHouse® cluster parameters
      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 checking
      df.repartition(1).write.mode("overwrite") \
      .csv(path='s3a://<output_bucket_name>/csv', header=True, sep=',')
      

   2. Specify the following in the script:

      • Managed Service for ClickHouse® cluster ID.
      • user1 user password.
      • Output bucket name.

   3. In the input bucket, create a directory named scripts and upload the ch-to-dataproc.py file to it.

2. Create a PySpark job by specifying the path to the script file in the Main python file field: s3a://<input_bucket_name>/scripts/ch-to-dataproc.py.

3. Wait for the job to complete and make sure the output bucket's csv directory contains the source table.

Export data to Managed Service for ClickHouse®Export data to Managed Service for ClickHouse®

1. Prepare a script file:

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

      dataproc-to-ch.py

      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 ClickHouse® cluster parameters
      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()
      

   2. Specify the following in the script:

      • Managed Service for ClickHouse® cluster ID.
      • user1 user password.

   3. In the input bucket, create a directory named scripts and upload the dataproc-to-ch.py file to it.

2. Create a PySpark job by specifying the path to the script file in the Main python file field: s3a://<input_bucket_name>/scripts/dataproc-to-ch.py.

3. Wait for the job to complete and make sure the data has been transferred to Managed Service for ClickHouse®:

   1. Connect to the Managed Service for ClickHouse® cluster's database named db1 as user1.

   2. Run the following query:

      SELECT * FROM people;
      

   If the import is successful, the response will contain a table with the data.

Delete the resources you createdDelete the resources you created

Some resources are not free of charge. To avoid paying for them, delete the resources you no longer need:

1. Delete the objects from the buckets. Delete the other resources depending on how they were created:

   Manually

   Terraform

   1. Managed Service for ClickHouse® cluster
   2. Yandex Data Processing cluster
   3. Object Storage buckets
   4. Subnet
   5. Route table
   6. NAT gateway
   7. Cloud network
   8. Service account

   1. In the terminal window, go to the directory containing the infrastructure plan.

      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.

   2. Delete resources:

      1. Run this command:

         terraform destroy
         

      2. Confirm deleting the resources and wait for the operation to complete.

      All the resources described in the Terraform manifests will be deleted.

ClickHouse® is a registered trademark of ClickHouse, Inc.