[ClickHouse] ClickHouse SQL简单操作

ClickHouse支持一种基于SQL的声明式查询语言，它在许多情况下与ANSI SQL标准相同。

支持的查询GROUP BY, ORDER BY, FROM, JOIN, IN以及非相关子查询。

创建数据库

ClickHouse默认使用default数据库，默认使用Atomic数据库引擎

创建数据库SQL语法

1

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]

ON CLUSTER 指定集群名称，指定后会在集群所有节点上创建数据库

执行 show databases 查看节点所有数据库

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


28b9c4cb4ed6 :) show databases;

SHOW DATABASES

Query id: 627e2dc7-8f4a-4ebd-a329-508a6205dc79

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ system             │
└────────────────────┘

4 rows in set. Elapsed: 0.004 sec

创建表

创建表SQL语法

1
2
3
4
5
6


CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

ON CLUSTER: 指定集群，在集群上创建表

创建一个MergeTree引擎的表test, 用创建日期create_time字段按天分区。

1
2
3
4
5
6
7
8
9


create table if not exists test (
   id UInt32 COMMENT 'id',
   shop_id String COMMENT '',
   amount Decimal(16,2) COMMENT '',
   create_time Datetime COMMENT '创建时间'
) engine =MergeTree()
partition by toYYYYMMDD(create_time) 
primary key (id)
order by (id, shop_id);

order by(必选)

`order by` 设定了分区内的数据按照哪些字段顺序进行有序保存。

`order by` 是 `MergeTree` 中唯一一个必填项，甚至比 primary key 还重要，因为当不设置主键的情况，很多处理会依照 order by 的字段进行处理

要求:主键必须是 `order by` 字段的前缀字段。

比如 order by 字段是 (id, shop_id) 那么主键必须是 id 或者(id, shop_id)

primary key 主键(可选)

ClickHouse 中的主键，和其他数据库不太一样，它只提供了数据的一级索引，
但是却不是唯一约束。这就意味着是可以存在相同 primary key 的数据的。

主键的设定主要依据是查询语句中的 where 条件。

根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity,避免了全表扫描。

index granularity: 直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数据的
间隔。ClickHouse 中的 MergeTree 默认是 8192。官方不建议修改这个值，除非该列存在
大量重复值，比如在一个分区中几万行才有一个不同数据。

artition by 分区(可选)

1）作用：分区的目的主要是降低扫描的范围，优化查询速度。如果不指明partition by的话，只会使用一个分区

2）分区目录：MergeTree 是以列文件+索引文件+表定义文件组成的，
但是如果设定了分区那么这些文件就会保存到不同的分区目录中。

3）并行：分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理。

4）数据写入与分区合并：任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。
写入后的某个时刻(大概 10-15 分钟后)，ClickHouse 会自动执行合并操作(等不及也可以
手动。通过 optimize 执行)，把临时分区的数据，合并到已有分区中。

手动合并执行SQL `optimize table test final;`

执行 show tables 查看当前库中所有表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


28b9c4cb4ed6 :) show tables;

SHOW TABLES

Query id: a2e44d1e-6617-426a-9600-0d7df0a768fa

┌─name─┐
│ test │
└──────┘

1 rows in set. Elapsed: 0.005 sec.

使用 describe test 查看表字段属性

分批插入数据

1
2
3
4
5
6
7


insert into test(id, shop_id, amount, create_time) VALUES 
(1, '101', '11.00', toDateTime('2023-01-01 00:00:00')),
(2, '102', '12.00', toDateTime('2023-01-01 00:00:00'));

insert into test(id, shop_id, amount, create_time) VALUES 
(3, '103', '13.00', toDateTime('2023-01-01 00:00:00')),
(4, '104', '14.00', toDateTime('2023-01-01 00:00:00'));

查询数据

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


28b9c4cb4ed6 :) select * from test;

SELECT *
FROM test

Query id: 989e4853-0eea-46fe-ba2f-81816580839e

┌─id─┬─shop_id─┬─amount─┬─────────create_time─┐
│  3 │ 103     │     13 │ 2023-01-01 00:00:00 │
│  4 │ 104     │     14 │ 2023-01-01 00:00:00 │
└────┴─────────┴────────┴─────────────────────┘
┌─id─┬─shop_id─┬─amount─┬─────────create_time─┐
│  1 │ 101     │     11 │ 2023-01-01 00:00:00 │
│  2 │ 102     │     12 │ 2023-01-01 00:00:00 │
└────┴─────────┴────────┴─────────────────────┘

4 rows in set. Elapsed: 0.006 sec.

分批插入的数据会生成新的临时片段,存储底层对应新的分区文件，可以通过system.parts表查看。

执行如下SQL查看test表的分区情况

 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


28b9c4cb4ed6 :)  SELECT
                 database,
                 table,
                 partition,
                 partition_id,
                 name,
                 active,
                 path
                 FROM system.parts
                 WHERE table = 'test'

SELECT
    database,
    table,
    partition,
    partition_id,
    name,
    active,
    path
FROM system.parts
WHERE table = 'test'

Query id: 55ddd330-e30c-4918-8777-bfa9c3b56dc2

┌─database─┬─table─┬─partition─┬─partition_id─┬─name───────────┬─active─┬─path───────────────────────────────────────────────────────────────────────────────┐
│ default  │ test  │ 20230101  │ 20230101     │ 20230101_1_1_0 │      1 │ /var/lib/clickhouse/store/683/6838f6bf-7c41-4b02-a838-f6bf7c417b02/20230101_1_1_0/ │
│ default  │ test  │ 20230101  │ 20230101     │ 20230101_2_2_0 │      1 │ /var/lib/clickhouse/store/683/6838f6bf-7c41-4b02-a838-f6bf7c417b02/20230101_2_2_0/ │
└──────────┴───────┴───────────┴──────────────┴────────────────┴────────┴────────────────────────────────────────────────────────────────────────────────────┘

2 rows in set. Elapsed: 0.006 sec.

MergeTree引擎会在插入数据15分钟左右，将同一个分区的各个分区文件片段合并成一整个分区文件。

也可以手动执行 OPTIMIZE 语句手动触发合并指定的分区片段。

1

OPTIMIZE TABLE test PARTITION '20230101';

合并分区操作

合并分区操作SQL语法

1

OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE [BY expression]]

OPTIMIZE TABLE test PARTITION ‘partition’：合并指定分区(partition)，如果有多个分区，需要执行多次。
OPTIMIZE TABLE test：合并一个分区，如果有多个分区，需要执行多次。
OPTIMIZE TABLE test FINAL：合并所有分区。

[ClickHouse] ClickHouse SQL简单操作

Contents

创建数据库

创建表

合并分区操作

参考