clickhouse简单使用及优化

原来项目用mysql，随着数据量增加已不再适用（目前每周大约2亿数据），且这部分数据主要用于分析，不涉及更改，调研后迁移到clickhouse。
目前用了 22.3.3.44 版本。

一、ClickHouse是什么？

ClickHouse® 是一种用于在线分析处理 (OLAP) 的高性能、面向列的 SQL 数据库管理系统 (DBMS)。它既可以作为开源软件使用，也可以作为云产品使用。。

二、特点

• 列式存储，OLAP（On-line Transaction Processing）数据库
• 具备DBMS功能
• 适用于只插入数据不更改或者少量更改数据的场景（clickhouse更改数据是很重的操作）
• 并发低，qps默认为100
• 查询速度快，多线程查询（单条sql即可多线程查询）
• 稀疏索引

---

三、使用步骤

1.安装看官网

clickhouse官方文档

2.数据类型

看官网，他的文档感觉写的很好~
![在这里插入图片描述](https://img-blog.csdnimg.cn/947e8b39b2c84bda849604314e9a08cc.png

3.命令行client操作

# 登录，如果没有密码就空着
clickhouse-client --user default --password root 
# 执行sql脚本
clickhouse-client --user default --password root  --multiquery <  /home/data/test_log.sql

4.用户密码设置

/etc/clickhouse-server/users.xml 文件中的users标签为用户，其中default代表用户名，这里我只用了default用户，可以添加其他用户

 <users>
        <default>
         <!-- 这个标签设置明文的密码，不建议用 -->
 			<!-- <password></password> -->
          <!-- 这个标签设置sha256的密码，推荐 -->  <password_sha256_hex>4813494d137e1631bba301d5acab6e7bb7aa74ce1185d456565ef51d737677b2</password_sha256_hex>
		    <networks>
            	<ip>::/0</ip>
            </networks>

            <!-- Settings profile for user. -->
            <profile>default</profile>

            <!-- Quota for user. -->
            <quota>default</quota>

            <!-- User can create other users and grant rights to them. -->
            <!-- <access_management>1</access_management> -->
        </default>
    </users>

5.表引擎

clickhouse有多种表引擎，其中用的最多的为合并树，是clickhouse中最强大的表引擎，它是一个家族，其中有多种细分类别。
该系列中的引擎MergeTree设计用于将大量数据插入表中。数据快速逐个写入表，然后应用规则在后台合并各个部分。这种方式比insert时不断改写storage中的数据效率高很多。

6.创建表

6.1创建普通表

我主要用了MergeTree，以它为例：

CREATE table test_log(
	id UInt32,
	sku_id String,
	total_amount Decimal(16,2),
	create_time Datetime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
PRIMARY KEY id
ORDER BY id

• ENGINE- 引擎名
• ORDER BY — 排序键
• PARTITION BY — 分区键 ，可选项。
• PRIMARY KEY - 如果要 选择与排序键不同的主键，在这里指定，可选项。

---

6.2创建分布式表

单机难以接入这么多流量，目前这个项目可以指定每台设备接入哪些流量，所以只需要增加一个分布式表用于查询所有设备数据即可，不需要通过分布式表写入。

1.更改配置文件 config.xml 在<remote_servers></remote_servers>标签之间添加集群信息，重启clickhouse，集群信息例（我设置了免密登录，需要密码还要写密码，看官网）：

<!-- 测试集群 -->
<test_cluster>
	<!-- 分片1 -->
	<shard>
		<replica>
			<host>192.168.10.183</host>
			<port>9000</port>
		</replica>
	</shard>
	<!-- 分片2 -->
	<shard>
		<replica>
			<host>192.168.10.184</host>
			<port>9000</port>
		</replica>
	</shard>
</test_cluster>

2.创建分布式表指定集群与服务器中的本地库名、表名，例：

CREATE TABLE d_test_log (
	id UInt32,
	create_time Datetime
) ENGINE = Distributed(test_cluster, default, test_log);

3.查询分布式表
select * from d_test_log limit 10;

四、用户及权限

1.用户

有两种方式添加用户：通过user.xml配置文件、通过sql。（官网说不能通过两种方式管理同一个用户，没有测试）

通过配置文件，就是在user.xml文件中添加用户标签，详情参考defaut用户的标签就可以，这里记录下通过sql对用户进行管理。

用户及权限相关文件默认在 /var/lib/clickhouse/access 目录下。

1. clickhouse默认带一个default用户，首先更改user.xml中设置，启用sql用户模式，在default用户标签中添加以下配置，重启服务后生效。

   <access_management>1</access_management>
   <named_collection_control>1</named_collection_control>
   <show_named_collections>1</show_named_collections>
   <show_named_collections_secrets>1</show_named_collections_secrets>
   

2. 此时default拥有可以通过sql个操作用户的权限了，可以执行命令查看用户及权限相关数据 SHOW ACCESS

3. 添加一个admin用户，后期作为管理员，default用户添加完用户及权限后可以去掉 1 中的相关配置，只保留默认配置，后期管理相关操作使用管理员用户。

   -- 创建用户并设置密码（其中密码为sha256存储）
   CREATE USER admin_user IDENTIFIED WITH sha256_password BY '123456';
   -- 赋予该用户所有权限
   GRANT ALL ON *.* TO admin_user WITH GRANT OPTION;
   

4. 添加一个普通用户（此处为了测试用，写明文密钥，线上需要用hash值）

   -- 创建用户并设置密码（其中密码为明文存储）
   CREATE USER test1 IDENTIFIED WITH plaintext_password BY '123456';
   -- 密码类型也可以省略，将使用服务器配置中指定的默认密码类型 <default_password_type>sha256_password</default_password_type>
   CREATE USER test1 IDENTIFIED BY '123456';
   

2.权限

根据官网的说明，clickhouse支持基于RBAC方式（基于角色的）的访问控制管理，也就是可以直接给用户设置权限，也可以创建角色给用户设置相应角色的权限。直接给用户赋权限与mysql方式一致（例如上方给管理员用户赋所用权限），此处记录下通过角色进行控制（还有可以对配额进行控制看官网）。

1. 创建角色

   CREATE ROLE test_role; 
   CREATE ROLE test_alter_role;
   

2. 添加角色权限

   GRANT SELECT ON test.* TO test_role; -- 给角色赋予查询 test 库所有表的权限（也可以一个角色有多个权限，此处为了测试写两个角色）
   GRANT ALTER COLUMN ON test.* TO test_alter_role; -- 给角色赋予更改 test 库所有表列数据的权限
   

3. 给用户添加角色权限

   GRANT test_role,test_alter_role TO test1; -- 赋予用户test1两个角色的权限 
   

4. 测试登录用户 test1 可以查询 指定库下的所有表及数据，测试可以更改column，测试不能更改表信息（例如表注释）。
   

五、同步mysql数据

CREATE TABLE log_mt ENGINE = MergeTree ORDER BY Id AS SELECT * FROM mysql('127.0.0.1:3306', 'test', 'test_log', 'root', 'root');

优化策略

1. 查询时指定select的字段，只获取需要的，这样io少
2. 批量插入，官方建议>1000
3. 不要使用join，clickhouse的join会将右表完全加载到内存，建议将一类型业务放到一张宽表中，如果确实合不到一起可以拆表，然后进行2次查询，或者使用去重的表引擎等。
4. 查询时尽量要指定时间，用PARTITION BY的字段
5. 使用物化视图（一张表不要建立太多的物化视图，插入时会关联插入这些物化视图的数据）
6. MergeTree设置冷热数据，将热数据放到固态盘中（冷热数据看官网的MergeTree引擎说明）
7. 字段添加索引