clickhouse

ClickHouse is an open-source column-oriented DBMS (columnar database management system) for online analytical processing (OLAP) that allows users to generate analytical reports using SQL queries in real-time.

Source: Wikipedia.

The clickhouse Connector

The clickhouse collector aims integrate the ClickHouse database in Tremor. It has been tested with ClickHouse v22.3-lts.

This connector is a sink-only connector.

Configuration

  # File: config.troy
  define connector clickhouse from clickhouse
  with
    config = {
      # The hostname of the database node
      "url": "localhost",

      # Compression
      "compression": "lz4",

      # The database to write data to
      #
      # This field is not mandatory.
      "database": "tremor_testing",

      # The table to write data to
      "table": "people",
      "columns": [
        # All the table's columns
        {
          # The column name
          "name": "name",
          # Its type
          "type": "String",
        }
        {
          "name": "age",
          "type": "UInt8",
        },
      ]
    }
  end;

Compression

Specifying a compression method is optional. This setting currently supports lz4 and none (no compression). If no value is specified, then no compression is performed.

Database

Specifying a database is optional. If no database name is supplied, then the default database is used.

Value conversion

The following sections show how Tremor values are transformed into ClickHouse values. As numerous casts can be performed, the conversions are grouped by output type.

Any type which is not documented in the following sections is considered as unsupported.

String

ClickHouse Strings values can be created from any Tremor string.

Example

The following Tremor values represent a valid ClickHouse string:

"Hello, world!"
"Grace Hopper"

Integers (UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64)

The following table shows the valid ranges where each numerical type can be created:

Type	Lower Bound (inclusive)	Upper Bound (inclusive)
UInt8	0	255
UInt16	0	65535
UInt32	0	4294967295
UInt64	0	18446744073709551615
Int8	- 128	127
Int16	- 32768	32767
Int32	- 2147483648	2147483647
Int64	- 9223372036854775808	9223372036854775807

Example

The following Tremor values can represent any integer type:

42
101
13
37

DateTime

DateTimes can be created from any non-negative Tremor integer. It represents the number of seconds elapsed since January 1st of 1970 at 00:00:00, in UTC timezone. It is encoded as a 32 bit unsigned integer.

Storing a DateTime in 32-bit format is likely to lead to a Year 2038 problem problem. It is advised to use DateTime64(0), as described below.

Example

The following Tremor values represent valid ClickHouse DateTime:

1634400000
954232020

DateTime64

ClickHouse DateTime64 type offers various precisions. Tremor supports only four precisions:

• DateTime64(0), second-precise,
• DateTime64(3), millisecond-precise,
• DateTime64(6), microsecond-precise,
• DateTime64(9), nanosecond-precise.

DateTime64(0) (respectively DateTime64(3), DateTime64(6) and DateTime64(9)) can be created from any Tremor integer representing the number of seconds (respectively milliseconds, microseconds and nanoseconds) elapsed since January 1st of 1970 at 00:00:00, in UTC timezone.

Example

The following Tremor values represent valid ClickHouse DateTime64(0, Etc/UTC) values.

1634400000
954232020

IPv4

ClickHouse IPv4s can be created from strings or from arrays of octets.

A ClickHouse IPv4 can be created from any 4-elements long array of integers in the [0 - 255] range.

A ClickHouse IPv4 can be created from any string composed of four octets written as decimal and separated by dots, as defined in the RFC 6943, section 3.1.1

Example

The following Tremor values represent valid ClickHouse IPv4 values:

"192.168.1.1"
[192, 168, 1, 1]

IPv6

ClickHouse IPv6 values can be created from strings or from arrays of octets.

A ClickHouse IPv6 can be created from any 16-elements long array of integers in the [0 - 255 range].

A ClickHouse IPv6 can be created from any RFC 5952-compliant string.

Example

The following Tremor values represent valid ClickHouse IPv6 values:

"FE80:0000:0000:0000:0202:B3FF:FE1E:8329"
[254, 128, 0, 0, 0, 0, 0, 0, 2, 2, 179, 255, 254, 30, 131, 41]

Nullable

Any column type can be marked as nullable. It allows to make optional the key-value pair for the events that are sent through the sink.

A column whose type is a nullable UInt8 can be declared as follows:

{
    "name": "column_name",
    "type": { "Nullable": "UInt8" }
}

Example

The following Tremor values represent valid ClickHouse UInt8 values:

101
null

Array

Arrays can store any number of elements.

A column whose type is an array of UInt8 can be declared as follows:

{
    "name": "column_name",
    "type": { "Array": "UInt8" }
}

Example

The following Tremor values represent valid ClickHouse Array(UInt8) values:

[101, 42, true]
[1, 2, false]

Uuid

An UUID can be created either from an array of integers or from a string.

In order to form a valid UUID, an array must have exactly 16 elements and every element must be an integer in the [0, 255] range.

An string respecting the RFC 4122 also represents a valid UUID.

Example

The following Tremor values represent valid ClickHouse Uuid values:

"123e4567-e89b-12d3-a456-426614174000"
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]