ClickHouse学习笔记

前言

ClickHouse是俄罗斯Yandex 2016年开源的列式存储数据库（DBMS）与hbase类似，使用C++编写。主要用于 在线分析处理查询（OLAP），常用于SQL查询进行数据分析生成数据报告，大数据领域-数仓。
官网在线测试
官网测试数据集

基础入门

版本

20.6.3开始支持执行 explain
20.8开始支持数据同步到mysql

列式存储vs行式存储

id	name	age
1	zhangsan	10
2	lisi	11
3	wangwu	12

行式存储，磁盘存储结构

| 1 | zhangsan | 10 | 2 | lisi | 11 | 3 | wangwu | 12 |

好处是想查某个人所有的属性时，可以通过一次磁盘查找加顺序读取就可以。但是当想查所有人的年龄时，需要不停的查找，或者全表扫描才行，遍历的很多数据都是不需要的。

列式存储，磁盘存储结构

| 1 | 2 | 3 | zhangsan | lisi | wangwu | 11 | 12 | 13 |
这时想查所有人的年龄只需把年龄那一列拿出来就可以

列式储存的好处

• 对于列的聚合，计数，求和等统计操作原因优于行式存储。
• 由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重。
• 由于数据压缩比更好，一方面节省了磁盘空间，另一方面对于 cache 也有了更大的发挥空间。

DBMS功能

对标准SQL语法支持的比较好，包括DDL和DML及相关函数、权限管理、数据备份与恢复。

多样化引擎

目前包括合并树（merge tree）、日志、接口、和其他四类20多种引擎

高吞吐写入能力

采用LSM Tree结构，数据写入后定期在后台compaction（合并），数据导入时都是顺序写append（速度快），写入后数据段不可更改，compaction时也是多个段merge sort后顺序写回磁盘

数据分区与线程级并行

数据分区可避免全表扫描。将数据划分为多个partition，每个partition再进一步划分为多个index。然后利用多核CPU分别处理其中一部分来实现并行数据处理。这种设计下，单条query就能利用整机所有CPU，极大提高查询效率。弊端是单条query就使用多核CPU不利于并发多条查询，因此不适用于高qps的查询业务

• cpu可能容易达到瓶颈
• 高并发查询也可能达到瓶颈

主要配置文件

/etc/clickhouse-server

• 服务端配置：config.xml （该配置无法通过client set 动态修改）需要重启CK服务
  /https://clickhouse.com/docs/zh/operations/server-configuration-parameters/settings/
  
• 用户参数配置users.xml（cpu、memory等，优先放到这里，没有的才去config.xml中查询，该配置中参数支持client set 动态修改）
  https://clickhouse.com/docs/zh/operations/settings/
  
• 集群分片和副本配置：clickhouse_remote_servers.xml / metrika.xml
• zookeeper集群配置：zookeeper-servers.xml

数据类型

https://clickhouse.com/docs/zh/sql-reference/data-types/
整型、浮点型（Float32、Float64）布尔型、Decimal型、字符串型、枚举型（数据之间绑定关系）、数组型
时间型
date 年月日
datetime 年月日时分秒
datetime64 年月日时分秒毫秒
…

数据库引擎（集成MYSQL等）

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换
https://clickhouse.com/docs/zh/engines/database-engines/mysql/

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause'])
SETTINGS
    [connection_pool_size=16, ]
    [connection_max_tries=3, ]
    [connection_wait_timeout=5, ] /* 0 -- do not wait */
    [connection_auto_close=true ]
;

表引擎（如何存储数据）

表引擎决定了如何存储数据。
表引擎的使用方式须显式在创建表时定义该表使用的引擎，以及引擎使用的相关参数。（名称大小写敏感）
https://clickhouse.com/docs/zh/engines/table-engines/

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

Tinylog：不支持索引、以列文件形式保存，一般保存较少数据的小表

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

Memory：不支持索引、数据以未压缩的原始形式保存在内存中，用的较少
MergeTree：支持索引和分区，生产环境最常用
https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/mergetree/#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);

# 插入
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');

ORDER BY （排序必选）。

• 可以是一组列的元组或任意的表达式。 例如: ORDER BY (CounterID, EventDate) ，设定了分区内的数据按哪些字段顺序进行有序保存。
• 如果没有使用 PRIMARY KEY 显式指定的主键，CK 会使用排序键作为主键。
• 如果不需要排序，可以使用 ORDER BY tuple()

PARTITION BY (分区可选)

• 使用分区可以降低扫描的范围，优化查询速度。
• 当跨分区查询，会以分区为单位并行处理。
• 不设置默认使用一个all分区，设定后会保存到不同的分区目录中。
• 要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 “YYYYMM”。如：

ls /var/lib/clickhouse/data/default/t_order_mt
20200601_1_11_1   20200602_14_14_0  20200602_2_14_3  format_version.txt
20200601_13_13_0  20200602_2_12_2   detached

• 数据写入与分区合并：任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后几分钟，会自动把临时分区的数据，合并到已有分区中。

手动合并表所有分区
optimize table t_order_mt final;
手动合并表指定分区
optimize table t_order_mt partition '20200602' final;

PRIMARY KEY（主键可选）
默认情况下主键跟排序键（由 ORDER BY 子句指定）相同，通常不需要修改。
CK中只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同 primary key 的数据。上述案例索引id就是重复的

SQL操作

官网

• ClickHouse 提供了 Delete 和 Update 的能力，这类操作被称为 Mutation 查询，它可以看做 Alter 的一种。虽然可以实现修改和删除，但是和一般的 OLTP 数据库不一样，Mutation 语句是一种很“重”的操作，而且不支持事务。
• “重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。

增
insert into st_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
删
alter table st_order_mt delete where sku_id ='sku_001';
改
alter table st_order_mt update total_amount=toDecimal32(2000.00,2) where id =205;
查
select * from st_order_mt_all2 where id = 205;

CK集群原理

参考文章：https://blog.csdn.net/qq_28603127/article/details/109788763

副本

使用副本可以提高数据可用性

副本测试

• 副本只能同步数据，不能同步表结构，所以我们需要在每台机器上自己手动建表

• 参数说明：ReplicatedMergeTree 中，第一个参数是分片的 zk_path 一般按照：/clickhouse/table/{shard}/{table_name} 的格式写，如果只有一个分片就写 01 即可。第二个参数是副本名称，相同的分片副本名称不能相同。

create table t_order_rep2 (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_76')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);

create table t_order_rep2 (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine =ReplicatedMergeTree('/clickhouse/table/01/t_order_rep','rep_77')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);

insert into t_order_rep2 values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 12:00:00'),
(103,'sku_004',2500.00,'2020-06-01 12:00:00'),
(104,'sku_002',2000.00,'2020-06-01 12:00:00'),
(105,'sku_003',600.00,'2020-06-02 12:00:00');

验证

 select * from t_order_rep2;

SELECT *
FROM t_order_rep2

Query id: 5b1c117a-ea95-41aa-b96c-4673298ccae2

┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2020-06-01 12:00:00 │
│ 102 │ sku_002 │      2000.00 │ 2020-06-01 12:00:00 │
│ 103 │ sku_004 │      2500.00 │ 2020-06-01 12:00:00 │
│ 104 │ sku_002 │      2000.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 105 │ sku_003 │       600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

5 rows in set. Elapsed: 0.006 sec. 

分片集群

• 副本可以提高数据可用性，但每台服务器需要容纳全量数据。
• 分片解决对数据的横向扩展，即将一份完整的数据进行切分，不同的分片分不到不同的节点上。再通过Distributed 表引擎把数据拼接起来一同使用。
• Distributed 表引擎本身不存储数据，有点类似于 MyCat 之于 MySql，成为一种代理中间件，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。
• ClickHouse 的集群是表级别的，实际企业中，大部分做了高可用，但是没有用分片，为了避免降低查询性能以及操作集群的复杂性。

ansible安装部署及配置管理

https://galaxy.ansible.com/alexeysetevoi/clickhouse 该role包含对clickhouse安装以及各种配置管理（集群分片和副本、权限、创建db、zk相关、指定安装版本等），不同版本之间稍加修改即可使用。

CK单点和集群安装方式一样，单点与集群最大的区别在于增加了/etc/clickhouse-server/conf.d/zookeeper-servers.xml zookeeper相关配置（默认文件名metrika.xml）。当有这个配置后CK单点会组成集群。但是要注意，CK集群和ES不同，分片与副本需要通过配置文件人工分配。 通常在/etc/clickhouse-server/conf.d/clickhouse_remote_servers.xml 中指定。

/etc/clickhouse-server/conf.d/zookeeper-servers.xml

<?xml version="1.0"?>
<!-- Ansible managed -->
<yandex>
  <zookeeper>
    <node index="1">
      <host>10.18.251.76</host>
      <port>12181</port>
    </node>
    <node index="2">
      <host>10.18.251.77</host>
      <port>12181</port>
    </node>
    <node index="3">
      <host>10.18.251.91</host>
      <port>12181</port>
    </node>
  </zookeeper>
</yandex>

/etc/clickhouse-server/conf.d/clickhouse_remote_servers.xml

<?xml version="1.0"?>
<!-- Ansible managed -->
<yandex>
  <remote_servers>
    <s1r2> <!-- 集群名称 -->
      <shard> <!-- 集群的第一个分片 -->
        <internal_replication>true</internal_replication>
        <replica> <!-- 集群的第一个副本 -->
          <host>10.18.251.76</host>
          <port>9000</port>
        </replica>
        <replica> <!-- 集群的第二个副本 -->
          <host>10.18.251.77</host>
          <port>9000</port>
        </replica>
      </shard>
    </s1r2>
  </remote_servers>
</yandex>

分片测试

在集群s1r2中创建表st_order_mt，发现配置定义中的副本节点和分片节点会同步

• 集群名字要和配置文件中的一致
• 分片和副本名称从配置文件的宏定义中获取，便于后续管理

create table st_order_mt on cluster s1r2 (
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine 
=ReplicatedMergeTree('/clickhouse/tables/{shard}/st_order_mt','{replica}')
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);

Query id: a7cef48a-ebdf-4419-8d06-e6fcae06bfcb

┌─host─────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 10.18.251.77 │ 9000 │      0 │       │                   1 │                1 │
└──────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host─────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 10.18.251.76 │ 9000 │      0 │       │                   0 │                0 │
└──────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

2 rows in set. Elapsed: 0.258 sec. 

创建Distribute分布式表 st_order_mt_all2
参数说明：
Distributed（集群名称，库名，本地表名，分片键）
分片键必须是整型数字，所以用 hiveHash 函数转换，也可以 rand()

create table st_order_mt_all2 on cluster s1r2
(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
)engine = Distributed(s1r2,default, st_order_mt,hiveHash(sku_id));

Query id: 25da6baa-8b7a-4f5f-9644-f1db529d184d

┌─host─────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 10.18.251.77 │ 9000 │      0 │       │                   1 │                0 │
│ 10.18.251.76 │ 9000 │      0 │       │                   0 │                0 │
└──────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

2 rows in set. Elapsed: 0.124 sec. 

insert into st_order_mt_all2 values
(201,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(202,'sku_002',2000.00,'2020-06-01 12:00:00'),
(203,'sku_004',2500.00,'2020-06-01 12:00:00'),
(204,'sku_002',2000.00,'2020-06-01 12:00:00'),
(205,'sku_003',600.00,'2020-06-02 12:00:00');

结论：通过分布式表查询可看到所有数据；查看本地表则只可以看到对应分片的数据。

优化

Explain 查看执行计划

https://clickhouse.com/docs/en/sql-reference/statements/explain/#explain-types

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] 
SELECT ... [FORMAT ...]

• PLAN：用于查看执行计划，默认值。

1. header 打印计划中各个步骤的 head 说明，默认关闭，默认值 0;
2. description 打印计划中各个步骤的描述，默认开启，默认值 1
3. actions 打印计划中各个步骤的详细信息，默认关闭，默认值 0。

• AST ：用于查看语法树;
• SYNTAX：用于优化语法;
• PIPELINE：用于查看 PIPELINE 计划。

plan 实例

简单查询
explain plan select arrayJoin([1,2,3,null,null]);
复杂查询
explain header=1, actions=1,description=1 select number from 
system.numbers limit 10;

SYNTAX 实例

//先做一次查询
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM 
numbers(10);
//查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 
'atguigu') FROM numbers(10);

建表优化

时间字段的类型

建表时能用数值型或日期时间型表示的字段就不要用字符串，因为 DateTime 不需要经过函数转换处理，执行效率高、可读性好。

create table t_type2(
id UInt32,
sku_id String,
total_amount Decimal(16,2) ,
create_time Int32 
) engine =ReplacingMergeTree(create_time)
partition by toYYYYMMDD(toDate(create_time)) –-需要转换一次，否则报错
primary key (id)
order by (id, sku_id);

空值存储类型

https://clickhouse.com/docs/zh/sql-reference/data-types/nullable/
官方建议：Nullable 类型几乎总是会拖累性能，因为存储 Nullable 列时需要创建一个额外的文件来存储 NULL 的标记，并且 Nullable 列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值（例如用-1 表示没有商品ID）

分区和索引

• 一般选择按天分区。
• 必须指定索引列，ClickHouse 中的索引列即排序列，通过 order by 指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、查询频率大的在前原则；还有基数特别大的不适合做索引列，如用户表的 userid 字段；通常筛选后的数据满足在百万以内为最佳。

表参数

如果表中不是必须保留全量历史数据，建议指定 TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL 也可以通过 alter table 语句随时修改。

配置优化

写入和删除优化

• 尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge 任务带来巨大压力
• 不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据（依服务器性能而定）
• Too many parts 处理：使用 WAL 预写日志，提高写入性能。in_memory_parts_enable_wal 默认为 true

1. 在服务器内存充裕的情况下增加内存配额，一般通过 max_memory_usage 来实现
2. 在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数来实现。

CPU

• background_pool_size
  后台线程池的大小，merge 线程就是在该线程池中执行，该线程池不仅仅是给 merge 线程用的，默认值 16，允许的前提下建议改成 cpu 个数的 2 倍（线程数）。
• background_schedule_pool_size
  执行后台任务（复制表、Kafka 流、DNS 缓存更新）的线程数。默认 128，建议改成 cpu 个数的 2 倍（线程数）。
• background_distributed_schedule_pool_size
  设置为分布式发送执行后台任务的线程数，默认 16，建议改成 cpu个数的 2 倍（线程数）。
• max_concurrent_queries
  最大并发处理的请求数(包含 select,insert 等)，默认值 100，推荐 150(不够再加)~300。
• max_threads
  设置单个查询所能使用的最大 cpu 个数，默认是 cpu 核数

内存

• max_memory_usage
• 此参数在 users.xml 中,表示单次 Query 占用内存最大值，该值可以设置的比较大，这样可以提升集群查询的上限。保留一点给 OS，比如 128G 内存的机器，设置为 100GB。
• max_bytes_before_external_group_by
  一般按照 max_memory_usage 的一半设置内存，当 group 使用内存超过阈值后会刷新到磁盘进行。
  因为 clickhouse 聚合分两个阶段：查询并及建立中间数据、合并中间数据，结合上一项，建议 50GB。
• max_bytes_before_external_sort
  当 order by 已使用 max_bytes_before_external_sort 内存就进行溢写磁盘(基于磁盘排序)，如果不设置该值，那么当内存不够时直接抛错，设置了该值 order by 可以正常完成，但是速度相对存内存来说肯定要慢点(实测慢的非常多，无法接受)。
• max_table_size_to_drop
  此参数在 config.xml 中，应用于需要删除表或分区的情况，默认是50GB，意思是如果删除 50GB 以上的分区表会失败。建议修改为 0，这样不管多大的分区表都可以删除。

存储

ClickHouse 不支持设置多数据目录，建议使用SSD提升读写性能。

查询优化

单表查询

Prewhere 替代 where

默认情况下， where 条件会自动优化成 prewhere。
但在以下情况，需要手动指定：

• 使用常量表达式
• 使用默认值为 alias 类型的字段
• 包含了 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查询
• select 查询的列字段和 where 的谓词相同
• 使用了主键字段

数据采样

采样修饰符只有在 MergeTree engine 表中才有效，且在创建表时需要指定采样策略。

SELECT Title,count(*) AS PageViews 
FROM hits_v1
SAMPLE 0.1 #代表采样 10%的数据,也可以是具体的条数
WHERE CounterID =57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000

列裁剪与分区裁剪

数据量太大时应避免使用 select * 操作，查询的性能会与查询的字段大小和数量成正比，字段越少，消耗的 io 资源越少，性能就会越高。

反例：
select * from datasets.hits_v1;
正例：
select WatchID, 
JavaEnable, 
Title, 
GoodEvent, 
EventTime, 
EventDate, 
CounterID, 
ClientIP, 
ClientIP6, 
RegionID, 
UserID
from datasets.hits_v1;

分区裁剪就是只读取需要的分区，在过滤条件中指定。、

select WatchID, 
JavaEnable, 
Title, 
GoodEvent, 
EventTime, 
EventDate, 
CounterID, 
ClientIP, 
ClientIP6, 
RegionID, 
UserID
from datasets.hits_v1
where EventDate='2014-03-23';

uniqCombined(近似去重) 替代 distinct

性能可提升 10 倍以上，uniqCombined 底层采用类似 HyperLogLog 算法实现，能接收 2%左右的数据误差，可直接使用这种去重方式提升查询性能。Count(distinct )会使用 uniqExact精确去重。
不建议在千万级不同数据上执行 distinct 去重查询，改为近似去重 uniqCombined

反例：
select count(distinct rand()) from hits_v1;
正例：
SELECT uniqCombined(rand()) from datasets.hits_v1

多表关联

用 IN 代替 JOIN

当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是 JOIN

大小表 JOIN

多表 join 时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较，ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。

分布式表使用 GLOBAL

两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上。如果不加 GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 N²次（N是该分布式表的分片数量），这就是查询放大，会带来很大开销。

物化视图（数据快照）

普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。
物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表。

优缺点

优点：查询速度快
缺点：占用机器资源

应用场景

对于一些确定的数据模型，可将统计指标通过物化视图的方式进行构建，这样可避免查询时重复计算的过程，物化视图会在有新数据插入时进行更新。

基本语法

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] 
[ENGINE = engine] [POPULATE] AS SELECT ...

测试

建表
CREATE TABLE hits_test
(
EventDate Date, 
CounterID UInt32, 
UserID UInt64, 
URL String, 
Income UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192;

导入数据
INSERT INTO hits_test 
SELECT 
EventDate,
CounterID,
UserID,
URL,
Income 
FROM hits_v1 
limit 10000;

创建物化视图
#建表语句
CREATE MATERIALIZED VIEW hits_mv 
ENGINE=SummingMergeTree
PARTITION BY toYYYYMM(EventDate) ORDER BY (EventDate, intHash32(UserID)) 
AS SELECT
UserID,
EventDate,
count(URL) as ClickCount,
sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate >= '2014-03-20' #设置更新点,该时间点之前的数据可以另外通过
#insert into select …… 的方式进行插入
GROUP BY UserID,EventDate;
##或者可以用下列语法，表 A 可以是一张 mergetree 表
CREATE MATERIALIZED VIEW 物化视图名 TO 表 A
AS SELECT FROM 表 B;

导入增量数据
#导入增量数据
INSERT INTO hits_test 
SELECT 
EventDate,
CounterID,
UserID,
URL,
Income 
FROM hits_v1 
WHERE EventDate >= '2014-03-23' 
limit 10;
#查询物化视图
SELECT * FROM hits_mv;

导入历史数据
#导入增量数据
INSERT INTO hits_mv
SELECT
UserID,
EventDate,
count(URL) as ClickCount,
sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate = '2014-03-20'
GROUP BY UserID,EventDate
#查询物化视图
SELECT * FROM hits_mv;

常见问题排查

分布式 DDL 某数据节点的副本不执行

解决方法：重启该节点

数据副本表和数据不一致

解决方法：在缺少表的数据副本节点上创建缺少的表，创建为本地表，表结构可以在其他数据副本通过 show crete table xxxx 获取。
表结构创建后，clickhouse 会自动从其他副本同步该表数据，验证数据量是否一致即可。

副本节点全量恢复

解决方法：
清空异常副本节点的 metadata 和 data 目录。
从另一个正常副本将 metadata 目录拷贝过来（这一步之后可以启动数据库，但是只有表结构没有数据）。
执行 sudo -u clickhouse touch /data/clickhouse/flags/force_restore_data
启动数据库。

Clickhouse 节点意外关闭

启动异常节点，期间其他副本写入数据会自动同步过来，其他副本的建表 DDL 也会同步。

其他问题参考

https://help.aliyun.com/document_detail/162815.html?spm=a2c4g.11186623.6.652.312e79bd17U8IO

监控及备份

监控prometheus+grafana

从20.1.2.4开始内置支持prometheus监控,从而自动的将 metrics 、 events 和asynchronous_metrics 三张系统的表的数据发送给 Prometheus

config.xml 配置

    <prometheus>
        <endpoint>/metrics</endpoint>
        <port>9363</port>
        <metrics>true</metrics>
        <events>true</events>
        <asynchronous_metrics>true</asynchronous_metrics>
        <status_info>true</status_info>
    </prometheus>

grafana图表 id：
https://grafana.com/grafana/dashboards/14432
https://grafana.com/grafana/dashboards/13606
https://grafana.com/grafana/dashboards/13500

备份及恢复

https://clickhouse.com/docs/zh/operations/backup/

手动备份：

参考part操作（繁琐）

自动备份工具clickhouse-backup（推荐）

https://github.com/AlexAkulov/clickhouse-backup
支持备份到s3
注：使用前务必充分测试备份与数据恢复，因为CK不同版本之间会有差异

优化

users.xml -> profiles -> default 标签：
	<!-- 单次查询最大使用内存 Maximum memory usage for processing single query, in bytes. -->
	<max_memory_usage>10000000000</max_memory_usage>
	<!-- 当分组操作占用超xx时，缓存到磁盘，建议内存一半-->
	<max_bytes_before_external_group_by>5000000000</max_bytes_before_external_group_by>
	<!-- 当排序操作占用超xx时，缓存到磁盘，建议内存一半-->
	<max_bytes_before_external_sort>5000000000</max_bytes_before_external_sort>
	<!-- 单个用户可用最大内存 -->
	<max_memory_usage_for_user>0</max_memory_usage_for_user>
	<!-- 所有查询可用最大内存 -->
	<max_memory_usage_for_all_queries>0</max_memory_usage_for_all_queries>
 

查询优化参数

参考文章

Clickhouse副本与分片

https://blog.csdn.net/qq_28603127/article/details/109788763