Clickhouse学习笔记（3）—— Clickhouse表引擎

news/2024/5/8 4:52:13/文章来源:https://blog.csdn.net/qq_51235856/article/details/134342975

前言：

有关Clickhouse的前置知识详见：

1.ClickHouse的安装启动_clickhouse后台启动_THE WHY的博客-CSDN博客

2.ClickHouse目录结构_clickhouse 目录结构-CSDN博客

Cickhouse创建表时必须指定表引擎

表引擎（即表的类型）决定了：

  • 数据的存储方式和位置，写到哪里以及从哪里读取数据

数据一般存储在本地，默认路径是/var/lib/clickhouse/

除此之外也可以集成一些外部的数据库，如Hive，MySQL等

  • 支持哪些查询以及如何支持

数组在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_level=trace <<< 'select * from t_order_mt2 where total_amount > toDecimal32(900., 2)';

可以看到：

index a在查询过程中起到了粒度划分的作用；

TTL

MergeTree | ClickHouse Docs

TTL 即 Time To Live，MergeTree 提供了可以管理数据表或者列的生命周期的功能

对于表和列都可以指定TTL；

指定列的TTL（建表时）

TTL time_column + interval

建表测试：

create table t_order_mt3(\id UInt32,\sku_id String,\total_amount Decimal(16,2) TTL create_time+interval 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

可以看到成功聚合：

根据聚合表的特性，在实际开发中设计聚合表时，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.luyixian.cn/news_show_190741.aspx

如若内容造成侵权/违法违规/事实不符，请联系dt猫网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

广州华锐互动：VR互动实训内容编辑器助力教育创新升级

广州华锐互动：VR互动实训内容编辑器助力教育创新升级

随着科技的飞速发展&#xff0c;教育领域也正在经历一场深刻的变革。其中&#xff0c;虚拟现实(VR)技术为教学活动提供了前所未有的便利和可能性。在诸多的VR应用中&#xff0c;VR互动实训内容编辑器无疑是最具潜力和创新性的一种。广州华锐互动开发的这款编辑器以其独特的功能…
阅读更多...
如何利用IP代理进行海外推广？

如何利用IP代理进行海外推广？

在当今数字化的时代&#xff0c;网络营销已经成为企业策略的重要组成部分。而对于进去海外市场的跨境玩家来说&#xff0c;海外的推广推广是重中之重。然而&#xff0c;在开展推广的过程中&#xff0c;我们常常会遇到各种挑战&#xff0c;如地域限制、访问速度慢等。 为了解决…
阅读更多...
VScode + opencv（cmake编译） + c++ + win配置教程

VScode + opencv（cmake编译） + c++ + win配置教程

1、下载opencv 2、下载CMake 3、下载MinGW 放到一个文件夹中 并解压另外两个文件 4、cmake编译opencv 新建文件夹mingw-build 双击cmake-gui 程序会开始自动生成Makefiles等文件配置&#xff0c;需要耐心等待一段时间。 简单总结下&#xff1a;finish->configuring …
阅读更多...
图像实验室搭建

图像实验室搭建

#搭建一个简单的图像实验室#&#xff0c;满足中低端camera产品的调试和测试需求。 目录 &#xff08;1&#xff09;实验室的功能 &#xff08;2&#xff09;实验室的设备 1、光源 2、图卡 3、辅助设备&#xff08;升级&#xff09; &#xff08;3&#xff09;实验室的布…
阅读更多...
Spark SQL 每年的1月1日算当年的第一个自然周, 给出日期，计算是本年的第几周

Spark SQL 每年的1月1日算当年的第一个自然周, 给出日期，计算是本年的第几周

一、问题 按每年的1月1日算当年的第一个自然周 (遇到跨年也不管&#xff0c;如果1月1日是周三&#xff0c;那么到1月5号&#xff08;周日&#xff09;算是本年的第一个自然周, 如果按周一是一周的第一天) 计算是本年的第几周&#xff0c;那么 spark sql 如何写 ? 二、分析 …
阅读更多...
2013年01月23日 Go生态洞察：使用 go fmt 格式化你的代码 ✨

2013年01月23日 Go生态洞察：使用 go fmt 格式化你的代码 ✨

&#x1f337;&#x1f341; 博主猫头虎&#xff08;&#x1f405;&#x1f43e;&#xff09;带您 Go to New World✨&#x1f341; &#x1f984; 博客首页——&#x1f405;&#x1f43e;猫头虎的博客&#x1f390; &#x1f433; 《面试题大全专栏》 &#x1f995; 文章图文…
阅读更多...
如何计算掩膜图中多个封闭图形的面积

如何计算掩膜图中多个封闭图形的面积

import cv2def calMaskArea(image,idx):mask cv2.inRange(image, idx, idx)contours, hierarchy cv2.findContours(mask, cv2.RETR_EXTERNAL, cv2.CHAIN_APPROX_NONE)for contour in contours:area cv2.contourArea(contour)print("图形的面积为", area) image是…
阅读更多...
前端---认识HTML

前端---认识HTML

文章目录 什么是HTML&#xff1f;HTML的读取、运行HTML的标签注释标签标题标签段落标签换行标签格式化标签图片标签a标签表格标签列表标签表单标签form标签input标签文本框单选框复选框普通按钮提交按钮文件选择框 select标签textarea标签特殊标签div标签span标签 什么是HTML&a…
阅读更多...
11.13 牛客刷题8/10

11.13 牛客刷题8/10

11.13 信号完整性 指针地址 的加减&#xff0c;注意 最后转为16进制
阅读更多...
防火防盗防小人 使用 Jasypt 库来加密配置文件

防火防盗防小人 使用 Jasypt 库来加密配置文件

⚔️ 项目配置信息存放在哪&#xff1f; 在日常开发工作中&#xff0c;我们经常需要使用到各种敏感配置&#xff0c;如数据库密码、各厂商的 SecretId、SecretKey 等敏感信息。 通常情况下&#xff0c;我们会将这些敏感信息明文放到配置文件中&#xff0c;或者放到配置中心中。…
阅读更多...
使用Inis搭配内网穿透实现Ubuntu上快速搭建博客网站远程访问

使用Inis搭配内网穿透实现Ubuntu上快速搭建博客网站远程访问

文章目录 前言1. Inis博客网站搭建1.1. Inis博客网站下载和安装1.2 Inis博客网站测试1.3 cpolar的安装和注册 2. 本地网页发布2.1 Cpolar临时数据隧道2.2 Cpolar稳定隧道&#xff08;云端设置&#xff09;2.3.Cpolar稳定隧道&#xff08;本地设置&#xff09; 3. 公网访问测试总…
阅读更多...
基于springboot+vue的学生毕业离校信息网站

基于springboot+vue的学生毕业离校信息网站

项目介绍 该学生毕业离校系统包括管理员、学生和教师。其主要功能包括管理员&#xff1a;首页、个人中心、学生管理、教师管理、离校信息管理、费用结算管理、论文审核管理、管理员管理、留言板管理、系统管理等&#xff0c;前台首页&#xff1b;首页、离校信息、网站公告、留…
阅读更多...
《网络协议》04. 应用层（DNS DHCP HTTP）

《网络协议》04. 应用层（DNS DHCP HTTP）

title: 《网络协议》04. 应用层&#xff08;DNS & DHCP & HTTP&#xff09; date: 2022-09-05 14:28:22 updated: 2023-11-12 06:55:52 categories: 学习记录&#xff1a;网络协议 excerpt: 应用层、DNS、DHCP、HTTP&#xff08;URI & URL&#xff0c;ABNF&#xf…
阅读更多...
芯片设计工程师必备基本功——《设计与验证：Verilog HDL》

芯片设计工程师必备基本功——《设计与验证：Verilog HDL》

Verilog HDL 作为两大硬件描述语言之一&#xff0c;拥有很大的用户群。据调查&#xff0c;目前美国有 90%左右的 IC 设计人员使用 Verilog. 在中国&#xff0c;大概再 50% 左右的人在使用 Verilog 。 大量高校毕业生和部分软件设计人员正在不断涌入这个领域。要想尽快在 IC设计…
阅读更多...
Git版本控制系统之分支与标签（版本）

Git版本控制系统之分支与标签（版本）

目录 一、Git分支&#xff08;Branch&#xff09; 1.1 分支作用 1.2 四种分支管理策略 1.3 使用案例 1.3.1 指令 1.3.2 结合应用场景使用 二、Git标签&#xff08;Tag&#xff09; 2.1 标签作用 2.2 标签规范 2.3 使用案例 2.3.1 指令 2.3.2 使用示例 一、Git分支&…
阅读更多...
C++——const成员

C++——const成员

这里先用队列举例&#xff1a; #define _CRT_SECURE_NO_WARNINGS 1 #include <iostream> #include <assert.h> using namespace std; class SeqList { public:void pushBack(int data){if (_size _capacity){int* tmp (int*)realloc(a, sizeof(int) * 4);if (tm…
阅读更多...
新零售时代，传统便利店如何转型？

新零售时代，传统便利店如何转型？

在零售批发业&#xff0c;如何降低各环节成本、提高业务运转效率、更科学地了解客户服务客户&#xff0c;是每家企业在激烈竞争中需要思考的课题。 对零售批发企业来说&#xff0c;这些问题或许由来已久&#xff1a; &#xff08;1&#xff09;如何对各岗位的员工进行科学的考…
阅读更多...
阿里云国际站：全球加速GA

阿里云国际站：全球加速GA

文章目录 一、前言 二、阿里云全球加速的概念 三、阿里云全球加速的功能优势 四、阿里云全球加速的原理 五、阿里云全球加速的应用场景 六、写在最后 一、前言 随着互联网的快速发展&#xff0c;网站速度已经成为了用户访问体验的一个重要指标。阿里云加速作为一种新的技…
阅读更多...
【开源】基于Vue.js的大学兼职教师管理系统的设计和实现

【开源】基于Vue.js的大学兼职教师管理系统的设计和实现

目录 一、摘要1.1 项目介绍1.2 项目详细录屏 二、研究内容三、界面展示3.1 登录注册3.2 学生教师管理3.3 课程管理模块3.4 授课管理模块3.5 课程考勤模块3.6 课程评价模块3.7 课程成绩模块3.8 可视化图表 四、免责说明 一、摘要 1.1 项目介绍 大学兼职教师管理系统&#xff0…
阅读更多...
【c++随笔12】继承

【c++随笔12】继承

【c随笔12】继承 一、继承1、继承的概念2、3种继承方式3、父类和子类对象赋值转换4、继承中的作用域——隐藏5、继承与友元6、继承与静态成员 二、继承和子类默认成员函数1、子类构造函数 二、子类拷贝构造函数3、子类的赋值重载4、子类析构函数 三、单继承、多继承、菱形继承1…
阅读更多...
推荐文章
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站 广利置业