ClickHouse destination plugin

Configuration #

Example #

kind: destination
spec:
  name: "clickhouse"
  path: "cloudquery/clickhouse"
  registry: "cloudquery"
  version: "v6.0.3"
  write_mode: "append"
  # Learn more about the configuration options at https://cql.ink/clickhouse_destination
  spec:
    connection_string: "clickhouse://${CH_USER}:${CH_PASSWORD}@localhost:9000/${CH_DATABASE}"
    # Optional parameters
    # cluster: ""
    # ca_cert: ""
    # engine:
    #   name: MergeTree
    #   parameters: []
    #
    # batch_size: 10000
    # batch_size_bytes: 5242880 # 5 MiB
    # batch_timeout: 20s

ClickHouse spec #

• connection_string (string) (required)

  Connection string to connect to the database. See SDK documentation for more details.

  Example connection string:

  • "clickhouse://username:password@host1:9000,host2:9000/database?dial_timeout=200ms&max_execution_time=60"
• cluster (string) (optional) (default: not used)

  Cluster name to be used for distributed DDL. If the value is empty, DDL operations will affect only the server the plugin is connected to.
• ca_cert (string) (optional) (default: not used)

  PEM-encoded certificate authorities. When set, a certificate pool will be created by appending the certificates to the system pool.

  See file variable substitution for how to read this value from a file.
• engine (optional, table engine settings. Default: MergeTree engine)

  Engine to be used for tables. Only *MergeTree family is supported at the moment.
• batch_size (integer) (optional) (default: 10000)

  Maximum number of items that may be grouped together to be written in a single write.
• batch_size_bytes (integer) (optional) (default: 5242880 (= 5 MiB))

  Maximum size of items that may be grouped together to be written in a single write.
• batch_timeout (duration) (optional) (default: 20s)

  Maximum interval between batch writes.
• partition (optional, partitioning) (default: no partitioning)

  Partitioning strategy to be used for tables (i.e. PARTITION BY clause in CREATE TABLE statements).
• order (optional, ordering) (default: use existing primary key)

  Ordering strategy to be used for tables (i.e. ORDER BY clause in CREATE TABLE statements).

ClickHouse table engine

• name (string) (required)

  Name of the table engine. Only *MergeTree family is supported at the moment.
• parameters (array of parameters) (optional) (default: empty)

  Engine parameters. Currently, no restrictions are imposed on the parameter types.

kind: destination
spec:
  name:       "clickhouse"
  path:       "cloudquery/clickhouse"
  registry:   "cloudquery"
  version:    "v6.0.3"
  write_mode: "append"

  spec:
    connection_string: "clickhouse://${CH_USER}:${CH_PASSWORD}@localhost:9000/${CH_DATABASE}"
    engine:
      name: ReplicatedMergeTree
      parameters:
      - "/clickhouse/tables/{shard}/{database}/{table}"
      - "{replica}"

Partitioning

• tables (array of strings) (optional) (default: ["*"])

  List of glob patterns to match table names against. Follows the same rules as the top-level spec tables option.

  If a table matches both a pattern in tables and skip_tables, the table will be skipped.

  Partition strategy table patterns should be disjointed sets: if a table matches two partition strategies, an error will be raised at runtime.
• skip_tables (array of strings) (optional) (default: empty)

  List of glob patterns to skip matching table names against. Follows the same rules as the top-level spec skip_tables option.

  If a table matches both a pattern in tables and skip_tables, the table will be skipped.

  Partition strategy table patterns should be disjointed sets: if a table matches two partition strategies, an error will be raised at runtime.
• partition_by (string) (required)

  Partitioning strategy to use, e.g. toYYYYMM(_cq_sync_time), the string is passed as is after "PARTITION BY" clause with no validation or quoting.

  An unset partition_by is not valid.

partition:
- tables: ["*"]
  skip_tables: ["special_partition_table", "non_partitioned_table"]
  partition_by: "toYYYYMM(_cq_sync_time)"
- tables: ["special_partition_table"]
  partition_by: "toYYYYMMDD(_cq_sync_time)"

Ordering

• tables (array of strings) (optional) (default: ["*"])

  List of glob patterns to match table names against. Follows the same rules as the top-level spec tables option.

  If a table matches both a pattern in tables and skip_tables, the table will be skipped.

  Ordering strategy table patterns should be disjointed sets: if a table matches two ordering strategies, an error will be raised at runtime.
• skip_tables (array of strings) (optional) (default: empty)

  List of glob patterns to skip matching table names against. Follows the same rules as the top-level spec skip_tables option.

  If a table matches both a pattern in tables and skip_tables, the table will be skipped.

  Ordering strategy table patterns should be disjointed sets: if a table matches two ordering strategies, an error will be raised at runtime.
• order_by (array of strings) (required)

  Sort key to use, the strings are passed as is after "ORDER BY" clause with no validation or quoting.

order:
- tables: ["aws_ec2_instances"]
  order_by:
  - "`account_id`"
  - "`region`"
  - "toYYYYMM(`_cq_sync_time`) DESC"
  - "`_cq_id`"

Connecting to ClickHouse Cloud #

connection_string: "clickhouse://default:${CH_PASSWORD}@<your-server-id>.<region>.<provider>.clickhouse.cloud:9440/${CH_DATABASE}?secure=true"

Verbose logging for debug

kind: destination
spec:
  name:       "clickhouse"
  path:       "cloudquery/clickhouse"
  registry:   "cloudquery"
  version:    "v6.0.3"
  write_mode: "append"

  spec:
    connection_string: "clickhouse://${CH_USER}:${CH_PASSWORD}@localhost:9000/${CH_DATABASE}?debug=true"