ClickHouse data store setup in minutes

Automate ClickHouse from a single space with no code

ClickHouse is a high-performance, column-oriented SQL database management system for online analytical processing (OLAP).

ClickHouse stores data in columns and uses a vectorised query execution engine, which makes it extremely fast for analytical queries over very large datasets. It supports a range of table engines (MergeTree, ReplacingMergeTree, ReplicatedMergeTree, and others), real-time data ingestion, and standard SQL. ClickHouse is widely used for analytics, observability, and data warehousing workloads where low-latency aggregation over billions of rows is required.

Because ClickHouse does not support row-level updates natively, upsert behaviour is emulated using a ReplacingMergeTree engine together with OPTIMIZE TABLE. Set engine_type to ReplacingMergeTree and enable optimize_after to deduplicate on the ordering key after each load. Note that ClickHouse has no separate schema namespace — a database is the schema.

Settings

SQLAlchemy URL

A full SQLAlchemy connection string for the ClickHouse database. If set, it takes precedence over the individual host/port/username/password settings.

Host

ClickHouse host

Port

ClickHouse connection port. Use 8123 for the HTTP driver (8443 when secure is enabled), or 9000 for the native driver.

Driver

The client driver used to connect. http (default) uses the native column-oriented bulk-insert path over the HTTP interface; native and asynch use the ClickHouse native protocol.

Username

ClickHouse username

Password

ClickHouse password

Database

ClickHouse database (ClickHouse has no separate schema — the database is the schema).

Secure (TLS)

Connect over HTTPS/TLS. Enable for ClickHouse Cloud and other TLS-terminated endpoints; remember to set the port to 8443.

Verify SSL/TLS

When secure is enabled, whether to verify the server's SSL/TLS certificate.

SSH Tunnel Enable

Enable an ssh tunnel (also known as bastion host), see the other ssh_tunnel.* properties for more details

SSH Tunnel Host

Host of the bastion host, this is the host we'll connect to via ssh

SSH Tunnel Port

Port to connect to bastion host

SSH Tunnel Private Key

A base64 encoded Private Key for authentication to the bastion host w/ key pair auth

SSH Tunnel Private Key Password

Private Key Password, leave None if no password is set

SSH Tunnel Username

Username to connect to bastion host

Default Target Schema

The default target database to write all streams to. ClickHouse has no schema namespace distinct from the database, so this is effectively the target database override.

Engine Type

The ClickHouse table engine to use when creating target tables. Defaults to ReplacingMergeTree (with optimize_after) for upsert-style deduplication; streams without a primary key automatically fall back to MergeTree so their rows are never collapsed. Use MergeTree for plain append-only loads. The Replicated* and cluster engines additionally require table_path, replica_name, and cluster_name.

Table Name

The name of the table to write to. Defaults to the stream name.

Table Path

The table path for replicated tables — required when using any of the Replicated* engines. Use $table_name to substitute the table name. See the ClickHouse ReplicatedMergeTree documentation.

Replica Name

The replica_name for replicated tables — required when using any of the Replicated* engines.

Cluster Name

The cluster to create tables in, passed as the clickhouse_cluster argument on table creation (ON CLUSTER). Required for cluster/replicated setups.

Order By Keys

List of columns to order by (ORDER BY key). Used for engines that require an ordering key; for ReplacingMergeTree this is the dedup key.

Optimize After Load

Run OPTIMIZE TABLE after each load. Required for ReplacingMergeTree upserts to collapse duplicate rows. Has performance implications on large tables.

Async Insert

Enable ClickHouse server-side async inserts for the (default) HTTP driver. Coalesces inserts into larger parts to reduce part churn on high-volume ingestion. The target waits for the async insert to flush before reporting success.

Load Method

The method to use when loading data into the destination. append-only always writes all input records; upsert deduplicates by primary key (via ReplacingMergeTree + optimize_after on ClickHouse); overwrite deletes all existing records and inserts the input records.

Hard Delete

When activate version is sent from a tap this specifies if we should delete the records that don't match, or mark them with a date in the _sdc_deleted_at column. Ignored if process_activate_version_messages is disabled.

Add Record Metadata

Whether to add metadata columns (_sdc_extracted_at, _sdc_batched_at, and more) to every table. Must be enabled for activate-version / hard-delete to work. See https://sdk.meltano.com/en/latest/implementation/record_metadata.html

Process ACTIVATE_VERSION messages

Whether to process ACTIVATE_VERSION messages.

Batch Size Rows

When a batch of rows reaches this size, the target will load it. Note the connector also caps batches at an internal maximum tuned for ClickHouse part sizing.

Validate Records

Whether to validate the schema of the incoming streams.

Flattening Enabled

'True' to enable schema flattening and automatically expand nested properties.

Flattening Max Depth

The max depth to flatten schemas.

Stream Maps

Config object for stream maps capability. For more information check out Stream Maps.

User Stream Map Configuration

User-defined config values to be used within map expressions.

Faker Locale

One or more LCID locale strings to produce localized output for: https://faker.readthedocs.io/en/master/#localization

Faker Seed

Value to seed the Faker generator for deterministic output: https://faker.readthedocs.io/en/master/#seeding-the-generator