ClickHouse数据类型

大白兔黑又黑

已于 2022-08-07 15:48:53 修改

阅读量2k

点赞数

分类专栏：大数据 # ClickHouse 文章标签：数据库 ClickHouse 大数据

于 2022-07-31 22:00:15 首次发布

本文链接：https://blog.csdn.net/haveanybody/article/details/126085228

版权

大数据同时被 2 个专栏收录

19 篇文章 8 订阅

订阅专栏

ClickHouse

10 篇文章 5 订阅

订阅专栏

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个字节，则用空字节\0补充字符串，如果没有插入任何字符，则全部用空字节填充，length函数的返回值永远是常数N，但是如果都是空字节，empty函数会返回1。如果字符串包含超过N个字节，则抛出FixedString(N)的过大值异常。在查询数据时，ClickHouse不会删除字符串末尾的空字节。如果使用WHERE子句，应该手动添加空字节来匹配FixedString值。例如定义FixedString(2)类型的字段a，WHERE a = ‘b’ 不会返回任何结果，WHERE a = ‘b\0’ 才会返回为’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为了兼容其他数据库类型而定义的类型别名，一般很少使用，也不建议使用，只需知道是什么意思即可：

Multiword types	Simple types
DOUBLE PRECISION	Float64
CHAR LARGE OBJECT	String
CHAR VARYING	String
CHARACTER LARGE OBJECT	String
CHARACTER VARYING	String
NCHAR LARGE OBJECT	String
NCHAR VARYING	String
NATIONAL CHARACTER LARGE OBJECT	String
NATIONAL CHARACTER VARYING	String
NATIONAL CHAR VARYING	String
NATIONAL CHARACTER	String
NATIONAL CHAR	String
BINARY LARGE OBJECT	String
BINARY VARYING	String

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;