Reference

​

Supported data sources

​

Isolation Level

The Kafka consumer isolation level determines whether a ClickPipe reads and inserts only messages committed by a Kafka transaction. Enable the kafka_read_committed setting to use read_committed and skip messages from aborted Kafka transactions. Disable it to use read_uncommitted and read all messages. You can configure this setting under Advanced Settings.

​

Supported data formats

The supported formats are:

• JSON
• AvroConfluent
• Protobuf

​

Supported data types

​

Standard

The following standard ClickHouse data types are currently supported in ClickPipes:

• Base numeric types - [U]Int8/16/32/64, Float32/64, and BFloat16
• Large integer types - [U]Int128/256
• Decimal Types
• Boolean
• String
• FixedString
• Date, Date32
• DateTime, DateTime64 (UTC timezones only)
• Enum8/Enum16
• UUID
• IPv4
• IPv6
• Time, Time64
• JSON
• all ClickHouse LowCardinality types
• Map with keys and values using any of the above types (including Nullables)
• Tuple and Array with elements using any of the above types (including Nullables, one level depth only)
• SimpleAggregateFunction types (for AggregatingMergeTree or SummingMergeTree destinations)

​

Variant type support

ClickPipes supports the Variant type in the following circumstances:

• Avro Unions. If your Avro schema contains a union with multiple non-null types, ClickPipes will infer the appropriate variant type. Variant types are not otherwise supported for Avro data.
• JSON fields. You can manually specify a Variant type (such as Variant(String, Int64, DateTime)) for any JSON field in the source data stream. Complex subtypes (arrays/maps/tuples) are not supported. In addition, because of the way ClickPipes determines the correct variant subtype to use, only one integer or datetime type can be used in the Variant definition - for example, Variant(Int64, UInt32) is not supported.

​

JSON type support

ClickPipes support the JSON type in the following circumstances:

• Avro Record and Protobuf Message fields can always be assigned to a JSON column.
• Avro String and Bytes fields can be assigned to a JSON column if the Avro field actually contains JSON String objects.
• Protobuf String and Bytes kinds can be assigned to a JSON column if the Protobuf field actually contains JSON String objects.
• JSON fields that are always a JSON object can be assigned to a JSON destination column.

Note that you will have to manually change the destination column to the desired JSON type, including any fixed or skipped paths.

​

Avro

​

Supported Avro data types

ClickPipes supports all Avro Primitive and Complex types, and all Avro Logical types except local-timestamp-millis and local_timestamp-micros. Avro record types are converted to Tuple, array types to Array, and map to Map (string keys only). In general the conversions listed here are available. We recommend using exact type matching for Avro numeric types, as ClickPipes does not check for overflow or precision loss on type conversion. Alternatively, all Avro types can be inserted into a String column, and will be represented as a valid JSON string in that case.

​

Nullable types and Avro unions

Nullable types in Avro are defined by using a Union schema of (T, null) or (null, T) where T is the base Avro type. During schema inference, such unions will be mapped to a ClickHouse “Nullable” column. Note that ClickHouse doesn’t support Nullable(Array), Nullable(Map), or Nullable(Tuple) types. Avro null unions for these types will be mapped to non-nullable versions (Avro Record types are mapped to a ClickHouse named Tuple). Avro “nulls” for these types will be inserted as:

• An empty Array for a null Avro array
• An empty Map for a null Avro Map
• A named Tuple with all default/zero values for a null Avro Record

​

Protobuf

​

Supported Protobuf data types

ClickPipes supports all Protobuf 2 and 3 types, with the exception of the long-deprecated proto 2 group type. Basic type conversions use the following mappings: The following well-known types are also supported:

​

Protobuf oneof

During schema inference, Protobuf oneof fields are mapped by default to a named Tuple, where at most one field will hold a non-default value. These fields can also be automatically mapped to a Variant column where the active value takes the type of whichever constituent field is set. Alternatively, each constituent field can be manually mapped to its own ClickHouse column; since oneof fields are mutually exclusive, only one column will ever be populated per record.

​

Message lists

If the top level Protobuf schema defined for the ClickPipe contains a single repeated field that is itself a protobuf Message, schema inference and column mapping will be based on the “contained” Message field. The Kafka message will be processed as a list of such messages, and a single Kafka message will unwrap into multiple ClickHouse rows.

​

Kafka virtual columns

The following virtual columns are supported for Kafka compatible streaming data sources. When creating a new destination, virtual columns can be added to the target table by using the Add Column button. Note that the _raw_message column is only recommended for JSON data. For use cases where only the JSON string is required (such as using ClickHouse JsonExtract* functions to populate a downstream materialized view), it may improve ClickPipes performance to delete all the “non-virtual” columns.

​

Structured message keys

Mapping _key to a String column stores the original Kafka message key. This mapping bypasses key decoding and schema lookup. To extract fields from a structured key, map source fields beginning with _key.. For example, for the JSON key {"customer":{"id":42}}, map _key.customer.id to a destination column such as customer_id. For _key.* mappings or a direct _key mapping to a type other than String, ClickPipes first checks whether the key was encoded using the configured schema registry. If it was not, ClickPipes checks whether the key is a JSON object. A registry-encoded key must use the same format and registry family as the record value, but it can use a different schema ID. Key schema changes are detected automatically. When decoding is enabled, raw keys, JSON scalars or arrays, and malformed JSON do not populate nested key fields; their destination columns receive type defaults. If ClickPipes recognizes a registry-encoded key but cannot retrieve or apply its schema, it writes the Kafka record to the ClickPipes error table. An Avro key can also have a non-record root schema, such as a primitive, array, map, or fixed type. To decode one, map _key directly to a compatible destination type other than String. Non-record schemas cannot populate nested key fields.