ClickHouse 可以在数据表中存储多种数据类型。
本节描述 ClickHouse 支持的数据类型,以及使用或者实现它们时(如果有的话)的注意事项。
由 T
类型元素组成的数组。
T
可以是任意类型,包含数组类型。 但不推荐使用多维数组,ClickHouse 对多维数组的支持有限。例如,不能存储在 MergeTree
表中存储多维数组。
您可以使用array函数来创建数组:
array(T)
您也可以使用方括号:
[]
创建数组示例:
:) SELECT array(1, 2) AS x, toTypeName(x)
SELECT
[1, 2] AS x,
toTypeName(x)
┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8) │
└───────┴─────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
ClickHouse会自动检测数组元素,并根据元素计算出存储这些元素最小的数据类型。如果在元素中存在 [NULL] 或存在 [可为空] 类型元素,那么数组的元素类型将会变成 [可为空]。
如果 ClickHouse 无法确定数据类型,它将产生异常。当尝试同时创建一个包含字符串和数字的数组时会发生这种情况 (SELECT array(1, 'a')
)。
自动数据类型检测示例:
:) SELECT array(1, 2, NULL) AS x, toTypeName(x)
SELECT
[1, 2, NULL] AS x,
toTypeName(x)
┌─x──────────┬─toTypeName(array(1, 2, NULL))─┐
│ [1,2,NULL] │ Array(Nullable(UInt8)) │
└────────────┴───────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
如果您尝试创建不兼容的数据类型数组,ClickHouse 将引发异常:
:) SELECT array(1, 'a')
SELECT [1, 'a']
Received exception from server (version 1.1.54388):
Code: 386. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString and some of them are not.
0 rows in set. Elapsed: 0.246 sec.
没有单独的类型来存储布尔值。可以使用 UInt8 类型,取值限制为 0 或 1。
日期类型,用两个字节存储,表示从 1970-01-01 (无符号) 到当前的日期值。允许存储从 Unix 纪元开始到编译阶段定义的上限阈值常量(目前上限是2106年,但最终完全支持的年份为2105)。最小值输出为1970-01-01。
日期中没有存储时区信息。
时间戳类型。用四个字节(无符号的)存储 Unix 时间戳)。允许存储与日期类型相同的范围内的值。最小值为 1970-01-01 00:00:00。时间戳类型值精确到秒(没有闰秒)。
使用启动客户端或服务器时的系统时区,时间戳是从文本(分解为组件)转换为二进制并返回。在文本格式中,有关夏令时的信息会丢失。
默认情况下,客户端连接到服务的时候会使用服务端时区。您可以通过启用客户端命令行选项 --use_client_time_zone
来设置使用客户端时间。
因此,在处理文本日期时(例如,在保存文本转储时),请记住在夏令时更改期间可能存在歧义,如果时区发生更改,则可能存在匹配数据的问题。
此类型允许以日期(date)加时间(time)的形式来存储一个时刻的时间值,具有定义的亚秒精度
时间刻度大小(精度):10-精度 秒
语法:
DateTime64(precision, [timezone])
在内部,此类型以Int64类型将数据存储为自Linux纪元开始(1970-01-01 00:00:00UTC)的时间刻度数(ticks)。时间刻度的分辨率由precision参数确定。此外,DateTime64
类型可以像存储其他数据列一样存储时区信息,时区会影响 DateTime64
类型的值如何以文本格式显示,以及如何解析以字符串形式指定的时间数据 (‘2020-01-01 05:00:01.000’)。时区不存储在表的行中(也不在resultset中),而是存储在列的元数据中。详细信息请参考 [DateTime] 数据类型.
1. 创建一个具有 DateTime64
类型列的表,并向其中插入数据:
CREATE TABLE dt
(
`timestamp` DateTime64(3, 'Europe/Moscow'),
`event_id` UInt8
)
ENGINE = TinyLog
INSERT INTO dt Values (1546300800000, 1), ('2019-01-01 00:00:00', 2)
SELECT * FROM dt
┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00.000 │ 1 │
│ 2019-01-01 00:00:00.000 │ 2 │
└─────────────────────────┴──────────┘
1546300800000
(精度为3)表示 '2019-01-01 00:00:00'
UTC. 不过,因为 timestamp
列指定了 Europe/Moscow
(UTC+3)的时区,当作为字符串输出时,它将显示为 '2019-01-01 03:00:00'
'2019-01-01 00:00:00'
将被认为处于 Europe/Moscow
时区并被存储为 1546290000000
.2. 过滤 DateTime64
类型的值
SELECT * FROM dt WHERE timestamp = toDateTime64('2019-01-01 00:00:00', 3, 'Europe/Moscow')
┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 00:00:00.000 │ 2 │
└─────────────────────────┴──────────┘
与 DateTime
不同, DateTime64
类型的值不会自动从 String
类型的值转换过来
3. 获取 DateTime64
类型值的时区信息:
SELECT toDateTime64(now(), 3, 'Europe/Moscow') AS column, toTypeName(column) AS x
┌──────────────────column─┬─x──────────────────────────────┐
│ 2019-10-16 04:12:04.000 │ DateTime64(3, 'Europe/Moscow') │
└─────────────────────────┴────────────────────────────────┘
4. 时区转换
SELECT
toDateTime64(timestamp, 3, 'Europe/London') as lon_time,
toDateTime64(timestamp, 3, 'Europe/Moscow') as mos_time
FROM dt
┌───────────────lon_time──┬────────────────mos_time─┐
│ 2019-01-01 00:00:00.000 │ 2019-01-01 03:00:00.000 │
│ 2018-12-31 21:00:00.000 │ 2019-01-01 00:00:00.000 │
└─────────────────────────┴─────────────────────────┘
有符号的定点数,可在加、减和乘法运算过程中保持精度。对于除法,最低有效数字会被丢弃(不舍入)。
对于不同的 P 参数值 Decimal 表示,以下例子都是同义的:
-P从[1:9]-对于Decimal32(S)
-P从[10:18]-对于Decimal64(小号)
-P从[19:38]-对于Decimal128(S)
例如,Decimal32(4) 可以表示 -99999.9999 至 99999.9999 的数值,步长为0.0001。
数据采用与自身位宽相同的有符号整数存储。这个数在内存中实际范围会高于上述范围,从 String 转换到十进制数的时候会做对应的检查。
由于现代CPU不支持128位数字,因此 Decimal128 上的操作由软件模拟。所以 Decimal128 的运算速度明显慢于 Decimal32/Decimal64。
对Decimal的二进制运算导致更宽的结果类型(无论参数的顺序如何)。
Decimal64(S1) <op> Decimal32(S2) -> Decimal64(S)
Decimal128(S1) <op> Decimal32(S2) -> Decimal128(S)
Decimal128(S1) <op> Decimal64(S2) -> Decimal128(S)
精度变化的规则:
对于 Decimal 和整数之间的类似操作,结果是与参数大小相同的十进制。
未定义Decimal和Float32/Float64之间的函数。要执行此类操作,您可以使用:toDecimal32、toDecimal64、toDecimal128 或 toFloat32,toFloat64,需要显式地转换其中一个参数。注意,结果将失去精度,类型转换是昂贵的操作。
Decimal上的一些函数返回结果为Float64(例如,var或stddev)。对于其中一些,中间计算发生在Decimal中。对于此类函数,尽管结果类型相同,但Float64和Decimal中相同数据的结果可能不同。
在对 Decimal 类型执行操作时,数值可能会发生溢出。分数中的过多数字被丢弃(不是舍入的)。整数中的过多数字将导致异常。
SELECT toDecimal32(2, 4) AS x, x / 3
┌──────x─┬─divide(toDecimal32(2, 4), 3)─┐
│ 2.0000 │ 0.6666 │
└────────┴──────────────────────────────┘
SELECT toDecimal32(4.2, 8) AS x, x * x
DB::Exception: Scale is out of bounds.
SELECT toDecimal32(4.2, 8) AS x, 6 * x
DB::Exception: Decimal math overflow.
检查溢出会导致计算变慢。如果已知溢出不可能,则可以通过设置decimal_check_overflow
来禁用溢出检查,在这种情况下,溢出将导致结果不正确:
SET decimal_check_overflow = 0;
SELECT toDecimal32(4.2, 8) AS x, 6 * x
┌──────────x─┬─multiply(6, toDecimal32(4.2, 8))─┐
│ 4.20000000 │ -17.74967296 │
└────────────┴──────────────────────────────────┘
溢出检查不仅发生在算术运算上,还发生在比较运算上:
SELECT toDecimal32(1, 8) < 100
DB::Exception: Can't compare.
包括 Enum8
和 Enum16
类型。Enum
保存 'string'= integer
的对应关系。在 ClickHouse 中,尽管用户使用的是字符串常量,但所有含有 Enum
数据类型的操作都是按照包含整数的值来执行。这在性能方面比使用 String
数据类型更有效。
Enum8
用 'String'= Int8
对描述。Enum16
用 'String'= Int16
对描述。创建一个带有一个枚举 Enum8('hello' = 1, 'world' = 2)
类型的列:
CREATE TABLE t_enum
(
x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog
这个 x
列只能存储类型定义中列出的值:'hello'
或'world'
。如果您尝试保存任何其他值,ClickHouse 抛出异常。
:) INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')
INSERT INTO t_enum VALUES
Ok.
3 rows in set. Elapsed: 0.002 sec.
:) insert into t_enum values('a')
INSERT INTO t_enum VALUES
Exception on client:
Code: 49. DB::Exception: Unknown element 'a' for type Enum8('hello' = 1, 'world' = 2)
当您从表中查询数据时,ClickHouse 从 Enum
中输出字符串值。
SELECT * FROM t_enum
┌─x─────┐
│ hello │
│ world │
│ hello │
└───────┘
如果需要看到对应行的数值,则必须将 Enum
值转换为整数类型。
SELECT CAST(x, 'Int8') FROM t_enum
┌─CAST(x, 'Int8')─┐
│ 1 │
│ 2 │
│ 1 │
└─────────────────┘
在查询中创建枚举值,您还需要使用 CAST
。
SELECT toTypeName(CAST('a', 'Enum8(\'a\' = 1, \'b\' = 2)'))
┌─toTypeName(CAST('a', 'Enum8(\'a\' = 1, \'b\' = 2)'))─┐
│ Enum8('a' = 1, 'b' = 2) │
└──────────────────────────────────────────────────────┘
Enum8
类型的每个值范围是 -128 ... 127
,Enum16
类型的每个值范围是 -32768 ... 32767
。所有的字符串或者数字都必须是不一样的。允许存在空字符串。如果某个 Enum 类型被指定了(在表定义的时候),数字可以是任意顺序。然而,顺序并不重要。
Enum
中的字符串和数值都不能是 [NULL]。
Enum
包含在 可为空 类型中。因此,如果您使用此查询创建一个表
CREATE TABLE t_enum_nullable
(
x Nullable( Enum8('hello' = 1, 'world' = 2) )
)
ENGINE = TinyLog
不仅可以存储 'hello'
和 'world'
,还可以存储 NULL
。
INSERT INTO t_enum_nullable Values('hello'),('world'),(NULL)
在内存中,Enum
列的存储方式与相应数值的 Int8
或 Int16
相同。
当以文本方式读取的时候,ClickHouse 将值解析成字符串然后去枚举值的集合中搜索对应字符串。如果没有找到,会抛出异常。当读取文本格式的时候,会根据读取到的字符串去找对应的数值。如果没有找到,会抛出异常。
当以文本形式写入时,ClickHouse 将值解析成字符串写入。如果列数据包含垃圾数据(不是来自有效集合的数字),则抛出异常。Enum 类型以二进制读取和写入的方式与 Int8
和 Int16
类型一样的。
隐式默认值是数值最小的值。
在 ORDER BY
,GROUP BY
,IN
,DISTINCT
等等中,Enum 的行为与相应的数字相同。例如,按数字排序。对于等式运算符和比较运算符,Enum 的工作机制与它们在底层数值上的工作机制相同。
枚举值不能与数字进行比较。枚举可以与常量字符串进行比较。如果与之比较的字符串不是有效Enum值,则将引发异常。可以使用 IN 运算符来判断一个 Enum 是否存在于某个 Enum 集合中,其中集合中的 Enum 需要用字符串表示。
大多数具有数字和字符串的运算并不适用于Enums;例如,Enum 类型不能和一个数值相加。但是,Enum有一个原生的 toString
函数,它返回它的字符串值。
Enum 值使用 toT
函数可以转换成数值类型,其中 T 是一个数值类型。若 T
恰好对应 Enum 的底层数值类型,这个转换是零消耗的。
Enum 类型可以被 ALTER
无成本地修改对应集合的值。可以通过 ALTER
操作来增加或删除 Enum 的成员(只要表没有用到该值,删除都是安全的)。作为安全保障,改变之前使用过的 Enum 成员将抛出异常。
通过 ALTER
操作,可以将 Enum8
转成 Enum16
,反之亦然,就像 Int8
转 Int16
一样。
固定长度 N 的字符串(N 必须是严格的正自然数)。
您可以使用下面的语法对列声明为FixedString
类型:
<column_name> FixedString(N)
其中N
表示自然数。
当数据的长度恰好为N个字节时,FixedString
类型是高效的。 在其他情况下,这可能会降低效率。
可以有效存储在FixedString
类型的列中的值的示例:
FixedString(16)
)FixedString(16)
,SHA256使用FixedString(32)
)请使用[UUID]数据类型来存储UUID值,。
当向ClickHouse中插入数据时,
N
,将抛出Too large value for FixedString(N)
异常。当做数据查询时,ClickHouse不会删除字符串末尾的空字节。 如果使用WHERE
子句,则须要手动添加空字节以匹配FixedString
的值。 以下示例阐明了如何将WHERE
子句与FixedString
一起使用。
考虑带有FixedString(2)
列的表:
┌─name──┐
│ b │
└───────┘
查询语句SELECT * FROM FixedStringTable WHERE a = 'b'
不会返回任何结果。请使用空字节来填充筛选条件。
SELECT * FROM FixedStringTable
WHERE a = 'b\0'
┌─a─┐
│ b │
└───┘
这种方式与MySQL的CHAR
类型的方式不同(MySQL中使用空格填充字符串,并在输出时删除空格)。
请注意,FixedString(N)
的长度是个常量。仅由空字符组成的字符串,函数[length]返回值为N
,而函数[empty]的返回值为1
。
浮点数。
类型与以下 C 语言中类型是相同的:
Float32
- float
Float64
- double
我们建议您尽可能以整数形式存储数据。例如,将固定精度的数字转换为整数值,例如货币数量或页面加载时间用毫秒为单位表示
SELECT 1 - 0.9
┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘
计算的结果取决于计算方法(计算机系统的处理器类型和体系结构)
浮点计算结果可能是诸如无穷大(INF
)和«非数字»(NaN
)。对浮点数计算的时候应该考虑到这点。
当一行行阅读浮点数的时候,浮点数的结果可能不是机器最近显示的数值。
与标准SQL相比,ClickHouse 支持以下类别的浮点数:
Inf
– 正无穷SELECT 0.5 / 0
┌─divide(0.5, 0)─┐
│ inf │
└────────────────┘
-Inf
– 负无穷SELECT -0.5 / 0
┌─divide(-0.5, 0)─┐
│ -inf │
└─────────────────┘
NaN
– 非数字
SELECT 0 / 0
┌─divide(0, 0)─┐
│ nan │
└──────────────┘
可以在 [ORDER BY 子句] 查看更多关于 NaN
排序的规则。
固定长度的整型,包括有符号整型或无符号整型。
把其它数据类型转变为字典编码类型。
LowCardinality(data_type)
参数
data_type
— [String], [FixedString], [Date], [DateTime],包括数字类型,但是[Decimal]除外。对一些数据类型来说,LowCardinality
并不高效,详查[allow_suspicious_low_cardinality_types]设置描述。LowCardinality
是一种改变数据存储和数据处理方法的概念。 ClickHouse会把 LowCardinality
所在的列进行dictionary coding。对很多应用来说,处理字典编码的数据可以显著的增加[SELECT]查询速度。
使用 LowCarditality
数据类型的效率依赖于数据的多样性。如果一个字典包含少于10000个不同的值,那么ClickHouse可以进行更高效的数据存储和处理。反之如果字典多于10000,效率会表现的更差。
当使用字符类型的时候,可以考虑使用 LowCardinality
代替[Enum]。 LowCardinality
通常更加灵活和高效。
创建一个 LowCardinality
类型的列:
CREATE TABLE lc_t
(
`id` UInt16,
`strings` LowCardinality(String)
)
ENGINE = MergeTree()
ORDER BY id
设置:
函数:
允许用特殊标记 ([NULL]) 表示«缺失值»,可以与 TypeName
的正常值存放一起。例如,Nullable(Int8)
类型的列可以存储 Int8
类型值,而没有值的行将存储 NULL
。
对于 TypeName
,不能使用复合数据类型 [阵列] 和 [元组]。复合数据类型可以包含 Nullable
类型值,例如Array(Nullable(Int8))
。
Nullable
类型字段不能包含在表索引中。
除非在 ClickHouse 服务器配置中另有说明,否则 NULL
是任何 Nullable
类型的默认值。
要在表的列中存储 Nullable
类型值,ClickHouse 除了使用带有值的普通文件外,还使用带有 NULL
掩码的单独文件。 掩码文件中的条目允许 ClickHouse 区分每个表行的 NULL
和相应数据类型的默认值。 由于附加了新文件,Nullable
列与类似的普通文件相比消耗额外的存储空间。
!!! 注意点 "注意点"
使用 Nullable
几乎总是对性能产生负面影响,在设计数据库时请记住这一点
掩码文件中的条目允许ClickHouse区分每个表行的对应数据类型的«NULL»和默认值由于有额外的文件,«Nullable»列比普通列消耗更多的存储空间
CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog
INSERT INTO t_null VALUES (1, NULL), (2, 3)
SELECT x + y FROM t_null
┌─plus(x, y)─┐
│ ᴺᵁᴸᴸ │
│ 5 │
└────────────┘
字符串可以任意长度的。它可以包含任意的字节集,包含空字节。因此,字符串类型可以代替其他 DBMSs 中的 VARCHAR、BLOB、CLOB 等类型。
ClickHouse 没有编码的概念。字符串可以是任意的字节集,按它们原本的方式进行存储和输出。
若需存储文本,我们建议使用 UTF-8 编码。至少,如果你的终端使用UTF-8(推荐),这样读写就不需要进行任何的转换了。
同样,对不同的编码文本 ClickHouse 会有不同处理字符串的函数。
比如,length
函数可以计算字符串包含的字节数组的长度,然而 lengthUTF8
函数是假设字符串以 UTF-8 编码,计算的是字符串包含的 Unicode 字符的长度。
元组,其中每个元素都有单独的 [类型]。
不能在表中存储元组(除了内存表)。它们可以用于临时列分组。在查询中,IN 表达式和带特定参数的 lambda 函数可以来对临时列进行分组。更多信息,请参阅 [IN 操作符] 和 [高阶函数]。
元组可以是查询的结果。在这种情况下,对于JSON以外的文本格式,括号中的值是逗号分隔的。在JSON格式中,元组作为数组输出(在方括号中)。
可以使用函数来创建元组:
tuple(T1, T2, ...)
创建元组的示例:
:) SELECT tuple(1,'a') AS x, toTypeName(x)
SELECT
(1, 'a') AS x,
toTypeName(x)
┌─x───────┬─toTypeName(tuple(1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String) │
└─────────┴───────────────────────────┘
1 rows in set. Elapsed: 0.021 sec.
在动态创建元组时,ClickHouse 会自动为元组的每一个参数赋予最小可表达的类型。如果参数为 [NULL],那这个元组对应元素是 [可为空]。
自动数据类型检测示例:
SELECT tuple(1, NULL) AS x, toTypeName(x)
SELECT
(1, NULL) AS x,
toTypeName(x)
┌─x────────┬─toTypeName(tuple(1, NULL))──────┐
│ (1,NULL) │ Tuple(UInt8, Nullable(Nothing)) │
└──────────┴─────────────────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
通用唯一标识符(UUID)是用于标识记录的16字节数。 有关UUID的详细信息,请参阅 维基百科.
UUID类型值的示例如下所示:
61f0c404-5cb3-11e7-907b-a6006ad3dba0
如果在插入新记录时未指定UUID列值,则UUID值将用零填充:
00000000-0000-0000-0000-000000000000
要生成UUID值,ClickHouse提供了 [generateuidv4] 功能。
示例1
此示例演示如何创建具有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 │
└──────────────────────────────────────┴───────────┘
示例2
在此示例中,插入新记录时未指定UUID列值。
INSERT INTO t_uuid (y) VALUES ('Example 2')
SELECT * FROM t_uuid
┌────────────────────────────────────x─┬─y─────────┐
│ 417ddc5d-e556-4d27-95dd-a34d84e46a50 │ Example 1 │
│ 00000000-0000-0000-0000-000000000000 │ Example 2 │
└──────────────────────────────────────┴───────────┘
UUID数据类型仅支持以下功能 [字符串] 数据类型也支持(例如, [min], [max],和 [计数]).
算术运算不支持UUID数据类型(例如, [abs])或聚合函数,例如 [sum] 和 [avg].
内容来源于网络,如有侵权,请联系作者删除!