Java技术债务Java技术债务

  •  首页
  •  分类
  •  归档
  •  标签
  • 博客日志
  • 资源分享
  •  友链
  •  关于本站
注册
登录

ClickHouse的SQL简单操作(四)

ClickHouse,数据库

文章目录


介绍

基本上来说传统关系型数据库(以 MySQL 为例)的 SQL 语句,ClickHouse 基本都支持, 这里不会从头讲解 SQL 语法只介绍 ClickHouse 与标准 SQL(MySQL)不一致的地方。

1 CREATE创建表(包含副本,分布式表创建)

# 创建单节点本地表
create table t_order_mt2 (
	id Uint32,
	sku_id String,
	total_amount Decimal(16,2) # TTL create_time+interval 10 second,//过期时间,也可以写在创建表末位,对表进行过期时间,非必选(秒、分、时、天、月、年等都可以)
	create_time Datetime,
	index a total_amount type minmax granularitys
) engine = mergeTree/ReplacingMergeTree(根据入参字段做去重依据,如果没有参数以最后一条数据为准)/SummingMergeTree(依据参数字段进行聚合,可多个)
parition by toYYYYMMDD(create_time)
primary by (id,sku_id)
order by (id,sku_id) #必选,最优包含三个字段
settings index_granulrity = 8192
# TTL create_time+interval 10 second
;

# 在集群中创建本地表(保存原数据到集群中每个节点中)
CREATE TABLE iov.t_fault12311 on cluster cluster_2s_2r(
    fault_time DateTime DEFAULT '1970-01-01 00:00:00' COMMENT '故障时间',
	fault_type Int8 DEFAULT 0 COMMENT '故障类型,详情请看字典',
	device_type Int8 DEFAULT 0 COMMENT '设备类型 100-DVR 200-OBD',
	vehicle_id Int64 DEFAULT 0 COMMENT '车辆ID',
	device_code String DEFAULT '' COMMENT '设备号',
	create_time DateTime DEFAULT now() COMMENT '创建时间'
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')
PARTITION BY toYYYYMMDD(fault_time)
ORDER BY (vehicle_id,device_code,fault_time,fault_type)
SETTINGS index_granularity = 8192;

# 创建分布式表
CREATE TABLE iov.dt_fault ON CLUSTER cluster_2s_2r as iov.t_fault ENGINE = Distributed(cluster_2s_2r, iov, t_fault, rand()或者javaHash(colName));

rand()或者javaHash(colName)使用此参数可以使数据均匀散列到各个节点中,防止数据集中命中集群中一个节点上。

2 ALTER修改表操作

同 MySQL 的修改字段基本一致

  • 新增字段
# 新增单节点本地表
alter table tableName add column newcolname String after col1;

# 新增集群中所有本地表
alter table tableName ON CLUSTER cluster_2s_2r add column newcolname String after col1;
# 新增集群中分布式表
alter table 分布式表名字 add column newcolname String after col1;


  • 修改字段类型
# 修改单节点本地表
alter table tableName modify column newcolname String;

# 修改集群中所有本地表
alter table tableName ON CLUSTER cluster_2s_2r modify column newcolname String;
# 修改集群中分布式表
alter table 分布式表名字 add column newcolname String after col1;
  • 删除字段
# 删除单节点本地表
alter table tableName drop column newcolname;

# 删除集群中所有本地表
alter table tableName ON CLUSTER cluster_2s_2r drop column newcolname;
# 删除集群中分布式表
alter table 分布式表名字 drop column newcolname;

注意:ClickHouse原则上是不允许对库进行修改删除,可以查询和插入;

但是修改和删除也是支持的,ClickHouse 提供了 Delete 和 Update 的能力,这类操作被称为 Mutation |/mjuː'teɪʃ(ə)n/| (突变)查询,它可以看做 Alter 的一种。

所以说对ClickHouse进行修改和删除数据,就是对表进行操作,所以需要使用到alter关键字。虽然可以实现修改和删除,但是和一般的 OLTP 数据库不一样,Mutation 语句是一种很“重”的操作,而且不支持事务。“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区,重建新分区。所以尽量做批量的变更,不要进行频繁小数据的操作。

  • 删除数据
# 删除单节点本地表的数据
ALTER TABLE db.tableName DELETE WHERE colName = 1;
# 或者使用TRUNCATE删除表数据(全表)
TRUNCATE TABLE db.tableName;

# 删除集群中所有本地表的数据
alter table db.tableName ON CLUSTER cluster_2s_2r DELETE WHERE colName = 1;
# 或者使用TRUNCATE删除表数据(全表)
TRUNCATE TABLE db.tableName ON CLUSTER cluster_2s_2r;
  • 修改数据
# 修改单节点本地表的数据
ALTER TABLE db.tableName  update colName = 1 WHERE colName = 0;

# 修改集群中所有本地表的数据
alter table db.tableName ON CLUSTER cluster_2s_2r update colName = 1 WHERE colName = 0;

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

3 INSERT插入数据

基本与标准 SQL(MySQL)基本一致

  • 标准语法
insert into [table_name] values(...),(....)
  • 从表到表的插入
insert into [table_name] select a,b,c from [table_name_2]

4 查询操作

ClickHouse 基本上与标准 SQL 差别不大

  • ➢ 支持子查询

  • ➢ 支持 CTE(Common Table Expression 公用表表达式 with 子句)

  • ➢ 支持各种JOIN,但是JOIN操作无法使用缓存,所以即使是两次相同的JOIN语句,ClickHouse 也会视为两条新 SQL

    A join B 将B表加载到缓存中,然后再和A表一一匹配

  • ➢ 窗口函数

  • ➢ 不支持自定义函数

  • ➢ GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。

(1)插入数据

alter table t_order_mt delete where 1=1;
 insert into t_order_mt values (101,'sku_001',1000.00,'2020-06-01 12:00:00'), 
(101,'sku_002',2000.00,'2020-06-01 12:00:00'),
 (103,'sku_004',2500.00,'2020-06-01 12:00:00'), 
 (104,'sku_002',2000.00,'2020-06-01 12:00:00'), 
 (105,'sku_003',600.00,'2020-06-02 12:00:00'), 
 (106,'sku_001',1000.00,'2020-06-04 12:00:00'), 
 (107,'sku_002',2000.00,'2020-06-04 12:00:00'), 
 (108,'sku_004',2500.00,'2020-06-04 12:00:00'), 
 (109,'sku_002',2000.00,'2020-06-04 12:00:00'), 
 (110,'sku_003',600.00,'2020-06-01 12:00:00');

(2)with rollup:从右至左去掉维度进行小计

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with rollup;

(3)with cube : 从右至左去掉维度进行小计,再从左至右去掉维度进行小计

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube;

(4)with totals: 只计算合计

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with totals;

5 导出数据

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

介绍

基本上来说传统关系型数据库(以 MySQL 为例)的 SQL 语句,ClickHouse 基本都支持, 这里不会从头讲解 SQL 语法只介绍 ClickHouse 与标准 SQL(MySQL)不一致的地方。

1 CREATE创建表(包含副本,分布式表创建)

# 创建单节点本地表
create table t_order_mt2 (
	id Uint32,
	sku_id String,
	total_amount Decimal(16,2) # TTL create_time+interval 10 second,//过期时间,也可以写在创建表末位,对表进行过期时间,非必选(秒、分、时、天、月、年等都可以)
	create_time Datetime,
	index a total_amount type minmax granularitys
) engine = mergeTree/ReplacingMergeTree(根据入参字段做去重依据,如果没有参数以最后一条数据为准)/SummingMergeTree(依据参数字段进行聚合,可多个)
parition by toYYYYMMDD(create_time)
primary by (id,sku_id)
order by (id,sku_id) #必选,最优包含三个字段
settings index_granulrity = 8192
# TTL create_time+interval 10 second
;

# 在集群中创建本地表(保存原数据到集群中每个节点中)
CREATE TABLE iov.t_fault12311 on cluster cluster_2s_2r(
    fault_time DateTime DEFAULT '1970-01-01 00:00:00' COMMENT '故障时间',
	fault_type Int8 DEFAULT 0 COMMENT '故障类型,详情请看字典',
	device_type Int8 DEFAULT 0 COMMENT '设备类型 100-DVR 200-OBD',
	vehicle_id Int64 DEFAULT 0 COMMENT '车辆ID',
	device_code String DEFAULT '' COMMENT '设备号',
	create_time DateTime DEFAULT now() COMMENT '创建时间'
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')
PARTITION BY toYYYYMMDD(fault_time)
ORDER BY (vehicle_id,device_code,fault_time,fault_type)
SETTINGS index_granularity = 8192;

# 创建分布式表
CREATE TABLE iov.dt_fault ON CLUSTER cluster_2s_2r as iov.t_fault ENGINE = Distributed(cluster_2s_2r, iov, t_fault, rand()或者javaHash(colName));

rand()或者javaHash(colName)使用此参数可以使数据均匀散列到各个节点中,防止数据集中命中集群中一个节点上。

2 ALTER修改表操作

同 MySQL 的修改字段基本一致

  • 新增字段
# 新增单节点本地表
alter table tableName add column newcolname String after col1;

# 新增集群中所有本地表
alter table tableName ON CLUSTER cluster_2s_2r add column newcolname String after col1;
# 新增集群中分布式表
alter table 分布式表名字 add column newcolname String after col1;


  • 修改字段类型
# 修改单节点本地表
alter table tableName modify column newcolname String;

# 修改集群中所有本地表
alter table tableName ON CLUSTER cluster_2s_2r modify column newcolname String;
# 修改集群中分布式表
alter table 分布式表名字 add column newcolname String after col1;
  • 删除字段
# 删除单节点本地表
alter table tableName drop column newcolname;

# 删除集群中所有本地表
alter table tableName ON CLUSTER cluster_2s_2r drop column newcolname;
# 删除集群中分布式表
alter table 分布式表名字 drop column newcolname;

注意:ClickHouse原则上是不允许对库进行修改删除,可以查询和插入;

但是修改和删除也是支持的,ClickHouse 提供了 Delete 和 Update 的能力,这类操作被称为 Mutation |/mjuː'teɪʃ(ə)n/| (突变)查询,它可以看做 Alter 的一种。

所以说对ClickHouse进行修改和删除数据,就是对表进行操作,所以需要使用到alter关键字。虽然可以实现修改和删除,但是和一般的 OLTP 数据库不一样,Mutation 语句是一种很“重”的操作,而且不支持事务。“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区,重建新分区。所以尽量做批量的变更,不要进行频繁小数据的操作。

  • 删除数据
# 删除单节点本地表的数据
ALTER TABLE db.tableName DELETE WHERE colName = 1;
# 或者使用TRUNCATE删除表数据(全表)
TRUNCATE TABLE db.tableName;

# 删除集群中所有本地表的数据
alter table db.tableName ON CLUSTER cluster_2s_2r DELETE WHERE colName = 1;
# 或者使用TRUNCATE删除表数据(全表)
TRUNCATE TABLE db.tableName ON CLUSTER cluster_2s_2r;
  • 修改数据
# 修改单节点本地表的数据
ALTER TABLE db.tableName  update colName = 1 WHERE colName = 0;

# 修改集群中所有本地表的数据
alter table db.tableName ON CLUSTER cluster_2s_2r update colName = 1 WHERE colName = 0;

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

3 INSERT插入数据

基本与标准 SQL(MySQL)基本一致

  • 标准语法
insert into [table_name] values(...),(....)
  • 从表到表的插入
insert into [table_name] select a,b,c from [table_name_2]

4 查询操作

ClickHouse 基本上与标准 SQL 差别不大

  • ➢ 支持子查询

  • ➢ 支持 CTE(Common Table Expression 公用表表达式 with 子句)

  • ➢ 支持各种JOIN,但是JOIN操作无法使用缓存,所以即使是两次相同的JOIN语句,ClickHouse 也会视为两条新 SQL

    A join B 将B表加载到缓存中,然后再和A表一一匹配

  • ➢ 窗口函数

  • ➢ 不支持自定义函数

  • ➢ GROUP BY 操作增加了 with rollupwith cubewith total 用来计算小计和总计。

(1)插入数据

alter table t_order_mt delete where 1=1;
 insert into t_order_mt values (101,'sku_001',1000.00,'2020-06-01 12:00:00'), 
(101,'sku_002',2000.00,'2020-06-01 12:00:00'),
 (103,'sku_004',2500.00,'2020-06-01 12:00:00'), 
 (104,'sku_002',2000.00,'2020-06-01 12:00:00'), 
 (105,'sku_003',600.00,'2020-06-02 12:00:00'), 
 (106,'sku_001',1000.00,'2020-06-04 12:00:00'), 
 (107,'sku_002',2000.00,'2020-06-04 12:00:00'), 
 (108,'sku_004',2500.00,'2020-06-04 12:00:00'), 
 (109,'sku_002',2000.00,'2020-06-04 12:00:00'), 
 (110,'sku_003',600.00,'2020-06-01 12:00:00');

(2)with rollup:从右至左去掉维度进行小计

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with rollup;

(3)with cube : 从右至左去掉维度进行小计,再从左至右去掉维度进行小计

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube;

(4)with totals: 只计算合计

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with totals;

5 导出数据

clickhouse-client --query "select * from t_order_mt where
create_time='2020-06-01 12:00:00'" --format CSVWithNames>
/opt/module/data/rs1.csv
完
  • 本文作者:Java技术债务
  • 原文链接: https://cuizb.top/myblog/article/1639407329
  • 版权声明: 本博客所有文章除特别声明外,均采用 CC BY 3.0 CN协议进行许可。转载请署名作者且注明文章出处。
阅读全文
Java技术债务

Java技术债务

Java技术债务
Java技术债务
热门文章
  1. ClickHouse使用过程中的一些查询优化(六)2003
  2. MySQL数据库被攻击,被删库勒索,逼迫我使出洪荒之力进行恢复数据764
  3. MySQL主从同步原理458
  4. 线程池的理解以及使用414
  5. Spring Cloud Gateway整合nacos实战(三)409
分类
  • Java
    30篇
  • 设计模式
    27篇
  • 数据库
    20篇
  • Spring
    18篇
  • MySQL
    13篇
  • ClickHouse
    11篇
  • Kubernetes
    10篇
  • Redis
    9篇
  • Docker
    8篇
  • SpringBoot
    7篇
  • JVM
    6篇
  • Linux
    5篇
  • Spring Cloud
    5篇
  • 多线程
    5篇
  • Netty
    4篇
  • Kafka
    4篇
  • 面经
    4篇
  • Nginx
    3篇
  • JUC
    3篇
  • 随笔
    2篇
  • 分布式
    1篇
  • MyBatis
    1篇
  • 报错合集
    1篇
  • 生活记录
    1篇
  • 源码
    1篇
  • 性能优化
    1篇

最新评论

  • MySQL数据库被攻击,被删库勒索,逼迫我使出洪荒之力进行恢复数据2022-05-06
    Java技术债务:@capture 一起探讨学习,服务器被黑很正常,及时做好备份以及做好防护
  • MySQL数据库被攻击,被删库勒索,逼迫我使出洪荒之力进行恢复数据2022-04-13
    capture:我的刚上线两天,网站里就两篇文章也被攻击了,纳闷
  • Java常用集合List、Map、Set介绍以及一些面试问题2022-01-18
    Java技术债务:HashSet和TreeSet 相同点:数据不能重复 不同点: 1、底层存储结构不同; HashSet底层使用HashMap哈希表存储 TreeSet底层使用TreeMap树结构存储 2、唯一性方式不同 HashSet底层使用hashcode()和equal()方法判断 TreeSet底层使用Comparable接口的compareTo判断的 3、HashSet无序,TreeSet有序
  • undefined2021-12-14
    Java技术债务:如果不指定线程池,CompletableFuture会默认使用ForkJoin线程池,如果同一时间出现大量请求的话,会出现线程等待问题,建议使用自定义线程池。。。
  • undefined2021-12-02
    you:很好,对于小白相当不错了,谢谢
  • CSDN
  • 博客园
  • 程序猿DD
  • 纯洁的微笑
  • spring4all
  • 廖雪峰的官方网站
  • 猿天地
  • 泥瓦匠BYSocket
  • crossoverJie
  • 张先森个人博客
  • 越加网

© 2021-2022 Java技术债务 - Java技术债务 版权所有
总访问量 0 次 您是本文第 0 位童鞋
豫ICP备2021034516号
Java技术债务 豫公网安备 51011402000164号

微信公众号

Java技术债务
Java技术债务

专注于Spring,SpringBoot等后端技术探索

以及MySql数据库开发和Netty等后端流行框架学习

日志
分类
标签
RSS

有不足之处也希望各位前辈指出