ClickHouse学习笔记之SQL语句

简介

ClickHouse支持传统的MySQL中的结构化查询语言SQL，因此此处不会从头学SQL，而是记录ClickHouse中对SQL扩展的新特性。

Insert

基本和MySQL一致：

insert into table_name values(...), (...);
insert into table_name select a, b, c from table2_name;

Update和Delete

ClickHouse将Update和Delete合称为Mutation查询，是Alter的一种。ClickHouse中的Mutation会导致放弃目标数据原有的分区，并重建分区，因此尽量做批量的变更，不要频繁进行小数据的更删，而且Mutation查询不支持事务。
Mutation语句分两步执行，同步执行的部分是第一步：新增数据或分区，并把旧分区打上失效标记；第二步是合并分区时，删除被标记的旧数据释放磁盘空间。

删除

alter table t_order_smt delete where sku_id = 'sku_001';

修改

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

查询

跟MySQL差别不大：支持子查询、支持CTE(Common Table Expression，公用表语句)、支持Join、窗口函数(官方正在测试)、不支持自定义函数、group by增加了with rollup/with cube/with total以支持计算小计和总计。
ClickHouse中的Join不会使用缓存，因此即使是两条相同的Join语句，ClickHouse也会视为两条新的SQL。

下面对支持小计和总计的语句进行测试

插入数据

scentos :) alter table t_order_mt delete where 1=1;

ALTER TABLE t_order_mt
    DELETE WHERE 1 = 1

Query id: ee8fdaf7-13b1-4591-92ca-d5b8c39a9072

Ok.

0 rows in set. Elapsed: 0.003 sec.

scentos :) 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');

INSERT INTO t_order_mt FORMAT Values

Query id: 544f51e2-1437-495e-8dbd-46ce298d2c43

Ok.

10 rows in set. Elapsed: 0.004 sec.

scentos :)

with rollup：

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

SELECT
    id,
    sku_id,
    sum(total_amount)
FROM t_order_mt
GROUP BY
    id,
    sku_id
    WITH ROLLUP

Query id: 8b99ff06-e5b5-4e8b-bbab-1bc4bc6d18f3

┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │               600 │
│ 109 │ sku_002 │              2000 │
│ 107 │ sku_002 │              2000 │
│ 106 │ sku_001 │              1000 │
│ 104 │ sku_002 │              2000 │
│ 101 │ sku_002 │              2000 │
│ 103 │ sku_004 │              2500 │
│ 108 │ sku_004 │              2500 │
│ 105 │ sku_003 │               600 │
│ 101 │ sku_001 │              1000 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │        │               600 │
│ 106 │        │              1000 │
│ 105 │        │               600 │
│ 109 │        │              2000 │
│ 107 │        │              2000 │
│ 104 │        │              2000 │
│ 103 │        │              2500 │
│ 108 │        │              2500 │
│ 101 │        │              3000 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │             16200 │
└────┴────────┴───────────────────┘
20 rows in set. Elapsed: 0.003 sec.

计算group by子句中从左往右的每个组合的小计，此处group by id, skull_id，那么会计算每个(id, skull_id)的小计、每个id的小计和合计

with cube

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

SELECT
    id,
    sku_id,
    sum(total_amount)
FROM t_order_mt
GROUP BY
    id,
    sku_id
    WITH CUBE

Query id: 6c515fc3-0002-4d32-a99f-e569e0749a31

┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │               600 │
│ 109 │ sku_002 │              2000 │
│ 107 │ sku_002 │              2000 │
│ 106 │ sku_001 │              1000 │
│ 104 │ sku_002 │              2000 │
│ 101 │ sku_002 │              2000 │
│ 103 │ sku_004 │              2500 │
│ 108 │ sku_004 │              2500 │
│ 105 │ sku_003 │               600 │
│ 101 │ sku_001 │              1000 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │        │               600 │
│ 106 │        │              1000 │
│ 105 │        │               600 │
│ 109 │        │              2000 │
│ 107 │        │              2000 │
│ 104 │        │              2000 │
│ 103 │        │              2500 │
│ 108 │        │              2500 │
│ 101 │        │              3000 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id──┬─sum(total_amount)─┐
│  0 │ sku_003 │              1200 │
│  0 │ sku_004 │              5000 │
│  0 │ sku_001 │              2000 │
│  0 │ sku_002 │              8000 │
└────┴─────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │             16200 │
└────┴────────┴───────────────────┘

24 rows in set. Elapsed: 0.004 sec.

计算group by子句中所有组合的小计，此处group by id, skull_id，那么会计算每个(id, skull_id)的小计、每个id的小计、每个skull_id的小计和合计。

with totals

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

SELECT
    id,
    sku_id,
    sum(total_amount)
FROM t_order_mt
GROUP BY
    id,
    sku_id
    WITH TOTALS

Query id: 74a55dba-83d1-4365-9cf4-961cf5685a2e

┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │               600 │
│ 109 │ sku_002 │              2000 │
│ 107 │ sku_002 │              2000 │
│ 106 │ sku_001 │              1000 │
│ 104 │ sku_002 │              2000 │
│ 101 │ sku_002 │              2000 │
│ 103 │ sku_004 │              2500 │
│ 108 │ sku_004 │              2500 │
│ 105 │ sku_003 │               600 │
│ 101 │ sku_001 │              1000 │
└─────┴─────────┴───────────────────┘

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

10 rows in set. Elapsed: 0.004 sec.

计算group by子句中的小计和合计，此处group by id, skull_id，那么会计算每个(id, skull_id)的小计和合计。

alter操作

基本和MySQL一致。

新增字段：

alter table table_name add column col_name String after old_col1;

修改字段类型：

alter table table_name modify column col_name String;

修改字段名：

rename column old_name to new_name;

删除字段：

alter table table_name drop column col_name;

导出数据

[szc@scentos ~]$ clickhouse-client --query "select * from t_order_mt" --format CSVWithNames > ./clickhouse_result1.csv
[szc@scentos ~]$ cat ./clickhouse_result1.csv
"id","sku_id","total_amount","create_time"
106,"sku_001",1000,"2020-06-04 12:00:00"
107,"sku_002",2000,"2020-06-04 12:00:00"
108,"sku_004",2500,"2020-06-04 12:00:00"
109,"sku_002",2000,"2020-06-04 12:00:00"
101,"sku_001",1000,"2020-06-01 12:00:00"
101,"sku_002",2000,"2020-06-01 12:00:00"
103,"sku_004",2500,"2020-06-01 12:00:00"
104,"sku_002",2000,"2020-06-01 12:00:00"
110,"sku_003",600,"2020-06-01 12:00:00"
105,"sku_003",600,"2020-06-02 12:00:00"

更多格式参考官网。