Configure a ClickHouse data source
The ClickHouse Data Source transfers table data between ClickHouse and the platform's preset warehouse through Offline Sync. This article covers connection settings, source and target configuration in both directions, supported field types, sync validation, and Flow scheduling.
Prerequisites and limitations
Supported versions
ClickHouse 24.0 and later.
Prerequisites
Before you configure the Data Source, check the following:
- The supported directions are ClickHouse → preset warehouse and preset warehouse → ClickHouse. This plan does not support direct synchronization between ClickHouse and other external Data Sources.
- Prepare the ClickHouse Server Address/IP, Port, Database Name, Username, and Password. The platform's runtime environment must be able to reach the service. If network access is restricted, check the actual outbound address of the platform servers and the permitted access scope.
- The connecting account must be allowed to access the database and view metadata for the selected tables. Reading a source table requires read permissions, and writing to a target table requires the corresponding write permissions. For distributed tables, the account must also be able to retrieve the relevant cluster and sharding information.
- Prepare the target table and check its field types, null constraints, and partition definitions. For a preset warehouse target, see Create and manage space tables. Create ClickHouse target tables in the corresponding database in advance.
- Use the tables available in the table selector. Tables with Aggregating-family engines and materialized views are not available as ordinary selectable tables. Do not assume that this plan can synchronize every engine or type supported by ClickHouse.
| Environment configuration | Requirements |
|---|---|
| not distinguish environment | The Development Environment and Production Environment share one set of connection and credential settings |
| Independent setting | Configure the Development Environment and Production Environment separately; both configurations must have access to the databases and tables selected in the plan |
Data preview uses the Development Environment configuration, while manual execution uses the Production Environment configuration. Check the service addresses, database and table permissions, and network access for each configuration. Transfer passwords securely, and keep credentials out of screenshots, logs, and public documentation.
Create a ClickHouse data source
- Go to DataOps Platform > Integration > Data Source and click + Data Source.
- Select ClickHouse, click Next, and enter a Data Source name and remarks.
- Select not distinguish environment or Independent setting. For independent configuration, enter the Development Environment and Production Environment settings separately.
- Enter the connection parameters below. Under Connectivity Test, click Test. After the test passes, click Finish.
| Configuration Item | Description |
|---|---|
| Data source name | The name identifying this connection in the current space; it cannot be changed after creation |
| Remarks | Optional; describe the purpose of the connection |
| Server Address/IP | The ClickHouse service hostname or IP address. Separate multiple addresses with commas. Enter the port separately below rather than repeating it in the address |
| Port | The service port used to access ClickHouse; confirm it with the administrator |
| Database name | A database that has already been created in ClickHouse and that the connecting account is allowed to access |
| Username | An account with the permissions required to read or write the data for this sync |
| Password | The password corresponding to the username |
| Advanced | Enter any required extended settings in JSON format; leave this blank if no additional settings are needed |
After the connectivity test passes, load the databases and tables in the sync plan, preview the source data, and run the plan to validate actual table permissions and read and write results. After changing a password or database permissions, validate the connection and related plans again.
Configure the data source
Read ClickHouse data into the preset warehouse
- Go to DataOps Platform > Integration > Offline Sync and click + Integration plan.
- Under Data Source, select ClickHouse and the Data Source you created.
- Select a Method and configure the source for an entire table or a custom query.
- Click Data Preview to check the source fields, actual values, and read scope.
- Under Data Target, select the platform's Preset Warehouse, then select the target space table and configure the target partitions and write mode.
| Method | Configuration |
|---|---|
| Entire Table | Select the database and table. To read only part of the data, enable Data Filtering and enter a filter condition |
| Custom Query | Enter a read-only SQL query that returns the fields to synchronize, with any filter conditions included in the query. This method does not use the separate database, table, or Data Filtering settings provided for Entire Table |
Data Filtering for Entire Table requires a complete condition including WHERE, for example, WHERE status = 'active', rather than a complete SELECT query. For Custom Query, check which database the query accesses, qualify database and table names in the SQL when needed, and ensure that result field names are explicit and types are recognizable.
| Preset warehouse target settings | Description |
|---|---|
| Target table | The preset warehouse space table that receives the synced data; if it does not exist, go to Tables and create it first |
| Partition Information | For a partitioned target table, enter partition values as required by the table. You can use space parameters to configure the partitions used at execution time |
| Write mode | Select Insert new data or Overwrite according to the target data retention requirements. Before execution, confirm the target table, target partitions, and requirements for retaining existing data again |
After changing the Data Source, database, table, or Method, check the read scope, preview results, and Field Mapping again. If the source table structure or query results change, parse the fields again and review the mappings.
Configure the data target
Write preset warehouse data to ClickHouse
Configure the preset warehouse source table under Data Source. Under Data Target, select ClickHouse and the Data Source you created, then select the ClickHouse target database and table.
| Configuration Item | Description |
|---|---|
| Data source name | A ClickHouse Data Source already created in the current space; if none has been created, go to Data Source Management and configure one |
| Database name | The ClickHouse database to write to for this sync |
| Table name (Target Table in the UI) | A target table that has already been prepared in ClickHouse and that the connecting account is allowed to write to |
| Use the sharding key | Enable this when the sharding conditions below are met. Before enabling it, include the corresponding target sharding field (Sharding Key) in Field Mapping |
| Write mode | Only Insert new data (append mode) is supported. Data is appended to the target table; before running the plan again, assess whether this will create duplicate records |
| Batch size | Under Advanced, shown as Batch Quantity; the default shown in the UI is 20000, and the configurable range is 10000–100000 |
A ClickHouse target does not provide overwrite or upsert modes. Before retrying with the same source scope, check previous execution results and target data to avoid appending duplicates.
Use the sharding key
Sharding is used to write to distributed tables that meet all of the following conditions:
- The target table uses the Distributed engine, and
internal_replicationis enabled for the corresponding cluster. - The platform can identify the sharding field used by the target table. Do not treat an arbitrary sharding expression as a directly selectable field.
- Field Mapping includes this target sharding field and maps it to a valid source field.
When these conditions are met, enable Use the sharding key. If the switch is unavailable, check the target table, cluster configuration, and Field Mapping. After switching target databases or tables, or removing the sharding field mapping, check this setting again.
Field Mapping and supported field types
After configuring the source and target, use Field Mapping to map source fields to target fields. You can select fields individually, or use Map by Name or Map by Row and then check each mapping. Each target field can map to only one source field.
The figure shows field correspondences from an existing plan. It does not replace validation of the current connection, field parsing, or actual sync results.
Supported field types for reading
When ClickHouse is the Data Source, the following basic types are supported if they can be parsed correctly. Parameterized or wrapped types must also meet the limitations below.
| Type category | Supported ClickHouse field types |
|---|---|
| Boolean | Bool, boolean |
| Integer | Int8, UInt8, Int16, UInt16, Int32, UInt32, Int64, UInt64, Int128, UInt128, Int256, UInt256 |
| Floating-point | Float32, Float64 |
| Fixed-point | Decimal, Decimal32, Decimal64, Decimal128, Decimal256 |
| Strings and enums | String, FixedString, Enum, Enum8, Enum16 |
| Date and time | Date, DateTime, DateTime32, DateTime64 |
| Geographic types | Point, Ring, Polygon |
| Intervals | IntervalYear, IntervalQuarter, IntervalMonth, IntervalWeek, IntervalDay, IntervalHour, IntervalMinute, IntervalSecond |
| Complex types | Array and Map; check the complete structure |
Unlisted types such as Nested, UUID, IPv4, IPv6, Date32, and MultiPolygon, as well as other unrecognized types, are not supported. See the limitations on Tuple, Nullable(T), and LowCardinality(T) below. Their names alone do not establish support.
Supported field types for writing
When ClickHouse is the Data Target, the supported and unsupported basic types are the same as in the table above. Create the target table in advance. Field parameters, complete structures, and constraints must meet the actual write requirements.
Read-only generated fields such as MATERIALIZED, EPHEMERAL, and ALIAS cannot be configured as ordinary writable fields. This restriction concerns the kind of field, regardless of its data type.
Field type limitations
| Type or scenario | Support conditions and limitations |
|---|---|
| Integers, fixed-point numbers, fixed strings, and enums | Check the complete value range, precision, scale, length, and allowed enum values. Inclusion among the basic types does not mean that every field definition or actual value is applicable |
| Parameterized types | Field parsing results may differ between entire-table reads and custom queries. Check complete definitions for numeric length or precision parameters. For time types containing time zone text and enums with allowed values, confirm that they can be parsed correctly with the current read method |
Nullable(T) | Only the outer wrapper can be recognized by its inner type. The inner type must still be supported, and the target field's null constraints must be checked. This does not extend to all nested wrapper forms |
LowCardinality(T) | Automatic recognition of the inner type is not guaranteed. Check the current field parsing results and availability; support for the inner type alone does not establish support for the wrapped type |
Tuple(...) | Correct recognition of parameterized Tuple types is not guaranteed. Use the complete field definition and current parsing results; Tuple is not listed as unconditionally supported |
Array, Map, and nested structures | Check the complete element and key-value definitions and null constraints. Support for a top-level type does not mean that every internal structure is supported |
For ClickHouse type definitions, see the official ClickHouse data type documentation. This plan does not necessarily support every type supported by the database. After the source or target table structure changes, parse the fields again and check their correspondences. Visibility in the data preview does not replace sync validation.
Saving, validation, and scheduling
Basic information and sync validation
- Check the source table or query, filter scope, target table and partitions, write mode, and Field Mapping. First use the source Data Preview to check fields and sample values.
- Enter the plan name, owner, sync rate, and remarks, then click Save. The plan name cannot be changed after saving.
- Click Manual Execution, select an execution mode as required by the dialog, check the current parameter values, and start execution. Manual execution uses the Production Environment configuration; the execution mode does not switch between the Development Environment and Production Environment.
- Check the status and logs under Manual Execution Records. After the task succeeds, check the data volume, field correspondence, and sample numeric, time, complex-type, and null values in the target table.
With independent environment configurations, successfully loading or previewing data in the Development Environment does not mean that production credentials and read/write permissions are ready. Before the first execution, check the databases, tables, partitions, and parameters in both configurations. Confirm the target scope for overwrite writes to the preset warehouse, and assess the impact of retries and repeated execution for appends to ClickHouse.
Mount on Flow
After manual validation passes, you can mount the plan on a Flow and run it on a schedule.
- Open the saved plan, click Mount on task flow, and select the target Flow. If there is no corresponding Flow, create one from the selector or see Create a Flow. If the target Flow is missing from the list, refresh the list and select it again.
- Enter the node name, execution mode, owner, and other details, then click Create and Mount to create an Offline Sync Plan node linked to the current plan.
- Click Go to Flow, check the node and its upstream and downstream dependencies, and verify the databases, tables, partitions, and space parameters used for scheduling.
- Complete the Flow schedule settings, save, and publish the Flow. Mounting the node does not mean that it has been published online.
After a plan is mounted on a Flow, it can no longer be run manually from the plan details page. Execute and validate the node from the Flow instead.
Unmount from a Flow
- If the Flow has not been published, delete the corresponding Offline Sync Plan node in the Flow's Development Mode.
- If the Flow has already been published, first delete the corresponding node in Development Mode, then publish the Flow again to unmount the node in the Production Environment.
FAQ
| Symptom | What to check |
|---|---|
| Connectivity test fails | Check the service address, separately configured port, database, account credentials, and network access scope of the platform servers |
| The connection passes, but the table cannot be found or previewed | Check database and table names, metadata and read permissions, and whether the table engine is available for selection. For Custom Query, also check the SQL database and field types |
| The sharding switch is unavailable | Check the Distributed table, cluster internal_replication, recognizable sharding field, and whether Field Mapping includes that target field |
| Preview works, but execution fails | Check production credentials, target write permissions, databases, tables and partitions, parameters, and the actual execution log |
| Duplicate records appear after repeated execution | Check the append scope and previous execution results to determine whether the same batch of data was read and written more than once |

