tip

Every element of the tuple has its own Type.
Tuples cannot be stored in tables (except for memory tables). They can be used for temporary column grouping. In queries, IN expressions and lambda functions with specific parameters can be used to group temporary columns.
A tuple can be the result of a query. In this case, for text formats other than JSON, the values in parentheses are separated by commas. In JSON format, tuples are output as arrays (in square brackets).
:::

Create tuples #

You can use a function to create tuples:

tuple(T1, T2, ...)

Example of creating tuples:

SELECT tuple(1,'a') AS x, toTypeName(x)

┌─x───────┬─toTypeName(tuple(1, 'a'))─┐ 
│ (1,'a') │ Tuple(UInt8, String)      │ 
└─────────┴───────────────────────────┘

Data types in tuples #

When dynamically creating tuples, ByteHouse automatically assigns the minimum expressible data type to each parameter in the tuple. If the parameter is NULL, then the corresponding element in the tuple is nullable.
Example of automatic data type detection:

SELECT tuple(1, NULL) AS x, toTypeName(x)

┌─x────────┬─toTypeName(tuple(1, NULL))──────┐ 
│ (1,NULL) │ Tuple(UInt8, Nullable(Nothing)) │ 
└──────────┴─────────────────────────────────┘

Includes Enum8 and Enum16 types. Enum stores the mapping of 'string'= integer. In ClickHouse, although users use string constants, all operations involving the Enum data type are performed based on integer values. This is more efficient in terms of performance compared to using the String data type.

• Enum8 is described by 'String'= Int8.
• Enum16 is described by 'String'= Int16.

Usage examples #

Create a column with an enum Enum8('hello' = 1, 'world' = 2) type:

CREATE TABLE t_enum 
( 
    x Enum8('hello' = 1, 'world' = 2) 
) 
ENGINE = TinyLog

This x column can only store the values listed in the type definition: 'hello'​ or 'world'. If you try to store any other value, ClickHouse throws an exception.

INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')

Ok. 

3 rows in set. Elapsed: 0.002 sec.

INSERT INTO t_enum VALUES('a')

Exception on client: 
Code: 49. DB::Exception: Unknown element 'a' for type Enum8('hello' = 1, 'world' = 2)

When you query data from the table, ClickHouse outputs the string value from Enum.

SELECT * FROM t_enum

┌─x─────┐ 
│ hello │ 
│ world │ 
│ hello │ 
└───────┘

To see the numerical value for the corresponding row, you must convert the Enum value to an integer type.

SELECT CAST(x, 'Int8') FROM t_enum

┌─CAST(x, 'Int8')─┐ 
│               1 │ 
│               2 │ 
│               1 │ 
└─────────────────┘

To create an enum value in a query, you also need to use CAST.

SELECT toTypeName(CAST('a', 'Enum8('a' = 1, 'b' = 2)'))

┌─toTypeName(CAST('a', 'Enum8('a' = 1, 'b' = 2)'))─┐ 
│ Enum8('a' = 1, 'b' = 2)                              │ 
└──────────────────────────────────────────────────────┘

Rules and usage #

The value range for each Enum8 type is -128 ... 127, and for each Enum16 type is -32768 ... 32767。 All strings or numbers must be unique. Empty strings are allowed. If an enum type is specified (when defining the table), the numbers can be in any order. However, the order does not matter.
Strings and numerical values in Enum cannot be NULL.
Enum can be included in a nullable type. Therefore, to create a table using this query

CREATE TABLE t_enum_nullable 
( 
    x Nullable( Enum8('hello' = 1, 'world' = 2) ) 
) 
ENGINE = TinyLog

You can store not only 'hello' and 'world', but also NULL.

INSERT INTO t_enum_nullable Values('hello'),('world'),(NULL)

In memory, the storage method for an Enum column is the same as for the corresponding integer value Int8 or Int16.
When reading in text mode, ClickHouse parses the value as a string and searches for the corresponding string in the set of Enum values. If the value is not found, an exception will be thrown. When reading in text format, the corresponding numeric value is determined based on the string read. If the value is not found, an exception will be thrown.
When writing in text form, ClickHouse parses the value as a string and writes it. If the column data contains invalid data (numbers not from the valid set), an exception will be thrown. The Enum type is read and written in binary format in the same way as Int8 and Int16 types.
The implicit default value is the value with the minimum numeric value.
In ORDER BY, GROUP BY, IN, DISTINCT, and similar operations, the behavior of Enum is the same as the corresponding numeric value. For example, sorting is performed by numeric value. For equality and comparison operators, Enum operates in the same way as its underlying numeric value.
Enum values cannot be compared with numbers. Enum values can be compared with constant strings. If the string being compared is not a valid Enum value, an exception will be thrown. The IN operator can be used to determine whether an Enum exists in a set of Enum values, where the Enum values in the set must be represented as strings.
Most operations involving numbers and strings are not applicable to Enums; for example, an Enum type cannot be added to a number. However, Enum has a native toString function, which returns its string value.
Enum values can be converted to numeric types using the toT function, where T is a numeric type. If T happens to correspond to the underlying numeric type of the Enum, this conversion is zero cost.
The Enum type can be modified at zero cost to change the corresponding set of values using ALTER. Enum members can be added or deleted through ALTER operations (as long as the table does not use the value, deletion is safe). As a safety measure, changing Enum members that have been used previously will throw an exception.
Through ALTER operations, Enum8 can be converted to Enum16, and vice versa, just like converting Int8 to Int16.

BitMap64 is a data type provided in ByteHouse. This type is similar in data representation to the data type offered by the ClickHouse community. A comparison between the two is provided below.

Differences between ByteHouse bitmap and the community's bitmap #

The ClickHouse community provides bitmap data processing capabilities. For details, see Bitmap Functions | ClickHouse Docs. Around the same time, ByteHouse independently developed its own set of types and related functions for processing bitmap data. The similarities and differences are as follows:

| | | \

Define ByteHouse BitMap64 type #

You can use the type keyword BitMap64 to define a bitmap type in a table. The following is an example:

CREATE TABLE test.bitmap_test ( 
    `p_date` Date, 
    `slice_id` UInt64, 
    `tag_id` Int32, 
    `tag_value` String, 
    `uids` BitMap64 
) ENGINE = MergeTree 
PARTITION BY p_date 
ORDER BY (tag_id, tag_value) 
SETTINGS index_granularity = 128;

Create ByteHouse BitMap64 type data #

Currently, bitmap has two constructor functions: use the bitmapFromColumn function to construct from a column of data, or use arrayToBitmap to construct from an array. The following is an example:

=== The number type is UInt64 
select bitmapFromColumn(number) as rbm, toTypeName(rbm) from numbers(10); 
-- {0,1,2,3,4,5,6,7,8,9}, "BitMap64" 

select arrayToBitmap(groupArray(number)) as rbm, toTypeName(rbm) from numbers(10); 
-- {0,1,2,3,4,5,6,7,8,9}, "BitMap64"

Writing ByteHouse BitMap64 type data #

This section is divided into writing from external systems to ByteHouse tables, and internal writing in ByteHouse.

External system writing #

Currently, data is mainly imported via text formats (CSV, JSONEachRow) and Parquet format files.
In text files, collection data can be wrapped with {} or [], and the engine parses it automatically.
In Parquet, collection data can be stored using LIST, and the engine converts it automatically.

• CSV format file import is as follows:

  cat ./test_bitmap_input.csv 
  -- 
  "2024-01-01",0,11,"a","{0,1,2,3,4,5,6,7,8,9}" 
  
  
  insert into test.bitmap_test format CSV infile 'test_bitmap_input.csv';
  

• Parquet format file import is as follows:

  parquet-meta ./test_bitmap_input.parquet 
  --- 
  file schema: schema 
  ------------------------------------------------- 
  p_date:      REQUIRED INT32 O:UINT_16 R:0 D:0 
  slice_id:    REQUIRED INT64 O:UINT_64 R:0 D:0 
  tag_id:      REQUIRED INT32 R:0 D:0 
  tag_value:   REQUIRED BINARY O:UTF8 R:0 D:0 
  uids:        REQUIRED F:1 
  .list:       REPEATED F:1 
  ..element:   OPTIONAL INT64 O:UINT_64 R:1 D:2 
   
  INSERT INTO test.bitmap_test FORMAT Parquet INFILE 'test_bitmap_input.parquet'
  

Internal writing in ByteHouse #

This is usually achieved by calculating a bitmap from one table and then writing it to the target table, typically using insert select. At this time, you may need to use the constructor functions described in [Create ByteHouse BitMap64 type data].
Examples are as follows:

• After calculation from the bitmap table, write back to the table

  INSERT INTO test.bitmap_test SELECT 
      p_date, 
      slice_id, 
      100 AS tag_id, 
      'test2' as tag_value, 
      uids 
  FROM 
  ( 
      SELECT p_date, slice_id, subBitmap(uids, 1, 2) AS uids 
      FROM test.bitmap_test 
      WHERE tag_id = 1 and tag_value = 't1' 
  );
  

• Build a bitmap from a single column of data and write it into the table

  INSERT INTO test.bitmap_test SELECT 
      '2024-01-01' as p_date, 
      1 as slice_id 
      101 AS tag_id, 
      'test2' as tag_value, 
      bitmapFromColumn(number) as uids 
  FROM numbers(10)
  

• A small portion is written directly using insert values

  INSERT INTO test.bitmap_test values ('2024-01-01', 1, 102, 'test3', [1,2,3,4]);
  

Querying ByteHouse BitMap64 type #

Direct query #

The ByteHouse BitMap64 type can be directly queried and displayed using select. However, note that when the number of bitmap elements is relatively large (for example, tens of thousands), printing to the screen takes considerable time, so direct printing is not recommended.

SELECT 
    tag_id, 
    uids 
FROM test.bitmap_test 
WHERE tag_id = 101

If the number of bitmap elements is relatively large, it is recommended to print the element count for inspection.

SELECT 
    tag_id, 
    bitmapCardinality(uids) 
FROM test.bitmap_test 
WHERE tag_id = 101

BitMap functions #

ByteHouse provides a rich set of function interfaces to facilitate querying BitMap64 type data. These can be roughly categorized as general functions, aggregate functions, and advanced aggregate functions. For details on the supported function lists, usage guidance, conversion between community functions, and other details, please refer to BitMap functions.

Convert other data types into dictionary-encoded types.

Syntax #

LowCardinality(data_type)

Parameters #

• data_type — String, FixedString, Date, DateTime, including number types except Decimal. For some data types, LowCardinality is not efficient. For details, see allow_suspicious_low_cardinality_types

Description #

LowCardinality is a concept that changes data storage and data processing methods. ClickHouse will perform dictionary coding on columns with LowCardinality. For many applications, processing dictionary-encoded data can significantly increase SELECT query speed.
The efficiency of using the LowCardinality data type depends on data diversity. If a dictionary contains fewer than 10,000 distinct values, ClickHouse can achieve more efficient data storage and processing. Conversely, if the dictionary contains more than 10,000, efficiency will be poorer.
When using character types, consider using LowCardinality instead of Enum. LowCardinality is usually more flexible and efficient.

Example #

Create a column of LowCardinality type:

CREATE TABLE lc_t 
( 
    `id` UInt16, 
    `strings` LowCardinality(String) 
) 
ENGINE = MergeTree() 
ORDER BY id

Related settings and functions #

Settings:

• low_cardinality_max_dictionary_size
• low_cardinality_use_single_dictionary_for_part
• low_cardinality_allow_in_native_format
• allow_suspicious_low_cardinality_types

Functions:

• toLowCardinality

IPv4 #

IPv4 is a domain type that is binary compatible with the UInt32 type, used for storing IPv4 address values. It provides more compact binary storage while supporting more user-friendly input and output formats.

CREATE TABLE hits (url String, from IPv4) ENGINE = MergeTree() ORDER BY url; 

DESCRIBE TABLE hits;

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┐ 
│ url  │ String │              │                    │         │                  │ 
│ from │ IPv4   │              │                    │         │                  │ 
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┘

Additionally, you can use a IPv4 type column as the primary key:

CREATE TABLE hits (url String, from IPv4) ENGINE = MergeTree() ORDER BY from;

When writing and querying, the IPv4 type can recognize more human-readable input and output formats:

INSERT INTO hits (url, from) VALUES ('https://wikipedia.org', '116.253.40.133')('https://clickhouse.com', '183.247.232.58')('https://clickhouse.com/docs/en/', '116.106.34.242'); 
SELECT * FROM hits;

┌─url────────────────────────────────┬───────────from─┐ 
│ https://clickhouse.com/docs/en/ │ 116.106.34.242 │ 
│ https://wikipedia.org              │ 116.253.40.133 │ 
│ https://clickhouse.com          │ 183.247.232.58 │ 
└────────────────────────────────────┴────────────────┘

It also provides a more compact binary storage format:

SELECT toTypeName(from), hex(from) FROM hits LIMIT 1;

┌─toTypeName(from)─┬─hex(from)─┐ 
│ IPv4             │ B7F7E83A  │ 
└──────────────────┴───────────┘

Implicit conversion to other types is not allowed except for UInt32. To convert a IPv4 type value to a string, you can use IPv4NumToString() for explicit conversion:

SELECT toTypeName(s), IPv4NumToString(from) as s FROM hits LIMIT 1;

┌─toTypeName(IPv4NumToString(from))─┬─s──────────────┐ 
│ String                            │ 183.247.232.58 │ 
└───────────────────────────────────┴────────────────┘

Alternatively, you can use CAST to convert it to UInt32 type:

SELECT toTypeName(i), CAST(from as UInt32) as i FROM hits LIMIT 1;

┌─toTypeName(CAST(from, 'UInt32'))─┬──────────i─┐ 
│ UInt32                           │ 3086477370 │ 
└──────────────────────────────────┴────────────┘

IPv6 #

IPv6 is a domain type that is binary compatible with FixedString(16) type, and is used to store IPv6 address values. It provides a more compact binary storage format while also supporting recognition of more human-readable input and output formats.

CREATE TABLE hits (url String, from IPv6) ENGINE = MergeTree() ORDER BY url; 

DESCRIBE TABLE hits;

┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┐ 
│ url  │ String │              │                    │         │                  │ 
│ from │ IPv6   │              │                    │         │                  │ 
└──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┘

Additionally, you can use a IPv6 type column as the primary key:

CREATE TABLE hits (url String, from IPv6) ENGINE = MergeTree() ORDER BY from;

When writing and querying, the IPv6 type can recognize more human-readable input and output formats:

INSERT INTO hits (url, from) VALUES ('https://wikipedia.org', '2a02:aa08:e000:3100::2')('https://clickhouse.com', '2001:44c8:129:2632:33:0:252:2')('https://clickhouse.com/docs/en/', '2a02:e980:1e::1'); 

SELECT * FROM hits;

┌─url────────────────────────────────┬─from──────────────────────────┐ 
│ https://clickhouse.com          │ 2001:44c8:129:2632:33:0:252:2 │ 
│ https://clickhouse.com/docs/en/ │ 2a02:e980:1e::1               │ 
│ https://wikipedia.org              │ 2a02:aa08:e000:3100::2        │ 
└────────────────────────────────────┴───────────────────────────────┘

It also provides a more compact binary storage format:

SELECT toTypeName(from), hex(from) FROM hits LIMIT 1;

┌─toTypeName(from)─┬─hex(from)────────────────────────┐ 
│ IPv6             │ 200144C8012926320033000002520002 │ 
└──────────────────┴──────────────────────────────────┘

Implicit conversion to other types is not allowed except for FixedString(16). To convert a IPv6 type value to a string, you can use IPv6NumToString() for explicit conversion:

SELECT toTypeName(s), IPv6NumToString(from) as s FROM hits LIMIT 1;

┌─toTypeName(IPv6NumToString(from))─┬─s─────────────────────────────┐ 
│ String                            │ 2001:44c8:129:2632:33:0:252:2 │ 
└───────────────────────────────────┴───────────────────────────────┘

Alternatively, use CAST to convert it to FixedString(16):

SELECT toTypeName(i), CAST(from as FixedString(16)) as i FROM hits LIMIT 1;

┌─toTypeName(CAST(from, 'FixedString(16)'))─┬─i───────┐ 
│ FixedString(16)                           │  ��� │ 
└───────────────────────────────────────────┴─────────┘

The intermediate state of an aggregate function can be obtained by appending the -State suffix to the function name. Additionally, when you need to access the final state data of this type, you need to use the same function name with the -Merge suffix to obtain the final state data.
AggregateFunction — a parameterized data type.

Parameter #

• Aggregate function name

If the function has multiple parameter lists, specify the values of the other parameter lists here.

• Type of aggregate function parameter

Example #

CREATE TABLE t 
( 
    column1 AggregateFunction(uniq, UInt64), 
    column2 AggregateFunction(anyIf, String, UInt8), 
    column3 AggregateFunction(quantiles(0.5, 0.9), UInt64) 
) ENGINE = ...

In the above, uniq, anyIf (any + if), and quantiles are aggregate functions supported in ClickHouse.

Data writing #

When writing data, you need to include the data in an INSERT SELECT statement, and for data of the AggregateFunction type, you need to use the corresponding function with the -State suffix for processing.

Function usage example #

uniqState(UserID) 
quantilesState(0.5, 0.9)(SendTiming)

Unlike the uniq and quantiles functions, which return the final value of the aggregate result, functions with the -State suffix always return the intermediate state of data of the AggregateFunction type.
For SELECT, the AggregateFunction type is always presented in a specific binary format across all output formats. For example, you can use a SELECT statement to dump the state data of the function in TabSeparated format, and at the same time use an INSERT statement to dump the data back.

Data query #

When querying data from an AggregatingMergeTree table, for fields of the AggregateFunction type, you need to use the same aggregate function with the -Merge suffix to aggregate the data. For fields that are not of the AggregateFunction type, include them in the GROUP BY clause.
Aggregate functions with the -Merge suffix can combine multiple intermediate states of the AggregateFunction type into the final aggregate result.
For example, the results returned by the following two queries are always consistent:

SELECT uniq(UserID) FROM table 

SELECT uniqMerge(state) FROM (SELECT uniqState(UserID) AS state FROM table GROUP BY RegionID)