持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第18天，点击查看活动详情

Logistics_Day15：ClickHouse 存储引擎

1614160990289

01-[复习]-上次课程内容回顾

主要讲解ClickHouse数据库（属于实时OLAP分析数据库），基本入门使用。

1）、OLAP分析需求以及技术选项对比

Hive（MapReduce、Tez、Spark）、Presto、Impala、SparkSQL

Kylin（Hive，预计算，存储HBase）、Druid、ClickHouse

2）、物流项目使用ClickHouse流程

数据实时ETL存储CK，编写结构化流程序

当业务数据实时同步到CK表中，如何被使用

1614386666928

3）、ClickHouse 基础入门

ClickHouse框架概述：官网定义、官方文档

ClickHouse应用场景、使用案例和优缺点

ClickHouse 单机安装、配置和服务启动：采用yum在线安装，或者rpm离线安装

配置文件：/etc/clickhouse-server-> config.xml 和 users.xml

端口号：HTTP服务【8123】，TCP服务【9000，修改为9999】

启动服务和命令行clickhouse-client
[root@node2 ~]# systemctl start clickhouse-server
[root@node2 ~]# 
[root@node2 ~]# ps -ef|grep clickhouse-server
clickho+  2891     1 16 08:47 ?        00:00:01 /usr/bin/clickhouse-server --config=/etc/clickhouse-server/config.xml --pid-file=/run/clickhouse-server/clickhouse-server.pid

[root@node2 ~]# clickhouse-client -m --host node2.itcast.cn --port 9999 --user root --password 123456
ClickHouse client version 20.4.2.9 (official build).
Connecting to node2.itcast.cn:9999 as user root.
Connected to ClickHouse server version 20.4.2 revision 54434.

node2.itcast.cn :) 
官方案例数据分析：航班飞行数据
4）、ClickHouse SQL 语法
常用SQL语句，创建表、删除表、创建视图等语句，类似MySQL数据库

插入数据INSERT

查询数据SELECT，某个字段类型为Array数组时，使用ARRAY JOIN进行行转列

ALTER 语法，针对表的列进行添加、删除和修改，此外使用UPDATE和DELETE
更新和删除
ALTER TABLE db.table UPDATE SET col1=value1, col2=value2, ... WHERE id = ?

ALTER TABLE db.table DELETE WHERE id = ?
基本函数
5）、ClickHouse 数据类型

数值类型、字符串、日期时间、数组和元组及其他数据类型

02-[了解]-第15天：课程内容提纲

主要讲解2个部分内容：ClickHouse数据类型和ClickHouse表引擎（重点【合并树系列引擎】）

ClickHouse 表引擎：MergeTree 系列引擎

MergeTree引擎

ReplicacingMerge引擎，可以替换数据，当主键重复时

SummingMergeTree引擎：求和引擎，聚合统计数据存储和AggregatingMergeTree聚合引擎

折叠树引擎：变通方式，实现数据更新和删除操作，扩展：多版本折叠树引擎，允许多线程插入数据

03-[了解]-ClickHouse 引擎之引擎概述

ClickHouse提供了多种不同的表引擎，表引擎可以简单理解为不同类型的表。表引擎（即表的类型）决定了：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B5VQFSQv-1641169977011)(/img/1616662005733-1622626089308.png)]

文档：clickhouse.tech/docs/zh/eng…

1）、MergeTree引擎（合并树及系列引擎）

适用于高负载任务的最通用和功能最强大的表引擎。

2）、日志Log引擎

最小功能的轻量级引擎

3）、集成引擎，使用其他存储引擎，存储表的数据

用于与其他的数据存储与处理系统集成的引擎。

4）、用于其他特定功能的引擎

04-[了解]-ClickHouse 引擎之TinyLog 引擎

TinyLog 引擎，最简单的表引擎，用于将数据存储在磁盘上。每列都存储在单独的压缩文件中，写入时，数据将附加到文件末尾。 == 该引擎没有并发控制 == 。

1616662365509

node2.itcast.cn :) create table user (id UInt16, name String) ENGINE=TinyLog;

CREATE TABLE user
(
    `id` UInt16, 
    `name` String
)
ENGINE = TinyLog

Ok.

0 rows in set. Elapsed: 0.009 sec. 

node2.itcast.cn :) insert into user (id, name) values (1, 'zhangsan');

INSERT INTO user (id, name) VALUES

Ok.

1 rows in set. Elapsed: 0.002 sec. 

node2.itcast.cn :) select * from user ;

SELECT *
FROM user

┌─id─┬─name─────┐
│  1 │ zhangsan │
└────┴──────────┘

1 rows in set. Elapsed: 0.002 sec.

查看上述创建表user，底层如何存储文件。

1616662585910

05-[了解]-ClickHouse 引擎之MySQL 引擎

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。

1614330767215

此种方式，CK没有存储表的数据，MySQL才是真正存储数据地方。

==MySQL 数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。但无法对其执行以下操作： RENAME、CREATE TABLE、 ALTER。==

1616664029323

案例演示：

1）、第一步、在MySQL数据库创建database和table

[root@node1 ~]# docker start mysql
mysql
[root@node1 ~]# docker exec -it mysql /bin/bash
root@8b5cd2152ed9:/# 
root@8b5cd2152ed9:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| canal_tsdb         |
| crm                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.17 sec)

mysql> create database db_test ;
Query OK, 1 row affected (0.00 sec)

mysql> use db_test ;
Database changed
mysql> 
mysql> CREATE TABLE `mysql_table` (
    -> `int_id` INT NOT NULL AUTO_INCREMENT,
    -> `float` FLOAT NOT NULL,
    -> PRIMARY KEY (`int_id`)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mysql_table (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0.21 sec)

mysql> select * from mysql_table ;
+--------+-------+
| int_id | float |
+--------+-------+
|      1 |     2 |
+--------+-------+
1 row in set (0.00 sec)

mysql>

2）、第二步、在CK中创建数据库，关联到MySQL数据库

node2.itcast.cn :) CREATE DATABASE mysql_db ENGINE = MySQL('node1.itcast.cn:3306', 'db_test', 'root', '123456');

CREATE DATABASE mysql_db
ENGINE = MySQL('node1.itcast.cn:3306', 'db_test', 'root', '123456')

Ok.

0 rows in set. Elapsed: 0.041 sec. 

node2.itcast.cn :) SHOW DATABASES;

SHOW DATABASES

┌─name───────────────────────────┐
│ _temporary_and_external_tables │
│ db_ck                          │
│ default                        │
│ logistics                      │
│ mysql_db                       │
│ system                         │
└────────────────────────────────┘

6 rows in set. Elapsed: 0.002 sec.

3）、第三步、在CK中操作，查询数据

node2.itcast.cn :) use mysql_db ;

USE mysql_db

Ok.

0 rows in set. Elapsed: 0.001 sec. 

node2.itcast.cn :) show tables ;

SHOW TABLES

┌─name────────┐
│ mysql_table │
└─────────────┘

1 rows in set. Elapsed: 0.008 sec. 

node2.itcast.cn :) select * from mysql_table ;

SELECT *
FROM mysql_table

┌─int_id─┬─float─┐
│      1 │     2 │
└────────┴───────┘

1 rows in set. Elapsed: 0.025 sec.

4）、当测试完成以后，将CK中数据库：mysql_db删除

node2.itcast.cn :) drop database mysql_db ;

DROP DATABASE mysql_db

Ok.

0 rows in set. Elapsed: 0.006 sec.

如果没有删除此数据库，当仅仅启动node2虚拟机，启动CK服务时，启动会报异常，原因在于连接不到node1容器中MySQL数据库。

06-[掌握]-CK 引擎之MergeTree 引擎

MergeTree（合并树）系列引擎是ClickHouse中最强大的表引擎，是官方主推的存储引擎，几乎支持ClickHouse所有的核心功能。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JbNpqS17-1641169977013)(/img/1616662758483-1622626089309.png)]

合并树系列引擎：具有批量数据快速插入和后台并发处理的优势

1616662858638

MergeTree引擎的表的允许插入主键重复的数据，主键主要作用是生成主键索引来提升查询效率，而不是用来保持记录主键唯一。

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],
...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

1616662962840

如果CK表中存储引擎：MergeTree引擎，创建表时，如果没有设置主键PRIMARY KEY，那么ORDER BY字段设置的话，就是主键字段；如果ORDER BY和PRIMARY KEY都没有设置的话，按照数据自然顺序排序。

==创建使用MergeTree引擎的本地表： test.tbl_test_mergetree_users==

CREATE TABLE tbl_test_mergetree_users
(
    `id` UInt64, 
    `email` String, 
    `username` String, 
    `gender` UInt8, 
    `birthday` DATE, 
    `mobile` FixedString(13), 
    `pwd` String, 
    `regDT` DateTime, 
    `lastLoginDT` DateTime, 
    `lastLoginIP` String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(regDT)
ORDER BY id
SETTINGS index_granularity = 8192 ;

==插入数据到表中：==

insert into tbl_test_mergetree_users(id, email, username, gender, birthday, mobile, pwd, regDT, lastLoginDT, lastLoginIP) values (1,'wcfr817e@yeah.net','督咏',2,'1992-05-31','13306834911','7f930f90eb6604e837db06908cc95149','2008-08-06 11:48:12','2015-05-08 10:51:41','106.83.54.165'),(2,'xuwcbev9y@ask.com','上磊',1,'1983-10-11','15302753472','7f930f90eb6604e837db06908cc95149','2008-08-10 05:37:32','2014-07-28 23:43:04','121.77.119.233'),(3,'mgaqfew@126.com','涂康',1,'1970-11-22','15200570030','96802a851b4a7295fb09122b9aa79c18','2008-08-10 11:37:55','2014-07-22 23:45:47','171.12.206.122'),(4,'b7zthcdg@163.net','金俊振',1,'2002-02-10','15207308903','96802a851b4a7295fb09122b9aa79c18','2008-08-10 14:47:09','2013-12-26 15:55:02','61.235.143.92'),(5,'ezrvy0p@163.net','阴福',1,'1987-09-01','13005861359','96802a851b4a7295fb09122b9aa79c18','2008-08-12 21:58:11','2013-12-26 15:52:33','182.81.200.32'),(6,'juestiua@263.net','岑山裕',1,'1996-02-12','13008315314','96802a851b4a7295fb09122b9aa79c18','2008-08-14 05:48:16','2013-12-26 15:49:12','36.59.3.248'),(7,'oyyrzd@yahoo.com.cn','姚茗咏',2,'1977-02-06','13303203846','96e79218965eb72c92a549dd5a330112','2008-08-15 10:07:31','2013-12-26 15:55:05','106.91.23.177'),(8,'lhrwkwwf@163.com','巫红影',2,'1996-02-21','15107523748','96802a851b4a7295fb09122b9aa79c18','2008-08-15 14:37:41','2013-12-26 15:55:05','123.234.85.27'),(9,'v2zqak8kh@0355.net','空进',1,'1974-01-16','13903178080','96802a851b4a7295fb09122b9aa79c18','2008-08-16 03:24:44','2013-12-26 15:52:42','121.77.192.123'),(10,'mqqqmf@yahoo.com','西香',2,'1980-10-13','13108330898','96802a851b4a7295fb09122b9aa79c18','2008-08-16 04:42:08','2013-12-26 15:55:08','36.57.21.234'),(11,'sf8oubu@yahoo.com.cn','壤晶媛',2,'1976-03-05','15202615557','96802a851b4a7295fb09122b9aa79c18','2008-08-16 06:08:51','2013-12-26 15:55:03','182.83.220.201'),(12,'k6k21ce@qq.com','东平',1,'2005-04-25','13603648382','96802a851b4a7295fb09122b9aa79c18','2008-08-16 06:18:20','2013-12-26 15:55:05','210.34.111.155'),(13,'zguxgg@qq.com','夹影悦',2,'2002-08-23','15300056290','96802a851b4a7295fb09122b9aa79c18','2008-08-16 06:57:45','2013-12-26 15:55:02','61.232.211.180'),(14,'g2jqhbzrf@aol.com','生晓怡',2,'1974-06-22','13507515420','96802a851b4a7295fb09122b9aa79c18','2008-08-16 08:23:43','2013-12-26 15:55:02','182.86.5.162'),(15,'1evn3spn@126.com','咎梦',2,'1998-04-14','15204567060','060ed80051e6384b77ddfaa26191778b','2008-08-16 08:29:57','2013-12-26 15:55:02','210.30.171.70'),(16,'tqndz6l@googlemail.com','司韵',2,'1992-08-28','15202706709','96802a851b4a7295fb09122b9aa79c18','2008-08-16 14:34:25','2013-12-26 15:55:03','171.10.115.59'),(17,'3472gs22x@live.com','李言',1,'1997-09-08','15701526600','96802a851b4a7295fb09122b9aa79c18','2008-08-16 15:04:07','2013-12-26 15:52:39','171.14.80.71'),(18,'p385ii@gmail.com','詹芸',2,'2004-11-05','15001974846','96802a851b4a7295fb09122b9aa79c18','2008-08-16 15:26:06','2013-12-26 15:55:02','182.89.147.245'),(19,'mfbncfu@yahoo.com','蒙芬霞',2,'1990-09-10','15505788156','96802a851b4a7295fb09122b9aa79c18','2008-08-16 15:30:58','2013-12-26 15:55:05','182.91.15.89'),(20,'l24ffbn@ask.com','后冠',1,'2000-09-02','15608241150','96802a851b4a7295fb09122b9aa79c18','2008-08-17 01:15:55','2014-08-29 00:51:12','36.58.7.85'),(21,'m26lggpe@qq.com','宋美月',2,'2003-01-13','15606561425','96802a851b4a7295fb09122b9aa79c18','2008-08-17 01:24:09','2013-12-26 15:52:36','123.235.233.160'),(22,'ndmfm13qf@0355.net','邬玲',2,'2002-08-11','13207844859','96802a851b4a7295fb09122b9aa79c18','2008-08-17 03:31:11','2013-12-26 15:55:03','36.60.8.4'),(23,'5shmvnd@sina.com','乐心有',1,'1998-05-01','13201212693','96802a851b4a7295fb09122b9aa79c18','2008-08-17 03:33:41','2013-12-26 15:55:02','123.234.184.210'),(24,'pwa0hu@3721.net','任学诚',1,'1978-03-19','15802040152','7f930f90eb6604e837db06908cc95149','2008-08-17 07:24:01','2013-12-26 15:52:34','210.43.167.14'),(25,'1ybjhul@googlemail.com','巫纨',2,'1995-01-20','15900530105','96802a851b4a7295fb09122b9aa79c18','2008-08-17 07:48:06','2013-12-26 15:55:02','222.55.139.104'),(26,'b31me2i8b@yeah.net','石娅',2,'2000-02-25','13908735198','96802a851b4a7295fb09122b9aa79c18','2008-08-17 08:17:24','2013-12-26 15:52:45','123.235.99.123'),(27,'qgb2w4n7@163.net','柏菁',2,'1975-02-09','15306552661','96802a851b4a7295fb09122b9aa79c18','2008-08-17 08:47:39','2013-12-26 15:55:02','121.77.245.202'),(28,'cfb3ck@sohu.com','鲜梦',2,'1974-01-26','13801751668','96802a851b4a7295fb09122b9aa79c18','2008-08-17 08:55:47','2013-12-26 15:55:02','210.26.163.24'),(29,'nfrf6mp@msn.com','鄂珍',2,'1974-04-14','13300247433','96802a851b4a7295fb09122b9aa79c18','2008-08-17 09:02:14','2013-12-26 15:55:08','210.31.214.157'),(30,'o1isumh@126.com',' 法姬',2,'1978-06-16','15607848127','96802a851b4a7295fb09122b9aa79c18','2008-08-17 09:09:59','2013-12-26 15:55:08','222.24.34.19'),(31,'y2wrclkq@msn.com','太以',1,'1998-09-07','13608585923','96802a851b4a7295fb09122b9aa79c18','2008-08-17 11:35:39','2013-12-26 15:52:35','182.89.218.177'),(32,'fv9avnuo@263.net','庚姣欣',2,'1982-09-14','13004625187','96802a851b4a7295fb09122b9aa79c18','2008-08-17 12:50:36','2013-12-26 15:55:02','106.82.225.130'),(33,'o1e96z@yahoo.com','微伟',1,'1981-07-30','13707663880','96802a851b4a7295fb09122b9aa79c18','2008-08-17 15:12:05','2013-12-26 15:49:12','171.13.152.247'),(34,'cm3oz64ja@msn.com','那竹娜',2,'1989-01-09','13607294767','96802a851b4a7295fb09122b9aa79c18','2008-08-17 15:51:08','2013-12-26 15:55:02','171.13.110.67'),(35,'g7impl@msn.com','闾和栋',1,'1994-10-12','13907368366','96802a851b4a7295fb09122b9aa79c18','2008-08-17 16:51:02','2013-12-26 15:55:01','210.28.163.83'),(36,'jz2fjtt@163.com','夏佳悦',2,'2001-03-17','15102554998','7af1b63f0d1f37c35c1274339c12b6a8','1970-01-01 08:00:00','1970-01-01 08:00:00','222.91.138.221'),(37,'klwrtomws@yahoo.com','南义梁',1,'1981-03-19','15105745902','96802a851b4a7295fb09122b9aa79c18','2008-08-18 01:49:17','2013-12-26 15:55:01','36.62.155.17'),(38,'yhzs1nnlk@3721.net','牧元',1,'2001-06-07','13501780163','96802a851b4a7295fb09122b9aa79c18','2008-08-18 04:24:52','2013-12-26 15:55:01','171.15.184.130'),(39,'hem76ot33@gmail.com','凌伟文',1,'1988-03-04','13201417535','96802a851b4a7295fb09122b9aa79c18','2008-08-18 05:34:52','2013-12-26 15:55:14','61.237.105.3'),(40,'ndp40j@sohu.com','弘枝',2,'2000-09-05','13001236425','96802a851b4a7295fb09122b9aa79c18','2008-08-18 06:23:48','2013-12-26 15:55:01','106.82.172.45'),(41,'zeyacpr@gmail.com','台凡',2,'1998-05-26','15102913418','96802a851b4a7295fb09122b9aa79c18','2008-08-18 06:41:24','2013-12-26 15:55:07','123.233.69.218'),(42,'0ts0wiz@aol.com','任晓红',2,'1984-05-02','13502366778','96802a851b4a7295fb09122b9aa79c18','2008-08-18 06:55:16','2013-12-26 15:55:01','210.26.44.18'),(43,'zi7dhzo@googlemail.com','蔡艺艳',2,'1990-08-07','15603954810','96802a851b4a7295fb09122b9aa79c18','2008-08-18 06:57:30','2013-12-26 15:55:01','171.12.171.179'),(44,'b0yfzilu@hotmail.com','郎诚',1,'1994-05-18','13602127171','96802a851b4a7295fb09122b9aa79c18','2008-08-18 07:02:04','2013-12-26 15:55:02','171.8.22.92'),(45,'er9az5e9s@163.com','台翰',1,'1994-06-22','15900953220','96802a851b4a7295fb09122b9aa79c18','2008-08-18 07:05:08','2013-12-26 15:55:14','222.31.141.156'),(46,'e34jy2@yeah.net','彭筠',2,'1983-08-12','15106915420','96802a851b4a7295fb09122b9aa79c18','2008-08-18 07:09:37','2013-12-26 15:52:34','36.60.51.67'),(47,'1u0zc56h@163.net','包华婉',2,'1998-10-03','13102518450','96802a851b4a7295fb09122b9aa79c18','2008-08-18 07:47:24','2013-12-26 15:55:02','121.76.76.105'),(48,'cs8kyk3@ask.com','淳盛',1,'2002-06-19','13203151569','96802a851b4a7295fb09122b9aa79c18','2008-08-18 08:01:58','2013-12-26 15:55:02','36.60.76.111'),(49,'ibcgi5ll@yahoo.com','车珍枫',2,'1975-07-27','15605361319','96802a851b4a7295fb09122b9aa79c18','2008-08-18 08:12:45','2013-12-26 15:55:01','106.83.110.76'),(50,'gzxcx6vz@live.com','应冰红',2,'2004-04-19','15104154370','96802a851b4a7295fb09122b9aa79c18','2008-08-18 09:00:09','2013-12-26 15:55:01','182.88.181.216');

进入表存储数据的目录，查看存储结构：
cd /var/lib/clickhouse/data/db_ck/tbl_test_mergetree_users

1616663330982

进入其中日期目录，查看文件

1616663459477

文件：columns.txt，列信息，元数据

[root@node2 20080817_7_7_0]# more columns.txt
columns format version: 1
10 columns:
`id` UInt64
`email` String
`username` String
`gender` UInt8
`birthday` Date
`mobile` FixedString(13)
`pwd` String
`regDT` DateTime
`lastLoginDT` DateTime
`lastLoginIP` String

文件：count.txt，计数文件

[root@node2 20080817_7_7_0]# more count.txt
16

如果在CllickHouse集群上创建表时，语句如下，需要指定集群ID。

1616663728705

集群中查看创建集群表，如下截图所示：

1616663753247

07-[掌握]-CK 引擎之ReplacingMergeTree 引擎

目标

ReplacingMergeTree 表引擎，解决MergeTree相同主键无法去重的问题。

路径

ReplacingMergeTree 引擎作用

如何创建表

案例演示

实施

为了解决MergeTree相同主键无法去重的问题，ClickHouse提供了ReplacingMergeTree引擎，用来对主键重复的数据进行去重。

删除重复数据可以使用optimize命令手动执行，这个合并操作是在后台运行的，且无法预测具体的执行时间。

1614388309916

创建表，演示案例：

# 创建表
CREATE TABLE tbl_test_replacingmergetree_users
(
    `id` UInt64, 
    `email` String, 
    `username` String, 
    `gender` UInt8, 
    `birthday` Date, 
    `mobile` FixedString(13), 
    `pwd` String, 
    `regDT` DateTime, 
    `lastLoginDT` DateTime, 
    `lastLoginIP` String
)
ENGINE = ReplacingMergeTree(id)
PARTITION BY toYYYYMMDD(regDT)
ORDER BY id
SETTINGS index_granularity = 8192 ;

# 插入数据
INSERT INTO tbl_test_replacingmergetree_users SELECT *
FROM tbl_test_mergetree_users
WHERE id <= 5 ;


# 查询数据
SELECT * FROM tbl_test_replacingmergetree_users ;


# 第二次、插入数据
INSERT INTO tbl_test_replacingmergetree_users (id, email, username, gender, birthday, mobile, pwd, regDT, lastLoginIP, lastLoginDT) SELECT 
    id, 
    email, 
    username, 
    gender, 
    birthday, 
    mobile, 
    pwd, 
    regDT, 
    lastLoginIP, 
    now() AS lastLoginDT
FROM tbl_test_mergetree_users
WHERE id <= 3 ;

# 第二次、查询数据 
SELECT * FROM tbl_test_replacingmergetree_users ;


# 执行优化操作，使用Optimize关键字
OPTIMIZE TABLE tbl_test_replacingmergetree_users FINAL ;


# 第三次、查询数据 
SELECT * FROM tbl_test_replacingmergetree_users

小结

为了解决MergeTree相同主键无法去重的问题，ClickHouse提供了ReplacingMergeTree引擎，用来对主键重复的数据进行去重。

08-[掌握]-CK 引擎之SummingMergeTree 引擎

目标

SummingMergeTree表引擎，用来支持对主键相同的列进行预聚合。

路径

SummingMergeTree 引擎作用

如何创建表

案例演示

实施

ClickHouse通过SummingMergeTree来支持对主键列进行预聚合。在后台合并时，会将主键相同的多行进行sum求和，然后使用一行数据取而代之，从而大幅度降低存储空间占用，提升聚合计算性能。

在预聚合时，ClickHouse会对主键列以外的其他所有列进行预聚合。

但这些列必须是数值类型才会计算sum（当sum结果为0时会删除此行数据）；

如果是String等不可聚合的类型，则随机选择一个值。

1614389158549

通常建议将SummingMergeTree与MergeTree配合使用，使用MergeTree来存储明细数据，使用SummingMergeTree存储预聚合的数据来支撑加速查询。

1614389217787

演示案例如下所示

# 1. 创建表
CREATE TABLE tbl_test_summingmergetree
(
    `key` UInt64, 
    `value` UInt64
)
ENGINE = SummingMergeTree()
ORDER BY key ;

# 2. 插入数据
INSERT INTO tbl_test_summingmergetree (key, value) VALUES (1,13);

# 3. 查询数据
SELECT * FROM tbl_test_summingmergetree ;

# 4. 第二次、插入数据
INSERT INTO tbl_test_summingmergetree (key, value) VALUES (1,13);

# 5. 第二次、查询数据
SELECT * FROM tbl_test_summingmergetree ;

# 6. 第三次、插入数据
INSERT INTO tbl_test_summingmergetree (key, value) VALUES (1,16);

# 7. 第四次、查询数据
SELECT * FROM tbl_test_summingmergetree ;

# 8. 第五次、查询数据
SELECT 
    key, 
    sum(value), 
    count(value)
FROM tbl_test_summingmergetree
GROUP BY key

# 9. 优化表
OPTIMIZE TABLE tbl_test_summingmergetree FINAL ;

# 10. 第六次、哈希数据
SELECT * FROM tbl_test_summingmergetree ;

小结

ClickHouse通过SummingMergeTree来支持对主键列进行预聚合，要求非主键列为数据类型。

09-[掌握]-CK 引擎之AggregatingMergeTree 引擎

目标

AggregatingMergeTree 引擎，用来做增量数据统计聚合，允许这对不同列指定不同的聚合函数。

路径

AggregatingMergeTree 引擎作用

如何创建表

案例演示

实施

AggregatingMergeTree也是预聚合引擎的一种，是在MergeTree的基础上针对聚合函数计算结果进行增量计算，用于提升聚合计算的性能。

1616811314761

插入数据时，语法：xxx-State，其中xxx表示聚合函数名称

查询数据时，语法：xx-Merge，其中xxx表示聚合函数名称

使用AggregatingMergeTree引擎创建表语法：

1614390567329

实例案例，如下所示：

# 1. 创建表
CREATE TABLE tbl_test_mergetree_logs
(
    `guid` String, 
    `url` String, 
    `refUrl` String, 
    `cnt` UInt16, 
    `cdt` DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(cdt)
ORDER BY toYYYYMMDD(cdt) ;

# 2. 插入数据


INSERT INTO tbl_test_mergetree_logs (guid, url, refUrl, cnt, cdt) VALUES ('a','www.itheima.com','www.itcast.cn',1,'2019-12-17 12:12:12'),('a','www.itheima.com','www.itcast.cn',1,'2019-12-17 12:14:45'),('b','www.itheima.com','www.itcast.cn',1,'2019-12-17 13:13:13');

# 3. 查询数据
SELECT * FROM tbl_test_mergetree_logs ;

┌─guid─┬─url─────────────┬─refUrl────────┬─cnt─┬─────────────────cdt─┐
│ a    │ www.itheima.com │ www.itcast.cn │   1 │ 2019-12-17 12:12:12 │
│ a    │ www.itheima.com │ www.itcast.cn │   1 │ 2019-12-17 12:14:45 │
│ b    │ www.itheima.com │ www.itcast.cn │   1 │ 2019-12-17 13:13:13 │
└──────┴─────────────────┴───────────────┴─────┴─────────────────────┘

# 4. 创建表
CREATE TABLE tbl_test_aggregationmergetree_visitor
(
    `guid` String, 
    `cnt` AggregateFunction(count, UInt16), 
    `cdt` Date
)
ENGINE = AggregatingMergeTree()
PARTITION BY cdt
ORDER BY cnt ;

# 5. 插入数据
INSERT INTO tbl_test_aggregationmergetree_visitor SELECT 
    guid, 
    countState(cnt), 
    toDate(cdt)
FROM tbl_test_mergetree_logs
GROUP BY 
    guid, 
    cnt, 
    cdt ;

# 6. 查询数据
SELECT 
    guid, 
    count(cnt)
FROM tbl_test_aggregationmergetree_visitor
GROUP BY 
    guid, 
    cnt ;

小结

AggregatingMergeTree也是预聚合引擎的一种，是在MergeTree的基础上针对聚合函数计算结果进行增量计算用于提升聚合计算的性能。通过预先定义的聚合函数计算数据并通过二进制的格式存入表内。

10-[掌握]-CK 引擎之CollapsingMergeTree 引擎

目标

CollapsingMergeTree 折叠树引擎，通过以增代删的思路，支持行级数据修改和删除的表引擎。

ClickHouse数据库中，不支持传统Update和Delete语句
	使用ALTER语句实现更新和删除操作

折叠树引擎思路：给表中添加一个字段：sign
	id=1001， 。。。， sign=1，增加新数据
	id=1001， 。。。， sign=-1，删除数据
对表进行合并compaciton时，删除数据（1 + （-1））= 0 ，将其删除

路径

CollapsingMergeTree 引擎（折叠树）作用

如何创建表

案例演示

实施

在ClickHouse中不支持对数据update和delete操作（不能使用标准的更新和删除语法操作CK），但在增量计算场景下，状态更新是一个常见的现象，此时update操作似乎更符合这种需求。

ClickHouse提供了一个CollapsingMergeTree表引擎，它继承于MergeTree引擎，是通过一种变通的方式来实现状态的更新。

==基本思路：创建表时，需要指定Sign列（必须是Int8类型，有2个值：1和-1）==

指定Sign列（必须是Int8类型）：

1表示为状态行，当需要新增一个状态时，需要将insert语句中的Sign列值设为1；

-1表示为取消行，当需要删除一个状态时，需要将insert语句中的Sign列值设为-1。

1614391139950

演示案例如下：

# 1. 创建表
CREATE TABLE tbl_test_collapsingmergetree_day_mall_sale
(
    `mallId` UInt64, 
    `mallName` String, 
    `totalAmount` Decimal(32, 2), 
    `cdt` Date, 
    `sign` Int8
)
ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMMDD(cdt)
ORDER BY mallId ;

# 2. 插入数据
INSERT INTO tbl_test_collapsingmergetree_day_mall_sale (mallId, mallName, totalAmount, cdt, sign) VALUES (1,'西单大悦城',17649135.64,'2019-12-24',1);

# 3. 插入数据
INSERT INTO tbl_test_collapsingmergetree_day_mall_sale (mallId, mallName, totalAmount, cdt, sign) VALUES (2,'朝阳大悦城',16341742.99,'2019-12-24',1);

# 4. 查询数据
SELECT * FROM tbl_test_collapsingmergetree_day_mall_sale

# 5. 插入数据
INSERT INTO tbl_test_collapsingmergetree_day_mall_sale (mallId, mallName, totalAmount, cdt, sign) VALUES (1,'西单大悦城',17649135.64,'2019-12-24',-1);

# 6. 插入数据
INSERT INTO tbl_test_collapsingmergetree_day_mall_sale (mallId, mallName, totalAmount, cdt, sign) VALUES (2,'朝阳大悦城',16341742.99,'2019-12-24',-1);

# 7. 优化数据
OPTIMIZE TABLE tbl_test_collapsingmergetree_day_mall_sale FINAL

# 8. 查询数据
SELECT * FROM tbl_test_collapsingmergetree_day_mall_sale

# 9. 插入数据
INSERT INTO tbl_test_collapsingmergetree_day_mall_sale (mallId, mallName, totalAmount, cdt, sign) VALUES (1,'西单大悦城',17649135.64,'2019-12-24',1);

INSERT INTO tbl_test_collapsingmergetree_day_mall_sale (mallId, mallName, totalAmount, cdt, sign) VALUES (2,'朝阳大悦城',16341742.99,'2019-12-24',1);

INSERT INTO tbl_test_collapsingmergetree_day_mall_sale (mallId, mallName, totalAmount, cdt, sign) VALUES (1,'西单大悦城',17649135.64,'2019-12-24',-1);

# 10. 查询数据
SELECT * FROM tbl_test_collapsingmergetree_day_mall_sale ;

# 11. 优化数据
OPTIMIZE TABLE tbl_test_collapsingmergetree_day_mall_sale FINAL ;

# 12. 查询数据
SELECT * FROM tbl_test_collapsingmergetree_day_mall_sale;

小结

CollapsingMergeTree表引擎，通过定义一个sign标记位字段，记录数据行的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标记为-1，则表示这行数据需要被删除。当CollapsingMergeTree分区合并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除。

每次需要新增数据时，写入一行sign标记为1的数据；

需要删除数据时，则写入一行sign标记为-1的数据。

11-[掌握]-CK 引擎之多版本折叠树引擎

目标

VersionedCollapsingMergeTree引擎，使用version列来实现乱序情况下的数据折叠。

路径

VersionedCollapsingMergeTree引擎作用

如何创建表

案例演示

实施

该引擎继承自 MergeTree 并将折叠行的逻辑添加到合并数据部分的算法中，这个引擎:

1616813313423

允许以多个线程的任何顺序插入数据，特别是 Version 列有助于正确折叠行。

比如1个表：
	- 第一步、插入id=1数据，sign=1，version=1
	- 第二步、插入id=1数据，sign=-1，version=1
	
	- 第三步、插入id=1数据，sign=1，version=3
	
	- 第四步、插入id=1数据，sign=1，version=2
	- 第五步、插入id=1数据，sign=-1，version=2

1614391619065

其中表中存在两个字段：

1）、Sign：标识字段，类型：Int8，值：1：状态行和-1：删除行

2）、Version：版本字段，类型UInt8，值为正整数

创建表，进行演示：

# 1. 创建表
CREATE TABLE UAct
(
    `UserID` UInt64, 
    `PageViews` UInt8, 
    `Duration` UInt8, 
    `Sign` Int8, 
    `Version` UInt8
)
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID ;

# 2. 插入数据
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1, 1) ;

# 3. 查询数据
SELECT * FROM UAct  ;

# 4. 插入数据
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1, 1),(4324182021466249494, 6, 185, 1, 2) ;

# 5. 查询数据
SELECT * FROM UAct  ;

# 6. 优化数据
OPTIMIZE TABLE UAct FINAL ;

# 7. 查询数据
SELECT * FROM UAct  ;

小结

VersionedCollapsingMergeTree表引擎的作用与CollapsingMergeTree完全相同，它们的不同之处在于，VersionedCollapsingMergeTree对数据的写入顺序没有要求，在同一个分区内，任意顺序的数据都能够完成折叠操作。