【用户画像】ClickHouse中的数据类型、表引擎介绍及使用、项目几个问题的解决办法

一 数据类型

1 整型

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

整型范围（-2^n-1~2^n-1-1）：

Int8 - [-128 : 127]
Int16 - [-32768 : 32767]
Int32 - [-2147483648 : 2147483647]		相当于int
Int64 - [-9223372036854775808 : 9223372036854775807]	相当于bigint

无符号整型范围（0~2n-1）：

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

使用场景： 个数、数量、也可以存储数字型id**。**

2 浮点型

Float32 - float
Float64 – double

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

其存储小数的方式为2进制，2进制不适合处理加减法，最适合处理位运算，其次为乘除法。

如下例，会出现精度丢失问题。

hadoop101 :) select 1-0.9

┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘

解决方案：将2进制转换为10进制，然后再进行加减，如Decimal。

使用场景：一般数据值比较小，不涉及大量的统计计算，精度要求不高的时候。比如保存商品的重量。

3 布尔型

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

4 Decimal 型

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

有三种声明：

Decimal32(s)，相当于Decimal(9-s,s)

Decimal64(s)，相当于Decimal(18-s,s) ==》 Decimal64(2) == Decimal(16,2)

Decimal128(s)，相当于Decimal(38-s,s)

s标识小数位，“ ，”前为整数位。

使用场景： 一般金额字段、汇率、利率等字段为了保证小数点精度，都使用Decimal进行存储。

5 字符串

（1）String

字符串可以任意长度的。它可以包含任意的字节集，包含空字节。

（2）FixedString(N)

固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的字符串时候，将返回错误消息。

与String相比，极少会使用FixedString，因为使用起来不是很方便。

使用场景：名称、文字描述、字符型编码。 固定长度的可以保存一些定长的内容，比如一些编码，性别等但是考虑到一定的变化风险，带来收益不够明显，所以定长字符串使用意义有限。

6 时间类型

目前clickhouse 有三种时间类型

Date 接受 年-月-日 的字符串比如 ‘2019-12-16’

Datetime 接受 年-月-日 时:分:秒 的字符串比如 ‘2019-12-16 20:50:10’

Datetime64 接受 年-月-日 时:分:秒.亚秒 的字符串比如 ‘2019-12-16 20:50:10.66’

日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值。

还有很多数据结构，可以参考官网文档

7 数组

**Array(T)：**由 T 类型元素组成的数组。

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

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

array(T)

也可以使用方括号：

[]

创建数组案例：

-- toTypeName(x)：查x的类型是什么
:) 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)       │
└───────┴────────────────────┘

8 元组

select tuple(1,2);

select (1,2);

二 三个小问题

• task-common如果编译不出classes 文件夹

  请手动在pom.xml中增加插件 一般2021版的idea

• 如果 clickhouse客户端无法正常连接服务器

  把config.xml 的配置

  <listen_host>::</listen_host>  打开，ipv6写法，部分环境不支持iP4的写法
  
  <listen_host>0.0.0.0</listen_host>  注掉，ipv4写法
  

  重启服务

• clickhouse启动不了

  用sudo journalctl –xe   来查看linux 服务启动日志 
  
  sudo vim /var/log/clickhouse-server/clickhouser-server.log 
  
  port  already in used  
  
  关闭  Zabbix       让出9000端口
  

三 表引擎

官网说明

引擎：表的存储过程和存储器，决定了如何将表数据和文件放到磁盘上，决定磁盘如何布局。

1 表引擎的使用

表引擎是clickhouse的一大特色。可以说， 表引擎决定了如何存储标的数据。包括：

1）数据的存储方式和位置

2）并发数据访问。

3）索引的使用。

4）是否可以执行多线程请求。

5）数据如何拷贝副本。

表引擎的使用方式就是必须显性在创建表时定义该表使用的引擎，以及引擎使用的相关参数。如：

create table t_tinylog ( id String, name String) **engine=TinyLog**;

特别注意：引擎的名称大小写敏感

2 TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，生产环境上作用有限。可以用于平时练习测试用。

3 Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过10G/s）。

一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景。

4 MergeTree

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）家族引擎及该系列（*MergeTree）中的其他引擎。地位可以相当于innodb之于Mysql。 而且基于MergeTree，还衍生出了很多家族成员，也是非常有特色的引擎。

建表语句

create table t_order_mt(
    uid UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time  Datetime
 ) engine = MergeTree
 -- 以下三条属性中，order by最关键
 -- 分区的目的：减少扫描（处理）范围
 -- toYYYYMMDD：一个函数，最终的数据格式为年月日，如20221020
   partition by toYYYYMMDD(create_time)
 -- 主键在MergeTree中不唯一，可以翻译为主索引
   primary key (uid)
 -- 最关键的，排序列，决定了哪些列可以作为索引 
 -- mysql中为稠密索引，所以数据可以无序，根据索引可以唯一定位到一行数据
 -- ClickHouse是系数索引，这就要求它的数据必须有序，而索引可以不连续
 -- order by将数据按照第一个字段排序，当第一个字段相同时按照第二个字段排序
 -- 索引必须建立在这两个有序字段上，因此order by最关键
 -- order by(a,b,c) 可以设置索引（primary key）的字段为a 或 ab 或 abc
   order by (uid,sku_id)

插入数据

insert into  t_order_mt
values(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00')
(102,'sku_002',12000.00,'2020-06-01 13:00:00')
(102,'sku_002',600.00,'2020-06-02 12:00:00')

MergeTree其实还有很多参数(绝大多数用默认值即可)，但是三个参数是更加重要的，也涉及了关于MergeTree的很多概念。

将插入数据代码重复执行三次，可以发现以下情况：

上图很好的说明了：临时分区，异步合并。当插入第一份数据时，其会自动进入主分区，插入第二份数据时，会将数据放到临时分区（独立的目录、文件）中，当触发某总条件会自动产生合并，也可以通过一行命令去强行合并，optimize table XXX [final]，但实际环境中，不会去强制合并，执行optimize table t_order_mt final;，合并后的结果如下：

（1）partition by 分区 （可选项）

**作用：**主要是降低扫描的范围，优化查询速度。

如果不填： 只会使用一个分区。

分区目录： MergeTree 是以列文件+索引文件 + 表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中。

**并行：**分区后，面对涉及跨分区的查询统计，clickhouse会以分区为单位并行处理。

数据写入与分区合并：

任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻（大概10-15分钟后），clickhouse会自动执行合并操作（等不及也可以手动通过optimize执行），把临时分区的数据，合并到已有分区中。

optimize table xxxx [final]

手动触发合并，除了合并分区还有很多别的事件会触发。

加入final选项，保证即使数据已经合并完成，也会强行合并（主要是可以保证触发其他事件）。否则，如果数据已经合并完成，则不会合并，也不会触发其他事件。

（2）primary key主键(可选)

clickhouse中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同primary key的数据的。

主键的设定主要依据是查询语句中的where 条件。

根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity，避免了全表扫描。

index granularity： 直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。clickhouse中的MergeTree默认是8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。

如果想进行精确查询，可以将index granularity调小，代价就是，系数索引会更多，占用内存空间会更大。

稀疏索引：

稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。

（3）order by （必选）

order by 设定了分区内的数据按照哪些字段顺序进行有序保存。

order by是MergeTree中唯一一个必填项，甚至比primary key 还重要，因为当用户不设置主键的情况，很多处理会依照order by的字段进行处理（比如后面会讲的去重和汇总）。

要求：主键必须是order by字段的前缀字段。

比如order by 字段是 (uid,sku_id) 那么主键必须是uid 或者(uid,sku_id)

（4）跳数索引

目前在clickhouse的官网上跳数索引的功能是被标注为实验性的。

所以使用二级索引前需要增加设置·(21.x版本变为正式版本，不用执行如下语句)

set allow_experimental_data_skipping_indices=1;

 create table t_order_mt2(
    uid UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time  Datetime,
	INDEX a total_amount TYPE minmax GRANULARITY 5
 ) engine =MergeTree
 partition by toYYYYMMDD(create_time)
   primary key (uid)
   order by (uid,sku_id)

其中GRANULARITY N 是设定二级索引对于一级索引粒度的粒度。 minmax GRANULARITY 5的含义就是为 每5 * 8192 行数据计算一对该列的最大最小值，当扫描行扫描到该区间时，会对比最大最小值，如果不在该范围，就可以直接跳过该区域的扫描。

如假设有一张按照地区排序的表，现想将金额大于6000的用户查询出来，uid，sku_id索引对应这个需求就没有什么用处，就只能顺序扫描。跳数索引就适用于这种情况，它可以统计几个颗粒度之间金额的最大值和最小值。

如上图，只会考虑第二个跳数索引。

（5）数据TTL

TTL即Time To Live，MergeTree提供了可以管理数据或者列的生命周期的功能。

必须靠触发合并操作才能实现数据的时效。

插入数据

insert into  t_order_mt
values(106,'sku_001',1000.00,'2020-06-12 22:52:30') ,
(107,'sku_002',2000.00,'2020-06-12 22:52:30'),
(110,'sku_003',600.00,'2021-06-13 12:00:00')

表级TTL

针对整张表

​ 下面的这条语句是数据会在create_time 之后10秒丢失（当前时间 - 创建时间）

alter table t_order_mt MODIFY TTL create_time + INTERVAL 10 SECOND; 

涉及判断的字段必须是Date或者Datetime类型，推荐使用分区的日期字段。

能够使用的时间周期：

- SECOND - MINUTE - HOUR - DAY - WEEK - MONTH - QUARTER - YEAR

5 ReplacingMergeTree

ReplacingMergeTree是MergeTree的一个变种，它存储特性完全继承MergeTree，只是多了一个去重的功能。

尽管MergeTree可以设置主键，但是primary key其实没有唯一约束的功能。如果想处理掉重复的数据，可以借助这个ReplacingMergeTree。通过ReplacingMergeTree可以实现幂等性：同一份数据重复进入，存储不重复。

去重时机：数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。

去重范围：如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。

所以ReplacingMergeTree能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

 create table t_order_rmt(
    uid UInt32,
    sku_id String,
    total_amount Decimal(16,2) ,
    create_time  Datetime 
 ) engine =ReplacingMergeTree(create_time)
 partition by toYYYYMMDD(create_time)
   primary key (uid)
   order by (uid,sku_id)

ReplacingMergeTree() 填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认保留最后一条。

insert into  t_order_rmt
values(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00')
(102,'sku_002',12000.00,'2020-06-01 13:00:00')
(102,'sku_002',600.00,'2020-06-02 12:00:00')

SELECT * FROM t_order_rmt

重复插入几次，然后执行强制合并分区操作

OPTIMIZE TABLE t_order_rmt FINAL

SELECT * FROM t_order_rmt

通过测试得到结论：

• 实际上是使用order by 字段作为唯一键。
• 去重不能跨分区。
• 只有合并分区才会进行去重。
• 认定重复的数据保留，版本字段值最大的。
• 如果版本字段相同则保留最后一条。

6 SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。

Clickhouse 为了这种场景，提供了一种能够“预聚合”的引擎，SummingMergeTree。

表定义

create table t_order_smt(
    uid UInt32,
    sku_id String,
    total_amount Decimal(16,2) ,
    create_time  Datetime 
 ) engine =SummingMergeTree(total_amount)
 partition by toYYYYMMDD(create_time)
   primary key (uid)
   order by (uid,sku_id)

插入数据

insert into  t_order_smt
values(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00')
(102,'sku_002',12000.00,'2020-06-01 13:00:00')
(102,'sku_002',600.00,'2020-06-02 12:00:00')

optimize table t_order_smt final;

通过结果可以得到以下结论：

• 以SummingMergeTree（）中指定的列作为汇总数据列。可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列。
• 以order by 的列为准，作为维度列。
• 其他的列保留第一行。
• 不在一个分区的数据不会被聚合。

设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳。

不能直接使用 select total_amount from province_name=’’ and create_date=’xxx’ 来得到汇总值，因为可能会包含一些还没来得及聚合的临时明细。

 select sum(total_amount) from province_name=’’ and create_date=’xxx’

即使使用SummingMergeTree 引擎也要手工进行sum， 聚合的效率肯定远远高于没有预聚合数据库或者其他引擎。

SummingMergeTree是非幂等的。