ClickHouse 入门(二)【基础SQL操作】

1、ClickHouse 

1.1、SQL 操作

这里只介绍一些和我们之前 MySQL 不同的语法;

1.1.1、Update 和 Delete

        ClickHouse 提供了 Delete 和 Update 的能力,这类操作被称为 Mutation 查询(可变查询),它可以看 做 Alter 的一种。

         虽然可以实现修改和删除,但是和一般的 OLTP 数据库不一样,Mutation 语句是一种很 “重”的操作,而且不支持事务

         “重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区,重建新分区。 所以尽量做批量的变更,不要进行频繁小数据的操作。

        由于操作比较“重”,所以 Mutation 语句分两步执行,同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候,才会删除旧数据释放磁盘空间,一般不会开放这样的功能给用户,由管理员完成。

1)删除操作

语法:

alter table order_info_5 delete where id = '101';

查看数据目录:

2)修改操作
alter table table_name update total_amount = toDecimal32(2000.0,2) where id = '102';

再次查看数据目录:

        可以看到,刚刚删除数据之后目录已经被自动合并,但是现在我们更新操作之后,它并不是在原本的数据之上直接更新,而是把更新后数据写入到一个新的目录!所以我们说它是一个重量级的操作;

优化思路

        正因为删除和更新操作太过重量级,会发生大量的磁盘 IO,所以我们可以给它加一个标记字段:

  • _isDelete:如果为 0 代表删除,如果为 1 代表可以被访问。
  • _version:更新操作都改用插入完成,只是对版本+1

这样我们只需要在使用的时候加个过滤即可

1.1.2、多维分析

ck 的查询和标准 SQL 差别不大:

  • 支持子查询
  • 支持 CTE(with ... as 语法)
  • 支持 join,但是不建议使用
  • 窗口函数

多维分析指的就是从多个维度进行分析,不同的聚合逻辑的粒度是不同的,所以分析结果也是不同的,比如学生总成绩:

  • 如果 group by 学校,就可以得到每个学校每个班级的总成绩
  • 如果 group by 学生,得到的就是各个学校总成绩

多维分析,从 SQL 中体现就是这样:

select xxx from xxx group by a,b
union all
select xxx from xxx group by a
union all
select xxx from xxx group by b
union all
select xxx from xxx

        当维度非常多的时候,n 个维度有 2^n 个组合,我们不可能自己手写出来,所以 ck 在 group by 语句后面提供了 with rollup\with cube\with tota;

        同样,Hive 其实也提供了多维分析的语法(grouping sets(xxx...)):

 

同样,毕竟当维度很多的时候,这种语法依然费手,所以 Hive 同样提供了 cubes 和 rollups 语法:

 下面我们插入测试数据演示一下:

1、with rollup 上卷

上卷指的是必须从最大粒度开始 group by,要有先后顺序:

select xxx from xxx
select xxx from xxx group by a
select xxx from xxx group by a,b

查询结果:

        可以看到,它的查询结果是从细粒度到粗粒度,这样做的好处是粗粒度计算结果可以直接从细粒度中聚合得到,就不需要重新大量计算了!这也是一种优化; 

:空字段会用默认值代替,这里的 id 是 UInt 所以默认是 0,这里的 sku_id 是字符串默认是空串; 

2、with cube 多维分析

也就是所有的组合情况它都得有:

select xxx from xxx group by a,b
select xxx from xxx group by a
select xxx from xxx group by b
select xxx from xxx

 查询结果:

3、with totals 总计

总计指的是维度要么都有,和都没有这两种情况

select xxx from xxx group by a,b
select xxx from xxx

查询结果:

 

1.1.3、alter 操作

上面我们已经使用过 alter table 进行删除和更新数据了;

新增字段

得益于 ck 是面向列的数据库,所以新增字段对它性能的影响并不大; 

alter table table_name add column new_col String after age
修改字段类型
alter table table_name modify column column_name String
删除字段
alter table table_name drop column col_name

1.1.4、导出数据

clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames > /opt/module/data/rs1.csv

这个用得不多;

1.2、副本引擎

        副本的目的主要是保障数据的高可用性;

1.2.1、副本写入流程

        在 ck 集群中并没有 master/slave 之分,当客户端向任意 ck 节点写入时,该 ck 节点都会向 zookeeper 提交写入日志,这是 zk 的 watch 机制就会通知到 ck 集群中的其他节点,这些节点就会去该节点去下载同步数据;

1.2.2、配置副本

在 hadoop102 的/etc/clickhouse-server/config.d 目录下创建一个名为 zk.xml(任意名) 的配置文件,内容如下:

<?xml version="1.0"?> 
<yandex> <zookeeper-servers> <node index="1"> <host>hadoop102</host> <port>2181</port> </node> <node index="2"> <host>hadoop103</host> <port>2181</port> </node> <node index="3"> <host>hadoop104</host> <port>2181</port> </node> </zookeeper-servers> 
</yandex> 

完了记得把这个文件的所有者和所在组设为 clickhouse:clickhouse:

chown -R clickhouse:clickhouse ./zk.xml

然后在 config.xml 中添加(指定外部文件): 

<zookeeper incl="zookeeper-servers" optional="true" /> <include_from>/etc/clickhouse-server/config.d/zk.xml</include_from>

完了之后,分发配置文件 config.xml 和 zk.xml,启动 zk 集群,然后重启 ck 集群;

要使用副本,必须使用副本表,官网中也说了,只有 MergeTree 支持副本表!

下面我们创建一张具有两个副本的表(hadoop102 和 hadoop103):

注意副本只能同步数据,不能同步表结构,所以表需要自己创建!

在 hadoop102 创建副本表:

注:这里的 01 是分片名称,这里是默认写法; 

在 hadoop103 创建:

创建成功后,我们可以在 zookeeper 中看到节点的信息(可以看出,ck 还是比较依赖 zookeeper 的):

此时,我们向 hadoop103 写入数据:

按照上面的同步原理,hadoop102 应该是可以查到的:

1.3、分片集群

        副本虽然能够提高数据的可用性,降低丢失风险,但是每台服务器实际上必须容纳全量 数据,对数据的横向扩容没有解决。

        要解决数据水平切分的问题,需要引入分片的概念。通过分片把一份完整的数据进行切 分,不同的分片分布到不同的节点上,再通过 Distributed 表引擎把数据拼接起来一同使用。

        Distributed 表引擎本身不存储数据,有点类似于 MyCat 之于 MySql,成为一种中间件, 通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。

注意:ClickHouse 的集群是表级别的,实际企业中,大部分做了高可用,但是没有用分片,避免降低查询性能以及操作集群的复杂性。

1.3.1、集群写入流程(3分片2副本)

这里有个 internal_replication 的参数,它决定了是否内部同步:

  • 当它为 true 的时候,客户端写入的数据,首先会同步到第一个切片副本,然后这个副本会自动同步一份给另一个副本;然后以此类推,最终客户端只需要写入 3 次(和分片数一致);
  • 当这个参数为 false 的时候,6 个副本都将由客户端来写入。这样会使客户端压力很大;

1.3.2、集群读取流程(3 分片 2 副本)

1.3.3、副本配置(2 分片 3 副本) 

这里我们配置 2 个分片,3 个副本:

hadoop102分片1分片1副本1
hadoop103分片1分片1副本2
hadoop104分片2分片2副本1
<yandex> <remote_servers> <gmall_cluster> <!-- 集群名称--> <shard> <!--集群的第一个分片--> <internal_replication>true</internal_replication> <!--该分片的第一个副本--> <replica> <host>hadoop102</host> <port>9000</port> </replica> <!--该分片的第二个副本--> <replica> <host>hadoop103</host> <port>9000</port> </replica> </shard> <shard> <!--集群的第二个分片--> <internal_replication>true</internal_replication> <replica> <!--该分片的第一个副本--> <host>hadoop104</host> <port>9000</port> </shard> </gmall_cluster> </remote_servers> 
<zookeeper-servers> <node index="1"> <host>hadoop102</host> <port>2181</port> </node> <node index="2"> <host>hadoop103</host> <port>2181</port> </node> <node index="3"> <host>hadoop104</host> <port>2181</port> </node> </zookeeper-servers> <macros> <shard>01</shard> <!--不同机器放的分片数不一样--> <replica>rep_1_1</replica> <!--不同机器放的副本数不一样--> </macros>
</yandex>

        这里最后的标签<macros> 是 "宏" 的意思,其中的子标签 <shard> 和 <replica> 是我们自定义的,为的是将来建表时能够从配置文件直接读取;每个节点的宏是不一样的,需要根据我们的分片副本规则来调整(同一个分片的相同副本不能存储在一个节点,不然会报错);上面是 hadoop102 的配置,剩余的节点的宏配置如下(别的配置都一样):

hadoop103: 

 <macros> <shard>01</shard> <!--分片1--> <replica>rep_1_2</replica> <!--分片1的第2个副本--> </macros>

hadoop104

 <macros> <shard>02</shard> <!--分片1--> <replica>rep_2_1</replica> <!--分片1的第2个副本--> </macros>

完了重启三台节点的 clickhouse 服务,可以 show clusters; 命令查看是否已经识别配置中的集群名称;

创建表

如果希望使用分片副本就需要再建表语句的表名后面加上 ON CLUSTER 集群名称

hadoop102 创建完成后,其余两台节点都会自动完成创建;

创建分布式表

       上面的表中存储的只是自己分片所负责存储的信息,虽然现在表中还没有存储数据(不能直接往里插入数据,那么分片就无效了,而是应该往分布式表插入,在分布式表中定义分片的规则):

 参数名称:Distributed(集群名称,库名,本地表名,分片键)

注意:这里的本地表名指的是本地的分片表表名!

上面我们创建了一张分布式表,我们根据 sku_id 来进行分片; 下面我们向分布式表中插入数据:

查询分布式表(全量数据)

首先,我们查询分布式表:

分布式表中存储的是全量的数据,这也是我们为什么要创建它的原因;

查询分片表(分片数据)

 

可以看到,我们的数据根据 sku_id 被分到不同的分片中了; 

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

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

相关文章

C语言 | Leetcode C语言题解之第241题为运算表达式设计优先级

题目&#xff1a; 题解&#xff1a; #define ADDITION -1 #define SUBTRACTION -2 #define MULTIPLICATION -3int* diffWaysToCompute(char * expression, int* returnSize) {int len strlen(expression);int *ops (int *)malloc(sizeof(int) * len);int opsSize 0;for (in…

钡铼分布式 IO 系统 OPC UA边缘计算耦合器BL205

深圳钡铼技术推出的BL205耦合器支持OPC UA Server功能&#xff0c;以服务器形式对外提供数据。符合IEC 62541工业自动化统一架构通讯标准&#xff0c;数据可以选择加密&#xff08;X.509证书&#xff09;、身份验证方式传送。 安全策略支持basic128rsa15、basic256、basic256s…

Web开发:ASP.NET CORE前后端交互之AJAX(含基础Demo)

目录 一、后端 二、前端 三、代码位置 四、实现效果 五、关键的点 1.后端传输给前端&#xff1a; 2.前端传输给后端 一、后端 using Microsoft.AspNetCore.Mvc; using Microsoft.AspNetCore.Mvc.RazorPages; using Microsoft.AspNetCore.Mvc.Rendering; using WebAppl…

LNMP环境配置问题整理

首先是一键安装直接报错: 换教程:搭建LNMP,步骤最详细,附源码,学不会打我-CSDN博客 mysql安装成功之后: MySQL 启动报错:Job for mysqld.service failed because the control process exited with error code. 如果所有方法都试过之后卸载后重装可以快速解决: 参考…

AI算不出9.11和9.9哪个大?六家大模型厂商总结了这些原因

大模型“答对”或“答错”其实是个概率问题。关于“9.11和9.9哪个大”&#xff0c;这样一道小学生难度的数学题难倒了一众海内外AI大模型。7月17日&#xff0c;第一财经报道了国内外“12个大模型8个都会答错”这道题的现象&#xff0c;大模型的数学能力引发讨论。 “从技术人员…

idea双击没有反应,打不开

问题描述 Error opening zip file or JAR manifest missing : /home/IntelliJ-IDEA/bin/jetbrains-agent.jar解决方案

第三篇 Vue项目目录结构介绍

1、最外层目录结构 passagerFrontPage ├── .vscode //vscode配置&#xff0c;不用理会 ├── node_modules //项目依赖&#xff0c;npm install命令执行后自动生成 ├── public //公共资源存放 ├── src //源码 ├── tests //选装&#xff1a;测试模块 ├── .git…

STM32智能安防系统教程

目录 引言环境准备智能安防系统基础代码实现&#xff1a;实现智能安防系统 4.1 数据采集模块 4.2 数据处理与控制模块 4.3 通信与网络系统实现 4.4 用户界面与数据可视化应用场景&#xff1a;家庭与企业安防管理问题解决方案与优化收尾与总结 1. 引言 智能安防系统通过STM32…

逻辑门的题目怎么做?

FPGA语法练习——二输入逻辑门&#xff0c;一起来听~~ FPGA语法练习——二输入逻辑门 题目介绍&#xff1a;F学社-全球FPGA技术提升平台 (zzfpga.com)

ABAP使用SQL直接更新数据库与使用IN UPDATE TASK的区别

1. 背景 刚接触ABAP的小伙伴常常会有这样的疑问&#xff0c;为什么不直接使用Open SQL直接更新数据库&#xff0c;而要把对DB的操作封装到IN UPDATE TASK中呢&#xff1f; 对于这个问题&#xff0c;比较常见的解释是&#xff0c;IN UPDATE TASK的方式会保证数据更新的一致性。…

Artix7系列FPGA实现SDI视频编解码+UDP以太网传输,基于GTP高速接口,提供工程源码和技术支持

目录 1、前言工程概述免责声明 2、相关方案推荐本博已有的 SDI 编解码方案本博已有的以太网方案本博已有的FPGA图像缩放方案本方案的缩放应用本方案在Xilinx--Kintex系列FPGA上的应用本方案在Xilinx--Zynq系列FPGA上的应用 3、详细设计方案设计原理框图SDI 输入设备Gv8601a 均衡…

STM32智能城市交通管理系统教程

目录 引言环境准备智能城市交通管理系统基础代码实现&#xff1a;实现智能城市交通管理系统 4.1 数据采集模块 4.2 数据处理与控制模块 4.3 通信与网络系统实现 4.4 用户界面与数据可视化应用场景&#xff1a;城市交通管理与优化问题解决方案与优化收尾与总结 1. 引言 智能城…

Chromium CI/CD 之Jenkins实用指南2024- Windows节点开启SSH服务(七)

1.引言 在现代软件开发和持续集成的过程中&#xff0c;自动化部署和远程管理是不可或缺的关键环节。SSH&#xff08;Secure Shell&#xff09;协议以其强大的安全性和灵活性&#xff0c;成为连接和管理远程服务器的首选工具。对于使用Windows虚拟机作为Jenkins从节点的开发者而…

谷粒商城-全文检索-ElasticSearch

1.简介 一个分布式的开源搜索和分析引擎,可以 秒 级的从海量数据中检索 主要功能:做数据的检索和分析(MySQL专攻于数据的持久化存储与管理CRUD达到百万以上的数据MSQL就会很慢,海量数据的检索和分析还是要用ElasticSearch) 用途:我们电商项目里的所有的检索功能都是由Elasti…

均值滤波算法及实现

均值滤波器的使用场景&#xff1a; 均值滤波器使用于处理一些如上述蓝色线的高斯噪声场景 红色曲线是经过均值滤波处理后的数据。主要因为均值滤波设置数据缓冲区&#xff08;也即延时周期&#xff09;&#xff0c;使得测量值经过缓冲不会出现特别大的变化。 黄色曲线为高斯噪声…

【Node.js】初识 Node.js

Node.js 概念 Node.js 是一个开源与跨平台的 JavaScript运行时环境 &#xff0c;在浏览器外运行 V8 JavaScript 引擎(Google Chrome的内核)&#xff0c;利用事件驱动、非阻塞和异步输入输出 等技术提高性能。 可以理解为 Node.js就是一个服务器端的、非阻塞式 l/O 的、事件驱…

香橙派AIpro-携手华为-为AI赋能

文章目录 香橙派AIpro-携手华为-为AI赋能开箱和功能介绍开箱功能介绍 环境搭建镜像烧录进入系统 测试项目YOLOv5部署YOLOv5识别单张图片实时识别视频使用Ascend测试yolov5 产品评价 香橙派AIpro-携手华为-为AI赋能 今天新入手了一款香橙派AIPro&#xff0c;让我们一起跟着文章…

【iOS】—— 消息传递和消息转发

【iOS】—— 消息传递和消息转发 1. 消息传递SEL选择子IMP快速查找汇编代码查找过程总结消息转送快速查找IMP 慢速查找总结消息传递慢速查找IMP 2. 消息转发动态决议动态解析添加方法 快速转发慢速转发 总结动态决议消息转发消息的三次拯救 1. 消息传递 在iOS中&#xff0c;消…

python 获取Shopee虾皮商家店铺商品列表 虾皮api数据采集

此api接口可用于获取虾皮平台商家店铺的商品列表&#xff0c;目前land参数支持id、vn、my、th、sg、ph、tw&#xff08;印尼、越南、马来、泰国、新加坡、菲律宾、台湾&#xff09;。 若有需要&#xff0c;请点击文末链接联系我们。 详细采集页面如下https://shopee.tw/yuesh…

【Python】数据处理(mongodb、布隆过滤器、索引)

数据 数据预处理 df pd.read_csv(file_path, encodingANSI) csv的编码方式一定要用 ANSI。要不然会出现各种报错 import pandas as pd from datetime import datetime# 读取CSV文件 file_path book_douban.csv df pd.read_csv(file_path, encodingANSI)# 定义一个函数来…