ClickHouse Data Types

1. Basic Types

The basic types consist of only three types: numeric, string, and time. There is no Boolean type, but you can use integers 0 or 1 as substitutes.

1. Numeric Types

Numeric types are divided into three categories: integers, floating-point numbers, and fixed-point numbers, which will be explained separately below.

1. Int

In common practice, Tinyint, Smallint, Int, and Bigint are often used to refer to different value ranges of integers. ClickHouse, however, directly uses Int8, Int16, Int32, and Int64 to refer to four sizes of Int types, where the trailing number indicates the size in bytes (8 bits = 1 byte).

ClickHouse supports unsigned integers, indicated by the prefix U.

2. Float Similar to integers, ClickHouse directly uses Float32 and Float64 to represent single-precision and double-precision floating-point numbers.

When using floating-point numbers, you should be aware that they have limited precision. For example, if we write values exceeding the effective precision to Float32 and Float64 respectively, let's see what happens. For instance, writing a number with 20 decimal places to both Float32 and Float64 will result in data errors:

:) SELECT toFloat32('0.12345678901234567890') as a , toTypeName(a)

┌──────a─┬─toTypeName(toFloat32('0.12345678901234567890'))─┐

│ 0.12345679 │ Float32 │

└────────┴───────────────────────────────┘

:) SELECT toFloat64('0.12345678901234567890') as a , toTypeName(a)

┌────────────a─┬─toTypeName(toFloat64('0.12345678901234567890'))─┐

│ 0.12345678901234568 │ Float64 │

└─────────────┴──────────────────────────────┘

It can be observed that Float32 overflows starting from the 8th decimal place, and Float64 overflows starting from the 17th decimal place.

ClickHouse's floating-point numbers support positive infinity, negative infinity, and NaN (not a number) representations.

Positive infinity:

:) SELECT 0.8/0

┌─divide(0.8, 0)─┐

│ inf │

└──────────┘

Negative infinity:

:) SELECT -0.8/0

┌─divide(-0.8, 0)─┐

│ -inf │

└───────────┘

NaN:

:) SELECT 0/0

┌─divide(0, 0)──┐

│ nan │

└──────────┘

3. Decimal

If higher precision numeric operations are required, fixed-point numbers should be used. ClickHouse provides three precisions of fixed-point numbers: Decimal32, Decimal64, and Decimal128. Fixed-point numbers can be declared in two forms: the shorthand forms Decimal32(S), Decimal64(S), Decimal128(S), and the native form Decimal(P,S), where:

·P represents precision, determining the total number of digits (integer part + fractional part), with a range of 1 to 38;

·S represents scale, determining the number of decimal places, with a range of 0 to P.

Correspondence between shorthand and native forms

When performing arithmetic operations on two fixed-point numbers with different precisions, their decimal places S will change. During addition, S takes the maximum value. For example, in the following query, adding toDecimal64(2,4) and toDecimal32(2,2) results in S=4:

:) SELECT toDecimal64(2,4) + toDecimal32(2,2)

┌─plus(toDecimal64(2, 4), toDecimal32(2, 2))─┐

│

││ 4.0000 │

└───────────────────────────┘

During subtraction, the rules are the same as addition, and S also takes the maximum value. For example, subtracting toDecimal64(2,2) from toDecimal32(4,4) results in S=4:

:) SELECT toDecimal32(4,4) - toDecimal64(2,2)

┌─minus(toDecimal32(4, 4), toDecimal64(2, 2))┐

│ 2.0000 │

└────────────────────────────┘

During multiplication, S takes the sum of both S values. For example, in the following query, multiplying toDecimal64(2,4) and toDecimal32(2,2) results in S=4+2=6:

:) SELECT toDecimal64(2,4) * toDecimal32(2,2)

┌─multiply(toDecimal64(2, 4), toDecimal32(2, 2))┐

│ 4.000000 │

└─────────────────────────────┘

During division, S takes the value of the dividend's S, and the dividend's S must be greater than the divisor's S, otherwise an error will be raised. For example, dividing toDecimal64(2,4) by toDecimal32(2,2) results in S=4:

:) SELECT toDecimal64(2,4) / toDecimal32(2,2)

┌─divide(toDecimal64(2, 4), toDecimal32(2, 2))┐

│ 1.0000 │

└───────────────────────────┘

One more thing worth noting when using fixed-point numbers: since modern computer systems only support 32-bit and 64-bit CPUs, Decimal128 is implemented through software simulation, making it significantly slower than Decimal32 and Decimal64.

2. String Types

String types can be further divided into three categories: String, FixedString, and UUID. Judging by their names, they don't seem like types provided by a database, but rather more like a design from a programming language.

1. String

Strings are defined by String, with no length limit. Therefore, there is no need to declare a size when using String. It completely replaces traditional database character types such as Varchar, Text, Clob, and Blob. The String type does not restrict character sets because it doesn't have this concept at all, so strings of any encoding can be stored in it. However, for program standardization and maintainability, a unified encoding should be followed within the same program. For example, "consistently maintaining UTF-8 encoding" is a good convention.

2. FixedString

The FixedString type is somewhat similar to the traditional Char type. For scenarios where characters have a clear length, fixed-length strings can be used. Fixed-length strings are declared using FixedString(N), where N represents the string length. However, unlike Char, FixedString pads the trailing characters with null bytes, while Char typically uses spaces for padding. For example, in the following example, the string 'abc' is only 3 characters long, but its length is 5 because there are 2 null characters padded at the end:

:) SELECT toFixedString('abc',5) , LENGTH(toFixedString('abc',5)) AS LENGTH

┌─toFixedString('abc', 5)─┬─LENGTH─┐

│ abc │ 5 │

└────────────────┴──────┘

3. UUID

UUID is a common primary key type in databases, and ClickHouse directly treats it as a data type. UUID has a total of 32 bits, with a format of 8-4-4-4-12. If a UUID field is not assigned a value when data is written, it will be filled with zeros according to the format, for example:

CREATE TABLE UUID_TEST (

c1 UUID,

c2 String

) ENGINE = Memory;

--First row UUID has a value

INSERT INTO UUID_TEST SELECT generateUUIDv4(),'t1'

--Second row UUID has no value

INSERT INTO UUID_TEST(c2) VALUES('t2')

:) SELECT * FROM UUID_TEST

┌─────────────────────c1─┬─c2─┐

│ f36c709e-1b73-4370-a703-f486bdd22749 │ t1 │

└───────────────────────┴────┘

┌─────────────────────c1─┬─c2─┐

│ 00000000-0000-0000-0000-000000000000 │ t2 │

└───────────────────────┴────┘

As you can see, the UUID in the second row, which was not assigned a value, has been filled with zeros.

3. Time Types

Time types are divided into three categories: DateTime, DateTime64, and Date. ClickHouse currently does not have a timestamp type. The highest precision for time types is seconds, meaning if you need to handle times with resolutions greater than seconds, such as milliseconds or microseconds, you can only use UInt types.

1. DateTime

The DateTime type includes hour, minute, and second information, precise to the second, and supports writing in string form:

CREATE TABLE Datetime_TEST (

c1 Datetime

) ENGINE = Memory

--Write in string form

INSERT INTO Datetime_TEST VALUES('2019-06-22 00:00:00')

SELECT c1, toTypeName(c1) FROM Datetime_TEST

┌──────────c1─┬─toTypeName(c1)─┐

│

│

││ 2019-06-22 00:00:00 │ DateTime │

└─────────────┴───────────┘

2. DateTime64

DateTime64 can record sub-seconds, adding a precision setting on top of DateTime, for example:

CREATE TABLE Datetime64_TEST (

c1 Datetime64(2)

) ENGINE = Memory

--Write in string form

INSERT INTO Datetime64_TEST VALUES('2019-06-22 00:00:00')

SELECT c1, toTypeName(c1) FROM Datetime64_TEST

┌─────────────c1─┬─toTypeName(c1)─┐

│ 2019-06-22 00:00:00.00 │ DateTime │

└───────────────┴──────────┘

3. Date

The Date type does not include specific time information, only precise to the day, and it also supports writing in string form:

CREATE TABLE Date_TEST (

c1 Date

) ENGINE = Memory

--Write in string form

INSERT INTO Date_TEST VALUES('2019-06-22')

SELECT c1, toTypeName(c1) FROM Date_TEST

┌─────────c1─┬─toTypeName(c1)─┐

│ 2019-06-22 │ Date │

└───────────┴──────────┘

2. Composite Types

In addition to basic data types, ClickHouse also provides four composite types: arrays, tuples, enums, and nested structures. These types are typically features not natively available in other databases. With composite types, ClickHouse's data model becomes more expressive.

1. Array

Arrays have two definition forms. The conventional way is array(T):

SELECT array(1, 2) as a , toTypeName(a)

┌─a───┬─toTypeName(array(1, 2))─┐

│ [1,2] │ Array(UInt8) │

└─────┴────────────────┘

Or the shorthand form [T]:

SELECT [1, 2]

From the examples above, it can be seen that you don't need to explicitly declare the element type of an array during queries. This is because ClickHouse's arrays have type inference capabilities, based on the principle of minimal storage cost, i.e., using the smallest expressible data type. For example, in the above example, array(1,2) will automatically infer UInt8 as the array type. However, if there are Null values in the array elements, the element type will become Nullable, for example:

SELECT [1, 2, null] as a , toTypeName(a)

┌─a──────┬─toTypeName([1, 2, NULL])─┐

│ [1,2,NULL] │ Array(Nullable(UInt8)) │

└────────┴─────────────────┘

Attentive readers may have noticed that multiple data types can be included in the same array, for example, the array [1,2.0] is valid. However, the types must be compatible with each other; for example, the array [1,'2'] will raise an error.

When defining table fields, arrays need to specify a clear element type, for example:

CREATE TABLE Array_TEST (

c1 Array(String)

) engine = Memory

2. Tuple

Tuple types consist of 1 to n elements, and each element is allowed to have a different data type, without requiring compatibility between them. Tuples also support type inference, still based on the principle of minimal storage cost. Similar to arrays, tuples can also be defined in two ways. The conventional way is tuple(T):

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

┌─x─────────────────┬─toTypeName(tuple(1, 'a', now()))─┐

│ (1,'a','2019-08-28 21:36:32') │ Tuple(UInt8, String, DateTime) │

└───────────────────┴─────────────────────┘

Or the shorthand form (T):

SELECT (1,2.0,null) AS x, toTypeName(x)

┌─x──────┬─toTypeName(tuple(1, 2., NULL))───────┐

│ (1,2,NULL) │ Tuple(UInt8, Float64, Nullable(Nothing)) │

└───────┴──────────────────────────┘

When defining table fields, tuples also need to specify clear element types:

CREATE TABLE Tuple_TEST (

c1 Tuple(String,Int8)

) ENGINE = Memory;

Element types, similar to generics, can further ensure data quality. Type checking is performed during data writing. For example, writing INSERT INTO Tuple_TEST VALUES(('abc',123)) is valid, while writing INSERT INTO Tuple_TEST VALUES(('abc','efg')) will raise an error.

3. Enum

ClickHouse supports enum types, which are commonly used when defining constants. ClickHouse provides two enum types, Enum8 and Enum16, which are identical except for their value ranges. Enums are always defined using (String:Int) Key/Value pairs, so Enum8 and Enum16 correspond to (String:Int8) and (String:Int16) respectively, for example:

CREATE TABLE Enum_TEST (

c1 Enum8('ready' = 1, 'start' = 2, 'success' = 3, 'error' = 4)

) ENGINE = Memory;

When defining an enum set, there are a few points to note. First, Key and Value must not be duplicated; uniqueness must be guaranteed. Second, neither Key nor Value can be Null, but Key is allowed to be an empty string. When writing enum data, only the Key string part is used, for example:

INSERT INTO Enum_TEST VALUES('ready');

INSERT INTO Enum_TEST VALUES('start');

During data writing, the values are checked one by one against the enum set items. If the Key string is not within the set range, an exception will be thrown. For example, executing the following statement will cause an error:

INSERT INTO Enum_TEST VALUES('stop');

Some might think that String could completely replace enums, so why is a dedicated enum type needed? This is for performance reasons. Although the Key in the enum definition is of type String, in all subsequent operations on the enum (including sorting, grouping, deduplication, filtering, etc.), the Int Value is used.

4. Nested

Nested types, as the name suggests, are a nested table structure. A data table can define any number of nested type fields, but each field's nesting level only supports one level, meaning nested types cannot be used within a nested table. For simple hierarchical or relational scenarios, using nested types is also a good choice. For example, the following nested_test is a simulated employee table, and its department field uses a nested type:

CREATE TABLE nested_test (

name String,

age UInt8 ,

dept Nested(

id UInt8,

name String

)

) ENGINE = Memory;

ClickHouse's nested types are different from traditional nested types, which can be quite confusing when first encountering them. Taking the above table as an example, if you interpret it literally, it's easy to understand nested_test and dept as a one-to-one containment relationship, but this is actually incorrect. If you don't believe it, try executing the following statement and see what happens:

INSERT INTO nested_test VALUES ('nauu',18, 10000, '研发部');

Exception on client:

Code: 53. DB::Exception: Type mismatch in IN or VALUES section. Expected: Array(UInt8). Got: UInt64

Note the exception message above; it indicates that an Array type is expected.

Now you should understand that nested types are essentially a multi-dimensional array structure. Each field in a nested table is an array, and the lengths of arrays between rows do not need to be aligned. So you need to adjust the INSERT statement above to the following form:

INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001,10002], ['研发部','技术支持中心','测试部']);

--Array lengths between rows do not need to be aligned

INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001], ['研发部','技术支持中心']);

Note that within the same row of data, the lengths of each array field must be equal. For example, in the following example, an exception is thrown because the array field lengths within the row are not aligned:

INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001], ['研发部','技术支持中心',

'测试部']);

DB::Exception: Elements 'dept.id' and 'dept.name' of Nested data structure 'dept' (Array columns) have different

When accessing data in nested types, dot notation must be used, for example:

SELECT name, dept.id, dept.name FROM nested_test

┌─name─┬─dept.id──┬─dept.name─────────────┐

│ bruce │ [16,17,18] │ ['研发部','技术支持中心','测试部']

3. Special Types

ClickHouse also has a category of unusual data types, which I define as special types.

1. Nullable

Strictly speaking, Nullable is not an independent data type; it's more like an auxiliary modifier that needs to be used together with basic data types. The Nullable type is somewhat similar to Java 8's Optional object, indicating that a basic data type can be Null. Its specific usage is as follows:

CREATE TABLE Null_TEST (

c1 String,

c2 Nullable(UInt8)

) ENGINE = TinyLog;

After being modified by Nullable, the c2 field can be written with Null values:

INSERT INTO Null_TEST VALUES ('nauu',null)

INSERT INTO Null_TEST VALUES ('bruce',20)

SELECT c1 , c2 ,toTypeName(c2) FROM Null_TEST

┌─c1───┬───c2─┬─toTypeName(c2)─┐

│ nauu │ NULL │ Nullable(UInt8) │

│ bruce │ 20 │ Nullable(UInt8) │

└─────┴──────┴───────────┘

There are two more points worth noting when using the Nullable type. First, it can only be used with basic types; it cannot be used with composite types like arrays and tuples, nor can it be used as an index field. Second, Nullable types should be used with caution, including tables with Nullable columns, as this can slow down query and write performance. Under normal circumstances, data for each column field is stored in the corresponding [Column].bin file. If a column field is modified by the Nullable type, an additional [Column].null.bin file will be generated specifically to store its Null values. This means that reading and writing data requires an additional file operation.

2. Domain

Domain types are divided into IPv4 and IPv6, which are essentially further encapsulations of integers and strings. The IPv4 type is based on UInt32 encapsulation, and its specific usage is as follows:

CREATE TABLE IP4_TEST (

url String,

ip IPv4

) ENGINE = Memory;

INSERT INTO IP4_TEST VALUES ('www.nauu.com','192.0.0.0')

SELECT url , ip ,toTypeName(ip) FROM IP4_TEST

┌─url──────┬─────ip─┬─toTypeName(ip)─┐

│ www.nauu.com │ 192.0.0.0 │ IPv4 │

└────────┴───────┴──────────┘

Attentive readers might ask, why not just use a string directly? Why go through the extra trouble? I think there are at least two reasons.

(1) For convenience, for example, the IPv4 type supports format checking, and IP data with incorrect formats cannot be written, such as:

INSERT INTO IP4_TEST VALUES ('www.nauu.com','192.0.0')

Code: 441. DB::Exception: Invalid IPv4 value.

(2) For performance reasons, taking IPv4 as an example again, IPv4 uses UInt32 storage, which is more compact than String, takes up less space, and provides faster query performance. The IPv6 type is based on FixedString(16) encapsulation, and its usage is identical to IPv4, so it won't be repeated here.

One more thing to note when using Domain types: although they appear the same as String on the surface, Domain types are not strings, so they do not support implicit automatic type conversion. If you need to return the string form of an IP, you need to explicitly call the IPv4NumToString or IPv6NumToString functions for conversion.