背景
业务需求需要对每次浏览都进行记录.对店铺、商品等多维度访问情况（pv,uv）的记录统计，mysql已不足以支撑该类型数据存储，在线统计分析查询，经过调研分析后，采用了clickhouse进行数据统计的存储，查询。
clickhouse介绍
ClickHouse是俄罗斯的Yandex于2016年开源的一个用于联机分析(OLAP:Online Analytical Processing)的列式数据库管理系统(DBMS:Database Management System) , 主要用于在线分析处理查询，能够使用SQL查询实时生成分析数据报告。
ClickHouse是一个完全的列式数据库管理系统，支持线性扩展，简单方便，高可靠性，容错。它它的系统在生产环境中可以应用到比较大的规模，因为它的线性扩展能力和可靠性保障能够原生支持 shard + replication （分片+副本）这种解决方案
Clickhouse全称是 Click Stream, Data WareHouse, 简称 ClickHouse
Yandex
ClickHouse 背后的研发团队是来自俄罗斯的 Yandex 公司 这是家俄罗斯本土的互联网企业，于 2011 年在纳斯达克上市，它的核心产品是搜索引擎。 根据最新(2020年)的数据显示，Yandex 占据了本国 47 ％以上的搜索市场，是现今世界上最大的俄语搜索引擎。 Google是它的直接竞争对手
OLTP、OLAP
OLTP on-line transaction processing（联机事务处理） 是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理。强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作；
OLAP On-Line Analytical Processing （联机分析处理） 是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。
常用场景
- 用户行为分析：在采集用户行为日志之后，进行 PV、UV、留存、转化漏斗等操作，例如头条、快手、喜马拉雅等。
- 用户画像圈选：每个公司都拥有大量的用户和用户画像标签，如何快速从用户画像标签里圈选出某几类标签的人群，例如阿里、喜马拉雅等。
- 机器日志监控 &查询：每台机器都产生大量日志，如何快速监控、查询机器日志，以确保整体服务没有问题。基本上所有的互联网公司都在这样使用 ClickHouse 的。
数据库表引擎
MergeTree
-
适用于高负载,最强大的表引擎
-
支持快速大量(百万行级)插入数据,应用规则在后台合并这些部分
-
支持数据复制、分区、辅助数据跳过索引以及其他引擎不支持的其他功能
种类
- MergeTree
- ReplacingMergeTree
- SummingMergeTree
- AggregatingMergeTree
- CollapsingMergeTree
- VersionedCollapsingMergeTree
- GraphiteMergeTree
Log
-
轻量级引擎，功能最少。当您需要快速编写许多小表（最多约100万行）并在以后作为一个整体读取它们时，它们是最有效的。
-
种类
- TinyLog
- StripeLog
- Log
-
Integration Engines
与其他数据存储和处理系统通信的引擎
-
种类
- Kafka
- MySQL
- ODBC
- JDBC
- HDFS
- S3
Special Engines（特殊引擎，不知道如何分类，ClickHouse特有的）
- Distributed
- MaterializedView
- Dictionary
- Merge
- File
- Null
- Set
- Join
- URL
- View
- Memory
- Buffer
表引擎可以说是clickhouse的核心，每种引擎都有各自的实现及使用场景，MergeTree家族（合并树）又可以说是表引擎的核心，除了少数场景外，选型到的表引擎无一例外都是用的MergeTree，部分clickhouse核心功能也仅在使用MergeTree时才能实现。
MergeTree建表说明
CREATE TABLE shop_view_log_suppday
(
`user_type` UInt8 DEFAULT 0 COMMENT '用户类型 0 游客 1类型A，2类型B',
`supp_id` UInt32 DEFAULT 0 COMMENT '供应商id',
`supp_name` String DEFAULT '' COMMENT '供应商名称',
`pv` UInt64 DEFAULT 0 COMMENT 'pv',
`uv` AggregateFunction(uniq, String) COMMENT 'uv',
`terminal_type` UInt32 DEFAULT 0 COMMENT '终端类型 1 网页端 2 手机端',
`create_time` Date DEFAULT now() COMMENT '创建时间'
)
ENGINE = SummingMergeTree((pv,uv))
PARTITION BY toYYYYMM(create_time)
ORDER BY (supp_id,create_time,terminal_type,user_type,source)
PRIMARY KEY (可选)
默认情况下，主键与排序键（由ORDER BY子句指定）相同。因此，在大多数情况下，没有必要指定单独的PRIMARY KEY子句。
ORDER BY
列名或任意表达式的元组。示例：ORDER BY (CounterID, EventDate)。
如果子句未显式定义主键，则 ClickHouse 将排序键用作主键PRIMARY KEY。
PARTITION BY(Merge阶段实现,可选)
在大多数情况下，您不需要分区键，而在大多数其他情况下，您不需要比几个月更细的分区键。分区不会加速查询（与 ORDER BY 表达式相反）。你永远不应该使用太细化的分区。不要按客户端标识符或名称对数据进行分区（而是将客户端标识符或名称作为 ORDER BY 表达式中的第一列）。
对于按月分区，请使用toYYYYMM(date_column)表达式，其中date_column是日期类型为Date的列。此处的分区名称具有"YYYYMM"格式。
稀疏索引（使用主键为标记）：
密集索引：文件中的每个搜索码值都对应一个索引值，就是叶子节点保存了整行，比如InnoDB
稀疏索引：文件只为索引码的某些值建立索引项，比如MyISAM，kafka
由于clickhouse为面对大数据量统计而生，所以在此使用的也为稀疏索引。默认索引区为8192（可调）
故可以理解，使用索引的前提，一定是数据存储有序的，所以在建表语句中，order by 为必选，primary key 为可选，当未选择primary key时，默认使用order by 键值作为主键。
数据的写入及整理
数据写入阶段
每一次insert语句的写入，clickhouse都会在临时分区文件夹生成文件，存储写入的数据，等到后台进程到了执行Merge任务的时间，才会对该批数据进行数据排序等操作。数据写入不建议频繁或者少量写入。
原因：
Merge阶段执行时间较长，花费资源多，如果频繁写入，未合并的临时文件过多，clickhouse又按照文件夹进行合并，同样时间的合并速度已经低于写入速度，长期下来分区功能近乎于没有，clickhouse考虑到此问题，对当前表存在的临时文件量（part）进行了上限限制报错，所以不支持少量及频繁写入。
CREATE TABLE partition_directory_merge
(
partition_key Int32,
orderBy_key String,
data String
)
ENGINE = MergeTree()
PARTITION BY partition_key
ORDER BY orderBy_key
insert into partition_directory_merge values(1, '001', 'Value');
insert into partition_directory_merge values(1, '002', 'Value2');
insert into partition_directory_merge values(1, '003', 'Value3');
insert into partition_directory_merge values(2, '004', 'Value3');
insert into partition_directory_merge values(2, '005', 'Value3')
,(3, '006', 'Value3');
Merge阶段
MergeTree起名由来便因其核心独特功能在Merge阶段进行实现。clickhouse程序启动后，会启动一个后台定时任务进行分区合并(一般为10-15分钟)，排序，Merge过程可能涉及到大量数据处理，所以是一件十分消耗资源的事情。
执行强制合并：
数据的删除与更新
ALTER TABLE [db_name.]table_name DELETE WHERE filter _expr
ALTER TABLE [db_name.]table_name UPDATE columnl = exprl [, . .. ] WHERE filter _expr
Clickhouse将数据的删除、更新定义为重的DDL语言。执行后会开启异步任务在后台执行。对涉及表、列创建新目录进行复制，过滤掉筛选条件，待复制完成后，则使用新目录供给查询，旧目录将会在下一次Merge阶段进行删除。
MergeTree家族
在MergeTree的基础（稀疏索引，分区，合并）上，在合并阶段增加额外的处理流程，便衍生出了MergeTree家族
ReplacingMergeTree（去重树）
在MergeTree的基础，增加合并阶段时，对同一分区内，相同排序键（order by字段），进行数据去重
create table tb_replace_tree4(
uid UInt8,
name String,
city String,
version UInt8
)engine=ReplacingMergeTree(version)
partition by city
order by uid;
insert into tb_replace_tree4 values
(1,'zs1','BJ',1),
(1,'zs2','BJ',2),
(1,'ls1','SH',2),
(1,'ls2','SH',1);
select * from tb_replace_tree4; //都保留了版本大的数据
┌─uid─┬─name─┬─city─┬─version─┐
│ 1 │ zs2 │ BJ │ 2 │
└─────┴──────┴──────┴─────────┘
┌─uid─┬─name─┬─city─┬─version─┐
│ 1 │ ls1 │ SH │ 2 │
└─────┴──────┴──────┴─────────┘
insert into tb_replace_tree4 values(1,'zs3','BJ',3);
insert into tb_replace_tree4 values(1,'ls3','SH',1);
optimize table tb_replace_tree4 final;
select * from tb_replace_tree4;
┌─uid─┬─name─┬─city─┬─version─┐
│ 1 │ zs3 │ BJ │ 3 │
└─────┴──────┴──────┴─────────┘
┌─uid─┬─name─┬─city─┬─version─┐
│ 1 │ ls1 │ SH │ 2 │
└─────┴──────┴──────┴─────────┘
SummingMergeTree（求和树）
各种在线分析功能，往往针对一类源数据，去进行多种维度统计查询。如公司里，按供应商、单位、时间等。SummingMergeTree,AggregatingMereTree两种表引擎,在Merge阶段，进行了数据预聚合处理，SummingMergeTree对同一分区内，相同排序键的数字类型数据（order by字段），进行数据累加，最后将聚合结果进行持久保存，达到同一分区内存储数据为Sum的结果。能够减少聚合查询所需数据，存储的目的。
create table order_table5
(
id UInt32,
item_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,item_id );
如果设置primary key ，需要是排序键（order by）的前缀字段
insert into order_table5
values (101, 's_001', 1000.00, '2020-06-01 12:00:00'),
(102, 's_002', 2000.00, '2020-06-01 11:00:00'),
(102, 's_004', 2500.00, '2020-06-01 12:00:00'),
(102, 's_002', 2000.00, '2020-06-01 13:00:00'),
(102, 's_002', 12000.00, '2020-06-01 13:00:00'),
(102, 's_002', 600.00, '2020-06-02 12:00:00');
可以看出，未到达合并阶段时，数据并不会被聚合处理。
强制执行合并
OPTIMIZE TABLE order_table5;
总结：
以 SummingMergeTree（）中指定的列作为汇总数据列
◼ 可以填写多列必须数字列，如果不填，默认以所有非维度列且为数字列的字段为汇总数据列
◼ 以 order by 的列为准，作为维度列进行Sum操作
◼ 其他的列按插入顺序保留第一行
◼ 不在一个分区的数据不会被聚合
实际查询语句：
select id,sum(total_amount) total_amount from order_table5
where id = 102
AggregatingMereTree（聚合树）
与SummingMergeTree类似，都是在Merge阶段对数据进行聚合处理，差别是该引擎能支持更多类型的聚合，不止数据累加和。
CREATE TABLE emp_aggregatingmergeTree
(
company_id UInt16 COMMENT '公司id',
company_name String COMMENT '公司名称',
dept_id UInt16 COMMENT '部门id',
depart String COMMENT '部门',
work_place String COMMENT '所在省份',
salary AggregateFunction(sum, Decimal32(2)) COMMENT '营业额',
max_salary AggregateFunction(max, Decimal32(2)) COMMENT '最大营业额'
) ENGINE = AggregatingMergeTree()
ORDER BY (company_id, dept_id)
PRIMARY KEY company_id
PARTITION BY work_place;
INSERT INTO TABLE emp_aggregatingmergeTree
SELECT 1,'测1','1','部门1','上海',sumState(toDecimal32(10000,2)
,maxtate(toDecimal32(10000,2));
INSERT INTO TABLE emp_aggregatingmergeTree
SELECT 1,'测1','1','部门1','上海',sumState(toDecimal32(20000,2))
,maxtate(toDecimal32(20000,2));
INSERT INTO TABLE emp_aggregatingmergeTree
SELECT 2,'测2','2','部门2','广州',sumState(toDecimal32(20000,2)),
,maxtate(toDecimal32(20000,2));
-- 查询数据
SELECT company_id,company_name,sumMerge(salary) maxMerge(max_salary) max
FROM emp_aggregatingmergeTree
GROUP BY emp_id,name;
-- 结果输出
┌─emp_id─┬─name─┬─sumMerge(salary)─┐┬─max ────────────┐
│ 1 │ 测1 │ 30000.00 │ 20000.00 │
└────────┴──────┴──────────────────┘──────────────────┘
│ 2 │ 测2 │ 20000.00 │ 20000.00 │
└────────┴──────┴──────────────────┘──────────────────┘
AggregateFunction
(聚合函数 特殊的数据类型)
示例：uniq函数（去重作用）
CREATE TABLE shop_view_log_supporgday
(
.....
`uv` AggregateFunction(uniq,String) COMMENT 'uv',
....
)
对数据源表(shop_view_log)的guid列进行去重得到uv
insert into shop_view_log_supporgday
select uniqState(guid) uv from shop_view_log
group by ....
select uniqMerge(uv) uv from shop_view_log_supporgday ......
AggregateFunction(uniq,String) 即对传进来的数据执行uniq函数，结果以二进制形式进行保存。uniq计算数据哈希值进行去重保存为字符串。
对AggregateFunction类型数据插入时，调用对应State方法获取函数中间状态进行保存，如uniqState，对uniq类型数据聚合查询时，调用对应Merge方法,此处为uniqMerge，对函数中间状态进行处理去重后计算总数，
VersionedCollapsingMergeTree
（版本折叠树-逻辑更新、删除数据）
该引擎在合并阶段，对排序键（order by）相等的且sign字段为-1，1的两个数据为一组进行删除。
折叠名字来源：
标记，版本的设置：
CREATE TABLE test
(
...
sign Int8,
version Int8
) ENGINE = VersionedCollapsingMergeTree(sign, version)
order by ...
sign用于标记数据是否被删除，-1已删除，1未删除，硬性规定，否则不利于当前引擎的数据查询。
version用于显示当前版本，新数据版本号需要比之前的数据都大（删除除外），折叠版本树会在所有查询语句后增加order by version desc(如原语句有order by 则补在排序尾)字段的逻辑，使得查询到的数据必然是最新一条。
也可看出，数据的删除，版本字段依赖于类似binlog等数据提供新旧数据，版本。
建表语句：
CREATE TABLE emp_versioned
(
emp_id UInt16 COMMENT '员工id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
sign Int8,
version Int8
) ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (emp_id, name)
PARTITION BY work_place;
-- 先插入需要被删除的数据，即sign=-1的数据
INSERT INTO emp_versioned VALUES (1,'tom','上海',25,'销售部',-1,1);
-- 再插入sign=1的数据
INSERT INTO emp_versioned VALUES (1,'tom','上海',25,'销售部',1,1);
-- 在插入一个新版本数据
INSERT INTO emp_versioned VALUES (1,'tom','上海',25,'销售部',1,2);
-- 先不执行合并，查看表数据
select * from emp_versioned;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬─sign─┬─version─┐
│ 1 │ tom │ 上海 │ 25 │ 销售部 │ 1 │ 1 │
└────────┴──────┴────────────┴─────┴────────┴──────┴─────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬─sign─┬─version─┐
│ 1 │ tom │ 上海 │ 25 │ 销售部 │ -1 │ 1 │
└────────┴──────┴────────────┴─────┴────────┴───── ┴─────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬─sign─┬─version─┐
│ 1 │ tom │ 上海 │ 26 │ 技术部 │ 1 │ 2 │
└────────┴──────┴────────────┴─────┴────────┴──────┴─────────┘
-- 获取正确查询结果
SELECT emp_id,name,sum(age * sign) FROM emp_versioned
GROUP BY emp_id,name
HAVING sum(sign) > 0;
┌─emp_id─┬─name─┬─sum(multiply(age, sign))─┐
│ 1 │ tom │ 26 │
└────────┴──────┴──────────────────────────┘
-- 手动合并
optimize table emp_versioned;
-- 再次查询
select * from emp_versioned;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart──┬─sign─┬─version─┐
│ 1 │ tom │ 上海 │ 26 │ 技术部 │ 1 │ 2 │
└────────┴──────┴────────────┴─────┴──────── ┴───────┴─────────┘
可见上面虽然在插入数据乱序的情况下，依然能够实现折叠的效果。之所以能够达到这种效果，是因为在定义version字段之后，VersionedCollapsingMergeTree会自动将version作为排序条件并增加到ORDER BY的末端，就上述的例子而言，最终的排序字段为ORDER BY emp_id,name，version desc。
物化视图：
上述说明的两种聚合场景使用引擎SummingMergeTree，AggregatingMergeTree的确可以通过预聚合减轻统计查询压力，每张预聚合表都能统计一个至多个维度的数据，但如果统计维度变多之后，增加一张变要多人为写一次插入语句进行数据存储，使流程变得繁琐，且源数据表与聚合表可能存在数据统计后结果极大不一致的隐患。clickhouse所提供的物化视图便能解决该问题。
物化视图能够在源表插入数据后，触发同步操作，将增量数据同步至物化视图对应表中进行固化存储。不过由于视图表未对历史数据进行存储，仅存储聚合后结果，故源表删除，更新数据操作并不支持同步至物化视图表中。
本次统计实践方案-聚合树+物化视图
需求数据源表：
1.创建聚合表，用于存储不同维度预聚合数据：
2.创建物化视图，指定统计后数据存储于预聚合表中，创建后当数据源表（shop_view_log）在之后的每次数据增量时，都会将新生成数据传给视图进行统计。
3.初始化视图实际表，将视图创建之前的数据进行统计存入视图实际表中
<<clickhouse原理解析与应用实践>>等
