ClickHouse

时间：Nov. 2, 2021 分类：Linux服务

目录：

ClickHouse介绍

特性

官方文档https://clickhouse.com/docs/zh/

ClickHouse是一个MPP架构的列式存储数据库

  • 完备的DBMS管理功能
  • 列式存储（避免无效的搜索）和数据压缩（列字段数据重复概率更高，压缩效率越高，压缩比8:1）
  • 向量化执行引擎（利用的CPU的SIMD指令，目前使用了SSE4.2指令集）
  • 关系模型和SQL查询（支持SQL的查询语言）
  • 多样化表引擎
  • 多线程与分布式（纵向分区利用多线程，横向分片利用多线程）
  • 多主架构
  • 在线查询
  • 数据分片与分布式查询（本地表和分布式表）

SIMD

SIMD的全称是Single Instruction Multiple Data，即用单条指令操作多条数据。现代计算机系统概念中，它是通过数据并行以提高性能的一种实现方式（其他的还有指令级并行和线程级并行），它的原理是在CPU寄存器层面实现数据的并行操作。

适用场景：大宽表的查询 不适用场景：事务、按行删除数据、kv查询

架构

基本映射单元为Column和Field，根据Column的类型不同会有不同的操作和处理逻辑

内部操作的数据流为block，由Colume、DataType和名称字符串组成

通过表引擎实现Table

函数作用在Column上，通过向量化执行应用于Column上的每个Field

为什么快

设计自下而上，最初的目的就是希望以最快的速度完成GROUP BY

  • 新的算法好用就直接替换
  • 针对特殊场景特殊优化

ClickHouse安装和部署

安装略过，参考官方文档https://clickhouse.com/docs/zh/getting-started/install/

$ grep -q sse4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"

如果不支持SSE指令集，不能直接使用预编译安装包，而是需要源码进行编译安装

命令行

  • -h/--host，默认localhost
  • --port，默认9000
  • -u/--user，默认default
  • --password
  • -d/--database
  • -q/--query，非交互查询使用
  • -m/--multiquery，非交互批量查询使用，多条语句逗号分隔
  • -t/--time，每条SQL的执行时间

示例

$ cat /chbase/test_fetch.tsv | clickhouse-client --query "INSERT INTO test_fetch FORMAT TSV"
$ clickhouse-client --query="SELECT * FROM test_fetch" > /chbase/test_fetch.tsv
$ clickhouse-client -h 10.37.129.10 --multiquery --query="SELECT 1;SELECT 2;SELECT3;"

clickhouse-local可以独立运行大部分SQL查询而不依赖ClickHouse

clickhouse-benchmark用于运行基准测试

ClickHouse数据定义

数据类型

基础类型只有数值、字符串和时间三种类型，没有boolean类型

  • 数值有Int、Float和Decimal
  • 字符串有String（不定长）、FixedString（定长不足用null填充）和UUID
  • 时间类型有DateTime、DateTime64和Date

复合类型

  • Array
  • Tuple 元组
  • Enum 枚举
  • Nested 嵌套

特殊类型

  • Nullable
  • IPV4和IPV6

数据库

创建数据库

CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]

数据库引擎支持5种

  • Ordinary，默认引擎，可以使用任意类型的表引擎
  • Dictionary，字典引擎，
  • Memory，内存引擎，重启后销毁
  • Lazy，日志引擎，只能使用Log系列的表引擎
  • MySQL，自动拉取远端MySQL数据
CREATE DATABASE DB_TEST

创建数据库之后会在磁盘上创建对应的目录，matedata目录也会有用于恢复数据库的SQL

DROP DATABASE [IF EXISTS] db_name

数据表

创建数据表

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name ( 
    name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr], 
    name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
) ENGINE = engine

复制表结构

CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name AS [db_name2.] table_name2 [ENGINE = engine]

支持临时表，通过关键字TEMPORARY，但是同名情况下临时表优先级高，优先查临时表

CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name ( name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr], name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr], )

分区表

只有MergeTree家族系列的表引擎才支持数据分区

分区可以在查询的时候跳过不必要的目录，提升查询性能。在删除的时候也可以根据分区进行删除

示例使用partition_v1进行分区

CREATE TABLE partition_v1 ( ID String, URL String, EventTime Date ) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventTime) ORDER BY ID

插入数据

INSERT INTO partition_v1 VALUES ('A000','www.nauu.com', '2019-05-01'), ('A001','www.brunce.com', '2019-06-02')

通过system.parts查看数据表的分区状态

SELECT table,partition,path from system.parts WHERE table = 'partition_v1' 
┌─table─────┬─partition─┬─path─────────────────────────┐ 
│ partition_v1 │ 201905 │ /chbase/data/default/partition_v1/201905_1_1_0/│ 
│ partition_v1 │ 201906 │ /chbase/data/default/partition_v1/201906_2_2_0/│ 
└─────────┴────────┴─────────────────────────────┘

如果查询语句是

SELECT * FROM partition_v1 WHERE EventTime ='2019-05-01'

只需要查询5月的分区表，而不会查询6月的

视图

创建视图

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ...
  • 物化视图支持表引擎
  • 物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新

数据表基本操作

只有MergeTree、Merge和Distributed这三类表引擎支持ALTER查询

追加字段

ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTER name_after]

示例

# 末尾新增字段
ALTER TABLE testcol_v1 ADD COLUMN OS String DEFAULT 'mac'
# 指定字段后新增字段
ALTER TABLE testcol_v1 ADD COLUMN IP String AFTER ID

修改数据类型

ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [type] [default_expr]

示例

ALTER TABLE testcol_v1 MODIFY COLUMN IP IPv4

移动数据表，可以实现重命名，但是只能在单节点范围操作

RENAME TABLE [db_name11.]tb_name11 TO [db_name12.]tb_name12, [db_name21.]tb_name21 TO [db_name22.]tb_name22, ...

清空数据表

TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name

数据分区

查询分区信息

SELECT partition_id,name,table,database FROM system.parts WHERE table = 'partition_v2' 
┌─partition_id─┬─name───────┬─table─────┬─database┐ 
│ 201905 │ 201905_1_1_0_6 │ partition_v2 │ default │ 
│ 201910 │ 201910_3_3_0_6 │ partition_v2 │ default │ 
│ 201911 │ 201911_4_4_0_6 │ partition_v2 │ default │ 
│ 201912 │ 201912_5_5_0_6 │ partition_v2 │ default │ 
└──────────┴──────────┴─────────┴──────┘

删除指定分区

ALTER TABLE tb_name DROP PARTITION partition_expr

复制分区数据，从一个表到另一个表但是需要两个表分区键相同，表结构相同

ALTER TABLE B REPLACE PARTITION partition_expr FROM A

重置分区数据，列字段都为空

ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr

卸载和装载分区，备份和还原也可以通过这个功能实现

# 卸载，文件会放到detached目录
ALTER TABLE tb_name DETACH PARTITION partition_expr
# 装载
ALTER TABLE tb_name ATTACH PARTITION partition_expr

分布式DDL

ClickHouse支持集群模式，一个集群有多个节点，一些DDL语句，例如CREATE、ALTER、DROP、RENMAE和TRUNCATE等，都需要支持分布式执行

DDL转换为分布式DDL需要加上上ON CLUSTER cluster_name声明

示例 

CREATE TABLE partition_v3 ON CLUSTER ch_cluster( ID String, URL String, EventTime Date ) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventTime) ORDER BY ID

数据写入

支持多种规范

# VALUE格式
INSERT INTO [db.]table [(c1, c2, c3…)] VALUES (v11, v12, v13…), (v21, v22, v23…), ...
# 示例
INSERT INTO partition_v2 VALUES ('A0011','www.nauu.com', '2019-10-01'), ('A0012','www.nauu.com', '2019-11-20'),('A0013','www.nauu.com', '2019-12-20')
INSERT INTO partition_v2 VALUES ('A0014',toString(1+2), now())
# 使用格式
INSERT INTO [db.]table [(c1, c2, c3…)] FORMAT format_name data_set
# 示例
INSERT INTO partition_v2 FORMAT CSV \ 'A0017','www.nauu.com', '2019-10-01' \ 'A0018','www.nauu.com', '2019-10-01'
# SELECT子句
INSERT INTO [db.]table [(c1, c2, c3…)] SELECT ...
# 示例
INSERT INTO partition_v2 SELECT * FROM partition_v1

INSERT的数据在单个数据块写入的时候具有原子性，默认情况下每个数据块最多可以写入1048576行数据（由max_insert_block_size参数控制），小于这个值就是原子操作，要么全成功要么全失败，但是只针对在server端处理数据的，例如JDBC和HTTP，在命令行和INSERT SELECT写入的时候不生效

数据修改和删除

这些操作提交就会执行，没有事务和回滚

ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr
# 示例
ALTER TABLE partition_v2 DELETE WHERE ID = 'A003'

操作会创建原来分区目录的同名目录，还有一个mutation_*.txt的文件记录着操作逻辑，在到MergeTree引擎的下一次合并周期，会触发合并动作，非激活目录被删除

修改操作

ALTER TABLE [db_name.]table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr
# 示例
ALTER TABLE partition_v2 UPDATE URL = 'www.wayne.com',OS = 'mac' WHERE ID IN (SELECT ID FROM partition_v2 WHERE EventTime = '2019-06-01')

ClickHouse数据字典

数据字典是ClickHouse的一种存储媒介，键值和属性映射的形式定义数据

字典数据会主动或被动加载到内存，支持动态更新，适合保存常量或经常使用的维度表数据

内置字典只有Yandex.Metrica字典，还因为版权的问题没有开发

外部扩展是以插件的形式注册到ClickHouse中

测试数据

企业组织数据，有id、code和name三个字段

1,"a0001","研发部" 
2,"a0002","产品部" 
3,"a0003","数据部" 
4,"a0004","测试部" 
5,"a0005","运维部" 
6,"a0006","规划部" 
7,"a0007","市场部"

销售数据，有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、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"

扩展字典

在config.xml中dictionaries_config配置项指定

默认为*_dictionary.xml会加载/etc/clickhouse-server/目录下所有_dictionary.xml结尾的配置文件，并且动态感知配置文件的变化

单个字典配置文件可以定义多个字典，示例

<?xml version="1.0"?> 
<dictionaries> 
    <dictionary> 
        <name>dict_name</name> 
        <structure> 
            <!—字典的数据结构 --> 
        </structure> 
        <layout> 
            <!—在内存中的数据格式类型 --> 
        </layout> 
        <source> 
            <!—数据源配置 --> 
        </source> 
        <lifetime>
            <!—字典的自动更新频率 --> 
        </lifetime>
    </dictionary> 
</dictionaries>
  • 数据结构
  • 数据格式
  • 数据源：本地文件、执行文件和远程文件，数据库MySQL、ClickHouse、MongoDB等

数据查询

SELECT dictGet('test_flat_dict','name',toUInt64(1))

也可以转换为字典表

这个数据字典我没看明白到底是一个什么样的功能

ClickHouse MergeTree原理解析

MergeTree创建方式

创建MergeTree数据表

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name ( 
    name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr], 
    name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    省略... 
) ENGINE = MergeTree() 
[PARTITION BY expr] 
[ORDER BY expr] 
[PRIMARY KEY expr] 
[SAMPLE BY expr] 
[SETTINGS name=value, 省略...]

其他的参数

  • PARTITION BY选填分区键，可以是单个字段，也可以是元组的形式的多个字段，默认是all
  • ORDER BY必填排序键，默认是主键，可以是单个列字段，也可以是多个列字段
  • PRIMARY KEY选填主键，可以根据主键生成一级索引，加速表查询
  • SAMPLE BY选填抽样表达式，用于声明数据以何种标准采样，如果配置需要主键也声明相同表达式
  • SETTINGS: index_granularity索引粒度，每间隔多少行生成一条索引
  • SETTINGS: index_granularity_bytes索引间隔，默认为10M(10×1024×1024)，设置为0表示不启动自适应功能
  • SETTINGS: enable_mixed_granularity_parts是否开启自适应索引间隔的功能，默认开启
  • SETTINGS：merge_with_ttl_timeout数据TTL
  • SETTINGS：storage_policy存储策略

MergeTree存储结构

数据会按照分区存储到磁盘上

  • partition: 分区目录下各类数据文件都是以分区目录的形式被组织和存放起来的
  • checksums.txt: 校验文件，使用二进制格式存储，保存了各类文件大小和hash值
  • columns.txt: 列信息文件，使用明文格式存储。保存了数据分区下的列字段信息
  • count.txt: 计数文件，使用明文格式存储，记录当前数据分区的总行数
  • primary.idx: 一级索引文件，使用二进制格式存储，用于存放稀疏索引，一张MergeTree只能声明一次
  • [Column].bin: 数据文件，使用压缩格式存储，默认为LZ4压缩格式，用于存储一列数据
  • [Column].mrk: 列字段标记文件，使用二进制格式存储，保存.bin文件的偏移量，首先通过稀疏索引（primary.idx）找到对应数据的偏移量信息（.mrk），再通过偏移量直接从.bin文件中读取数据
  • [Column].mrk2：列字段标记文件，如果使用了自适应大小的索引间隔，则标记文件会以.mrk2命名。它的工作原理和作用与.mrk标记文件相同
  • partition.dat与minmax_[Column].idx：如果使用了分区键，则会额外生成partition.dat与minmax索引文件，它们均使用二进制格式存储
  • skp_idx_[Column].idx与skp_idx_[Column].mrk：如果在建表语句中声明了二级索引，则会额外生成相应的二级索引与标记文件，它们同样也使用二进制存储

MergeTree数据分区

分区根据分区ID决定，有四种规则

  • 不指定分区键，分区ID为all，都写入all分区
  • 使用整型，且不能转为日期类型，直接按照整型的字符形式作为分区ID
  • 使用日期类型，按照日期格式化形式作为分区ID
  • 使用其他类型，分区键不属于以上，通过Hash作为分区ID

示例日期2021-10-17和2021-11-17

  • PARTITION BY EventTime，分区为20211017和20211117
  • PARTITION BY toYYYYMM(EventTime)，分区为202110和202111

如果是多个ID组层的元组

  • PARTITION BY (length(Code),EventTime)，分区为2-20211017 2-20211117

分区目录的命名规则为PartitionID_MinBlockNum_MaxBlockNum_Leve

  • MinBlockNum 最小数据块编号
  • MaxBlockNum 最大数据块编号
  • Level 合并层级，分区的合并次数

分区目录合并过程

  1. 分区目录是在写入的过程中创建的，每一批写入都会创建分区目录，即使这些批次都属于相同分区也会创建不同的分区目录
  2. 之后的某个时刻会通过后台任务将相同分区合并为一个新目录，旧目录不会删除而被标记为未激活
  3. 之后的某个时刻会删除旧目录
  • MinBlockNum：取同一分区内所有目录中最小的MinBlockNum值
  • MaxBlockNum：取同一分区内所有目录中最大的MaxBlockNum值
  • Level：取同一分区内最大Level值并加1

一级索引

主键使用PRIMARY KEY定义，定义后会根据index_granularity间隔，为数据表生成一级索引并保存到primary.idx文件内

相比使用PRIMARY KEY定义，更为常见的简化形式是通过ORDER BY指代主键，PRIMARY KEY与ORDER BY定义相同，所以索引（primary.idx）和数据（.bin）会按照完全相同的规则排序。对于不同的可以使用SummingMergeTree引擎

在稠密索引中每一行索引标记都会对应到一行具体的数据记录。而在稀疏索引中，每一行索引标记对应的是一段数据，而不是一行

稀疏索引的优势是显而易见的，它仅需使用少量的索引标记就能够记录大量数据的区间位置信息，且数据量越大优势越为明显。以默认的索引粒度（8192）为例，MergeTree只需要12208行索引标记就能为1亿行数据记录提供索引

由于稀疏索引占用空间小，所以primary.idx内的索引数据常驻内存，取用速度自然极快

二级索引

略

MergeTree数据存储

按列独立存储的设计优势显而易见

  1. 可以更好地进行数据压缩（相同类型的数据放在一起，对压缩更加友好）
  2. 能够最小化数据扫描的范围

数据会事先依照ORDER BY的声明排序，经过压缩以压缩数据块的形式被组织并写入.bin文件中的，默认使用LZ4算法，还支持ZSTD、Multiple和Delta几种算法

bin文件中有很多的压缩数据块

压缩数据块示意图

每个压缩数据块的体积，按照其压缩前的数据字节大小，都被严格控制在64KB～1MB，其上下限分别由min_compress_block_size（默认65536）与max_compress_block_size（默认1048576）参数指定。而一个 压缩数据块最终的大小，则和一个间隔（index_granularity）内数据的实际大小相关

规则

  1. 单个批次数据size<64KB ：如果单个批次数据小于64KB，则继续获取下一批数据，直至累积到size>=64KB时，生成下一个压缩数据块
  2. 单个批次数据64KB<=size<=1MB ：如果单个批次数据大小恰好在64KB与1MB之间，则直接生成下一个压缩数据块
  3. 单个批次数据size>1MB ：如果单个批次数据直接超过1MB，则首先按照1MB大小截断并生成下一个压缩数据块。剩余数据继续依照上述规则执行。此时，会出现一个批次数据生成多个压缩数据块的情况

压缩数据块的目的

  1. 数据被压缩后能够有效减少数据大小，降低存储空间并加速数据传输效率，但数据的压缩和解压动作，其本身也会带来额外的性能损耗。所以需要控制被压缩数据的大小，以求在性能损耗和压缩率之间寻求一种平衡
  2. 读取某一列数据时，首先需要将压缩数据加载到内存并解压。通过压缩数据块，可以在不读取整个.bin文件的情况下将读取粒度降低到压缩数据块级别压缩数据加载到内存并解压，

MergeTree数据标记

数据标记衔接一级索引和数据

标记数据与一级索引数据不同，它并不能常驻内存，而是使用LRU（最近最少使用）缓存策略加快其取用速度

定位压缩数据块并读取数据流程

  1. 读取压缩数据块，根据需要寻找对应的数据块
  2. 读取数据，以以index_granularity的粒度加载特定的一小段

MergeTree分区、索引、标记和压缩数据的协同

写入过程

每一批写入数据都会生成新的分区目录，在后续的时刻会对分区目录进行合并，然后生成一级索引等操作

读取过程

查询是一个不断缩小数据范围，先是分区索引，然后是一级索引，再根据数据标记进行解压。如果没有where条件就会是一个所有分区的索引扫描

由于压缩数据块体积被控制在64KB到1MB，每个间隔的数据会创建数据标记

就会造成三种情况

多个数据标记对一个数据块

一个数据标记对一个数据块

一个数据标记对多个数据块

ClickHouse MergeTree系列表引擎

MergeTree有两个能力

  • 数据TTL
  • 存储策略

数据TTL

TTL需要依赖某个DataTime或Data类型字段

TTL time_col + INTERVAL 3 DAY

支持SECOND、MINUTE、HOUR、DAY、 WEEK、MONTH、QUARTER和YEAR

示例列级别的TTL

CREATE TABLE ttl_table_v1( id String, create_time DateTime, code String TTL create_time + INTERVAL 10 SECOND, type UInt8 TTL create_time + INTERVAL 10 SECOND )ENGINE = MergeTree PARTITION BY toYYYYMM(create_time) ORDER BY id

可以强制触发TTL清理

optimize TABLE ttl_table_v1 FINAL

示例表级别的TTL

CREATE TABLE ttl_table_v2( id String, create_time DateTime, code String TTL create_time + INTERVAL 1 MINUTE, type UInt8 )ENGINE = MergeTree PARTITION BY toYYYYMM(create_time) ORDER BY create_time TTL create_time + INTERVAL 1 DAY

也支持TTL的修改

ALTER TABLE ttl_table_v2 MODIFY TTL create_time + INTERVAL 3 DAY

强制触发分区合并

optimize TABLE table_name FINAL

运行机制为

  1. 在写入数据之后，分区目录生成ttl.txt
  2. 在合并的时候触发删除ttl过期数据
  3. 在删除分区的时候使用贪婪算法

ttl的合并频率由merge_with_ttl_timeout控制

多路径存储策略

有三类存储策略

  • 默认策略
  • JBOD策略，针对没有做raid的磁盘，对产生的新数据轮询写入每个磁盘
  • HOT/COLD策略，产生的新数据写入到HOT区域，当分区大小到达一定阈值，会移动的COLD区域

其他MergeTree引擎

  • ReplacingMergeTree: 针对然MergeTree的主键不是唯一约束，但是也是分区唯一
  • SummingMergeTree: 用于用户只关心汇总结果，不关心明细数据，可以在合并分区的时候按照预先定义聚合，但是依然提供实时聚合查询
  • AggregatingMergeTree: 将需要聚合的数据预先计算出来，并将结果保存起来。在后续进行聚合查询的时候，直接使用结果数据
  • CollapsingMergeTree: 种通过以增代删的思路，支持行级数据修改和删除的表引擎

ClickHouse其他常见类型表引擎

Everything is table，万物皆表是ClickHouse的设计思路

外部存储类型

  • HDFS：按照CSV格式读写数据
  • MySQL：与MySQL数据库中的数据表建立映射，并通过SQL向其发起远程查询，包括SELECT和INSERT
  • JDBC：不仅可以对接MySQL数据库，还能够与PostgreSQL、SQLite和H2数据库对接。但是，JDBC表引擎无法单独完成所有的工作，它需要依赖名为clickhouse-jdbc-bridge的查询代理服务
  • Kafka：从kafka读取数据，读取一次就没有了
  • File

内存类型

  • Memory
  • Set 数据首先会被写至内存，然后被同步到磁盘文件中，不支持select，只能在查询的in后使用
  • Join 为Join的时候使用的
  • Buffer

日志类型

特征有

  • 不支持索引、分区等高级特性
  • 不支持并发读写，当针对一张日志表写入数据时，针对这张表的查询会被阻塞，直至写入动作结束

类型有

  • TinyLog
  • StripeLog
  • Log

接口类型

自身并不存储任何数据，而是像黏合剂一样可以整合其他的数据表

  • Merge
  • Dictionary
  • Distributed

其他类型

  • Live View
  • NULL
  • URL

ClickHouse查询语句

ClickHouse对于SQL语句的解析是大小写敏感的

WITH

SELECT pow(pow(2, 2), 3)

可以写作

WITH pow(2, 2) AS a SELECT pow(a, 3)

主要用法

# 定义常量
WITH 10 AS start SELECT number FROM system.numbers WHERE number > start LIMIT 5
# 调用函数
WITH SUM(data_uncompressed_bytes) AS bytes SELECT database , formatReadableSize(bytes) AS format FROM system.columns GROUP BY database ORDER BY bytes DESC
# 定义子查询
WITH (SELECT SUM(data_uncompressed_bytes) FROM system.columns ) AS total_bytes SELECT database , (SUM(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage FROM system.columns GROUP BY database ORDER BY database_disk_usage DESC
# 在子查询中重复使用WITH
WITH (round(database_disk_usage) ) AS database_disk_usage_v1 SELECT database,database_disk_usage, database_disk_usage_v1 FROM (--嵌套 WITH ( SELECT SUM(data_uncompressed_bytes) FROM system.columns ) AS total_bytes SELECT database , (SUM(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage FROM system.columns GROUP BY database ORDER BY database_disk_usage DESC )

FROM

FROM支持3种形式

# 从数据表中取
SELECT WatchID FROM hits_v1 
# 从子查询中取数：
SELECT MAX_WatchID FROM (SELECT MAX(WatchID) AS MAX_WatchID FROM hits_v1)
# 从表函数中取数：
SELECT number FROM numbers(5)

SAMPLE

只能用在MergeTree引擎，并且在创建表的时候声明抽样表达式

# 建表
CREATE TABLE hits_v1 ( CounterID UInt64, EventDate DATE, UserID UInt64 ) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, intHash32(UserID)) SAMPLE BY intHash32(UserID)
  • 所声明的表达式必须同时包含在主键的声明内
  • Sample Key必须是Int类型，如若不是，ClickHouse在进行CREATE TABLE操作时也不会报错，但在数据查询时会得到如下类似异常Invalid sampling column type in storage parameters: Float32. Must be unsigned integer type.

抽样命令

SELECT CounterID FROM hits_v1 SAMPLE 0.1
SELECT CounterID FROM hits_v1 SAMPLE 1/10
# 统计结果估算
SELECT count() * 10 FROM hits_v1 SAMPLE 0.1

其他略

ClickHouse副本与分片

ClickHouse的集群配置非常灵活，可以将所有节点组成一个单一集群，也可以按照业务的诉求，把节点划分为多个小的集群。在每个小的集群区域之间，它们的节点、分区和副本数量可以各不相同

使用ReplicatedMergeTree的数据表就是副本

数据副本

副本的特点

  • 依赖zookeeper，在执行INSERT和ALTER查询的时候，查询副本的时候需要
  • 表级别的副本，副本是在表级别定义的，所以每张表的副本配置都可以按照它的实际需求进行个性化定义，包括副本的数量，以及副本在集群内的分布位置等
  • 多主架构，可以在任意一个副本上执行INSERT 和ALTER查询，它们的效果是相同的。这些操作会借助ZooKeeper的协同能力被分发至每个副本以本地形式执行
  • 原子性
  • 唯一性

zookeeper配置方式

/etc/clickhouse-server/config.d/metrika.xml

<?xml version="1.0"?> 
<yandex> 
    <zookeeper-servers> <!—ZooKeeper配置，名称自定义 --> 
        <node index="1"> <!—节点配置，可以配置多个地址--> 
            <host>hdp1.nauu.com</host> 
            <port>2181</port> 
        </node> 
    </zookeeper-servers> 
</yandex>

在全局配置中引用

<include_from>/etc/clickhouse-server/config.d/metrika.xml</include_from>
<zookeeper incl="zookeeper-servers" optional="false" />

在系统表中单独有zookeeper的表

SELECT * FROM system.zookeeper where path = '/' 
┌─name─────────┬─value─┬─czxid─┐ 
│ dolphinscheduler │ │ 2627 │ 
│ clickhouse │ │ 92875 │ 
└─────────────┴─────┴─────┘
SELECT name, value, czxid, mzxid FROM system.zookeeper where path = '/clickhouse' 
┌─name─────┬─value─┬─czxid─┬──mzxid─┐
│ tables │ │ 134107 │ 134107 │ 
│ task_queue │ │ 92876 │ 92876 │ 
└────────┴─────┴─────┴──────┘

副本定义方式

ENGINE = ReplicatedMergeTree('zk_path', 'replica_name')
  • zk_path用于在ZooKeeper中创建的数据表的路径，路径名称是自定义的，并没有固定规则，用户可以设置成自己希望的任何路径，参考/clickhouse/tables/{shard}/table_name
  • replica_name

一个副本一个分片

//1分片，1副本. zk_path相同，replica_name不同
ReplicatedMergeTree('/clickhouse/tables/01/test_1, 'ch5.nauu.com') 
ReplicatedMergeTree('/clickhouse/tables/01/test_1, 'ch6.nauu.com')

一个副本多个分片

//分片1 
//2分片，1副本. zk_path相同，其中{shard}=01, replica_name不同
ReplicatedMergeTree('/clickhouse/tables/01/test_1, 'ch5.nauu.com') 
ReplicatedMergeTree('/clickhouse/tables/01/test_1, 'ch6.nauu.com') 
//分片2 
//2分片，1副本. zk_path相同，其中{shard}=02, replica_name不同
ReplicatedMergeTree('/clickhouse/tables/02/test_1, 'ch7.nauu.com') ReplicatedMergeTree('/clickhouse/tables/02/test_1, 'ch8.nauu.com')

ReplicatedMergeTree原理解析

在创建表的过程中，会以zk_path为表创建一组监听节点

  • /metadata：保存元数据信息，包括主键、分区键、采样表达式等
  • /columns：保存列字段信息，包括列名称和数据类型
  • /replicas：保存副本名称，对应的replica_name
  • /leader_election：主副本的选举，主副本会主导MERGE 和MUTATION操作（ALTER DELETE和ALTER UPDATE），在主副本完成任务再借助zookeeper分发到其他副本
  • /blocks：记录Block数据块的Hash信息摘要，以及对应的partition_id
  • /block_numbers：按照分区的写入顺序，记录partition_id， 各副本本地MERGE按照这个顺序执行
  • /quorum：当至少有quorum数量的副本写入成功后，整个写操作才算成功。quorum的数量由insert_quorum参数控制，默认值为0
  • /log：常规操作日志节点（INSERT、MERGE和DROP PARTITION），它是整个工作机制中最为重要的一环，保存了副本需要执行的任务指令，使用的zookeeper的持久顺序节点
  • /mutations：为MUTATION操作日志节点，作用与log日志类似，当执行ALERT DELETE和ALERT UPDATE查询时，操作指令会被添加到这个节点
  • /replicas/{replica_name}/*每个副本各自的节点下的一组监听节点，用于指导副本在本地执行具体的任务指令
  • /queue：任务队列节点，用于执行具体的操作任务。当副本从/log或/mutations节点监听到操作指令时，会将执行任务添加至该节点下，并基于队列执行
  • /log_pointer：log日志指针节点，记录了最后一次执行的log日志下标信息，例如log_pointer：4对应了/log/log-0000000003（从0开始计数）。
  • /mutation_pointer：mutations日志指针节点，记录了最后一次执行的mutations日志名称

在/log和/mutations节点，添加子节点，所有副本示例都会监听到，实现分发操作

副本协同的核心流程主要有

  • INSERT
  • MERGE
  • MUTATION
  • ALTER

分别对应了数据写入、分区合并、数据修改和元数据修改。

INSERT和ALTER查询是分布式执行的。借助ZooKeeper的事件通知机制，多个副本之间会自动进行有效协同，但是它们不会使用ZooKeeper存储任何分区数据。

而其他查询并不支持分布式执行，包括SELECT、CREATE、DROP、RENAME和ATTACH。例如，为了创建多个副本，我们需要分别登录每个ClickHouse节点，在它们本地执行各自的CREATE语句

拟定一个演示场景，即使用ReplicatedMergeTree实现一张拥有1分片、1副本的数据表，并以此来贯穿整个讲解过程（对于大于1个副本的场景，流程以此类推），对ReplicatedMergeTree分别执行INSERT、MERGE、 MUTATION和ALTER操作

INSERT

创建第一个副本实例

CREATE TABLE replicated_sales_1( id String, price Float64, create_time DateTime ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/01/replicated_sales_1','ch5.nauu.com') PARTITION BY toYYYYMM(create_time) ORDER BY id

这个流程

  1. 在zk_path初始化所有节点
  2. 在/replicas/节点下注册自己的副本实例ch5.nauu.com
  3. 启动监听任务，监听/log日志节点
  4. 参与副本选举，选举出主副本，选举的方式是向/leader_election/插入子节点，第一个插入成功的副本就是主副本

响应同步需求的为节点的DataPartsExchange端口

MERGE

无论MERGE操作从哪个副本发起，其合并计划都会交由主副本来制定

MUTATION

和MERGE类似

ALTER

ALTET的流程会简单很多，其执行过程中并不会涉及/log日志的分发。整个流程从上至下按照时间顺序进行，其大致分成3个步骤

数据分片

数据分片需要结合Distributed表引擎一同使用

Distributed表引擎自身不存储任何数据，它能够作为分布式表的一层透明代理，在集群内部自动开展数据的写入、分发、查询、路由等工作

集群配置方式，用shard代表分片、用replica代表副本

# 1分片、0副本语义的配置
<shard> <!-- 分片 --> 
    <replica><!—副本 --> 
    </replica> 
</shard>
# 1分片、1副本语义的配置
<shard> <!-- 分片 --> 
    <replica><!—副本 --> 
    </replica> 
    <replica> 
    </replica> 
</shard>

使用node的方式就是，只包含分片，不包含副本

<yandex> <!--自定义配置名，与config.xml配置的incl属性对应即可 -->
    <clickhouse_remote_servers> 
        <shard_2><!--自定义集群名称--> 
            <node><!--定义ClickHouse节点--> 
                <host>ch5.nauu.com</host> 
                <port>9000</port> <!--选填参数
                <weight>1</weight> 
                <user></user> 
                <password></password> 
                <secure></secure> 
                <compression></compression> 
                --> 
            </node> 
            <node> 
                <host>ch6.nauu.com</host> 
                <port>9000</port> 
            </node> 
        </shard_2>
 </clickhouse_remote_servers>

包含了两个分片节点

  • shard_2表示自定义的集群名称，全局唯一，是后续引用集群配置的唯一标识。在一个配置文件内，可以定义任意组集群
  • node用于定义分片节点，不包含副本
  • host指定部署了ClickHouse节点的服务器地址
  • port指定ClickHouse服务的TCP端口
  • weight分片权重默认为1
  • user为ClickHouse用户，默认为default
  • password为ClickHouse的用户密码，默认为空字符串
  • secure为SSL连接的端口，默认为9440
  • compression表示是否开启数据压缩功能，默认为true
# 不包含副本的分配
<sharding_simple> <!-- 自定义集群名称 --> 
    <shard> <!-- 分片 --> 
        <replica> <!-- 副本 --> 
            <host>ch5.nauu.com</host> 
            <port>9000</port> 
        </replica> 
    </shard> 
    <shard> 
        <replica> 
            <host>ch6.nauu.com</host> 
            <port>9000</port> 
        </replica> 
    </shard> 
</sharding_simple>
# 1个分片 1个副本
<sharding_simple_1> 
    <shard> 
        <replica> 
            <host>ch5.nauu.com</host> 
            <port>9000</port> 
        </replica>
        <replica> 
            <host>ch6.nauu.com</host> 
            <port>9000</port> 
        </replica>
    </shard> 
</sharding_simple_1>
# 2个分片 1个副本
<sharding_ha> 
    <shard> 
        <replica> 
            <host>ch5.nauu.com</host> 
            <port>9000</port>
        </replica> <replica>
            <host>ch6.nauu.com</host> 
            <port>9000</port> 
        </replica> 
    </shard> 
    <shard> 
        <replica> 
            <host>ch7.nauu.com</host> 
            <port>9000</port> 
        </replica> 
        <replica> 
            <host>ch8.nauu.com</host> 
            <port>9000</port> 
        </replica> 
    </shard> 
</sharding_ha>

可以通过命令行查看

SELECT cluster, host_name FROM system.clusters 
┌─cluster────────┬─host_name──┐ 
│ shard_2 │ ch5.nauu.com │ 
│ shard_2 │ ch6.nauu.com │ 
│ sharding_simple │ ch5.nauu.com │ 
│ sharding_simple │ ch6.nauu.com │ 
│ sharding_simple_1 │ ch5.nauu.com │ 
│ sharding_simple_1 │ ch6.nauu.com │ 
└─────────────┴─────────┘

分布式DDL建表

CREATE TABLE test_1_local ON CLUSTER shard_2( id UInt64 
--这里可以使用任意其他表引擎，
)ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/test_1', '{replica}') 
ORDER BY id 
┌─host──────┬─port─┬─status─┬─error─┬─num_hosts_active─┐ 
│ ch6.nauu.com │ 9000 │ 0 │ │ 0 │ 
│ ch5.nauu.com │ 9000 │ 0 │ │ 0 │ 
└─────────┴────┴──────┴─────┴───────────┘

删除表

DROP TABLE test_1_local ON CLUSTER shard_2 
┌─host──────┬─port─┬─status─┬─error─┬─num_hosts_active─┐ 
│ ch6.nauu.com │ 9000 │ 0 │ │ 0 │ 
│ ch5.nauu.com │ 9000 │ 0 │ │ 0 │ 
└─────────┴────┴──────┴─────┴───────────┘

建表语句采用了宏变量

# CH5配置
<macros> 
    <shard>01</shard> 
    <replica>ch5.nauu.com</replica> 
</macros>
# CH6配置
<macros> 
    <shard>02</shard> 
    <replica>ch6.nauu.com</replica> 
</macros>

分布式DDL分发也依赖zookeeper

根路径为/clickhouse/task_queue/ddl，配置由

<distributed_ddl> 
    <!-- Path in ZooKeeper to queue with DDL queries --> 
    <path>/clickhouse/task_queue/ddl</path> 
</distributed_ddl>

流程

点执行CREATE TABLE ON CLUSTER，本着谁执行谁负责的原则，创建DDLLogEntry日志并将日志推送到ZooKeeper，同时也会由这个节点负责监控任务的执行进度

Distributed原理

自身不存储任何数据，而是作为数据分片的透明代理，能够自动路由数据至集群中的各个节点，所以Distributed表引擎需要和其他数据表引擎一起协同工作

  • 本地表：通常以_local为后缀进行命名。本地表是承接数据的载体，可以使用非Distributed的任意表引擎，一张本地表对应了一个数据分片
  • 分布式表：通常以_all为后缀进行命名。分布式表只能使用Distributed表引擎，它与本地表形成一对多的映射关系，日后将通过分布式表代理操作多张本地表

对于分布式表与本地表之间表结构的一致性检查，Distributed表引擎采用了读时检查的机制，这意味着如果它们的表结构不兼容，只有在查询时才会抛出错误，而在创建表时并不会进行检查

不同ClickHouse节点上的本地表之间，使用不同的表引擎也是可行的，但是通常不建议这么做

定义格式

ENGINE = Distributed(cluster, database, table [,sharding_key])
  • cluster 集群名称
  • database和table 数据库和表的名称，分布式表使用这组配置映射到本地表
  • sharding_key 分片键，通过分片规则将数据分布到各个host节点的本地表

示例创建分区表

CREATE TABLE test_shard_2_all ON CLUSTER sharding_simple ( id UInt64 )ENGINE = Distributed(sharding_simple, default, test_shard_2_local,rand())

示例创建本地表

CREATE TABLE test_shard_2_local ON CLUSTER sharding_simple ( id UInt64 )ENGINE = MergeTree() ORDER BY id PARTITION BY id

Distributed表的查询操作可以分为

  • 会作用于本地表：INSERT和SELECT，会以分布式的方式作用与local本地表
  • 只会影响Distributed：CREATE、DROP、RENAME和ALTER（不包括分区操作ATTACH PARTITION、REPLACE PARTITION等）
  • 不支持查询：不支持任何MUTATION类型的操作，包括ALTER DELETE和ALTER UPDATE

如果要删除一张分布式表，需要同时删除分布式表和本地表

# 删除分布式表
DROP TABLE test_shard_2_all ON CLUSTER sharding_simple 
# 删除本地表
DROP TABLE test_shard_2_local ON CLUSTER sharding_simple

分片规则可以是字段，也可以是表达式

# 按照用户id的余数划分
Distributed(cluster, database, table ,userid)
# 按照随机数划分
Distributed(cluster, database, table ,rand()) 
# 按照用户id的散列值划分
Distributed(cluster, database, table , intHash64(userid))

分片权重默认为1，可以设置为任意整数

如果有俩个分片，一个权重为10，另一个权重为20，slot的数量为30，按照权重元素的取值区间，与对应分片形成映射关系，slot落在[0, 10)对应第一个分片，落在[11, 29)对应第二个分片

slot = shard_value % sum_weight

写入分区数据的流程

  1. 对insert进行分片规划，划分数据
  2. 当前分片数据直接写入本地表
  3. 其他分片放到远端分片对应的分区目录
  4. 建立远端连接分发数据，并且监听目录下文件变化
  5. 其他分片确认insert节点的连接，接收到数据
  6. 由insert节点确认完成

副本复制数据的流程有两种

  1. 基于Distributed表引擎
  2. 基于ReplicatedMergeTree表引擎

Distributed需要insert的节点负责分片和副本的写入工作，单节点写入会成为瓶颈

ReplicatedMergeTree需要在shard配置中添加internal_replication为true，Distributed表在该shard中只会选择一个合适的replica并对其写入数据，多个replica副本之间的数据复制会交由ReplicatedMergeTree自己处理，不再由Distributed负责，从而为其减负

<shard> 
    <!-- 由ReplicatedMergeTree复制表自己负责数据分发 --> 
    <internal_replication>true</internal_replication> 
    <replica> 
        <host>ch5.nauu.com</host> 
        <port>9000</port> 
    </replica>
    <replica> 
        <host>ch6.nauu.com</host> 
        <port>9000</port> 
    </replica> 
</shard>

在shard中选择replica的算法为在ClickHouse的服务节点中，拥有一个全局计数器errors_count，当服务出现任何异常时，该计数累积加1；接着，当一个shard内拥有多个replica时，选择errors_count错误最少的那个

查询流程

当Distributed表接收到SELECT查询的时候，它会依次查询每个分片的数据，再合并汇总返回

多副本的路由规则为使用负载均衡算法从众多replica中选择一个，而具体使用何种负载均衡算法，则由load_balancing参数控制

  • random默认算法，根据errors_count值最小，相同值随机选择
  • nearest_hostname，random的变种，相同值的时候选择主机名接近的
  • in_order，random的变种，相同值的时候按照配置的顺序逐个选择
  • first_or_random，random的变种

分布式查询也遵循着谁执行谁负责

接收的节点负责串联整个过程，会先将分布式表的SQL语句，按照分片数量拆分为若干针对本地表的子查询，然后向每个分片发起查询，最后汇总各个分片的返回结果

ClickHouse管理与运维

用户配置

用来定义用户相关，包括系统参数、用户设定、权限和熔断机制

/etc/clickhouse-server/user.xml文件

<yandex> 
    <profiles><!-- 配置profile --> 
        <default> <!-- 自定义名称，默认角色--> 
            <max_memory_usage>10000000000</max_memory_usage> 
            <use_uncompressed_cache>0</use_uncompressed_cache> 
        </default> 
        <test1> <!-- 自定义名称，默认角色--> 
            <allow_experimental_live_view>1</allow_experimental_live_view> 
            <distributed_product_mode>allow</distributed_product_mode> 
        </test1> 
    </profiles>

client可以切换用户

SET profile = test1

支持继承

# 为normal_inherit的profile继承了test1和test2的所有配置项，并且使用新的参数值覆盖了test1中原有的distributed_product_mode配置项
<normal_inherit> <!-- 只有read查询权限--> 
    <profile>test1</profile> 
    <profile>test2</profile> 
    <distributed_product_mode>deny</distributed_product_mode> 
</normal_inherit>

配置约束

constraints标签可以设置一组约束条件，以保障profile内的参数值不会被随意修改

  • Min：最小值约束，在设置相应参数的时候，取值不能小于该阈值
  • Max：最大值约束，在设置相应参数的时候，取值不能大于该阈值
  • Readonly：只读约束，该参数值不允许被修改
<profiles><!-- 配置profiles --> 
    <default> <!-- 自定义名称，默认角色-->
         <max_memory_usage>10000000000</max_memory_usage> 
         <distributed_product_mode>allow</distributed_product_mode> 
         <constraints><!-- 配置约束--> 
            <max_memory_usage> 
                <min>5000000000</min> 
                <max>20000000000</max> 
            </max_memory_usage> 
            <distributed_product_mode> 
                <readonly/> 
            </distributed_product_mode> 
         </constraints> 
    </default>

命令行修改无效

SET max_memory_usage = 50 
DB::Exception: Setting max_memory_usage shouldn't be less than 5000000000.

用户定义

  • username
  • password
  • password_sha256_hex sha256加密算法
  • profile 用户使用的profile
  • quota 资源限制

权限管理

访问管理

网络访问权限：IP地址、host主机名称、正则匹配

示例

<user_normal> 
    <password></password> 
    <networks> 
        <ip>10.37.129.13</ip> 
    </networks> 
    <profile>default</profile> 
    <quota>default</quota> 
</user_normal>

数据库与字典访问权限，通过allow_databases和allow_dictionaries标签进行设置

<user_normal> 
    <allow_databases> 
        <database>default</database> 
        <database>test_dictionaries</database> 
    </allow_databases> 
    <allow_dictionaries> 
        <dictionary>test_flat_dict</dictionary> 
    </allow_dictionaries> 
</user_normal>

查询权限

包括四类

  • 读权限：包括SELECT、EXISTS、SHOW和DESCRIBE查询
  • 写权限：包括INSERT和OPTIMIZE查询
  • 设置权限：包括SET查询
  • DDL权限：包括CREATE、DROP、ALTER、RENAME、 ATTACH、DETACH和TRUNCATE查询
  • 其他权限：包括KILL和USE查询，任何用户都可以执行这些查询

通过两种标签控制

readonly：读权限、写权限和设置权限均由此标签控制，有三种取值

  • 当取值为0时，不进行任何限制（默认值）
  • 当取值为1时，只拥有读权限（只能执行SELECT、EXISTS、 SHOW和DESCRIBE）
  • 当取值为2时，拥有读权限和设置权限（在读权限基础上，增加了SET查询）

allow_ddl：DDL权限由此标签控制，它有两种取值

  • 当取值为0时，不允许DDL查询。
  • 当取值为1时，允许DDL查询（默认值）
<profiles> 
    <normal> <!-- 只有read读权限--> 
        <readonly>1</readonly> 
        <allow_ddl>0</allow_ddl> 
    </normal> 
    <normal_1> <!-- 有读和设置参数权限--> 
        <readonly>2</readonly> 
        <allow_ddl>0</allow_ddl> 
    </normal_1>

行级别权限

示例

<databases> 
    <database_name><!--数据库名称--> 
        <table_name><!--表名称--> 
            <filter> id < 10</filter><!--数据过滤条件--> 
        </table_name> 
    </database_name>
    <!—支持组合条件 <filter>id <= 100 or repo >= 100</filter>

熔断机制

根据时间周期的累积用量熔断

系统资源的用量是按照时间周期累积统计的，当累积量达到阈值，则直到下个计算周期开始之前，该用户将无法继续进行操作

<quotas> 
    <default> <!-- 自定义名称 --> 
        <interval> 
            <duration>3600</duration><!-- 时间周期 单位：秒 --> 
            <queries>0</queries> 
            <errors>0</errors> 
            <result_rows>0</result_rows> 
            <read_rows>0</read_rows> 
            <execution_time>0</execution_time> 
        </interval> 
    </default> 
</quotas>
  • default：表示自定义名称，全局唯一。
  • duration：表示累积的时间周期，单位是秒。
  • queries：表示在周期内允许执行的查询次数，0表示不限制。
  • errors：表示在周期内允许发生异常的次数，0表示不限制。
  • result_row：表示在周期内允许查询返回的结果行数，0表示不限制。
  • read_rows：表示在周期内在分布式查询中，允许远端节点读取的数据行数，0表示不限制。
  • execution_time：表示周期内允许执行的查询时间，单位是秒，0表示不限制

根据单次查询的用量熔断

  • max_memory_usage 运行一次查询限制使用的最大内存量，默认为10GB
  • max_memory_usage_for_user 以用户为单位进行统计，单个用户在运行查询时限制使用的最大内存量，默认为0，不限制
  • max_memory_usage_for_all_queries 所有运行的查询累加在一起所限制使用的最大内存量，默认为0，不限制
  • max_partitions_per_insert_block 在单次INSERT写入的时候，限制创建的最大分区个数，默认值为100个
  • max_rows_to_group_by 在执行GROUP BY聚合查询的时候，限制去重后聚合KEY的最大个数，默认为0，不限制
  • max_bytes_before_external_group_by 在执行GROUP BY聚合查询的时候，限制使用的最大内存量，默认为0，不限制
  • group_by_overflow_mode 熔断之后的处理方式

熔断后处理方式

  • throw：抛出异常，此乃默认值
  • break：立即停止查询，并返回当前数据
  • any：仅根据当前已存在的聚合KEY继续完成聚合查询

数据备份

导出文件备份

如果数据的体量较小，可以通过dump的形式将数据导出为本地文件

clickhouse-client --query="SELECT * FROM test_backup" > /chbase/test_backup.tsv

导入

cat /chbase/test_backup.tsv | clickhouse-client --query "INSERT INTO test_backup FORMAT TSV"

整个表备份可以拷贝目录

 mkdir -p /chbase/backup/default/ & cp -r /chbase/data/default/test_backup /chbase/backup/default/

通过快照表备份

CREATE TABLE test_backup_0206 AS test_backup
INSERT INTO TABLE test_backup_0206 SELECT * FROM test_backup

备份表可以放到其他的节点

INSERT INTO TABLE test_backup_0206 SELECT * FROM remote('ch5.nauu.com:9000', 'default', 'test_backup', 'default')

通过分区备份

提供了两种方式

  • FREEZE
  • FETCH

FREEZE方式备份

ALTER TABLE tb_name FREEZE PARTITION partition_expr

分区备份会同一保存到ClickHouse根路径/shadow/N子目录下，N为FREEZE执行过多少次决定，参考/shadow/increment.txt

备份还原需要使用ATTACH装载分区的方式，先将分区文件复制到相应数据表的detached目录下

FETCH方式备份

FETCH只支持ReplicatedMergeTree系列的表引擎

ALTER TABLE tb_name FETCH PARTITION partition_id FROM zk_path

工作原理与ReplicatedMergeTree同步数据原理类似，指定zk_path找到所有的副本实例，并选择合适的副本，下载对应分区的数据

分区备份还原需要借助ATTACH装载分区的方式

这两种方式虽然可以备份

服务监控

服务监控主要是两方面

  • 系统表
  • 查询日志

系统表主要是三种

  • metrics
  • events
  • asynchronous_metrics

metrics表内容

SELECT * FROM system.metrics LIMIT 5 
┌─metric──────┬─value─┬─description─────────────────────┐ 
│ Query             │ 1 │ Number of executing queries                       │ 
│ Merge             │ 0 │ Number of executing background merges             │ 
│ PartMutation      │ 0 │ Number of mutations (ALTER DELETE/UPDATE)         │ 
│ ReplicatedFetch   │ 0 │ Number of data parts being fetched from replica   │ 
│ ReplicatedSend    │ 0 │ Number of data parts being sent to replicas       │ 
└──────────┴─────┴─────────────────────────────┘

event表内容

SELECT event, value FROM system.events LIMIT 5 
┌─event─────────────────────┬─value─┐ 
│ Query                             │ 165 │ 
│ SelectQuery                       │ 92 │ 
│ InsertQuery                       │ 14 │ 
│ FileOpen                          │ 3525 │ 
│ ReadBufferFromFileDescriptorRead  │ 6311 │ 
└─────────────────────────┴─────┘

asynchronous_metrics表包括一些分配内存、执行队列

SELECT * FROM system.asynchronous_metrics LIMIT 5 
┌─metric───────────────────────┬─────value─┐ 
│ jemalloc.background_thread.run_interval   │ 0 │ 
│ jemalloc.background_thread.num_runs       │ 0 │ 
│ jemalloc.background_thread.num_threads    │ 0 │ 
│ jemalloc.retained                         │ 79454208 │ 
│ jemalloc.mapped                           │ 531341312 │
└────────────────────────────┴──────────┘

查询日志有6种需要单独配置

  • query_log
  • query_thread_log
  • part_log
  • text_log
  • metric_log