clickhouse入门

概述

ClickHouse 是一款由 Yandex 开发的 列式存储 OLAP（在线分析处理）数据库，核心优势是 高吞吐、低延迟的海量数据实时分析能力，尤其擅长处理 PB 级结构化数据的聚合查询。

https://clickhouse.com/docs/zh

https://github.com/ClickHouse/ClickHouse

列式存储 与 行式存储

数据如何“摆放”

数据在磁盘 / 内存中以 “页”（Page，通常 4KB-64KB）为基本存储单元，两种存储架构的核心区别在于 “一页中存储的是一行数据的所有列，还是一列数据的所有行”。

行式存储（Row-wise Storage）

  • 组织逻辑：按 “行” 为单位存储数据，一页中包含完整的多行数据（每行的所有列字段连续存储）。

    例如，存储 “用户表（ID、姓名、年龄、注册时间）” 时，行式存储的一页可能包含：

    1
    [1, 张三, 25, 2023-01-01] → [2, 李四, 30, 2023-02-01] → [3, 王五, 28, 2023-03-01]

  • 典型代表：MySQL、PostgreSQL、Oracle（传统 OLTP 数据库）。

列式存储（Columnar Storage）

  • 组织逻辑：按 “列” 为单位存储数据，一页中包含同一列的多行数据（不同列的页分开存储）。

    同样存储 “用户表”，列式存储会将各列拆分为独立的页：

    • 年龄列页：[25, 30, 28, ...]
    • 注册时间列页：[2023-01-01, 2023-02-01, 2023-03-01, ...]
    • 其他列同理，通过 “行索引” 关联同一行的不同列数据。

  • 典型代表：ClickHouse、HBase、Vertica、Redshift（OLAP 分析型数据库）。

总结

  • 行式存储：“一行数据打包存，单行读写快，适合做事务”；
  • 列式存储：“一列数据单独存，聚合计算快，适合做分析”。

OLAP 与 OLTP

1. OLTP（Online Transaction Processing 在线事务处理）：“处理日常事务，保证数据准确”

OLTP 是面向业务操作层的数据库架构，核心目标是支持高频、实时的单行 / 少量行数据读写，确保事务的一致性和数据的准确性，相当于企业的 “日常业务操作系统”。

  • 典型场景：用户注册、订单创建、支付转账、修改个人信息 —— 这些操作都是 “单次、小额” 的事务，需要快速响应且不能出错（比如支付不能重复扣款）。
  • 数据视角：处理的是 “实时产生的业务明细数据”（如每一笔订单、每一次点击），数据量通常是 “单条或小批量”，但操作频率极高（可能每秒数千次）。

2. OLAP（Online Analytical Processing 在线分析处理）：“分析海量数据，支撑决策”

OLAP 是面向管理层 / 决策层的数据库架构，核心目标是支持低频、批量的海量数据聚合分析，快速生成统计结果或洞察，相当于企业的 “数据决策支持系统”。

  • 典型场景：计算 “月度销售额 Top10 商品”“各地区用户留存率”“实时 GMV 看板”—— 这些操作需要 “汇总大量明细数据”，通过多维度聚合（如按时间、地区、用户类型分组）得到分析结果。
  • 数据视角：处理的是 “历史或实时的批量数据”（可能是 PB 级的订单明细、用户行为日志），操作频率低（可能每天几次或每小时几次），但单次分析的数据量极大。

总结

  • OLTP：“做业务”—— 处理日常操作，保证数据对；
  • OLAP：“看业务”—— 分析海量数据，得出结论。

适合场景

1. 海量结构化数据的实时分析（PB 级数据，秒级响应）

ClickHouse 采用列式存储（仅加载查询所需列）、分区键（按时间 / 业务维度拆分数据）、主键排序（加速过滤）等设计，能在 PB 级数据量下快速完成聚合计算（如 COUNT/SUM/GROUP BY）。

典型场景：

  • 互联网平台的 实时数据看板：如电商的 “实时成交 GMV、订单量看板”，直播平台的 “实时在线人数、礼物收入统计”，需每秒 / 分钟更新数据，支持多维度下钻（如按地区、时段、用户类型）。
  • 大数据用户行为分析：如 APP 的 “用户留存率（7 日 / 30 日）”“页面访问路径分析”，需对每日千万级用户行为日志（点击、停留、转化）做聚合，且查询延迟要求 < 10 秒。

2. 固定维度的离线批量分析（高吞吐写入 + 高效聚合）

ClickHouse 支持 高吞吐写入（单机每秒可写入百万级行数据），且对 “预定义维度的批量分析” 性能极佳 —— 适合业务逻辑中分析维度相对固定，无需频繁变更查询模式的场景。

典型场景：

  • 企业 离线数据报表：如金融机构的 “每日交易流水汇总报表”（按账户类型、交易渠道、地区汇总）、运营商的 “月度用户流量消耗报表”，需每日批量导入千万级明细数据，支持业务人员按固定维度查询。
  • 日志存储与分析：如服务器日志、应用埋点日志的存储（按时间分区），需支持 “按日志级别（ERROR/WARN）、服务名称、时间范围” 快速筛选和聚合，替代传统的 ELK 栈（ClickHouse 聚合性能远超 Elasticsearch）。

3. 时间序列数据的分析（按时间分区，高效过滤）

ClickHouse 天然支持 按时间字段分区（如 PARTITION BY toYYYYMMDD(create_time)），对 “时间维度的查询”（如 “近 7 天”“上月”）有极致优化，且支持时间窗口函数（如滑动窗口聚合）。

典型场景：

  • IoT 设备监控数据：如工业传感器的 “实时温度 / 压力监控”（每秒产生大量数据），需按设备 ID、时间范围查询历史数据，计算 “每小时平均温度”“峰值压力”，并支持异常值筛选。
  • 监控告警数据：如系统 CPU / 内存使用率的监控数据，需存储数月的历史数据，支持 “按主机名、时间范围” 快速查询，生成趋势图或触发阈值告警。

4. 多维度下钻分析（灵活的维度组合查询）

ClickHouse 支持 任意维度的 GROUP BY 组合，且通过 “稀疏索引”“向量化执行” 优化多维度聚合，即使同时按 3-5 个维度（如 “地区 + 用户等级 + 商品分类”）聚合，也能保持低延迟。

典型场景：

  • 电商用户画像分析：如 “按用户性别（男 / 女）、年龄段（18-25/26-35）、消费等级（VIP / 普通）” 组合查询，计算各群体的 “平均客单价”“复购率”，支持业务人员灵活下钻（如从 “全国” 下钻到 “某省份”，再到 “某城市”）。
  • 广告投放效果分析：如按 “广告位、投放渠道、用户设备类型” 聚合，计算 “点击率（CTR）、转化率（CVR）”，实时调整投放策略。

不适用场景

ClickHouse 的设计定位决定了它不适合以下场景，强行使用会导致性能瓶颈或数据一致性问题：

  1. OLTP 事务场景：如电商订单创建、支付流程（需支持 UPDATE/DELETE 高频操作，且要求 ACID 强一致性）。ClickHouse 对单条数据的修改 / 删除性能差（需重写整个分区），且不支持事务回滚。
  2. 高频单行查询：如 “查询某用户的单个订单详情”（仅返回 1 行数据）。ClickHouse 优化的是 “批量聚合”，单行查询性能不如 MySQL/PostgreSQL（列式存储对单行读取不友好）。
  3. 非结构化 / 半结构化数据存储：如存储 JSON 格式的非固定字段数据、图片 / 视频等二进制数据。ClickHouse 擅长结构化数据，对非结构化数据的解析和查询支持较弱（需提前定义字段类型）。
  4. 高并发写冲突场景：如多线程同时写入同一条数据（需加锁）。ClickHouse 写入时采用 “追加模式”，适合批量写入，但不支持行级锁，无法处理高频写冲突。

安装

1
2
3
4
5
mkdir dockerClickHouse
cd dockerClickHouse
mkdir conf
mkdir data
mkdir log
1
2
cd conf #先启动下载一份users.xml config.xml
#/etc/clickhouse-server/config.xml  /etc/clickhouse-server/users.xml

修改config.xml中的密码：

1
<password>123456</password>

在config.xml中的remote_servers添加：

1
2
3
4
5
6
7
8
<all-sharded>
  <shard>
    <replica>
      <host>localhost</host>
      <port>9000</port>
    </replica>
  </shard>
</all-sharded>
1
docker pull clickhouse/clickhouse-server:22.2.3.5
1
2
3
4
5
6
7
docker run --privileged=true --ulimit nofile=262144:262144 \
-v /Users/qichuanhan/NoIconApplications/dockerClickHouse/conf/config.xml:/etc/clickhouse-server/config.xml \
-v /Users/qichuanhan/NoIconApplications/dockerClickHouse/conf/users.xml:/etc/clickhouse-server/users.xml \
-v /Users/qichuanhan/NoIconApplications/dockerClickHouse/data/:/var/lib/clickhouse-server \
-v /Users/qichuanhan/NoIconApplications/dockerClickHouse/log/:/var/log/clickhouse-server \
-p 8123:8123 -p 9000:9000 -p 9009:9009 --name my-clickhouse \
-e TZ=Asia/Shanghai -d clickhouse/clickhouse-server:22.2.3.5

http://localhost:8123/ 返回OK 安装成功

http://localhost:8123/play 可以执行命令界面

创建数据库和表

1
CREATE DATABASE test;
1
2
3
4
5
6
7
8
9
CREATE TABLE test.clickstream (
customer_id String,
time_stamp Date,
click_event_type String,
page_code FixedString(20),
source_id UInt64
)
ENGINE = MergeTree()
ORDER BY (time_stamp);
1
DESCRIBE test.clickstream;
1
INSERT INTO test.clickstream VALUES('customer1', '2021-10-02', 'add_to_cart','home_enter', 568239 );
1
SELECT * FROM test.clickstream;
1
SELECT * FROM test.clickstream WHERE time_stamp >= '2001-11-01'

数据类型和语法说明

数值类型（整形，浮点数，定点数）

整型

固定⻓度的整型，包括有符号整型或⽆符号整型 IntX X是位的意思， 1Byte字节=8bit位

1
2
3
4
有符号整型范围
Int8/Int16/Int32/Int64/Int128/Int256/
⽆符号整型范围
UInt8/UInt16/UInt32/UInt64/UInt128/UInt256

浮点型（存在精度损失问题）

建议尽可能以整型形式存储数据
Float32 - mysql⾥⾯的float类型
Float64 - mysql⾥⾯的double类型

Decimal类型

需要要求更⾼的精度的数值运算，则需要使⽤定点数

⼀般⾦额字段、汇率、利率等字段为了保证⼩数点精度，都使⽤ Decimal

Clickhouse提供了Decimal32， Decimal64， Decimal128三种精度的定点数

⽤Decimal(P,S)来定义：

P代表精度（Precise），表示总位数（整数部分 + ⼩数部分）

S代表规模（Scale），表示⼩数位数

例⼦：

Decimal(10,2) ⼩数部分2位，整数部分 8位（10-2)，也可以使⽤Decimal32(S)、 Decimal64(S)和 Decimal128(S)的⽅式来表示。

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE test.clickstream1 (
customer_id String,
time_stamp Date,
click_event_type String,
page_code FixedString(20),
source_id UInt64,
money Decimal(2,1)
)
ENGINE = MergeTree()
ORDER BY (time_stamp);

INSERT INTO test.clickstream1 VALUES ('customer1', '2021-10-02','add_to_cart', 'home_enter', 568239,2.11 );

字符串类型

UUID

通⽤唯⼀标识符(UUID)是由⼀组32位数的16进制数字所构成，⽤于标识记录。

要⽣成UUID值， ClickHouse提供了 generateuidv4 函数。

如果在插⼊新记录时未指定UUID列的值，则UUID值将⽤零填充。

1
00000000-0000-0000-0000-000000000000

建表和插⼊例⼦:

1
2
3
4
CREATE TABLE t_uuid (x UUID, y String)
ENGINE=TinyLog;

INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1';

FixedString固定字符串类型（相对少⽤）

类似MySQL的Char类型，属于定⻓字符，固定⻓度 N 的字符串（N 必须是严格的正⾃然数）

如果字符串包含的字节数少于`N’,将对字符串末尾进⾏空字节填充。

如果字符串包含的字节数⼤于N,将抛出Too large value for FixedString(N)异常。

当数据的⻓度恰好为N个字节时， FixedString类型是⾼效的，在其他情况下，这可能会降低效率

1
2
3
4
5
6
7
CREATE TABLE test.t_uuid2
(
    `y` FixedString(5)
)
ENGINE = TinyLog;

insert into t_uuid2 values('Examp1');

String 字符串类型

字符串可以任意⻓度的。它可以包含任意的字节集，包含空字节。

字符串类型可以代替其他 DBMS中的 VARCHAR、BLOB、 CLOB 等类型

ClickHouse 没有编码的概念，字符串可以是任意的字节集，按它们原本的⽅式进⾏存储和输出

时间类型

Date

⽇期类型，⽤两个字节存储，表示从 1970-01-01 (⽆符号)到当前的⽇期值，⽀持字符串形式写⼊。

上限是2106年，但最终完全⽀持的年份为2105

DateTime

时间戳类型。⽤四个字节（⽆符号的）存储 Unix 时间戳，⽀持字符串形式写⼊。

时间戳类型值精确到秒。

值的范围: [1970-01-01 00:00:00, 2106-02-07 06:28:15]

DateTime64

此类型允许以⽇期（date）加时间（time）的形式来存储⼀个时刻的时间值，具有定义的亚秒精度。

值的范围: [1925-01-01 00:00:00, 2283-11-1123:59:59.99999999] (注意: 最⼤值的精度是8)

枚举类型

包括 Enum8 和 Enum16 类型， Enum 保存 ‘string’=integer 的对应关系。

在 ClickHouse 中，尽管⽤户使⽤的是字符串常量，但所有含有 Enum 数据类型的操作都是按照包含整数的值来执⾏。这在性能⽅⾯⽐使⽤ String 数据类型更有效。

Enum8 ⽤ ‘String’= Int8 对描述。
Enum16 ⽤ ‘String’= Int16 对描述。

创建⼀个带有⼀个枚举 Enum8(‘home’ = 1, ‘detail’ =2, ‘pay’=3) 类型的列：

1
2
3
4
5
CREATE TABLE test.t_enum
(
    `page_code` Enum8('home' = 1, 'detail' = 2, 'pay' = 3)
)
ENGINE = TinyLog

插⼊, page_code 这列只能存储类型定义中列出的值： ‘home’或`’detail’ 或 ‘pay’。

如果您尝试保存任何其他值， ClickHouse 抛出异常。

1
2
3
4
5
6
#插⼊成功
INSERT INTO t_enum VALUES ('home'), ('detail')
#插⼊报错
INSERT INTO t_enum VALUES ('home1')
#查询
SELECT * FROM t_enum

布尔值

旧版以前没有单独的类型来存储布尔值。可以使⽤ UInt8 类型，取值限制为 0 或 1

新版⾥⾯新增了Bool

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE test.clickstream2 (
customer_id String,
time_stamp Date,
click_event_type String,
page_code FixedString(20),
source_id UInt64,
money Decimal(2,1),
is_new Bool
)
ENGINE = MergeTree()
ORDER BY (time_stamp)

DESCRIBE test.clickstream2

INSERT INTO test.clickstream2 VALUES ('customer1', '2021-10-02', 'add_to_cart','home_enter', 568239, 3.8,1)

更多数据类型

1
select * from system.data_type_families
1
2
name 列为 字段类型名称
case_insensitive 列为 大小写是否敏感  选项为1 表示⼤⼩写不敏感，字段类型不区分⼤⼩写  为0 表示⼤⼩写敏感，即字段类型需要严格区分⼤⼩写

常⻅SQL语法和注意事项

https://clickhouse.com/docs/zh/sql-reference/statements

创建表

1
2
3
4
5
6
7
8
9
10
11
12
#建表语句
CREATE TABLE test.clickstream3 (
customer_id String,
time_stamp Date,
click_event_type String,
page_code FixedString(20),
source_id UInt64,
money Decimal(2,1),
is_new Bool
)
ENGINE = MergeTree()
ORDER BY (time_stamp)

查看表结构

1
DESCRIBE test.clickstream3

查询

1
SELECT * FROM test.clickstream3

插⼊

1
INSERT INTO test.clickstream3 VALUES ('customer2', '2021-10-02', 'add_to_cart', 'home_enter', 568239,2.1, False )

更新和删除

在OLAP数据库中，可变数据（Mutable data）通常是不被欢迎的，早期ClickHouse是不⽀持，后来版本才有。

不⽀持事务，建议批量操作，不要⾼频率⼩数据量更新删除。

删除和更新是⼀个异步操作的过程，语句提交⽴刻返回，但不⼀定已经完成了。

判断是否完成:

1
SELECT database, table, command, create_time,is_done FROM system.mutations LIMIT 20

注意事项:

每次更新或者删除，会废弃⽬标数据的原有分区，⽽重建新分区。

例⼦：

如果只更新⼀条数据，那么需要重建⼀个分区

如果更新100条数据，⽽这100条可能落在3个分区上，则需重建3个分区

相对来说⼀次更新⼀批数据的整体效率远⾼于⼀次更新⼀⾏

更新

1
2
ALTER TABLE test.clickstream3 
UPDATE click_event_type = 'pay' where customer_id = 'customer2';

删除

1
2
ALTER TABLE test.clickstream3 
delete where customer_id = 'customer2';

分片/分区/副本

什么是ClickHouse的分区

分区是表的分区，把⼀张表的数据分成N多个区块，分区后的表还是⼀张表，数据处理还是由⾃⼰来完成。

PARTITION BY，指的是⼀个表按照某⼀列数据（⽐如⽇期）进⾏分区，不同分区的数据会写⼊不同的⽂件中。

建表时加⼊partition概念，可以按照对应的分区字段，允许查询在指定了分区键的条件下，尽可能的少读取数据。

不是所有的表引擎都可以分区，合并树(MergeTree) 系列的表引擎才⽀持数据分区， Log系列引擎不⽀持。

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

什么是ClickHouse的分⽚

Shard 分⽚是把数据库横向扩展（Scale Out）到多个物理节点上的⼀种有效的⽅式。

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

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

什么是ClickHouse的副本

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
集群 (Cluster)
├── 分布式表 (Distributed Table)  -- 逻辑抽象，不实际存储数据
│   ├── 分片规则                  -- 定义数据如何分配到分片
│   └── 关联所有分片
│
├── 分片 1 (Shard 1)              -- 数据水平切分的一部分
│   ├── 副本 A (Replica A)        -- 分片1的完整拷贝
│   │   ├── 本地表 (Local Table)  -- 实际存储数据的物理表
│   │   │   ├── 分区 2023-01      -- 按时间划分的分区
│   │   │   ├── 分区 2023-02
│   │   │   └── 分区 2023-03
│   │   └── 存储节点 (Server A)
│   │
│   └── 副本 B (Replica B)        -- 分片1的冗余备份
│       ├── 本地表 (Local Table)  -- 与副本A数据完全一致
│       │   ├── 分区 2023-01
│       │   ├── 分区 2023-02
│       │   └── 分区 2023-03
│       └── 存储节点 (Server B)
│
└── 分片 2 (Shard 2)              -- 另一部分水平切分的数据
    ├── 副本 A (Replica A)        -- 分片2的完整拷贝
    │   ├── 本地表 (Local Table)
    │   │   ├── 分区 2023-01
    │   │   ├── 分区 2023-02
    │   │   └── 分区 2023-03
    │   └── 存储节点 (Server C)
    │
    └── 副本 B (Replica B)        -- 分片2的冗余备份
        ├── 本地表 (Local Table)  -- 与副本A数据完全一致
        │   ├── 分区 2023-01
        │   ├── 分区 2023-02
        │   └── 分区 2023-03
        └── 存储节点 (Server D)

数据分区-允许查询在指定了分区键的条件下，尽可能的少读取数据

数据分⽚-允许多台机器/节点同并⾏执⾏查询，实现了分布式并⾏计算

副本- 是为了保证高可用性和数据的可靠性

常⻅引擎

https://clickhouse.com/docs/zh/engines/table-engines

表引擎(table engine)

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 引擎混淆) 本身不存储数据，但可⽤于同时从任意多个其他的表中读取数据。读是⾃动并⾏的，不⽀持写⼊,读取时，那些被真正读取到数据的表的索引（如果有的话）会被使⽤。Set、 Buffer、 Dictionary等

MergeTree表引擎

MergeTree 系列的引擎被设计⽤于插⼊极⼤量的数据到⼀张表当中。

数据可以以【数据⽚段】的形式⼀个接着⼀个的快速写⼊，数据⽚段在后台按照⼀定的规则进⾏合并。

相⽐在插⼊时不断修改（重写）已存储的数据，这种策略会⾼效很多。

这种数据⽚段反复合并的特性，也正是合并树名称的由来。

特点

ClickHouse 不要求主键唯⼀，所以可以插⼊多条具有相同主键的⾏。

如果指定了【分区键】则可以使⽤【分区】，可以通过PARTITION 语句指定分区字段，合理使⽤数据分区，可以有效减少查询时数据⽂件的扫描范围。

在相同数据集的情况下 ClickHouse 中某些带分区的操作会⽐普通操作更快，查询中指定了分区键时 ClickHouse 会⾃动截取分区数据，这也有效增加了查询性能。

⽀持数据副本和数据采样：

副本是表级别的不是整个服务器级的，所以服务器⾥可以同时有复制表和⾮复制表。

副本不依赖分⽚，每个分⽚有它⾃⼰的独⽴副本。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [[NOT] NULL] [DEFAULT|MATERIALIZED|ALIAS|EPHEMERAL expr1] [COMMENT ...] [CODEC(codec1)] [STATISTICS(stat1)] [TTL expr1] [PRIMARY KEY] [SETTINGS (name = value, ...)],
    name2 [type2] [[NOT] NULL] [DEFAULT|MATERIALIZED|ALIAS|EPHEMERAL expr2] [COMMENT ...] [CODEC(codec2)] [STATISTICS(stat2)] [TTL expr2] [PRIMARY KEY] [SETTINGS (name = value, ...)],
    ...
    INDEX index_name1 expr1 TYPE type1(...) [GRANULARITY value1],
    INDEX index_name2 expr2 TYPE type2(...) [GRANULARITY value2],
    ...
    PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]),
    PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY])
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name = value, ...]

语法解析
【必填】 ENGINE - 引擎名和参数。 ENGINE = MergeTree(). MergeTree 引擎没有参数。

【必填】 ORDER BY — 排序键，可以是⼀组列的元组或任意的表达式

例如:

ORDER BY (CounterID, EventDate) 。如果没有使⽤ PRIMARY KEY 显式指定的主键， ClickHouse 会使⽤排序键作为主键

如果不需要排序，可以使⽤ ORDER BY tuple()

【选填】 PARTITION BY — 分区键 ，可选项

要按⽉分区，可以使⽤表达式 toYYYYMM(date_column)，这⾥的 date_column 是⼀个 Date 类型的列, 分区名的格式会是 “YYYYMM”

分区的好处是降低扫描范围提升速度，不填写默认就使⽤⼀个分区

【选填】 PRIMARY KEY -主键，作为数据的⼀级索引，但是不是唯⼀约束，和其他数据库区分

如果要 选择与排序键不同的主键，在这⾥指定，可选项

默认情况下主键跟排序键（由 ORDER BY ⼦句指定）相同。

⼤部分情况下不需要再专⻔指定⼀个 PRIMARY KEY。

PRIMARY KEY 主键必须是 order by 字段的前缀字段。

主键和排序字段这两个属性只设置⼀个时，另⼀个默认与它相同， 当两个都设置时， PRIMARY KEY必须为ORDER BY的前缀

⽐如ORDER BY (CounterID, EventDate)，那主键需要是（CounterID ）或 (CounterID, EventDate)

测试

1
2
3
4
5
6
7
8
9
10
create table test.order_merge_tree(
id UInt32,
sku_id String,
out_trade_no String,
total_amount Decimal(16,2),
create_time Datetime
) engine =MergeTree()
order by (id,sku_id)
partition by toYYYYMMDD(create_time)
primary key (id);
1
2
3
4
5
6
7
insert into test.order_merge_tree values
(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
(2,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
(3,'sku_3','542323',55.02,'2023-03-01 18:00:00'),
(4,'sku_1','54222',2000.3,'2023-04-01 19:00:00'),
(5,'sku_2','53423',120.2,'2023-04-01 19:00:00'),
(6,'sku_4','65432',600.01,'2023-04-02 11:00:00');

分区合并验证

1
2
3
4
5
6
7
8
9
10
登录容器，连接命令 clickhouse-client

新的数据写⼊会有临时分区产⽣，不之间加⼊已有分区
写⼊完成后经过⼀定时间（10到15分钟）， ClickHouse会⾃动化
执⾏合并操作，将临时分区的数据合并到已有分区当中
optimize的合并操作是在后台执⾏的，⽆法预测具体执⾏时间点，
除⾮是⼿动执⾏
通过⼿⼯合并( optimize table xxx final; )
在数据量⽐较⼤的情况，尽量不要使⽤该命令，执⾏optimize要消
耗⼤量时间

ReplacingMergeTree表引擎

去重合并树ReplaceMergeTree

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

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

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

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

注意去重访问：

如果是有多个分区表，只在分区内部进⾏去重，不会跨分区。

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

ver — 版本列。类型为 UInt*, Date 或 DateTime。可选参数。
在数据合并的时候， ReplacingMergeTree 从所有具有相同排序键的⾏中选择⼀⾏留下：
如果 ver 列未指定，保留最后⼀条。
如果 ver 列已指定，保留 ver 值最⼤的版本

如何判断数据重复

在去除重复数据时，是以ORDER BY排序键为基准的，⽽不是PRIMARY KEY。

若排序字段为两个，则两个字段都相同时才会去重

何时删除重复数据

在执⾏分区合并时触发删除重复数据， optimize的合并操作是在后台执⾏的，⽆法预测具体执⾏时间点，除⾮是⼿动执⾏。

不同分区的重复数据不会被去重

ReplacingMergeTree是以分区为单位删除重复数据的，在相同的数据分区内重复的数据才会被删除，⽽不同数据分区之间的重复数据依然不能被删除的。

删除策略

ReplacingMergeTree() 填⼊的参数为版本字段，重复数据就会保留版本字段值最⼤的。

如果不填写版本字段，默认保留插⼊顺序的最后⼀条数据。

测试

1
2
3
4
5
6
7
8
9
10
create table test.order_relace_merge_tree(
id UInt32,
sku_id String,
out_trade_no String,
total_amount Decimal(16,2),
create_time Datetime
) engine =ReplacingMergeTree(id)
order by (sku_id)
partition by toYYYYMMDD(create_time)
primary key (sku_id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
insert into test.order_relace_merge_tree
values
(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
(2,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
(3,'sku_3','542323',55.02,'2023-03-01 18:00:00'),
(4,'sku_5','54222',2000.3,'2023-04-01 19:00:00'),
(5,'sku_6','53423',120.2,'2023-04-01 19:00:00'),
(6,'sku_7','65432',600.01,'2023-04-02 11:00:00');


insert into test.order_relace_merge_tree
values
(11,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00')
,
(21,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
(31,'sku_3','542323',55.02,'2023-03-01 18:00:00'),
(41,'sku_5','54222',2000.3,'2023-04-01 19:00:00'),
(51,'sku_8','53423',120.2,'2023-04-01 19:00:00'),
(61,'sku_9','65432',600.01,'2023-04-02 11:00:00');

分区合并验证

1
2
3
4
5
6
登录容器，连接命令 clickhouse-client

写⼊完成后经过⼀定时间（10到15分钟）， ClickHouse会⾃动化执⾏合并操作，
将临时分区的数据合并到已有分区当中，并进⾏去重
也可以通过⼿⼯合并( optimize table xxx final; )
⽤在后台清除重复的数据，但是它不保证没有重复的数据出现。

聚合引擎SummingMergeTree

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

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

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

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

设计聚合统计表，字段全部是维度、度量或者时间戳即可，⾮相关的字段可以不添加。

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

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

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

1
2
3
4
5
6
7
8
9
10
create table test.order_summing_merge_tree(
id UInt32,
sku_id String,
out_trade_no String,
total_amount Decimal(16,2),
create_time Datetime
) engine =SummingMergeTree(total_amount)
order by (id,sku_id)
partition by toYYYYMMDD(create_time)
primary key (id);
1
2
3
4
5
6
7
8
9
10
11
12
insert into test.order_summing_merge_tree
values
(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
(2,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
(3,'sku_3','542323',55.02,'2023-03-01 18:00:00'),
(4,'sku_5','54222',2000.3,'2023-04-01 19:00:00'),
(5,'sku_6','53423',120.2,'2023-04-01 19:00:00'),
(6,'sku_7','65432',600.01,'2023-04-02 11:00:00');

insert into test.order_summing_merge_tree
values
(1,'sku_1','aabbccbb',5600.00,'2023-03-01 23:09:00')
1
select sku_id, sum(total_amount) from test.order_summing_merge_tree group by sku_id

SummingMergeTree是根据什么对数据进⾏合并的

【ORBER BY排序键相同】作为聚合数据的条件Key的⾏中的列进⾏汇总，将这些⾏替换为包含汇总数据的⼀⾏记录

跨分区内的相同排序key的数据是否会进⾏合并

以数据分区为单位来聚合数据，同⼀数据分区内相同ORBER BY排序键的数据会被合并汇总，⽽不同分区之间的数据不会被汇总

如果没有指定聚合字段，会怎么聚合

如果没有指定聚合字段，则会⽤⾮维度列，且是数值类型字段进⾏聚合

对于⾮汇总字段的数据，该保留哪⼀条

如果两⾏数据除了【ORBER BY排序键】相同，其他的⾮聚合字段不相同，在聚合时会【保留最初】的那条数据，新插⼊的数据对应的那个字段值会被舍弃

总结

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

ReplicatedMergeTree引擎

副本合并树引擎

数据复制

复制是多主异步

数据会先插⼊到执⾏该语句的服务器上，然后被复制到其他服务器。

由于它是异步的，在其他副本上最近插⼊的数据会有⼀些延迟。

如果部分副本不可⽤，则数据在其可⽤时再写⼊；

副本可⽤的情况下，则【延迟时⻓】是通过⽹络传输、压缩数据块所需的时间；

默认情况下， INSERT 语句仅等待⼀个副本写⼊成功后返回，如果数据只成功写⼊⼀个副本后该副本所在的服务器不再存在，则存储的数据会丢失。

要启⽤数据写⼊多个副本才确认返回，使⽤ insert_quorum进⾏配置，但是会影响性能。

对于 INSERT 和 ALTER 语句操作数据的会在压缩的情况下被复制，⽽ CREATE， DROP， ATTACH， DETACH 和 RENAME语句只会在单个服务器上执⾏，不会被复制。

数据写入流程

  1. 客户端发起写入请求

    客户端将数据发送到集群中的任意一个节点（如节点一，该节点称为 “写入节点”，非固定主节点）。

  2. 节点一本地写入数据

    节点一接收到数据后，按照 MergeTree 的逻辑，将数据排序、分块，生成一个新的本地数据块（part文件），并写入自身磁盘，确保本地存储成功。

  3. 节点一向 ZooKeeper 提交日志

    节点一本地写入成功后，向 ZooKeeper 中该表的 “复制日志节点”（预设路径）写入一条 “数据块日志”。日志内容仅包含数据块的元信息（如数据块 ID、存储路径、校验和、所属节点等），不包含原始数据，体积极小。

  4. 节点二监听 ZooKeeper 日志变化

    节点二（副本节点）会通过后台线程持续监听 ZooKeeper 中该表的 “复制日志节点”。当发现有新的日志写入时，立即触发同步流程。

  5. 节点二拉取数据并完成同步

    • 节点二先从 ZooKeeper 拉取新日志，解析出数据块的元信息（尤其是数据块所在的节点一地址）。
    • 节点二通过 ClickHouse 内部协议（如 HTTP）直接向节点一发起请求，拉取对应的完整数据块。
    • 节点二将拉取到的数据块写入本地磁盘，生成与节点一一致的part文件，完成数据同步。

特点

如果有两个副本的话,相当于分布在两台clickhosue节点中的两个表。

这个两个表具有协调功能, ⽆论是哪个表执⾏insert或者alter操作,都会同步到另外⼀张表，副本就是相互同步数据的表。

副本同步需要借助zookeeper实现数据的同步, 副本节点会在zk上进⾏监听，但数据同步过程是不经过zk的。

zookeeper要求3.4.5以及以上版本

ReplicatedMergeTree 参数

zoo_path — zk 中该表的路径，可⾃定义名称，同⼀张表的同⼀分⽚的不同副本，要定义相同的路径；

replica_name — zk 中的该表的副本名称，同⼀张表的同⼀分⽚的不同副本，要定义不同的名称；

1
2
3
4
5
6
7
8
9
10
CREATE TABLE table_name
(
    EventDate DateTime,
    CounterID UInt32,
    UserID UInt32,
    ver UInt16
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{layer}-{shard}/table_name', '{replica}', ver)
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID);

只有 MergeTree 系列⾥的表可⽀持副本：

ReplicatedMergeTree
ReplicatedSummingMergeTree
ReplicatedReplacingMergeTree
ReplicatedAggregatingMergeTree
ReplicatedCollapsingMergeTree
ReplicatedVersionedCollapsingMergeTree
ReplicatedGraphiteMergeTree

测试

1
2
3
4
5
6
7
8
9
10
CREATE TABLE tb_order
(
EventDate DateTime,
CounterID UInt32,
UserID UInt32
) ENGINE =
ReplicatedMergeTree('/clickhouse/tables/01/tb_order', 'tb_order_01')
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)

«ZooKeeper 中该表的路径»对每个可复制表都要是唯⼀的,不同分⽚上的表要有不同的路径

推荐格式 即 【通⽤前缀】【分⽚标识部分】

1
/clickhouse/tables/{shard}/{table_name}

⼤括号的部分的可以⽤参数替代

【每个机器】上创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#节点⼀， zk路径⼀致，副本名称不⼀样
CREATE TABLE tb_product
(
userId UInt32
) 
ENGINE = ReplicatedMergeTree('/clickhouse/tables/1/tb_product', 'product-replica-1')
ORDER BY (userId)


#节点⼆， zk路径⼀致，副本名称不⼀样
CREATE TABLE tb_product
(
userId UInt32
) 
ENGINE = ReplicatedMergeTree('/clickhouse/tables/1/tb_product', 'product-replica-2')
ORDER BY (userId)

副本只能同步数据，不能同步表结构，需要在每台机器上⼿动建表。

1
2
3
4
INSERT into tb_product values(1),(2),(3)
select * from tb_product
#查询zk配置
select * from system.zookeeper where path='/'

分布式表引擎Distributed引擎

Distributed表引擎主要是⽤于分布式，⾃身不存储任何数据，数据都分散存储在某⼀个分⽚上，能够⾃动路由数据⾄集群中的各个节点，需要和其他数据表引擎⼀起协同⼯作。

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

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

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

分布式参数

参数 描述
cluster 服务器配置文件中的集群名称
database 远程数据库的名称
table 远程表的名称
sharding_key (可选) 分片键。 指定 sharding_key 对于以下操作是必要的：对于分布式表的 INSERT（因为表引擎需要 sharding_key 来确定如何拆分数据）。但是，如果启用了 insert_distributed_one_random_shard 设置，则 INSERT 不需要分片键。与 optimize_skip_unused_shards 一起使用，因为需要 sharding_key 来确定应该查询哪些分片
policy_name (可选) 策略名称，将用于存储后台发送的临时文件

多分⽚表查询

本着谁执⾏谁负责的原则，向ClickHouse发起分布式表执⾏
SELECT * FROM distributed_table
它会转为如下形式SELECT * FROM local_table，先执⾏本地分⽚，再发送远端各分⽚执⾏
合并结果为临时表返回

spring boot 集成

1
2
3
4
5
6
7
8
9
10
<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.6.0-patch1</version>
</dependency>
<dependency>
  <groupId>com.github.yulichang</groupId>
  <artifactId>mybatis-plus-join-boot-starter</artifactId>
  <version>3.4.0</version>
</dependency>
1
2
3
4
5
server.port=8080
spring.datasource.driver-classname=ru.yandex.clickhouse.ClickHouseDriver
spring.datasource.url=jdbc:clickhouse://127.0.0.1:8123/default
mybatis-plus.configuration.logimpl=org.apache.ibatis.logging.stdout.StdOutImpl
logging.level.root=INFO
clickhouse > 大数据
#clickhouse
clickhouse入门
http://hanqichuan.com/2025/09/30/大数据/clickhouse入门/
作者
韩启川
发布于
2025年9月30日
许可协议