ClickHouse Reference

Authentication options and data type handling for ClickHouse connections.

Server

servers:
  - server: production
    type: clickhouse
    host: clickhouse.acme.com
    port: 8123
    database: sales

The CLI connects to ClickHouse's HTTP interface. The native protocol ports 9000 and 9440, which clickhouse-client uses, are replaced by the HTTP ports 8123 and 8443.

Authentication

Variable	Example	Description
DATACONTRACT_CLICKHOUSE_USERNAME	analyst	User. Defaults to default
DATACONTRACT_CLICKHOUSE_PASSWORD	mysecretpassword	Password. Defaults to empty
DATACONTRACT_CLICKHOUSE_SECURE	true	Connect over HTTPS, as ClickHouse Cloud requires. The port then defaults to 8443. Defaults to false

host, port, and database come from the contract's servers block, and can be overridden with DATACONTRACT_CLICKHOUSE_HOST, DATACONTRACT_CLICKHOUSE_PORT, and DATACONTRACT_CLICKHOUSE_DATABASE.

Data types

Importing

datacontract import clickhouse reads system.columns and takes the declared type as the physicalType. Nullable(...) and LowCardinality(...) are looked through for the logical type: String/FixedString/UUID/Enum8/Enum16/IPv4/IPv6 → string, Int8…Int256/UInt8…UInt256 → integer, Float32/Float64/Decimal → number, Bool → boolean, Date/Date32 → date, DateTime/DateTime64 → timestamp, Array(...) → array, Map(...) → map, Tuple(...)/JSON → object. A column is required unless its type is Nullable.

Testing

ClickHouse supports native type introspection: the declared physicalType is checked against system.columns. The SQL spellings ClickHouse stores under another name match that name (VARCHAR(255) matches String, TIMESTAMP matches DateTime), and a DateTime64(3) without a time zone matches a DateTime64(3, 'UTC') column. Nullable is checked by required, not by the type: String matches a Nullable(String) column. LowCardinality(String) and String are distinct types.

Logical type mapping

When no physicalType is declared, the CLI derives the native type from the logicalType — for example in datacontract export sql and the dbt exports. This table is generated from the converter in the CLI's code:

logicalType	ClickHouse type
string	String
integer	Int32
number	Decimal(38,0)
boolean	Bool
date	Date
timestamp	DateTime64(6)
time	String
object	Tuple()
array	Array(String)
map	Map(String, String)
vector	Array(Float32)