DataWorks:Batch-synchronize a single MaxCompute table to ClickHouse

Parameter

Description

Tunnel Resource Group

By default, Public Transmission Resource is used. If you have an exclusive Tunnel Quota, you can select it from the drop-down list.

Table

Select the MaxCompute table that you want to synchronize. If you are using a standard DataWorks workspace, ensure that a MaxCompute table with the same name and table schema exists in both the development and production environments.

• You cannot find the source table in the drop-down list during configuration if it does not exist in the development environment.

• If the source table does not exist in the production environment, the synchronization task fails after it is published and scheduled to run because the table cannot be found.

• If the table schemas in the development and production environments are inconsistent, the field mapping used during task execution may differ from the mapping that you configure. This can cause incorrect data to be written.

Filtering Method

Supports Partition Filtering and Data Filter.

• Partition Filtering: Filters the synchronization scope of specified source data by using a partition expression. When you select this method, you need to configure the Partition Information and If partitions do not exist, parameters.

• Data Filter: Specifies the source data to synchronize by using an SQL WHERE clause. (Do not include the WHERE keyword.)

Partition

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

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

• The value can be a scheduling system parameter, such as ds=${bizdate}. When the task runs, the system automatically replaces this parameter with its corresponding value.

• You can specify multiple partitions.

If partitions do not exist,

Specifies the policy to apply when a specified partition does not exist. Valid values:

• Error.

• the partitions are ignored and tasks are normally run.

Parameter

Description

Table

Select the ApsaraDB for ClickHouse table to which you want to synchronize data. We recommend that the table schemas for the ApsaraDB for ClickHouse data source be identical in the development and production environments.

Primary or Unique Key Conflict Handling

When you select insert into, the integration task validates the uniqueness of the primary key and constraint conflicts at runtime.

Statement Run Before Writing

You can run SQL statements before and after the data synchronization task as needed. For example, before a daily synchronization, you can run a statement to clear the corresponding daily partition, ensuring it is empty before new data is written.

Statement Run After Writing

Batch Insert Size (Bytes)

Data is written to ApsaraDB for ClickHouse in batches. These parameters define the upper limit for the size in bytes and the number of records per batch. When the amount of cached data reaches either the specified byte size or record count, a batch write is triggered.

We recommend that you set Batch size (Bytes) to 16777216 (16 MB) and set Batch size (Records) to a large value based on your single record size. This ensures that batch writes are primarily triggered by the batch size in bytes.

For example, if a single record is 1 KB, you can 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, writes are triggered each time the batch size reaches 16 MB.

Data Records Per Write

If a batch write fails

Specifies the policy for handling exceptions that occur during batch writes to ApsaraDB for ClickHouse:

• Try single-row write. If it still fails, record it as dirty data.: Single-row writes can be used to identify a small amount of dirty data, but they have a significant impact on the ClickHouse load. You must select this option with caution.

• Exit on Sync Task Failure: To ensure ClickHouse stability, you can choose to have the task exit when a write error occurs. By configuring alerts, you can be notified promptly and manually intervene to handle the error.

• Count batch write failures as dirty data.: If you want to ensure that a task runs to completion and can tolerate discarded batch data, you can choose to count the batch data as dirty data. This allows you to use the dirty data limit to control whether the task exits.