SQL 操作

基本上来说传统关系型数据库(以 MySQL 为例)的 SQL 语句,ClickHouse 基本都支持，但是也有一些不同之处。

Insert

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

(1)标准

insert into [table_name] values(...),(....)

(2)从表到表的插入

insert into [table_name] select a,b,c from [table_name_2]

Update 和 Delete（不建议用）

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

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

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

(1)删除操作

alter table t_order_smt delete where sku_id ='sku_001';

(2)修改操作

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102;

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

优雅操作：

更新操作：更新时直接插入除了一条更新部门之外和之前一样的数据（order by字段一定是一样的），并且使用ReplacingMergeTree引擎，加一个标识version的字段，查询时每次只查询version最大的字段即是最新的；因为该引擎合并时会进行去重操作，因此旧数据会被删除。

删除操作：当删除时将标识删除的字段标识为删除，查询时只查询未被删除的字段。

查询操作

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

* 支持子查询

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

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

* 窗口函数(官方正在测试中…)

* 不支持自定义函数，系统函数详见官网

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

插入数据

/* 删除旧数据 */
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');

/* 查询数据如下 */
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 105 │ sku_003 │       600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 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 │
│ 110 │ sku_003 │       600.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 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 │
└─────┴─────────┴──────────────┴─────────────────────┘

with rollup：从右至左去掉维度进行小计，上卷

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

/* 从右至左依次减少维度 */
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │            600.00 │
│ 109 │ sku_002 │           2000.00 │
│ 107 │ sku_002 │           2000.00 │
│ 106 │ sku_001 │           1000.00 │
│ 104 │ sku_002 │           2000.00 │
│ 101 │ sku_002 │           2000.00 │
│ 103 │ sku_004 │           2500.00 │
│ 108 │ sku_004 │           2500.00 │
│ 105 │ sku_003 │            600.00 │
│ 101 │ sku_001 │           1000.00 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │        │            600.00 │
│ 106 │        │           1000.00 │
│ 105 │        │            600.00 │
│ 109 │        │           2000.00 │
│ 107 │        │           2000.00 │
│ 104 │        │           2000.00 │
│ 103 │        │           2500.00 │
│ 108 │        │           2500.00 │
│ 101 │        │           3000.00 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │          16200.00 │
└────┴────────┴───────────────────┘

with cube : 从右至左去掉维度进行小计,再从左至右去掉维度进行小计，多为分析

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

/* 从右至左依次减少维度 */
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │            600.00 │
│ 109 │ sku_002 │           2000.00 │
│ 107 │ sku_002 │           2000.00 │
│ 106 │ sku_001 │           1000.00 │
│ 104 │ sku_002 │           2000.00 │
│ 101 │ sku_002 │           2000.00 │
│ 103 │ sku_004 │           2500.00 │
│ 108 │ sku_004 │           2500.00 │
│ 105 │ sku_003 │            600.00 │
│ 101 │ sku_001 │           1000.00 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │        │            600.00 │
│ 106 │        │           1000.00 │
│ 105 │        │            600.00 │
│ 109 │        │           2000.00 │
│ 107 │        │           2000.00 │
│ 104 │        │           2000.00 │
│ 103 │        │           2500.00 │
│ 108 │        │           2500.00 │
│ 101 │        │           3000.00 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id──┬─sum(total_amount)─┐
│  0 │ sku_003 │           1200.00 │
│  0 │ sku_004 │           5000.00 │
│  0 │ sku_001 │           2000.00 │
│  0 │ sku_002 │           8000.00 │
└────┴─────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │          16200.00 │
└────┴────────┴───────────────────┘

with totals: 只计算合计，总计

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

/* 从右至左依次减少维度 */
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │            600.00 │
│ 109 │ sku_002 │           2000.00 │
│ 107 │ sku_002 │           2000.00 │
│ 106 │ sku_001 │           1000.00 │
│ 104 │ sku_002 │           2000.00 │
│ 101 │ sku_002 │           2000.00 │
│ 103 │ sku_004 │           2500.00 │
│ 108 │ sku_004 │           2500.00 │
│ 105 │ sku_003 │            600.00 │
│ 101 │ sku_001 │           1000.00 │
└─────┴─────────┴───────────────────┘

Totals:
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │          16200.00 │
└────┴────────┴───────────────────┘

例如维度是a,b

rollup：首先是不group by；然后是group by a；最后是group by a, b

cube：是所有的情况都要有，不group by；group by a；group by b；group by a, b

totals：不group by和group by a, b

alter 操作

同 MySQL 的修改字段基本一致

/* 新增字段 */
alter table tableName add column newcolname String after col1;

/* 修改字段类型 */
alter table tableName modify column newcolname String;

/* 删除字段 */
alter table tableName drop column newcolname;

导出数据

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

–query相当于mysql -e，不进入cli界面执行语句

更多支持格式参照:https://clickhouse.tech/docs/en/interfaces/formats/