Skip to main content

ClickHouse setup

Some core functionality may be limited. If you're interested in contributing, check out the source code for each repository listed below.

  • Maintained by: Community
  • Authors: Geoff Genz
  • GitHub repo: ClickHouse/dbt-clickhouse
  • PyPI package: dbt-clickhouse
  • Slack channel: #db-clickhouse
  • Supported dbt Core version: v0.19.0 and newer
  • dbt Cloud support: Not Supported
  • Minimum data platform version: ?

Installing dbt-clickhouse

Use pip to install the adapter. Before 1.8, installing the adapter would automatically install dbt-core and any additional dependencies. Beginning in 1.8, installing an adapter does not automatically install dbt-core. This is because adapters and dbt Core versions have been decoupled from each other so we no longer want to overwrite existing dbt-core installations. Use the following command for installation:

Configuring dbt-clickhouse

For Clickhouse-specific configuration, please refer to Clickhouse configs.

Connecting to ClickHouse with dbt-clickhouse

To connect to ClickHouse from dbt, you'll need to add a profile to your profiles.yml file. A ClickHouse profile conforms to the following syntax:

profiles.yml
<profile-name>:
target: <target-name>
outputs:
<target-name>:
type: clickhouse
schema: <database-name>
user: <username>
password: <password>
#optional fields
driver: http|native
port: <port>
host: <hostname>
retries: 1
verify: False
secure: True
connect_timeout: 10
send_receive_timeout: 300
sync_request_timeout: 5
compression: False
compress_block_size: 1048576
database_engine: <db_engine>
check_exchange: True
use_lw_deletes: False
custom_settings: <empty>

Description of ClickHouse Profile Fields

FieldDescription
typeThis must be included either in profiles.yml or in the dbt_project.yml file. Must be set to clickhouse.
schemaRequired. A ClickHouse's database name. The dbt model database.schema.table is not compatible with ClickHouse because ClickHouse does not support a schema. So we use a simple model schema.table, where schema is the ClickHouse's database. We don't recommend using the default database.
userRequired. A ClickHouse username with adequate permissions to access the specified schema.
passwordRequired. The password associated with the specified user.
driverOptional. The ClickHouse client interface, http or native. Defaults to http unless the port is set to 9440 or 9400, in which case the native driver is assumed.
portOptional. ClickHouse server port number. Defaults to 8123/8443 (secure) if the driver is http, and to 9000/9440(secure) if the driver is native.
hostOptional. The host name of the connection. Default is localhost.
retriesOptional. Number of times to retry the initial connection attempt if the error appears to be recoverable.
verifyOptional. For (secure=True) connections, validate the ClickHouse server TLS certificate, including matching hostname, expiration, and signed by a trusted Certificate Authority. Defaults to True.
secureOptional. Whether the connection (either http or native) is secured by TLS. This converts an http driver connection to https, and a native driver connection to the native ClickHouse protocol over TLS. the Defaults to False.
cluster_modeOptional. Add connection settings to improve compatibility with clusters using the Replicated Database Engine. Default False.
connect_timeoutOptional. Connection timeout in seconds. Defaults is 10 seconds.
send_receive_timeoutOptional. Timeout for receiving data from or sending data to ClickHouse. Defaults to 5 minutes (300 seconds)
sync_request_timeoutOptional. Timeout for connection ping request (native connection only). Defaults to 5 seconds.
compressionOptional. Use compression in the connection. Defaults to False. If set to True for HTTP, this enables gzip compression. If set to True for the native protocol, this enabled lz4 compression. Other valid values are lz4hc and zstd for the native driver only.
compress_block_sizeOptional. Compression block size (in bytes) when using compression with the native driver. Defaults to 1MB
database_engineOptional. Database engine to use when creating new ClickHouse schemas (databases). If not set (the default), new databases will use the default ClickHouse database engine (usually Atomic).
check_exchangeOptional. On connecting to the ClickHouse, if this is parameter is True DBT will validate that the ClickHouse server supports atomic exchange of tables. Using atomic exchange (when available) improves reliability and parallelism. This check is unnecessary for ClickHouse running on recent Linux operating system, and in those circumstances can be disabled by setting check_exchange to False to avoid additional overhead on startup. Defaults to True.
use_lw_deletesOptional. If ClickHouse experimental lightweight deletes are available, use the delete+insert strategy as the default strategy for incremental materializations. Defaults to False (use legacy strategy).
custom_settingsOptional. A mapping of ClickHouse specific user settings to use with the connection. See the ClickHouse documentation for supported settings.

Troubleshooting Connections

If you encounter issues connecting to ClickHouse from dbt, make sure the following criteria are met:

  • The engine must be one of the supported engines.
  • You must have adequate permissions to access the database.
  • If you're not using the default table engine for the database, you must specify a table engine in your model configuration.
0