ClickHouseSQL
对于标准SQL语法,ClickHouse基本都支持,下面只记录ClickHouse与标准SQL不一致的地方
Insert
基本与标准 SQL(MySQL)基本一致,支持手动逐条的插入和查询结果的插入
Update 和 Delete
虽然ClickHouse是OLAP数据库,但是ClickHouse是支持Updata和Delete操作的,这类操作被称为Mutation查询,这种操作通过Alter完成,语法上与标准SQL有所区别
-- 删除操作
ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr
-- 修改操作
ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr
这里需要注意,ClickHouse虽然支持修改和删除,但是实现机制和传统OLTP数据库不同,在修改和删除时,ClickHouse会将要修改的数据所在的分区全部数据整体迁移,形成一个新的分区,在这个过程中完成修改/删除操作,在迁移完成后,旧分区会打上逻辑失效的标记,直到触发分区合并时,才会删除旧数据释放磁盘空间。因此, Mutation 语句是一种很“重”的操作,而且不支持事务。
所以对于数据的修改和删除尽量做批量的变更,不要进行频繁小数据的操作。
select
ClickHouse语法基本上与标准 SQL一致,只需要注意以下几点
- 支持各种 JOIN, 但是 JOIN 操作无法使用缓存,所以即使是两次相同的 JOIN 语句,ClickHouse 也会视为两条新 SQL
- 支持窗口函数,但是窗口函数的功能处于测试中,当前版本不推荐使用
- 不支持自定义函数
- GROUP BY 操作增加了with rollup\with cube\with totals用来计算小计和总计。
这里简单介绍下with rollup\with cube\with totals语法
假设有表tab中有a,b,c三个字段,下面是以上三种分组语法和它们的等价语法
-- with rollup 是group by 指定字段的前缀
select a,b,sum(c) from tab group by a,b with rollup;
-- 等价语法
select a,b,sum(c) from tab group by a,b
union all
select a,'',sum(c) from tab group by a
union all
select '','',sum(c) from tab;
-- with cube 是group by 指定字段的全部组合形式
select a,b,sum(c) from tab group by a,b with cube;
-- 等价语法
select a,b,sum(c) from tab group by a,b
union all
select a,'',sum(c) from tab group by a
union all
select a,'',sum(c) from tab group by b
union all
select '','',sum(c) from tab;
-- with totals 是group by 指定字段全部生效和全部不生效两种情况的并集
select a,b,sum(c) from tab group by a,b with totals;
-- 等价语法
select a,b,sum(c) from tab group by a,b
union all
select '','',sum(c) from tab;
alter 操作
语法与标准SQL一致,但是ClickHouse是列示存储,因此执行效率会比传统行式数据库效率更高,不过需要注意,当修改字段类型时,最好满足修改前字段的类型可以隐式转换为修改后字段的类型。
导出数据
通过clickhouse-client --query
查询需要导出的结果,指定导出格式(ClickHouse支持各种文件导出格式,具体可以查看官网配置),将结果重定向到目标文件中
clickhouse-client --query "select * from t_order_mt where
create_time='2020-06-01 12:00:00'" --format CSVWithNames >
/opt/module/data/rs1.csv