Exchanging data with Yandex Managed Service for ClickHouse®

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.

Required paid resourcesRequired 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).
• Managed Service for ClickHouse® cluster fee: Covers the use of computational resources allocated to hosts (including ZooKeeper hosts) and disk space (see Managed Service for ClickHouse® pricing).
• Fee for a NAT gateway (see Virtual Private Cloud pricing).
• Fee for an Object Storage bucket: Covers data storage and bucket operations (see Object Storage pricing).
• Fee for public IP addresses assigned to cluster hosts (see Virtual Private Cloud pricing).

Getting startedGetting started

Set up the infrastructure:

Export data from Managed Service for ClickHouse®Export 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 db1 database in the Managed Service for ClickHouse® cluster as user1.

2. Add test data to the database. In this example, we will use a simple table containing 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;
      

Export the table from Managed Service for ClickHouse®Export 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 into it:

      ch-to-dataproc.py

      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=',')
      

   2. In your script, specify the following:

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

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

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

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

Import data to Managed Service for ClickHouse®Import 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 into 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 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()
      

   2. In your script, specify the following:

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

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

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

3. 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 in the Managed Service for ClickHouse® cluster as user1.

   2. Run this query:

      SELECT * FROM people;
      

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

Delete the resources you createdDelete the resources you created

Some resources incur charges. To avoid unnecessary expenses, delete the resources you no longer need:

1. Delete all objects from the buckets. Delete other resources using the method matching their creation method:

   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.