DataWorks:Synchronize data from a single MaxCompute table to ClickHouse in offline mode

Configuration item

Configuration details

Data Source

The MaxCompute data source that you selected in the previous step is displayed by default. If your DataWorks workspace is in standard mode, the names of the development and production projects are displayed separately.

Tunnel Resource Group

This tutorial uses the Public Transmission Resource by default. If you have an exclusive Tunnel Quota, you can select it from the drop-down list.

Table

Select the MaxCompute table to synchronize. If your DataWorks workspace is in standard mode, make sure that a MaxCompute table with the same name and schema exists in both the development and production environments of MaxCompute.

Filter Method

You can filter data using Partition Filter or Data Filtering.

• Partition Filter: Filters the synchronization scope of the source data using a partition filter expression. If you select this method, you must also configure the Partition Info and When Partition Does Not Exist parameters.

• Data Filtering: Specifies the synchronization scope of the source data using a SQL WHERE clause. You do not need to enter the WHERE keyword.

Partition Info

This parameter is required when you set Filter Method to Partition Filter. You can enter the value of the partition key column.

• The value can be a static field, such as ds=20220101.

• The value can be a system parameter, such as ds=${bizdate}. The system parameter is automatically replaced with its value at runtime.

When Partition Does Not Exist

The policy to handle the synchronization task when a partition does not exist. The following options are supported:

• Error.

• Ignore Non-existent Partitions And Run The Task As Normal.

Configuration item

Configuration details

Data Source

The ClickHouse data source that you selected in the previous step is displayed by default.

Table

Select the ClickHouse table to synchronize. We recommend that you ensure the table to be synchronized has the same table schema in the development and production environments of the ClickHouse data source.

Primary Key Or Unique Key Conflict Handling

When you select insert into, the integration task checks for primary key uniqueness and constraint violations at runtime.

Pre-SQL Statement

You can execute SQL statements as needed before and after the data synchronization task runs. For example, before synchronizing data by day, you can clear the data in the corresponding daily partition to ensure that the partition is empty before the data is written.

Post-import Statements

Batch Size (Bytes)

Data is written to ClickHouse in batches. These parameters specify the upper limits for the batch size in bytes and the number of records. When the read data reaches either the byte limit or the record count limit, a batch is considered complete. Each completed batch is then written to ClickHouse.

We recommend that you set Batch Size (Bytes) to 16777216 (16 MB). We recommend that you set Batch Size (Records) to a large value based on the size of a single record. This way, the batch write is triggered by the batch size in bytes.

For example, if a single record is 1 KB, set Batch Size (Bytes) to 16777216 (16 MB) and Batch Size (Records) to 20000 (which is greater than 16 MB/1 KB = 16384). In this case, the write is triggered by the batch size in bytes, and a write occurs every 16 MB.

Batch Size (Records)

When Batch Write To ClickHouse Is Abnormal

When a batch write to ClickHouse is abnormal, you can select an error handling policy:

• Try Single-record Writes. If A Single-record Write Still Fails, Count It As Dirty Data: Single-record writes can identify a small amount of dirty data but have a significant impact on the ClickHouse payload. Select this option with caution.

• Fail And Exit The Synchronization Task: For ClickHouse stability, you can choose to have the task fail and exit when a write error occurs. Configure alerts to be notified promptly and handle the exceptions manually.

• Count The Batch As Dirty Data: If you want to ensure the task completes and can tolerate the loss of batch data, you can choose to count the batch as dirty data. Use the number of allowed dirty data records to control whether the task exits.