Loading data from Greenplum® to a ClickHouse® data mart

You can migrate a database from Greenplum® to ClickHouse® using Yandex Data Transfer.

To transfer a database from Greenplum® to ClickHouse®:

1. Set up the transfer.
2. Activate the transfer.
3. Check the copy function upon re-activation.

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

Getting startedGetting started

For clarity, we will create all required resources in Yandex Cloud. Prepare the infrastructure:

Set up the transferSet up the transfer

1. Create a source endpoint of the Greenplum® type, and specify the cluster connection settings in it.

2. Create a target endpoint of the ClickHouse type.

3. Create a transfer of the Snapshot type that will use the created endpoints.

   Replication is not available for this endpoint pair, but you can set up regular copying when creating a transfer. To do this, in the Snapshot field under Transfer parameters, select Regular and specify the copy interval. This will activate a transfer automatically after the specified time interval.

   Warning

   Before configuring regular copying, make sure that the target endpoint parameters include either a Drop or a Truncate cleanup policy. Otherwise, data on the target will be duplicated.

Activate the transferActivate the transfer

1. Connect to the Managed Service for Greenplum® cluster, create a table named x_tab, and populate it with data:

   CREATE TABLE x_tab
   (
       id NUMERIC,
       name CHARACTER(5)
   );
   CREATE INDEX ON x_tab (id);
   INSERT INTO x_tab (id, name) VALUES
   (40, 'User1'),
   (41, 'User2'),
   (42, 'User3'),
   (43, 'User4'),
   (44, 'User5');
   

2. Activate the transfer and wait for its status to change to Completed.

3. To verify that the data has transferred correctly, connect to the Managed Service for ClickHouse® target cluster and make sure that the columns of the x_tab table in the db1 database match those of the source database x_tab table:

   SELECT id, name FROM db1.x_tab;
   

   ┌─id─┬─name──┐
   │ 40 │ User1 │
   │ 41 │ User2 │
   │ 42 │ User3 │
   │ 43 │ User4 │
   │ 44 │ User5 │
   └────┴───────┘
   

Check the copy function upon re-activationCheck the copy function upon re-activation

1. Connect to the Managed Service for Greenplum® cluster, delete one row in the x_tab table and edit the other:

   DELETE FROM x_tab WHERE id = 41;
   UPDATE x_tab SET name = 'Key3' WHERE id = 42;
   

2. Reactivate the transfer and wait for its status to change to Completed.

3. Check the changes in the x_tab table on the ClickHouse® target:

   SELECT id, name FROM db1.x_tab;
   

   ┌─id─┬─name──┐
   │ 42 │ Key3  │
   │ 40 │ User1 │
   │ 43 │ User4 │
   │ 44 │ User5 │
   └────┴───────┘
   

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:

• Make sure the transfer has the Completed status and delete it.

• Delete both the source endpoint and the target endpoint.

• Delete the clusters:

  Manually

  Terraform

  • Managed Service for ClickHouse®.
  • Managed Service for Greenplum®.

  If you created your resources using Terraform:

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

  2. Delete the greenplum-clickhouse.tf configuration file.

  3. Make sure the Terraform configuration files are correct using this command:

     terraform validate
     

     If there are any errors in the configuration files, Terraform will point them out.

  4. Confirm updating the resources.

     1. Run the command to view planned changes:

        terraform plan
        

        If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

     2. If you are happy with the planned changes, apply them:

        1. Run the command:

           terraform apply
           

        2. Confirm the update of resources.

        3. Wait for the operation to complete.

     All the resources described in the greenplum-clickhouse.tf configuration file will be deleted.

ClickHouse® is a registered trademark of ClickHouse, Inc.