ClickHouse

Supports:

✅ Models
✅ Model sync destination
✅ Bulk sync source
✅ Bulk sync destination

Connection

Configuration

Name	Type	Description	Required
`cloud_provider`	`string`	Cloud Provider (destination support only) Accepted values: `aws` ↓, `azure` ↓	`false`
`database`	`string`	Database (optional)	`false`
`hostname`	`string`	Hostname	`true`
`password`	`string`	Password	`false`
`port`	`integer`	Port	`true`
`skip_verify`	`boolean`	Skip certificate verification	`false`
`ssh`	`boolean`	Connect over SSH tunnel ↓	`false`
`ssl`	`boolean`	Use SSL	`false`
`username`	`string`	Username	`true`

`cloud_provider`

When `cloud_provider` is `aws`

Name	Type	Description	Required
`auth_mode`	`string`	AWS Authentication Method How to authenticate with AWS for the staging bucket. Accepted values: `access_key_and_secret`, `iam_role`	`true`
`s3_bucket_name`	`string`	S3 Bucket Name (destinations only) Name of bucket used for staging data load files	`true`
`s3_bucket_region`	`string`	S3 Bucket Region (destinations only)	`true`

When `cloud_provider` is `azure`

Name	Type	Description	Required
`azure_access_key`	`string`	Storage Account Access Key (destinations only)	`true`
`azure_account_name`	`string`	Storage Account Name (destinations only)	`true`
`container_name`	`string`	Storage Container Name (destinations only) Container used for staging data load files (may be “container” or “container/prefix”)	`true`

`ssh`

When `ssh` is `true`

Name	Type	Description	Required
`ssh_host`	`string`	SSH host	`false`
`ssh_port`	`integer`	SSH port	`false`
`ssh_private_key`	`string`	Private key	`false`
`ssh_user`	`string`	SSH user	`false`

`auth_mode`

When `auth_mode` is `access_key_and_secret`

Name	Type	Description	Required
`aws_access_key_id`	`string`	AWS Access Key ID (destinations only)	`true`
`aws_secret_access_key`	`string`	AWS Secret Access Key (destinations only)	`true`

When `auth_mode` is `iam_role`

Name	Type	Description	Required
`iam_role_arn`	`string`	IAM Role ARN	`true`

Example

1 {
2   "name": "ClickHouse connection",
3   "type": "clickhouse",
4   "configuration": {
5     "auth_mode": "access_key_and_secret",
6     "aws_access_key_id": "AKIAIOSFODNN7EXAMPLE",
7     "aws_secret_access_key": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
8     "cloud_provider": "aws",
9     "database": "default",
10     "hostname": "clickhouse.example.com",
11     "password": "",
12     "port": 0,
13     "s3_bucket_name": "my-bucket",
14     "s3_bucket_region": "us-east-1",
15     "skip_verify": false,
16     "ssh": false,
17     "ssl": false,
18     "username": "default"
19   }
20 }

Read-only properties

Name	Type	Description	Required

`auth_mode`

When `auth_mode` is `access_key_and_secret`

Name	Type	Description	Required
`aws_user`	`string`	User ARN (destinations only)	`false`

When `auth_mode` is `iam_role`

Name	Type	Description	Required
`external_id`	`string`	External ID for the IAM role	`false`

Model Sync

Source

Configuration

Name	Type	Description	Required
`query`	`string`		`false`
`table`	`string`	Table	`false`
`view`	`string`	View	`false`

Example

1 {
2   ...
3   "configuration": {
4     "query": "SELECT * from users",
5     "table": "users",
6     "view": "active_users"
7   }
8 }

Target

ClickHouse connections may be used as the destination in a model sync.

All targets

Configuration

Name	Type	Description	Required
`column_codec`	`string`	Column compression codec CODEC spec applied to every column in Polytomic-created tables. Examples: ZSTD; ZSTD(3); LZ4HC(9).	`false`
`created_column`	`string`	’Created at’ timestamp column	`false`
`optimize_after_sync`	`boolean`	Run OPTIMIZE FINAL after each sync When enabled	`false`
`preserve_table_on_resync`	`boolean`	Preserve destination table when resyncing	`false`
`updated_column`	`string`	’Updated at’ timestamp column	`false`
`write_record_timestamps`	`boolean`	Write row timestamp metadata	`false`

Example

1 {
2   ...
3   "target": {
4       "configuration": {
5         "column_codec": "",
6         "created_column": "",
7         "optimize_after_sync": false,
8         "preserve_table_on_resync": false,
9         "updated_column": "",
10         "write_record_timestamps": false
11     }
12   }
13 }

Target creation

ClickHouse connections may be used to create a new target for a model sync. The following parameters are required to create a new target:

NAME	DESCRIPTION	ENUM
name	Table name	false

Bulk Sync

Source

ClickHouse connections may be used as a bulk sync source. No additional configuration options are required.

Destination

Configuration

Name	Type	Description	Required
`advanced`	`object`		`false`
`mirror_schemas`	`boolean`	Mirror schemas	`false`
`schema`	`string`	Output schema	`false`

Example

1 {
2   ...
3   "destination_configuration": {
4     "advanced": {
5         "column_codec": "",
6         "empty_strings_null": false,
7         "hard_deletes": false,
8         "optimize_after_sync": false,
9         "table_prefix": "",
10         "truncate_existing": false
11     },
12     "mirror_schemas": false,
13     "schema": "schema"
14   }
15 }

Type handling

Destination types

POLYTOMIC TYPE	CLICKHOUSE TYPE
`array<>`	`Array(Nullable(String))`
`bigint`	`Nullable(Int64)`
`boolean`	`Nullable(Bool)`
`date`	`Nullable(DateTime64(9))`
`datetime`	`Nullable(DateTime64(9))`
`decimal(precision, scale)`	`Nullable(Decimal(0, 0))`
`double`	`Nullable(Float64)`
`int`	`Nullable(Int32)`
`json`	`Nullable(JSON)`
`jsonarray`	`Array(Nullable(String))`
`number`	`Nullable(Float64)`
`object{}`	`Nullable(JSON)`
`single`	`Nullable(Float32)`
`smallint`	`Nullable(Int16)`
`string`	`Nullable(String)`
`time`	`Nullable(DateTime64(9))`

Source types

CLICKHOUSE TYPE	POLYTOMIC TYPE
`AGGREGATEFUNCTION`	`string`
`Array()`	`array<>`
`BOOL`	`boolean`
`BOOLEAN`	`boolean`
`DATE`	`date`
`DATE32`	`date`
`DATETIME`	`datetime`
`DATETIME64`	`datetime`
`DECIMAL`	`number`
`DECIMAL128`	`number`
`DECIMAL256`	`number`
`DECIMAL32`	`number`
`DECIMAL64`	`number`
`DYNAMIC`	`string`
`ENUM16`	`string`
`ENUM8`	`string`
`FIXEDSTRING`	`string`
`FLOAT32`	`single`
`FLOAT64`	`double`
`INT128`	`bigint`
`INT16`	`smallint`
`INT256`	`bigint`
`INT32`	`int`
`INT64`	`bigint`
`INT8`	`smallint`
`IPV4`	`string`
`IPV6`	`string`
`Map()`	`map<>`
`Nested()`	`object{}`
`SIMPLEAGGREGATEFUNCTION`	`string`
`STRING`	`string`
`TIME`	`time`
`TIME64`	`time`
`Tuple()`	`object{}`
`UINT128`	`bigint`
`UINT16`	`int`
`UINT256`	`bigint`
`UINT32`	`int`
`UINT64`	`bigint`
`UINT8`	`smallint`
`UUID`	`string`
`VARIANT`	`string`

1	{
2	"name": "ClickHouse connection",
3	"type": "clickhouse",
4	"configuration": {
5	"auth_mode": "access_key_and_secret",
6	"aws_access_key_id": "AKIAIOSFODNN7EXAMPLE",
7	"aws_secret_access_key": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
8	"cloud_provider": "aws",
9	"database": "default",
10	"hostname": "clickhouse.example.com",
11	"password": "",
12	"port": 0,
13	"s3_bucket_name": "my-bucket",
14	"s3_bucket_region": "us-east-1",
15	"skip_verify": false,
16	"ssh": false,
17	"ssl": false,
18	"username": "default"
19	}
20	}

1	{
2	...
3	"configuration": {
4	"query": "SELECT * from users",
5	"table": "users",
6	"view": "active_users"
7	}
8	}

1	{
2	...
3	"target": {
4	"configuration": {
5	"column_codec": "",
6	"created_column": "",
7	"optimize_after_sync": false,
8	"preserve_table_on_resync": false,
9	"updated_column": "",
10	"write_record_timestamps": false
11	}
12	}
13	}

1	{
2	...
3	"destination_configuration": {
4	"advanced": {
5	"column_codec": "",
6	"empty_strings_null": false,
7	"hard_deletes": false,
8	"optimize_after_sync": false,
9	"table_prefix": "",
10	"truncate_existing": false
11	},
12	"mirror_schemas": false,
13	"schema": "schema"
14	}
15	}

Connection

Configuration

cloud_provider

When cloud_provider is aws

When cloud_provider is azure

ssh

When ssh is true

auth_mode

When auth_mode is access_key_and_secret

When auth_mode is iam_role

Example

Read-only properties

auth_mode

When auth_mode is access_key_and_secret

When auth_mode is iam_role

Model Sync

Source

Configuration

Example

Target

All targets

Configuration

Example

Target creation

Bulk Sync

Source

Destination

Configuration

Example

Type handling

Destination types

Source types

Connection

Configuration

cloud_provider

When cloud_provider is aws

When cloud_provider is azure

ssh

When ssh is true

auth_mode

When auth_mode is access_key_and_secret

When auth_mode is iam_role

Example

Read-only properties

auth_mode

When auth_mode is access_key_and_secret

When auth_mode is iam_role

Model Sync

Source

Configuration

Example

Target

All targets

Configuration

Example

Target creation

Bulk Sync

Source

Destination

Configuration

Example

Type handling

Destination types

Source types

`cloud_provider`

When `cloud_provider` is `aws`

When `cloud_provider` is `azure`

`ssh`

When `ssh` is `true`

`auth_mode`

When `auth_mode` is `access_key_and_secret`

When `auth_mode` is `iam_role`

`auth_mode`

When `auth_mode` is `access_key_and_secret`

When `auth_mode` is `iam_role`

`cloud_provider`

When `cloud_provider` is `aws`

When `cloud_provider` is `azure`

`ssh`

When `ssh` is `true`

`auth_mode`

When `auth_mode` is `access_key_and_secret`

When `auth_mode` is `iam_role`

`auth_mode`

When `auth_mode` is `access_key_and_secret`

When `auth_mode` is `iam_role`