ClickHouse整理

最新推荐文章于 2024-08-18 11:29:58 发布

一坛剩菜

最新推荐文章于 2024-08-18 11:29:58 发布

阅读量2k

点赞数

文章标签：数据仓库

本文链接：https://blog.csdn.net/weixin_50405727/article/details/123282425

版权

1. ClickHouse 概述

官方文档： ClickHouse官网

ClickHouse 是俄罗斯的 Yandex 于 2016 年开源的列式存储数据库（DBMS），使用 C++ 语言编写，主要用于在线分析处理查询（OLAP），能够使用 SQL 查询实时生成分析数据报告。

具有如下特点：

列式存储
- 对于列的聚合，计数，求和等统计操作原因优于行式存储。
- 由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重，从而降低磁盘和内存存储空间。
DBMS功能

几乎覆盖了标准 SQL 的大部分语法，包括 DDL 和 DML 以及配套的各种函数，用户管理及权限管理，数据的备份与恢复。
多样化引擎

ClickHouse 和 MySQL 类似，把表级的存储引擎插件化，根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口和其他四大类 20 多种引擎。
高吞吐写入能力

ClickHouse 采用类似 LSM Tree (日志结构合并树)的结构，即先追加后合并数据 Compaction。通过类似 LSM tree 的结构，ClickHouse 在数据导入时全部是顺序 append 写，写入后数据段不可更改，在后台合并时也是多个段合并排序后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力，官方数据单台 ClickHouse 服务的写入性能达到 50~200 M/s。
当节点数据分区与线程级并行

ClickHouse 将数据划分为多个 partition，每个 parition 可以分配一个CPU进行并行处理。在这种设计下，单条查询就能利用整机所有 CPU。极致的并行处理能力，极大的降低了查询延时。

但是有一个弊端就是对于单条查询使用多 cpu，就不利于同时并发多条查询。所以对于高 qps 的查询业务，ClickHouse 并不是强项。

2. ClickHouse 入门

2.1. 数据类型

2.1.1. 整形

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

Int8 - [-128 : 127]

Int16 - [-32768 : 32767]

Int32 - [-2147483648 : 2147483647]

Int64 - [-9223372036854775808 : 9223372036854775807]
无符号整型范围（0~2n-1）：

UInt8 - [0 : 255]

UInt16 - [0 : 65535]

UInt32 - [0 : 4294967295]

UInt64 - [0 : 18446744073709551615]

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

2.1.2. 浮点型

Float32 - float

Float64 – double

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

2.1.3. Decimal 型

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

Decimal32(s)，相当于 Decimal(9-s,s)，有效位数为 1~9
Decimal64(s)，相当于 Decimal(18-s,s)，有效位数为 1~18
Decimal128(s)，相当于 Decimal(38-s,s)，有效位数为 1~38

注意：s 标识小数位

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

2.1.4. 字符串

String

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

固定长度 N 的字符串，N 必须是严格的正自然数。长度小于 N ，在字符串末尾添加空字节来达到 N 字节长度。长度大于 N 的字符串时候，报错。

使用场景: 名称、文字描述、字符型编码。

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

2.1.5. 枚举类型

包括 Enum8 和 Enum16 类型。Enum 保存 ‘string’= integer 的对应关系。
Enum8 用 ‘String’= Int8 对描述。

Enum16 用 ‘String’= Int16 对描述。

使用场景：对一些状态、类型的字段算是一种空间优化，也算是一种数据约束。但是实际使用中往往因为一些数据内容的变化增加一定的维护成本，甚至是数据丢失问题。所以谨慎使用。

2.1.6. 时间类型

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

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

2.2. SQL操作

ClickHouse 基本上与标准 SQL 差别不大

支持子查询
支持 CTE(Common Table Expression 公用表表达式 with 子句)
支持各种 JOIN，但是 JOIN 操作无法使用缓存，所以即使是两次相同的 JOIN 语句，ClickHouse 也会视为两条新 SQL
窗口函数(官方正在测试中…)
不支持自定义函数
GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。

2.2.1. 特殊函数

with rollup：从右至左去掉维度进行小计
```
hadoop102 :) select id,sku_id,sum(total_amount) from t_order_mt group by id,sku_id with rollup;
```
1. 第一次聚合所有维度(id, sku_id)
2. 第二次聚合排除最右侧维度(id)
3. 第三次聚合再排除最右侧维度()
with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计
```
hadoop102 :) select id,sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube;
```
即将 group by 所有维度组合情况，分别聚合并 Union 在一起(2的N次方种)

在这里插入图片描述

with totals: 只计算合计

hadoop102 :) select id,sku_id,sum(total_amount) from t_order_mt group by 
id,sku_id with totals;

在这里插入图片描述

多个维度组合 Union 的语法，同为 OLAP 数据分析引擎的 HIVE 也支持。相关语法有：

with rollup;

with cube;

Grouping Set( (a,b), a, b, ( ) ); -> group by a,b union group by a union group by b union * (可以自己选)

2.2.2. 修改表结构

同 MySQL 的修改字段基本一致

新增字段

alter table tableName add column newcolname String after col1;

修改字段类型

alter table tableName modify column newcolname String;

删除字段

alter table tableName drop column newcolname;

修改表的操作，在OLAP数据分析引擎中，并不常用，不过 HIVE 和 ClickHouse 都支持，新增字段最好追加

2.2.3. 导出数据

语法同 HIVE

clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames> /opt/module/data/rs1.csv1

更多导出格式，见官网格式文档：https://clickhouse.tech/docs/en/interfaces/formats/

3. 表引擎

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

数据的存储方式和位置，写到哪里以及从哪里读取数据。
支持哪些查询以及如何支持。
并发数据访问。
索引的使用（如果存在）。
是否可以执行多线程请求。

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

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

3.1. TinyLog

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

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

3.2. Memory

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

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

3.3. MergeTree

ClickHouse 中最强大的表引擎当属 MergeTree（合并树）引擎及该系列中的其他引擎，支持索引和分区，地位可以相当于 innodb 之于 Mysql。而且基于MergeTree，还衍生除了很多小弟，也是非常有特色的引擎。

建表语句举例：

create table t_order_mt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time Datetime
) engine =MergeTree
	partition by toYYYYMMDD(create_time)	# 可选
	primary key (id)												# 可选
	order by (id,sku_id);									 # 必填

3.3.1. partition by 分区(可选)

partition by 就是给数据划分分区，需要指定分区字段，如果不填则默认一个分区。不同分区数据存储在不同的分区目录下，分区优点如下：

分区的目的主要是降低扫描的范围，避免全表扫描，优化查询速度。
分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理。

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

# 手动合并分区
hadoop102 :) optimize table t_order_mt final;

3.3.2. primary key 主键(可选)

ClickHouse 的主键，它只提供了数据的一级索引，不提供唯一约束。 可能存在相同 primary key 的数据。

主键的设定是由于 MergeTree 特殊的索引策略, 即 稀疏索引，稀疏索引区别于普通索引的地方为，普通索引会将所有数据的索引都保存在索引文件，查询语句击中普通索引时，去遍历完整的索引文件。而稀疏索引在保存索引的时候。就不是保存所有数据的索引，而是根据设置的index granularity(索引粒度)，来生成少数数据的索引，在查询时，如果命中索引，就通过二分查找法定位查询数据索引的范围，最后再去数据文件中查询这个范围的数据。

稀疏索引的优劣点：

优点
1. 索引文件数据量小很多，使用二分查找法定位索引范围，索引吞吐量及检索速度性能大大增强
2. 官方提供的索引粒度为 8192，扫描表数据性能可观。
缺点
1. 稀疏索引需要表数据使用索引列来排序，制约了 order by 条件(索引字段必须是“前缀字段”)，这也就限制了稀疏索引个数。
2. 检索完索引之后，需要额外查询表数据索引粒度范围内数据，范围内数据量越大，速度越低

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

在这里插入图片描述

3.3.3. order by 排序(必填)

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

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

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

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

3.3.4. 表/列 TTL

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

表级别或者说行级别TTL，达到过期时间整条数据会失效：

-- create_time 之后10秒 这条数据会失效
alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

列级别的 TTL，达到过期时间这个列的数据会设为默认值：

total_amount Decimal(16,2) TTL create_time+interval 10 SECOND;

3.3.4. ReplacingMergeTree

ReplacingMergeTree 是 MergeTree 的一个变种，它存储特性完全继承 MergeTree，只是多了一个去重的功能。尽管 MergeTree 可以设置主键，但是 primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个 ReplacingMergeTree。

建表语句举例：

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

特点如下：

使用 order by (primary key可能为空) 字段作为唯一键。
去重不能跨分区，分区内去重，跨分区可能出现重复。
只有同一批插入的数据在合并分区时才会进行去重，可能存在未被去重的数据。
认定重复的数据保留，取版本字段(建表时指定的字段create_time)值最大的。
如果版本字段相同则按插入顺序保留最后一笔。

实际 DWS层数据使用的也是 ReplacingMergeTree 来存储数据，之所以选用 ReplacingMergeTree 引擎主要是靠它来保证数据表的幂等性。

3.3.5. SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。ClickHouse 为了这种场景，提供了一种能够预聚合的引擎 SummingMergeTree。

建表语句举例：

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

SummingMergeTree 以 order by 字段组合为聚合维度，可以指定多个汇总数据列，如果不指定所有非维度列都是汇总数据列，并且汇总数据列必须是数值类型，合并时计算出所有汇总数据列的累加值。

特点如下：

可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列
以 order by 的列为准，作为维度列
其他的列按插入顺序保留第一行
不在一个分区的数据不会被聚合
只有在同一批次插入(新版本)或分片合并时才会进行聚合
虽然会自动聚合，但是还是不能直接查询字段作为聚合值，因为有一些数据还没来得及合并。正确做法和常规做法一样使用 sum( )，触发计算。由于之前的数据都已经计算过了，还是会有性能提升，但是由于 ClickHouse 本身是列式存储，所以提升不是很明显。
没有 ReplacingMergeTree 的区内幂等性支持，需要自己维护数据一致性。