ClickHouse

Use the ClickHouse Connector to get data from Decodable into a ClickHouse table.

Overview

Connector name

clickhouse

Type

sink

Delivery guarantee

at least once

The ClickHouse Connector connects to a ClickHouse table through HTTP requests. The ClickHouse Connector can materialize data from both append and change streams. However, a ClickHouse connection handling change streams, particular change streams with frequent update and delete records, may operate significantly slower than pure append streams.

Prerequisites

Before you can get data from Clickhouse, the following requirements must be met:

  • Your ClickHouse database must be accessible from the Decodable network. Contact us for more information about enabling network access to your infrastructure.

  • A ClickHouse cluster, database, and table to send data to. Decodable does not create any of these entities for you, so they must already exist in your ClickHouse instance for Decodable to send data to them.

Steps

Follow these steps to get data from Decodable into ClickHouse. These steps assume that you are using Decodable Web. However, if you want to use the Decodable CLI to create the connection, you can refer to the Property Name column for information about what the underlying property names are.

  1. From the Connections page, select ClickHouse and complete the following fields.

    UI Field Property Name Description

    Hostname

    hostname

    The hostname to connect to your ClickHouse database. Make sure you specify the web address for ClickHouse, not the native TCP address.

    Port

    port

    The port number where your database accepts connections.

    Cluster Name

    cluster-name

    The name of your ClickHouse cluster.

    Database Name

    database-name

    The name of your ClickHouse database containing the table you want to send data to.

    Table Name

    table-name

    The name of your ClickHouse table.

    Note: If you want to send data from a change stream, then you must specify a non-distributed table.

    If you want to send data from an append stream, then you can specify a distributed or a non-distributed table. In addition, we recommend that you avoid sending data to buffer tables, as their flush strategy nullifies Decodable’s delivery guarantees.

    Username

    username

    The username to use to authenticate to ClickHouse.

    Password

    password

    The password associated with the username. This must be provided as a secret resource. If you are using the Decodable CLI, run decodable secret list to view available secrets or decodable secret --help for help with creating a new secret.

    Note: For security purposes, Decodable will never display secret values in plaintext. You can manage which users have permissions to create, delete, or modify secrets in the Access Control management view. See Roles, Groups, and Permissions for more information.

  2. Select which stream contains the records that you’d like to send to ClickHouse. You can also choose to create a new stream. Then, select Next.

  3. If you decided to create a new stream in the previous step, you must define its schema. Select New Schema to manually enter the fields and field types present or Import Schema if you want to paste the schema in the form of an Avro or JSON array.

    1. The stream’s schema must match the schema of the data that you plan on sending through this connection. For information on how ClickHouse types map to Decodable types, see Data Type Mappings.

    2. If you want to send Change Data Capture (CDC) records to ClickHouse, then you must specify one or more field(s) to use as a primary key. To do this, you must first explicitly tell Decodable that the type is not null explicitly by entering: <type> NOT NULL. For example: BIGINT NOT NULL. Then, you are able to specify the not null field as a primary key field.

  4. Select Next when you are finished providing defining the connection’s schema.

  5. Give the newly created connection a Name and Description and select Save.

Data Type Mappings

The following table describes the mapping of Decodable data types to their ClickHouse data type counterparts. See the documentation for more info on ClickHouse’s types, and Decodable Data Types.

Decodable Type ClickHouse Type

CHAR

String

VARCHAR

String / IP / UUID

STRING

String / Enum

BOOLEAN

UInt8

BYTES

FixedString

DECIMAL

Decimal / Int128 / Int256 / UInt64 / UInt128 / UInt256

TINYINT

Int8

SMALLINT

Int16 / UInt8

INT

Int32 / UInt16 / Interval

BIGINT

Int64 / UInt32

FLOAT

Float32

DOUBLE

Float64

DATE

Date

TIME

DateTime

TIMESTAMP

DateTime

TIMESTAMP_LTZ

DateTime

INTERVAL

Int32 / Int64

ARRAY

Array

MAP

Map

ROW

Not supported

MULTISET

Not supported