Configure a ClickHouse data source
ClickHouse is a column-oriented database management system (DBMS) with a highly specialized, modular, engine-based architecture.
ClickHouse provides complete data management capabilities, including:
- Data storage, querying, indexing, replication, sharding, access control, and more.
- Support for standard SQL syntax (with gradually improving compatibility).
- Client tools, network interfaces (HTTP/TCP), user management, and more.
As a result, it can be used directly as a standalone database without relying on other systems.
This article describes the data synchronization capabilities supported for ClickHouse.
Supported versions
clickhouse 24.0+
Limitations
Currently, only the following configuration is supported:
- Writing from AE Built-in Warehouse workspace tables to ClickHouse (offline write)
Note:
- Reading from ClickHouse data sources (offline read) isn't supported yet
Supported field types
For a complete introduction to ClickHouse data types, see the official open-source data type documentation.
| Field type | Offline write (Writer) |
|---|---|
| Numeric types | |
| Int8（tinyint） | Supported |
| Int6（smallint） | Supported |
| Int32（int） | Supported |
| Int64（bigint） | Supported |
| Float | Supported |
| Decimal | Supported |
| Boolean | |
| Boolean | Supported |
| String types | |
| String | Supported |
| FixedString | Supported |
| UUID | Not supported |
| Time types | |
| Date | Supported |
| DateTime | Supported |
| DateTime64 | Supported |
| Composite types | |
| Array<boolean> | Supported |
| Array<tinyint> | Supported |
| Array<smallint> | Supported |
| Array<integer> | Supported |
| Array<bigint> | Supported |
| Array<float> | Supported |
| Array<double> | Supported |
| Array<varchar> | Supported |
| Tuple | Supported |
| Enum | Supported |
| Nested | Supported |
| Special types | |
| Nullable | Supported |
| Others | Not supported |
Create a ClickHouse data source
In the DataOps Platform - Integration module, you can choose to add a ClickHouse data source.
Data source configuration parameters
Fill in the configuration required by the data source and pass the connectivity test to create the ClickHouse data source.
| Field name | Description |
|---|---|
| Basic Information | |
| *Datasource Name | Must be unique within the DataOps Platform space. Can contain only letters, digits, and underscores, and can't start with a digit or an underscore |
| Remarks | Optional |
| Data source configuration | |
| not distinguish environment / Independent setting | Choose one: not distinguish environment means the production and development environments share one configuration; Independent setting means the two environments are configured independently |
| *Server Address/IP | Address of the server where the ClickHouse database runs. Separate multiple addresses with commas |
| *Port | Port used to access ClickHouse |
| *Database | Name of a database already created in ClickHouse |
| *Username | Username with permission to access the database |
| *Password | Password of the username |
| Advanced | Other advanced parameters required to connect to the database; you can customize them |
| Note: Cluster deployment mode by default | |
Parameters whose names start with * are required; parameters without * are optional.
Create an offline sync task
After you create the ClickHouse data source and pass the connectivity test as described above, you can configure a ClickHouse offline write task for your scenario.
ClickHouse as the Data Target
Select ClickHouse as the Data Target and configure the following parameters:
| Field name | Description |
|---|---|
| *Source Type | Select ClickHouse as the target type of the Data Target |
| *Datasource Name | A ClickHouse data source registered on the data source management page; select it from the drop-down list. If you haven't created the data source yet, click the Data sources management button to create a ClickHouse data source. |
| *Database | Name of the database to write to |
| *Table name | Information of the table to write to |
| Sharding Key | shardingKey. Sharding is available only when the deployment mode is cluster and internal_replication is enabled. After you configure the sharding key, data is imported in parallel based on it |
| *Write Mode |
|
| Advanced | Supports parameters such as the batch size Batch size: Amount of data submitted per batch; 20000 records by default |
Field mapping
After configuring the data source and the target, create field mappings. The system automatically syncs data from source fields to target fields based on the mappings. You can configure field mappings in three ways:
- Method 1: Custom selection. Select a source table field, then select the target field in the target table
- Method 2: Name Mapping. The system automatically maps fields with the same name in the source and target tables
- Method 3: Line Mapping. The system automatically maps fields in the same row
Note that each target field can correspond to only one source field
Field type conversion
During data synchronization, the system uses the CAST() function to try to convert source table fields to the types of the target fields. If the conversion fails, the value of the target field is set to NULL
Notes on data type conversion
- √ means the type conversion is supported
- √, null means the type conversion is supported; if the conversion fails, the value is set to NULL
| Source type/Target type | ROW | ARRAY | MAP | STRING | BOOLEAN | TINYINT | SMALLINT | INT | BIGINT | FLOAT | DOUBLE | DECIMAL | BYTES | DATE | TIMESTAMP | TIME |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ROW | √, null | |||||||||||||||
| ARRAY | √, null | |||||||||||||||
| MAP | √, null | |||||||||||||||
| STRING | √ | √, null | √, null | √, null | √, null | √, null | √, null | √, null | √, null | √, null | √, null | √, null | ||||
| BOOLEAN | √, null | √ | ||||||||||||||
| TINYINT | √, null | √ | √, null | √, null | √, null | |||||||||||
| SMALLINT | √, null | √, null | √ | √, null | √, null | |||||||||||
| INT | √, null | √, null | √, null | √ | √, null | |||||||||||
| BIGINT | √, null | √, null | √, null | √, null | √ | |||||||||||
| FLOAT | √, null | √ | √, null | √, null | ||||||||||||
| DOUBLE | √, null | √, null | √ | √, null | ||||||||||||
| DECIMAL | √, null | √, null | √, null | √ | ||||||||||||
| BYTES | √, null | √ | ||||||||||||||
| DATE | √, null | √ | √, null | |||||||||||||
| TIMESTAMP | √, null | √, null | √ | |||||||||||||
| TIME | √, null | √ |
Basic information settings
Finally, set the basic information of the integration plan, including the plan name, owner, synchronization rate, and remarks.
When you're done, click Save to create the integration plan.
Note: The plan name can't be changed after it's saved
The details page looks like this:
Mount an offline sync plan on a Flow
Mount on Flow
1. Start mounting
- On the integration plan details page, click Mount on task flow in the upper-right corner
2. Select a Flow
-
Select the target Flow from the drop-down menu
-
To create a new Flow:
- Click the New Flow shortcut button below the drop-down menu
- Or go to the Dev module to create one
-
Tip: If the target Flow isn't shown, click the ↻ refresh button on the right
3. Create a sync node
- In the Flow, create a node of the Offline sync plan type
- The node is linked to the current integration plan. When the node runs, it triggers a run of that integration plan
4. Complete mounting
- Click Create node and mount on it to complete the configuration
- After mounting succeeds, click Go to flow page to view the result right away
Note: The task node created by mounting is in the unreleased state. We recommend going to the Flow and releasing the node.
Unmount from a Flow
To unmount an offline sync plan from a Flow:
- If the Flow hasn't been released yet, go to the Flow that the plan is mounted on and delete the Offline sync plan task node in Dev Mode.
- If the Flow has already been released, after deleting the Offline sync plan task node in Dev Mode, release the Flow again. This also unmounts the node from the Production environment.