ClickHouse 概念整理
什么是ClickHouse？
- 毛子开源的一个列式存储数据库(DBMS), 主要用于OLAP, 能使用SQL查询实时生成分析数据报告。
- 可以类比HBase
数据类型
-
与其他框架比较
MySQL Hive ClickHouse byte TINYINT Int8 short SMALLINT Int16 int INT Int32 long BIGINT Int64 varchar STRING String timestamp TIMESTAMP DateTime float FLOAT Float32 double DOUBLE Float64 boolean BOOLEAN 无 -
整形
- 固定长度的整形, 包括有符号整型或无符号整型。
- 整型范围 (-2n-1, 2n-1 -1)
- 无符号整型范围 (0 ~ 2n -1)
-
浮点型(不建议用, 有精度损失问题)
- Float32
- Float64
-
布尔型
- 没有单独的类型来存储布尔值。可以使用 UInt8 类型, 取值限制为 0 或 1。
-
字符串
- String
- 字符串可以是任意长度的。它可以包含任意的字节集, 包含空字节。
- FixedString(N)
- 固定长度 N 的字符串, N必须是严格的正自然数。
- 当服务端读取长度小于 N 的字符串时, 通过在字符串末尾添加空字节来达到 N 字节长度。
- 当服务端读取长度大鱼 N 的字符串时, 将返回错误消息。
- 与 String 相比, 极少会使用 FixedString, 因为使用起来不是很方便。
- String
-
数组
- Array(T): 由 T 类型元素组成的数组
- T 可以是任意类型, 包含数组类型(也就是说可以套娃), 但不推荐使用多维数组(不能再MergeTree 表中存储多维数组)。
-
元组
- Tuple(T1, T2, ...) 其中每个元素都有单独的类型, 可以类比python 和 Scala 的 Tuple。
表引擎
-
TinyLog
-
存在磁盘中
-
不支持索引
-
没有并发控制(同时读ok, 同时写会造成数据损坏, 同时读写会相互阻塞并报错)
-
使用场景: 存储小表以及不会改动的数据
-
SQL Demo:
e8e77479edf9 :) create table customer(id Int8, name String)engine=TinyLog CREATE TABLE customer ( `id` Int8, `name` String ) ENGINE = TinyLog Ok. 0 rows in set. Elapsed: 0.005 sec.插入数据:
insert into customer values(1, 'Thermo Fisher Scientific') insert into customer values(2, 'Agilent Technologies') insert into customer values(3, 'Shimadzu')- 其实会在对应的表目录下创建id.bin, name.bin, 及sizes.json(记录字节数)文件
-
-
Memory
- 内存引擎, 数据以未压缩的原始形式直接保存在内存中, 服务器重启数据就会消失。
- 读写操作不会相互阻塞
- 不支持索引
- 简单查询下有非常高的性能表现(超过10G/s)
- 应用场景: 测试, 需要非常高的性能, 同时数据量又不太大(上限大约1亿行)的场景
-
Merge
-
本身不存储数据
-
可用于同时从任意多个其他的表中读取数据
-
读是自动并行的, 不支持写入。
-
读取时, 那些被真正读取的数据的表的索引(如果有的话)会被使用
-
SQL Demo:
-- 先创建3个TinyLog引擎的表 create table t1 (id UInt16, name String) ENGINE=TinyLog; create table t2 (id UInt16, name String) ENGINE=TinyLog; create table t3 (id UInt16, name String) ENGINE=TinyLog; -- 插入数据 insert into t1(id, name) values (1, 'Trump'); insert into t2(id, name) values (2, 'Powell'); insert into t3(id, name) values (3, 'Merkel'); -- 使用正则合并所有带t名的表的数据 create table t (id UInt16, name String) ENGINE=Merge(currentDatabase(), '^t'); -- 查看表t数据 e8e77479edf9 :) select * from t; SELECT * FROM t ┌─id─┬─name──┐ │ 1 │ Trump │ └────┴───────┘ ┌─id─┬─name───┐ │ 2 │ Powell │ └────┴────────┘ ┌─id─┬─name───┐ │ 3 │ Merkel │ └────┴────────┘ 3 rows in set. Elapsed: 0.003 sec.- 需要注意的是merge的是本地的表, 不能跨机器
-
-
MergeTree(重点)
-
使用场景: 有海量数据要插入到表中, 需要高效地一批批写入数据片段, 并希望这些数据片段在后台按照一定规则合并。
-
特点:
- 数据按主键排序
- 可以使用分区(如果指定了主键)
- 支持数据副本
- 支持数据采集
-
格式:
ENGINE=MergeTree() # 指定引擎 PARTITION BY # 分区键, 要按月分区, 可以使用表达式toYYYYMM(date_column) ORDER BY # 表的排序键, 可以是一组列的元组或任意的表达式, 例如: ORDER BY(id, name)。 PRIMARY KEY # 主键, 需要与排序键字段不同, 默认情况下主键跟排序键相同 SAMPLE BY # 用于抽样的表达式式。如果要用抽样表达式, 主键中必须包含此表达式 SETTINGS # 影响MergeTree性能的额外参数: (1) index_granularity: 索引粒度, 即索引中相邻【标记】间的数据行数, 默认值: 8192 (2) use_minimalistic_part_header_in_zookeeper: 数据片段头在Zookeeper中的存储方式。 (3) min_merge_bytes_to_use_direct_io: 使用直接 I/O 来操作磁盘的合并操作时要求的最小的数据量。 -
SQL Demo
-- 创建表 create table mt_table(date Date, id UInt8, name String) engine=MergeTree() partition by date order by (id, name) -- id 和 name 会默认被当成主键 settings index_granularity=8192; -- 插入数据 insert into mt_table values ('2020-03-25', 1, 'Trump'); insert into mt_table values ('2020-03-26', 1, 'Powell'); insert into mt_table values ('2020-03-27', 1, 'Merkel');-
会在表目录下产生时间目录, 时间目录中会有许多文件
-
checksums.txt (检查总数)
-
columns.txt (存储列信息)
-
count.txt (列中数据量)
-
date.bin
-
data.mrk (mrk 存的是偏移量)
-
id.bin
-
id.mrk
-
minmax_date.idx (存最小最大时间)
-
name.bin
-
name.mrk
-
partition.dat
-
primary.idx (存主键参数)
-
-
-
-
ReplacingMergeTree
-
在MergeTree的基础上, 添加了"处理重复数据"的功能, 该引擎和MergeTree的不同之处在于它会删除有相同主键的重复项。
-
数据的去重只会在合并的过程中出现
-
合并会在未知的事件在后台运行, 所以无法预先作出计划。
-
有一些数据可能仍未必被处理, 因此ReplacingMergeTree适用于在后台清除重复的数据以节省空间, 但是它不保证没有重复的数据出现。
-
格式:
ENGINE=ReplacingMergeTree([ver]) # ver: 版本列, 类型为UInt*, Date 或DateTime 合并的时候, ReplacingMergeTree 从所有具有相同主键的行中选择一行留下, 如果ver列未指定, 选择最后一条。如果ver列已指定, 选择ver值最大的版本 PARTITION BY ORDER BY SAMPLE BY SETTING name = value,... -
SQL Demo
-- 创建表 create table rmt_table (date Date, id UInt8, name String, point UInt8) ENGINE=ReplacingMergeTree(point) partition by date order by (id, name); -- 插入数据 insert into rmt_table values ('2020-03-20', 1, 'a', 20); insert into rmt_table values ('2020-03-21', 1, 'a', 30); insert into rmt_table values ('2020-03-22', 1, 'a', 20); insert into rmt_table values ('2020-03-23', 1, 'a', 30); insert into rmt_table values ('2020-03-24', 1, 'a', 10); insert into rmt_table values ('2020-03-20', 1, 'a', 20); insert into rmt_table values ('2020-03-20', 1, 'a', 20); -- 查看数据 e8e77479edf9 :) select * from rmt_table e8e77479edf9 :) select * from rmt_table SELECT * FROM rmt_table ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-21 │ 1 │ a │ 30 │ └────────────┴────┴──────┴───────┘ ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-23 │ 1 │ a │ 30 │ └────────────┴────┴──────┴───────┘ ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-20 │ 1 │ a │ 20 │ └────────────┴────┴──────┴───────┘ ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-24 │ 1 │ a │ 10 │ └────────────┴────┴──────┴───────┘ ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-22 │ 1 │ a │ 20 │ └────────────┴────┴──────┴───────┘ ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-20 │ 1 │ a │ 20 │ └────────────┴────┴──────┴───────┘ ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-20 │ 1 │ a │ 20 │ └────────────┴────┴──────┴───────┘ -- 使用optimize参数优化表(实际工作中千万别用, 让它自己慢慢合并) optimize table rmt_table; -- 再查看表, 可以看见重复行已经去除 e8e77479edf9 :) select * from rmt_table SELECT * FROM rmt_table ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-24 │ 1 │ a │ 10 │ └────────────┴────┴──────┴───────┘ ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-23 │ 1 │ a │ 30 │ └────────────┴────┴──────┴───────┘ ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-22 │ 1 │ a │ 20 │ └────────────┴────┴──────┴───────┘ ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-21 │ 1 │ a │ 30 │ └────────────┴────┴──────┴───────┘ ┌───────date─┬─id─┬─name─┬─point─┐ │ 2020-03-20 │ 1 │ a │ 20 │ └────────────┴────┴──────┴───────┘ 5 rows in set. Elapsed: 0.003 sec.
-
-
SummingMergeTree
-
继承自MergeTree
-
当合并 SummingMergeTree 表的数据片段时, ClickHouse 会把所有具有相同主键的行合并为一行, 该行包含了被合并的行中具有数值数据类型的列的汇总值。
-
如果主键的组合方式使得单个兼职对应于大量的行, 则可以显著的减少存储空间并加快数据查询的速度
-
对于不可加的列, 会取一个最先出现的值。
-
格式:
ENGINE = SummingMergeTree([columns]) # column: 包含将要被汇总的列的列名的元组 PARTITION BY ORDER BY SAMPLE BY SETTINGs name=value, ... -
SQL Demo
-- 创建表 create table smt_table (date Date, name String, sum UInt16, not_sum UInt16) engine=SummingMergeTree(sum) partition by date order by (date, name); --插入数据 insert into smt_table values ('2020-03-20', 'a', 1, 2); insert into smt_table values ('2020-03-20', 'b', 2, 1); insert into smt_table values ('2020-03-21', 'b', 3, 9); insert into smt_table values ('2020-03-21', 'a', 3, 8); insert into smt_table values ('2020-03-21', 'a', 3, 1); insert into smt_table values ('2020-03-22', 'c', 1, 3); -- 查看数据 e8e77479edf9 :) select * from smt_table; SELECT * FROM smt_table ┌───────date─┬─name─┬─sum─┬─not_sum─┐ │ 2020-03-22 │ c │ 1 │ 3 │ └────────────┴──────┴─────┴─────────┘ ┌───────date─┬─name─┬─sum─┬─not_sum─┐ │ 2020-03-21 │ a │ 3 │ 1 │ └────────────┴──────┴─────┴─────────┘ ┌───────date─┬─name─┬─sum─┬─not_sum─┐ │ 2020-03-21 │ a │ 3 │ 8 │ └────────────┴──────┴─────┴─────────┘ ┌───────date─┬─name─┬─sum─┬─not_sum─┐ │ 2020-03-20 │ b │ 2 │ 1 │ └────────────┴──────┴─────┴─────────┘ ┌───────date─┬─name─┬─sum─┬─not_sum─┐ │ 2020-03-21 │ b │ 3 │ 9 │ └────────────┴──────┴─────┴─────────┘ ┌───────date─┬─name─┬─sum─┬─not_sum─┐ │ 2020-03-20 │ a │ 1 │ 2 │ └────────────┴──────┴─────┴─────────┘ 6 rows in set. Elapsed: 0.004 sec. -- 使用optimize参数优化表(实际工作中千万别用, 让它自己慢慢合并) optimize table smt_table; -- 再次查看表 e8e77479edf9 :) select * from smt_table; SELECT * FROM smt_table ┌───────date─┬─name─┬─sum─┬─not_sum─┐ │ 2020-03-22 │ c │ 1 │ 3 │ └────────────┴──────┴─────┴─────────┘ ┌───────date─┬─name─┬─sum─┬─not_sum─┐ │ 2020-03-20 │ b │ 2 │ 1 │ └────────────┴──────┴─────┴─────────┘ ┌───────date─┬─name─┬─sum─┬─not_sum─┐ │ 2020-03-21 │ a │ 6 │ 8 │ │ 2020-03-21 │ b │ 3 │ 9 │ └────────────┴──────┴─────┴─────────┘ ┌───────date─┬─name─┬─sum─┬─not_sum─┐ │ 2020-03-20 │ a │ 1 │ 2 │ └────────────┴──────┴─────┴─────────┘ 5 rows in set. Elapsed: 0.002 sec.
-
-
Distrubuted(重点)
-
分布式引擎, 本身不存储数据, 但可以在多个服务器上进行分布式查询。
-
读是自动并行的
-
读取时, 远程服务器表的索引(如果有的话)会被使用
-
格式
Distributed(cluster_name, database, table[, sharding_key]) # 参数: 集群名, 数据库, 表，分片键
-
