网站互动推广,制作网站管理系统,大良营销型网站设计公司,郑州网站建设推广渠道1、数据库引擎和表引擎 数据库引擎默认是Ordinary#xff0c;在这种数据库下面的表可以是任意类型引擎。 生产环境中常用的表引擎是MergeTree系列#xff0c;也是官方主推的引擎。 MergeTree是基础引擎#xff0c;有主键索引、数据分区、数据副本、数据采样、删除和修改等功…
1、数据库引擎和表引擎 数据库引擎默认是Ordinary在这种数据库下面的表可以是任意类型引擎。 生产环境中常用的表引擎是MergeTree系列也是官方主推的引擎。 MergeTree是基础引擎有主键索引、数据分区、数据副本、数据采样、删除和修改等功能 ReplacingMergeTree有了去重功能 SummingMergeTree有了汇总求和功能 AggregatingMergeTree有聚合功能 CollapsingMergeTree有折叠删除功能 VersionedCollapsingMergeTree有版本折叠功能 GraphiteMergeTree有压缩汇总功能。 在这些的基础上还可以叠加Replicated和Distributed。Integration系列用于集成外部的数据源常用的有HADOOPMySQL。
2、MaterializeMySQL
2.1、基本概述
MySQL 的用户群体很大为了能够增强数据的实时性很多解决方案会利用 binlog 将数据写入到 ClickHouse。为了能够监听 binlog 事件我们需要用到类似 canal 这样的第三方中间件这无疑增加了系统的复杂度。
ClickHouse 20.8.2.3 版本新增加了 MaterializeMySQL 的 database 引擎该 database 能映 射 到 MySQL 中 的 某 个 database 并 自 动 在 ClickHouse 中 创 建 对 应 的ReplacingMergeTree。ClickHouse 服务做为 MySQL 副本读取 Binlog 并执行 DDL 和 DML 请求实现了基于 MySQL Binlog 机制的业务数据库实时同步功能。
2.2、特点
MaterializeMySQL 同时支持全量和增量同步在 database 创建之初会全量同步MySQL 中的表和数据之后则会通过 binlog 进行增量同步
MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了_sign 和 _version 字段。
其中_version 用作 ReplacingMergeTree 的 ver 版本参数每当监听到 insert、update 和 delete 事件时在 databse 内全局自增。而 _sign 则用于标记是否被删除取值 1 或者 -1。
目前 MaterializeMySQL 支持如下几种 binlog 事件:
MYSQL_WRITE_ROWS_EVENT_sign 1_version MYSQL_DELETE_ROWS_EVENT_sign -1_version MYSQL_UPDATE_ROWS_EVENT新数据 _sign 1 MYSQL_QUERY_EVENT: 支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE 等。
2.3、使用细则
DDL 查询
MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询ALTER, CREATE, DROP, RENAME。如果 ClickHouse 不能解析某些 DDL 查询该查询将被忽略。
数据复制
MaterializeMySQL 不支持直接插入、删除和更新查询而是将 DDL 语句进行相应转换
MySQL INSERT 查询被转换为 INSERT with _sign1 MySQL DELETE 查询被转换为 INSERT with _sign-1 MySQL UPDATE 查询被转换成 INSERT with _sign1 和 INSERT with _sign-1。
SELECT 查询
如果在 SELECT 查询中没有指定_version则使用 FINAL 修饰符返回_version 的最大值对应的数据即最新版本的数据 如果在 SELECT 查询中没有指定_sign则默认使用 WHERE _sign1即返回未删除状态_sign1)的数据。
索引转换
ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为ORDER BY 元组 ClickHouse 只有一个物理顺序由 ORDER BY 子句决定。如果需要创建新的物理顺序请使用物化视图。
2.4、案例实操
MySQL 开启 binlog 和 GTID 模式确保 MySQL 开启了 binlog 功能且格式为 ROW 打开/etc/my.cnf,在[mysqld]下添加
server-id1
log-binmysql-bin
binlog_formatROW开启 GTID 模式如果如果 clickhouse 使用的是 20.8 prestable 之后发布的版本那么 MySQL 还需要配置开启 GTID 模式, 这种方式在 mysql 主从模式下可以确保数据同步的一致性(主从切换时)。
gtid-modeon
enforce-gtid-consistency1 # 设置为主从强一致性
log-slave-updates1 # 记录日志GTID 是 MySQL 复制增强版从 MySQL 5.6 版本开始支持目前已经是 MySQL 主流复制模式。它为每个 event 分配一个全局唯一 ID 和序号我们可以不用关心 MySQL 集群主从拓扑结构直接告知 MySQL 这个 GTID 即可。
重启 MySQL
sudo systemctl restart mysqld准备 MySQL 表和数据
CREATE DATABASE testck;
CREATE TABLE testck.t_organization (id int(11) NOT NULL AUTO_INCREMENT,code int NOT NULL,name text DEFAULT NULL,updatetime datetime DEFAULT NULL,PRIMARY KEY (id),UNIQUE KEY (code)
) ENGINEInnoDB;INSERT INTO testck.t_organization (code,name,updatetime) VALUES(1000,Realinsight,NOW());
INSERT INTO testck.t_organization (code,name,updatetime) VALUES(1001, Realindex,NOW());
INSERT INTO testck.t_organization (code,name,updatetime) VALUES(1002,EDT,NOW());CREATE TABLE testck.t_user (id int(11) NOT NULL AUTO_INCREMENT,code int,PRIMARY KEY (id)
) ENGINEInnoDB;INSERT INTO testck.t_user (code) VALUES(1);开启 ClickHouse 物化引擎
set allow_experimental_database_materialize_mysql1;创建复制管道
ClickHouse 中创建MaterializeMySQL 数据库
CREATE DATABASE test_binlog ENGINE MaterializeMySQL(hadoop1:3306,testck,root,000000); 其中 4 个参数分别是 MySQL 地址、databse、username 和 password。
查看 ClickHouse 的数据
use test_binlog;
show tables;
select * from t_organization;
select * from t_user;修改数据在 MySQL 中修改数据
update t_organization set name CONCAT(name,-v1) where id 1查看 clickhouse 日志可以看到 binlog 监听事件查询clickhouse
select * from t_organization;删除数据MySQL 删除数据
DELETE FROM t_organization where id 2;ClicKHouse日志有 DeleteRows 的 binlog 监听事件查看数据
select * from t_organization;在刚才的查询中增加 _sign 和 _version 虚拟字段select *,_sign,_version from t_organization order by _sign desc,_version desc;
在查询时对于已经被删除的数据_sign-1ClickHouse 会自动重写 SQL将 _sign -1 的数据过滤掉;
对于修改的数据则自动重写 SQL为其增加 FINAL 修饰符。
select * from t_organization
等同于
select * from t_organization final where _sign 1删除表
在 mysql 执行删除表drop table t_user; 此时在 clickhouse 处会同步删除对应表如果查询会报错
show tables;
select * from t_user;
DB::Exception: Table scene_mms.scene doesnt exist..
mysql 新建表clickhouse 可以查询到
CREATE TABLE testck.t_user (
id int(11) NOT NULL AUTO_INCREMENT,
code int,
PRIMARY KEY (id)
) ENGINEInnoDB;INSERT INTO testck.t_user (code) VALUES(1);#ClickHouse 查询
show tables;
select * from t_user;
3、表引擎
表引擎 表引擎的使用 表引擎决定了如何存储表的数据。包括
数据的存储方式和位置写到哪里以及从哪里读取数据 支持哪些查询以及如何支持 并发数据访问 索引的使用如果存在 是否可以执行多线程请求 数据复制参数 表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎以及引擎使用的相关参数。 引擎的名称大小写敏感。 3.1、TinyLog
以列文件的形式保存在磁盘上不支持索引没有并发控制。一般保存少量数据的小表生产环境上作用有限。可以用于平时练习测试用。
3.2、Memory
内存引擎数据以未压缩的原始形式直接保存在内存当中服务器重启数据就会消失。读写操作不会相互阻塞不支持索引。简单查询下有非常非常高的性能表现超过10G/s。一般用到它的地方不多除了用来测试就是在需要非常高的性能同时数据量又不太大上限大概1亿行的场景
3.3、MergeTree
ClickHouse中最强大的表引擎当属MergeTree合并树引擎及该系列MergeTree中的其他引擎支持索引和分区地位可以相当于innodb之于Mysql。
基本sql建表语句
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],...INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK xxx|TO VOLUME xxx], ...]
[SETTINGS namevalue, ...]
PARTITION BY分区键用于指定数据以何种标准进行分区。分区键可以是单个列字段、元组形式的多个列字段、列表达式。如果不声明分区键则ClickHouse会生成一个名为all的分区。合理使用数据分区可以有效减少查询时数据文件的扫描范围。 ORDER BY 决定了每个分区中数据的排序规则主键必须是order by字段的前缀字段在ReplactingmergeTree中order by相同的被认为是重复的数据在SummingMergeTree中作为聚合的维度列PRIMARY KEY 决定了一级索引(primary.idx)默认情况下主键与排序键ORDER BY相同所以通常使用ORDER BY代为指定主键。一般情况下在单个数据片段内数据与一级索引以相同的规则升序排序。与其他数据库不同MergeTree主键允许存在重复数据SAMPLE BY抽样表达式用于声明数据以何种标准进行采样。抽样表达式需要配合SAMPLE子查询使用SETTINGSindex_granularity索引粒度默认值8192。也就是说默认情况下每隔8192行数据才生成一条索引SETTINGSindex_granularity_bytes在19.11版本之前ClickHouse只支持固定大小的索引间隔index_granularity。在新版本中增加了自适应间隔大小的特性即根据每一批次写入数据的体量大小动态划分间隔大小。而数据的体量大小由index_granularity_bytes参数控制默认10M SETTINGSenable_mixed_granularity_parts设置是否开启自适应索引间隔的功能默认开启。
例子
create table t_order_mt(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);插入数据
insert into t_order_mt values
(101,sku_001,1000.00,2020-06-01 12:00:00) ,
(102,sku_002,2000.00,2020-06-01 11:00:00),
(102,sku_004,2500.00,2020-06-01 12:00:00),
(102,sku_002,2000.00,2020-06-01 13:00:00),
(102,sku_002,12000.00,2020-06-01 13:00:00),
(102,sku_002,600.00,2020-06-02 12:00:00);查询数据 1根据日期分区2020-06-01、2020-06-02共两个分区 2主键可重复 3分区内根据id和sku_id排序。 分区(可选)
分区的目的主要是降低扫描的范围优化查询速度如果不填只会使用一个分区。分区后面对涉及跨分区的查询统计ClickHouse会以分区为单位并行处理。
文件结构
在前面安装时就介绍过配置文件中表明了默认的数据存储位置是/var/lib/clickhouse因此结构如下 里面有两个文件夹很重要metadata和data。metadata保存了数据库的元数据每个库的每个表都会记录表结构信息 ATTACH TABLE _ UUID c51df0c7-bae7-4abb-b8a8-d2a5523cdb26
(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)
SETTINGS index_granularity 8192基本上和建表语句差不多。
data保存了每个库的表数据 20200601_1_1_0、20200602_2_2_0共两个分区目录。
分区目录命名格式PartitionId_MinBlockNum_MaxBlockNum_Level分表代表分区值、最小分区块编号、最大分区块编号、合并层级。而每一个分区目录下都包含如下文件
-rw-r-----. 1 clickhouse root 259 8月 29 03:02 checksums.txt
-rw-r-----. 1 clickhouse root 118 8月 29 03:02 columns.txt
-rw-r-----. 1 clickhouse root 1 8月 29 03:02 count.txt
-rw-r-----. 1 clickhouse root 189 8月 29 03:02 data.bin
-rw-r-----. 1 clickhouse root 144 8月 29 03:02 data.mrk3
-rw-r-----. 1 clickhouse root 10 8月 29 03:02 default_compression_codec.txt
-rw-r-----. 1 clickhouse root 8 8月 29 03:02 minmax_create_time.idx
-rw-r-----. 1 clickhouse root 4 8月 29 03:02 partition.dat
-rw-r-----. 1 clickhouse root 8 8月 29 03:02 primary.idxdata.bin数据文件其实每一个列都会有一个bin文件 data.mrk3标记文件标记文件在idx索引文件和bin数据文件之间起到了桥梁作用每一个列都会有一个mrk文件 count.txt有几条数据 default_compression_codec.txt默认压缩格式 columns.txt列的信息 primary.idx主键索引文件 partition.dat与minmax_[Column].idx如果使用了分区键则会额外生成这2个文件均使用二进制存储。partition.dat保存当前分区下分区表达式最终生成的值minmax索引用于记录当前分区下分区字段对应原始数据的最小值和最大值。以t_order_mt的20200601分区为例partition.dat中的值为20200601minmax索引中保存的值为2020-06-01 12:00:002020-06-01 13:00:00
分区命名
PartitionId数据分区规则由分区ID决定分区ID由partition by分区键决定。根据分区键字段类型ID生成规则可分为
未定义分区键没有定义partition by默认生成一个目录名为all的数据分区所有数据均存放在all目录下 整型分区键分区键为整型直接用该整型值的字符串形式作为分区ID 日期类分区键分区键为日期类型或者可以转换为日期类型 其他类型分区键String、Float类型等通过128位的Hash算法取其Hash值作为分区ID。 MinBlockNum最小分区块编号自增类型从1开始向上递增。每产生一个新的目录分区就向上递增一个数字
MaxBlockNum最大分区块编号新创建的分区MinBlockNum等于MaxBlockNum的编号
Level合并的层级被合并的次数。合并次数越多层级值越大。对于每一个新创建的分区目录而言其初始值均为0。以分区为单位如果相同分区发生合并动作则在相应分区内计数1。
分区合并 任何一个批次的数据写入都会产生一个临时分区也就是写入一个新的分区目录不会纳入任何一个已有的分区。写入后的某个时刻大概10-15分钟后ClickHouse会自动执行合并操作等不及也可以手动通过optimize执行把临时分区的数据合并到已有分区中已经存在的旧分区目录并不会立即被删除而是在之后的某个时刻通过后台任务被删除默认8分钟。
MinBlockNum取同一分区内所有目录中最小的MinBlockNum值 MaxBlockNum取同一分区内所有目录中最大的MaxBlockNum值 Level取同一分区内最大Level值1
合并过程
手动合并
optimize table xxxx final;在如上的基础上在插入数据
insert into t_order_mt values
(101,sku_001,1000.00,2020-06-01 12:00:00) ,
(102,sku_002,2000.00,2020-06-01 11:00:00),
(102,sku_004,2500.00,2020-06-01 12:00:00),
(102,sku_002,2000.00,2020-06-01 13:00:00),
(102,sku_002,12000.00,2020-06-01 13:00:00),
(102,sku_002,600.00,2020-06-02 12:00:00);查看数据并没有纳入任何分区而是新产生了一个分区 在看数据文件 也多加了两个分区文件。手动optimize之后
optimize table t_order_mt final;再查询 可以看到一个PartitionId的数据就已经合并成功了。而也会新产生两个文件代表新的分区数据。
主键可选 ClickHouse中的主键和其他数据库不太一样**它只提供了数据的一级索引但是却不是唯一约束。**这就意味着是可以存在相同primary key的数据。
主键的设定主要依据是查询语句中的where条件。根据条件通过对主键进行某种形式的二分查找能够定位到对应的index granularity避免了全表扫描。
index granularity直接翻译的话就是索引粒度指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse中的MergeTree默认是8192。官方不建议修改这个值除非该列存在大量重复值比如在一个分区中几万行才有一个不同数据。 稀疏索引的好处就是可以用很少的索引数据定位更多的数据代价就是只能定位到索引粒度的第一行然后再进行进行一点扫描。
order by必须 order by设定了分区内的数据按照哪些字段顺序进行有序保存。order by是MergeTree中唯一一个必填项甚至比primary key还重要因为当用户不设置主键的情况很多处理会依照order by的字段进行处理。
要求主键必须是order by字段的前缀字段。
比如order by字段是(id,sku_id)那么主键必须是id或者(id,sku_id)。
一级索引
MergeTree的主键使用PRIMARY KEY定义待主键定义之后MergeTree会依据index_granularity间隔默认8192行为数据表生成一级索引并保存至primary.idx文件内索引数据按照PRIMARY KEY排序。相比使用PRIMARY KEY定义更为常见的是通过ORDER BY指代主键。在此种情形下PRIMARY KEY与ORDER BY定义相同所以索引primary.idx和数据.bin会按照完全相同的规则排序。
稀疏索引 primary.idx文件内的一级索引采用稀疏索引实现
稀疏索引与稠密索引的区别 在稠密索引中每一行索引标记都会对应到一行具体的数据记录而在稀疏索引中每一行索引标记对应的是一段数据而不是一行。由于稀疏索引占用空间小所以primary.idx内的索引数据常驻内存。
索引粒度
index_granularity表示索引的粒度默认8192 数据以index_granularity的粒度被标记成多个小的区间其中每个区别最多index_granularity行数据。MergeTree使用MarkRange表示一个具体的区间并通过start和end表示其具体的范围
索引数据的生成规则 由于是稀疏索引所以MergeTree需要间隔index_granularity行数据才会生成一条索引记录其索引值会依据声明的主键字段获取
使用CounterID作为主键 该表使用年月分区PARTITION BY toYYYYMM(EventDate)所以2014年3月份的数据最终会被划分到同一个分区目录内。如果使用CounterID作为主键ORDER BY CounterID则每间隔8192行数据就会取一次CounterID的值作为索引值索引数据最终会被写入primary.idx文件进行保存
例如第08192 ∗ 0行CounterID取值57第81928192 ∗ 1行CounterID取值1635而第163848192 ∗ 2行CounterID取值3266最终索引数据将会是5716353266
使用CounterID和EventDate作为主键 如果使用多个主键例如ORDER BY(CounterID,EventDate)则每间隔8192行可以同时取CounterID与EventDate两列的值作为索引值如上图所示。
索引的查询过程
生成查询条件区间首先将查询条件转换为条件区间例如下面的例子
WHERE ID A003
[A003, A003]WHERE ID A000
(A000, inf)WHERE ID A188
(-inf, A188)WHERE ID LIKE A006%
[A006, A007)递归交集判断
以递归的形式依次对MarkRange的数值区间与条件区间做交集判断。从最大区间[A000, inf)开始如果不存在交集则直接通过剪枝算法优化此整段MarkRange 如果存在交集且MarkRange步长大于8(end-start)则将此区间进一步拆分成8个子区间merge_tree_coarse_index_granularity指定默认值为8并重复此规则继续做递归交集判断如果存在交集且MarkRange不可再分解步长小于8则记录MarkRange并返回。合并MarkRange区间将最终匹配的MarkRange聚在一起合并它们的范围 MergeTree通过递归的形式持续向下拆分区间最终将MarkRange定位到最细的粒度以帮助在后续读取数据的时候能够最小化扫描数据的范围。以上图为例当查询条件WHERE IDA003’的时候最终只需要读取[A000, A003]和[A003, A006]两个区间的数据,它们对应MarkRange(start:0,end:2)范围而其他无用的区间都被裁剪掉了。因为MarkRange转换的数值区间是闭区间所以会额外匹配到临近的一个区间。
二级索引
目前在ClickHouse的官网上二级索引的功能在v20.1.2.4之前是被标注为实验性的在这个版本之后默认是开启的
**老版本使用二级索引前需要增加设置**是否允许使用实验性的二级索引v20.1.2.4开始这个参数已被删除默认开启
set allow_experimental_data_skipping_indices1;如果在建表语句中声明了跳数索引则会额外生成相应的索引与标记文件skp_idx_[Column].idx与skp_idx_[Column].mrk。
创建测试表
create table t_order_mt2(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime,
INDEX a total_amount TYPE minmax GRANULARITY 5
) engine MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);INDEX a total_amount TYPE minmax GRANULARITY 5
a表示索引名称
total_amount表示索引字段 TYPE表示索引类型 minmax 存储指定一段数据内的最小和最大极值如果表达式是 tuple 则存储 tuple 中每个元素的极值这些信息用于跳过数据块类似主键。 set(max_rows) 存储指定表达式的不重复值不超过 max_rows 个max_rows0 则表示『无限制』。这些信息可用于检查数据块是否满足 WHERE 条件。 ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed) 存储一个包含数据块中所有 n元短语ngram 的 布隆过滤器。只可用在字符串上。 可用于优化 equals like 和 in 表达式的性能。 n – 短语长度。 size_of_bloom_filter_in_bytes – 布隆过滤器大小字节为单位。因为压缩得好可以指定比较大的值如 256 或 512。 number_of_hash_functions – 布隆过滤器中使用的哈希函数的个数。 random_seed – 哈希函数的随机种子。 tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed) 跟 ngrambf_v1 类似但是存储的是token而不是ngrams。Token是由非字母数字的符号分割的序列。 bloom_filter(bloom_filter([false_positive]) – 为指定的列存储布隆过滤器 GRANULARITY 5 是设定二级索引对于一级索引粒度的粒度。 minmax索引的聚合信息是在一个index_granularity区间内数据的最小和最大值。以下图为例假设index_granularity8192且granularity3则数据会按照index_granularity划分为n等份MergeTree从第0段分区开始依次获取聚合信息。当获取到第3个分区时granularity3则汇总并会生成第一行minmax索引前3段minmax汇总后取值为[1, 9]
minmax索引的聚合信息是在一个index_granularity区间内数据的最小和最大值。以下图为例假设index_granularity8192且granularity3则数据会按照index_granularity划分为n等份MergeTree从第0段分区开始依次获取聚合信息。当获取到第3个分区时granularity3则汇总并会生成第一行minmax索引前3段minmax汇总后取值为[1, 9] 插入数据
insert into t_order_mt2 values
(101,sku_001,1000.00,2020-06-01 12:00:00) ,
(102,sku_002,2000.00,2020-06-01 11:00:00),
(102,sku_004,2500.00,2020-06-01 12:00:00),
(102,sku_002,2000.00,2020-06-01 13:00:00),
(102,sku_002,12000.00,2020-06-01 13:00:00),
(102,sku_002,600.00,2020-06-02 12:00:00);对比效果
clickhouse-client --send_logs_leveltrace
select * from t_order_mt2 where total_amount toDecimal32(900., 2);日志中可以看到二级索引能够为非主键字段的查询发挥作用分区下文件skp_idx_a.idx和skp_idx_a.mrk3为跳数索引文件
checksums.txt count.txt data.mrk3 minmax_create_time.idx primary.idx skp_idx_a.mrk3
columns.txt data.bin default_compression_codec.txt partition.dat skp_idx_a.idx数据TTL
MergeTree提供了可以管理数据表或者列的生命周期的功能。
列级TTL
当列中的值过期时, ClickHouse会将它们替换成该列数据类型的默认值。如果数据片段中列的所有值均已过期则ClickHouse 会从文件系统中的数据片段中删除此列。TTL的列必须是日期类型且不能为主键。
create table t_order_mt3(id UInt32,sku_id String,total_amount Decimal(16,2) TTL create_timeinterval 10 SECOND,create_time Datetime
) engine MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);插入数据请根据实际时间修改数据注意是linux的时间
insert into t_order_mt3 values
(106,sku_001,1000.00,2022-08-29 04:14:20),
(107,sku_002,2000.00,2022-08-29 04:14:20),
(110,sku_003,600.00,2022-08-29 04:14:20);手动合并查看效果到期后指定的字段数据归0
optimize table t_order_mt3 final;
select * from t_order_mt3;表级TTL
表可以设置一个用于移除过期行的表达式以及多个用于在磁盘或卷上自动转移数据片段的表达式。当表中的行过期时ClickHouse 会删除所有对应的行。对于数据片段的转移特性必须所有的行都满足转移条件。
下面的这条语句是数据会在create_time之后10秒丢失
alter table t_order_mt3 MODIFY TTL create_time INTERVAL 10 SECOND;[TTL expr [DELETE|TO DISK xxx|TO VOLUME xxx], ...]TTL 规则的类型紧跟在每个 TTL 表达式后面它会影响满足表达式时到达指定时间时应当执行的操作
DELETE - 删除过期的行默认操作; TO DISK aaa - 将数据片段移动到磁盘 aaa; TO VOLUME bbb - 将数据片段移动到卷 bbb. GROUP BY - 聚合过期的行 涉及判断的字段必须是Date或者Datetime类型推荐使用分区的日期字段
能够使用的时间周期
SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
数据存储各列独立存储 在MergeTree中数据按列存储。而具体到每个列字段数据也是独立存储的每个列字段都拥有一个与之对应的.bin数据文件。数据文件以分区目录的形式被组织存放所以在.bin文件中只会保存当前分区片段内的这一部分数据。按列独立存储的设计优势显而易见 可以更好地进行数据压缩相同类型的数据放在一起对压缩更加友好 能够最小化数据扫描的范围。 数据是经过压缩的目前支持LZ4、ZSTD、Multiple和Delta几种算法默认使用LZ4算法其次数据会事先依照ORDER BY的声明排序最后数据是以压缩数据块的形式被组织并写入.bin文件中的
压缩数据块 一个压缩数据块由头信息和压缩数据两部分组成。头信息固定使用9位字节表示具体由1个UInt81字节整型和2个UInt324字节整型组成分别代表使用的压缩算法类型、压缩后的数据大小和压缩前的数据大小如下图所示 MergeTree在数据具体的写入过程中会按照索引粒度默认情况下每次取8192行按批次获取数据并进行处理。如果把一批数据的未压缩大小设为size则整个写入过程遵循以下规则
单个批次数据size 64KB如果单个批次数据小于64KB则继续获取下一批数据直至累积到size 64KB时生成下一个压缩数据块 单个批次数据64KB size 1MB如果单个批次数据大小恰好在64KB与1MB之间则直接生成下一个压缩数据块 单个批次数据size 1MB如果单个批次数据直接超过1MB则首先按照1MB大小截断并生成下一个压缩数据块。剩余数据继续依照上述规则执行。此时会出现一个批次数据生成多个压缩数据块的情况。 一个.bin文件是由1至多个压缩数据块组成的每个压缩块大小在64KB1MB之间。多个压缩数据块之间按照写入顺序首尾相接紧密地排列在一起。
数据标记
数据标记的生成规则 从上图中可以发现数据标记和索引区间是对齐的均按照index_granularity的粒度间隔。
数据标记文件与.bin文件一一对应每一个列字段[Column].bin文件都有一个与之对应的[Column].mrk数据标记文件用于记录数据在.bin文件中的偏移量信息
一行标记数据使用一个元组表示元组内包含两个整型数值的偏移量信息。它们分别表示此段数据区间内在对应的.bin压缩文件中压缩数据块的起始偏移量以及将该数据压缩块解压后其未压缩数据的起始偏移量 每一行标记数据都表示了一个片段的数据默认8192行在.bin压缩文件中的读取位置信息。标记数据与一级索引数据不同它并不能常驻内存而是使用LRU最近最少使用缓存策略加快其取用速度。
数据标记的工作方式
下图为为hits_v1测试表的JavaEnable字段及其标记数据与压缩数据的对应关系 JavaEnable字段的数据类型为UInt8所以每行数值占用1字节。而hits_v1数据表的index_granularity粒度为8192所以一个索引片段的数据大小恰好是8192B。按照压缩数据块的生成规则如果单个批次数据小于64KB则继续获取下一批数据直至累积到size 64KB时生成下一个压缩数据块。因此在JavaEnable的标记文件中每8行标记数据对应1个压缩数据块1B*81928192B64KB65536B65536/81928。所以从上图能够看到其左侧的标记数据中8行数据的压缩文件偏移量都是相同的因为这8行标记都指向了同一个压缩数据块。而在这8行的标记数据中它们的解压缩数据块中的偏移量则依次按照8192B每行数据1B每一个批次8192行数据累加当累加达到6553664KB时则置0。因为根据规则此时会生成下一个压缩数据块。
1读取压缩数据块
上下相邻的两个压缩文件中的起始偏移量构成了与获取当前标记对应的压缩数据块的偏移量区间。由当前标记数据开始向下寻找直到找到不同的压缩文件偏移量为止。此时得到的一组偏移量区间即时压缩数据块在.bin文件中的偏移量。如上图所示读取右侧.bin文件中[0, 12016]812000812016字节数据就能获得第0个压缩数据块。
压缩数据块被整个加载到内存之后会进行解压在这之后就进入具体数据的读取环节了。
2读取数据
在读取解压后的数据时MergeTree并不需要一次性扫描整段解压数据它可以根据需要以index_granularity的粒度加载特定的一小段
上下相邻两个解压缩数据块中的起始偏移量构成了与获取当前标记对应的数据的偏移区间。通过这个区间能够在它的压缩块被解压之后依照偏移量按需读取数据如上图所示通过[0, 8192]能够读取压缩数据块0中的第一个数据片段。
数据标记与压缩数据块的对应关系 由于压缩数据块的划分与一个间隔index_granularity内的数据大小相关每个压缩数据块的体积都被严格控制在64KB1MB。而一个间隔index_granularity的数据又只会产生一行数据标记。那么根据一个间隔内数据的实际字节大小数据标记和压缩数据块之间会产生三种不同的对应关系
1多对一
多个数据标记对应一个压缩数据块当一个间隔index_granularity内的数据未压缩大小size小于64KB时会出现这种对应关系
以hits_v1测试表的JavaEnable字段为例。JavaEnable数据类型为UInt8大小为1B则一个间隔内数据大小为8192B。所以在此种情形下每8个数据标记会对应同一个压缩数据块 2一对一
一个数据标记对应一个压缩数据块当一个间隔index_granularity内的数据未压缩大小size大于等于64KB且小于等于1MB时会出现这种对应关系。
以hits_v1测试表的URLHash字段为例。URLHash数据类型为UInt64大小为8B则一个间隔内数据大小为65536B恰好等于64KB。所以在此种情形下数据标记与压缩数据块是一对一的关系。
3一对多
一个数据标记对应多个压缩数据块当一个间隔index_granularity内的数据未压缩大小size直接大于1MB时会出现这种对应关系。
以hits_v1测试表的URL字段为例。URL数据类型为String大小根据实际内容而定。 小节 写入过程 数据写入的第一步是生成分区目录伴随着每一批数据的写入都会生成一个新的分区目录。在后续的某一时刻属于相同分区的目录会依照规则合并到一起接着按照index_granularity索引粒度会分别生成primary.idx一级索引如果声明了二级索引还会创建二级索引文件、每一个列字段的.mrk数据标记和.bin压缩数据文件 从分区目录201403_1_34_3能够得知该分区数据共分34批写入期间发生过3次合并。在数据写入的过程中依据index_granularity的粒度依次为每个区间的数据生成索引、标记和压缩数据块。
查询过程 在最理想的情况下MergeTree首先可以依次借助分区索引、一级索引和二级索引将数据扫描范围缩至最小。然后再借助数据标记将需要解压与计算的数据范围缩至最小
如果一条查询语句没有指定任何WHERE条件或是指定了WHERE条件但条件没有匹配到任何索引分区索引、一级索引和二级索引那么MergeTree就不能预先减小数据范围。在后续进行数据查询时它会扫描所有分区目录以及目录内索引段的最大区间。虽然不能减少数据范围但是MergeTree仍然能够借助数据标记以多线程的形式同时读取多个压缩数据块以提升性能。 3.4、ReplacingMergeTree
ReplacingMergeTree是MergeTree的一个变种它存储特性完全继承MergeTree只是多了一个去重的功能去重时机。只有同一批插入新版本或合并分区时才会进行去重。合并会在未知的时间在后台进行所以你无法预先作出计划。有一些数据可能仍未被处理 1去重范围。如果表经过了分区去重只会在分区内部进行去重不能执行跨分区的去重 2实际上是使用order by字段作为唯一键进行去重 3认定重复的数据保留版本字段值最大的 4如果版本字段相同则按插入顺序保留最后一笔 所以ReplacingMergeTree能力有限ReplacingMergeTree适用于在后台清除重复的数据以节省空间但是它不保证没有重复的数据出现。
create table t_order_rmt(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);ReplacingMergeTree()填入的参数为版本字段重复数据保留版本字段值最大的。如果不填版本字段默认按照插入顺序保留最后一条
插入数据
insert into t_order_rmt values
(101,sku_001,1000.00,2020-06-01 12:00:00) ,
(102,sku_002,2000.00,2020-06-01 11:00:00),
(102,sku_004,2500.00,2020-06-01 12:00:00),
(102,sku_002,2000.00,2020-06-01 13:00:00),
(102,sku_002,12000.00,2020-06-01 13:00:00),
(102,sku_002,600.00,2020-06-02 12:00:00);执行查询操作
select * from t_order_rmt; 3.5、SummingMergeTree
对于不查询明细只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话无论是存储空间的开销还是查询时临时聚合的开销都比较大
ClickHouse为了这种场景提供了一种能够预聚合的引擎SummingMergeTree。
以SummingMergeTree()中指定的列作为汇总数据列 可以填写多列必须数字列如果不填以所有非维度列除了order by的列之外且为数字列的字段为汇总数据列 以order by的列为准作为维度列 其他的列按插入顺序保留第一行 不在一个分区的数据不会被聚合 只有在同一批次插入新版本或分片合并时才会进行聚合
create table t_order_smt(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 );插入数据
insert into t_order_smt values
(101,sku_001,1000.00,2020-06-01 12:00:00),
(102,sku_002,2000.00,2020-06-01 11:00:00),
(102,sku_004,2500.00,2020-06-01 12:00:00),
(102,sku_002,2000.00,2020-06-01 13:00:00),
(102,sku_002,12000.00,2020-06-01 13:00:00),
(102,sku_002,600.00,2020-06-02 12:00:00);执行查询操作 文章转载自： http://www.morning.dpdr.cn.gov.cn.dpdr.cn http://www.morning.phjyb.cn.gov.cn.phjyb.cn http://www.morning.ypcbm.cn.gov.cn.ypcbm.cn http://www.morning.hmktd.cn.gov.cn.hmktd.cn http://www.morning.djlxz.cn.gov.cn.djlxz.cn http://www.morning.kxbry.cn.gov.cn.kxbry.cn http://www.morning.shinezoneserver.com.gov.cn.shinezoneserver.com http://www.morning.ntqqm.cn.gov.cn.ntqqm.cn http://www.morning.djpps.cn.gov.cn.djpps.cn http://www.morning.jwbfj.cn.gov.cn.jwbfj.cn http://www.morning.gzzxlp.com.gov.cn.gzzxlp.com http://www.morning.wjdgx.cn.gov.cn.wjdgx.cn http://www.morning.cwqrj.cn.gov.cn.cwqrj.cn http://www.morning.bnfrj.cn.gov.cn.bnfrj.cn http://www.morning.yqfdl.cn.gov.cn.yqfdl.cn http://www.morning.trjdr.cn.gov.cn.trjdr.cn http://www.morning.mtdfn.cn.gov.cn.mtdfn.cn http://www.morning.lxhgj.cn.gov.cn.lxhgj.cn http://www.morning.rksnk.cn.gov.cn.rksnk.cn http://www.morning.yrsg.cn.gov.cn.yrsg.cn http://www.morning.rdnpg.cn.gov.cn.rdnpg.cn http://www.morning.zfqdt.cn.gov.cn.zfqdt.cn http://www.morning.rjbb.cn.gov.cn.rjbb.cn http://www.morning.rnrwq.cn.gov.cn.rnrwq.cn http://www.morning.czgfn.cn.gov.cn.czgfn.cn http://www.morning.ntqnt.cn.gov.cn.ntqnt.cn http://www.morning.qgxnw.cn.gov.cn.qgxnw.cn http://www.morning.pqsys.cn.gov.cn.pqsys.cn http://www.morning.hqnsf.cn.gov.cn.hqnsf.cn http://www.morning.xpqdf.cn.gov.cn.xpqdf.cn http://www.morning.wdhhz.cn.gov.cn.wdhhz.cn http://www.morning.hmbxd.cn.gov.cn.hmbxd.cn http://www.morning.qcsbs.cn.gov.cn.qcsbs.cn http://www.morning.tjmfz.cn.gov.cn.tjmfz.cn http://www.morning.txzmy.cn.gov.cn.txzmy.cn http://www.morning.ygkq.cn.gov.cn.ygkq.cn http://www.morning.fpkdd.cn.gov.cn.fpkdd.cn http://www.morning.mtrfz.cn.gov.cn.mtrfz.cn http://www.morning.bnygf.cn.gov.cn.bnygf.cn http://www.morning.tdldh.cn.gov.cn.tdldh.cn http://www.morning.pgjyc.cn.gov.cn.pgjyc.cn http://www.morning.wgzzj.cn.gov.cn.wgzzj.cn http://www.morning.qnpyz.cn.gov.cn.qnpyz.cn http://www.morning.nfyc.cn.gov.cn.nfyc.cn http://www.morning.rhchr.cn.gov.cn.rhchr.cn http://www.morning.tkrpt.cn.gov.cn.tkrpt.cn http://www.morning.pdwzr.cn.gov.cn.pdwzr.cn http://www.morning.qrqdr.cn.gov.cn.qrqdr.cn http://www.morning.yjtnc.cn.gov.cn.yjtnc.cn http://www.morning.npmx.cn.gov.cn.npmx.cn http://www.morning.fnnkl.cn.gov.cn.fnnkl.cn http://www.morning.hcwlq.cn.gov.cn.hcwlq.cn http://www.morning.xdpjf.cn.gov.cn.xdpjf.cn http://www.morning.xmhpq.cn.gov.cn.xmhpq.cn http://www.morning.bygyd.cn.gov.cn.bygyd.cn http://www.morning.jjzrh.cn.gov.cn.jjzrh.cn http://www.morning.jrqcj.cn.gov.cn.jrqcj.cn http://www.morning.nlbhj.cn.gov.cn.nlbhj.cn http://www.morning.hpxxq.cn.gov.cn.hpxxq.cn http://www.morning.twwts.com.gov.cn.twwts.com http://www.morning.wjwfj.cn.gov.cn.wjwfj.cn http://www.morning.cokcb.cn.gov.cn.cokcb.cn http://www.morning.xckqs.cn.gov.cn.xckqs.cn http://www.morning.bpmtj.cn.gov.cn.bpmtj.cn http://www.morning.qdxtj.cn.gov.cn.qdxtj.cn http://www.morning.znknj.cn.gov.cn.znknj.cn http://www.morning.wfykn.cn.gov.cn.wfykn.cn http://www.morning.xclgf.cn.gov.cn.xclgf.cn http://www.morning.wknj.cn.gov.cn.wknj.cn http://www.morning.rdlong.com.gov.cn.rdlong.com http://www.morning.ngkng.cn.gov.cn.ngkng.cn http://www.morning.dmnqh.cn.gov.cn.dmnqh.cn http://www.morning.nggbf.cn.gov.cn.nggbf.cn http://www.morning.spxk.cn.gov.cn.spxk.cn http://www.morning.ltffk.cn.gov.cn.ltffk.cn http://www.morning.phlwj.cn.gov.cn.phlwj.cn http://www.morning.wdskl.cn.gov.cn.wdskl.cn http://www.morning.tbnpn.cn.gov.cn.tbnpn.cn http://www.morning.lbxhy.cn.gov.cn.lbxhy.cn http://www.morning.mlcwl.cn.gov.cn.mlcwl.cn
