Clickhouse简介及Clickhouse部署、原理和使用介绍
第一章：Clickhouse简介
第一节：clickhouse历史
ClickHouse 是俄罗斯的第一大搜索引擎Yandex于2016年开源的列式存储数据库（DBMS [数据库管理系统Database Management System] ），使用C++语言编写，主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告, 与Hadoop, Spark相比，ClickHouse很轻量级。
第二节：clickhouse相关概念以及特点
1.2.1 列式存储
1.2.1-1 行式存储
采用行式存储时，数据在磁盘上的呈现效果如图：
采用行式存储的好处是想查某行所有数据时，可以通过一次磁盘查找加顺序读取就可以全部获取到。但是当想查所有数据的某列局部数据时，需要不停的查找，或者全表扫描才行，遍历的很多数据都是为了最终结果的中间遍历过程，是不需要的。
1.2.1-2 列式存储
采用列式存储时，数据在磁盘上的呈现效果如图：
采用列式存储的好处是，如需要查某列，只需要通过一次磁盘查找就把对应列全部获取出来。
1.2.1-3 列式存储优点
1.对于列的聚合，计数，求和等统计操作列式优于行式存储。
2.由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重。
3.由于数据压缩比更好，一方面节省了磁盘空间，另一方面对于cache也有了更大的发挥空间。
1.2.2 DBMS功能
数据库管理系统DBMS (Database Management System)是一种操纵和管理数据库的大型软件，是用于建立、使用和维护数据库。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。用户通过dbms访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。它提供多种功能，可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。它使用户能方便地定义和操纵数据，维护数据的安全性和完整性，以及进行多用户下的并发控制和恢复数据库。
ClickHouse是一个DBMS，而不是一个单一的数据库。ClickHouse允许在运行时创建表和数据库，加载数据和运行查询，而无需重新配置和重新启动服务器。
用户管理及权限管理。
数据的备份与恢复
1.2.3 多样化引擎
clickhouse和mysql类似，把表级的存储引擎插件化，根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口和其他四大类20多种引擎。
1.2.4 高吞吐写入能力
clickHouse采用类LSM Tree的结构，数据写入后定期在后台Compaction。通过类LSM tree的结构，ClickHouse在数据导入时全部是顺序append写，写入后数据段不可更改，在后台compaction时也是多个段merge sort后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力，即便在HDD上也有着优异的写入性能。
官方公开benchmark测试显示能够达到50MB-200MB/s的写入吞吐能力，按照每行100Byte估算，大约相当于50W-200W条/s的写入速度。
1.2.5 数据分区与线程级并行
ClickHouse将数据划分为多个partition，每个partition再进一步划分为多个index granularity，然后通过多个CPU核心分别处理其中的一部分来实现并行数据处理。
在这种设计下，单条Query就能利用整机所有CPU。极致的并行处理能力，极大的降低了查询延时。
所以，clickhouse即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多cpu，就不利于同时并发多条查询。所以对于高qps的查询业务，clickhouse并不是强项。
1.2.6 SQL语法支持
几乎覆盖了标准SQL的大部分语法，包括 DDL和 DML ,以及配套的各种函数；
1.2.7 实时数据更新
ClickHouse支持主键表。为了快速执行对主键范围的查询，数据使用合并树(MergeTree)进行递增排序。由于这个原因，数据可以不断地添加到表中。添加数据时无锁处理。
第二章：clickHouse安装部署
ClickHouse的官方Docker镜像部署网址：https://hub.docker.com/r/yandex/clickhouse-server/
本篇文章采用容器部署方式，部署简单快捷，可以快速的进入到熟悉使用和数据库功能了解的阶段；本篇文章主要还是为了介绍clickhouse的使用和概念知识。
第一节：环境准备安装docker
【注意：】 采用容器部署需要先安装一下docker；如果已经安装部署了docker可以直接进行镜像下载和运行。部署clickhouse前先安装docker。
如果docker部署不是很熟练可以使用我下边提供的一键部署脚本执行，运行完毕后即可安装完成docker
#!/bin/sh
# auth bitdata.wangting
# info: please install on centOs7+
# install command: bash install_docker.sh
wget https://download.docker.com/linux/static/stable/x86_64/docker-19.03.14.tgz
usage(){
echo "Usage: $0 FILE_NAME_DOCKER_CE_TAR_GZ"
echo " $0 docker-19.03.14.tgz"
echo "Get docker-ce binary from: https://download.docker.com/linux/static/stable/x86_64/"
echo "eg: wget https://download.docker.com/linux/static/stable/x86_64/docker-19.03.14.tgz"
echo ""
}
SYSTEMDDIR=/usr/lib/systemd/system
SERVICEFILE=docker.service
DOCKERDIR=/usr/bin
DOCKERBIN=docker
SERVICENAME=docker
if [ $# -ne 1 ]; then
usage
exit 1
else
FILETARGZ="$1"
fi
if [ ! -f ${FILETARGZ} ]; then
echo "Docker binary tgz files does not exist, please check it"
echo "Get docker-ce binary from: https://download.docker.com/linux/static/stable/x86_64/"
echo "eg: wget https://download.docker.com/linux/static/stable/x86_64/docker-19.03.14.tgz"
exit 1
fi
echo "##unzip : tar xvpf ${FILETARGZ}"
tar xvpf ${FILETARGZ}
echo
echo "##binary : ${DOCKERBIN} copy to ${DOCKERDIR}"
cp -p ${DOCKERBIN}/* ${DOCKERDIR} >/dev/null 2>&1
which ${DOCKERBIN}
echo "##systemd service: ${SERVICEFILE}"
echo "##docker.service: create docker systemd file"
cat >${SYSTEMDDIR}/${SERVICEFILE} <<EOF
[Unit]
Description=Docker Application Container Engine
Documentation=http://docs.docker.com
After=network.target docker.socket
[Service]
Type=notify
EnvironmentFile=-/run/flannel/docker
WorkingDirectory=/usr/local/bin
ExecStart=/usr/bin/dockerd \
-H unix:///var/run/docker.sock \
--selinux-enabled=false \
--log-opt max-size=1g
ExecReload=/bin/kill -s HUP $MAINPID
# Having non-zero Limit*s causes performance problems due to accounting overhead
# in the kernel. We recommend using cgroups to do container-local accounting.
LimitNOFILE=infinity
LimitNPROC=infinity
LimitCORE=infinity
# Uncomment TasksMax if your systemd version supports it.
# Only systemd 226 and above support this version.
#TasksMax=infinity
TimeoutStartSec=0
# set delegate yes so that systemd does not reset the cgroups of docker containers
Delegate=yes
# kill only the docker process, not all processes in the cgroup
KillMode=process
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
echo ""
systemctl daemon-reload
echo "##Service status: ${SERVICENAME}"
systemctl status ${SERVICENAME}
echo "##Service restart: ${SERVICENAME}"
systemctl restart ${SERVICENAME}
echo "##Service status: ${SERVICENAME}"
systemctl status ${SERVICENAME}
echo "##Service enabled: ${SERVICENAME}"
systemctl enable ${SERVICENAME}
echo "## docker version"
docker version
第二节：安装clickhouse-server端
默认会下载最新版本，可以点开标签页去下载对应的历史版本image
[[email protected] ~]# docker pull yandex/clickhouse-server
Using default tag: latest
latest: Pulling from yandex/clickhouse-server
16ec32c2132b: Pull complete
f222cd12296a: Pull complete
13db1b3dceec: Pull complete
a7f280431f05: Pull complete
9fb0a552d31d: Pull complete
d2ce89d8e63a: Pull complete
Digest: sha256:025a94573ba693ec61d6ae643973883c5c8024ffb42edc11c69999ce3190a994
Status: Downloaded newer image for yandex/clickhouse-server:latest
docker.io/yandex/clickhouse-server:latest
启动server服务：
第三节：安装clickhouse-client端
下载client镜像image
启动client客户端：
[[email protected] ~]# docker run -d --name clickhouse_test --ulimit nofile=262144:262144 --volume=/clickhouse_data:/var/lib/clickhouse yandex/clickhouse-server
Digest: sha256:689005ff7144db1228b71301008209b17f694f273b51421789937a64549ef3a5
Status: Downloaded newer image for yandex/clickhouse-client:latest
ClickHouse client version 21.8.4.51 (official build).
Connecting to clickhouse-server:9000 as user default.
Connected to ClickHouse server version 21.8.4 revision 54449.
9b53bb2ac9f5 :)
9b53bb2ac9f5 :) # 到这里就已经是连入的命令行
第四节：验证服务运行状况
9b53bb2ac9f5 :) show databases;
SHOW DATABASES
Query id: 124ada8d-632c-4242-af92-5916dbddffe2
┌─name────┐
│ default │
│ system │
└─────────┘
2 rows in set. Elapsed: 0.003 sec.
9b53bb2ac9f5 :) use system;
USE system
Query id: 289a0f7c-f845-45fd-922a-838cc28d1fc4
Ok.
0 rows in set. Elapsed: 0.001 sec.
9b53bb2ac9f5 :) show tables limit 10;
SHOW TABLES LIMIT 10
Query id: 23b16eeb-952a-44bf-96e4-263fa6f225ac
┌─name───────────────────────────┐
│ aggregate_function_combinators │
│ asynchronous_metric_log │
│ asynchronous_metrics │
│ build_options │
│ clusters │
│ collations │
│ columns │
│ contributors │
│ current_roles │
│ data_skipping_indices │
└────────────────────────────────┘
10 rows in set. Elapsed: 0.004 sec.
9b53bb2ac9f5 :) select * from aggregate_function_combinators limit 2;
SELECT *
FROM aggregate_function_combinators
LIMIT 2
Query id: 60066d2b-39f7-40a4-82f2-010da1df063e
┌─name────────┬─is_internal─┐
│ SimpleState │ 0 │
│ OrDefault │ 0 │
└─────────────┴─────────────┘
2 rows in set. Elapsed: 0.003 sec.
9b53bb2ac9f5 :) describe table asynchronous_metric_log;
DESCRIBE TABLE asynchronous_metric_log
Query id: 629836f8-72c3-4608-9bd2-7307f86e167f
┌─name────────────────────┬─type───────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ event_date │ Date │ │ │ │ │ │
│ event_time │ DateTime │ │ │ │ │ │
│ event_time_microseconds │ DateTime64(6) │ │ │ │ │ │
│ metric │ LowCardinality(String) │ │ │ │ │ │
│ value │ Float64 │ │ │ │ │ │
└─────────────────────────┴────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
5 rows in set. Elapsed: 0.002 sec.
第三章：clickhouse使用介绍
第一节：clickhouse的数据类型
3.1.1 整型
固定长度的整型，包括有符号整型或无符号整型。
整型范围（-2n-1~2n-1-1）：
Int8 - [-128 : 127]
Int16 - [-32768 : 32767]
Int32 - [-2147483648 : 2147483647]
Int64 - [-9223372036854775808 : 9223372036854775807]
无符号整型范围（0~2n-1）：
UInt8 - [0 : 255]
UInt16 - [0 : 65535]
UInt32 - [0 : 4294967295]
UInt64 - [0 : 18446744073709551615]
使用场景： 个数、数量、也可以存储型id
3.1.2 浮点型
Float32 - float
Float64 – double
建议尽可能以整数形式存储数据。例如，将固定精度的数字转换为整数值，如时间用毫秒为单位表示，因为浮点型进行计算时可能引起四舍五入的误差。
示例：
9b53bb2ac9f5 :) select 10-9
SELECT 10 - 9
Query id: 759b068b-9e35-42fd-88dc-a9dcd98032f5
┌─minus(10, 9)─┐
│ 1 │
└──────────────┘
1 rows in set. Elapsed: 0.002 sec.
9b53bb2ac9f5 :) select 1-0.9
SELECT 1 - 0.9
Query id: e5158320-3bda-4f97-a2cb-c78e8af40467
┌───────minus(1, 0.9)─┐
│ 0.09999999999999998 │
└─────────────────────┘
1 rows in set. Elapsed: 0.003 sec.
使用场景：一般数据值比较小，不涉及大量的统计计算，精度要求不高的时候。比如保存商品的重量
3.1.3 布尔型
clickhouse没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1
3.1.4 Decimal型
有符号的浮点点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃（不舍入）。
有三种声明：Decimal32(s)，相当于Decimal(9-s,s)
Decimal64(s)，相当于Decimal(18-s,s)
Decimal128(s)，相当于Decimal(38-s,s)
s标识小数位
使用场景： 一般金额字段、汇率、利率等字段为了保证小数点精度，都使用Decimal进行存储
3.1.5 字符串型
1）String
字符串可以任意长度的。它可以包含任意的字节集，包含空字节。
2）FixedString(N)
固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的字符串时候，将返回错误消息。
与String相比，极少会使用FixedString，因为使用起来不是很方便。
使用场景：名称、文字描述、字符型编码。 固定长度的可以保存一些定长的内容，比如一些编码，性别等但是考虑到一定的变化风险，带来收益不够明显，所以定长字符串使用意义有限。。
3.1.6 枚举类型
包括 Enum8 和 Enum16 类型。Enum 保存 ‘string’= integer 的对应关系。
Enum8 用 ‘String’= Int8 对描述。
Enum16 用 ‘String’= Int16 对描述。
示例：
【注意】：做实验时复制下方干净开头的语句，类似:-] 开头的是命令行粘贴过来时跨行自动填充的。
9b53bb2ac9f5 :)
-- #############
-- 建t_enum测试表;定义一个列名test_col；wang -> 1 | ting -> 2
-- #############
9b53bb2ac9f5 :) CREATE TABLE t_enum
:-] (
:-] test_col Enum8('wang' = 1, 'ting' = 2)
:-] )
:-] ENGINE = TinyLog
CREATE TABLE t_enum
(
`test_col` Enum8('wang' = 1, 'ting' = 2)
)
ENGINE = TinyLog
Query id: 29e8ba97-afdc-424f-9ffc-c14bca7b85cd
Ok.
0 rows in set. Elapsed: 0.010 sec.
-- #############
-- 这个test_col列只能存储类型定义中列出的值Enum8('wang' = 1, 'ting' = 2)；保存任何其他值抛异常
-- #############
9b53bb2ac9f5 :) INSERT INTO t_enum VALUES ('wang'), ('ting'), ('ting'), ('wang')
INSERT INTO t_enum VALUES
Query id: 6b59ccd4-d056-4885-8447-cb14f1ac2575
Ok.
4 rows in set. Elapsed: 0.172 sec.
-- #############
-- 存储值wangting注意看抛出错误：Code: 36. DB::Exception: Unknown element 'wangting' for enum
-- #############
9b53bb2ac9f5 :) insert into t_enum values('wangting')
INSERT INTO t_enum VALUES
Query id: a19d1b1f-c153-4f07-8cc1-2be8bfd5d3d8
Exception on client:
Code: 36. DB::Exception: Unknown element 'wangting' for enum: data for INSERT was parsed from query
Connecting to clickhouse-server:9000 as user default.
Connected to ClickHouse server version 21.8.4 revision 54449.
-- #############
-- 查询数据
-- #############
9b53bb2ac9f5 :) select * from t_enum
SELECT *
FROM t_enum
Query id: 07ec326b-1a67-4d59-8cc7-2e3d4c6f60cd
┌─test_col─┐
│ wang │
│ ting │
│ ting │
│ wang │
└──────────┘
4 rows in set. Elapsed: 0.018 sec.
-- #############
-- 查询对应行的数值，则必须将 Enum 值转换为Int整数类型
-- #############
9b53bb2ac9f5 :) select CAST(test_col, 'Int8') from t_enum
SELECT CAST(test_col, 'Int8')
FROM t_enum
Query id: f53b149c-7706-4cce-a1c7-c5b4dc217bb1
┌─CAST(test_col, 'Int8')─┐
│ 1 │
│ 2 │
│ 2 │
│ 1 │
└────────────────────────┘
4 rows in set. Elapsed: 0.003 sec.
9b53bb2ac9f5 :)
使用场景：对一些状态、类型的字段算是一种空间优化，也算是一种数据约束。但是实际使用中往往因为一些数据内容的变化增加一定的维护成本，甚至是数据丢失问题，所以谨慎使用。
3.1.7 时间类型
目前clickhouse 常用三种时间类型
Date 接受 年-月-日 的字符串比如 ‘2019-12-16’
Datetime 接受 年-月-日 时:分:秒 的字符串比如 ‘2019-12-16 20:50:10’
Datetime64 接受 年-月-日 时:分:秒.亚秒 的字符串比如 ‘2019-12-16 20:50:10.66’
示例：
-- #############
-- Date ： 2021-08-23
-- #############
9b53bb2ac9f5 :) select toDate(Now())
SELECT toDate(Now())
Query id: c817968e-bc65-47d0-bfc9-7af42b50a5d3
┌─toDate(now())─┐
│ 2021-08-23 │
└───────────────┘
1 rows in set. Elapsed: 0.003 sec.
-- #############
-- Datetime ：2021-08-23 02:08:14
-- #############
9b53bb2ac9f5 :) select toDateTime(Now())
SELECT toDateTime(Now())
Query id: c7929712-856f-402a-89a9-a794425cecdc
┌───toDateTime(now())─┐
│ 2021-08-23 02:08:14 │
└─────────────────────┘
1 rows in set. Elapsed: 0.003 sec.
-- #############
-- DateTime64 ：2021-08-23 10:22:26.000
-- #############
9b53bb2ac9f5 :) SELECT toDateTime64(now(), 3, 'Asia/Shanghai') AS column, toTypeName(column) AS x
SELECT
toDateTime64(now(), 3, 'Asia/Shanghai') AS column,
toTypeName(column) AS x
Query id: 432bf915-7074-4f79-bf76-1b4dd0c5e938
┌──────────────────column─┬─x──────────────────────────────┐
│ 2021-08-23 10:22:26.000 │ DateTime64(3, 'Asia/Shanghai') │
└─────────────────────────┴────────────────────────────────┘
1 rows in set. Elapsed: 0.003 sec.
3.1.8 数组
Array(T)：由 T 类型元素组成的数组。
也可以使用方括号：[]
示例：
9b53bb2ac9f5 :)
9b53bb2ac9f5 :) SELECT array(1, 2) AS x, toTypeName(x)
SELECT
[1, 2] AS x,
toTypeName(x)
Query id: dbaeb0ef-80bb-4f4d-b120-e28cd4214f50
┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8) │
└───────┴─────────────────────────┘
1 rows in set. Elapsed: 0.003 sec.
9b53bb2ac9f5 :) SELECT [3, 4] AS y, toTypeName(y)
SELECT
[3, 4] AS y,
toTypeName(y)
Query id: d09392d8-6bad-4aa5-bf02-1f5f944666f1
┌─y─────┬─toTypeName([3, 4])─┐
│ [3,4] │ Array(UInt8) │
└───────┴────────────────────┘
1 rows in set. Elapsed: 0.002 sec.
第二节：clickhouse的表引擎
表引擎是clickhouse的一大特色。可以说， 表引擎决定了如何存储标的数据。包括：
1）数据的存储方式和位置，写到哪里以及从哪里读取数据
2）支持哪些查询以及如何支持。
3）并发数据访问。
4）索引的使用。
5）是否可以执行多线程请求。
6）数据复制参数。
表引擎的使用方式就是必须显形在创建表时定义该表使用的引擎，以及引擎使用的相关参数。如：
【注意】：引擎的名称大小写敏感
-- #############
-- 创建表t_tinylog，定义引擎为engine=TinyLog
-- #############
9b53bb2ac9f5 :) create table t_tinylog ( id String, name String) engine=TinyLog;
CREATE TABLE t_tinylog
(
`id` String,
`name` String
)
ENGINE = TinyLog
Query id: 251f8316-0f54-479c-8082-7a9a2928ace5
Ok.
0 rows in set. Elapsed: 0.004 sec.
-- #############
-- 引擎名称大小写注意正确定义，tinyLog、Tinylog等等都错误，会创建失败
-- #############
9b53bb2ac9f5 :) create table t_tinylogg ( id String, name String) engine=tinyLog;
CREATE TABLE t_tinylogg
(
`id` String,
`name` String
)
ENGINE = tinyLog
Query id: a80ab722-dfa1-4e5a-a304-5ac5246426f5
0 rows in set. Elapsed: 0.002 sec.
Received exception from server (version 21.8.4):
Code: 56. DB::Exception: Received from clickhouse-server:9000. DB::Exception: Unknown table engine tinyLog. Maybe you meant: ['TinyLog'].
3.2.1 TinyLog
以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，生产环境上作用有限。
3.2.2 Memory
内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过10G/s）。
使用场景不多，在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景。
3.2.3 MergeTree
Clickhouse中最强大表引擎为MergeTree( 合并树 ),引擎及该系列（*MergeTree）中的其他引擎。地位可以相当于innodb之于Mysql。基于MergeTree还衍生出比较有特色的引擎。
示例：
-- #############
-- 创建t_mergetree表，指定engine =MergeTree
-- #############
9b53bb2ac9f5 :) create table t_mergetree(
:-] 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)
CREATE TABLE t_mergetree
(
`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)
Query id: 74a98273-89f3-4f2b-85b8-e664d3a67749
Ok.
0 rows in set. Elapsed: 0.027 sec.
-- #############
-- 插入t_mergetree表数据
-- #############
9b53bb2ac9f5 :) INSERT INTO t_mergetree
:-] VALUES (101, 'sku_001', 1000.00, '2021-08-01 12:00:00'),
:-] (102, 'sku_002', 2000.00, '2021-08-01 11:00:00'),
:-] (102, 'sku_004', 2500.00, '2021-08-01 12:00:00'),
:-] (102, 'sku_002', 2000.00, '2021-08-01 13:00:00'),
:-] (102, 'sku_002', 12000.00, '2021-08-01 13:00:00'),
:-] (102, 'sku_002', 600.00, '2021-08-02 12:00:00')
INSERT INTO t_mergetree VALUES
Query id: bbab9faf-3853-4803-9959-9c0270ed13f5
Ok.
6 rows in set. Elapsed: 0.005 sec.
3.2.3-1 partition by 分区
作用：分区的目的主要是降低扫描的范围,优化查询速度。如果设定:会默认使用一个分区。
分区目录: MergeTree 是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中。
并行：分区后，面对涉及跨分区的查询统计，clickhouse会以分区为单位并行处理。
数据写入与分区合并：任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻（大概10-15分钟后），clickhouse会自动执行合并操作（也可以手动通过optimize执行），把临时分区的数据，合并到已有分区中。
optimize table xxxx [final]
3.2.3-2 primary key 主键
clickhouse中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同primary key的数据的。
主键的设定主要依据是查询语句中的where 条件。根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity,避免了全包扫描。
index granularity： 直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。clickhouse中的MergeTree默认是8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。
稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。
3.2.3-3 order by
order by 设定了分区内的数据按照哪些字段顺序进行有序保存。
order by是MergeTree中唯一必填项，甚至比primary key 还重要，因为当用户不设置主键的情况，很多处理会依照order by的字段进行处理
要求：主键必须是order by字段的前缀字段。比如order by 字段是 (id,sku_id) 那么主键必须是id 或者(id,sku_id)
3.2.3-4 数据TTL
TTL即Time To Live，MergeTree提供了可以管理数据或者列的生命周期的功能。
定义相对抽象，示例说明：
-- #############
-- 新建一张t_mergetree02表
-- #############
9b53bb2ac9f5 :) create table t_mergetree02(
:-] id UInt32,
:-] sku_id String,
:-] total_amount Decimal(16,2) TTL create_time+interval 10 SECOND,
:-] create_time Datetime
:-] ) engine =MergeTree
:-] partition by toYYYYMMDD(create_time)
:-] primary key (id)
:-] order by (id, sku_id)
CREATE TABLE t_mergetree02
(
`id` UInt32,
`sku_id` String,
`total_amount` Decimal(16, 2) TTL create_time + toIntervalSecond(10),
`create_time` Datetime
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY id
ORDER BY (id, sku_id)
Query id: 35328623-2db0-47bc-97f2-e4154faec826
Ok.
0 rows in set. Elapsed: 0.022 sec.
-- #############
-- t_mergetree02表中插入样例数据
-- #############
9b53bb2ac9f5 :) insert into t_mergetree02
:-] values(106,'sku_001',1000.00,'2021-08-01 22:52:30') ,
:-] (107,'sku_002',2000.00,'2021-08-02 22:52:30'),
:-] (110,'sku_003',600.00,'2021-08-03 12:00:00')
INSERT INTO t_mergetree02 VALUES
Query id: 9e5b3763-5341-4bc2-ac16-f82c31bfbc9c
Ok.
3 rows in set. Elapsed: 0.005 sec.
-- #############
-- 查询t_mergetree02表中数据
-- #############
9b53bb2ac9f5 :) select * from t_mergetree02
SELECT *
FROM t_mergetree02
Query id: 124020b6-922b-4532-8e46-a5f27de88b97
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 106 │ sku_001 │ 1000.00 │ 2021-08-01 22:52:30 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 107 │ sku_002 │ 2000.00 │ 2021-08-02 22:52:30 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 110 │ sku_003 │ 600.00 │ 2021-08-03 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
3 rows in set. Elapsed: 0.004 sec.
-- #############
-- 修改表结构,表数据生命周期与create_time相关，设置10秒存活
-- #############
9b53bb2ac9f5 :) alter table t_mergetree02 MODIFY TTL create_time + INTERVAL 10 SECOND
ALTER TABLE t_mergetree02
MODIFY TTL create_time + toIntervalSecond(10)
Query id: ef38e9c0-cfb4-423e-b6dc-77597da06e05
Ok.
0 rows in set. Elapsed: 0.030 sec.
-- #############
-- 稍许片刻，再次查询t_mergetree02表中数据，这时数据已经为空
-- #############
9b53bb2ac9f5 :) select * from t_mergetree02
SELECT *
FROM t_mergetree02
Query id: 959696ea-2f8e-4884-8150-c94d9db6a528
Ok.
0 rows in set. Elapsed: 0.003 sec.
3.2.4 ReplacingMergeTree
ReplacingMergeTree是MergeTree的一个变种，存储特性完全继承MergeTree，只是多了一个去重的功能。
尽管MergeTree可以设置主键，但是primary key其实没有唯一约束的功能。需要处理重复数据时，可以借助这个ReplacingMergeTree。
去重时机：数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。
去重范围：如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。
所以ReplacingMergeTree能力有限，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。
ReplacingMergeTree() 填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认保留最后一条。
示例：
-- #############
-- 新建一张t_mergetree03表
-- #############
9b53bb2ac9f5 :) create table t_mergetree03(
:-] 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)
CREATE TABLE t_mergetree03
(
`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)
Query id: 3c32cf02-0058-459c-921d-6f5122e4c287
Connecting to clickhouse-server:9000 as user default.
Connected to ClickHouse server version 21.8.4 revision 54449.
Ok.
0 rows in set. Elapsed: 0.033 sec.
-- #############
-- 插入t_mergetree03表7条数据
-- #############
9b53bb2ac9f5 :) insert into t_mergetree03
:-] values(101,'sku_001',1000.00,'2021-08-01 12:00:00') ,
:-] (102,'sku_002',2000.00,'2021-08-01 11:00:00'),
:-] (102,'sku_004',2500.00,'2021-08-01 12:00:00'),
:-] (102,'sku_002',2000.00,'2021-08-01 13:00:00')
:-] (102,'sku_002',12000.00,'2021-08-01 13:00:00')
:-] (102,'sku_002',600.00,'2021-08-02 12:00:00')
INSERT INTO t_mergetree03 VALUES
Query id: 0f8ba3f5-3100-4f61-adbb-6bf4a2fddbe5
Ok.
6 rows in set. Elapsed: 0.007 sec.
-- #############
-- 查询t_mergetree03表数据，可以看到结果展示已经去掉了部分重复内容
-- #############
9b53bb2ac9f5 :) SELECT * FROM t_mergetree03
SELECT *
FROM t_mergetree03
Query id: c7bdc983-e337-4ded-91aa-c3c46b98462e
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │ 12000.00 │ 2021-08-01 13:00:00 │
│ 102 │ sku_004 │ 2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
4 rows in set. Elapsed: 0.004 sec.
【注意】：
- 实际上是使用order by 字段作为唯一键。
- 去重不能跨分区。
- 只有合并分区才会进行去重。
- 认定重复的数据保留，版本字段值最大的。
- 如果版本字段相同则保留最后一笔。
3.2.5 SummingMergeTree
对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。
Clickhouse 为了这种场景，提供了一种能够“预聚合”的引擎，SummingMergeTree.
示例：
-- #############
-- 新建t_mergetree04表
-- #############
9b53bb2ac9f5 :) create table t_mergetree04(
:-] 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 )
CREATE TABLE t_mergetree04
(
`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)
Query id: c735fc07-58ae-49ba-babf-caf43a44a8a5
Connecting to clickhouse-server:9000 as user default.
Connected to ClickHouse server version 21.8.4 revision 54449.
Ok.
0 rows in set. Elapsed: 0.027 sec.
-- #############
-- 插入6条数据
-- #############
9b53bb2ac9f5 :) insert into t_mergetree04
:-] values(101,'sku_001',1000.00,'2021-08-01 12:00:00') ,
:-] (102,'sku_002',2000.00,'2021-08-01 11:00:00'),
:-] (102,'sku_004',2500.00,'2021-08-01 12:00:00'),
:-] (102,'sku_002',2000.00,'2021-08-01 13:00:00'),
:-] (102,'sku_002',12000.00,'2021-08-01 13:00:00'),
:-] (102,'sku_002',600.00,'2021-08-02 12:00:00')
INSERT INTO t_mergetree04 VALUES
Query id: 1674bd42-282a-4e51-869b-8aea80f8e89f
Ok.
6 rows in set. Elapsed: 0.065 sec.
-- #############
-- 查询表数据，发现sku_002 同一天时间分区的total_amount会聚合
-- #############
9b53bb2ac9f5 :) select * from t_mergetree04
SELECT *
FROM t_mergetree04
Query id: 4d5d0ea2-113e-4b8a-868c-cfcbbd8b8b44
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │ 1000.00 │ 2021-08-01 12:00:00 │
│ 102 │ sku_002 │ 16000.00 │ 2021-08-01 11:00:00 │
│ 102 │ sku_004 │ 2500.00 │ 2021-08-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 102 │ sku_002 │ 600.00 │ 2021-08-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
4 rows in set. Elapsed: 0.004 sec.
第三节：clickhouse常用SQL操作
3.3.1 insert
表插入数据
insert into [table_name] values(…),(….)
从表A到表B插入数据
insert into [table_name_A] select a,b,c from [table_name_B]
3.3.2 update | delete
ClickHouse提供了Delete 和Update的能力，这类操作被称为Mutation查询，它可以看做Alter 的一种。
虽然可以实现修改和删除，但是和一般的OLTP数据库不一样，Mutation语句是一种很“重”的操作，而且不支持事务。
“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。
由于操作比较“重”，所以 Mutation语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。知道触发分区合并的时候，才会删除旧数据释放磁盘空间。
删除delete操作
修改update操作
3.3.3 select
clickhouse基本上与标准SQL 差别不大。
支持子查询
支持CTE(with 子句)
支持各种JOIN， 但是JOIN操作无法使用缓存，所以即使是两次相同的JOIN语句，Clickhouse也会视为两条新SQL。
不支持窗口函数。
不支持自定义函数。
GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。
3.3.4 alter
新增字段
alter table table_name add column new_col_name String after col1
修改字段类型
alter table table_name modify column col_name String
删除字段
alter table table_name drop column col_name