ClickHouse快速入门 2-阿里云开发者社区

3.5 ReplacingMergeTree

ReplacingMergeTree 是 MergeTree 的一个变种，它存储特性完全继承 MergeTree，只是多了一个去重的功能。尽管 MergeTree 可以设置主键，但是 primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个 ReplacingMergeTree。

1）去重时机

数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。

2）去重范围

如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。所以 ReplacingMergeTree 能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现

3）案例演示

（1）创建表

create table t_order_rmt(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2) ,
 create_time Datetime 
) engine =ReplacingMergeTree(create_time)
 partition by toYYYYMMDD(create_time)
 primary key (id)
 order by (id, sku_id);

ReplacingMergeTree() 填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认按照插入顺序保留最后一条。

（2）向表中插入数据

insert into t_order_rmt 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');

（3）执行第一次查询

hadoop102 :) select * from t_order_rmt;

（4）手动合并

OPTIMIZE TABLE t_order_rmt FINAL;

（5）再执行一次查询

hadoop102 :) select * from t_order_rmt;

4）通过测试得到结论

➢ 实际上是使用 order by 字段作为唯一键

➢ 去重不能跨分区

➢ 只有同一批插入（新版本）或合并分区时才会进行去重

➢ 认定重复的数据保留，版本字段值最大的

➢ 如果版本字段相同则按插入顺序保留最后一笔

3.6 SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。

ClickHouse 为了这种场景，提供了一种能够“预聚合”的引擎 SummingMergeTree

1）案例演示

（1）创建表

create table t_order_smt(
 id UInt32,
 sku_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,sku_id );

（2）插入数据

insert into t_order_smt 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');

（3）执行第一次查询

hadoop102 :) select * from t_order_smt;

（4）手动合并

OPTIMIZE TABLE t_order_smt FINAL;

（5）再执行一次查询

hadoop102 :) select * from t_order_smt;

2）通过结果可以得到以下结论

➢ 以 SummingMergeTree（）中指定的列作为汇总数据列

➢ 可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列

➢ 以 order by 的列为准，作为维度列

➢ 其他的列按插入顺序保留第一行

➢ 不在一个分区的数据不会被聚合

➢ 只有在同一批次插入(新版本)或分片合并时才会进行聚合

3）开发建议

设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳。

4）问题

能不能直接执行以下 SQL 得到汇总值

select total_amount from XXX where province_name=’’ and create_date=’xxx’

不行，可能会包含一些还没来得及聚合的临时明细如果要是获取汇总值，还是需要使用 sum 进行聚合，这样效率会有一定的提高，但本身 ClickHouse 是列式存储的，效率提升有限，不会特别明显。

select sum(total_amount) from province_name=’’ and create_date=‘xxx’

4 SQL 操作

基本上来说传统关系型数据库（以 MySQL 为例）的 SQL 语句，ClickHouse 基本都支持，这里不会从头讲解 SQL 语法只介绍 ClickHouse 与标准 SQL（MySQL）不一致的地方。

4.1 Insert

基本与标准 SQL（MySQL）基本一致

（1）标准

insert into [table_name] values(…),(….)

（2）从表到表的插入

insert into [table_name] select a,b,c from [table_name_2]

4.2 Update 和 Delete

ClickHouse 提供了 Delete 和 Update 的能力，这类操作被称为 Mutation 查询，它可以看做 Alter 的一种。

虽然可以实现修改和删除，但是和一般的 OLTP 数据库不一样，Mutation 语句是一种很“重”的操作，而且不支持事务。

“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。

所以尽量做批量的变更，不要进行频繁小数据的操作。

（1）删除操作

alter table t_order_smt delete where sku_id ='sku_001';

（2）修改操作

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id 
=102;

由于操作比较“重”，所以 Mutation 语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成

4.3 查询操作

ClickHouse 基本上与标准 SQL 差别不大

➢ 支持子查询

➢ 支持 CTE(Common Table Expression 公用表表达式 with 子句)

➢ 支持各种 JOIN，但是 JOIN 操作无法使用缓存，所以即使是两次相同的 JOIN 语句，ClickHouse 也会视为两条新 SQL

➢ 窗口函数(官方正在测试中…) ➢ 不支持自定义函数

➢ GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。

（1）插入数据

hadoop102 :) alter table t_order_mt delete where 1=1;
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(101,'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'),
(106,'sku_001',1000.00,'2020-06-04 12:00:00'),
(107,'sku_002',2000.00,'2020-06-04 12:00:00'),
(108,'sku_004',2500.00,'2020-06-04 12:00:00'),
(109,'sku_002',2000.00,'2020-06-04 12:00:00'),
(110,'sku_003',600.00,'2020-06-01 12:00:00');

（2）with rollup：从右至左去掉维度进行小计

hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with rollup;

（3）with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计

hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube;

（4）with totals: 只计算合计

hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with totals;

4.4 alter 操作

同 MySQL 的修改字段基本一致

1）新增字段

alter table tableName add column newcolname String after col1;

2）修改字段类型

alter table tableName modify column newcolname String;

3）删除字段

alter table tableName drop column newcolname;

4.5 导出数据

clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames> /opt/module/data/rs1.csv

5 第三方开发的可视化界面

5.1 开源

5.1.1 Tabix

ClickHouse Web 界面 Tabix.

主要功能：

浏览器直接连接 ClickHouse，不需要安装其他软件。

高亮语法的编辑器。

自动命令补全。

查询命令执行的图形分析工具。

配色方案选项。

Tabix 文档.

5.1.2 HouseOps

HouseOps 是一个交互式 UI/IDE 工具，可以运行在 OSX, Linux and Windows 平台中。

主要功能：

查询高亮语法提示，可以以表格或 JSON 格式查看数据。

支持导出 CSV 或 JSON 格式数据。

支持查看查询执行的详情，支持 KILL 查询。

图形化显示，支持显示数据库中所有的表和列的详细信息。

快速查看列占用的空间。

服务配置。

以下功能正在计划开发：

- 数据库管理

- 用户管理

- 实时数据分析

- 集群监控

- 集群管理

- 监控副本情况以及 Kafka 引擎表

5.1.3 灯塔

灯塔是ClickHouse的轻量级Web界面。

特征：

包含过滤和元数据的表列表。

带有过滤和排序的表格预览。

只读查询执行。

5.1.4 DBeaver

DBeaver 具有ClickHouse支持的通用桌面数据库客户端。

特征：

使用语法高亮显示查询开发。

表格预览。

自动完成。

clickhouse-cli

clickhouse-cli 是ClickHouse的替代命令行客户端，用Python 3编写。

特征：

自动完成。

查询和数据输出的语法高亮显示。

寻呼机支持数据输出。

自定义PostgreSQL类命令。

5.1.5 clickhouse-flamegraph

[clickhouse-flamegraph](https://github.com/Slach/clickhouse-flamegraph) 是一个可视化的专业工具`system.trace_log`如[flamegraph](http://www.brendangregg.com/flamegraphs.html).

5.1.6 DBM

DBM DBM是一款ClickHouse可视化管理工具!

特征：

支持查询历史（分页、全部清除等）

支持选中的sql子句查询(多窗口等)

支持终止查询

支持表管理

支持数据库管理

支持自定义查询

支持多数据源管理（连接测试、监控）

支持监控（处理进程、连接、查询）

支持迁移数据

5.2 商业

Holistics

Holistics 在2019年被Gartner FrontRunners列为可用性最高排名第二的商业智能工具之一。 Holistics是一个基于SQL的全栈数据平台和商业智能工具，用于设置您的分析流程。

特征：

-自动化的电子邮件，Slack和Google表格报告时间表。

-强大的SQL编辑器，具有版本控制，自动完成，可重用的查询组件和动态过滤器。

-通过iframe在自己的网站或页面中嵌入仪表板。

-数据准备和ETL功能。

-SQL数据建模支持数据的关系映射。

5.2.1 DataGrip

DataGrip 是JetBrains的数据库IDE，专门支持ClickHouse。它还嵌入到其他基于IntelliJ的工具中：PyCharm，IntelliJ IDEA，GoLand，PhpStorm等。

特征：

非常快速的代码完成。

ClickHouse语法高亮显示。

支持ClickHouse特有的功能，例如嵌套列，表引擎。

数据编辑器。

重构。

搜索和导航。

ClickHouse快速入门 2