ClickHouse简单使用（三）

7 ClickHouse 的常用数据类型

7.1 UUID

通用唯一标识符(UUID)是用于标识记录的16字节数。

UUID类型值的示例如下所示：

61f0c404-5cb3-11e7-907b-a6006ad3dba0

7.2 Decimal

Decimal(P,S)，Decimal32(S)，Decimal64(S)，Decimal128(S)

有符号的定点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃（不舍入）。

参数：

• P - 精度。有效范围：[1:38]，决定可以有多少个十进制数字（包括分数）。
• S - 规模。有效范围：[0：P]，决定数字的小数部分中包含的小数位数。

对于不同的 P 参数值 Decimal(P, S) 和以下例子是同义的：
-P从[1:9]，对于Decimal32(S)
-P从[10:18]，对于Decimal64(小号)
-P从[19:38]，对于Decimal128(S)

Decimal值的范围：

• Decimal32(S) - ( -1 * 10^(9 - S),1*10^(9-S) )
• Decimal64(S) - ( -1 * 10^(18 - S),1*10^(18-S) )
• Decimal128(S) - ( -1 * 10^(38 - S),1*10^(38-S) )

例如，Decimal32(4) 可以表示 -99999.9999 至 99999.9999 的数值，步长为0.0001。

7.3 Float32, Float64

浮点数，类型与以下 C 语言中类型是相同的：

• Float32 - float
• Float64 - double

我们建议您尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，例如货币数量或页面加载时间用毫秒为单位表示

7.4 UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64

固定长度的整型，包括有符号整型或无符号整型。

整型范围：

• Int8-[-128:127]
• Int16-[-32768:32767]
• Int32-[-2147483648:2147483647]
• Int64-[-9223372036854775808:9223372036854775807]

无符号整型范围：

• UInt8-[0:255]
• UInt16-[0:65535]
• UInt32-[0:4294967295]
• UInt64-[0:18446744073709551615]

7.5 Tuple(T1, T2, …)

元组，其中每个元素都有单独的类型。

不能作为字段类型（除了内存表）。它们可以用于临时列分组。在查询中，IN 表达式和带特定参数的 lambda 函数可以来对临时列进行分组。更多信息，请参阅 IN 操作符和 高阶函数。

元组可以是查询的结果。在这种情况下，对于JSON以外的文本格式，括号中的值是逗号分隔的。在JSON格式中，元组作为数组输出（在方括号中）。

可以使用函数来创建元组：

tuple(T1, T2, ...)

 

创建元组的示例：

node01 :) 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.

7.6 Enum

包含命名值的枚举类型。

命名值必须声明为 'string' = integer 对。ClickHouse仅存储数字，但支持通过其名称使用值进行运算。

ClickHouse支持：

8位Enum。它最多可以包含该 [-128, 127] 范围内列举的256个值。

16位Enum。它最多可以包含该 [-32768, 32767] 范围内列举的65536个值。

ClickHouse会自动选择Enum插入数据的类型。您也可以使用Enum8或输入Enum16以确保存储大小。

用法示例：

创建一个带有一个枚举 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');

 

Ok.

 

insert into t_enum values('a');

 

Exception on client:

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

7.7 Nullable(typename)

允许用特殊标记 (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

7.8 Fixedstring(N)

固定长度 为N 的字符串（N 必须是严格的正整数）。

您可以使用下面的语法对列声明为FixedString类型：

<column_name> FixedString(N)

其中N表示自然数。

当数据的长度恰好为N个字节时，FixedString类型是高效的。 在其他情况下，这可能会降低效率。

可以有效存储在FixedString类型的列中的值的示例：

• 二进制表示的IP地址（IPv6使用FixedString(16)）
• 语言代码（ru_RU, en_US … ）
• 货币代码（USD, RUB … ）
• 二进制表示的哈希值（MD5使用FixedString(16)，SHA256使用FixedString(32)）

请使用UUID数据类型来存储UUID值。

当向ClickHouse中插入数据时，

• 如果字符串包含的字节数少于N，将对字符串末尾进行空字节填充。
• 如果字符串包含的字节数大于N，将抛出Too large value for FixedString(N)异常。

当做数据查询时，ClickHouse不会删除字符串末尾的空字节。 如果使用WHERE子句，则须要手动添加空字节以匹配FixedString的值。 以下示例阐明了如何将WHERE子句与FixedString一起使用。

7.9 String

字符串可以任意长度的。它可以包含任意的字节集，包含空字节。因此，字符串类型可以代替其他 DBMSs 中的 VARCHAR、BLOB、CLOB 等类型。

7.10 Boolean 

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。

7.11 Datetime64 

允许存储时间时刻，可以表示为日历日期和一天中的时间，具有定义的亚秒精度。

精度: 10^-precision  秒

语法：

DateTime64(precision, [timezone])

在内部，存储数据作为一些 ‘ticks’ 自纪元开始(1970-01-01 00:00:00UTC)作为Int64. 刻度分辨率由precision参数确定。 此外，该 DateTime64 类型可以存储时区是相同的整个列，影响如何的值 DateTime64 类型值以文本格式显示，以及如何解析指定为字符串的值 (‘2020-01-01 05:00:01.000’). 时区不存储在表的行中（或resultset中），而是存储在列元数据中。

7.12 Date

日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。允许存储从 Unix 纪元开始到编译阶段定义的上限阈值常量（目前上限是2106年，但最终完全支持的年份为2105）。最小值输出为1970-01-01。

示例：

CREATE TABLE dt

(

    `timestamp` Date,

    `event_id` UInt8

)

ENGINE = TinyLog;

INSERT INTO dt Values (1546300800, 1), ('2019-01-01', 2);

7.13 DateTime 

时间戳类型。用四个字节（无符号的）存储 Unix 时间戳）。允许存储与日期类型相同的范围内的值。最小值为 1970-01-01 00:00:00。时间戳类型值精确到秒（没有闰秒）

示例：

CREATE TABLE dt

(

      `timestamp` DateTime('Europe/Moscow'),

      `event_id` UInt8

)

ENGINE = TinyLog;

INSERT INTO dt Values (1546300800, 1), ('2019-01-01 00:00:00', 2);

7.14 LowCardinality(Low Cardinality data type)

把其它数据类型转变为字典编码类型。

语法：

LowCardinality(data_type)

LowCardinality 是一种改变数据存储和数据处理方法的概念。 ClickHouse会把 LowCardinality 所在的列进行字典编码。对很多应用来说，处理字典编码的数据可以显著的增加SELECT查询速度。

7.15 Domain 

Domain类型是特定实现的类型，它总是与某个现存的基础类型保持二进制兼容的同时添加一些额外的特性，以能够在维持磁盘数据不变的情况下使用这些额外的特性。目前ClickHouse暂不支持自定义domain类型。

如果你可以在一个地方使用与Domain类型二进制兼容的基础类型，那么在相同的地方您也可以使用Domain类型，例如：

使用Domain类型作为表中列的类型；

对Domain类型的列进行读/写数据；

如果与Domain二进制兼容的基础类型可以作为索引，那么Domain类型也可以作为索引；

将Domain类型作为参数传递给函数使用；

 

局限性：

1. 无法通过将基本类型的索引列转换为Domain类型ALTER TABLE。

2. 从另一列或表插入数据时，无法将字符串值隐式转换为Domain值。

3. Domain对存储的值不加任何限制。

 

下面是几种Domain 的子类型。

IPv4

IPv4是与UInt32类型保持二进制兼容的Domain类型，其用于存储IPv4地址的值。它提供了更为紧凑的二进制存储的同时支持识别可读性更加友好的输入输出格式。

基本用法：

CREATE TABLE hits (url String, from IPv4)

ENGINE = MergeTree() ORDER BY url;

IPv6 

IPv6是与FixedString(16)类型保持二进制兼容的Domain类型，其用于存储IPv6地址的值。它提供了更为紧凑的二进制存储的同时支持识别可读性更加友好的输入输出格式。

基本用法：

CREATE TABLE hits (url String, from IPv6)

ENGINE = MergeTree() ORDER BY url;

7.16 array(T) 

由 T 类型元素组成的数组。

T 可以是任意类型，包含数组类型。 但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。例如，不能存储在 MergeTree 表中存储多维数组。

您可以使用array函数来创建数组：

array(T)

也可以使用方括号：

[]

创建数组示例：

:) SELECT array(1, 2) AS x, toTypeName(x);

 

:) SELECT [1, 2] AS x, toTypeName(x);

7.17 Nested(name1 Type1, Name2 Type2, …) 

嵌套的数据结构就像单元格内的表格。 嵌套数据结构的参数（列名和类型）的指定方式与CREATE TABLE查询中的指定方式相同。 每个表行都可以对应于嵌套数据结构中的任意数量的行。

示例：

CREATE TABLE test.visits

(

CounterID UInt32,

      StartDate Date,

      Sign Int8,

      IsNew UInt8,

      VisitID UInt64,

      UserID UInt64,

      ...

      Goals Nested

      (

         ID UInt32,

         Serial UInt32,

         EventTime DateTime,

         Price Int64,

         OrderID String,

         CurrencyID UInt32

      ),

      ...

) ENGINE = CollapsingMergeTree(StartDate, intHash32(UserID), (CounterID, StartDate, intHash32(UserID), VisitID), 8192, Sign);

 

此示例声明了目标嵌套数据结构，其中包含有关转化的数据（达到目标）。 “访问”表中的每一行都可以对应零次或任意数量的转化。