李林超博客
首页
归档
留言
友链
动态
关于
归档
留言
友链
动态
关于
首页
大数据
正文
07.ClickHouse之SQL操作
Leefs
2022-05-01 PM
1413℃
0条
[TOC] ### 前言 基本上来说传统关系型数据库(以 MySQL 为例)的 SQL 语句,ClickHouse 基本都支持, 这里不会从头讲解 SQL 语法只介绍 ClickHouse 与标准 SQL(MySQL)不一致的地方。 ### 一、CREATE #### 1.1 创建数据库 ```sql #用于创建指定名称的数据库 CREATE DATABASE [IF NOT EXISTS] db_name; ``` #### 1.2 创建数据表 **语法如下** ```sql CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ENGINE = engine ``` + **DEFAULT expr**:默认值,用法与SQL类似。 + **MATERIALIZED expr**:物化表达式,被该表达式指定的列不能被INSERT,因为它总是被计算出来的,对于INSERT而言,不需要考虑这些列。 另外,在SELECT查询中如果包含星号,此列不会被查询。 + **ALIAS expr**:别名 **创建表的三种方式:** **(1)直接创建** ```sql create table t1(id UInt16,name String) engine=TinyLog ``` **(2)创建一个与其他表具有相同结构的表** ```sql CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine] ``` **(3)使用指定的引擎创建一个与SELECT子句的结果具有相同结构的表,并使用SELECT子句的结果填充它** ```sql CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ... ``` ### 二、Insert 基本与标准 SQL(MySQL)基本一致 **(1)标准** ```sql insert into [table_name] values(…),(….) ``` + 主要用于向表中添加数据 **(2)从表到表的插入** ```sql insert into [table_name] select a,b,c from [table_name_2] ``` + 用于根据查询条件向表中同步数据 ### 三、Update 和 Delete + ClickHouse 提供了 Delete 和 Update 的能力,这类操作被称为 Mutation 查询,可以看作是 Alter 的一种。 + 虽然可以实现修改和删除,但是和一般的 OLTP 数据库不一样,**Mutation 语句是一种很“重”的操作,而且不支持事务。** + “重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区,重建新分区。所以尽量做批量的变更,不要进行频繁小数据的操作。 **(1)删除操作** ```sql alter table t_order_smt delete where sku_id ='sku_001'; ``` ![07.ClickHouse之SQL操作01.jpg](https://lilinchao.com/usr/uploads/2022/05/3523818126.jpg) **(2)修改操作** ```sql alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id=102; ``` ![07.ClickHouse之SQL操作02.jpg](https://lilinchao.com/usr/uploads/2022/05/514226724.jpg) 由于操作比较“重”,所以 Mutation 语句分两步执行,同步执行的部分其实只是进行 新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候,才会删 除旧数据释放磁盘空间,一般不会开放这样的功能给用户,由管理员完成。 ### 四、查询操作 ClickHouse 基本上与标准 SQL 差别不大 + 支持子查询; + 支持 CTE(Common Table Expression 公用表达式 with 子句); + 支持各种 JOIN,但是 JOIN 操作无法使用缓存,所以即使是两次相同的 JOIN 语句,ClickHouse 也会视为两条新的 SQL; + 窗口函数:目前最新版本已经支持; + 暂不支持自定义函数; + GROUP BY 操作增加了 with rollup、with cube、with total 用于按不同维度统计。 **(1)插入数据** ```sql 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, '2022-04-15 09:00:00'), (102, 'sku_002', 1500.00, '2022-04-15 10:30:00'), (102, 'sku_003', 2500.00, '2022-04-15 12:30:00'), (103, 'sku_004', 1500.00, '2022-04-15 13:00:00'), (104, 'sku_001', 10000.00, '2022-04-15 13:00:00'), (105, 'sku_002', 800.00, '2022-04-15 12:00:00'), (106, 'sku_002', 1500.00, '2022-04-15 10:30:00'), (107, 'sku_003', 2500.00, '2022-04-15 12:30:00'), (108, 'sku_004', 1500.00, '2022-04-15 13:00:00'), (109, 'sku_002', 10000.00, '2022-04-15 13:00:00'), (110, 'sku_003', 800.00, '2022-04-15 12:00:00'); ``` **(2)with rollup(上卷):从右至左去掉维度进行小计** ```sql select id,sku_id,sum(total_amount) from t_order_mt group by id,sku_id with rollup; ``` ![07.ClickHouse之SQL操作03.jpg](https://lilinchao.com/usr/uploads/2022/05/2297558381.jpg) **(3)with cube:从右至左去掉维度进行小计,再从左至右去掉维度进行小计** ```sql select id,sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube; ``` ![07.ClickHouse之SQL操作04.jpg](https://lilinchao.com/usr/uploads/2022/05/1648556003.jpg) **(4)with totals:只计算合计** ```sql select id,sku_id,sum(total_amount) from t_order_mt group by id,sku_id with totals; ``` ![07.ClickHouse之SQL操作05.jpg](https://lilinchao.com/usr/uploads/2022/05/666443680.jpg) ### 五、alter 操作 同 MySQL 的修改字段基本一致 **(1)新增字段** ```sql alter table tableName add column newcolname String after col1; ``` **(2)修改字段类型** ```sql alter table tableName modify column newcolname String; ``` **(3)删除字段** ```sql alter table tableName drop column newcolname; ``` ### 六、导出数据 ```sql clickhouse-client --port 9001 --query "select * from demo.t_order_mt where create_time='2022-04-15 12:00:00'" --format CSVWithNames > /home/hadoop/rs.csv ``` ![07.ClickHouse之SQL操作06.jpg](https://lilinchao.com/usr/uploads/2022/05/4064474197.jpg) ### 七、其他操作 ```sql -- 列出数据库列表 show databases; -- 查看当前使用的数据库 select currentDatabase(); -- 列出数据库中表列表 show tables; -- 查看表结构 desc test.t1 -- 删除一个表 drop table if exists test.t1 -- 删除一个库 drop database test -- 清空数据 truncate table test.t1 -- 重命名表 rename table tab1 to tab2 ``` *附参考文章链接:* *https://blog.csdn.net/yangwei234/article/details/124205182*
标签:
ClickHouse
非特殊说明,本博所有文章均为博主原创。
如若转载,请注明出处:
https://lilinchao.com/archives/2043.html
上一篇
【转载】06.ClickHouse表引擎之外部集成表引擎
下一篇
08.ClickHouse副本和分片介绍
取消回复
评论啦~
提交评论
栏目分类
随笔
2
Java
326
大数据
229
工具
31
其它
25
GO
47
标签云
二叉树
Http
HDFS
Git
MyBatisX
LeetCode刷题
Map
Spark RDD
序列化和反序列化
MyBatis-Plus
Spark Streaming
高并发
Filter
Java编程思想
JavaSE
递归
Quartz
Sentinel
Kafka
前端
正则表达式
排序
Scala
FastDFS
Spark SQL
Flink
Spring
gorm
Yarn
工具
友情链接
申请
范明明
庄严博客
Mx
陶小桃Blog
虫洞