Clickhouse数据类型
本章我们来介绍一下clickhouse的数据类型。从分类上讲可以划分为:基础类型、 复合类型和特殊类型。 其中基础类型使 ClickHouse 具备了描述数据的基本能力,而另外两种类型则使 ClickHouse 的数据表达能力更加丰富立体 。
基础类型
数值类型
数值类型分为整数 、 浮点数和定点数三类 。
核心功能说明: ClickHouse则直接使用Int8,Int16,Int32,Int64,Int128,Int256指代几种大小的Int类型,其末尾的数字正好表明了占用字节的大小(8位=1字节)
其取值范围如下:
- Int8—[-128:127]
- Int16—[-32768:32767]
- Int32—[-2147483648:2147483647]
- Int64—[-9223372036854775808:9223372036854775807]
- Int128—[-170141183460469231731687303715884105728:170141183460469231731687303715884105727]
- Int256—[-57896044618658097711785492504343953926634992332820282019728792003956564819968:57896044618658097711785492504343953926634992332820282019728792003956564819967]
ClickHouse支持无符号的整数,使用前缀U表示
- UInt8—[0:255]
- UInt16—[0:65535]
- UInt32—[0:4294967295]
- UInt64—[0:18446744073709551615]
- UInt128—[0:340282366920938463463374607431768211455]
- UInt256—[0:115792089237316195423570985008687907853269984665640564039457584007913129639935]
Float
与整数类似,ClickHouse直接使用Float32和Float64代表单精度浮点数以及双精度浮 点数。
在使用浮点数的时候,应当要意识到它是有限精度的。假如,分别对Float32和 Float64写入超过有效精度的数值,下面我们看看会发生什么。例如,将拥有20位小数的 数值分别写人Float32和Float64,此时结果就会出现数据误差
可以发现, Float32 从小数点后第 8 位起及 Float64 从小数点后第 17 位起,都产生了数据、溢出 。
Decimal 如果要求更高精度的数值运算 ,则需要使用定点数 。 ClickHouse 提供了 Decimal32 、 Decimal64 、 Decimal128 和Decimal256三种精度的定点数。
生方式为 Decimal(P ,时,其中:
- P 代表精度,决定总位数 (整数部分 + 小数部分),取值范围是 1~ 38;
- S 代表规模,决定小数位数 , 取值范围是 0~P 。
数据范围
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) )
Decimal256(S) - ( -1 * 10^(76 - S), 1 * 10^(76 - S) )
字符串类型
字符串类型可以细分为 String 、 FixedString 和 UUID 三类 。
String
字符串由 String 定义,长度不限 。 因此在使用 String的时候无须声明大小 。 它完全 代替了传统意义上数据库的 Varchar 、 Text 、 Clob 和 Blob 等字符类型 。
FixedString
FixedString 类型和传统 意义 上的 Char 类型有些类似,对于 一些字符有明确长度的场合,可以使用固定长度的字符串 。 定长字符串通过 FixedString(N) 声明,其中 N 表示字符串长度 。
但与 Char 不同的是, FixedString 使用 null 字节填充末尾字符,而 Char 通常使用空格填充。 比如在下面的例子中, 字柯:串 ' abc '虽然只有 3 位,但氏度却是 5 ,因为末尾有 2 位空字符填充 。
UUID
UUID是一种数据库常见的主键类型,在ClickHouse中直接把它作为一种数据类型。 UUID共有32位,它的格式为8-4-4-4-12。如果一个UUID类型的字段在写人数据时没有 被赋值,则会依照格式使用0填充。
时间类型
时间类型分为DateTime、DateTime64和Date三类。ClickHouse目前没有时间戳类型。 时间类型最高的精度是秒,也就是说,如果需要处理毫秒、微秒等大于秒分辨率的时间, 则只能借助Uint类型实现。
DateTime
DateTime类型包含时、分、军l、信息,精确到秒,支持使用字符串形式写入
以字符串形式写入 INSERT INTO Datetime_TEST VALUES (' 2023-01-01 00:00:00 ' )
DateTime64
DateTime64可以记录亚秒,它在DateTime之上增加了精度的设置
INSERTI NTO Datetime64_ TEST VALUES ('2023-01-01 00:00:00.00 ' )
Date
Date类型不包含具体的时间信息,只精确到天,它同样也支持字符串形式写人:
INSERT INTO Datetime_TEST VALUES (' 2023-01-01 ' )
复合类型
除了基础数据类型之外,ClickHouse还提供了数组、元组、枚举和嵌套四类复合类型。 这些类型通常是其他数据库原生不具备的特性。
Array
数组有两种定义形式,常规方式 array(T):
SELECT array (1 , 2) as a , toTypeName(a)
Tuple
元组类型 由 1 ~n 个元素组成,每个元素之间允许设置不同的数据类型,且彼此之间不要求兼容。
与数组类似,元组也可 以使用两种方式定义,常规方式 tuple(T):
SELECT tuple(l, ' a ' , now()) AS x, toTypeName(x)
或者简写方式( T) :
SELECT (1 , 2 0' null ) AS x' toTypeName (x)
Enum
ClickHouse支持枚举类型,这是一种在定义常量时经常会使用的数据类型。ClickHouse 提供了Enum18和Enum16两种枚举类型
枚举固定使用(String:lnt)Key/Value键值对的形式定义数据,所以Enum18和Enum16分别会对应(String:lnt8)和(String:Int16),例如:
CREATE TABLE Enum TEST (
cl Enum8 (' ready ' = 1,' start ' = 2 , ' success ' = 3, 'error ' = 4)
) ENGINE = Memory ;
Nested
嵌套类型,顾名思义是一种嵌套表结构。一张数据表,可以定义任意多个嵌套类型字 段,但每个字段的嵌套层级只支持一级,即嵌套表内不能继续使用嵌套类型。对于简单场 景的层级关系或关联关系,使用嵌套类型也是一种不错的选择。
例如,下面的 nested test 是一张模拟的员I表 ,它的所属部门字段就使用了嵌套类型:
CREATE TABLE nested test (
name String,
age Uint8
dept Nested(
id Uint8,
name String
)
) ENGINE = Memory;
嵌套类型本质是一种多维数组的结构 。
嵌套表中的每个字段都是一个数组,并且行与行之间数组的长度无须对齐。
INSERT INTO nested tes t VALUES ('bruce', 30, [10000 , 10001, 10002],['研发部','技术支持中心', '测试部' ]);
在访问嵌套类型的数据时需要使用点符号 ,例如:
SELECT name, dept.id, dept.name FROM nested_test
特殊类型
ClickHouse还有一类不同寻常的数据类型,我将它们定义为特殊类型
Nullable准确来说,Nullable并不能算是一种独立的数据类型,它更像是一种辅助的修饰符,需要与基础数据类型一起搭配使用。
CREATE TABLE Null_TEST (
cl String ,
c2 Nullable (Uint8 )
) ENGINE = TinyLog ;
通过 Nullable 修饰后 c2 字段可以被写入 Null 值:
在使用 Nullable 类型的时候还有两点值得注意:
- 它只能和基础类型搭配使用,不能用于数组和元组这些复合类型,也不能作为索引字段
- 应该慎用Nullable类型
包括Nullable的数据表,不然会使查询和写人性能变慢。因为在正常情况下,每个列宇段的数据会被存储在对应的(Colurnn].bin文件中。如果一个列字段被Nullable类型修饰后,会额外生成一个[Column].null.bin文件专门保存它的Null值。这意味着在读取和写入数据时,需要一倍的额外文件操作。
Domain
域名类型分为IPv4和IPv6两类,本质上它们是对整型和字符串的进一步封装。IPv4 类型是基于Ulnt32封装的,它的具体用法如下所示:
CREATE TABLE IP4_TEST (
url String ,
ip IPv4 )
ENGINE = Memory ;
INSERT INTO IP4 _ TEST VALUES ('www.nauu.com’,t192000')
SELECT url , Ip , toTypeName(ip) FROM IP4_TEST
-
出于便捷性的考量,例如IPv4类型支持格式检查,格式错误的IP数据是无法被 写入的
-
出于性能的考量,同样以IPv4为例,IPv4使用Uint32存储,相比String更加紧 凑,占用的空间更小,查询性能更快。IPv6类型是基于FixedString(16)封装的,它的使用方法与IPv4别无二致,此处不再赘述。
-
另外它从表象上看起来与String一样,但Domain类型并不是字符串,所以它不支持隐式的自动类型转换。如果需要返回IP的字符串形式,则需要显式调用IPv4NumToString或IPv6NumToString函数进行转换
数据字典
ClickHouse支持一些特殊函数配合字典在查询中使用。将字典与函数结合使用比将JOIN操作与引用表结合使用更简单、更有效。
由于字典数据常驻内存的特性,所以它非常适合保存常量或经常使用的维度表数据,以避免不必要的JOIN查询。
NULL值不能存储在字典中。
内置字典
ClickHouse 目前只有一种内置字典一Yandex.Metrica 字典 。
内置字典在默认的情况下是禁用状态,需要开启后才能使用。开启它的方式也十分简 单,只需将config泪nl文件中path_to_regions_hierarchy_file和path_to_regions_names_files两项配置打开。
<path_to_regions_hierarchy_file>
/opt/geo/regions_hierarchy.txt
</path_to_regions_hierarchy_file>
<path_to_regions_names_files>
/opt/geo/
</path_to_regions_names_files>
使用内置字典
在知晓了内置字典的开启方式和Yandex.Metrica字典的数据模型之后,就可以配置字典的数据并使用它们了。首先,在/opt路径下新建geo目录:
#mkdir/opt/geo
最后,找到config.xml并开启内置字典。
至此,内置字典就已经全部设置好了,执行下面的语句就能够访问字典中的数据:
SELECTregionToName(toUInt32(20009))
┌─regionToName(toUInt32(20009))───┐
│BuenosAiresProvince│
└─────────────────────────────────┘
可以看到,对于Yandex.Metrica字典数据的访问,这里用到了regionToName
函数。类似这样的函数还有很多,在ClickHouse中它们被称为Yandex.Metrica函数。关于这套函数的更多用法,请参阅官方手册。
外部扩展字典
外部扩展字典是以插件形式注册到ClickHouse中的,由用户自行定义数据模式及数据来源。
目前扩展字典支持7种类型的内存布局和4类数据来源。相比内容十分有限的内置字典,扩展字典才是更加常用的功能。
准备字典数据
在接下来的篇幅中,会逐个介绍每种扩展字典的使用方法,包括它们的配置形式、数据结构及创建方法,但是在此之前还需要进行一些准备工作。
为了便于演示,此处事先准备了三份测试数据,它们均使用CSV格式。
其中,第一份是企业组织数据,它将用于flat
、hashed
、cache
、complex_key_hashed
和complex_key_cache
字典的演示场景。
这份数据拥有id
、code
和name
三个字段,数据格式如下所示:
1,"a0001","研发部"
2,"a0002","产品部"
3,"a0003","数据部"
4,"a0004","测试部"
5,"a0005","运维部"
6,"a0006","规划部"
7,"a0007","市场部"
第二份是销售数据,它将用于range_hashed字典的演示场景。这份数据拥有id、start、end和price四个字段,数据格式如下所示:
1,2016-01-01,2017-01-10,100
2,2016-05-01,2017-07-01,200
3,2014-03-05,2018-01-20,300
4,2018-08-01,2019-10-01,400
5,2017-03-01,2017-06-01,500
6,2017-04-09,2018-05-30,600
7,2018-06-01,2019-01-25,700
8,2019-08-01,2019-12-12,800
最后一份是asn数据,它将用于演示ip_trie字典的场景。这份数据拥有ip、asn和country三个字段,数据格式如下所示:
"82.118.230.0/24","AS42831","GB"
"148.163.0.0/17","AS53755","US"
"178.93.0.0/18","AS6849","UA"
"200.69.95.0/24","AS262186","CO"
"154.9.160.0/20","AS174","US"
可以从下面的地址获取到上述三份数据:
下载后,将数据文件上传到ClickHouse节点所在的服务器即可。
扩展字典配置文件的元素组成
扩展字典的配置文件由config.xml文件中的dictionaries_config配置项指定:
<!-- Configuration of external dictionaries. See:
https://clickhouse.yandex/docs/en/dicts/external_dicts/
-->
<dictionaries_config>*_dictionary.xml</dictionaries_config>
在默认的情况下,ClickHouse会自动识别并加载/etc/clickhouse-server目录下所有以_dictionary.xml结尾的配置文件。
同时ClickHouse也能够动态感知到此目录下配置文件的各种变化,并支持不停机在线更新配置文件。
在单个字典配置文件内可以定义多个字典,其中每一个字典由一组dictionary元素定义。在dictionary元素之下又分为5个子元素,均为必填项,它们完整的配置结构如下所示:
<?xml version="1.0"?>
<dictionaries>
<dictionary>
<name>dict_name</name>
<structure>
<!-- 字典的数据结构 -->
</structure>
<layout>
<!-- 在内存中的数据格式类型 -->
</layout>
<source>
<!-- 数据源配置 -->
</source>
<lifetime>
<!-- 字典的自动更新频率 -->
</lifetime>
</dictionary>
</dictionaries>
在上述结构中,主要配置的含义如下。
- name:字典的名称,用于确定字典的唯一标识,必须全局唯一,多个字典之间不允许重复。
- structure:字典的数据结构,5.2.3节会详细介绍。
- layout:字典的类型,它决定了数据在内存中以何种结构组织和存储。目前扩展字典共拥有7种类型,5.2.4节会详细介绍。
- source:字典的数据源,它决定了字典中数据从何处加载。目前扩展字典共拥有文件、数据库和其他三类数据来源,5.2.5节会详细介绍。
- lifetime:字典的更新时间,扩展字典支持数据在线更新,5.2.6节会详细介绍。
扩展字典的数据结构
扩展字典的数据结构由structure元素定义,由键值key和属性attribute两部分组成,它们分别描述字典的数据标识和字段属性。structure的完整形式如下所示(在后面的查询过程中都会通过这些字段来访问字典中的数据):
<dictionary>
<structure>
<!-- <id> 或 <key> -->
<id>
<!--Key属性-->
</id>
<attribute>
<!--字段属性-->
</attribute>
...
</structure>
</dictionary>
-
key
key用于定义字典的键值,每个字典必须包含1个键值key字段,用于定位数据,类似数据库的表主键。键值key分为数值型和复合型两类。
(1)数值型:数值型key由UInt64整型定义,支持flat、hashed、range_hashed和cache类型的字典(扩展字典类型会在后面介绍),它的定义方法如下所示。
<structure> <id> <!—名称自定义--> <name>Id</name> </id> 省略…
(2)复合型:复合型key使用Tuple元组定义,可以由1到多个字段组成,类似数据库中的复合主键。它仅支持complex_key_hashed、complex_key_cache和ip_trie类型的字典。其定义方法如下所示。
<structure> <key> <attribute> <name>field1</name> <type>String</type> </attribute> <attribute> <name>field2</name> <type>UInt64</type> </attribute> 省略… </key> 省略…
-
attribute
attribute用于定义字典的属性字段,字典可以拥有1到多个属性字段。它的完整定义方法如下所示:
<structure> 省略… <attribute> <name>Name</name> <type>DataType</type> <!—空字符串--> <null_value></null_value> <expression>generateUUIDv4()</expression> <hierarchical>true</hierarchical> <injective>true</injective> <is_object_id>true</is_object_id> </attribute> 省略… </structure>
在attribute元素下共有7个配置项,其中name、type和null_value为必填项。这些配置项的详细说明如表所示。
文章内容引自ClickHouse原理解析与应用实践