Technical reference

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/etl-tools/fivetran/reference.md)

## Setup details[​](#setup-details "Direct link to Setup details")

### User and role management[​](#user-and-role-management "Direct link to User and role management")

Consider not using the `default` user; instead, create a dedicated one to use it with this Fivetran
destination only. The following commands, executed with the `default` user, will create a new `fivetran_user` with the
required privileges.

```
CREATE USER fivetran_user IDENTIFIED BY '<password>'; -- use a secure password generator

GRANT CURRENT GRANTS ON *.* TO fivetran_user;
```

Additionally, you can revoke access to certain databases from the `fivetran_user`.
For example, by executing the following statement, we restrict access to the `default` database:

```
REVOKE ALL ON default.* FROM fivetran_user;
```

You can execute these statements in the ClickHouse SQL console.

### Advanced configuration[​](#advanced-configuration "Direct link to Advanced configuration")

The ClickHouse Cloud destination supports an optional JSON configuration file for advanced use cases. This file allows you to fine-tune destination behavior by overriding the default settings that control batch sizes, parallelism, connection pools, and request timeouts.

Note

This configuration is entirely optional. If no file is uploaded, the destination uses sensible defaults that work well for most use cases.

The file must be valid JSON and conform to the schema described below.

If you need to modify the configuration after the initial setup, you can edit the destination configurations in the Fivetran dashboard and upload an updated file.

The configuration file has a top-level section:

```
{
  "destination_configurations": { ... }
}
```

Inside of it you can specify the following configurations that control the internal behavior of the ClickHouse destination connector itself.
These configurations affect how the connector processes data before sending it to ClickHouse.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Setting Type Default Allowed Range Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `write_batch_size` integer `100000` 5,000 – 100,000 Number of rows per batch for insert, update, and replace operations.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `select_batch_size` integer `1500` 200 – 1,500 Number of rows per batch for SELECT queries used during updates.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `mutation_batch_size` integer `1500` 200 – 1,500 Number of rows per batch for ALTER TABLE UPDATE mutations in history mode. Lower it if you are experiencing large SQL statements.|  |  |  |  |  | | --- | --- | --- | --- | --- | | `hard_delete_batch_size` integer `1500` 200 – 1,500 Number of rows per batch for hard delete operations in normal syncs and in history mode. Lower it if you are experiencing large SQL statements. | | | | | | | | | | | | | | | | | | | | | | | | |

All fields are optional. If a field is not specified, the default value is used.
If a value is outside the allowed range, the destination will report an error during sync.
Unknown fields are silently ignored (a warning is logged) and do not cause errors, which allows forward compatibility when new settings are added.

Example:

```
{
  "destination_configurations": {
    "write_batch_size": 50000,
    "select_batch_size": 200
  }
}
```

## Type transformation mapping[​](#type-mapping "Direct link to Type transformation mapping")

The Fivetran ClickHouse destination maps [Fivetran data types](https://fivetran.com/docs/destinations#datatypes) to ClickHouse types as follows:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Fivetran type ClickHouse type|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | BOOLEAN [Bool](/docs/sql-reference/data-types/boolean)| SHORT [Int16](/docs/sql-reference/data-types/int-uint)| INT [Int32](/docs/sql-reference/data-types/int-uint)| LONG [Int64](/docs/sql-reference/data-types/int-uint)| BIGDECIMAL [Decimal(P, S)](/docs/sql-reference/data-types/decimal)| FLOAT [Float32](/docs/sql-reference/data-types/float)| DOUBLE [Float64](/docs/sql-reference/data-types/float)| LOCALDATE [Date32](/docs/sql-reference/data-types/date32)| LOCALDATETIME [DateTime64(0, 'UTC')](/docs/sql-reference/data-types/datetime64)| INSTANT [DateTime64(9, 'UTC')](/docs/sql-reference/data-types/datetime64)| STRING [String](/docs/sql-reference/data-types/string)| LOCALTIME [String](/docs/sql-reference/data-types/string) \* \*\*| BINARY [String](/docs/sql-reference/data-types/string) \*| XML [String](/docs/sql-reference/data-types/string) \*| JSON [String](/docs/sql-reference/data-types/string) \* | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

Note

\* BINARY, XML, LOCALTIME, and JSON are stored as [String](/docs/sql-reference/data-types/string) because ClickHouse's `String` type can represent an arbitrary set of bytes. The destination adds a column comment to indicate the original data type. The ClickHouse [JSON](/docs/sql-reference/data-types/newjson) data type is not used as it was marked as obsolete and never recommended for production usage.
\*\* NOTE: Issue to track the support for LOCALTIME type: [clickhouse-fivetran-destination #15](https://github.com/ClickHouse/clickhouse-fivetran-destination/issues/15).

### Date and time value ranges[​](#date-and-time-value-ranges "Direct link to Date and time value ranges")

Fivetran sources can send date and time values in the range [0001-01-01, 9999-12-31](https://fivetran.com/docs/destinations#dateandtimevaluerange).
ClickHouse Cloud date types have narrower ranges, so values outside the supported range are silently clamped to the nearest boundary:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Fivetran type ClickHouse Cloud type Min value Max value|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | LOCALDATE Date32 1900-01-01 2299-12-31|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | LOCALDATETIME DateTime64(0, 'UTC') 1900-01-01 00:00:00 2262-04-11 23:47:16|  |  |  |  | | --- | --- | --- | --- | | INSTANT DateTime64(9, 'UTC') 1900-01-01 00:00:00 2262-04-11 23:47:16 | | | | | | | | | | | | | | | |

- The INSTANT upper bound is 2262-04-11 23:47:16 because DateTime64(9) stores nanoseconds since epoch as int64, and 2^63 - 1 nanoseconds corresponds to this date.
  ClickHouse itself supports DateTime64 with precision <= 9 up to 2299-12-31 23:59:59.
- The LOCALDATETIME upper bound is also limited to 2262-04-11 23:47:16 due to a [known bug](https://github.com/ClickHouse/clickhouse-go/issues/1311) in the Go ClickHouse driver, where `time.Time.UnixNano()` is called for all DateTime64 precisions before scaling, causing int64 overflow for dates beyond 2262 even at precision 0.

## Destination tables[​](#table-structure "Direct link to Destination tables")