Clickhouse建表语法、视图语法、数据表DDL（数据定义语⾔）、数据DML（数
据操作语⾔）
参考官⽹：，更多详细⽂档可以参考官⽹，强烈推荐。
1、Clickhouse创建数据库，CREATE DATABASE，该查询⽤于根据指定名称创建数据库。
1 CREATE DATABASE [IF NOT EXISTS] db_name
数据库其实只是⽤于存放表的⼀个⽬录。如果查询中存在IF NOT EXISTS，则当数据库已经存在时，该查询不会返回任何错误。
2、Clickhouse数据表的定义语法，是在标准SQL的基础之上建⽴的。Clickhouse⽬前提供了三种最基本的建表⽅法，但是注意的是在Clickhouse中建表⼀定要指定表的引擎，在指定数据表字段之后，最后⼀定要指定数据表的引擎。CREATE TABLE的三种⽅式，对于CREATE TABLE，存在以下⼏种⽅式。
2.1、第⼀种⽅式，直接指定字段名称、字段类型，是否有默认值，中⽂备注名称等信息。
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 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
170 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
251 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
361 rows in set. Elapsed: 0.014 sec.
37
38 master :)
注意，如果创建数据表没有指定数据库，将在default默认的数据库下创建⼀张数据表。注意末尾的engine参数，它被⽤于指定数据表的引擎，表引擎决定了数据表的特性，也决定了数据将会被如何存储和加载。
2.2、第⼆种⽅式，这种⽅式其实就是复制已经存在的⼀张表结构，可⽤于数据的备份，可⽤于多个数据库之间复制表机构。
创建⼀个与db2.name2具有相同结构的表，同时你可以对其指定不同的表引擎声明。如果没有表引擎声明，则创建的表将与db2.name2使⽤相同的表引擎。
1 CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name
2 [ENGINE = engine]
使⽤案例，如下所⽰：
1 master :) create database gab_db2;
2
3 CREATE DATABASE gab_db2
4
5 Ok.
6
70 rows in set. Elapsed: 0.009 sec.
8
9 master :) use gab_db2;
10
11 USE gab_db2
12
13 Ok.
14
150 rows in set. Elapsed: 0.051 sec.
16
17 master :) show tables;
18
19 SHOW TABLES
20
21 Ok.
22
230 rows in set. Elapsed: 0.010 sec.
24
25 master :) show tables;
26
27 SHOW TABLES
28
29 Ok.
30
310 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
390 rows in set. Elapsed: 0.014 sec.
40
41 master :) show tables;
42
43 SHOW TABLES
44
45┌─name────┐
46│ tb_name │
47└─────────┘
48
491 rows in set. Elapsed: 0.010 sec.
50
51 master :)
2.3、第三种⽅式，通过select查询的⽅式来创建表，同时也会导⼊查询的结果数据。
1 CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ...
使⽤案例，如下所⽰：
1 master :) create table tb_name
2 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
100 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
202 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-2014:21:30│
29│2│24│1994-05-17│2021-02-2014:21:30│
30└────┴─────┴────────────┴─────────────────────┘
31
322 rows in set. Elapsed: 0.010 sec.
33
34 master :)
3、Clickhouse删除表的语法结构。也可以通过此语法删除普通视图和物化视图。
1 DROP TABLE [IF EXISTS] [db_name.]table_name;
使⽤案例，如下所⽰：
1 master :)
2 master :) show tables;
3
4 SHOW TABLES
5
6┌─name─────┐
7│ tb_name │
8│ tb_name2 │
9└──────────┘
10
112 rows in set. Elapsed: 0.008 sec.
12
13 master :) drop table tb_name2;
14
15 DROP TABLE tb_name2
16
17 Ok.
18
190 rows in set. Elapsed: 0.005 sec.
20
21 master :) show tables;
22
23 SHOW TABLES
24
25┌─name────┐
26│ tb_name │
27└─────────┘
28
291 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 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 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
160 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
241 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
351 rows in set. Elapsed: 0.007 sec.
36
37 master :)
临时表不需要指定数据表的引擎，我们可以理解成临时表会将当前数据库中已经存在的同名表覆盖隐藏，当出现操作的时候，如果有临时表，那么会操作临时表。
6、Clickhouse拥有普通视图和物化两种视图。其中物化视图拥有独⽴的存储，⽽普通视图只是⼀层简单的查询代理。
6.1、普通视图不会存储任何数据，它只是⼀层单纯的select查询映射，起着简化查询，明晰语义的作⽤，对查询性能不会有任何增强。
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
146 rows in set. Elapsed: 0.017 sec.
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-2014:21:30│
23│2│24│1994-05-17│2021-02-2014:21:30│
24└────┴─────┴────────────┴─────────────────────┘
25
262 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
360 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-2014:21:30│
45│2│24│1994-05-17│2021-02-2014:21:30│
46└────┴─────┴────────────┴─────────────────────┘
47
482 rows in set. Elapsed: 0.011 sec.
49
50 master :)
6.2、物化视图存储的数据是由相应的SELECT查询转换得来的。物化视图是特殊的表，有数据表结构，有数据表引擎，可以将数据持久化。
在创建物化视图时，你还必须指定表的引擎，将会使⽤这个表引擎存储数据。⽬前物化视图的⼯作原理：当将数据写⼊到物化视图中SELECT⼦句所指定的表时，插⼊的数据会通过SELECT⼦句查询进⾏转换并将最终结果插⼊到视图中。
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
167 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
270 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-2014:21:30│
36│2│24│1994-05-17│2021-02-2014:21:30│
37└────┴─────┴────────────┴─────────────────────┘
38
392 rows in set. Elapsed: 0.007 sec.
40
drop删除表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;
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
250 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
331 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-2212:20:20│120000│
42└─────┴────────┴─────────────────────┴────────┘
43
441 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
574 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
651 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-2212:20:20│120000│
74└─────┴────────┴─────────────────────┴────────┘
75┌─pid─┬─name───┬───────────────ctime─┬──money─┐
76│1│张三三│2021-01-2212:20:20│120000│
77└─────┴────────┴─────────────────────┴────────┘
78
792 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-2212:20:20│120000│
8└─────┴────────┴─────────────────────┴────────┘
9┌─pid─┬─name───┬───────────────ctime─┬──money─┐
10│1│张三三│2021-01-2212:20:20│120000│
11└─────┴────────┴─────────────────────┴────────┘
12┌─pid─┬─name───┬───────────────ctime─┬──money─┐
13│1│张三三│2021-02-2412:20:20│120000│
14└─────┴────────┴─────────────────────┴────────┘
15┌─pid─┬─name───┬───────────────ctime─┬──money─┐
16│1│张三三│2021-01-2312:20:20│120000│
17└─────┴────────┴─────────────────────┴────────┘
18┌─pid─┬─name───┬───────────────ctime─┬──money─┐
19│1│张三三│2021-01-2412:20:20│120000│
20└─────┴────────┴─────────────────────┴────────┘
21┌─pid─┬─name───┬───────────────ctime─┬──money─┐
22│1│张三三│2021-03-2412:20:20│120000│
23└─────┴────────┴─────────────────────┴────────┘
24
256 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
350 rows in set. Elapsed: 0.006 sec.
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论