OLTP

    全称 OnLine Transaction Processing，联机事务处理系统, 就是对数据的增删改查等操作。存储的是业务数据，来记录某类业务事件的发生，比如下单、支付、注册、等等。
    典型代表有Mysql、 Oracle等数据库，对应的网站、系统应用后端数据库 针对事务进行操作，对响应时间要求高，面向前台应用的，应用比较简单，数据量相对较少，是GB级别的。
    面向群体：业务人员

OLAP

    当数据积累到一定的程度，需要对过去发生的事情做一个总结分析时，就需要把过去一段时间内产生的数据拿出来进行统计分析，从中获取想要的信息，为公司做决策提供支持，这个就是做OLAP了。
    OnLine Analytical Processing，联机分析处理系统,存储的是历史数据，对应的风控平台、BI平台、数据可视化等系统就属于OLAP。
    OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策，并且提供直观易懂的查询结果
    典型代表有 Hive、ClickHouse
    针对基于查询的分析系统，基础数据来源于生产系统中的操作数据，数据量非常大，常规是TB级别的
    面向群体：分析决策人员

OLAP 场景的关键特征

    基本业务量到达这个分库分表量级，则离不开数据大屏、推荐系统、画像系统等搭建
    需要搭建上面的系统，则离不开海量数据进行存储-分析-统计
    海量数据下 TB、PB级别数据存储，靠Mysql进行存储-分析-统计无疑是灾难

• 绝大多数是读请求
• 数据以相当大的批次(> 1000 行)更新，而不是单行更新;或者根本没有更新。
• 已添加到数据库的数据不能修改。
• 对于读取，从数据库中提取相当多的行，但只提取列的一小部分。
• 宽表，即每个表包含着大量的列
• 查询相对较少(通常每台服务器每秒查询数百次或更少)
• 对于简单查询，允许延迟大约 50 毫秒
• 列中的数据相对较小：数字和短字符串(例如，每个 URL 60 个字节)
• 处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)
• 事务不是必须的
• 对数据一致性要求低
• 每个查询有一个大表。除了他以外，其他的都很小。
• 查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的 RAM 中

    很容易可以看出，OLAP场景与其他通常业务场景(例如,OLTP或K/V)有很大的不同， 因此想要使用OLTP或Key-Value数据库去高效的处理分析查询场景，并不是非常完美的适用方案。例如，使用OLAP数据库去处理分析请求通常要优于使用MongoDB或Redis去处理分析请求。

列式数据库更适合 OLAP 场景的原因
    列式数据库更适合于OLAP场景(对于大多数查询而言，处理速度至少提高了100倍)，下面详细解释了原因(通过图片更有利于直观理解)：

行式

列式

行式存储

    传统的OLTP关系型数据库 都是行存储。
    一行中的数据在存储介质中以连续存储形式存在。
    适合随机的增删改查操作，或者在行中选取所有属性的查询操作，结合索引提升性能。

缺点
    查询的全部记录的某几个字段，但由于这些字段在各行数据单元中，而整个行特别大(字段特别多)，程序必须不断读取每一条的行记录取对应的字段，使得读取效率大大降低。

例子
    找某个订单的话，就很方便，订单的全部信息都能获取
    但找全部订单总金额就需要遍历多个数据

列式存储

    存储结构化数据时，在底层的存储介质上，数据是以列的方式来组织
    存储完若干条记录的首个字段后，再存储这些记录的第二个字段，然后再第三个字段、第四个字段...
    查询时只有涉及到的列会被读取，而不需要全表扫描，可以显著减少IO消耗，并降低查询响应时间

缺点
    列存储数据更新成本较高，一般适合读多写少的场景，适合 OLAP 分析型系统。

例子
    查询全部成交额，只需要把订单金额拿出来即可

行/列存储对比

• 行/列存储描述的是底层存储介质上，数据的组织形式，哪种组织对应哪种业务需求
• 列存储在聚合、统计等操作性能会优于行存储
• 列存储将多行记录的列连续存储在一起，一列接着一列
• 列式存储是同个数据类型，会进行数据压缩率更高，更省空间
• 列存储数据更新成本较高，一般适合读多写少的场景，适合 OLAP 分析型系统

ClickHouse

特点
• 不依赖 Hadoop 生态、安装和维护简单
• 擅长对列的聚合、计数等统计操作性能强劲
• 对列存储和压缩的采用更好的算法，更好节省成本
• 拓展性强，在生产中经过实战测试，从单服务器部署到具有数千个节点的集群的线性水平可扩展性
• 具有企业级安全功能和故障安全机制，可防止数据因应用程序错误和人为错误而损坏
• 支持主流的大部分 SQL 语法和函数
• 吞吐能力强，官方测试支持，支持多种存储引擎，满足多数业务场景

应用场景
• 互联网电商
• 在线教育
• 金融等领域用
• 户行为数据记录和分析
• 搭建数据可视化平台

性能对比

有谁在用
• 国内：阿里、字节、腾讯 、虎牙、青云、新浪等
• 国外：优步、Ebay、Spotify、思科、等

支持多种部署方式
• Linux：源码部署、Docker 部署
• Window：安装 Docker、安装 Linux 虚拟机

ClickHouse 与 MySQL 的对比

ClickHouse 支持事务的情况
ClickHouse 是一个高性能的列式数据库，主要用于分析和 OLAP（在线分析处理）场景。关于事务支持，ClickHouse 的情况如下：

1. 事务支持的现状
   不支持传统的 ACID 事务：ClickHouse 不支持传统意义上的 ACID（原子性、一致性、隔离性、持久性）事务。这意味着在 ClickHouse 中，不能像在关系型数据库中那样进行复杂的多行或多表的事务处理。
2. 事务特性
   单行插入：ClickHouse 支持单行插入操作，但在并发写入时，可能会出现数据不一致的情况。
   批量插入：支持批量插入，但在批量插入过程中，如果发生错误，可能会导致部分数据插入成功，部分失败。
   表的合并：ClickHouse 使用 MergeTree 引擎进行数据的合并和压缩，这个过程是异步的，可能会影响数据的即时可见性。
3. 解决方案
   虽然 ClickHouse 不支持传统的事务，但可以通过以下方式来处理数据一致性问题：
   使用引擎：选择合适的表引擎（如 ReplacingMergeTree）来处理数据的更新和替换。
   数据版本控制：在数据表中添加版本号或时间戳，以便在查询时选择最新的数据。
   外部事务管理：在应用层实现事务管理，确保在写入 ClickHouse 之前，数据在其他系统中已经完成事务处理。

Docker 安装

 1# 安装并运行Docker。
 2yum install docker-io -y
 3systemctl start docker
 4
 5# 检查安装结果。
 6docker info
 7
 8# 启动使用Docker
 9systemctl start docker     #运行Docker守护进程
10systemctl stop docker      #停止Docker守护进程
11systemctl restart docker   #重启Docker守护进程
12
13# 修改镜像仓库
14vim /etc/docker/daemon.json
15{
16  "registry-mirrors": ["https://dockerpull.com"]
17}

器容器化部署 ClickHouse

端口

   默认 http端口是8123
   tcp端口是9000
   同步端口9009

Web 可视化界面
   http://192.168.10.63:8123/play

命令
   默认数据库最初为空，用于执行未指定数据库的命令

强烈不推荐

• 在任何其他情况下不能将 ClickHouse 服务器暴露给公共互联网
• ClickHouse体验平台界面实际上是通过ClickHouse 的HTTP API接口实现的
• 确保它只在私有网络上侦听，并由正确配置的防火墙监控。

docker 部署 ClickHouse

1mkdir -pv /mydata/docker/clickhouse/log
2mkdir -pv /mydata/docker/clickhouse/data
3
4docker run -d --name clickhouse --ulimit nofile=262144:262144 \
5-p 8123:8123 -p 9000:9000 -p 9009:9009 --privileged=true \
6-v /mydata/docker/clickhouse/log:/var/log/clickhouse-server \
7-v /mydata/docker/clickhouse/data:/var/lib/clickhouse clickhouse/clickhouse-server:22.2.3.5

数据库可视化工具 DBeaver 连接 ClickHouse

   可视化工具 DBeaver 下载 Windows (zip)

创建数据库和表

 1### 创建数据库 soulboy_shop
 2CREATE DATABASE soulboy_shop
 3
 4### 创建表 clickstream
 5# ClickHouse 有自己的数据类型，每个表都必须指定一个Engine引擎属性来确定要创建的表的类型
 6# 引擎决定了数据的存储方式和存储位置、支持哪些查询、对并发的支持
 7
 8CREATE TABLE soulboy_shop.clickstream (
 9    customer_id String, 
10    time_stamp Date, 
11    click_event_type String,
12    page_code FixedString(20),  
13    source_id UInt64,
14    money Decimal(2,1),
15    is_new Bool
16) 
17ENGINE = MergeTree()
18ORDER BY (time_stamp)
19
20### 删除表
21DROP TABLE IF EXISTS soulboy_shop.clickstream
22
23### 查看表结构
24DESCRIBE soulboy_shop.clickstream
25
26### 插入数据
27INSERT INTO soulboy_shop.clickstream
28VALUES ('customer1', '2021-10-02', 'add_to_cart', 'home_enter', 568239, 3.8, 1) 
29
30### 查询数据
31SELECT * FROM soulboy_shop.clickstream
32
33### 条件查询数据
34SELECT * FROM soulboy_shop.clickstream WHERE time_stamp >= '2001-11-01'
35
36### 更新和删除 
37在OLAP数据库中，可变数据（Mutable data）通常是不被欢迎的，早期ClickHouse是不支持，后来版本才有
38不支持事务，建议批量操作，不要高频率小数据量更新删除
39删除和更新是一个异步操作的过程，语句提交立刻返回，但不一定已经完成了
40
41# 判断是否完成（is_done字段为1代表sql语句执行完成）   
42SELECT database, table, command, create_time, is_done FROM system.mutations LIMIT 20
43
44# 更新
45ALTER TABLE soulboy_shop.clickstream UPDATE click_event_type = 'pay' where customer_id = 'customer1'; 
46
47# 删除
48ALTER TABLE soulboy_shop.clickstream delete where customer_id = 'customer2';

数据类型

• ASCII 码：一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间
• UTF-8 编码：一个英文字符等于一个字节，一个中文（含繁体）等于三个字节

查看所有数据类型

• 不同 CH 版本存在不一样的数据类型，毕竟是火热项目，有些尽管新增但是也很少没用上,记住常用的即可
• case_insensitive 选项为 1 表示大小写不敏感，字段类型不区分大小写
• 为 0 表示大小写敏感，即字段类型需要严格区分大小写

1select * from system.data_type_families

 1##################### 整型 
 2### 有符号整型范围
 3Int8 — [-128 : 127]
 4
 5Int16 — [-32768 : 32767]
 6
 7Int32 — [-2147483648 : 2147483647]
 8
 9Int64 — [-9223372036854775808 : 9223372036854775807]
10
11Int128 — [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]
12
13Int256 — [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]
14
15### 无符号整型范围
16UInt8 — [0 : 255]
17
18UInt16 — [0 : 65535]
19
20UInt32 — [0 : 4294967295]
21
22UInt64 — [0 : 18446744073709551615]
23
24UInt128 — [0 : 340282366920938463463374607431768211455]
25
26UInt256 — [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935]
27
28
29### Decimal类型
30例子：Decimal(10,2) 小数部分2位，整数部分 8位（10-2)
31整数部分超过了会报错，小数部分超过了会截取丢失
32
33
34CREATE TABLE soulboy_shop.clickstream1 (
35    customer_id String, 
36    time_stamp Date, 
37    click_event_type String,
38    page_code FixedString(20),  
39    source_id UInt64,
40    money Decimal(2,1)
41) 
42ENGINE = MergeTree()
43ORDER BY (time_stamp)
44
45
46INSERT INTO soulboy_shop.clickstream1
47VALUES ('customer1', '2021-10-02', 'add_to_cart', 'home_enter', 568239,2.11 ) 
48
49
50### Decimal类型
51CREATE TABLE soulboy_shop.clickstream1 (
52    customer_id String, 
53    time_stamp Date, 
54    click_event_type String,
55    page_code FixedString(20),  
56    source_id UInt64,
57    money Decimal(2,1)
58) 
59ENGINE = MergeTree()
60ORDER BY (time_stamp)
61
62INSERT INTO soulboy_shop.clickstream1
63VALUES ('customer1', '2021-10-02', 'add_to_cart', 'home_enter', 568239,2.11 ) 
64
65### 字符串类型
66通用唯一标识符(UUID)是由一组32位数的16进制数字所构成，用于标识记录
67
68# 建表和插入例子
69CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog
70INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'
71
72### 枚举类型
73包括 Enum8 和 Enum16 类型，Enum 保存 'string'= integer 的对应关系
74在 ClickHouse 中，尽管用户使用的是字符串常量，但所有含有 Enum 数据 类型的操作都是按照包含整数的值来执行。这在性能方面比使用 String 数据类型更有效。
75
76Enum8 用 'String'= Int8 对描述。
77Enum16 用 'String'= Int16 对描述。
78
79# 创建一个带有一个枚举 Enum8('home' = 1, 'detail' = 2, 'pay'=3) 类型的列
80CREATE TABLE t_enum
81(
82    page_code Enum8('home' = 1, 'detail' = 2,'pay'=3)
83)
84ENGINE = TinyLog
85插入, page_code 这列只能存储类型定义中列出的值：'home'或`'detail' 或 'pay'。如果您尝试保存任何其他值，ClickHouse 抛出异常
86
87# 插入成功
88INSERT INTO t_enum VALUES ('home'), ('detail')
89
90# 插入报错
91INSERT INTO t_enum VALUES ('home1')
92
93# 查询
94SELECT * FROM t_enum

MySQL 数据类型对比

常见 SQL 语法

   ClickHouse 语法和常规 SQL 语法类似，多数都是支持的

注意事项

   每次更新或者删除，会废弃目标数据的原有分区，而重建新分区
   如果只更新一条数据，那么需要重建一个分区，开销大
   如果更新100条数据，而这100条可能落在3个分区上，则需重建3个分区
   相对来说 一次更新一批数据的整体效率远高于一次更新一行

 1### 创建数据库 soulboy_shop
 2CREATE DATABASE soulboy_shop
 3
 4### 创建表 clickstream
 5# ClickHouse 有自己的数据类型，每个表都必须指定一个Engine引擎属性来确定要创建的表的类型
 6# 引擎决定了数据的存储方式和存储位置、支持哪些查询、对并发的支持
 7
 8CREATE TABLE soulboy_shop.clickstream (
 9    customer_id String, 
10    time_stamp Date, 
11    click_event_type String,
12    page_code FixedString(20),  
13    source_id UInt64,
14    money Decimal(2,1),
15    is_new Bool
16) 
17ENGINE = MergeTree()
18ORDER BY (time_stamp)
19
20### 删除表
21DROP TABLE IF EXISTS soulboy_shop.clickstream
22
23### 查看表结构
24DESCRIBE soulboy_shop.clickstream
25
26### 插入数据
27INSERT INTO soulboy_shop.clickstream
28VALUES ('customer1', '2021-10-02', 'add_to_cart', 'home_enter', 568239, 3.8, 1) 
29
30### 查询数据
31SELECT * FROM soulboy_shop.clickstream
32
33### 条件查询数据
34SELECT * FROM soulboy_shop.clickstream WHERE time_stamp >= '2001-11-01'
35
36### 更新和删除 
37在OLAP数据库中，可变数据（Mutable data）通常是不被欢迎的，早期ClickHouse是不支持，后来版本才有
38不支持事务，建议批量操作，不要高频率小数据量更新删除
39删除和更新是一个异步操作的过程，语句提交立刻返回，但不一定已经完成了
40
41# 判断sql语句是否执行完成（is_done字段为1代表sql语句执行完成）   
42SELECT database, table, command, create_time, is_done FROM system.mutations LIMIT 20
43
44# 更新
45ALTER TABLE soulboy_shop.clickstream UPDATE click_event_type = 'pay' where customer_id = 'customer1'; 
46
47# 删除
48ALTER TABLE soulboy_shop.clickstream delete where customer_id = 'customer2';

分片-分区-副本

分区

   分区是表的分区，把一张表的数据分成N多个区块，分区后的表还是一张表，数据处理还是由自己来完成

   PARTITION BY，指的是一个表按照某一列数据（比如日期）进行分区，不同分区的数据会写入不同的文件中

   建表时加入 partition 概念，可以按照对应的分区字段，允许查询在指定了分区键的查询，减少表的扫描范围，提升性能

 1create table soulboy_shop.order_merge_tree( 
 2    id UInt32,
 3    sku_id String,
 4    out_trade_no String,
 5    total_amount Decimal(16,2), 
 6    create_time Datetime
 7) engine =MergeTree()
 8  partition by toYYYYMMDD(create_time) 
 9  order by (id,sku_id)
10  primary key (id);

分片

   shard 分片是把 数据库横向扩展（Scale Out）到多个物理节点上的一种有效的方式

   复用了数据库的分区概念，相当于在原有的分区下作为第二层分区，ClickHouse会将数据分为多个分片，并且分布到不同节点上，再通过 Distributed 表引擎把数据拼接起来一同使用

   Sharding 机制使得 ClickHouse 可以横向线性拓展，构建大规模分布式集群，但需要避免数据倾斜问题(热点数据)

副本

   两个相同数据的表, 作 用是为了数据备份与安全，保障数据的高可用性，

   即使一台 ClickHouse 节点宕机，那么也可以从其他服务器获得相同的数据

   类似 MySQL 主从架构，主节点宕机，从节点也能提供服务

表引擎

MySQL 的表引擎

ClickHouse 提供了多种的表引擎，不同的表引擎也代表有不同的功能

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

Log 系列

   最小功能的轻量级引擎，当需要快速写入许多小表并在以后整体读取它们时效果最佳，一次写入多次查询

• TinyLog
• StripLog
• Log

MergeTree 系列

   ClickHouse 最强大的表引擎，有多个不同的种类
   适用于高负载任务的最通用和功能最强大的表引擎，可以快速插入数据并进行后续的后台数据处理
   支持主键索引、数据分区、数据副本等功能特性和一些其他引擎不支持的其他功能

• MergeTree、ReplacingMergeTree
• SummingMergeTree、AggregatingMergeTree
• CollapsingMergeTree、VersionedCollapsingMergeTree、GraphiteMergeTree

外部存储引擎系列

   能够直接从其它的存储系统读取数据，例如直接读取 HDFS 的文件或者 MySQL 数据库的表，这些表引擎只负责元数据管理和数据查询

• HDFS、MySQL
• Kafka、JDBC

Memory 引擎

   原生数据直接存储内存，性能高，重启则消失，读写不会阻塞，不支持索引，主要是测试使用

Distributed 引擎

   分布式引擎 本身不存储数据, 但可以在多个服务器上进行分布式查询。 读是自动并行的。读取时，远程服务器表的索引（如果有的话）会被使用

File 引擎

   数据源是以 Clickhouse 支持的一种输入格式（TabSeparated，Native 等）存储数据的文件
   从 ClickHouse 导出数据到文件,将数据从一种格式转换为另一种格式

Merge 引擎

   Merge 引擎 (不要跟 MergeTree 引擎混淆) 本身不存储数据，但可用于同时从任意多个其他的表中读取数据。读是自动并行的，不支持写入(读取时)， 那些被真正读取到数据的表的索引（如果有的话）会被使用

MergeTree

• 中文名叫合并树，MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。
• 数据可以以【数据片段】的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。
• 相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多
• 这种数据片段反复合并的特性，也正是合并树名称的由来

MergeTree 特点

语法

 1CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
 2(
 3    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
 4    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
 5    ...
 6    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
 7    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
 8) ENGINE = MergeTree()
 9ORDER BY expr
10[PARTITION BY expr]
11[PRIMARY KEY expr]
12[SAMPLE BY expr]
13[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
14[SETTINGS name=value, ...]

合并树 MergeTree 建表和数据实操
   新的数据写入会有 临时分区产生，不加入已有分区

   写入完成后经过一定时间（10 到 15 分钟），ClickHouse 会 自动化执行合并操作，将临时分区的数据合并到已有分区当中

   optimize 的合并操作是 在后台执行的，无法预测具体执行时间点，除非是手动执行

1optimize table xxx final;
2optimize table soulboy_shop.order_merge_tree final;

   在数据量比较大的情况，尽量不要使用该命令，执行 optimize 要消耗大量时间

 1###  创建表 order_merge_tree
 2create table soulboy_shop.order_merge_tree( 
 3    id UInt32,
 4    sku_id String,
 5    out_trade_no String,
 6    total_amount Decimal(16,2), 
 7    create_time Datetime
 8) engine =MergeTree()
 9  order by (id,sku_id)
10  partition by toYYYYMMDD(create_time) 
11  primary key (id);
12
13### 查看表结构
14DESCRIBE soulboy_shop.order_merge_tree
15
16### 表数据写入
17* 查询
18* 再插入
19* 查询，有多个分区
20* 合并
21* 查询，分区减少
22
23insert into soulboy_shop.order_merge_tree values 
24(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
25(2,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
26(3,'sku_3','542323',55.02,'2023-03-01 18:00:00'), 
27(4,'sku_1','54222',2000.3,'2023-04-01 19:00:00'), 
28(5,'sku_2','53423',120.2,'2023-04-01 19:00:00'), 
29(6,'sku_4','65432',600.01,'2023-04-02 11:00:00');
30
31# 分区合并验证
32docker exec -it clickhouse /bin/bash
33clickhouse-client
34491cfd7182f5 :) SELECT * FROM soulboy_shop.order_merge_tree
35
36SELECT *
37FROM soulboy_shop.order_merge_tree
38
39Query id: 839b0acd-48d1-4689-8956-9ea362d4bd13
40
41┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
42│  1 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
43│  2 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
44│  3 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
45└────┴────────┴──────────────┴──────────────┴─────────────────────┘
46┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
47│  4 │ sku_1  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
48│  5 │ sku_2  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
49└────┴────────┴──────────────┴──────────────┴─────────────────────┘
50┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
51│  6 │ sku_4  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
52└────┴────────┴──────────────┴──────────────┴─────────────────────┘

ReplacingMergeTree

   MergeTree 的拓展，该引擎和 MergeTree 的不同之处在它会 删除【排序键值】相同重复项,根据OrderBy字段

   数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。

   有一些数据可能仍未被处理，尽管可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写

   因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现

   注意去重访问：如果是有多个分区表，只在分区内部进行去重，不会跨分区

语法

 1CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
 2(
 3    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
 4    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
 5    ...
 6) ENGINE = ReplacingMergeTree([ver])
 7[PARTITION BY expr]
 8[ORDER BY expr]
 9[SAMPLE BY expr]
10[SETTINGS name=value, ...]

去重合并树 ReplaceMergeTree 建表实操

   建表：ver表示的列只能是UInt*,Date和DateTime 类型

   删除策略：ReplacingMergeTree() 填入的参数为版本字段，重复数据就会保留版本字段值最大的。 如果不填写版本字段，默认保留插入顺序的最后一条数据

 1### 创建表 order_relace_merge_tree
 2create table soulboy_shop.order_relace_merge_tree( 
 3    id UInt32,
 4    sku_id String,
 5    out_trade_no String,
 6    total_amount Decimal(16,2), 
 7    create_time Datetime
 8) engine =ReplacingMergeTree(id)
 9  order by (sku_id)
10  partition by toYYYYMMDD(create_time) 
11  primary key (sku_id);
12
13 insert into soulboy_shop.order_relace_merge_tree values 
14(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
15(2,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
16(3,'sku_3','542323',55.02,'2023-03-01 18:00:00'), 
17(4,'sku_5','54222',2000.3,'2023-04-01 19:00:00'), 
18(5,'sku_6','53423',120.2,'2023-04-01 19:00:00'), 
19(6,'sku_7','65432',600.01,'2023-04-02 11:00:00');
20
21insert into soulboy_shop.order_relace_merge_tree values 
22(11,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
23(21,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
24(31,'sku_3','542323',55.02,'2023-03-01 18:00:00'), 
25(41,'sku_5','54222',2000.3,'2023-04-01 19:00:00'), 
26(51,'sku_8','53423',120.2,'2023-04-01 19:00:00'), 
27(61,'sku_9','65432',600.01,'2023-04-02 11:00:00');
28
29SELECT * FROM soulboy_shop.order_relace_merge_tree;
30
31
32┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
33│  1 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
34│  2 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
35│  3 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
36└────┴────────┴──────────────┴──────────────┴─────────────────────┘
37┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
38│ 11 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
39│ 21 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
40│ 31 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
41└────┴────────┴──────────────┴──────────────┴─────────────────────┘
42┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
43│  4 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
44│  5 │ sku_6  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
45└────┴────────┴──────────────┴──────────────┴─────────────────────┘
46┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
47│ 41 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
48│ 51 │ sku_8  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
49└────┴────────┴──────────────┴──────────────┴─────────────────────┘
50┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
51│  6 │ sku_7  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
52└────┴────────┴──────────────┴──────────────┴─────────────────────┘
53┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
54│ 61 │ sku_9  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
55└────┴────────┴──────────────┴──────────────┴─────────────────────┘
56
57# 手工合并
58optimize table soulboy_shop.order_relace_merge_tree final;
59
60SELECT * FROM soulboy_shop.order_relace_merge_tree;
61
62┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
63│ 11 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
64│ 21 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
65│ 31 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
66└────┴────────┴──────────────┴──────────────┴─────────────────────┘
67┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
68│ 41 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
69│  5 │ sku_6  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
70│ 51 │ sku_8  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
71└────┴────────┴──────────────┴──────────────┴─────────────────────┘
72┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
73│  6 │ sku_7  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
74│ 61 │ sku_9  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
75└────┴────────┴──────────────┴──────────────┴─────────────────────┘

SummingMergeTree

   聚合引擎 SummingMergeTree，该引擎继承自 MergeTree，区别在于，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有 相同 OrderBy 排序键 的行合并为一行，该行包含了被合并的行中具有 数值类型的列的汇总值。

   类似 group by 后使用聚合函数的效果，这个可以 显著的减少存储空间并加快数据查询的速度

   推荐将该引擎和 MergeTree 一起使用。例如在准备做数据报表的时候，将完整的数据存储在 MergeTree 表中，并且 使用SummingMergeTree 来存储聚合数据，这样做可以避免因为使用不正确的 排序健组合方式而丢失有价值的数据

   只需要查询汇总结果，不关心明细数据

   设计聚合统计表，字段全部是维度（商品）、度量（销量）或者时间戳即可，非相关的字段可以不添加

   获取汇总值，不能直接 select 对应的字段，而 需要使用 sum 进行聚合，因为自动合并的部分可能没进行，会导致一些还没来得及聚合的临时明细数据少

   在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分区下的【相同排序】的多行数据汇总合并成一行，既减少了数据行节省空间，又降低了后续汇总查询的开销

语法

1(
2name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
3name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
4...
5) ENGINE = SummingMergeTree([columns])
6[PARTITION BY expr]
7[ORDER BY expr]
8[SAMPLE BY expr]
9[SETTINGS name=value, ...]

聚合引擎 SummingMergeTree 建表实操

SummingMergeTree 的参数

• columns 包含了将要被汇总的列的列名的元组。可选参数。
• 所选的【列必须是数值类型】，具有 相同 OrderBy 排序键 的行合并为一行
• 如果没有指定 columns，ClickHouse 会把非维度列且是【数值类型的列】都进行汇总

  1### 创建表 order_summing_merge_tree
  2create table soulboy_shop.order_summing_merge_tree( 
  3    id UInt32,
  4    sku_id String,
  5    out_trade_no String,
  6    total_amount Decimal(16,2), 
  7    create_time Datetime
  8) engine =SummingMergeTree(total_amount)
  9  order by (id,sku_id)
 10  partition by toYYYYMMDD(create_time) 
 11  primary key (id);
 12
 13
 14
 15### 插入数据
 16insert into soulboy_shop.order_summing_merge_tree values 
 17(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
 18(2,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
 19(3,'sku_3','542323',55.02,'2023-03-01 18:00:00'), 
 20(4,'sku_5','54222',2000.3,'2023-04-01 19:00:00'), 
 21(5,'sku_6','53423',120.2,'2023-04-01 19:00:00'), 
 22(6,'sku_7','65432',600.01,'2023-04-02 11:00:00');
 23
 24### 第一次查询
 25491cfd7182f5 :) SELECT * FROM soulboy_shop.order_summing_merge_tree;
 26
 27SELECT *
 28FROM soulboy_shop.order_summing_merge_tree
 29
 30Query id: 1d03663c-4e48-4a58-b5b8-4196418bce35
 31
 32┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
 33│  1 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
 34│  2 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
 35│  3 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
 36└────┴────────┴──────────────┴──────────────┴─────────────────────┘
 37┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
 38│  4 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
 39│  5 │ sku_6  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
 40└────┴────────┴──────────────┴──────────────┴─────────────────────┘
 41┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
 42│  6 │ sku_7  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
 43└────┴────────┴──────────────┴──────────────┴─────────────────────┘
 44
 45### 二次插入数据
 46insert into soulboy_shop.order_summing_merge_tree values 
 47(1,'sku_1','aabbccbb',5600.00,'2023-03-01 23:09:00')
 48insert into soulboy_shop.order_summing_merge_tree values 
 49(1,'sku_1','aabbccbb',5600.00,'2023-03-01 23:09:00')
 50
 51### 二次查询
 52491cfd7182f5 :) SELECT * FROM soulboy_shop.order_summing_merge_tree;
 53
 54SELECT *
 55FROM soulboy_shop.order_summing_merge_tree
 56
 57Query id: 43711bf8-6a7f-4da0-bfbc-0c05882342ec
 58
 59┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
 60│  1 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
 61│  2 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
 62│  3 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
 63└────┴────────┴──────────────┴──────────────┴─────────────────────┘
 64┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
 65│  1 │ sku_1  │ aabbccbb     │         5600 │ 2023-03-01 23:09:00 │
 66└────┴────────┴──────────────┴──────────────┴─────────────────────┘
 67┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
 68│  1 │ sku_1  │ aabbccbb     │         5600 │ 2023-03-01 23:09:00 │
 69└────┴────────┴──────────────┴──────────────┴─────────────────────┘
 70┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
 71│  4 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
 72│  5 │ sku_6  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
 73└────┴────────┴──────────────┴──────────────┴─────────────────────┘
 74┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
 75│  6 │ sku_7  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
 76└────┴────────┴──────────────┴──────────────┴─────────────────────┘
 77
 78### 手工合并数据
 79optimize table soulboy_shop.order_summing_merge_tree final;
 80
 81### 三次查询数据（发现已合并 sku_1  16800 ）
 82491cfd7182f5 :) SELECT * FROM soulboy_shop.order_summing_merge_tree; 0J9 KI8M
 83
 84SELECT *
 85FROM soulboy_shop.order_summing_merge_tree
 86
 87Query id: a95f4507-20ca-4771-a186-8a5e46328e82
 88
 89┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
 90│  1 │ sku_1  │ aabbcc       │        16800 │ 2023-03-01 16:00:00 │
 91│  2 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
 92│  3 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
 93└────┴────────┴──────────────┴──────────────┴─────────────────────┘
 94┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
 95│  4 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
 96│  5 │ sku_6  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
 97└────┴────────┴──────────────┴──────────────┴─────────────────────┘
 98┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
 99│  6 │ sku_7  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
100└────┴────────┴──────────────┴──────────────┴─────────────────────┘
101
102### 查询汇总数据（查询时聚合）
103select sku_id,sum(total_amount) from soulboy_shop.order_summing_merge_tree group by sku_id

ClickHouse 高可用集群分片-副本

ClickHouse 的副本

• 两个相同数据的表, 作用是为了数据备份与安全，保障数据的高可用性
• 副本是 表级别的，不是整个服务器级的，服务器里 可以同时有复制表和非复制表。
• 副本不依赖分片，每个分片有它自己的独立副本。

副本写入流程

• 复制是 多主异步
• 数据会 先插入到执行该语句的服务器上，然后被复制到其他服务器。
• 由于它是 异步 的，在 其他副本上最近插入的数据会有一些延迟。
  • 如果部分副本不可用，则数据在其可用时再写入
  • 副本可用的情况下，则【延迟时长】是 通过网络传输、压缩数据块所需的时间。
• 默认情况下，INSERT 语句仅等待一个副本写入成功后返回，如果数据只成功写入一个副本，而后该副本所在的服务器不再存在，则存储的数据会丢失
• 要 启用数据写入多个副本才确认返回，使用 insert_quorum 进行配置，但是 会影响性能
• 对于 INSERT 和 ALTER 语句 操作数据的 会在压缩的情况下被复制，而 CREATE，DROP，ATTACH，DETACH 和 RENAME 语句只会在单个服务器上执行，不会被复制。

ReplicatedMergeTree(副本合并树表引擎)

• 如果有 两个副本 的话,相当于 分布在两台clickhosue节点中的两个表
• 这个 两个表具有协调功能, 无论是哪个表执行insert或者alter操作,都会同步到另外一张表，副本就是相互同步数据的表
• 副本同步需要借助zookeeper实现数据的同步, 副本节点会在zk上进行监听，但数据同步过程是不经过zk的
• zookeeper 要求 3.4.5 以及以上版本

语法

 1CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
 2(
 3    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
 4    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
 5    ...
 6    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
 7    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
 8) ENGINE = ReplicatedMergeTree('{zoo_path}', '{replica_name}')
 9ORDER BY expr
10[PARTITION BY expr]
11[PRIMARY KEY expr]
12
13
14### 示例
15
16CREATE TABLE tb_order
17(
18    EventDate DateTime,
19    CounterID UInt32,
20    UserID UInt32
21) ENGINE = ReplicatedMergeTree('/clickhouse/tables/01/tb_order', 'tb_order_01')
22PARTITION BY toYYYYMM(EventDate)
23ORDER BY (CounterID, EventDate, intHash32(UserID))
24SAMPLE BY intHash32(UserID)

只有 MergeTree 系列里的表可支持副本

ClickHouse 高可用架构准备

   高可用架构需要 ZooKeeper
   副本同步需要借助 ZooKeeper 实现数据的同步，副本节点会在ZooKeeper上进行监听，但数据同步过程是不经过ZooKeeper的

环境准备

Docker 部署 ZooKeeper

1docker run -d --name zookeeper -p 2181:2181 -t zookeeper:3.7.0

RPM 包部署 ClickHouse(版本：ClickHouse 22.1.2.2)

安装 ClickHouse 22.1.2.2

 1# 配置DNS域名解析
 2vim /etc/hosts
 3192.168.10.63   ClickHouse
 4192.168.10.64   ClickHouse2
 5
 6# 在2个节点分别上传rpm包至如下路径
 7[root@ClickHouse tmp]# pwd
 8/tmp
 9[root@ClickHouse tmp]# ls
10clickhouse-client-22.1.2.2-2.noarch.rpm
11clickhouse-common-static-22.1.2.2-2.x86_64.rpm
12clickhouse-common-static-dbg-22.1.2.2-2.x86_64.rpm
13clickhouse-server-22.1.2.2-2.noarch.rpm
14
15# 安装（默认回车）
16[root@ClickHouse tmp]# rpm -ivh *.rpm
17
18# 启动
19systemctl start clickhouse-server
20
21# 停止
22systemctl stop clickhouse-server
23
24# 重启
25systemctl restart clickhouse-server
26
27# 状态查看
28sudo systemctl status clickhouse-server
29
30# 查看端口占用，如果命令不存在 yum install -y lsof
31lsof -i :8123
32
33# 关闭防火墙
34systemctl stop firewalld
35
36# 查看日志 、错误日志
37tail -f /var/log/clickhouse-server/clickhouse-server.log
38tail -f /var/log/clickhouse-server/clickhouse-server.err.log
39
40# 开启远程访问，取消下面的注释
41vim /etc/clickhouse-server/config.xml
42
43# 编辑配置文件
44<listen_host>0.0.0.0</listen_host>
45
46# 重启
47systemctl restart clickhouse-server

ClickHouse 可视化工具连接

ClickHouse 副本配置实操

【每个节点】都需要配置

 1# 进入配置目录
 2cd /etc/clickhouse-server
 3
 4# 编辑配置文件（找到zookeeper节点，增加下面的，如果有多个zookeeper则按照顺序加即可）
 5vim /etc/clickhouse-server/config.xml
 6<zookeeper>
 7          <node>
 8                <host>192.168.10.60</host>
 9                <port>2181</port>
10          </node>
11</zookeeper>
12
13# 重启
14systemctl restart clickhouse-server
15
16# 状态查看
17sudo systemctl status clickhouse-server
18
19# 查看端口占用，如果命令不存在 yum install -y lsof
20lsof -i :8123
21
22# 查看日志 、错误日志
23tail -f /var/log/clickhouse-server/clickhouse-server.log
24tail -f /var/log/clickhouse-server/clickhouse-server.err.log

【每个机器】上创建表
    副本只能同步数据，不能同步表结构，需要在每台机器上手动建表

 1# 节点一（192.168.10.63），zookeeper路径一致，副本名称不一样
 2CREATE TABLE tb_product
 3(
 4    userId UInt32
 5) ENGINE = ReplicatedMergeTree('/clickhouse/tables/1/tb_product', 'product-replica-1')
 6ORDER BY (userId)
 7
 8# 节点二（192.168.10.64），zookeeper路径一致，副本名称不一样
 9CREATE TABLE tb_product
10(
11    userId UInt32
12) ENGINE = ReplicatedMergeTree('/clickhouse/tables/1/tb_product', 'product-replica-2')
13ORDER BY (userId)
14
15# 查询zk配置
16select * from system.zookeeper where path='/';
17
18ClickHouse2 :) select * from system.zookeeper where path='/'
19
20Query id: c0f47288-9774-4bae-943e-64e9c210d22c
21
22┌─name───────┬─value─┬─czxid─┬─mzxid─┬───────────────ctime─┬───────────────mtime─┬─version─┬─cversion─┬─aversion─┬─ephemeralOwner─┬─dataLength─┬─numChildren─┬─pzxid─┬─path─┐
23│ zookeeper  │       │     0 │     0 │ 1970-01-01 08:00:00 │ 1970-01-01 08:00:00 │       0 │       -2 │        0 │              0 │          0 │           2 │     0 │ /    │
24│ clickhouse │       │     4 │     4 │ 2024-10-04 13:22:30 │ 2024-10-04 13:22:30 │       0 │        2 │        0 │              0 │          0 │           2 │    27 │ /    │
25└────────────┴───────┴───────┴───────┴─────────────────────┴─────────────────────┴─────────┴──────────┴──────────┴────────────────┴────────────┴─────────────┴───────┴──────┘

【任意节点】插入和查询数据
   在任意节点插入数据，在另外一个节点观察数据是否可以同步

 1# ClickHouse1插入数据
 2clickhouse-client --host=192.168.10.64
 3INSERT into tb_product values(1),(2),(3)
 4
 5# ClickHouse2查询数据
 6clickhouse-client --host=192.168.10.64
 7ClickHouse2 :) select * from tb_product
 8
 9SELECT *
10FROM tb_product
11
12Query id: df0236ee-b358-44fc-b93f-7d1048e6f3dc
13
14┌─userId─┐
15│      1 │
16│      2 │
17│      3 │
18└────────┘

高可用架构之分片和 Distributed 表引擎

什么是 ClickHouse 的副本

   两个相同数据的表, 作 用是为了数据备份与安全，保障数据的高可用性，

什么是 ClickHouse 的分片

• 数据分片-允许 多台机器/节点同并行执行查询，实现了 分布式并行计算
• 分片间的数据是不同的，不同的服务器存储同一张表的不同部分,作用是为了水平切分表,缓解单节点的压力

分布式表引擎 Distributed

   Distributed 表引擎主要是用于分布式，自身不存储任何数据，数据都分散存储在某一个分片上，能够 自动路由数据 至集群中的各个节点，需要和其他数据表引擎一起协同工作

   一张 分布式表 底层会对应多个 分片数据表，由具体的分片表存储数据，分布式表 与 分片表 是一对多的关系

   分布式表主要有 本地表（xxx_local） 和 分布式表（xxx_all） 两部分组成

语法

1CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
2(
3name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
4name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
5...
6) ENGINE = Distributed(cluster, database, table[, sharding_key[, policy_name]])
7[SETTINGS name=value, ...]

多分片表查询
   本着 谁执行谁负责 的原则，向 ClickHouse 发起分布式表执行 SELECT * FROM distributed_table

   它会转为如下形式 SELECT * FROM local_table，先 执行本地分片，再 发送远端各分片执行

   最后 合并结果为临时表返回

分片配置详解 config.xml
   /etc/clickhouse-server/config.xml

 1<remote_servers>
 2    <logs>
 3        <shard>
 4            <!-- 可选的。写数据时分片权重。 默认: 1. -->
 5            <weight>1</weight>
 6            <!-- 可选的。是否只将数据写入其中一个副本。默认值:false(将数据写入所有副本)。 -->
 7            <internal_replication>false</internal_replication>
 8            
 9            <replica>
10<!-- 可选的。负载均衡副本的优先级，请参见（load_balancing 设置)。默认值:1(值越小优先级越高)。 -->
11                <priority>1</priority>
12                <host>example01-01-1</host>
13                <port>9000</port>
14            </replica>
15            
16            <replica>
17                <host>example01-01-2</host>
18                <port>9000</port>
19            </replica>
20        </shard>
21
22        <shard>
23            <weight>2</weight>
24            <internal_replication>false</internal_replication>
25
26            <replica>
27                <host>example01-02-1</host>
28                <port>9000</port>
29            </replica>
30            
31            <replica>
32                <host>example01-02-2</host>
33                <secure>1</secure>
34                <port>9440</port>
35            </replica>
36        </shard>
37    </logs>
38</remote_servers>

【每个机器】上配置
   /etc/clickhouse-server/config.xml

 1# 编辑配置文件
 2vim /etc/clickhouse-server/config.xml
 3
 4<!-- 2_Shard 1_Replica -->
 5    <cluster_2shards_1replicas>
 6        <!-- shard1  -->
 7        <shard>
 8            <replica>
 9                <host>192.168.10.63</host>
10                <port>9000</port>
11            </replica>
12        </shard>
13        
14        <!-- shard2  -->
15        <shard>
16            <replica>
17                <host>192.168.10.64</host>
18                <port> 9000</port>
19            </replica>
20        </shard>
21        
22    </cluster_2shards_1replicas>
23
24
25# 每个节点重启ClickHouse
26systemctl restart clickhouse-server
27
28# 判断配置是否成功（重启ClickHouse后能查询到） 2个节点都可以查看到
29clickhouse-client --host=192.168.10.64
30select * from system.clusters
31
32┌─cluster─────────────────────────────────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name─────┬─host_address──┬─port─┬─is_local─┬─user────┬─default_database─┬─errors_count─┬─slowdowns_count─┬─estimated_recovery_time─┐
33│ cluster_2shards_1replicas                       │         1 │            1 │           1 │ 192.168.10.63 │ 192.168.10.63 │ 9000 │        0 │ default │                  │            0 │               0 │                       0 │
34│ cluster_2shards_1replicas                       │         2 │            1 │           1 │ 192.168.10.64 │ 192.168.10.64 │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
35│ test_cluster_one_shard_three_replicas_localhost │         1 │            1 │           1 │ 127.0.0.1     │ 127.0.0.1     │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
36│ test_cluster_one_shard_three_replicas_localhost │         1 │            1 │           2 │ 127.0.0.2     │ 127.0.0.2     │ 9000 │        0 │ default │                  │            0 │               0 │                       0 │
37│ test_cluster_one_shard_three_replicas_localhost │         1 │            1 │           3 │ 127.0.0.3     │ 127.0.0.3     │ 9000 │        0 │ default │                  │            0 │               0 │                       0 │
38│ test_cluster_two_shards                         │         1 │            1 │           1 │ 127.0.0.1     │ 127.0.0.1     │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
39│ test_cluster_two_shards                         │         2 │            1 │           1 │ 127.0.0.2     │ 127.0.0.2     │ 9000 │        0 │ default │                  │            0 │               0 │                       0 │
40│ test_cluster_two_shards_internal_replication    │         1 │            1 │           1 │ 127.0.0.1     │ 127.0.0.1     │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
41│ test_cluster_two_shards_internal_replication    │         2 │            1 │           1 │ 127.0.0.2     │ 127.0.0.2     │ 9000 │        0 │ default │                  │            0 │               0 │                       0 │
42│ test_cluster_two_shards_localhost               │         1 │            1 │           1 │ localhost     │ ::1           │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
43│ test_cluster_two_shards_localhost               │         2 │            1 │           1 │ localhost     │ ::1           │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
44│ test_shard_localhost                            │         1 │            1 │           1 │ localhost     │ ::1           │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
45│ test_shard_localhost_secure                     │         1 │            1 │           1 │ localhost     │ ::1           │ 9440 │        0 │ default │                  │            0 │               0 │                       0 │
46│ test_unavailable_shard                          │         1 │            1 │           1 │ localhost     │ ::1           │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
47│ test_unavailable_shard                          │         2 │            1 │           1 │ localhost     │ ::1           │    1 │        0 │ default │                  │            0 │               0 │                       0 │
48└─────────────────────────────────────────────────┴───────────┴──────────────┴─────────────┴───────────────┴───────────────┴──────┴──────────┴─────────┴──────────────────┴──────────────┴─────────────────┴─────────────────────────┘

建表实操

 1# 【选一个节点】 创建好本地表后，在1个节点创建，会自动同步到其他节点
 2create table default.soulboy_order on cluster cluster_2shards_1replicas
 3(id Int8,name String) engine =MergeTree order by id;
 4
 5
 6# 【选一个节点】 创建分布式表名 soulboy_order_all，在1个节点创建，会自动同步到其他节点
 7create table soulboy_order_all on cluster cluster_2shards_1replicas (
 8id Int8,name String
 9)engine = Distributed(cluster_2shards_1replicas,default, soulboy_order,hiveHash(id));
10
11# 通过分布式表插入
12insert into soulboy_order_all values(1,'里昂'),(2,'笨象'),(3,'巨象'),(4,'程书远');
13
14# 【任意节点查询-分布式，全部数据】
15SELECT * from soulboy_order_all
16┌─id─┬─name─┐
17│  1 │ 里昂 │
18│  3 │ 巨象 │
19└────┴──────┘
20┌─id─┬─name───┐
21│  2 │ 笨象   │
22│  4 │ 程书远 │
23└────┴────────┘
24
25# 【ClickHouse1查询，部分数据】
26SELECT * from soulboy_order
27┌─id─┬─name───┐
28│  2 │ 笨象   │
29│  4 │ 程书远 │
30└────┴────────┘
31
32# 【ClickHouse2查询，部分数据】
33SELECT * from soulboy_order
34┌─id─┬─name─┐
35│  1 │ 里昂 │
36│  3 │ 巨象 │
37└────┴──────┘

向集群中写入数据的两种方法

SpringBoot2.X 整合 ClickHouse 实操

   HTTP 接口允许您在任何编程语言的任何平台上使用 ClickHouse。我们使用它在 Java 和 Perl 以及 shell 脚本中工作。在其他部门中，HTTP 接口用于 Perl、Python 和 Go。HTTP 接口比原生接口 受到更多的限制，但它具有更好的兼容性。

在线创建项目
    start.spring.io

引入依赖

 1<!-- Spring Web -->
 2		<dependency>
 3			<groupId>org.springframework.boot</groupId>
 4			<artifactId>spring-boot-starter-web</artifactId>
 5		</dependency>
 6		<!-- lombok -->
 7		<dependency>
 8			<groupId>org.projectlombok</groupId>
 9			<artifactId>lombok</artifactId>
10			<optional>true</optional>
11		</dependency>
12		<!-- test -->
13		<dependency>
14			<groupId>org.springframework.boot</groupId>
15			<artifactId>spring-boot-starter-test</artifactId>
16			<scope>test</scope>
17		</dependency>
18		<!-- clickhouse-jdbc -->
19		<dependency>
20			<groupId>ru.yandex.clickhouse</groupId>
21			<artifactId>clickhouse-jdbc</artifactId>
22			<version>0.1.55</version>
23		</dependency>
24		<!--mybatis plus-->
25		<dependency>
26			<groupId>com.baomidou</groupId>
27			<artifactId>mybatis-plus-boot-starter</artifactId>
28			<version>3.4.0</version>
29		</dependency>

application.properties

1server.port=8080
2
3spring.datasource.driver-class-name=ru.yandex.clickhouse.ClickHouseDriver
4spring.datasource.url=jdbc:clickhouse://192.168.10.63:8123/default
5
6mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
7
8logging.level.root=INFO

ClickHouse 项目数据统计需求分析

需求背景

   访问商品日志记录 数据，经过 Flink实时计算轻度聚合 之后，存储到 ClickHouse

需求	说明
统计商品访问 pv 数	指定商品，统计指定日期范围内，各个省市的访问的pv数量，如7天内有各个城市访问某个商品分布
商品访问量波动图	指定商品，多天内的访问曲线变化图，如，1~7号商品访问量波动图

前端显示图标组件（Charts）

库表和数据准备

建表语句

 1CREATE TABLE default.visit_stats
 2(
 3    `product_id` UInt64,
 4    `is_new` UInt16,
 5    `province` String,
 6    `city` String,
 7    `pv` UInt32,
 8    `visit_time` DateTime
 9)
10ENGINE = MergeTree()
11PARTITION BY toYYYYMMDD(visit_time)
12ORDER BY (
13 product_id,
14 is_new,
15 province,
16 city
17 );

插入数据

 1### 0代表老用户，1代表新用户，PV是访问量（轻度聚合后）、访问时间
 2INSERT into visit_stats values
 3('1','1','广东','广州',14323,'2023-01-01 12:11:13'),
 4('1','0','广东','广州',4232,'2023-02-12 16:16:13'),
 5('1','1','广东','佛山',54323,'2023-03-06 16:11:13'),
 6('1','0','广东','东莞',42341,'2023-03-02 16:12:13'),
 7('1','1','广东','梅州',52422,'2023-03-09 12:11:13'),
 8
 9('2','1','广东','广州',14323,'2021-03-01 12:11:13'),
10('2','0','广东','深圳',425232,'2023-04-12 16:16:13'),
11('2','1','广东','佛山',543323,'2022-06-06 16:11:13'),
12('2','0','广东','东莞',42341,'2021-05-02 16:12:13'),
13('2','1','广东','梅州',52422,'2022-01-09 12:11:13'),
14
15('3','1','北京','北京',13132,'2023-01-01 12:11:13'),
16('3','0','广东','广州',533232,'2022-02-16 16:16:13'),
17('4','1','浙江','杭州',663643,'2023-12-06 12:11:13'),
18('4','0','广东','东莞',4142,'2023-11-02 16:12:13'),
19('5','1','湖南','长沙',52123,'2022-01-09 12:11:13'),
20('4','0','湖南','衡阳',4142,'2024-05-02 16:12:13'),
21('5','1','广东','中山',52123,'2024-01-09 12:11:13'),
22
23('2','1','上海','上海',14323,'2021-03-01 12:11:13'),
24('5','0','浙江','宁波',425232,'2023-04-12 16:16:13'),
25('3','1','广东','佛山',543323,'2022-06-06 16:11:13'),
26('2','0','湖南','长沙',42341,'2021-05-02 16:12:13'),
27('2','1','广东','深圳',52422,'2022-01-09 12:11:13')

查询

1SELECT * FROM visit_stats;

函数编写统计 SQL

函数
   ClickHouse 中至少存在两种类型的函数 - 常规函数（它们称之为«函数»）和聚合函数。 常规函数的工作就像分别为每一行执行一次函数计算一样（对于每一行，函数的结果不依赖于其他行）。 聚合函数则从各行累积一组值（即函数的结果以来整个结果集）。

统计需求
   某个商品在一定的时间范围内（天级别），各地区（城市级别）访问分布

 1# 连接
 2clickhouse-client --host=192.168.10.63
 3
 4# 某个商品在一定的时间范围内（天级别），各地区（城市级别）访问分布
 5select province,city, sum(pv) pv_count  
 6from visit_stats where  product_id =1 
 7and toYYYYMMDD(visit_time) BETWEEN '20200101' and '20241212' 
 8group by province,city order by pv_count desc
 9
10┌─province─┬─city─┬─pv_count─┐
11│ 广东     │ 佛山 │    54323 │
12│ 广东     │ 梅州 │    52422 │
13│ 广东     │ 东莞 │    42341 │
14│ 广东     │ 广州 │    18555 │
15└──────────┴──────┴──────────┘
16
17# 全部商品在一定的时间范围内（天级别），各地区（城市级别）访问分布
18select province,city, sum(pv) pv_count  
19from visit_stats where toYYYYMMDD(visit_time) BETWEEN '20200101' and '20241212' 
20group by province,city order by pv_count desc
21
22┌─province─┬─city─┬─pv_count─┐
23│ 广东     │ 佛山 │  1140969 │
24│ 浙江     │ 杭州 │   663643 │
25│ 广东     │ 广州 │   566110 │
26│ 广东     │ 深圳 │   477654 │
27│ 浙江     │ 宁波 │   425232 │
28│ 广东     │ 梅州 │   104844 │
29│ 湖南     │ 长沙 │    94464 │
30│ 广东     │ 东莞 │    88824 │
31│ 广东     │ 中山 │    52123 │
32│ 上海     │ 上海 │    14323 │
33│ 北京     │ 北京 │    13132 │
34│ 湖南     │ 衡阳 │     4142 │
35└──────────┴──────┴──────────┘

统计需求

   某个商品，多天内的访问曲线图（天级别）

 1### 某个商品，多天内的访问曲线图（天级别）
 2select 
 3  toYYYYMMDD(visit_time) date_time_str, 
 4  sum(pv) pv_count 
 5 from visit_stats
 6 where product_id = 1 and toYYYYMMDD(visit_time) BETWEEN '20200101' and '20241212' 
 7 group by date_time_str ORDER BY date_time_str desc
 8
 9┌─date_time_str─┬─pv_count─┐
10│      20230309 │    52422 │
11│      20230306 │    54323 │
12│      20230302 │    42341 │
13│      20230212 │     4232 │
14│      20230101 │    14323 │
15└───────────────┴──────────┘

SpringBoot2.X 整合 ClickHouse 基础模块搭建

响应工具类 JsonData（封装响应结果给前端）

 1import lombok.AllArgsConstructor;
 2import lombok.Data;
 3import lombok.NoArgsConstructor;
 4
 5@Data
 6@AllArgsConstructor
 7@NoArgsConstructor
 8public class JsonData {
 9
10    /**
11     * 状态码 0 表示成功
12     */
13
14    private Integer code;
15    /**
16     * 数据
17     */
18    private Object data;
19    /**
20     * 描述
21     */
22    private String msg;
23
24
25    /**
26     * 成功，不传入数据
27     *
28     * @return
29     */
30    public static JsonData buildSuccess() {
31        return new JsonData(0, null, null);
32    }
33
34    /**
35     * 成功，传入数据
36     *
37     * @param data
38     * @return
39     */
40    public static JsonData buildSuccess(Object data) {
41        return new JsonData(0, data, null);
42    }
43
44    /**
45     * 失败，传入描述信息
46     *
47     * @param msg
48     * @return
49     */
50    public static JsonData buildError(String msg) {
51        return new JsonData(-1, null, msg);
52    }
53
54
55    /**
56     * 自定义状态码和错误信息
57     *
58     * @param code
59     * @param msg
60     * @return
61     */
62    public static JsonData buildCodeAndMsg(int code, String msg) {
63        return new JsonData(code, null, msg);
64    }
65
66
67}

Model 层

VisitStatsDO 类

 1import lombok.Data;
 2
 3@Data
 4public class VisitStatsDO {
 5
 6    /**
 7     * 商品
 8     */
 9    private Long productId;
10
11    /**
12     * 访问时间
13     */
14    private String visitTime;
15
16
17    /**
18     * 1是新访客，0是老访客
19     */
20    private Integer isNew;
21
22    /**
23     * 访问量
24     */
25    private Integer pv;
26
27    /**
28     * 省份
29     */
30    private String province;
31
32    /**
33     * 城市
34     */
35    private String city;
36
37    /**
38     * ========度量值=========
39     */
40    private Long pvCount=0L;
41
42    /**
43     * 时间的字符串映射，天、小时
44     */
45    private String dateTimeStr;
46}

Mapper 接口

VisitStatsMapper 接口类

 1import com.baomidou.mybatisplus.core.mapper.BaseMapper;
 2import net.xdclass.demo.model.VisitStatsDO;
 3import org.apache.ibatis.annotations.Mapper;
 4import org.apache.ibatis.annotations.Param;
 5
 6import java.util.List;
 7
 8@Mapper
 9public interface VisitStatsMapper extends BaseMapper<VisitStatsDO> {
10
11    /**
12     * 统计总条数
13     * @param productId
14     * @return
15     */
16    int countTotal(@Param("productId") long productId);
17
18    /**
19     * 分页查询
20     * @param productId
21     * @param from
22     * @param size
23     * @return
24     */
25    List<VisitStatsDO> pageVisitRecord(@Param("productId") Long productId , @Param("from") int from, @Param("size") int size);
26
27
28
29    /**
30     * 查询地区访问分布
31     * @param productId
32     * @param startTime
33     * @param endTime
34     * @return
35     */
36    List<VisitStatsDO> queryRegionTrendWithMultiDay(@Param("productId")Long productId, @Param("startTime") String startTime,  @Param("endTime")String endTime);
37
38    /**
39     * 查询访问趋势，多天内
40     * @param productId
41     * @param startTime
42     * @param endTime
43     * @return
44     */
45    List<VisitStatsDO> queryVisitTrendWithMultiDay(@Param("productId")Long productId, @Param("startTime") String startTime,  @Param("endTime")String endTime);
46}

VisitStatsMapper.xml
   D:\Project\ClickHouse\src\main\resources\mapper\VisitStatsMapper.xml

 1<?xml version="1.0" encoding="UTF-8"?>
 2<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3<mapper namespace="net.xdclass.demo.mapper.VisitStatsMapper">
 4
 5    <!-- 通用查询映射结果 -->
 6    <resultMap id="BaseResultMap" type="net.xdclass.demo.model.VisitStatsDO">
 7        <result column="product_id" property="productId"/>
 8        <result column="is_new" property="isNew"/>
 9        <result column="province" property="province"/>
10        <result column="city" property="city"/>
11        <result column="pv_count" property="pvCount"/>
12        <result column="visit_time" property="visitTime"/>
13        <result column="date_time_str" property="dateTimeStr"/>
14    </resultMap>
15
16    <!-- 通用查询结果列 -->
17    <sql id="Base_Column_List">
18        product_id,is_new,province,city,pv,visit_time
19    </sql>
20
21    <!-- 统计总条数 -->
22    <select id="countTotal" resultType="java.lang.Integer">
23        select count(1) from visit_stats  where   product_id=#{productId}
24    </select>
25
26    <!-- 分页查找 -->
27    <select id="pageVisitRecord" resultMap="BaseResultMap">
28        select
29        <include refid="Base_Column_List"/>
30        from visit_stats where product_id=#{productId}
31        order by visit_time desc limit #{from},#{size}
32    </select>
33
34    <!-- 某个商品，多天内的访问曲线图（天级别）: 按城市查询 -->
35    <select id="queryRegionTrendWithMultiDay" resultMap="BaseResultMap">
36        select province ,city, sum(pv) pv_count from visit_stats
37        where product_id = #{productId} and toYYYYMMDD(visit_time) BETWEEN #{startTime} and #{endTime}
38        GROUP BY province ,city  order by pv_count desc
39    </select>
40
41    <!-- 某个商品，多天内的访问曲线图（天级别）: 按时间查询 -->
42    <select id="queryVisitTrendWithMultiDay" resultMap="BaseResultMap">
43        select toYYYYMMDD(visit_time) date_time_str,sum(pv) pv_count from visit_stats
44        where product_id = #{productId} and toYYYYMMDD(visit_time) BETWEEN #{startTime} and #{endTime}
45        GROUP BY date_time_str order by date_time_str desc
46    </select>
47
48</mapper>

Service 层

VisitStatsService 接口

 1import net.xdclass.demo.controller.request.VisitRecordPageRequest;
 2import net.xdclass.demo.controller.request.VisitTrendQueryRequest;
 3import net.xdclass.demo.model.VisitStatsDO;
 4
 5import java.util.List;
 6import java.util.Map;
 7
 8public interface VisitStatsService {
 9
10    /**
11     * 分页查询
12     * @param pageRequest
13     * @return
14     */
15    Map<String, Object> pageVisitRecord(VisitRecordPageRequest pageRequest);
16
17
18    /**
19     * 某个商品，多天内的访问曲线图（天级别） 按城市 or 时间
20     * @param queryRequest
21     * @return
22     */
23    List<VisitStatsDO> queryVisitTrend(VisitTrendQueryRequest queryRequest);
24}

VisitStatsServiceImpl 实现类

 1import org.springframework.beans.factory.annotation.Autowired;
 2import org.springframework.stereotype.Service;
 3import java.util.HashMap;
 4import java.util.List;
 5import java.util.Map;
 6
 7@Service
 8public class VisitStatsServiceImpl implements VisitStatsService {
 9
10    @Autowired
11    private VisitStatsMapper visitStatsMapper;
12
13    /**
14     * 分页查询
15     * @param pageRequest
16     * @return
17     */
18    @Override
19    public Map<String, Object> pageVisitRecord(VisitRecordPageRequest pageRequest) {
20        Map<String, Object> data = new HashMap<>(3);
21        //获取商品id
22        long productId = pageRequest.getProductId();
23        //当前页数
24        int page = pageRequest.getPage();
25        //每页多少条
26        int size = pageRequest.getSize();
27        //统计总记录数
28        int count = visitStatsMapper.countTotal(productId);
29        //分页查询
30        int from = (page - 1) * size;
31        List<VisitStatsDO> visitStatsDOS = visitStatsMapper.pageVisitRecord(productId, from, size);
32        //将信息封装到Map中返回
33        data.put("total", count); //总记录数
34        data.put("current_page", page); //当前页
35        data.put("data", visitStatsDOS); //当前页数据
36        //计算总页数
37        int totalPage;
38        if (count % size == 0) {
39            totalPage = count / size;
40        } else {
41            totalPage = count / size + 1;
42        }
43        data.put("total_page", totalPage);
44        return data;
45    }
46
47    /**
48     * 某个商品，多天内的访问曲线图（天级别） 支持按照城市 or 时间 查询
49     * @param queryRequest
50     * @return
51     */
52    @Override
53    public List<VisitStatsDO> queryVisitTrend(VisitTrendQueryRequest queryRequest) {
54        long productId = queryRequest.getProductId();
55        String type = queryRequest.getType();
56        List<VisitStatsDO> list = null;
57
58        //判断type 城市或者时间
59        if (type.equalsIgnoreCase("region")) {
60            list = visitStatsMapper.queryRegionTrendWithMultiDay(productId,queryRequest.getStartTime(),queryRequest.getEndTime());
61        } else if (type.equalsIgnoreCase("day")) {
62            list = visitStatsMapper.queryVisitTrendWithMultiDay(productId,queryRequest.getStartTime(),queryRequest.getEndTime());
63        }
64        return list;
65    }
66}

Controller 层

分页查询参数接收类 VisitRecordPageRequest

 1import lombok.Data;
 2
 3@Data
 4public class VisitRecordPageRequest {
 5
 6    //商品id
 7    private long productId;
 8
 9    //显示当前第几页
10    private int page;
11
12    //每页显示多少条
13    private int size;
14}

分页查询参数接收类 VisitTrendQueryRequest

 1import lombok.Data;
 2
 3@Data
 4public class VisitTrendQueryRequest {
 5
 6    private long productId;
 7
 8    private String startTime;
 9
10    private String endTime;
11
12    /**
13     * 查询类型: 时间分布 or 城市分布
14     */
15    private String type;
16}

DataController 类

 1import org.springframework.beans.factory.annotation.Autowired;
 2import org.springframework.web.bind.annotation.RequestBody;
 3import org.springframework.web.bind.annotation.RequestMapping;
 4import org.springframework.web.bind.annotation.RestController;
 5
 6import java.util.List;
 7import java.util.Map;
 8
 9@RestController
10@RequestMapping("/api/v1/data")
11public class DataController {
12
13    @Autowired
14    private VisitStatsService visitStatsService;
15
16    /**
17     * 分页查询
18     * @param pageRequest
19     * @return
20     */
21    @RequestMapping("page")
22    public JsonData queryVisitRecord(@RequestBody VisitRecordPageRequest pageRequest) {
23        Map<String, Object> map = visitStatsService.pageVisitRecord(pageRequest);
24        return JsonData.buildSuccess(map);
25    }
26
27
28    /**
29     * 某个商品，多天内的访问曲线图（天级别）
30     * @param queryRequest
31     * @return
32     */
33    @RequestMapping("trend")
34    public JsonData queryVisitTrend(@RequestBody VisitTrendQueryRequest queryRequest){
35        List<VisitStatsDO> list = visitStatsService.queryVisitTrend(queryRequest);
36        return JsonData.buildSuccess(list);
37    }
38
39
40}

测试结果

分页测试
   http://192.168.10.88:8080/api/v1/data/page

某个商品，多天内的访问曲线图（天级别） 按照城市查询
   http://192.168.10.88:8080/api/v1/data/trend

某个商品，多天内的访问曲线图（天级别） 按照时间查询
   http://192.168.10.88:8080/api/v1/data/trend

建表和引擎选择归纳

   主要是数据仓库用比较多，一般都是 MergeTree系列引擎，没有最好，根据业务选择最合适的

• MergeTree

  • 去重合并树引擎 ReplacingMergeTree
    • 可以保证幂等性处理
  • 聚合合并树引擎 SummingMergeTree
    • 可以进行预聚合，性能更高
    • 重复作业会导致数据累计

• Log 系列

  • 说明：最小功能的​轻量级引擎，​当需要快速写入许多小表并在以后整体读取它们时效果最佳，即一次写入多次查询，使用大量的小表存储数据，性能会很低，生产环境使用少，练习为主
  • 这些引擎是为了需要写入许多小数据量（少于一百万行）的表的场景而开发的
  • 将数据存储在磁盘上，不支持索引，数据写入时追加写到文件末尾
  • 该引擎没有并发控制，不允许同时读写，当向表中写入数据时，这张表的查询会被阻塞，直至写入完成
  • 不支持原子写，如果写过程中断，可能会损坏表数据
  • 包括：TinyLog、Log、StripLog

• 主键选择

  • 主键可以重复，也可以是多列的组合
  • 如果未指明主键，则使用 order by 字段作为主键
  • 业务里面 where 条件常用的列，频率高的字段排前面

• 排序键选择

  • 业务里面 where 条件常用的列，频率高的字段排前面

• 分区键选择

  • 常规根据时间进行分区，分区字段类型建议是 DateTime 类型，不要是 Long 类型
  • 不能分区粒度太细，比如秒级别分区键，则插入很容易报错，

常见注意事项和异常问题排查

• 注意点一

  • 查询要使用的列，避免 select * 全部字段，浪费 IO 资源

• 注意点二

  • 避免大量的小批量数据，插入更新操作，会导致分区过多
    • 每次插入 1 条，产生了一个分区，大量写入产生大量临时分区和合并操作浪费资源

• 注意点三

  • JOIN 操作时一定要把数据量小的表放在右边，无论是 Left Join 、Right Join 还是 Inner Join，右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表

• 注意点四

  • 批量写入数据时，控制每个批次的数据中涉及到的分区的数量，无序的数据导致涉及的分区太多，建议写入之前最好对需要导入的数据进行排序

• 注意点五

  • 写入分布式表还是本地表? 建议：数据量不大，写入本地表和分布式表都行

1分布式表不存储数据，本地表存储数据的表
2
3大量数据，日新增500万行以上，分布式表在写入时会在本地节点生成临时数据，会产生写放大，所以会对CPU及内存造成一些额外消耗，服务器merge的工作量增加, 导致写入速度变慢；
4
5数据写入默认是异步的（可以开启同步写但性能会影响），先在分布式表所在的机器进行落盘, 然后异步的发送到本地表所在机器进行存储，中间没有一致性的校验，短时间内可能造成不一致，且如果分布式表所在机器时如果机器出现down机, 会存在数据丢失风险。
6
7建议大数据量尽量少使用分布式表进行写操作，可以根据业务规则均衡的写入本地表；
8  
9必须用的话尽量只用读，因为写分布式表对性能影响非常大

• 注意点六

  • 单 SQL 查询可以压榨 CPU ，但并发多条查询则不是很强
  • 一个分区查询占据一个 CPU，业务需要查询表的多个分区可以多个 CPU 并行处理

• 注意点七

  • 没有完整的事务支持，不支持 Transaction
  • OLAP 类业务由于数据量非常大，建议数据批量写入，尽量避免数据更新或少更新

• 注意点八

  • 在分布式模式下，ClickHouse 会将数据分为多个分片，并且分布到不同节点上，有哪种分片策略
  • ClickHouse 提供了丰富的 sharding 策略，让业务可以根据实际需求选用
    • random 随机分片:写入数据会被随机分发到分布式集群中的某个节点上
    • constant 固定分片:写入数据会被分发到固定一个节点上
    • hash column value 分片：按照某一列的值进行 hash 分片

常见异常问题

• 错误码 300，Too many parts

1写入频率过快，使用了不合理的分区键导致总的 part 数目太多，好比-soulboy，直接拿精确到秒的 timestamp 来作为分区键来进行分区，GG了。

• 错误码 252，Too many partitions for single INSERT block (more than 100)

1同一批次写入里包括大于100个分区值，clickhouse认为这样会存在性能问题
2
3让数据是按照天/小时分区的，一批数据里的日期跨度为一年，单次插入可能产生365个分区，导致后台异步合并数据出现问题，也避免跨度过大
4
5解决方案：单批次写入数据，要控制写入分区过多
6
7参数：max_partitions_per_insert_block 限制单个插入块中的最大分区数，默认是100

ClickHouse 高性能查询原因剖析-稀疏索引

密集索引
   在密集索引中，数据库中的每个键值都有一个索引记录，可以加快搜索速度，但需要更多空间来存储索引记录本身，索引记录 包含 键值 和 指向磁盘上实际记录的指针，索引项是连续的。

稀疏索引
   在稀疏索引中，不会为每个关键字创建索引记录，而是 为数据记录文件的每个存储块设一个键-指针对，存储块意味着 块内存储单元连续

案例

• MySQL 的 MyISAM 引擎里面， 使用均为 稀疏索引；
• MySQL 的 Innodb 引擎里面，如果有主键，则主键为 密集索引
• Kafka 里面的索引文件也是采用 稀疏索引 进行构造消息索引
• ClickHouse 的合并树 MergeTree 引擎是 稀疏索引，默认 index_granularity 设置 8192，新版本的提供了自适应粒度大小的特性

1建表语句最后加这个，可以调整
2SETTINGS index_granularity = 8192

总结

• ClickHouse 一级索引就是【稀疏索引】，可以 大幅减少索引占用的空间
  • 默认的 索引力度8192，假如 1 亿行数据只需要存储 12208 行索引，占用空间小，clickhouse 中 ` 一级索引的数据是常驻内存的，取用速度极快
  • 稀疏索引所占空间小，并且 插入和删除时所需维护的开销也小，缺点是 速度比密集索引慢一点
• 密集索引 空间占用多，比稀疏索引更快的定位一条记录，缺点就是会占用较多的空间
• 不变思想：时间换空间、空间换时间

ClickHouse 高性能写入剖析-LSM-Tree 数据结构

磁盘顺序读写和随机读写的性能差距大概是 1 千到 5 千倍之间

• 连续 I/O 顺序读写，磁头几乎不用换道，或者换道的时间很短，性能很高，比如 0.03 * 2000 MB /s
• 随机 I/O 随机读写，会导致磁头不停地换道，造成效率的极大降低，0.03MB/s

ClickHouse 中的 MergeTree 也是类 LSM 树的思想

• 充分利用了 磁盘顺序写的特性，实现高吞吐写能力，数据写入后 定期在后台Compaction
• 在数据导入时全部是顺序 append 写，在后台合并时也是多个段 merge sort 后顺序写回磁盘
• 官方公开 benchmark 测试显示能够达到 50MB-200MB/s的写入吞吐能力，按照每行100Byte估算，大约相当于50W-200W条/s的写入速度

什么是 LSM-Tree

   全称 Log-Structured Merge-Tree 日志结构合并树，但不是树，而是 利用磁盘顺序读写能力，实现一个多层读写的存储结构

• 是一种分层，有序，面向磁盘的数据结构，核心思想是 利用了磁盘批量的顺序写要远比随机写性能高出很多
• 大大 提升了数据的写入能力，但会 牺牲部分读取性能为代价
• HBase、LevelDB、ClickHouse 这些 NoSQL 存储都是 采用的类LSM树结构
• 在 NoSQL 系统里非常常见，基本已经成为必选方案, 为了解决快速读写的问题去设计的

分两个部分理解

• Log-Structured 日志结构的，打印日志是一行行往下写，不需要更改，只需要在后边追加就好了
• Merge-tree ，合并就是把多个合成一个，自上而下

LSM-tree 是一个多层结构，就更一个喷泉树一样，上小下大

• 专门为 key-value 存储系统设计的，最主要的就两个个功能
  • 写入 put（k，v）
  • 查找 get（k）得到 v
• 首先是内存的 C0 层，保存了所有最近写入的 （k，v），这个 内存结构是有序的，且可以 随时原地更新，同时 支持随时查询
• 接下去的 C1 到 Cn 层都在磁盘上，每一层都是一个有序的存储结构
• 降低一点读性能，通过牺牲小部分读性能，换来高性能写

写入流程

• put 操作，首先追加到写前日志（Write Ahead Log），然后加到 C0 层
• 当 C0 层的数据达到一定大小，就把 C0 层 和 C1 层合并，这个过程就是 Compaction（合并）
• 合并出来的新的 C1 会顺序写磁盘，替换掉原来的 C1
• 当 C1 层达到一定大小，会和下层继续合并，合并后删除旧的，留下新的

查询流程

• 最新的数据在 C0 层，最老的数据在 Cn 层
• 查询也是先查 C0 层，如果没有要查的数据，再查 C1，逐层查下去直到最后一层（通过牺牲小部分读性能，换来高性能写）

缺点

• 读放大：读取数据时实际 读取的数据量大于真正的数据量，在 LSM 树中需要先 在C0查看当前key是否存在，不存在继续从Cn层中寻找
• 写放大：写入数据时实际 写入的数据量大于真正的数据量，在 LSM 树中写入时可能触发 Compact 操作，导致实际写入的数据量远大于该 key 的数据量