Clickhouse-SQL操作

[toc]

# Insert

# 插入数据

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

# 查询表插入表

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

# Update 和 Delete

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

虽然可以实现修改和删除，但是和一般的 OLTP 数据库不一样，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;

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

# 查询

# Group by

创建表

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);

1
2
3
4
5
6
7
8
9

插入测试数据

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

1
2
3
4
5
6
7
8
9
10
11

# with rollup:从右至左去掉维度进行统计

如：group by a,b,c ；

统计内容包括：group by a,b,c / group by a,b /group by a /group by

查询结果：

:) 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: acb0999c-6886-4c4a-a51e-338116414836

┌──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 │
└────┴────────┴───────────────────┘

20 rows in set. Elapsed: 0.007 sec.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

# with cube:多维分析

从右至左去掉维度进行小计，再从左至右去掉维度进行小计

如：group by a,b,c ；

统计内容包括：group by a,b,c / group by a,b /group by a /group by b/group by c/group by a,c / group by a,b /group by b,c/group by

查询结果：

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

1
2

:)  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: fd93453a-db65-44b8-83a5-be989d938743

┌──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 │
└────┴────────┴───────────────────┘

24 rows in set. Elapsed: 0.014 sec.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

# with total：额外计算合计

除了 group by 字段外，额外统计一下总额

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

1
2

统计结果

┌──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 │
└────┴────────┴───────────────────┘

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# SAMPLE 采样

SAMPLE子句能够实现数据采样的功能，使查询仅返回采样数据而不是全部数据，从而有效减少查询负载。SAMPLE子句的采样机制是一种幂等设计，也就是说在数据不发生变化的情况下，使用相同的采样规则总是能够返回相同的数据，所以这项特性非常适合在那些可以接受近似查询结果的场合使用。例如在数据量十分巨大的情况下，对查询时效性的要求大于准确性时就可以尝试使用SAMPLE子句。

SAMPLE子句只能用于MergeTree系列引擎的数据表，并且要求在CREATE TABLE时声明SAMPLE BY抽样表达式，例如下面的语句：

CREATE TABLE hits_v1 ( CounterID UInt64, EventDate DATE, UserID UInt64 ) 
ENGINE = MergeTree() 
PARTITION BY toYYYYMM(EventDate) 
ORDER BY (CounterID, intHash32(UserID)) 
SAMPLE BY intHash32(UserID) --Sample Key声明的表达式必须也包含在主键的声明中

1
2
3
4
5

SAMPLE BY表示hits_v1内的数据，可以按照intHash32(UserID)分布后的结果采样查询。

在声明Sample Key的时候有两点需要注意：

SAMPLE BY所声明的表达式必须同时包含在主键的声明内；
Sample Key必须是Int类型，如若不是，ClickHouse在进行CREATE TABLE操作时也不会报错，但在数据查询时会得到如下类似异常：

Invalid sampling column type in storage parameters: Float32. Must be unsigned integer type.

# SAMPLE子句目前支持如下3种用法

# SAMPLE factor

SAMPLE factor表示按因子系数采样，其中factor表示采样因子，它的取值支持0～1之间的小数。如果factor设置为0或者1，则效果等同于不进行数据采样。如下面的语句表示按10%的因子采样数据：

SELECT CounterID FROM hits_v1 SAMPLE 0.1

factor也支持使用十进制的形式表述：

SELECT CounterID FROM hits_v1 SAMPLE 1/10

在进行统计查询时，为了得到最终的近似结果，需要将得到的直接结果乘以采样系数。例如若想按0.1的因子采样数据，则需要将统计结果放大10倍：

SELECT count() * 10 FROM hits_v1 SAMPLE 0.1

# SAMPLE rows

SAMPLE rows表示按样本数量采样，其中rows表示至少采样多少行数据，它的取值必须是大于1的整数。如果rows的取值大于表内数据的总行数，则效果等于rows=1（即不使用采样）。

下面的语句表示采样10000行数据：

SELECT count() FROM hits_v1 SAMPLE 10000

最终查询返回了9576行数据，从返回的结果中可以得知，数据采样的范围是一个近似范围，这是由于采样数据的最小粒度是由index_granularity索引粒度决定的。由此可知，设置一个小于索引粒度或者较小的rows值没有什么意义，应该设置一个较大的值。

可以使用虚拟字段_sample_factor来获取当前查询所对应的采样系数：

SELECT CounterID,_sample_factor FROM hits_v1 SAMPLE 100000 LIMIT 1

# SAMPLE factor OFFSET n

SAMPLE factor OFFSET n表示按因子系数和偏移量采样，其中factor表示采样因子，n表示偏移多少数据后才开始采样，它们两个的取值都是0～1之间的小数。例如下面的语句表示偏移量为0.5并按0.4的系数采样：

SELECT CounterID FROM hits_v1 SAMPLE 0.4 OFFSET 0.5

如果在计算OFFSET偏移量后，按照SAMPLE比例采样出现了溢出，则数据会被自动截断。

# alter操作

# 新增字段

alter table tableName add column newcolname String after col1;

# 修改字段类型

alter table tableName modify column newcolname String;

# 删除字段

alter table tableName drop column newcolname;

# 导出数据

# 导出CSV

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

上次更新: 2023/03/10, 17:30:33

← Clickhouse基础知识 Clickhouse-副本→