Clickhouse建表语法、视图语法、数据表DDL（数据定义语言）、数据DML（数据操作语言）...

113 阅读 0 评论 75 点赞

我是靠谱客的博主碧蓝太阳，这篇文章主要介绍Clickhouse建表语法、视图语法、数据表DDL（数据定义语言）、数据DML（数据操作语言）...，现在分享给大家，希望可以做个参考。

参考官网：https://clickhouse.tech/docs/zh/sql-reference/statements/create/，更多详细文档可以参考官网，强烈推荐。

1、Clickhouse创建数据库，CREATE DATABASE，该查询用于根据指定名称创建数据库。

复制代码

1
1 CREATE DATABASE [IF NOT EXISTS] db_name

数据库其实只是用于存放表的一个目录。如果查询中存在IF NOT EXISTS，则当数据库已经存在时，该查询不会返回任何错误。

2、Clickhouse数据表的定义语法，是在标准SQL的基础之上建立的。Clickhouse目前提供了三种最基本的建表方法，但是注意的是在Clickhouse中建表一定要指定表的引擎，在指定数据表字段之后，最后一定要指定数据表的引擎。CREATE TABLE的三种方式，对于CREATE TABLE，存在以下几种方式。

2.1、第一种方式，直接指定字段名称、字段类型，是否有默认值，中文备注名称等信息。

复制代码

1
2
3
4
5
6
7
8
9
1 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
2 (
3
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
4
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
5
...
6 ) ENGINE = engine

使用案例，如下所示：

复制代码

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
 1 master :)
2 master :) CREATE TABLE tb_table1
3 :-] (
4 :-]
`id` UInt8,
5 :-] `name` String
6 :-] )ENGINE = TinyLog;
7
8 CREATE TABLE tb_table1
9 (
10
`id` UInt8,
11
`name` String
12 )
13 ENGINE = TinyLog
14
15 Ok.
16
17 0 rows in set. Elapsed: 0.038 sec.
18
19 master :) insert into tb_table1 values(1, '张三三');
20
21 INSERT INTO tb_table1 VALUES
22
23 Ok.
24
25 1 rows in set. Elapsed: 0.036 sec.
26
27 master :) select * from tb_table1;
28
29 SELECT *
30 FROM tb_table1
31
32 ┌─id─┬─name───┐
33 │
1 │ 张三三 │
34 └────┴────────┘
35
36 1 rows in set. Elapsed: 0.014 sec.
37
38 master :)

注意，如果创建数据表没有指定数据库，将在default默认的数据库下创建一张数据表。注意末尾的engine参数，它被用于指定数据表的引擎，表引擎决定了数据表的特性，也决定了数据将会被如何存储和加载。

2.2、第二种方式，这种方式其实就是复制已经存在的一张表结构，可用于数据的备份，可用于多个数据库之间复制表机构。

　　创建一个与db2.name2具有相同结构的表，同时你可以对其指定不同的表引擎声明。如果没有表引擎声明，则创建的表将与db2.name2使用相同的表引擎。

复制代码

1
1 CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]

使用案例，如下所示：

复制代码

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
50
51
 1 master :) create database gab_db2;
2
3 CREATE DATABASE gab_db2
4
5 Ok.
6
7 0 rows in set. Elapsed: 0.009 sec.
8
9 master :) use gab_db2;
10
11 USE gab_db2
12
13 Ok.
14
15 0 rows in set. Elapsed: 0.051 sec.
16
17 master :) show tables;
18
19 SHOW TABLES
20
21 Ok.
22
23 0 rows in set. Elapsed: 0.010 sec.
24
25 master :) show tables;
26
27 SHOW TABLES
28
29 Ok.
30
31 0 rows in set. Elapsed: 0.011 sec.
32
33 master :) create table if not exists gab_db2.tb_name as gab_db.tb_name;
34
35 CREATE TABLE IF NOT EXISTS gab_db2.tb_name AS gab_db.tb_name
36
37 Ok.
38
39 0 rows in set. Elapsed: 0.014 sec.
40
41 master :) show tables;
42
43 SHOW TABLES
44
45 ┌─name────┐
46 │ tb_name │
47 └─────────┘
48
49 1 rows in set. Elapsed: 0.010 sec.
50
51 master :)

2.3、第三种方式，通过select查询的方式来创建表，同时也会导入查询的结果数据。

复制代码

1
1 CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ...

使用案例，如下所示：

复制代码

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
 1 master :) create table tb_name2 engine=TinyLog as select * from gab_db.tb_name;
2
3 CREATE TABLE tb_name2
4 ENGINE = TinyLog AS
5 SELECT *
6 FROM gab_db.tb_name
7
8 Ok.
9
10 0 rows in set. Elapsed: 0.021 sec.
11 master :) show tables;
12
13 SHOW TABLES
14
15 ┌─name─────┐
16 │ tb_name
│
17 │ tb_name2 │
18 └──────────┘
19
20 2 rows in set. Elapsed: 0.010 sec.
21
22 master :) select * from tb_name2;
23
24 SELECT *
25 FROM tb_name2
26
27 ┌─id─┬─age─┬───birthday─┬──────────updateTime─┐
28 │
1 │
22 │ 1994-05-16 │ 2021-02-20 14:21:30 │
29 │
2 │
24 │ 1994-05-17 │ 2021-02-20 14:21:30 │
30 └────┴─────┴────────────┴─────────────────────┘
31
32 2 rows in set. Elapsed: 0.010 sec.
33
34 master :)

3、Clickhouse删除表的语法结构。也可以通过此语法删除普通视图和物化视图。

复制代码

1
1 DROP TABLE [IF EXISTS] [db_name.]table_name;

使用案例，如下所示：

复制代码

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
 1 master :)
2 master :) show tables;
3
4 SHOW TABLES
5
6 ┌─name─────┐
7 │ tb_name
│
8 │ tb_name2 │
9 └──────────┘
10
11 2 rows in set. Elapsed: 0.008 sec.
12
13 master :) drop table tb_name2;
14
15 DROP TABLE tb_name2
16
17 Ok.
18
19 0 rows in set. Elapsed: 0.005 sec.
20
21 master :) show tables;
22
23 SHOW TABLES
24
25 ┌─name────┐
26 │ tb_name │
27 └─────────┘
28
29 1 rows in set. Elapsed: 0.006 sec.
30
31 master :)

4、临时表，Clickhouse也有临时表的概念，创建临时表的方法是在普通表的基础上添加temporary关键字，相比普通表而言，临时表有如下两点特殊之处。

　　1)、它的生命周期是会话绑定的，所以它只支持Memory表引擎，如果会话结束，数据表就会被销毁。　　2)、临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有数据表引擎参数。

临时表的优先级大于系统中的表，一般用于集群之间的数据传播的载体。临时表不属于任何数据库。会话断开以后表会自动删除，不会持久化。如果本地表和临时表冲突，临时表优先。可以用于数据库之间的数据迁移。

5、ClickHouse支持临时表，其具有以下特征：

　　1)、当回话结束时，临时表将随会话一起消失，这包含链接中断。　　2)、临时表仅能够使用Memory表引擎。　　3)、无法为临时表指定数据库。它是在数据库之外创建的。　　4)、如果临时表与另一个表名称相同，那么当在查询时没有显示的指定db的情况下，将优先使用临时表。　　5)、对于分布式处理，查询中使用的临时表将被传递到远程服务器。

可以使用下面的语法创建一个临时表：

复制代码

1
2
3
4
5
6
7
8
9
1 CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name [ON CLUSTER cluster]
2 (
3
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
4
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
5
...
6 )

大多数情况下，临时表不是手动创建的，只有在分布式查询处理中使用(GLOBAL) IN时为外部数据创建。

复制代码

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
 1 master :)
2 master :) CREATE temporary TABLE tb_table1
3 :-] (
4 :-]
`id` UInt8,
5 :-] `name` String
6 :-] );
7
8 CREATE TEMPORARY TABLE tb_table1
9 (
10
`id` UInt8,
11
`name` String
12 )
13
14 Ok.
15
16 0 rows in set. Elapsed: 0.003 sec.
17
18 master :) insert into tb_table1 values(1, '张三三');
19
20 INSERT INTO tb_table1 VALUES
21
22 Ok.
23
24 1 rows in set. Elapsed: 0.004 sec.
25
26 master :) select * from tb_table1;
27
28 SELECT *
29 FROM tb_table1
30
31 ┌─id─┬─name───┐
32 │
1 │ 张三三 │
33 └────┴────────┘
34
35 1 rows in set. Elapsed: 0.007 sec.
36
37 master :)

临时表不需要指定数据表的引擎，我们可以理解成临时表会将当前数据库中已经存在的同名表覆盖隐藏，当出现操作的时候，如果有临时表，那么会操作临时表。

6、Clickhouse拥有普通视图和物化两种视图。其中物化视图拥有独立的存储，而普通视图只是一层简单的查询代理。

6.1、普通视图不会存储任何数据，它只是一层单纯的select查询映射，起着简化查询，明晰语义的作用，对查询性能不会有任何增强。

复制代码

1
1 CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

　　创建一个视图。它存在两种可选择的类型：普通视图与物化视图。普通视图不存储任何数据，只是执行从另一个表中的读取。换句话说，普通视图只是保存了视图的查询，当从视图中查询时，此查询被作为子查询用于替换FROM子句。

使用案例，如下所示：

复制代码

1 master :) show tables;
2
3 SHOW TABLES
4
5 ┌─name───────┐
6 │ tb_array
│
7 │ tb_enum
│
8 │ tb_name
│
9 │ tb_tinyLog │
10 │ tb_tuple
│
11 │ user_db
│
12 └────────────┘
13
14 6 rows in set. Elapsed: 0.017 sec.
15
16 master :) select * from tb_name;
17
18 SELECT *
19 FROM tb_name
20
21 ┌─id─┬─age─┬───birthday─┬──────────updateTime─┐
22 │
1 │
22 │ 1994-05-16 │ 2021-02-20 14:21:30 │
23 │
2 │
24 │ 1994-05-17 │ 2021-02-20 14:21:30 │
24 └────┴─────┴────────────┴─────────────────────┘
25
26 2 rows in set. Elapsed: 0.006 sec.
27
28 master :) CREATE VIEW view_name AS SELECT * from tb_name;
29
30 CREATE VIEW view_name AS
31 SELECT *
32 FROM tb_name
33
34 Ok.
35
36 0 rows in set. Elapsed: 0.009 sec.
37
38 master :) select * from view_name;
39
40 SELECT *
41 FROM view_name
42
43 ┌─id─┬─age─┬───birthday─┬──────────updateTime─┐
44 │
1 │
22 │ 1994-05-16 │ 2021-02-20 14:21:30 │
45 │
2 │
24 │ 1994-05-17 │ 2021-02-20 14:21:30 │
46 └────┴─────┴────────────┴─────────────────────┘
47
48 2 rows in set. Elapsed: 0.011 sec.
49
50 master :)

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 1 master :) show tables;
2
3 SHOW TABLES
4
5 ┌─name───────┐
6 │ tb_array
│
7 │ tb_enum
│
8 │ tb_name
│
9 │ tb_tinyLog │
10 │ tb_tuple
│
11 │ user_db
│
12 └────────────┘
13
14 6 rows in set. Elapsed: 0.017 sec.
15
16 master :) select * from tb_name;
17
18 SELECT *
19 FROM tb_name
20
21 ┌─id─┬─age─┬───birthday─┬──────────updateTime─┐
22 │
1 │
22 │ 1994-05-16 │ 2021-02-20 14:21:30 │
23 │
2 │
24 │ 1994-05-17 │ 2021-02-20 14:21:30 │
24 └────┴─────┴────────────┴─────────────────────┘
25
26 2 rows in set. Elapsed: 0.006 sec.
27
28 master :) CREATE VIEW view_name AS SELECT * from tb_name;
29
30 CREATE VIEW view_name AS
31 SELECT *
32 FROM tb_name
33
34 Ok.
35
36 0 rows in set. Elapsed: 0.009 sec.
37
38 master :) select * from view_name;
39
40 SELECT *
41 FROM view_name
42
43 ┌─id─┬─age─┬───birthday─┬──────────updateTime─┐
44 │
1 │
22 │ 1994-05-16 │ 2021-02-20 14:21:30 │
45 │
2 │
24 │ 1994-05-17 │ 2021-02-20 14:21:30 │
46 └────┴─────┴────────────┴─────────────────────┘
47
48 2 rows in set. Elapsed: 0.011 sec.
49
50 master :)

6.2、物化视图存储的数据是由相应的SELECT查询转换得来的。物化视图是特殊的表，有数据表结构，有数据表引擎，可以将数据持久化。

　　在创建物化视图时，你还必须指定表的引擎，将会使用这个表引擎存储数据。目前物化视图的工作原理：当将数据写入到物化视图中SELECT子句所指定的表时，插入的数据会通过SELECT子句查询进行转换并将最终结果插入到视图中。

复制代码

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
50
51
 1 master :)
2 master :) show tables;
3
4 SHOW TABLES
5
6 ┌─name───────┐
7 │ tb_array
│
8 │ tb_enum
│
9 │ tb_name
│
10 │ tb_tinyLog │
11 │ tb_tuple
│
12 │ user_db
│
13 │ view_name
│
14 └────────────┘
15
16 7 rows in set. Elapsed: 0.007 sec.
17
18 master :) create materialized view m_name_view engine=Log populate as select * from tb_name;
19
20 CREATE MATERIALIZED VIEW m_name_view
21 ENGINE = Log POPULATE AS
22 SELECT *
23 FROM tb_name
24
25 Ok.
26
27 0 rows in set. Elapsed: 0.011 sec.
28
29 master :) select * from m_name_view;
30
31 SELECT *
32 FROM m_name_view
33
34 ┌─id─┬─age─┬───birthday─┬──────────updateTime─┐
35 │
1 │
22 │ 1994-05-16 │ 2021-02-20 14:21:30 │
36 │
2 │
24 │ 1994-05-17 │ 2021-02-20 14:21:30 │
37 └────┴─────┴────────────┴─────────────────────┘
38
39 2 rows in set. Elapsed: 0.007 sec.
40
41 master :)

　　如果创建物化视图时指定了POPULATE子句，则在创建时将该表的数据插入到物化视图中。就像使用CREATE TABLE ... AS SELECT ...一样。否则，物化视图只会包含在物化视图创建后的新写入的数据。我们不推荐使用POPULATE，因为在视图创建期间写入的数据将不会写入其中。

7、Clickhouse的分区表，数据分区(partition)和数据分片(shard)是完全不同的两个概念。数据分区是针对本地数据而言的，是数据的一种纵向切分，而数据分片是数据的一种横向切分。

　　数据分区对于一款OLAP数据库而言意义非凡，借助数据分区，在后续的查询过程中能够跳过不必要的数据目录，从而提升查询的性能。合理利用分区特性，还可以变相实现数据的更新操作，因为数据分区支持删除、替换和重置操作。假设数据表按照月份分区，那么数据就可以按照月份的粒度被替换更新。分区虽好，但不是所有的表引擎都可以使用这项特性，目前只有合并树MergeTree家族系统的表引擎才支持数据分区。

复制代码

1 master :)
2 master :) CREATE TABLE tb_partition
3 :-] (
4 :-]
`pid` UInt8,
5 :-] `name` String,
6 :-] `ctime` DateTime,
7 :-] `money` Float64
8 :-] )engine=MergeTree()
9 :-] partition by toYYYYMM(ctime)
10 :-] ORDER BY pid;
11
12 CREATE TABLE tb_partition
13 (
14
`pid` UInt8,
15
`name` String,
16
`ctime` DateTime,
17
`money` Float64
18 )
19 ENGINE = MergeTree()
20 PARTITION BY toYYYYMM(ctime)
21 ORDER BY pid
22
23 Ok.
24
25 0 rows in set. Elapsed: 0.010 sec.
26
27 master :) insert into tb_partition values(1, '张三三', '2021-02-22 12:20:20', '120000');
28
29 INSERT INTO tb_partition VALUES
30
31 Ok.
32
33 1 rows in set. Elapsed: 0.008 sec.
34
35 master :) select * from tb_partition;
36
37 SELECT *
38 FROM tb_partition
39
40 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
41 │
1 │ 张三三 │ 2021-02-22 12:20:20 │ 120000 │
42 └─────┴────────┴─────────────────────┴────────┘
43
44 1 rows in set. Elapsed: 0.017 sec.
45
46 master :) desc tb_partition;
47
48 DESCRIBE TABLE tb_partition
49
50 ┌─name──┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
51 │ pid
│ UInt8
│
│
│
│
│
│
52 │ name
│ String
│
│
│
│
│
│
53 │ ctime │ DateTime │
│
│
│
│
│
54 │ money │ Float64
│
│
│
│
│
│
55 └───────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
56
57 4 rows in set. Elapsed: 0.012 sec.
58
59 master :) insert into tb_partition values(1, '张三三', '2021-01-22 12:20:20', '120000');
60
61 INSERT INTO tb_partition VALUES
62
63 Ok.
64
65 1 rows in set. Elapsed: 0.008 sec.
66
67 master :) select * from tb_partition;
68
69 SELECT *
70 FROM tb_partition
71
72 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
73 │
1 │ 张三三 │ 2021-02-22 12:20:20 │ 120000 │
74 └─────┴────────┴─────────────────────┴────────┘
75 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
76 │
1 │ 张三三 │ 2021-01-22 12:20:20 │ 120000 │
77 └─────┴────────┴─────────────────────┴────────┘
78
79 2 rows in set. Elapsed: 0.012 sec.
80
81 master :)

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
 1 master :)
2 master :) CREATE TABLE tb_partition
3 :-] (
4 :-]
`pid` UInt8,
5 :-] `name` String,
6 :-] `ctime` DateTime,
7 :-] `money` Float64
8 :-] )engine=MergeTree()
9 :-] partition by toYYYYMM(ctime)
10 :-] ORDER BY pid;
11
12 CREATE TABLE tb_partition
13 (
14
`pid` UInt8,
15
`name` String,
16
`ctime` DateTime,
17
`money` Float64
18 )
19 ENGINE = MergeTree()
20 PARTITION BY toYYYYMM(ctime)
21 ORDER BY pid
22
23 Ok.
24
25 0 rows in set. Elapsed: 0.010 sec.
26
27 master :) insert into tb_partition values(1, '张三三', '2021-02-22 12:20:20', '120000');
28
29 INSERT INTO tb_partition VALUES
30
31 Ok.
32
33 1 rows in set. Elapsed: 0.008 sec.
34
35 master :) select * from tb_partition;
36
37 SELECT *
38 FROM tb_partition
39
40 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
41 │
1 │ 张三三 │ 2021-02-22 12:20:20 │ 120000 │
42 └─────┴────────┴─────────────────────┴────────┘
43
44 1 rows in set. Elapsed: 0.017 sec.
45
46 master :) desc tb_partition;
47
48 DESCRIBE TABLE tb_partition
49
50 ┌─name──┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
51 │ pid
│ UInt8
│
│
│
│
│
│
52 │ name
│ String
│
│
│
│
│
│
53 │ ctime │ DateTime │
│
│
│
│
│
54 │ money │ Float64
│
│
│
│
│
│
55 └───────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
56
57 4 rows in set. Elapsed: 0.012 sec.
58
59 master :) insert into tb_partition values(1, '张三三', '2021-01-22 12:20:20', '120000');
60
61 INSERT INTO tb_partition VALUES
62
63 Ok.
64
65 1 rows in set. Elapsed: 0.008 sec.
66
67 master :) select * from tb_partition;
68
69 SELECT *
70 FROM tb_partition
71
72 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
73 │
1 │ 张三三 │ 2021-02-22 12:20:20 │ 120000 │
74 └─────┴────────┴─────────────────────┴────────┘
75 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
76 │
1 │ 张三三 │ 2021-01-22 12:20:20 │ 120000 │
77 └─────┴────────┴─────────────────────┴────────┘
78
79 2 rows in set. Elapsed: 0.012 sec.
80
81 master :)

可以使用分区合并命令：optimize table 数据表名称，将相同分区的数据进行分区合并，如下所示：

复制代码

1 master :) select * from tb_partition;
2
3 SELECT *
4 FROM tb_partition
5
6 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
7 │
1 │ 张三三 │ 2021-02-22 12:20:20 │ 120000 │
8 └─────┴────────┴─────────────────────┴────────┘
9 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
10 │
1 │ 张三三 │ 2021-01-22 12:20:20 │ 120000 │
11 └─────┴────────┴─────────────────────┴────────┘
12 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
13 │
1 │ 张三三 │ 2021-02-24 12:20:20 │ 120000 │
14 └─────┴────────┴─────────────────────┴────────┘
15 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
16 │
1 │ 张三三 │ 2021-01-23 12:20:20 │ 120000 │
17 └─────┴────────┴─────────────────────┴────────┘
18 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
19 │
1 │ 张三三 │ 2021-01-24 12:20:20 │ 120000 │
20 └─────┴────────┴─────────────────────┴────────┘
21 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
22 │
1 │ 张三三 │ 2021-03-24 12:20:20 │ 120000 │
23 └─────┴────────┴─────────────────────┴────────┘
24
25 6 rows in set. Elapsed: 0.017 sec.
26
27 master :)
28 master :)
29 master :) optimize table tb_partition;
30
31 OPTIMIZE TABLE tb_partition
32
33 Ok.
34
35 0 rows in set. Elapsed: 0.006 sec.
36
37 master :) select * from tb_partition;
38
39 SELECT *
40 FROM tb_partition
41
42 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
43 │
1 │ 张三三 │ 2021-02-24 12:20:20 │ 120000 │
44 └─────┴────────┴─────────────────────┴────────┘
45 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
46 │
1 │ 张三三 │ 2021-03-24 12:20:20 │ 120000 │
47 └─────┴────────┴─────────────────────┴────────┘
48 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
49 │
1 │ 张三三 │ 2021-01-22 12:20:20 │ 120000 │
50 │
1 │ 张三三 │ 2021-01-23 12:20:20 │ 120000 │
51 │
1 │ 张三三 │ 2021-01-24 12:20:20 │ 120000 │
52 └─────┴────────┴─────────────────────┴────────┘
53 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
54 │
1 │ 张三三 │ 2021-02-22 12:20:20 │ 120000 │
55 └─────┴────────┴─────────────────────┴────────┘
56
57 6 rows in set. Elapsed: 0.016 sec.
58
59 master :)

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
 1 master :) select * from tb_partition;
2
3 SELECT *
4 FROM tb_partition
5
6 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
7 │
1 │ 张三三 │ 2021-02-22 12:20:20 │ 120000 │
8 └─────┴────────┴─────────────────────┴────────┘
9 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
10 │
1 │ 张三三 │ 2021-01-22 12:20:20 │ 120000 │
11 └─────┴────────┴─────────────────────┴────────┘
12 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
13 │
1 │ 张三三 │ 2021-02-24 12:20:20 │ 120000 │
14 └─────┴────────┴─────────────────────┴────────┘
15 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
16 │
1 │ 张三三 │ 2021-01-23 12:20:20 │ 120000 │
17 └─────┴────────┴─────────────────────┴────────┘
18 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
19 │
1 │ 张三三 │ 2021-01-24 12:20:20 │ 120000 │
20 └─────┴────────┴─────────────────────┴────────┘
21 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
22 │
1 │ 张三三 │ 2021-03-24 12:20:20 │ 120000 │
23 └─────┴────────┴─────────────────────┴────────┘
24
25 6 rows in set. Elapsed: 0.017 sec.
26
27 master :)
28 master :)
29 master :) optimize table tb_partition;
30
31 OPTIMIZE TABLE tb_partition
32
33 Ok.
34
35 0 rows in set. Elapsed: 0.006 sec.
36
37 master :) select * from tb_partition;
38
39 SELECT *
40 FROM tb_partition
41
42 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
43 │
1 │ 张三三 │ 2021-02-24 12:20:20 │ 120000 │
44 └─────┴────────┴─────────────────────┴────────┘
45 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
46 │
1 │ 张三三 │ 2021-03-24 12:20:20 │ 120000 │
47 └─────┴────────┴─────────────────────┴────────┘
48 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
49 │
1 │ 张三三 │ 2021-01-22 12:20:20 │ 120000 │
50 │
1 │ 张三三 │ 2021-01-23 12:20:20 │ 120000 │
51 │
1 │ 张三三 │ 2021-01-24 12:20:20 │ 120000 │
52 └─────┴────────┴─────────────────────┴────────┘
53 ┌─pid─┬─name───┬───────────────ctime─┬──money─┐
54 │
1 │ 张三三 │ 2021-02-22 12:20:20 │ 120000 │
55 └─────┴────────┴─────────────────────┴────────┘
56
57 6 rows in set. Elapsed: 0.016 sec.
58
59 master :)

参考：https://clickhouse.tech/docs/zh/sql-reference/statements/alter/#alter_drop-column，更多参考官网。

8、数据表DDL，目前只有MergeTree、Merge和Distributed这三类表引擎支持Alter查询，所以在进行alter操作的是注意表的引擎。

　　1)、DML(data manipulation language，数据操作语言)：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。凡是和数据的增删改查有关的是DML。

　　2)、DDL(data definition language，数据定义语言)：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表(TABLE)的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。凡是和数据库，数据表结构有关的都是DDL

　　3)、DCL(Data Control Language，数据控制语言)：是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括(grant、deny、revoke等)语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL 。

列操作，改变表结构的标准语法：

复制代码

1
1 ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...

在语句中，配置一个或多个用逗号分隔的动作。每个动作是对某个列实施的操作行为。支持下列动作：

1)、ADD COLUMN — 添加列，默认添加到最后一列的后面，也可以指定列的位置。

复制代码

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 1 master :)
2 master :) CREATE TABLE tb_alter
3 :-] (
4 :-]
`pid` UInt8,
5 :-] `name` String
6 :-] )engine=MergeTree()
7 :-] order by pid;
8
9 CREATE TABLE tb_alter
10 (
11
`pid` UInt8,
12
`name` String
13 )
14 ENGINE = MergeTree()
15 ORDER BY pid
16
17 Ok.
18
19 0 rows in set. Elapsed: 0.010 sec.
20
21 master :) alter table tb_alter add column age UInt8;
22
23 ALTER TABLE tb_alter
24
ADD COLUMN `age` UInt8
25
26
27 Ok.
28
29 0 rows in set. Elapsed: 0.024 sec.
30
31 master :) desc tb_alter;
32
33 DESCRIBE TABLE tb_alter
34
35 ┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
36 │ pid
│ UInt8
│
│
│
│
│
│
37 │ name │ String │
│
│
│
│
│
38 │ age
│ UInt8
│
│
│
│
│
│
39 └──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
40
41 3 rows in set. Elapsed: 0.004 sec.
42
43 master :)

2)、DROP COLUMN — 删除列，删除列之后，列中的数据也会被删除。

复制代码

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
 1 master :) alter table tb_alter drop column age;
2
3 ALTER TABLE tb_alter
4
DROP COLUMN age
5
6
7 Ok.
8
9 0 rows in set. Elapsed: 0.034 sec.
10
11 master :) desc tb_alter;
12
13 DESCRIBE TABLE tb_alter
14
15 ┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
16 │ pid
│ UInt8
│
│
│
│
│
│
17 │ name │ String │
│
│
│
│
│
18 └──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
19
20 2 rows in set. Elapsed: 0.004 sec.
21
22 master :)

3)、CLEAR COLUMN — 重置列的值。

复制代码

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
 1 master :) select * from tb_alter;
2
3 SELECT *
4 FROM tb_alter
5
6 ┌─pid─┬─name───┐
7 │
1 │ 张三三 │
8 └─────┴────────┘
9
10 1 rows in set. Elapsed: 0.007 sec.
11
12 master :) alter table tb_alter clear column name;
13
14 ALTER TABLE tb_alter
15
CLEAR COLUMN name
16
17
18 Ok.
19
20 0 rows in set. Elapsed: 0.029 sec.
21
22 master :) select * from tb_alter;
23
24 SELECT *
25 FROM tb_alter
26
27 ┌─pid─┬─name─┐
28 │
1 │
│
29 └─────┴──────┘
30
31 1 rows in set. Elapsed: 0.006 sec.
32
33 master :)

4)、COMMENT COLUMN — 给列增加注释说明。

复制代码

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
 1 master :)
2 master :) alter table tb_alter comment column name '姓名';
3
4 ALTER TABLE tb_alter
5
COMMENT COLUMN name '姓名'
6
7
8 Ok.
9
10 0 rows in set. Elapsed: 0.004 sec.
11
12 master :) desc tb_alter;
13
14 DESCRIBE TABLE tb_alter
15
16 ┌─name─┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
17 │ pid
│ UInt8
│
│
│
│
│
│
18 │ name │ String │
│
│ 姓名
│
│
│
19 └──────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
20
21 2 rows in set. Elapsed: 0.003 sec.
22
23 master :)

5)、MODIFY COLUMN — 改变列的值类型，默认表达式以及TTL。不能修改主键和排序字段。

复制代码

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
 1 master :) alter table tb_alter modify column name UUID;
2
3 ALTER TABLE tb_alter
4
MODIFY COLUMN `name` UUID
5
6
7 Ok.
8
9 0 rows in set. Elapsed: 0.008 sec.
10
11 master :) desc tb_alter;
12
13 DESCRIBE TABLE tb_alter
14
15 ┌─name─┬─type──┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
16 │ pid
│ UInt8 │
│
│
│
│
│
17 │ name │ UUID
│
│
│ 姓名
│
│
│
18 └──────┴───────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
19
20 2 rows in set. Elapsed: 0.006 sec.
21
22 master :)

参考：https://clickhouse.tech/docs/zh/sql-reference/statements/misc/#misc_operations-rename，更多参考官网。

9、Clickhouse移动表，在Linux系统中，mv命令的本意是将一个文件从原始位置A移动到目标位置B，但是如果位置A和位置B相同，则可以变相实现重命名的作用。Clickhouse的Rename查询就与之有异曲同工之妙，Rename语句的完整语法，如下所示：

复制代码

1
1 RENAME TABLE [db11.]name11 TO [db12.]name12, [db21.]name21 TO [db22.]name22, ... [ON CLUSTER cluster]

　　Rename可以修改表的名称，如果将原始数据库和目标数据库设为不同的名称，那么就可以实现数据表在两个数据库之间移动的效果。

复制代码

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 1 master :) show tables;
2
3 SHOW TABLES
4
5 ┌─name───────────────┐
6 │ .inner.m_name_view │
7 │ m_name_view
│
8 │ tb_alter
│
9 │ tb_array
│
10 │ tb_enum
│
11 │ tb_name
│
12 │ tb_partition
│
13 │ tb_tinyLog
│
14 │ tb_tuple
│
15 │ user_db
│
16 │ view_name
│
17 └────────────────────┘
18
19 11 rows in set. Elapsed: 0.006 sec.
20
21 master :) rename table tb_alter to tb_alter2;
22
23 RENAME TABLE tb_alter TO tb_alter2
24
25 Ok.
26
27 0 rows in set. Elapsed: 0.005 sec.
28
29 master :) show tables;
30
31 SHOW TABLES
32
33 ┌─name───────────────┐
34 │ .inner.m_name_view │
35 │ m_name_view
│
36 │ tb_alter2
│
37 │ tb_array
│
38 │ tb_enum
│
39 │ tb_name
│
40 │ tb_partition
│
41 │ tb_tinyLog
│
42 │ tb_tuple
│
43 │ user_db
│
44 │ view_name
│
45 └────────────────────┘
46
47 11 rows in set. Elapsed: 0.006 sec.
48
49 master :)

　　需要注意的是，数据表的移动只能在单个节点的范围内，换言之，数据表移动的目标数据库和原始数据库必须在同一个服务节点内，而不能是集群的远程节点。

复制代码

1 master :) show tables;
2
3 SHOW TABLES
4
5 ┌─name───────────────┐
6 │ .inner.m_name_view │
7 │ m_name_view
│
8 │ tb_alter2
│
9 │ tb_array
│
10 │ tb_enum
│
11 │ tb_name
│
12 │ tb_partition
│
13 │ tb_tinyLog
│
14 │ tb_tuple
│
15 │ user_db
│
16 │ view_name
│
17 └────────────────────┘
18
19 11 rows in set. Elapsed: 0.007 sec.
20
21 master :) rename table tb_alter2 to gab_db2.tb_alter2;
22
23 RENAME TABLE tb_alter2 TO gab_db2.tb_alter2
24
25 Ok.
26
27 0 rows in set. Elapsed: 0.004 sec.
28
29 master :) show tables;
30
31 SHOW TABLES
32
33 ┌─name───────────────┐
34 │ .inner.m_name_view │
35 │ m_name_view
│
36 │ tb_array
│
37 │ tb_enum
│
38 │ tb_name
│
39 │ tb_partition
│
40 │ tb_tinyLog
│
41 │ tb_tuple
│
42 │ user_db
│
43 │ view_name
│
44 └────────────────────┘
45
46 10 rows in set. Elapsed: 0.007 sec.
47
48 master :) show tables in gab_db2;;
49
50 SHOW TABLES FROM gab_db2
51
52 ┌─name──────┐
53 │ tb_alter2 │
54 │ tb_name
│
55 └───────────┘
56
57 2 rows in set. Elapsed: 0.009 sec.
58
59 master :)

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 1 master :) show tables;
2
3 SHOW TABLES
4
5 ┌─name───────────────┐
6 │ .inner.m_name_view │
7 │ m_name_view
│
8 │ tb_alter2
│
9 │ tb_array
│
10 │ tb_enum
│
11 │ tb_name
│
12 │ tb_partition
│
13 │ tb_tinyLog
│
14 │ tb_tuple
│
15 │ user_db
│
16 │ view_name
│
17 └────────────────────┘
18
19 11 rows in set. Elapsed: 0.007 sec.
20
21 master :) rename table tb_alter2 to gab_db2.tb_alter2;
22
23 RENAME TABLE tb_alter2 TO gab_db2.tb_alter2
24
25 Ok.
26
27 0 rows in set. Elapsed: 0.004 sec.
28
29 master :) show tables;
30
31 SHOW TABLES
32
33 ┌─name───────────────┐
34 │ .inner.m_name_view │
35 │ m_name_view
│
36 │ tb_array
│
37 │ tb_enum
│
38 │ tb_name
│
39 │ tb_partition
│
40 │ tb_tinyLog
│
41 │ tb_tuple
│
42 │ user_db
│
43 │ view_name
│
44 └────────────────────┘
45
46 10 rows in set. Elapsed: 0.007 sec.
47
48 master :) show tables in gab_db2;;
49
50 SHOW TABLES FROM gab_db2
51
52 ┌─name──────┐
53 │ tb_alter2 │
54 │ tb_name
│
55 └───────────┘
56
57 2 rows in set. Elapsed: 0.009 sec.
58
59 master :)

可以同时，在移动数据表的时候，修改数据表的名称，如下所示：

复制代码

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
 1 master :)
2 master :) rename table gab_db2.tb_alter2 to gab_db.tb_alter;
3
4 RENAME TABLE gab_db2.tb_alter2 TO gab_db.tb_alter
5
6 Ok.
7
8 0 rows in set. Elapsed: 0.006 sec.
9
10 master :) show tables;
11
12 SHOW TABLES
13
14 ┌─name───────────────┐
15 │ .inner.m_name_view │
16 │ m_name_view
│
17 │ tb_alter
│
18 │ tb_array
│
19 │ tb_enum
│
20 │ tb_name
│
21 │ tb_partition
│
22 │ tb_tinyLog
│
23 │ tb_tuple
│
24 │ user_db
│
25 │ view_name
│
26 └────────────────────┘
27
28 11 rows in set. Elapsed: 0.007 sec.
29
30 master :)

参考：https://clickhouse.tech/docs/zh/engines/table-engines/mergetree-family/mergetree/#mergetree-column-ttl，更多参考官网。

10、Clickhouse的TTL，定义值的存储时间，只能为MergeTree系统表指定，确定值的生存期。当列中的值过期时候，Clickhouse会将其替换为列数据类型的默认值，如果数据部分中的所有列均已过期，Clickhouse则将从文件系统中的数据部分删除此列。TTL可以为整个表和每个单独的列设置该子句，表级TTL也可以指定在磁盘和卷之间自动移动数据的逻辑。

　　1)、列TTL，当列的时间超过设置的值，一整列数据会被删除。表TTL，当表中的某个写行的时间超过设置的值，整行会被删除。

　　2)、TTL 表达式的计算结果必须是日期或日期时间类型的字段。例如TTL time_column、TTL time_column + interval。要定义interval, 需要使用时间间隔操作符，TTL date_time + INTERVAL 1 MONTH、TTL date_time + INTERVAL 15 HOUR。

　　3)、列TTL，当列中的值过期时, ClickHouse会将它们替换成该列数据类型的默认值。如果数据片段中列的所有值均已过期，则ClickHouse 会从文件系统中的数据片段中删除此列。TTL子句不能被用于主键字段。

复制代码

1 master :)
2 master :) CREATE TABLE tb_ttl
3 :-] (
4 :-]
id Int TTL ctime + INTERVAL 1 MONTH,
5 :-]
age Int TTL ctime + INTERVAL 1 HOUR,
6 :-]
name String,
7 :-] ctime DateTime
8 :-] )ENGINE = MergeTree
9 :-] PARTITION BY toYYYYMM(ctime)
10 :-] ORDER BY ctime;
11
12 CREATE TABLE tb_ttl
13 (
14
`id` Int TTL ctime + toIntervalMonth(1),
15
`age` Int TTL ctime + toIntervalHour(1),
16
`name` String,
17
`ctime` DateTime
18 )
19 ENGINE = MergeTree
20 PARTITION BY toYYYYMM(ctime)
21 ORDER BY ctime
22
23 Ok.
24
25 0 rows in set. Elapsed: 0.035 sec.
26
27 master :) desc tb_ttl;
28
29 DESCRIBE TABLE tb_ttl
30
31 ┌─name──┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─────────────┐
32 │ id
│ Int32
│
│
│
│
│ ctime + toIntervalMonth(1) │
33 │ age
│ Int32
│
│
│
│
│ ctime + toIntervalHour(1)
│
34 │ name
│ String
│
│
│
│
│
│
35 │ ctime │ DateTime │
│
│
│
│
│
36 └───────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────────────────┘
37
38 4 rows in set. Elapsed: 0.007 sec.
39
40 master :)
41 master :)
42 master :) CREATE TABLE tb_ttl
43 :-] (
44 :-]
id Int TTL ctime + INTERVAL 1 MONTH,
45 :-]
age Int TTL ctime + INTERVAL 1 HOUR,
46 :-]
name String,
47 :-] ctime DateTime
48 :-] )ENGINE = MergeTree
49 :-] PARTITION BY toYYYYMM(ctime)
50 :-] ORDER BY ctime;
51
52 CREATE TABLE tb_ttl
53 (
54
`id` Int TTL ctime + toIntervalMonth(1),
55
`age` Int TTL ctime + toIntervalHour(1),
56
`name` String,
57
`ctime` DateTime
58 )
59 ENGINE = MergeTree
60 PARTITION BY toYYYYMM(ctime)
61 ORDER BY ctime
62
63 Ok.
64
65 0 rows in set. Elapsed: 0.035 sec.
66
67 master :) desc tb_ttl;
68
69 DESCRIBE TABLE tb_ttl
70
71 ┌─name──┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─────────────┐
72 │ id
│ Int32
│
│
│
│
│ ctime + toIntervalMonth(1) │
73 │ age
│ Int32
│
│
│
│
│ ctime + toIntervalHour(1)
│
74 │ name
│ String
│
│
│
│
│
│
75 │ ctime │ DateTime │
│
│
│
│
│
76 └───────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────────────────┘
77
78 4 rows in set. Elapsed: 0.007 sec.
79
80 master :)

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
 1 master :)
2 master :) CREATE TABLE tb_ttl
3 :-] (
4 :-]
id Int TTL ctime + INTERVAL 1 MONTH,
5 :-]
age Int TTL ctime + INTERVAL 1 HOUR,
6 :-]
name String,
7 :-] ctime DateTime
8 :-] )ENGINE = MergeTree
9 :-] PARTITION BY toYYYYMM(ctime)
10 :-] ORDER BY ctime;
11
12 CREATE TABLE tb_ttl
13 (
14
`id` Int TTL ctime + toIntervalMonth(1),
15
`age` Int TTL ctime + toIntervalHour(1),
16
`name` String,
17
`ctime` DateTime
18 )
19 ENGINE = MergeTree
20 PARTITION BY toYYYYMM(ctime)
21 ORDER BY ctime
22
23 Ok.
24
25 0 rows in set. Elapsed: 0.035 sec.
26
27 master :) desc tb_ttl;
28
29 DESCRIBE TABLE tb_ttl
30
31 ┌─name──┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─────────────┐
32 │ id
│ Int32
│
│
│
│
│ ctime + toIntervalMonth(1) │
33 │ age
│ Int32
│
│
│
│
│ ctime + toIntervalHour(1)
│
34 │ name
│ String
│
│
│
│
│
│
35 │ ctime │ DateTime │
│
│
│
│
│
36 └───────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────────────────┘
37
38 4 rows in set. Elapsed: 0.007 sec.
39
40 master :)
41 master :)
42 master :) CREATE TABLE tb_ttl
43 :-] (
44 :-]
id Int TTL ctime + INTERVAL 1 MONTH,
45 :-]
age Int TTL ctime + INTERVAL 1 HOUR,
46 :-]
name String,
47 :-] ctime DateTime
48 :-] )ENGINE = MergeTree
49 :-] PARTITION BY toYYYYMM(ctime)
50 :-] ORDER BY ctime;
51
52 CREATE TABLE tb_ttl
53 (
54
`id` Int TTL ctime + toIntervalMonth(1),
55
`age` Int TTL ctime + toIntervalHour(1),
56
`name` String,
57
`ctime` DateTime
58 )
59 ENGINE = MergeTree
60 PARTITION BY toYYYYMM(ctime)
61 ORDER BY ctime
62
63 Ok.
64
65 0 rows in set. Elapsed: 0.035 sec.
66
67 master :) desc tb_ttl;
68
69 DESCRIBE TABLE tb_ttl
70
71 ┌─name──┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─────────────┐
72 │ id
│ Int32
│
│
│
│
│ ctime + toIntervalMonth(1) │
73 │ age
│ Int32
│
│
│
│
│ ctime + toIntervalHour(1)
│
74 │ name
│ String
│
│
│
│
│
│
75 │ ctime │ DateTime │
│
│
│
│
│
76 └───────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────────────────┘
77
78 4 rows in set. Elapsed: 0.007 sec.
79
80 master :)

为数据表中已存在的列字段添加 TTL，如下所示：

复制代码

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
50
 1 master :) ALTER TABLE tb_ttl MODIFY COLUMN name String TTL ctime + INTERVAL 1 DAY;
2
3 ALTER TABLE tb_ttl
4
MODIFY COLUMN `name` String TTL ctime + toIntervalDay(1)
5
6
7 Ok.
8
9 0 rows in set. Elapsed: 0.012 sec.
10
11 master :) desc tb_ttl;
12
13 DESCRIBE TABLE tb_ttl
14
15 ┌─name──┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─────────────┐
16 │ id
│ Int32
│
│
│
│
│ ctime + toIntervalMonth(1) │
17 │ age
│ Int32
│
│
│
│
│ ctime + toIntervalHour(1)
│
18 │ name
│ String
│
│
│
│
│ ctime + toIntervalDay(1)
│
19 │ ctime │ DateTime │
│
│
│
│
│
20 └───────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────────────────┘
21
22 4 rows in set. Elapsed: 0.005 sec.
23
24 master :)

修改数据表的列字段的 TTL，如下所示：

复制代码

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
50
 1 master :) ALTER TABLE tb_ttl MODIFY COLUMN name String TTL ctime + INTERVAL 1 HOUR;
2
3 ALTER TABLE tb_ttl
4
MODIFY COLUMN `name` String TTL ctime + toIntervalHour(1)
5
6
7 Ok.
8
9 0 rows in set. Elapsed: 0.012 sec.
10
11 master :) desc tb_ttl;
12
13 DESCRIBE TABLE tb_ttl
14
15 ┌─name──┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─────────────┐
16 │ id
│ Int32
│
│
│
│
│ ctime + toIntervalMonth(1) │
17 │ age
│ Int32
│
│
│
│
│ ctime + toIntervalHour(1)
│
18 │ name
│ String
│
│
│
│
│ ctime + toIntervalHour(1)
│
19 │ ctime │ DateTime │
│
│
│
│
│
20 └───────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────────────────┘
21
22 4 rows in set. Elapsed: 0.004 sec.
23
24 master :)

4)、表 TTL，表可以设置一个用于移除过期行的表达式，以及多个用于在磁盘或卷上自动转移数据片段的表达式。当表中的行过期时，ClickHouse 会删除所有对应的行。对于数据片段的转移特性，必须所有的行都满足转移条件。

复制代码

1
1 TTL expr [DELETE|TO DISK 'aaa'|TO VOLUME 'bbb'], ...

TTL 规则的类型紧跟在每个 TTL 表达式后面，它会影响满足表达式时(到达指定时间时)应当执行的操作：

　　a、DELETE - 删除过期的行(默认操作)。

　　b、TO DISK 'aaa' - 将数据片段移动到磁盘 aaa。

　　c、TO VOLUME 'bbb' - 将数据片段移动到卷 bbb。

复制代码

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
50
51
52
53
 1 master :)
2 master :) CREATE TABLE tb_table_ttl
3 :-] (
4 :-]
ctime DateTime,
5 :-]
id Int
6 :-] )
7 :-] ENGINE = MergeTree
8 :-] PARTITION BY toYYYYMM(ctime)
9 :-] ORDER BY ctime
10 :-] TTL ctime + INTERVAL 1 MONTH DELETE;
11
12 CREATE TABLE tb_table_ttl
13 (
14
`ctime` DateTime,
15
`id` Int
16 )
17 ENGINE = MergeTree
18 PARTITION BY toYYYYMM(ctime)
19 ORDER BY ctime
20 TTL ctime + toIntervalMonth(1)
21
22 Ok.
23
24 0 rows in set. Elapsed: 0.012 sec.
25
26 master :) desc tb_table_ttl;
27
28 DESCRIBE TABLE tb_table_ttl
29
30 ┌─name──┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
31 │ ctime │ DateTime │
│
│
│
│
│
32 │ id
│ Int32
│
│
│
│
│
│
33 └───────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
34
35 2 rows in set. Elapsed: 0.004 sec.
36
37 master :)

修改数据表的 TTL，如下所示：

复制代码

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
 1 master :) ALTER TABLE tb_table_ttl MODIFY TTL ctime + INTERVAL 1 DAY;
2
3 ALTER TABLE tb_table_ttl
4
MODIFY TTL ctime + toIntervalDay(1)
5
6
7 Ok.
8
9 0 rows in set. Elapsed: 0.018 sec.
10
11 master :) desc tb_table_ttl;
12
13 DESCRIBE TABLE tb_table_ttl
14
15 ┌─name──┬─type─────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
16 │ ctime │ DateTime │
│
│
│
│
│
17 │ id
│ Int32
│
│
│
│
│
│
18 └───────┴──────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
19
20 2 rows in set. Elapsed: 0.004 sec.
21
22 master :)

11、Clickhouse的数据分区的DDL操作。

　　Clickhouse内置了很多system系统表，用于查询自身的状态信息，其中parts系统表专门用于查询数据表的分区信息，show tables from system;

复制代码

1 master :)
2 master :) CREATE TABLE tb_partition
3 :-] (
4 :-]
`pid` UInt8,
5 :-] `name` String,
6 :-] `city` String
7 :-] )engine=MergeTree()
8 :-] partition by(city)
9 :-] order by pid;
10
11 CREATE TABLE tb_partition
12 (
13
`pid` UInt8,
14
`name` String,
15
`city` String
16 )
17 ENGINE = MergeTree()
18 PARTITION BY city
19 ORDER BY pid
20
21 Ok.
22
23 0 rows in set. Elapsed: 0.008 sec.
24
25 master :) insert into tb_partition values(1, '张三三', '北京市'),
26 :-] (1, '李思思', '天津市'),
27 :-] (1, '王五五', '重庆市'),
28 :-] (1, '小刚', '重庆市'),
29 :-] (1, '小红', '重庆市'),
30 :-] (1, '小明', '北京市');
31
32 INSERT INTO tb_partition VALUES
33
34 Ok.
35
36 6 rows in set. Elapsed: 0.006 sec.
37
38 master :) select * from tb_partition;
39
40 SELECT *
41 FROM tb_partition
42
43 ┌─pid─┬─name───┬─city───┐
44 │
1 │ 张三三 │ 北京市 │
45 │
1 │ 小明
│ 北京市 │
46 └─────┴────────┴────────┘
47 ┌─pid─┬─name───┬─city───┐
48 │
1 │ 李思思 │ 天津市 │
49 └─────┴────────┴────────┘
50 ┌─pid─┬─name───┬─city───┐
51 │
1 │ 王五五 │ 重庆市 │
52 │
1 │ 小刚
│ 重庆市 │
53 │
1 │ 小红
│ 重庆市 │
54 └─────┴────────┴────────┘
55
56 6 rows in set. Elapsed: 0.021 sec.
57
58 master :)

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
 1 master :)
2 master :) CREATE TABLE tb_partition
3 :-] (
4 :-]
`pid` UInt8,
5 :-] `name` String,
6 :-] `city` String
7 :-] )engine=MergeTree()
8 :-] partition by(city)
9 :-] order by pid;
10
11 CREATE TABLE tb_partition
12 (
13
`pid` UInt8,
14
`name` String,
15
`city` String
16 )
17 ENGINE = MergeTree()
18 PARTITION BY city
19 ORDER BY pid
20
21 Ok.
22
23 0 rows in set. Elapsed: 0.008 sec.
24
25 master :) insert into tb_partition values(1, '张三三', '北京市'),
26 :-] (1, '李思思', '天津市'),
27 :-] (1, '王五五', '重庆市'),
28 :-] (1, '小刚', '重庆市'),
29 :-] (1, '小红', '重庆市'),
30 :-] (1, '小明', '北京市');
31
32 INSERT INTO tb_partition VALUES
33
34 Ok.
35
36 6 rows in set. Elapsed: 0.006 sec.
37
38 master :) select * from tb_partition;
39
40 SELECT *
41 FROM tb_partition
42
43 ┌─pid─┬─name───┬─city───┐
44 │
1 │ 张三三 │ 北京市 │
45 │
1 │ 小明
│ 北京市 │
46 └─────┴────────┴────────┘
47 ┌─pid─┬─name───┬─city───┐
48 │
1 │ 李思思 │ 天津市 │
49 └─────┴────────┴────────┘
50 ┌─pid─┬─name───┬─city───┐
51 │
1 │ 王五五 │ 重庆市 │
52 │
1 │ 小刚
│ 重庆市 │
53 │
1 │ 小红
│ 重庆市 │
54 └─────┴────────┴────────┘
55
56 6 rows in set. Elapsed: 0.021 sec.
57
58 master :)

可以使用Clickhouse的system数据库下面的表parts查看自己创建的分区数据信息，如下所示：

复制代码

1 master :)
2 master :) select path,table,name,partition from system.parts where table = 'tb_partition';
3
4 SELECT
5
path,
6
table,
7
name,
8
partition
9 FROM system.parts
10 WHERE table = 'tb_partition'
11
12 ┌─path──────────────────────────────────────────────────────────────────────────────────┬─table────────┬─name───────────────────────────────────┬─partition─┐
13 │ /var/lib/clickhouse/data/default/tb_partition/0166cf470a90ac651aab8618c56861aa_2_2_0/ │ tb_partition │ 0166cf470a90ac651aab8618c56861aa_2_2_0 │ 天津市
│
14 │ /var/lib/clickhouse/data/default/tb_partition/44ceaf3baa845be4fde99b820ce370b7_1_1_0/ │ tb_partition │ 44ceaf3baa845be4fde99b820ce370b7_1_1_0 │ 北京市
│
15 │ /var/lib/clickhouse/data/default/tb_partition/63ff9f9250ece854b1460461034fc2da_3_3_0/ │ tb_partition │ 63ff9f9250ece854b1460461034fc2da_3_3_0 │ 重庆市
│
16 └───────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────────────────────────┴───────────┘
17
18 3 rows in set. Elapsed: 0.021 sec.
19
20 master :)
21 master :)
22 master :)

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
 1 master :)
2 master :) select path,table,name,partition from system.parts where table = 'tb_partition';
3
4 SELECT
5
path,
6
table,
7
name,
8
partition
9 FROM system.parts
10 WHERE table = 'tb_partition'
11
12 ┌─path──────────────────────────────────────────────────────────────────────────────────┬─table────────┬─name───────────────────────────────────┬─partition─┐
13 │ /var/lib/clickhouse/data/default/tb_partition/0166cf470a90ac651aab8618c56861aa_2_2_0/ │ tb_partition │ 0166cf470a90ac651aab8618c56861aa_2_2_0 │ 天津市
│
14 │ /var/lib/clickhouse/data/default/tb_partition/44ceaf3baa845be4fde99b820ce370b7_1_1_0/ │ tb_partition │ 44ceaf3baa845be4fde99b820ce370b7_1_1_0 │ 北京市
│
15 │ /var/lib/clickhouse/data/default/tb_partition/63ff9f9250ece854b1460461034fc2da_3_3_0/ │ tb_partition │ 63ff9f9250ece854b1460461034fc2da_3_3_0 │ 重庆市
│
16 └───────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────────────────────────┴───────────┘
17
18 3 rows in set. Elapsed: 0.021 sec.
19
20 master :)
21 master :)
22 master :)

合理的设计分区键并利用分区的删除功能，就能够达到数据更新的目的，如下所示：

　　此命令会将指定分区删除，并且分区中的数据也会被删除，我们的可以删除指定分区，然后再导入这个分区的数据，从而达到分区数据更新的目的。

复制代码

1 master :) alter table tb_partition drop partition '天津市';
2
3 ALTER TABLE tb_partition
4
DROP PARTITION '天津市'
5
6
7 Ok.
8
9 0 rows in set. Elapsed: 0.008 sec.
10
11 master :) select path,table,name,partition from system.parts where table = 'tb_partition';
12
13 SELECT
14
path,
15
table,
16
name,
17
partition
18 FROM system.parts
19 WHERE table = 'tb_partition'
20
21 ┌─path──────────────────────────────────────────────────────────────────────────────────┬─table────────┬─name───────────────────────────────────┬─partition─┐
22 │ /var/lib/clickhouse/data/default/tb_partition/44ceaf3baa845be4fde99b820ce370b7_1_1_0/ │ tb_partition │ 44ceaf3baa845be4fde99b820ce370b7_1_1_0 │ 北京市
│
23 │ /var/lib/clickhouse/data/default/tb_partition/63ff9f9250ece854b1460461034fc2da_3_3_0/ │ tb_partition │ 63ff9f9250ece854b1460461034fc2da_3_3_0 │ 重庆市
│
24 └───────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────────────────────────┴───────────┘
25
26 2 rows in set. Elapsed: 0.022 sec.
27
28 master :)

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
 1 master :) alter table tb_partition drop partition '天津市';
2
3 ALTER TABLE tb_partition
4
DROP PARTITION '天津市'
5
6
7 Ok.
8
9 0 rows in set. Elapsed: 0.008 sec.
10
11 master :) select path,table,name,partition from system.parts where table = 'tb_partition';
12
13 SELECT
14
path,
15
table,
16
name,
17
partition
18 FROM system.parts
19 WHERE table = 'tb_partition'
20
21 ┌─path──────────────────────────────────────────────────────────────────────────────────┬─table────────┬─name───────────────────────────────────┬─partition─┐
22 │ /var/lib/clickhouse/data/default/tb_partition/44ceaf3baa845be4fde99b820ce370b7_1_1_0/ │ tb_partition │ 44ceaf3baa845be4fde99b820ce370b7_1_1_0 │ 北京市
│
23 │ /var/lib/clickhouse/data/default/tb_partition/63ff9f9250ece854b1460461034fc2da_3_3_0/ │ tb_partition │ 63ff9f9250ece854b1460461034fc2da_3_3_0 │ 重庆市
│
24 └───────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────────────────────────┴───────────┘
25
26 2 rows in set. Elapsed: 0.022 sec.
27
28 master :)

Clickhouse支持将A表中的分区数据复制到B表，这项特性可以用于快速数据写入，多表间数据同步和备份等场景，不过需要注意的是，并不是任意数据表质检都能够相互复制，它们还需要满足两个前提条件，如下所示：

　　1)、两张表需要拥有相同的分区键。
　　2)、它们的表结构完全相同。

复制代码

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
50
51
52
53
54
55
56
57
 1 master :) CREATE TABLE tb_partition2
2 :-] (
3 :-]
`pid` UInt8,
4 :-]
`name` String,
5 :-]
`city` String
6 :-] )
7 :-] ENGINE = MergeTree()
8 :-] PARTITION BY city
9 :-] ORDER BY pid;
10
11 CREATE TABLE tb_partition2
12 (
13
`pid` UInt8,
14
`name` String,
15
`city` String
16 )
17 ENGINE = MergeTree()
18 PARTITION BY city
19 ORDER BY pid
20
21 Ok.
22
23 0 rows in set. Elapsed: 0.017 sec.
24
25 master :) alter table tb_partition2 replace partition '北京市' from tb_partition;
26
27 ALTER TABLE tb_partition2
28
REPLACE PARTITION '北京市' FROM tb_partition
29
30
31 Ok.
32
33 0 rows in set. Elapsed: 0.004 sec.
34
35 master :) select * from tb_partition2;
36
37 SELECT *
38 FROM tb_partition2
39
40 ┌─pid─┬─name───┬─city───┐
41 │
1 │ 张三三 │ 北京市 │
42 │
1 │ 小明
│ 北京市 │
43 └─────┴────────┴────────┘
44
45 2 rows in set. Elapsed: 0.017 sec.
46
47 master :)

重置分区数据，如果数据表某一列的数据有误，需要将其重置为初始值，如果设置了默认值那么就是默认值数据，如果没有设置默认值，系统会给出默认的初始值，此时可以使用下面的语句实现：

复制代码

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
 1 master :) alter table tb_partition2 clear column name in partition '北京市';
2
3 ALTER TABLE tb_partition2
4
CLEAR COLUMN name
IN PARTITION '北京市'
5
6
7 Ok.
8
9 0 rows in set. Elapsed: 0.014 sec.
10
11 master :) select * from tb_partition2;
12
13 SELECT *
14 FROM tb_partition2
15
16 ┌─pid─┬─name─┬─city───┐
17 │
1 │
│ 北京市 │
18 │
1 │
│ 北京市 │
19 └─────┴──────┴────────┘
20
21 2 rows in set. Elapsed: 0.023 sec.
22
23 master :)

卸载分区和装载分区，表分区可以通过detach语句协助分区，卸载分区之后，它的物理数据并没有删除，而是被转移到了当前数据表目录的detached子目录下面了。而装载分区则是反向操作，它能够将detached子目录下的某个分区重新装载回去。卸载与装载这一对伴生的操作，常用于分区数据的迁移和备份场景。卸载某个分区的语法，如下所示：

复制代码

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
50
51
52
53
54
 1 master :) select * from tb_partition;
2
3 SELECT *
4 FROM tb_partition
5
6 ┌─pid─┬─name───┬─city───┐
7 │
1 │ 张三三 │ 北京市 │
8 │
1 │ 小明
│ 北京市 │
9 └─────┴────────┴────────┘
10 ┌─pid─┬─name───┬─city───┐
11 │
1 │ 王五五 │ 重庆市 │
12 │
1 │ 小刚
│ 重庆市 │
13 │
1 │ 小红
│ 重庆市 │
14 └─────┴────────┴────────┘
15
16 5 rows in set. Elapsed: 0.027 sec.
17
18 master :)
19 master :) alter table tb_partition detach partition '北京市';
20
21 ALTER TABLE tb_partition
22
DETACH PARTITION '北京市'
23
24
25 Ok.
26
27 0 rows in set. Elapsed: 0.002 sec.
28
29 master :) select * from tb_partition;
30
31 SELECT *
32 FROM tb_partition
33
34 ┌─pid─┬─name───┬─city───┐
35 │
1 │ 王五五 │ 重庆市 │
36 │
1 │ 小刚
│ 重庆市 │
37 │
1 │ 小红
│ 重庆市 │
38 └─────┴────────┴────────┘
39
40 3 rows in set. Elapsed: 0.016 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
 1 master :) alter table tb_partition attach partition '北京市';
2
3 ALTER TABLE tb_partition
4
ATTACH PARTITION '北京市'
5
6
7 Ok.
8
9 0 rows in set. Elapsed: 0.007 sec.
10
11 master :) select * from tb_partition;
12
13 SELECT *
14 FROM tb_partition
15
16 ┌─pid─┬─name───┬─city───┐
17 │
1 │ 张三三 │ 北京市 │
18 │
1 │ 小明
│ 北京市 │
19 └─────┴────────┴────────┘
20 ┌─pid─┬─name───┬─city───┐
21 │
1 │ 王五五 │ 重庆市 │
22 │
1 │ 小刚
│ 重庆市 │
23 │
1 │ 小红
│ 重庆市 │
24 └─────┴────────┴────────┘
25
26 5 rows in set. Elapsed: 0.020 sec.
27
28 master :)

12、Clickhouse的数据DML(数据操作语言)，数据导入的几种方式，如下所示：

复制代码

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
 1 ----------- 方式一，可以指定字段和对应的值
2 master :) insert into tb_partition(pid,name,city) values(2,'clickhouse','北京市');
3
4 INSERT INTO tb_partition (pid, name, city) VALUES
5
6 Ok.
7
8 1 rows in set. Elapsed: 0.012 sec.
9
10 ----------- 方式二，可以直接给定全部字段的值
11 master :) insert into tb_partition values(3,'java','北京市');
12
13 INSERT INTO tb_partition VALUES
14
15 Ok.
16
17 1 rows in set. Elapsed: 0.007 sec.
18
19 ----------- 方式三，可以查询出一个表的字段，插入到另外一个表中
20 master :) insert into tb_partition select * from tb_partition2;
21
22 INSERT INTO tb_partition SELECT *
23 FROM tb_partition2
24
25 Ok.
26
27 0 rows in set. Elapsed: 0.016 sec.
28
29 master :)
30
31
32 ----------- 方式四，可以在创建的表的时候，将查询的数据插入到该表中
33 master :)
34 master :) CREATE TABLE default.tb_partition3 ENGINE=Log as select * from tb_partition;
35
36 CREATE TABLE default.tb_partition3
37 ENGINE = Log AS
38 SELECT *
39 FROM tb_partition
40
41 Ok.
42
43 0 rows in set. Elapsed: 0.024 sec.
44
45 master :)

由文件导入到数据表中的时候，需要指定文件的分割符，--format_csv_delimiter=','参数后面跟的是字段之间的分割符。

复制代码

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
50
 1 ----------- 方式五，可以使用--query参数来指定sql语句，查看相关的数据信息。
2 [root@master ~]#
3 [root@master ~]# clickhouse-client --query='show databases';
4 _temporary_and_external_tables
5 default
6 system
7
8 ----------- 可以通过-q参数来执行sql语句，查看相关的数据信息。
9 [root@master ~]# clickhouse-client -q 'show databases';
10 _temporary_and_external_tables
11 default
12 system
13 [root@master ~]#
14
15 ----------- 可以通过-n参数来开启多行sql语句同时执行。-m参数支持多行书写。
16 [root@master ~]# clickhouse-client -n -q 'show databases;use default;select * from tb_partition';
17 _temporary_and_external_tables
18 default
19 system
20 3
java
北京市
21 1
张三三
北京市
22 1
小明
北京市
23 1
北京市
24 1
北京市
25 2
clickhouse
北京市
26 1
王五五
重庆市
27 1
小刚
重庆市
28 1
小红
重庆市
29 [root@master ~]#
30
31 ----------- 由文件导入到数据表中的时候，需要指定文件的分割符，--format_csv_delimiter=','参数后面跟的是字段之间的分割符。
32 [root@master clickhouse]# clickhouse-client --format_csv_delimiter=',' -q 'insert into default.tb_partition format CSV ' < ./data.txt
33 [root@master clickhouse]#
34 [root@master clickhouse]#

13、在Clickhouse中支持更新和删除操作，这类操作称为mutation操作，它可以看作是Alter语句的变种，虽然Mutation能最终实现修改和删除，但是不能完全以通常意义上的update和delete来理解，我们必须清醒的人事到它的不同，不建议使用。

　　1)、首先，Mutation语句是一种很重的操作操作，更适用于批量数据的修改和删除，建议批量操作。

　　2)、其次，Clickhouse对数据不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚。

　　3)、最后Mutation语句执行是一个异步的后台过程，语句被提交后就会立即返回，但是这并不代表已经执行完毕，它的具体执行进度需要通过system.mutations系统表查询。注意，数据的修改和删除操作是使用MergeTree家族引擎。

复制代码

1 ----------- 删除操作的语句，必须使用MergeTree引擎。
2 master :) alter table tb_partition delete where pid = 1;
3
4 ALTER TABLE tb_partition
5
DELETE WHERE pid = 1
6
7
8 Ok.
9
10 0 rows in set. Elapsed: 0.012 sec.
11
12 master :) select * from tb_partition;
13
14 SELECT *
15 FROM tb_partition
16
17 ┌─pid─┬─name───────┬─city───┐
18 │
14 │ java
│ 北京市 │
19 │
15 │ 张三三
│ 北京市 │
20 │
16 │ 小明
│ 北京市 │
21 │
17 │ what
│ 北京市 │
22 │
18 │ why
│ 北京市 │
23 │
19 │ clickhouse │ 北京市 │
24 └─────┴────────────┴────────┘
25 ┌─pid─┬─name───────┬─city───┐
26 │
2 │ clickhouse │ 北京市 │
27 │
3 │ java
│ 北京市 │
28 └─────┴────────────┴────────┘
29 ┌─pid─┬─name───┬─city───┐
30 │
10 │ 王五五 │ 重庆市 │
31 │
11 │ 小刚
│ 重庆市 │
32 │
12 │ 小红
│ 重庆市 │
33 └─────┴────────┴────────┘
34 ┌─pid─┬─name───────┬─city───┐
35 │
4 │ java
│ 北京市 │
36 │
5 │ 张三三
│ 北京市 │
37 │
6 │ 小明
│ 北京市 │
38 │
7 │ what
│ 北京市 │
39 │
8 │ why
│ 北京市 │
40 │
9 │ clickhouse │ 北京市 │
41 └─────┴────────────┴────────┘
42 ┌─pid─┬─name───┬─city───┐
43 │
20 │ 王五五 │ 重庆市 │
44 │
21 │ 小刚
│ 重庆市 │
45 │
22 │ 小红
│ 重庆市 │
46 └─────┴────────┴────────┘
47
48 20 rows in set. Elapsed: 0.027 sec.
49
50 master :)

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 1 ----------- 删除操作的语句，必须使用MergeTree引擎。
2 master :) alter table tb_partition delete where pid = 1;
3
4 ALTER TABLE tb_partition
5
DELETE WHERE pid = 1
6
7
8 Ok.
9
10 0 rows in set. Elapsed: 0.012 sec.
11
12 master :) select * from tb_partition;
13
14 SELECT *
15 FROM tb_partition
16
17 ┌─pid─┬─name───────┬─city───┐
18 │
14 │ java
│ 北京市 │
19 │
15 │ 张三三
│ 北京市 │
20 │
16 │ 小明
│ 北京市 │
21 │
17 │ what
│ 北京市 │
22 │
18 │ why
│ 北京市 │
23 │
19 │ clickhouse │ 北京市 │
24 └─────┴────────────┴────────┘
25 ┌─pid─┬─name───────┬─city───┐
26 │
2 │ clickhouse │ 北京市 │
27 │
3 │ java
│ 北京市 │
28 └─────┴────────────┴────────┘
29 ┌─pid─┬─name───┬─city───┐
30 │
10 │ 王五五 │ 重庆市 │
31 │
11 │ 小刚
│ 重庆市 │
32 │
12 │ 小红
│ 重庆市 │
33 └─────┴────────┴────────┘
34 ┌─pid─┬─name───────┬─city───┐
35 │
4 │ java
│ 北京市 │
36 │
5 │ 张三三
│ 北京市 │
37 │
6 │ 小明
│ 北京市 │
38 │
7 │ what
│ 北京市 │
39 │
8 │ why
│ 北京市 │
40 │
9 │ clickhouse │ 北京市 │
41 └─────┴────────────┴────────┘
42 ┌─pid─┬─name───┬─city───┐
43 │
20 │ 王五五 │ 重庆市 │
44 │
21 │ 小刚
│ 重庆市 │
45 │
22 │ 小红
│ 重庆市 │
46 └─────┴────────┴────────┘
47
48 20 rows in set. Elapsed: 0.027 sec.
49
50 master :)

Clickhouse的修改语句，注意，不能修改排序和主键字段，必须使用MergeTree引擎，如下所示：

复制代码

1 master :) alter table tb_partition update name='王五' where pid = 20;
2
3 ALTER TABLE tb_partition
4
UPDATE name = '王五' WHERE pid = 20
5
6
7 Ok.
8
9 0 rows in set. Elapsed: 0.012 sec.
10
11 master :) select * from tb_partition;
12
13 SELECT *
14 FROM tb_partition
15
16 ┌─pid─┬─name───────┬─city───┐
17 │
14 │ java
│ 北京市 │
18 │
15 │ 张三三
│ 北京市 │
19 │
16 │ 小明
│ 北京市 │
20 │
17 │ what
│ 北京市 │
21 │
18 │ why
│ 北京市 │
22 │
19 │ clickhouse │ 北京市 │
23 └─────┴────────────┴────────┘
24 ┌─pid─┬─name───────┬─city───┐
25 │
2 │ clickhouse │ 北京市 │
26 │
3 │ java
│ 北京市 │
27 └─────┴────────────┴────────┘
28 ┌─pid─┬─name───┬─city───┐
29 │
10 │ 王五五 │ 重庆市 │
30 │
11 │ 小刚
│ 重庆市 │
31 │
12 │ 小红
│ 重庆市 │
32 └─────┴────────┴────────┘
33 ┌─pid─┬─name───────┬─city───┐
34 │
4 │ java
│ 北京市 │
35 │
5 │ 张三三
│ 北京市 │
36 │
6 │ 小明
│ 北京市 │
37 │
7 │ what
│ 北京市 │
38 │
8 │ why
│ 北京市 │
39 │
9 │ clickhouse │ 北京市 │
40 └─────┴────────────┴────────┘
41 ┌─pid─┬─name─┬─city───┐
42 │
20 │ 王五 │ 重庆市 │
43 │
21 │ 小刚 │ 重庆市 │
44 │
22 │ 小红 │ 重庆市 │
45 └─────┴──────┴────────┘
46
47 20 rows in set. Elapsed: 0.014 sec.
48
49 master :)

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 1 master :) alter table tb_partition update name='王五' where pid = 20;
2
3 ALTER TABLE tb_partition
4
UPDATE name = '王五' WHERE pid = 20
5
6
7 Ok.
8
9 0 rows in set. Elapsed: 0.012 sec.
10
11 master :) select * from tb_partition;
12
13 SELECT *
14 FROM tb_partition
15
16 ┌─pid─┬─name───────┬─city───┐
17 │
14 │ java
│ 北京市 │
18 │
15 │ 张三三
│ 北京市 │
19 │
16 │ 小明
│ 北京市 │
20 │
17 │ what
│ 北京市 │
21 │
18 │ why
│ 北京市 │
22 │
19 │ clickhouse │ 北京市 │
23 └─────┴────────────┴────────┘
24 ┌─pid─┬─name───────┬─city───┐
25 │
2 │ clickhouse │ 北京市 │
26 │
3 │ java
│ 北京市 │
27 └─────┴────────────┴────────┘
28 ┌─pid─┬─name───┬─city───┐
29 │
10 │ 王五五 │ 重庆市 │
30 │
11 │ 小刚
│ 重庆市 │
31 │
12 │ 小红
│ 重庆市 │
32 └─────┴────────┴────────┘
33 ┌─pid─┬─name───────┬─city───┐
34 │
4 │ java
│ 北京市 │
35 │
5 │ 张三三
│ 北京市 │
36 │
6 │ 小明
│ 北京市 │
37 │
7 │ what
│ 北京市 │
38 │
8 │ why
│ 北京市 │
39 │
9 │ clickhouse │ 北京市 │
40 └─────┴────────────┴────────┘
41 ┌─pid─┬─name─┬─city───┐
42 │
20 │ 王五 │ 重庆市 │
43 │
21 │ 小刚 │ 重庆市 │
44 │
22 │ 小红 │ 重庆市 │
45 └─────┴──────┴────────┘
46
47 20 rows in set. Elapsed: 0.014 sec.
48
49 master :)