网站可以做砍价软件吗,罗岗网站建设公司,电子商务网站建设作品,企业网站建设属于什么科目前言#xff1a; 有关Clickhouse的前置知识详见#xff1a; 1.ClickHouse的安装启动_clickhouse后台启动_THE WHY的博客-CSDN博客 2.ClickHouse目录结构_clickhouse 目录结构-CSDN博客 Cickhouse创建表时必须指定表引擎
表引擎#xff08;即表的类型#xff09;决定了 有关Clickhouse的前置知识详见 1.ClickHouse的安装启动_clickhouse后台启动_THE WHY的博客-CSDN博客 2.ClickHouse目录结构_clickhouse 目录结构-CSDN博客 Cickhouse创建表时必须指定表引擎
表引擎即表的类型决定了
数据的存储方式和位置写到哪里以及从哪里读取数据 数据一般存储在本地默认路径是/var/lib/clickhouse/ 除此之外也可以集成一些外部的数据库如HiveMySQL等 支持哪些查询以及如何支持 数组在mergetree引擎中无法使用 并发数据访问索引的使用如果存在是否可以执行多线程请求数据复制参数
TinyLog
以列文件的形式保存在磁盘上不支持索引没有并发控制。一般保存少量数据的小表生产环境上作用有限。可以用于平时练习测试用
Memory
内存引擎数据以未压缩的原始形式直接保存在内存当中服务器重启数据就会消失。读写操作不会相互阻塞不支持索引。简单查询下有非常非常高的性能表现超过 10G/s。
一般用到它的地方不多除了用来测试就是在需要非常高的性能同时数据量又不太大上限大概 1 亿行的场景
MergeTree*合并树
MergeTree支持索引和分区
建表语句如下
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);
需要注意的是clickhouse中主键会自动创建索引但并不唯一
而且order by设置的排序是在分区内排序
插入数据
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);
进行查询 可以看到通过命令行查询出的数据可以明显观察到分区
语法知识
MergeTree | ClickHouse Docs 可以看到primary key 和 partition by字段都不是必须的但order by字段是必须的
分区合并
分区的目的主要是降低扫描的范围优化查询速度
在hive中分区是通过HDFS中分目录实现的clickhouse中也是通过分目录实现的只不过是在本地磁盘
MergeTree 是以列文件索引文件表定义文件组成的但是如果设定了分区那么这些文件就会保存到不同的分区目录中 具体操作
向表中插入数据 在本地按分区存储数据 再次插入数据 可以看到数据并没有纳入任何分区这是因为任何一个批次的数据写入都会产生一个临时分区不会纳入任何一个已有的分区写入后的某个时刻大概 10-15 分钟后ClickHouse 会自动执行合并操作
也可以手动通过 optimize 执行把临时分区的数据合并到已有分区中
optimize table xxxx final 详细语法见OPTIMIZE Statement | ClickHouse Docs 查看数据文件可以看到合并后的分区数据
可以看到最小分区块编号、最大分区块编号和合并层级都发生了变化 需要注意手动执行分区合并后会生成新的数据文件但过期数据不会立即删除
等到自动合并操作执行后过期数据就会被删除了因此过一段时间再去查看 除此之外optimize还可以指定要合并的分区
optimize table xxxx PARTITION partition final;
示例
插入一些数据目前的分区如下 接下来只合并分区id为20200601的数据
optimize table t_order_mt partition 20200601 final;
合并结果如下 primary key
MergeTree | ClickHouse Docs
只提供了数据的一级索引但是却不是唯一约束主键的设定主要依据是查询语句中的 where 条件根据条件通过对主键进行某种形式的二分查找能够定位到对应的 index granularity避免了全表扫描 index granularity索引粒度也就是在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse 中的 MergeTree 默认是 8192官方不建议修改这个值除非该列存在大量重复值比如在一个分区中几万行才有一个不同数据 稀疏索引的好处就是可以用很少的索引数据定位更多的数据代价就是只能定位到索引粒度的第一行然后再进行进行一点扫描 order by
order by进行分区内排序是必须设置的因为clickhouse使用稀疏索引如果数据无序无法根据索引来进行定位主键必须是 order by 字段的前缀字段 比如 order by 字段是 (id,sku_id) 那么主键必须是 id 或者(id,sku_id) 假如主键是sku_id那么可以发现数据在主键维度上是无序的索引依然无法定位 二级索引
clickhouse从v20.1.2.4 开始全面支持二级索引 创建二级索引的语法 INDEX a total_amount TYPE minmax GRANULARITY 5 索引名 对应的列 二级索引的类型 粒度 注意这里的粒度指的是二级索引相对于一级索引的粒度 测试
建表
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);
插入数据
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);
可以看到 index a在查询过程中起到了粒度划分的作用
TTL
MergeTree | ClickHouse Docs
TTL 即 Time To LiveMergeTree 提供了可以管理数据表或者列的生命周期的功能
对于表和列都可以指定TTL
指定列的TTL建表时
TTL time_column interval
建表测试
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);
对total_amount列设置了TTL
插入数据
insert into t_order_mt3 values \
(106,sku_001,1000.00,2023-07-31 20:45:10),\
(107,sku_002,2000.00,2023-07-31 20:45:10),\
(110,sku_003,600.00,2023-07-31 20:45:10);
插入完成后可以正常查询到数据 等待到20:45:20之后再次查询
发现依然能查询到数据 可能是因为尚未合并导致的因此手动合并
optimize table t_order_mt3 final
发现字段值已经清空 如果没有反应可以尝试重启以下clickhouse的服务器因为TTL操作是单独开启一个进程去完成的如果机器资源较少可能出现应答不及时的情况
修改列的TTL
语法 通过MODIFY COLUMN实现简单来说就是重新定义一下这个列
指定表的TTL
语法 就是在ORDER BY后面设置TTL即可
官网给出了TTL到达后的三种策略
DELETE删除对应数据
TO DISK aaa将数据移动到磁盘aaa
TO VOLUME bbb将数据移动到磁盘bbb
修改表的TTL
语法 ReplacingMergeTree去重
ReplacingMergeTree 是 MergeTree 的一个变种它存储特性完全继承 MergeTree只是
多了一个去重的功能根据order by字段进行去重而不是主键
去重时机数据的去重只会在合并的过程中出现合并会在未知的时间在后台进行所以你无法预先作出计划。有一些数据可能仍未被处理 在新版本中插入数据时会先进行一次去重 去重范围分区内去重无法跨分区去重
测试
创建表指定引擎为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);
查询结果如下 与下图对比可知在插入数据时已经进行了去重 注意到有两条数据的版本字段相同 最终保留的数据是 因此可以看到但版本字段相同时按照插入顺序保留最后一条 接下来再次插入数据查询结果如下 可以看到同一分区内的数据并未进行去重
因此手动执行合并后再查询 可以看到已经进行了去重 SummingMergeTree聚合
适用于不查询明细只关心以维度进行汇总聚合结果的场景可以避免因临时聚合而带来的开销 测试
创建表指定引擎为SummingMergeTree
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 );
注意SummingMergeTree()中的字段为聚合字段即在哪一维度上进行聚合这里指定的是total_amount也可以指定多个字段但必须是数值类型 如果不填以所有非维度列且为数字列的字段为汇总数据列 插入数据
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);
查询结果如下 首先可以发现SummingMergeTree是以order by的列作为维度列进行聚合的而且是分区内聚合
同时可以看到同一分区内的相应数据已经进行了聚合 除了维度列和聚合字段之外create_time这一列保留最早插入的一行 再次插入数据进行测试 可以看到并未进行聚合 这是因为SummingMergeTree和ReplacingMergeTree一样都是只有在同一批次插入(新版本)或分片合并时才会进行聚合
因此手动执行合并optimize table t_order_smt final
可以看到成功聚合 根据聚合表的特性在实际开发中设计聚合表时唯一键值、流水号可以去掉所有字段全部是维度、度量或者时间戳
