ClickHouse数据类型

ClickHouse支持非常丰富的数据类型，如果从数据类型和函数的角度去考虑，甚至可以认为ClickHouse SQL是一门编程语言。ClickHouse中的system.data_type_families表也列出了支持的数据类型。

1. 整型

Int8/TINYINT/BOOL/BOOLEAN/INT1 — [-128 : 127]
Int16/SMALLINT/INT2 — [-32768 : 32767]
Int32/INT/INT4/INTEGER — [-2147483648 : 2147483647]
Int64/BIGINT — [-9223372036854775808 : 9223372036854775807]
Int128 — [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]
Int256 — [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]
UInt8 — [0 : 255]
UInt16 — [0 : 65535]
UInt32 — [0 : 4294967295]
UInt64 — [0 : 18446744073709551615]
UInt128 — [0 : 340282366920938463463374607431768211455]
UInt256 — [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935]

2. 浮点型

Float32/FLOAT — float
Float64/DOUBLE — double
Inf/-Inf — 无穷大，例如 SELECT 0.5 / 0 的结果为inf
NaN — 非数值，例如 SELECT 0 / 0 的结果为NaN

浮点型在计算时容易导致精度丢失，所以在表示金额、时间等要求较高的场景尽量使用整型类型。

3. Decimal

Decimal(P, S) — P精度，有效范围:[1:76]，确定数字可以有多少个十进制位数(包括小数位)。S规模，有效范围:[0:P]。确定数字可以有多少十进制小数位。
Decimal32(S) — 对应P范围[1 : 9]，(-1 * 10^(9 - S), 1 * 10^(9 - S))
Decimal64(S) — 对应P范围[10 : 18]，(-1 * 10^(18 - S), 1 * 10^(18 - S))
Decimal128(S) — 对应P范围[19 : 38]，(-1 * 10^(38 - S), 1 * 10^(38 - S))
Decimal256(S) — 对应P范围[39 : 76]，(-1 * 10^(76 - S), 1 * 10^(76 - S))

例如，Decimal32(4)可以包含-99999.9999到99999.9999之间的数字。由于现代cpu本身不支持128位整数（寄存器限制），因此Decimal128类型是模拟类型，工作速度也明显慢于Decimal32/Decimal64。

对于加减操作，结果类型S = max(S1, S2)。对于乘法，结果类型S = S1 + S2。对于除法，结果类型S = S1。

※注意：使用Decimal类型要注意内存溢出问题。

4. Boolean

BOOL/BOOLEAN

Boolean类型的底层存储类型是UInt8。

CREATE TABLE test_bool
(
`A` Int64,
`B` Bool
)
ENGINE = Memory;
INSERT INTO test_bool VALUES (1, true),(2,0);
SELECT * FROM test_bool;
┌─A─┬─B─────┐
│ 1 │ true
│
│ 2 │ false │
└───┴───────┘

5. String

String/LONGTEXT/MEDIUMTEXT/TINYTEXT/TEXT/LONGBLOB/MEDIUMBLOB/TINYBLOB/BLOB/VARCHAR/CHAR

对于其他关系型数据库中的VARCHAR, BLOB, CLOB等类型，ClickHouse统一定义为String类型。

6. FixedString

<column_name> FixedString(N)

长度固定的N字节字符串，如长度固定的ID值、MD5值等。如果是UUID值，请使用UUID类型。

插入数据时，如果字符串包含少于N个字节，则用空字节补充字符串，如果没有插入任何字符，则全部用空字节填充，length函数的返回值永远是常数N，但是如果都是空字节，empty函数会返回1。如果字符串包含超过N个字节，则抛出FixedString(N)的过大值异常。在查询数据时，ClickHouse不会删除字符串末尾的空字节。如果使用WHERE子句，应该手动添加空字节来匹配FixedString值。例如定义FixedString(2)类型的字段a，WHERE a = ‘b’ 不会返回任何结果，WHERE a = ‘b’ 才会返回为’b’记录。

7. UUID

UUID

UUID类型的默认值是00000000-0000-0000-0000-000000000000，可以通过generateUUIDv4函数生成UUID。

CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog;
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1';
SELECT * FROM t_uuid;
┌────────────────────────────────────x─┬─y─────────┐
│ 417ddc5d-e556-4d27-95dd-a34d84e46a50 │ Example 1 │
└──────────────────────────────────────┴───────────┘

UUID数据类型只支持String数据类型也支持的函数(例如min、max和count)，不支持算术运算函数，如abs、sum等。

8. 日期时间类型

8.1 Date

Date类型以两个字节存储，表示自1970-01-01(无符号)以来的天数，支持范围[1970-01-01, 2149-06-06]，不包含时区信息。

CREATE TABLE dt
(
`timestamp` Date,
`event_id` UInt8
)
ENGINE = TinyLog;
INSERT INTO dt VALUES (1546300800, 1), ('2019-01-01', 2);
SELECT * FROM dt;
┌──timestamp─┬─event_id─┐
│ 2019-01-01 │
1 │
│ 2019-01-01 │
2 │
└────────────┴──────────┘

8.2 Date32

Date32类型以四个字节存储，表示从1925-01-01开始的天数。允许存储值到2283-11-11。

CREATE TABLE new
(
`timestamp` Date32,
`event_id` UInt8
)
ENGINE = TinyLog;
INSERT INTO new VALUES (4102444800, 1), ('2100-01-01', 2);
SELECT * FROM new;

8.3 Datetime

DateTime([timezone])

支持范围：[1970-01-01 00:00:00, 2106-02-07 06:28:15]，最小单位是秒。可以通过 SELECT * FROM system.time_zones 查找支持的时区。

DateTime类型的时区信息不是存储在数据行中，而是存储在列字段的元数据中，在创建表时，可以显式地为DateTime类型的列设置时区。如果没有设置时区，那么ClickHouse将使用ClickHouse服务配置文件中的时区参数值或ClickHouse服务器启动时的操作系统时区。

DateTime类型的输出格式取决于date_time_output_format 设置，默认是YYYY-MM-DD hh:mm:ss，还可以通过formatDateTime函数格式化输出。在向ClickHouse插入数据时，可以使用不同格式的日期和时间字符串，具体取决于date_time_input_format设置的值，默认是YYYY-MM-DD hh:mm:ss。当插入datetime为整数时，它被视为Unix Timestamp (UTC)，然后转为定义的时区值。

CREATE TABLE dt
(
`timestamp` DateTime('Asia/Istanbul'),
`event_id` UInt8
)
ENGINE = TinyLog;
INSERT INTO dt Values (1546300800, 1), ('2019-01-01 00:00:00', 2);
SELECT * FROM dt WHERE timestamp = toDateTime('2019-01-01 00:00:00', 'Asia/Istanbul');
SELECT * FROM dt WHERE timestamp = '2019-01-01 00:00:00';

可以使用字符串值过滤DateTime列值，它将自动转换为DateTime。

8.4 Datetime64

DateTime64(precision, [timezone])

Datetime64支持纳秒精度，支持范围[1925-01-01 00:00:00, 2283-11-11 23:59:59.99999999]。precision范围[ 0 : 9 ]，常用定义：,

• 3，milliseconds 毫秒
• 6，microseconds 微秒
• 9，nanoseconds 纳秒

在插入数据时，Datetime64同样支持字符串格式写入，但是在where过滤时，和DateTime不同，DateTime64值不会自动从String转换。

CREATE TABLE dt
(
`timestamp` DateTime64(3, 'Asia/Istanbul'),
`event_id` UInt8
)
ENGINE = TinyLog;
INSERT INTO dt Values (1546300800123, 1), (1546300800.123, 2), ('2019-01-01 00:00:00', 3);
SELECT * FROM dt;
┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00.123 │
1 │
│ 2019-01-01 03:00:00.123 │
2 │
│ 2019-01-01 00:00:00.000 │
3 │
└─────────────────────────┴──────────┘

当插入为正数时，后3位表示毫秒精度。当插入为小数时，只有小数位为毫秒精度，需要注意区分。

9. Enum

除了上面常见的数据类型，ClickHouse还支持枚举类型，枚举类型值可以通过字符串或者数字指定。ClickHouse底层都是按照数字存储的，但是支持按照名称操作。

当插入数据时，ClickHouse自动选择8bit和16bit的Enum类型，还可以使用Enum8或Enum16类型来确定存储的大小。8-bit Enum可以包含256个枚举值，范围为[-128, 127]，16-bit Enum可以包含65536个枚举值，范围为[-32768, 32767]。

CREATE TABLE t_enum
(
x Enum('hello' = 1, 'world' = 2)
-- x Enum('hello', 'world')
-- x Enum('hello' = 1, 'world')
)
ENGINE = TinyLog;
SELECT CAST(x, 'Int8') FROM t_enum;
SELECT toTypeName(CAST('a', 'Enum('a' = 1, 'b' = 2)')); -- 类型转换

定义枚举类型可以省略数字，ClickHouse将自动分配连续的数字，默认从1开始分配号码。也可以指定起始数字。列x只能存储类型定义中列出的值:‘hello’或’world’。如果试图保存任何其他值，ClickHouse将引发异常。这个Enum的8位大小是自动选择的。如果需要查看记录对应的数值，则必须将Enum值转换为整型。

上述定义的Enum类型不能为空，如果需要保存NULL，则需要定义如下：

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

在ORDER BY、GROUP BY、IN、DISTINCT等过程中，enum的行为与相应的数字相同，尤其注意ORDER BY是按照对应数字排序的。Enum值不能与数字进行比较，可以与枚举类型中包含的常量字符串进行比较。如果所比较的字符串不是Enum的有效值，则会抛出异常。支持IN操作符，左边是Enum，右边是一组字符串，字符串是对应Enum的值。如果需要对Enum类型字段做字符串操作，如拼接一个字符串，则可以调用toString函数将Enum类型转为字符串。也可以调用toT系列函数转为数字，但是T最好和枚举类型保存的数据类型一样大小，这样转换过程就是0代价。可以使用ALTER添加和删除Enum的成员(只有删除的值从未在表中使用时，删除才是安全的)。作为一种保护措施，更改先前定义的Enum成员的数值将引发异常。使用ALTER也可以将Enum8更改为Enum16，反之亦然，就像将Int8更改为Int16一样。

10. LowCardinality

在常见数据库系统的类型体系中，字符串是最灵活、包容性最强的类型，但是存储成本无疑也最高。为了降低存储成本，提高查询效率，很多DBMS都定义了最大长度的字符串类型，如VARCHAR2(10)等。ClickHouse同样也提供了两种基于String的变种类型优化效率：存储固定长度（按字节数计）字符串的FixedString类型和将字符串转为定长整形枚举值的Enum类型。但是毫无疑问，这两种类型都有局限，尤其是对于大多数长度不固定，且枚举值过多（>100）或者暂时不能穷举全部枚举值的场景。基于此，ClickHouse又提供了一种新的数据类型——LowCardinality（低基数类型）。

LowCardinality是一种改变数据存储方法和数据处理规则的上层修饰类型。ClickHouse将其他数据类型的内部表示形式更改为字典编码，对于许多应用程序，使用字典编码的数据可以显著提高SELECT查询的性能。

LowCardinality(data_type)

其中type表示的原始类型可以是String、FixedString、Date、DateTime，以及除了Decimal之外的所有数值类型。但是，LowCardinality的设计初衷就是为了优化字符串存储，修饰其他类型的效率未必会更高，所以一般多用于data_type为String的场景。在处理字符串时，即使字段是暂时可枚举额的，在字段值不确定的情况下，也建议使用LowCardinality而不是Enum，LowCardinality在使用上提供了更高的灵活性，并且经常显示出相同或更高的效率。

使用LowCardinality数据类型的效率取决于数据多样性（该字段的count(distinct)值）。如果一个字典包含少于10000个不同的值，那么ClickHouse通常会显示更高的数据读取和存储效率。如果一个字典包含超过100000个不同的值，那么与使用普通数据类型相比，ClickHouse的性能可能更差。

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

LowCardinality类型不仅查询效率更高，而且存储压缩率也更高，是怎么实现的呢？LowCardinality是一种改变数据存储方法和数据处理规则的上层结构，通过字典编码和倒排索引存储相应字段。简单说就是把该字段的所有去重后的值作为字典的key存储，值在数据库记录中的位置（存储偏移量）作为value存储，这样在where条件查询的时候可以根据key直接命中所有符合条件的记录位置，再去查询其他列即可。而对于某些函数，LowCardinality同样可以起到加速效果，如length函数，对于值相同的记录，只需要计算对应key的长度，然后返回多个即可。

ClickHouse还提供了以下参数控制LowCardinality类型的使用：

• low_cardinality_max_dictionary_size：控制单个字典的大小阈值，默认为8192。也就是说，如果LowCardinality(String)列的基数大于该阈值，就会被拆分成多个字典文件存储。
• low_cardinality_use_single_dictionary_for_part：控制是否允许创建多个字典（默认允许）。
• allow_suspicious_low_cardinality_types：是否允许或限制对固定大小为8字节或更小的数据类型使用LowCardinality数值型数据类型和FixedString。对于较小的固定值，使用LowCardinality通常是低效的，因为ClickHouse需要为每一行存储一个数字索引，磁盘空间使用率可能上升，内存消耗可能会更高，由于额外的编码工作，一些函数的工作速度可能会变慢。

※注意：因为需要建立字典，LowCardinality类型会降低数据写入速度。

11. Array

array(T)

数组类型，泛型T可以是任何数据类型，包括数组。需要注意数组的起始索引为1。定义Array类型可以通过array函数，也可以通过[]直接定义：

SELECT array(1, 2) AS x, toTypeName(x);
┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8)
│
└───────┴─────────────────────────┘
SELECT [1, 2] AS x, toTypeName(x);
┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8)
│
└───────┴────────────────────┘

数组的最大大小限制为100万个元素。在动态创建数组时，像上面一样，ClickHouse自动将参数类型定义为能够存储所有列出的参数的最窄数据类型。如果有任何NULL值，数组元素的类型也会变成Nullable。但是数组元素必须属于同一类值，例如 array(1, ‘a’) 就会报错，因为数字和字符串不属于同一类。

可以通过sizeN查看嵌套数组的大小：

CREATE TABLE t_arr (`arr` Array(Array(Array(UInt32)))) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO t_arr VALUES ([[[12, 13, 0, 1],[12]]]);
SELECT arr.size0, arr.size1, arr.size2 FROM t_arr;
┌─arr.size0─┬─arr.size1─┬─arr.size2─┐
│
1 │ [2]
│ [[4,1]]
│
└───────────┴───────────┴───────────┘

12. Tuple

Array类型虽然可以存储多个字段，但是有一个明显的缺陷，就是数组的所有元素类型必须一致。我们是否可以在一个字段中存储多个类型不同的业务数据呢？答案是可以的。Tuple（元祖）类型数据每个元素可以有一个单独的类型。

tuple(T1, T2, ...)

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

可以分别使用索引和名称读取命名元组中的元素:

CREATE TABLE named_tuples (`a` Tuple(s String, i Int64)) ENGINE = Memory;
INSERT INTO named_tuples VALUES (('y', 10)), (('x',-10));
SELECT a.s FROM named_tuples;
SELECT a.2 FROM named_tuples;
┌─a.s─┐
│ y
│
│ x
│
└─────┘
┌─tupleElement(a, 2)─┐
│
10 │
│
-10 │
└────────────────────┘

13. Map

除了Array和Tuple，ClickHouse还支持Map类型。

Map(key, value)

key可以是 String, Integer, LowCardinality, FixedString, UUID, Date, DateTime, Date32, Enum。value可以是任意类型，包括Array和Map。在查询的时候，Map类型字段a可以根据key索引查找：a[‘key’]。

CREATE TABLE table_map (a Map(String, UInt64)) ENGINE=Memory;
INSERT INTO table_map VALUES ({'key1':1, 'key2':10}), ({'key1':2,'key2':20}), ({'key1':3,'key2':30});
SELECT a['key2'] FROM table_map;
┌─arrayElement(a, 'key2')─┐
│
10 │
│
20 │
│
30 │
└─────────────────────────┘

如果Map类型中不存在要查找的key，则如果value类型是数值类型会返回0，如果value类型是字符串会返回空字符串，如果value类型是数组会返回空数组。

可以发现Tuple和Map类型是很像的，他们之间也可以互相转换：

SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS map;
┌─map───────────────────────────┐
│ {1:'Ready',2:'Steady',3:'Go'} │
└───────────────────────────────┘

Tuple和Map的不同点是Map的key和value类型是固定的，但是字段的长度是不固定的，可以有长度不等的键值对，而Tuple的长度是固定的，但是类型是不固定的，第一个元素、第二个、…、第n个元素的类型可以互不相同。

在某些情况下，可能我们只需要Map的key或者value，此时可以使用键和值子列，而不是读取整个列来优化处理。

SELECT a.keys FROM t_map;
SELECT a.values FROM t_map;
┌─a.keys─────────────────┐
│ ['key1','key2','key3'] │
└────────────────────────┘
┌─a.values─┐
│ [1,2,3]
│
└──────────┘

14. Nested

Nested（嵌套数据类型）也是ClickHouse特有的复合数据类型，它的定义格式非常类似于C语言中的结构体：

CREATE TABLE TrainInfo
(
UserID UInt32,
Weight Decimal32(2),
BodyTemperature Decimal32(2),
Place String,
Coach String,
Food Sting,
RecordInfo Nested
(
TrainTime Datetime,
Record Decimal32(4)
)
)ENGINE=Memory;

咋一看Nested类型似乎和Tuple很像，实则不然，Nested中的每个字段都是一个数组，并且行与行之间的数组长度无须对齐，尤其适用于明细数据重复字段很高的场景。

假如国家游泳队需要记录运动员的训练成绩，需要记录的有运动员的编号、姓名、当天食物、体重、体温、训练地点、训练时间、训练成绩、指导教练等，每个运动员每天需要练习多次，而不同运动员每天练习的次数肯定是不同的。那么这些数据应该怎么存储呢？首先，姓名、编号信息一般是不变的，食物、体重、体温、训练地点、指导教练这些数据是一天记录一次，训练时间、训练成绩则需要每次记录。可以按照每次训练成绩保存一条记录，但是如果某个运动员一天练习20次，那么体重、地点这些信息就要记录20次，显然会存在大量冗余数据。当然也可以日变化的数据记录在一张表中，训练成绩记录在另一张表中，然后通过编号关联查询，也就是在MySQL数据库中的典型存储模式。但是每次查询join必然要花费时间，而且在ClickHouse中我们要尽量避免使用join查询，此时就可以考虑使用Nested存储。上面的建表语句就是记录运动员信息的表结构，插入某个运动员的2022年8月5号的训练数据如下：

INSERT INTO TrainInfo VALUES (10033, 63.8, 36.7, '1号训练场', '李伟', '牛肉、南瓜、米饭、土豆', ['2022-08-05 10:16:00','2022-08-05 10:35:00','2022-08-05 11:02:00'],[8.35, 8.26, 8.22]);
SELECT UserID, RecordInfo.TrainTime, RecordInfo.Record FROM TrainInfo WHERE UserID=10033;
┌─a.UserID──────RecordInfo.TrainTime─────────────────────────────────────────────────RecordInfo.Record─┐
│ 10033
│ ['2022-08-05 10:16:00','2022-08-05 10:35:00','2022-08-05 11:02:00'] │ [8.35, 8.26, 8.22]
└──────────────────────────────────────────────────────────────────────────────────────────────────────┘

如果想进行列转行，查询每一条明细记录，只需要使用ARRAY JOIN查询即可。

15. Multiword

Multiword类型并不是一种单独的数据类型，只是ClickHouse为了兼容其他数据库类型而定义的类型别名，一般很少使用，也不建议使用，只需知道是什么意思即可：

16. Domains

Domains（域）是特殊用途的类型，它在现有基类型的基础上添加了一些额外的特性，但保持基础数据类型的格式不变。目前，ClickHouse支持的域类型有IPv4和IPv6。

16.1 IPv4

IPv4是一个基于UInt32类型的域，用于存储IPv4值的类型替换。它提供了紧凑的存储与人性化的输入输出格式和列类型信息的检查。

CREATE TABLE hits (url String, from IPv4) ENGINE = MergeTree() ORDER BY from;
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;

底层数据以更小空间的二进制形式存储:

SELECT toTypeName(from), hex(from) FROM hits LIMIT 1;
┌─toTypeName(from)─┬─hex(from)─┐
│ IPv4
│ B7F7E83A
│
└──────────────────┴───────────┘

域值不能隐式转换为UInt32以外的类型。如果你想将IPv4值转换为字符串，你必须使用IPv4NumToString()函数显式转换，或者使用CAST转为UInt32。

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

16.2 IPv6

IPv6是一个基于FixedString(16)类型的域，用于存储IPv6值。它提供了紧凑的存储与人性化的输入输出格式和列类型信息的检查。

CREATE TABLE hits (url String, from IPv6) ENGINE = MergeTree() ORDER BY from;
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;

底层数据同样以紧凑的二进制形式存储。域值不能隐式转换为除FixedString(16)以外的类型。如果你想将IPv6值转换为字符串，你必须使用IPv6NumToString()函数显式转换，或者使用CAST函数显示转为FixedString(16)：

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

最后

以上就是虚拟柠檬为你收集整理的ClickHouse数据类型的全部内容，希望文章能够帮你解决ClickHouse数据类型所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。