ClickHouse 数据类型全解析及实际应用

2022-03-28 08:10:22 浏览数 (1)

一、数据类型

基础类型只有数值、字符串和时间三种类型，没有 Boolean 类型，但可以使用整型的 0 或 1 替代。ClickHouse 的数据类型和常见的其他存储系统的数据类型对比:

官网：https://clickhouse.tech/docs/zh/sql-reference/data-types/

1.1、数值类型

数值类型分为整数、浮点数和定点数三类

1.1.1、整数

在普遍观念中，常用Tinyint、Smallint、Int 和 Bigint 指代整数的不同取值范围。而 ClickHouse 则直接 使用Int8、Int16、Int32 和 Int64 指代 4 种大小的 Int 类型，其末尾的数字正好表明了占用字节的大小 (8位=1字节)。ClickHouse 支持无符号的整数，使用前缀 U 表示。固定长度的整型，包括有符号整 型或无符号整型。

整型范围:( -2^{n-1}到 2^{n-1}-1):

代码语言：javascript复制
Int8 - [-128 : 127]
Int16 - [-32768 : 32767]
Int32 - [-2147483648 : 2147483647]
Int64 - [-9223372036854775808 : 9223372036854775807]

无符号整型范围( 0到 2^{n-1}-1):

代码语言：javascript复制
UInt8 - [0 : 255]
UInt16 - [0 : 65535]
UInt32 - [0 : 4294967295]
UInt64 - [0 : 18446744073709551615]

1.1.2、浮点型

与整数类似，ClickHouse 直接使用 Float32 和 Float64 代表单精度浮点数以及双精度浮点数

ClickHouse 的浮点类型有两种值:

代码语言：javascript复制
Float32 - float
Float64 - double

建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示，因为浮点型进行计算时可能引起四舍五入的误差。

代码语言：javascript复制
SELECT 1 - 0.9
┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘
1 rows in set. Elapsed: 0.002 sec.

与标准 SQL 相比，ClickHouse 支持以下类别的浮点数:

Inf - 正无穷:

代码语言：javascript复制
bigdata02 :) select 1 / 0;
SELECT 1 / 0
┌─divide(1, 0)─┐
│          inf │
└──────────────┘
1 rows in set. Elapsed: 0.001 sec.

-Inf - 负无穷:

代码语言：javascript复制
bigdata02 :) select -1 / 0;
SELECT -1 / 0
┌─divide(-1, 0)─┐
│          -inf │
└───────────────┘
1 rows in set. Elapsed: 0.001 sec.

NaN - 非数字:

代码语言：javascript复制
bigdata02 :) select 0 / 0;
SELECT 0 / 0
┌─divide(0, 0)─┐
│          nan │
└──────────────┘
1 rows in set. Elapsed: 0.001 sec.

在使用浮点数的时候，应当要意识到它是有限精度的。假如，分别对 Float32 和 Float64 写入超过有效 精度的数值，下面我们看看会发生什么。例如，将拥有 20 位小数的数值分别写入 Float32 和 Float64， 此时结果就会出现数据误差:

代码语言：javascript复制
bigdata02 :) select toFloat32('0.1234567901234567890123456789') as a,
toTypeName(a);
┌──────────a─┬─toTypeName(toFloat32('0.1234567901234567890123456789'))─┐
│ 0.12345679 │ Float32                                                 │
└────────────┴─────────────────────────────────────────────────────────┘
bigdata02 :) select toFloat64('0.1234567901234567890123456789') as a,
toTypeName(a);
┌───────────────────a─┬─toTypeName(toFloat64('0.1234567901234567890123456789'))─┐
│ 0.12345679012345678 │ Float64│
└─────────────────────┴─────────────────────────────────────────────────────────┘

可以发现，Float32 从小数点后第 8 位起及 Float64 从小数点后第 17 位起，都产生了数据溢出。

1.1.3、Decimal

如果要求更高精度的数值运算，则需要使用定点数。ClickHouse 提供了Decimal32、Decimal64 和 Decimal128 三种精度的定点数。

可以通过两种形式声明定点:简写方式有 Decimal32(S)、 Decimal64(S)、Decimal128(S)三种，原生方式为Decimal(P, S)，其中:

P代表精度，决定总位数(整数部分 小数部分)，取值范围是1~38; S代表规模，决定小数位数，取值范围是0~P。

简写方式与原生方式的对应关系

在使用两个不同精度的定点数进行四则运算的时候，它们的小数点位数 S 会发生变化。在进行加法运算 时，S 取最大值。例如下面的查询，toDecimal64(2,4) 与 toDecimal32(2,2) 相加后 S=4:

代码语言：javascript复制
select toDecimal64(2, 4)   toDecimal32(2, 2);

结果:

代码语言：javascript复制
┌─plus(toDecimal64(2, 4), toDecimal32(2, 2))─┐
│                                     4.0000 │
└────────────────────────────────────────────┘

注意 Decimal 进行加减乘除四则运算的时候的精度问题!总结一下是:

1.1.4、布尔型

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。而且在 ClickHouse 使用过 程中，你也会发现，做比较得到的结果都是 1 或者 0，而不是通常意义上的 True 或者 False

代码语言：javascript复制
select 1 == 1;
select 1 == 2;
1.2、字符串

字符串类型可以细分为 String、FixedString 和 UUID 三类

1.2.1、String类型

字符串可以任意长度的。它可以包含任意的字节集，包含空字节，可以用来替换 VARCHAR ,BLOB,CLOB 等数据类型。字符串由String定义，长度不限。因此在使用 String 的时候无须声明大小。它完全代替了传统意义上数据库的Varchar、Text、Clob 和 Blob 等字符类型。String 类型不限定字符 集，因为它根本就没有这个概念，所以可以将任意编码的字符串存入其中。但是为了程序的规范性和可 维护性，在同一套程序中应该遵循使用统一的编码，例如“统一保持UTF-8编码”就是一种很好的约定。

代码语言：javascript复制
create table dylan_test02(
    id UInt8,
    name String
) engine = Memory;

1.2.2、FixedString(N)

FixedString 类型和传统意义上的 Char 类型有些类似，对于一些字符有明确长度的场合，可以使用固定长度的字符串。定长字符串通过 FixedString(N) 声明，其中 N 表示字符串长度。但与 Char 不同的是， FixedString 使用 null字节填充末尾字符，而 Char 通常使用空格填充。比如在下面的例子中，字符串 ‘abc’ 虽然只有 3 位，但长度却是5，因为末尾有2位空字符填充:

代码语言：javascript复制
SELECT toFixedString('abc', 5), LENGTH(toFixedString ('abc',5))AS LENGTH;

固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字 符串末尾添加空字节来达到 N 字节长度。当服务端读取长度大于 N 的字符串时候，将返回错误消息。与 String 相比，极少会使用 FixedString，因为使用起来不是很方便。

总结:

A、N是最大字节数(Byte),不是字符长度,如果是UTF8字符串,那么就会占用3字节,GBK会占用2字节. B、当内容少于N,数据库会自动在右填充空字节(null byte)(跟PGsql不一样,PGsql填充的是空格),当内 容大于N时候,会抛出错误. C、当写入内容后面后空字节,系统不会自动去裁剪,查询的时候也会被输出(mysql不会输出) D、FixedString(N) 比 String 支持更少的方法

1.2.3、UUID

UUID 是一种数据库常见的主键类型，在 ClickHouse 中直接把它作为一种数据类型。UUID 共有 32 位，它的格式为00000000-0000-0000-0000-000000000000。如果一个 UUID 类型的字段在写入数据时没有被赋值，则会依照格式使用 0 填充，例如:

代码语言：javascript复制
-- 建表
create table uuid_test(
c1 UUID,
    c2 String
) engine = Memory;
-- 插入数据
insert into uuid_test select generateUUIDv4(), 't1'; insert into uuid_test (c2) select 't2';
-- 查询数据
select * from uuid_test;

自行执行结果可以看到，第二行没有被赋值的 UUID 被 0 填充了。

1.3. 日期时间类型

时间类型分为 DateTime、DateTime64 和 Date 三类。ClickHouse 目前没有时间戳类型。时间类型最 高的精度是秒，也就是说，如果需要处理毫秒、微秒等大于秒分辨率的时间，则只能借助 UInt 类型实 现。

代码语言：javascript复制
Date:  2020-02-02
DateTime: 2020-02-02 20:20:20
DateTime64: 2020-02-02 20:20:20.335

1.3.1. Date类型

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

Date类型不包含具体的时间信息，只精确到天，它支持字符串形式写入。需要注意的是:日期中没有存储时区信息。默认情况下，客户端连接到服务的时候会使用服务端时区。

可以通过启用客户端命令行选项 --use_client_time_zone 来设置使用客户端时间。

代码语言：javascript复制
-- 建表
drop table if exists date_test; create table date_test(
    c1 Date
) engine = Memory;
-- 插入数据
insert into date_test values('2021-04-25');
--查询
select c1, toTypeName(c1) from date_test;

1.3.2. DateTime类型

时间戳类型。用四个字节(无符号的整数类型，Uint32)存储 Unix 时间戳)。允许存储与 Date 类型相同的范围内的值。最小值为 1970-01-01 00:00:00。时间戳类型值精确到秒(没有闰秒)。

DateTime 类型包含时、分、秒信息，精确到秒，支持使用字符串形式写入。

总结:

A、因为是Unsigned的整形，因此不能支持1970年1月1日(UTC/GMT的午夜)以前的时间。 B、时区会影响输入和输出。请使–use_client_time_zone 进行切换时区，服务端启动时候最好使用TZ=X 来保证时区

代码语言：javascript复制
-- 建表
create table datetime_test(
    c1 DateTime
) engine = Memory;
-- 插入数据
insert into datetime_test values('2021-04-25 10:20:30');
--查询
select c1, toTypeName(c1) from datetime_test;

1.3.3. DateTime64类型

DateTime64 可以记录亚秒，它在 DateTime 之上增加了精度的设置，例如:

代码语言：javascript复制
-- 建表
drop table if exists datetime64_test; create table datetime64_test(
    c1 DateTime64(2),
    c2 DateTime64(4)
) engine = Memory;
-- 插入数据
insert into datetime64_test (c1, c2) values('2021-04-25 10:20:30', '2021-04-25 10:20:30.333');
--查询
select c1, toTypeName(c1), c2, toTypeName(c2) from datetime64_test;
1.4. 复合类型

除了基础数据类型之外，ClickHouse 还提供了数组、元组、枚举和嵌套四类复合类型。这些类型通常是其他数据库原生不具备的特性。拥有了复合类型之后，ClickHouse 的数据模型表达能力更强了。

1.4.1. 枚举类型

ClickHouse 支持枚举类型，这是一种在定义常量时经常会使用的数据类型。ClickHouse 提供了 Enum8 和Enum16 两种枚举类型，它们除了取值范围不同之外，别无二致。枚举固定使用(String:Int) Key/Value键值对的形式定义数据，所以Enum8和Enum16分别会对应(String:Int8) 和 (String:Int16)包括 Enum8 和 Enum16 类型。Enum 保存 'string'= integer 的对应关系。建表的时候 是 不允许重复的。其次， Key/Value 不能同时为 Null ，但是Key允许空字符串。在定义枚举集合的时候，有几点需要注意。首先，Key和Value是不允许重复的，要保证唯一性。其次， Key和Value的值都不能为Null，但Key允许是空字符串。在写入枚举数据的时候，只会用到Key字符串部分

代码语言：javascript复制
Enum8 用 'String'= Int8 对描述 
Enum16 用 'String'= Int16 对描述

用法演示:创建一个带有一个枚举 Enum8('a' = 1, 'b' = 2, 'c'=3) 类型的列:

代码语言：javascript复制
CREATE TABLE dylan_enum_test (x Enum8('a' = 1, 'b' = 2, 'c'=3)) ENGINE = TinyLog;

这个 res 列只能存储类型定义中列出的值: 'a' 或 'b' 或者 'c' 。如果尝试保存任何其他值， ClickHouse 抛出异常。

插入正常数据:

代码语言：javascript复制
INSERT INTO dylan_enum_test VALUES ('a'), ('b'), ('c');

查询结果:

代码语言：javascript复制
select * from dylan_enum_test;

插入异常数据:

代码语言：javascript复制
insert into dylan_enum_test values('test');

如果需要看到对应行的数值，则必须将 Enum 值转换为整数类型。

代码语言：javascript复制
SELECT CAST(res, 'Int8') FROM dylan_enum_test;

可能有人会觉得，完全可以使用String代替枚举，为什么还需要专门的枚举类型呢?这是出于性能的考虑。因为虽然枚举定义中的Key属于String类型，但是在后续对枚举的所有操作中(包括排序、分组、 去重、过滤等)，会使用Int类型的Value值。

1.4.2. 数组

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

可以使用array函数来创建数组:

代码语言：javascript复制
array(T)

也可以使用方括号:

代码语言：javascript复制
[]

创建数组:

代码语言：javascript复制
SELECT array(1, 2) AS x, toTypeName(x);
SELECT [1, 2] AS x, toTypeName(x);

ClickHouse 会自动检测数组元素，并根据元素计算出存储这些元素最小的数据类型。如果在元素中存 在 Null 或存在 Nullable 类型元素，那么数组的元素类型将会变成 Nullable 。

如果 ClickHouse 无法确定数据类型，它将产生异常。当尝试同时创建一个包含字符串和数字的数组时 会发生这种情况 ( SELECT array(1, 'a') )。

代码语言：javascript复制
SELECT array(1, 2, NULL) AS x, toTypeName(x);

如果尝试创建不兼容的数据类型数组，ClickHouse 将引发异常:

代码语言：javascript复制
SELECT array(1, 'a');

1.4.3. 元组

元组类型由1~n个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。元组同 样支持类型推断，其推断依据仍然以最小存储代价为原则。与数组类似，元组也可以使用两种方式定 义，常规方式tuple(T)。

元素类型和泛型的作用类似，可以进一步保障数据质量。在数据写入的过程中会进行类型检查。Tuple(T1, T2, ...):元组，其中每个元素都有单独的类型。

创建元组的示例:

代码语言：javascript复制
SELECT tuple(1,'a') AS x, toTypeName(x);
SELECT tuple(1,'a') AS x, toTypeName(x), x.1, x.2;

创建一张带tuple字段的表:

代码语言：javascript复制
drop table dylan_tuple_table;
CREATE TABLE dylan_tuple_table (t Tuple(Int8, String, Array(String), Array(Int8)))
ENGINE = TinyLog;

插入数据:

代码语言：javascript复制
INSERT INTO dylan_tuple_table VALUES((1, 'a', ['a', 'b', 'c'], [1, 2, 3])),(tuple(11, 'A', ['A', 'B', 'C'], [11, 22, 33]));

查看数据:

代码语言：javascript复制
SELECT t, t.2, t.3, t.4 FROM dylan_tuple_table;

1.4.4. 嵌套类型

嵌套类型，顾名思义是一种嵌套表结构。一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系，使用嵌套类型也是一种不错的选择。嵌套Nested(Name1 Type1，Name2 Type2，...) 嵌套的数据结构就像一个嵌套的表。嵌套数据结构的参数 - 列名和类型 - 与在CREATE查询中的指定方式相同。每个表的行可以对应于嵌套数据结构中的任意数量的行。

如下：

代码语言：javascript复制
drop table dylan_test_table;
CREATE TABLE dylan_nest_table (
    name String,
    age Int8,
    dept Nested(
id UInt8,
name String )
)
ENGINE = TinyLog;

插入数据:

代码语言：javascript复制
insert into dylan_test_table values ('tt', 1, 1, '哎'); 
insert into dylan_test_table values ('tt1', 2, (1, '哟'));

插入报错，核心异常信息为:

代码语言：javascript复制
DB::Exception: Type mismatch in IN or VALUES section. Expected: Array(UInt8).Got: UInt64

通过此信息显示，异常显示需要 Array ，而不是单纯的 Int 。所以这里也就明白:嵌套类型本质是一 个多维数组的结构。嵌套类型的一个字段对应一个数组。字段对应的数组内的数量没有限制，但是字段 之间需要数组内的数量对齐。

正确的插入语句应该为:

代码语言：javascript复制
insert into dylan_test_table values ('tt', 1, [1], ['哎']); 
insert into dylan_test_table values ('tt1', 1, [1,2,3], ['python','spark','flink']);

其实，在Hive中，有一种复杂类型叫做 Struct，跟当前这种情况很类似，但是根据经验，推荐尽量少使用 Nested 类型

查询:

代码语言：javascript复制
select * from dylan_test_table;

结果:

代码语言：javascript复制
┌─name────┬─age─┬─dept.id─┬─dept.name────────────┐ 
│ tt │ 1 │ [1] │ ['哎'] │ 
│ tt1 │ 1 │ [1,2,3] │ ['python','spark','flink'] │
└─────────┴─────┴─────────┴──────────────────────┘
1.5. 其他类型

1.5.1. Nullable(TypeName)

准确来说，Nullable 并不能算是一种独立的数据类型，它更像是一种辅助的修饰符，需要与基础数据类 型一起搭配使用。Nullable 类型与 Java8 的 Optional 对象有些相似，它表示某个基础数据类型可以是 Null 值。

可以用特殊标记 (NULL) 表示 "缺失值"，可以与 TypeName 的正常值存放一起。例如，Nullable(Int8) 类 型的列可以存储 Int8 类型值，而没有值的行将存储 NULL。

官网解释:

允许用特殊标记 (NULL) 表示«缺失值»，可以与 TypeName 的正常值存放一起。例如， Nullable(Int8) 类型的列可以存储 Int8 类型值，而没有值的行将存储 NULL。 对于 TypeName，不能使用复合数据类型 阵列 和 元组。复合数据类型可以包含 Nullable 类型值，例如 Array(Nullable(Int8))。 Nullable 类型字段不能包含在表索引中。 除非在 ClickHouse 服务器配置中另有说明，否则 NULL 是任何 Nullable 类型的默认值。

特点:

  1. Nullable 只能和基本类型搭配使用;
  2. 不能使用在 Array/Tuple 这种复合类型上;
  3. 不能作为索引字段【Order by()】;
  4. 慎用 Nullable ，写入写出性能不好。因为它会生成单独的文件。

用法:

代码语言：javascript复制
--建表
create table dylan_null_test(
c1 String,
    c2 Nullable(UInt8)
) engine = TinyLog;
--插入数据
insert into dylan_null_test values ('aa', null); 
insert into dylan_null_test values ('aa', 1);
--查询数据
select c1, c2 from dylan_null_test;

在使用 Nullable 类型的时候还有两点值得注意:

首先，它只能和基础类型搭配使用，不能用于数组和 元组这些复合类型，也不能作为索引字段;

其次，应该慎用 Nullable 类型，包括 Nullable 的数据表， 不然会使查询和写入性能变慢。因为在正常情况下，每个列字段的数据会被存储在对应的 [Column].bin 文件中。如果一个列字段被Nullable 类型修饰后，会额外生成一个 [Column].null.bin 文件专门保存它 的 Null 值。这意味着在读取和写入数据时，需要一倍的额外文件操作。

1.5.2. Domain

域名类型分为 IPv4 和 IPv6 两类，本质上它们是对整型和字符串的进一步封装。IPv4 类型是基于 UInt32 封装的，它的具体用法如下所示:

分为 IPv4 IPv6 。其实本质都是对整型，字符串进行的封装。

代码语言：javascript复制
IPv4 使用 UInt32 存储。如 110.253.30.113
IPv6 使用 FixedString(16) 存储。如 2e02:ea08:e010:4100::2

用法:

代码语言：javascript复制
-- 建表
drop table if exists dylan_ip_test; 
create table dylan_ip_test(
url String,
    ip IPv4
) engine = Memory;
-- 插入数据
insert into dylan_ip_test values ('www.baidu.com', '110.253.30.113');
-- 查询数据
select url, ip, toTypeName(ip) from dylan_ip_test;

疑问:为什么不直接使用字符串来代替 IPv4 类型呢?

1、出于便捷性的考量，例如IPv4类型支持格式检查，格式错误的IP数据是无法被写入的 2、出于性能的考量，同样以IPv4为例，IPv4使用UInt32存储，相比String更加紧凑，占用的空间更小， 查询性能更快。IPv6类型是基于FixedString(16)封装的，它的使用方法与IPv4别无二致

在使用Domain类型的时候还有一点需要注意，虽然它从表象上看起来与String一样，但Domain类型并不是字符串，所以它不支持隐式的自动类型转换。如果需要返回IP的字符串形式，则需要显式调用 IPv4NumToString或IPv6NumToString函数进行转换。

java ipv6 编程算法 tcp/ip python

0 人点赞