> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://apidocs.polytomic.com/2024-02-08/guides/configuring-your-connections/connections/clickhouse/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://apidocs.polytomic.com/_mcp/server.

# ClickHouse

Supports:
* ✅ Models
* ✅ Model sync destination
* ✅ Bulk sync source
* ✅ Bulk sync destination


## Connection

### Configuration
<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>cloud_provider</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Cloud provider (destination support only)<br/><br/>Accepted values: `aws` <a href="#cloud-provider-aws">↓</a>, `azure` <a href="#cloud-provider-azure">↓</a>, `gcp` <a href="#cloud-provider-gcp">↓</a></td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>database</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Database (optional)</td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>hostname</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Hostname</td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>password</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Password</td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>port</code></td>
<td><code>integer</code></td>
<td style="vertical-align: top;">Port</td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>skip_verify</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Skip certificate verification</td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>ssh</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Connect over SSH tunnel <a href="#ssh-true">↓</a></td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>ssl</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Use SSL</td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>username</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Username</td>
<td><code>required</code></td>
</tr>
</tbody>
</table>

<a id="cloud-provider-aws"></a>

#### `cloud_provider` = `aws`

<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>auth_mode</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">AWS authentication method<br/><br/>How to authenticate with AWS for the staging bucket. Accepted values: `access_key_and_secret` <a href="#auth-mode-access-key-and-secret">↓</a>, `iam_role` <a href="#auth-mode-iam-role">↓</a></td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>s3_bucket_name</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">S3 bucket name (destinations only)<br/><br/>Name of bucket used for staging data load files</td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>s3_bucket_region</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">S3 bucket region (destinations only)</td>
<td><code>required</code></td>
</tr>
</tbody>
</table>

```json
{
  "name": "ClickHouse connection",
  "type": "clickhouse",
  "configuration": {
    "auth_mode": "access_key_and_secret",
    "aws_access_key_id": "AKIAIOSFODNN7EXAMPLE",
    "aws_secret_access_key": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
    "cloud_provider": "aws",
    "database": "default",
    "hostname": "clickhouse.example.com",
    "password": "",
    "port": 9440,
    "s3_bucket_name": "my-bucket",
    "s3_bucket_region": "us-east-1",
    "skip_verify": true,
    "ssh": false,
    "ssl": true,
    "username": "default"
  }
}
```

<a id="auth-mode-access-key-and-secret"></a>

##### `auth_mode`

When `auth_mode` is `access_key_and_secret`:

<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>aws_access_key_id</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">AWS access key ID (destinations only)</td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>aws_secret_access_key</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">AWS secret access key (destinations only)</td>
<td><code>required</code></td>
</tr>
</tbody>
</table>

<table>
<thead>
<tr>
<th>Read-only property</th><th>Type</th><th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>aws_user</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">User ARN (destinations only)</td>
</tr>
</tbody>
</table>

<a id="auth-mode-iam-role"></a>

When `auth_mode` is `iam_role`:

<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>iam_role_arn</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">IAM role ARN</td>
<td><code>required</code></td>
</tr>
</tbody>
</table>

<table>
<thead>
<tr>
<th>Read-only property</th><th>Type</th><th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>external_id</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">External ID for the IAM role</td>
</tr>
</tbody>
</table>

```json
{
  "name": "ClickHouse connection",
  "type": "clickhouse",
  "configuration": {
    "auth_mode": "iam_role",
    "cloud_provider": "aws",
    "database": "default",
    "hostname": "clickhouse.example.com",
    "iam_role_arn": "",
    "password": "",
    "port": 9440,
    "s3_bucket_name": "my-bucket",
    "s3_bucket_region": "us-east-1",
    "skip_verify": true,
    "ssh": false,
    "ssl": true,
    "username": "default"
  }
}
```

<a id="cloud-provider-azure"></a>

#### `cloud_provider` = `azure`

<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>azure_access_key</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Storage account access key (destinations only)</td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>azure_account_name</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Storage account name (destinations only)</td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>container_name</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Storage container name (destinations only)<br/><br/>Container used for staging data load files (may be "container" or "container/prefix")</td>
<td><code>required</code></td>
</tr>
</tbody>
</table>

```json
{
  "name": "ClickHouse connection",
  "type": "clickhouse",
  "configuration": {
    "azure_access_key": "abcdefghijklmnopqrstuvwxyz0123456789/+ABCDEabcdefghijklmnopqrstuvwxyz0123456789/+ABCDE==",
    "azure_account_name": "account",
    "cloud_provider": "azure",
    "container_name": "container",
    "database": "default",
    "hostname": "clickhouse.example.com",
    "password": "",
    "port": 9440,
    "skip_verify": true,
    "ssh": false,
    "ssl": true,
    "username": "default"
  }
}
```

<a id="cloud-provider-gcp"></a>

#### `cloud_provider` = `gcp`

<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>gcs_bucket_name</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">GCS bucket name (destinations only)<br/><br/>Bucket used for staging data (may be "bucket" or "bucket/prefix")</td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>gcs_hmac_access_id</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">HMAC access ID (destinations only)</td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>gcs_hmac_secret</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">HMAC secret (destinations only)</td>
<td><code>required</code></td>
</tr>
</tbody>
</table>

```json
{
  "name": "ClickHouse connection",
  "type": "clickhouse",
  "configuration": {
    "cloud_provider": "gcp",
    "database": "default",
    "gcs_bucket_name": "my-bucket",
    "gcs_hmac_access_id": "GOOG1EXAMPLEACCESSID",
    "gcs_hmac_secret": "bGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9EXAMPLE",
    "hostname": "clickhouse.example.com",
    "password": "",
    "port": 9440,
    "skip_verify": true,
    "ssh": false,
    "ssl": true,
    "username": "default"
  }
}
```

<a id="ssh-true"></a>

#### `ssh`

When `ssh` is `true`:

<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>ssh_host</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">SSH host</td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>ssh_port</code></td>
<td><code>integer</code></td>
<td style="vertical-align: top;">SSH port</td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>ssh_private_key</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Private key</td>
<td><code>required</code></td>
</tr>
<tr>
<td><code>ssh_user</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">SSH user</td>
<td><code>required</code></td>
</tr>
</tbody>
</table>

```json
{
  "name": "ClickHouse connection",
  "type": "clickhouse",
  "configuration": {
    "auth_mode": "access_key_and_secret",
    "aws_access_key_id": "AKIAIOSFODNN7EXAMPLE",
    "aws_secret_access_key": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
    "cloud_provider": "aws",
    "database": "default",
    "hostname": "clickhouse.example.com",
    "password": "",
    "port": 9440,
    "s3_bucket_name": "my-bucket",
    "s3_bucket_region": "us-east-1",
    "skip_verify": true,
    "ssh": true,
    "ssh_host": "bastion.example.com",
    "ssh_port": 22,
    "ssh_private_key": "",
    "ssh_user": "root",
    "ssl": true,
    "username": "default"
  }
}
```



## Model Sync
### Source
#### Configuration
<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>query</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;"></td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>table</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Table</td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>view</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">View</td>
<td><code>optional</code></td>
</tr>
</tbody>
</table>


#### Example
```json
{
  ...
  "configuration": {
    "query": "SELECT * from users",
    "table": "users",
    "view": "active_users"
  }
}
```


### Target
ClickHouse connections may be used as the destination in a model sync.
#### All targets

##### Configuration
<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>column_codec</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Column compression codec<br/><br/>CODEC spec applied to every column in Polytomic-created tables. Examples: ZSTD; ZSTD(3); LZ4HC(9).</td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>created_column</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">'Created at' timestamp column</td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>optimize_after_sync</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Run OPTIMIZE FINAL after each sync<br/><br/>When enabled</td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>preserve_table_on_resync</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Preserve destination table when resyncing</td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>updated_column</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">'Updated at' timestamp column</td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>write_record_timestamps</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Write row timestamp metadata</td>
<td><code>optional</code></td>
</tr>
</tbody>
</table>


##### Example
```json
{
  ...
  "target": {
      "configuration": {
        "column_codec": "ZSTD",
        "created_column": "",
        "optimize_after_sync": false,
        "preserve_table_on_resync": false,
        "updated_column": "",
        "write_record_timestamps": false
    }
  }
}
```






### 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:

<table>
<thead>
<tr>
  <th>NAME</th>
  <th>DESCRIPTION</th>
  <th>ENUM</th>
</tr></thead>
<tbody>
<tr>
  <td>name</td>
  <td>Table name</td>
  <td>false</td>
</tr>

</tbody>
</table>




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

### Destination
#### Configuration
<table>
<thead>
<tr>
<th>Name</th><th>Type</th><th>Description</th><th>Required</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>advanced</code></td>
<td><code>object</code></td>
<td style="vertical-align: top;"></td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>mirror_schemas</code></td>
<td><code>boolean</code></td>
<td style="vertical-align: top;">Mirror schemas</td>
<td><code>optional</code></td>
</tr>
<tr>
<td><code>schema</code></td>
<td><code>string</code></td>
<td style="vertical-align: top;">Output schema</td>
<td><code>optional</code></td>
</tr>
</tbody>
</table>


#### Example
```json
{
  ...
  "destination_configuration": {
    "advanced": {
        "column_codec": "ZSTD",
        "empty_strings_null": true,
        "hard_deletes": false,
        "initial_execution": "rebuild",
        "optimize_after_sync": false,
        "table_prefix": "",
        "truncate_existing": false
    },
    "mirror_schemas": false,
    "schema": "schema"
  }
}
```



## 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`       |