ClickHouse 的管理与运维
ClickHouse 管理和运维相关的知识，该部分可以让 ClickHouse 变得更加安全与健壮。在前面演示的案例中，为了方便，我们一直使用默认的 default 用户，并且没有配置密码，这显然不符合生产环境的要求。

接下来，我们就来介绍 ClickHouse 的权限、熔断机制、数据备份和服务监控等知识。

docker start docker-clickhouse
docker exec -it docker-clickhouse /bin/bash
clickhouse-client
clickhouse-client --host 地址 --port 端口 --user 用户 --password 密码
clickhouse-client --user default --password bigdata

1 用户配置

users.xml 配置文件默认位于 /etc/clickhouse-server 路径下，ClickHouse 用它来定义用户相关的配置项，包括系统参数的设定、用户的定义、权限以及熔断机制等。

1.1 用户的角色配置(profile)

在users.xml中有一个profiles标签，在该标签中我们可以定义用户角色。

一、默认配置：

10000000000random1

(1)random - choose random replica from set of replicas with minimum number of errors.
随机-从具有最小错误数的副本集中选择随机副本。
(2)nearest_hostname - from set of replicas with minimum number of errors, choose replica with minimum number of different symbols between replica’s hostname and local hostname (Hamming distance).
从具有最小错误数的副本集中选择副本，复制副本的主机名和本地主机名之间具有最小数量的不同符号（汉明距离）。
(3)in_order - first live replica is chosen in specified order.
按指定顺序选择第一个活动副本。
(4)first_or_random - if first replica one has higher number of errors, pick a random one from replicas with minimum number of errors.
如果第一个副本的错误数较高，请从错误数最少的副本中选择一个随机副本。

这是 ClickHouse 的默认配置，显然默认有两个角色，分别是 default 和 readonly。需要注意的是，这里的 default 和 readonly 指的是角色，每一个用户都具有一个角色。

二、切换角色
我们可以在 CLI 中直接切换到想要的角色：

SET profile = '角色名'

我们可以测试一下，首先我们默认使用的是default用户，该用户对应的角色默认也是default。然后还有一个readonly角色，从名字上也能看出该角色只能读数据，无法写数据，因为内部的readonly属性为 1，默认为0。

(1)默认为default角色，读写正常
insert into t1 values('lucy',[1,2]);
select * from t1;
┌─title─┬─value─┐
│ lucy  │ [1,2] │
└───────┴───────┘
(2)将角色切换为readonly
set profile = 'readonly';(3)写入数据失败，查询成功
insert into t1 values('lily',[3,4]);
DB::Exception: 
default: Cannot execute query in readonly mode. (READONLY)

当default用户具有default角色时，写数据一切正常，但是将default用户的角色切换为readonly时则被告知：Cannot execute query in readonly mode。

如果我们在default角色对应配置中也加上 
1
那么具有该角色的用户同样也会无法写数据。

在所有的角色配置（profile）中，名称为default的profile将作为默认的配置被加载，所以它必须存在。如果缺失名为 default 的 profile，那么 ClickHouse Server 会启动失败： Application: DB::Exception: Settings profile default not found。

三、profile的继承
profile 还支持继承，实现继承的方式是在定义中引用其他的 profile 名称，例如：

defaultdeny

相当于新建了一个名为 my_role 的角色，然后在对应的 profile 中继承了 default 的所有配置项，并且使用新的参数值覆盖了 default 中原有的 distributed_product_mode 配置项。

1.2 配置约束

constraints标签可以设置一组约束条件，以保障 profile 内的参数值不会被随意修改，约束条件有如下三种规则：

min：最小值约束，在设置相应参数的时候，取值不能小于该阈值。
max：最小值约束，在设置相应参数的时候，取值不能大于该阈值。
readonly：只读约束，该参数值不能被修改。

下面举例说明：

10000000000random5000000000200000000001

从上面的配置定义中可以看出，在 default 默认的 profile 内，给两组参数设置了约束。首先为 max_memory_usage 设置了 min 和 max 阈值；其次为 distributed_product_mode 设置了只读约束。然后重启 ClickHouse，并尝试修改 max_memory_usage 参数，将它改为 50：

set max_memory_usage = 50;
报错
DB::Exception: Setting max_memory_usage shouldn't be 
less than 5000000000. (SETTING_CONSTRAINT_VIOLATION)

可以看到最小值约束阻止了这次修改，接着继续修改 distributed_product_mode 的值：

set distributed_product_mode = 'allow';
报错
DB::Exception: Setting distributed_product_mode 
should not be changed. (SETTING_CONSTRAINT_VIOLATION)

同样配置约束成功阻止了预期外的修改。还有一点需要明确，在 default 中默认定义的 constraints 约束，将作为默认的全局约束，自动被其它 profile 继承。

1.3 用户

通过profiles标签可以为用户定义角色，那么可不可以定义用户呢？显然是可以的，通过 users 标签即可。
如果打开配置文件，会发现 users 标签下已经有一个默认的 default 用户，我们之前使用的一直都是这个用户，而我们也可以定义一个新用户，但必须包含如下属性：
一、默认配置

bigdata::/0defaultdefault

1.3.1 password

password 用于设置登录密码，支持明文(plaintext)、SHA256(in hex format)加密和 double_sha1 加密三种形式，可以任选其中一种进行设置。
Password could be specified in or in SHA256
(1)明文密码
在使用明文密码的时候，直接通过password标签定义，例如下面的代码。

123
如果 password 为空，则表示免密码登录。

(2)SHA256加密
在使用SHA256加密算法的时候，需要通过password_sha256_hex标签定义密码，例如下面的代码。

a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3

至于计算的方式，可以通过如下命令，比如对123进行加密：

# echo -n 123 |openssl dgst -sha256
(stdin)= a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3

或者使用 Python：

>>> import hashlib
>>> hashlib.sha256(b"123").hexdigest()
'a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3'
>>>

(3)double_sha1 加密：
在使用double_sha1加密算法的时候，则需要通过 password_double_sha1_hex 标签定义密码，例如下面的代码。

23ae809ddacaf96af0fd78ed04b6a265e05aa257

至于计算的方式，可以通过如下命令，比如对 123 进行加密：

# echo -n 123 | openssl dgst -sha1 -binary | openssl dgst -sha1
(stdin)= 23ae809ddacaf96af0fd78ed04b6a265e05aa257

或者使用 Python：

>>> import hashlib
>>> _ = hashlib.sha1(b"123").digest()
>>> hashlib.sha1(_).hexdigest()
'23ae809ddacaf96af0fd78ed04b6a265e05aa257'

1.3.2 networks

networks 表示被允许登录的网络地址，用于限制用户登录的客户端地址，关于这方面的介绍将会在后续展开。

1.3.3 profile

用户所使用的角色，直接引用相应的名称即可，例如：

profile_1

该配置的语义表示：该用户使用了名为 profile_1 的角色。

1.3.4 quota

quota 用于设置该用户能够使用的资源限额，可以理解成一种熔断机制。关于这方面的介绍同样将会在后续展开。

1.3.5 举例

下面我们就来定义一个完整的实例来定义三个用户，密码分别使用明文密码、sha256 加密、double sha1 加密：

  ...123::/0defaultdefaulta665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3::/0defaultdefault23ae809ddacaf96af0fd78ed04b6a265e05aa257::/0defaultdefault    

由于配置了密码，那么以后使用指定用户登录时，就必须指定密码了，举个栗子：

clickhouse-client --user user1_plaintext --password 123
配置文件中写的是加密后的结果，但是登录时，密码还是使用加密前的结果，这里是123
clickhouse-client --user user2_sha256 --password 123
clickhouse-client --user user3_double_sha1 --password 123

2 权限管理

权限管理是一个始终都绕不开的话题，ClickHouse分别从访问、查询和数据等角度出发，层层递进，为我们提供了一个较为立体的权限体系。

2.1 访问权限

访问层控制是整个权限体系的第一层防护，它又可进一步细分成两类权限。

2.1.1 网络访问权限

网络访问权限使用networks标签设置，用于限制客户端登录的地址，限制方式可以通过 IP 地址、host 主机名称实现，两者选其一即可。

比如通过 IP 地址设置：

127.0.0.1

通过 host 主机名称设置：

satori

2.1.2 数据库与字典访问权限

在客户端连入服务之后，可以进一步限制某个用户数据库和字典的访问权限，它们分别通过 allow_databases 和 allow_dictionaries 标签进行设置。如果不进行定义，则表示不进行限制。

123...defaultkagura_nana        test_dict    

通过上述操作，该用户在登录之后，将只能看到为其开放了访问权限的数据库和字典。

2.2 查询权限

查询权限是整个权限体系的第二层防护，设置在profile中，它决定了拥有此角色的用户能够执行的查询语句，查询权限可以分为以下四类：

(1)读权限：包括SELECT、EXISTS、SHOW和DESCRIBE查询。
(2)写权限：包括INSERT和OPTIMIZE查询。
(3)设置权限：包括SET查询。
(4)DDL权限：包括CREATE、DROP、ALTER、RENAME、ATTACH、DETACH和TRUNCATE查询。
(5)其它权限：包括KILL和USE查询，任何用户都可以执行这些查询。

上述权限，可以通过以下两项配置标签控制：
(1)readonly：读权限、写权限和设置权限均由此标签控制，它有三种取值：
当取值为0时，表示不进行任何限制（默认值）
当取值为1时，表示只拥有读权限，即只能执行 SELECT 、EXISTS、SHOW 和 DESCRIBE
当取值为2时，表示拥有读权限和设置权限，相当于在读权限的基础上增加了 SET 查询
(2)allow_ddl：DDL 权限由此标签控制，它有两种取值：
当取值为 0 时，不允许 DDL 查询
当取值为 1 时，允许 DDL 查询（默认值）
举个栗子，我们增加一个角色。

......  10        

修改配置文件之后，重启 ClickHouse，然后连接：

create database test;
set profile = 'profile_test';
drop database test;

我们看到切换角色之后，不再具有 DDL 执行权限。当然数据也是只读模式，此时写入数据是失败的。至此，权限设置已然生效。

2.3 数据行级权限

数据权限是整个权限体系中的第三层防护，它决定了一个用户能够看到什么数据，说人话就是数据粒度更细了，可以只将一张表的部分数据暴露给用户。

而该权限使用 database 标签定义（位于 users 标签内部），database 通过定义用户级别的查询过滤器来实现数据的行级粒度权限，它的定义规则如下所示：

   id > 10  

我们以之前的表t1 为例，测试一下，所以配置文件修改如下：

  123::/0defaultdefault   id >2  

修改配置文件之后，重启 ClickHouse，然后连接：

clickhouse-client --user default --password bigdata

整个过程还是很好理解的，那么 ClickHouse 底层是怎么做的呢？答案很简单，从配置文件中 filter 标签的内容也能看出来，就是追加了一个 WHERE 条件。

对于数据权限的使用有一点需要明确，在使用了这项功能之后，PREWHERE 优化将不再生效。所以是直接利用 ClickHouse 的内置过滤器，还是通过拼接 WHERE 查询条件的方式实现行级过滤，需要根据使用场景进行权衡。

3 熔断机制

熔断是限制资源被过度使用的一种自我保护机制，当使用的资源数量达到阈值时，那么正在进行的操作会被自动中断。而按照使用资源统计方式的不同，熔断机制可以分为两类。

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

在这种方式下，系统资源的用量是按照时间周期累积统计的，当累积量达到阚值，则直到下个计算周期开始之前，该用户将无法继续进行操作。这种方式通过 users.xml 内的 quotas 标签来定义资源配额，以默认的配置为例：

360000000

看图中最后一行，是 /clickhouse，显然到此配置文件就结束了，所以 users.xml 中最外层是clickhouse标签，然后clickhouse标签里面有三个子标签，分别是 profiles、users、quotas，分别用于定义角色、定义用户、熔断限流，还是比较简单的，整个结构比较清晰。然后看看 quotas 标签里面的内容都代表什么含义吧。

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

我们来配置一下：

360020000

在名为 limit_1 的配置中，1 个小时的周期内只允许最多 2 次查询，下面让其作用在 user_test_2 用户上。

 ...limit_1 ...

然后重启 ClickHouse，并以 user_test_2 用户启动。

clickhouse-client --user user_test_2 --password 123
报错
Code: 201. DB::Exception: Received from localhost:9000.DB::Exception: Quota for user `user_test_2` for 3600s has been exceeded: queries = 4/2. Interval will end at 2022-11-19 05:00:00. Name of quota template: `limit_1`. (QUOTA_EXPIRED)

执行两次查询之后，如果再执行的话就会报错，证明熔断机制确实已经生效。

3.2 根据单次查询的用量熔断

在这种方式下，系统资源的用量是按照单次查询统计的，而具体的熔断规则，则是由许多不同配置项组成的，这些配置项需要定义在用户 profile 中。如果某次查询使用的资源用量达到了阈值，则会被中断。以配置项 max memory_usage 为例，它限定了单次查询可以使用的内存用量，在默认的情况下其规定不得超过 10 GB，如果一次查询的内存用量超过 10 GB，则会得到异常。

需要注意的是，在单次查询的用量统计中，ClickHouse 是以分区为最小单元进行统计的（不是数据行的粒度），这意味着单次查询的实际内存用量是有可能超过阈值的。

熔断相关的配置比较多，这里介绍几个常用的。

1）max_memory_usage：在单个 ClickHouse 服务进程中，运行一次查询限制使用的最大内存量，默认值为 10GB。

10000000000

2）max_memory_usage_for_user：在单个 ClickHouse 服务进程中，以用户为单位进行统计，单个用户在运行查询时限制使用的最大内存量，默认值为 0，即不做限制。

3）max_memory_usage_for_all_queries：在单个 ClickHouse 服务进程中，所有运行的查询累加在一起所限制使用的最大内存量，默认值为 0，即不做限制。

4）max_partitions_per_insert_block：在单次 INSERT 写入的时候，限制创建的最大分区个数，默认值为 100 个。如果超过这个阈值，将会出现异常。

Too many partitions for single INSERT block ······

5）max_rows_to_group_by：在执行 GROUP BY 聚合查询的时候，限制去重后的聚合 KEY 的最大个数，默认值为 0，不做限制。当超过阈值时，其处理方式由 group_by_overflow_mode 决定。

6）group_by_overflow_mode：当 max_rows_to_group_by 熔断规则触发时，group_by_overflow_mode 将会提供三种处理方式。

throw：抛出异常，此乃默认值
break：立即停止查询，并返回当前数据
any：仅根据当前已存在的聚合 KEY 继续完成聚合查询
7）max_bytes_before_external_group_by：在执行 GROUP BY 查询的时候，限制使用的最大内存量，默认值为 0，不做限制。当超过阈值时，聚合查询将会进一步借用本地磁盘。

4 数据备份

在之前的系列中，我们已经知道了数据副本的使用方法，那么问题来了：既然已经有了数据副本，那么还需要数据备份吗？显然数据备份是需要的，因为数据副本并不能处理误删数据这类行为。ClickHouse自身提供了多种备份数据的方法，根据数据规模的不同，可以选择不同的形式。

4.1 导出文件备份

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

clickhouse-client --query="SELECT * FROM table_name" > table_name.tsv

如果想将备份数据导入的话，可以这么做：

cat table_name.csv | clickhouse-client --query="INSERT INTO table_name FORMAT TSV"

上述这种 dump 形式的优势在于，可以利用 SELECT 查询并筛选数据，然后按需备份。如果是备份整个表的数据，也可以直接复制它的整个目录文件。

4.2 通过快照表备份

快照表实质上就是普通的数据表，它通常按照业务规定的备份频率创建，例如按天或者按周创建。所以首先需要建立一张与原表结构相同的数据表，然后再使用 INSERT INTO SELECT句式，点对点地将数据从原表写人备份表。假设数据表 table_name 需要按日进行备份，现在为它创建当天的备份表：

CREATE TABLE table_name_bak AS table_name

有了备份表之后就可以点对点地备份数据了。

INSERT INTO table_name_bak SELECT * FROM table_name

如果考虑到容灾问题，也可以将备份表放在不同的 ClickHouse 节点上，此时需要将 SQL 语句改成远程查询的形式：

INSERT INTO table_name_bak SELECT * FROM remote('xx.xx.xx.xx:9000', 'default', 'table_name', 'default')

4.3 按分区备份

基于数据分区的备份，ClickHouse 目前提供了 FREEZE 和 FETCH 两种方式，现在分别介绍它们的使用方法。

5 服务监控

基于原生功能对 ClickHouse 进行监控，可以从两方面入手：系统表和查询日志，接下来分别介绍它们的使用方法。

5.1 系统表

在众多的 SYSTEM 系统表中，主要由以下三张表支撑了对 ClickHouse 运行指标的查询，它们分别是 metrics、events 和 asynchronous_metrics。

5.1.1 metrics

metrics 表用于统计 ClickHouse 服务在运行时，当前正在执行的高层次的概要信息，包括正在执行的查询总次数、正在发生合并的操作总次数等。

select * from system.metrics limit 5;metric         |value|description                                    |
---------------+-----+-----------------------------------------------+
Query          |    0|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    |

5.1.2 events

events 表用于统计 ClickHouse 服务在运行过程中，已经执行过的高层次的累积概要信息，包括总的查询次数、总的 SELECT 查询次数等。

select event,value from system.events limit 5;event                |value|
---------------------+-----+
Query                |    9|
SelectQuery          |    8|
FailedQuery          |    3|
FailedSelectQuery    |    2|
QueryTimeMicroseconds|11061|

5.1.3 asynchronous_metrics

asynchronous_metrics 表用于统计 ClickHouse 服务在运行过程中，当前后台正在异步运行的高层次的概要信息，包括当前分配的内存、执行队列中的任务数量等。

select * from system.asynchronous_metrics limit 5;metric                          |value    |
--------------------------------+---------+
jemalloc.arenas.all.muzzy_purged|5077730.0|
BlockDiscardBytes_dm-0          |      0.0|
jemalloc.arenas.all.dirty_purged|5303889.0|
BlockDiscardBytes_sda           |      0.0|
jemalloc.arenas.all.pdirty      |   2253.0|

5.2 查询日志

查询日志目前主要有 6 种类型，它们分别从不同角度记录了 ClickHouse 的操作行为。所有查询日志在默认配置下都是关闭状态，需要在 config.xml 配置中进行更改，接下来分别介绍它们的开启方法。在配置被开启之后，ClickHouse 会为每种类型的查询日志自动生成相应的系统表以供查询。

5.2.1 query_log

query_log 是最常用的查询日志，它记录了 ClickHouse 服务中所有已经执行的查询记录，它的全局定义方式如下所示：

systemquery_logtoYYYYMM(event_date)7500

如果只希望具有某些角色用户才能开启 query_log，那么可以在 users.xml 的用户 profile 中增加一个标签：1。
query_log 开启后，即可通过相应的系统表对记录进行查询。

SELECT * FROM system.query_log

返回的日志信息十分完善，涵盖了查询语句、执行时间、返回的数据量和执行用户等。

5.2.2 query_thread_log

query_thread_log 记录了所有线程的执行查询的信息，它的全局定义方式如下所示：

systemquery_thread_logtoYYYYMM(event_date)7500

如果只希望具有某些角色用户才能开启 query_log，那么可以在 users.xml 的用户 profile 中增加一个标签：1。
log_query_thread 开启后，即可通过相应的系统表对记录进行查询。

SELECT * FROM system.log_query_thread

返回的日志信息十分完善，涵盖了线程名称、查询语句、执行时间、和内存使用量等。

5.2.3 part_log

part_log 记录了 MergeTree 系列表引擎的分区操作日志，其全局定义方式如下：

systempart_log7500

part_log 开启后，即可通过相应的系统表对记录进行查询。

SELECT * FROM system.part_log

返回的日志信息十分完善，涵盖了操纵类型、表名称、分区信息和执行时间等。

5.2.4 text_log

text_log 日志记录了 ClickHouse 运行过程中产生的一系列打印日志，包括 INFO、DEBUG 和 TRACE，它的全局定义方式如下：

systemtext_log7500

text_log 开启后，即可通过相应的系统表对记录进行查询。

SELECT * FROM system.text_log

返回的日志信息十分完善，涵盖了线程名称、日志对象、日志信息和执行时间等等。

5.2.5 metric_log

metric_log 日志用于将 system.metrics 和 system.events 中的数据汇聚到一起，它的全局定义方式如下所示：

systemmetric_log75001000    

metric_log 开启后，即可通过相应的系统表对记录进行查询。

SELECT * FROM system.metric_log

以上就是几种查询日志，当然，除了这些自身的查询日志之外，ClickHouse 还能够与众多的第三方系统集成，比如 Prometheus。当然监控系统又是一个比较大的话题了，这里就不再展开了，有兴趣可以去调研一下。