clickhouse学习笔记（四）库、表、分区相关DDL操作

目录

一、数据库操作

1、创建数据库

2、查询及选择数据库

3、删除数据库

二、数据表操作

1、创建表

2、删除表

3、基本操作

①追加新字段

②修改字段类型或默认值

③修改字段注释

④删除已有字段

⑤移动数据表（重命名）

⑥清空表

三、默认值

1、默认值三种定义方法之间的不同：

2、可以使用 ALTER 语句修改默认值，例如:

四、临时表

 五、分区表

1、创建分区

2、删除指定分区

3、复制分区数据

4、重置分区数据

5、装载与卸载分区

六、视图

七、分布式DDL执行

---

一、数据库操作

1、创建数据库

创建数据库的语法如下

CREATE DATABASE IF NOT EXISTS db_name[ENGINE = engine]

数据库也支持设置引擎，[ENGINE = engine]表示数据库所使用的的引擎类型，当不加[ENGINE = engine]时会默认为使用默认引擎Ordinary

目前支持5种引擎如下

• Ordinary：默认引擎，在绝大多数情况下我们都会使用默认引擎，使用时无须刻意声明，在此数据库下可以使用任意类型的表引擎
• Dictionary：字典引擎，此类数据库会自动为所有数据字典创建它们的数据表，关于数据字典的详细介绍会在后面展开
• Memory：内存引擎，用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除
• Lazy：日志引擎，此类数据库下只能使用 Log 系列的表引擎，关于 Log 表引擎的详细介绍会后续章节展开
• MySQL：MySQL 引擎，此类数据库下会自动拉取远端 MySQL 中的数据，并为它们创建 MySQL 表引擎的数据表，关于MySQL表引擎的详细介绍也会在后续章节展开。

数据库的实质就是物理磁盘上的一个目录文件，在执行创建语句后安装路径下会创建对应名字的目录文件

2、查询及选择数据库

SHOW DATABASES;USE DATABASES;

3、删除数据库

DROP DATABASE [IF EXISTS] db_name;

二、数据表操作

表也是在物理磁盘上的一个目录文件，会在数据库的目录下，而数据是在表的目录下的文本文件

1、创建表

clickhouse提供了三种建表方式：

第一种：

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (column_name1 type [DEFAULT|MATERIALIZED|ALIAS expr],column_name2 type [DEFAULT|MATERIALIZED|ALIAS expr],......
) ENGINE = engine#使用 [db_name.] 参数可以为数据表指定数据库，如果不指定此参数，则默认会使用 default 数据库

第二种：支持在不同的数据库之间复制表结构

CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name1 AS [db_name2.]table_name2 [ENGINE = engine]

第三种：通过 SELECT 子句的形式创建，同时还会将 SELECT 子句查询的数据顺带写入

CREATE TABLE [IF NOT EXISTS] [db_name].table_name ENGINE = engine AS SELECT ...
#如下
CREATE TABLE IF NOT EXISTS db.not_exists_table ENGINE = Memory AS SELECT * FROM db.exists_table

2、删除表

DROP TABLE [IF EXISTS] [db_name.]table_name

3、基本操作

目前只有 MergeTree、Merge 和 Distributed 这三类表引擎支持 ALTER 查询 

①追加新字段

ALTER TABLE table_name ADD COLUMN [IF NOT EXISTS] 字段名 [类型] [默认值] [插在哪个字段后面]

②修改字段类型或默认值

ALTER TABLE table_name MODIFY COLUMN [IF NOT EXISTS] 字段名 [类型] [默认值]

③修改字段注释

ALTER TABLE table_name COMMENT COLUMN [IF EXISTS] 字段名 'some comment'

④删除已有字段

ALTER TABLE table_name DROP COLUMN [IF EXISTS] name

⑤移动数据表（重命名）

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

RENAME TABLE [db_name1.]table_name1 TO [db_name2.]table_name2, [db_name1.]table_name3 TO [db_name2.]table_name3......

RENAME 可以修改数据表的名称，如果将原始数据库与目标数据库设为不同的名称，那么就可以实现数据表在两个数据库之间移动的效果，并且还可以同时移动多张 ，但是只能在单个节点范围内移动，即同一台服务器，而不是集群中的其他节点

⑥清空表

TRUNCATE TABLE [IF EXISTS] [db_name.]table_name

三、默认值

表字段支持三种默认值表达式的定义方法，分别是 DEFAULT、MATERIALIZED 和 ALIAS，有默认值且没有明确定义数据类型的以默认值为主，有明确数据类型的以定义的数据类型为主，如下：

CREATE TABLE table_name (    id String,    col1 DEFAULT 100,    col2 String DEFAULT col1
) ENGINE=Memory

其中 col1 字段根据默认值被推断为 UInt8；而 col2 字段由于同时定义了数据类型和默认值，所以它最终的数据类型来自明确定义的 String。

1、默认值三种定义方法之间的不同：

• 1）数据写入：在数据写入时，只有 DEFAULT 类型的字段可以出现在 INSERT 语句中，而 MATERIALIZED 和 ALIAS 都不能被显式赋值，它们只能依靠计算取值。例如试图为 MATERIALIZED 类型的字段写入数据，将会得到如下的错误。

DB::Exception: Cannot insert column URL,because it is MATERIALIZED column..

• 2）数据查询：在数据查询时，只有 DEFAULT 类型的字段可以通过 SELECT * 返回，而 MATERIALIZED 和 ALIAS 类型的字段不会出现在 SELECT * 查询的返回结果集中。
• 3）数据存储：在数据存储时，只有 DEFAULT 和 MATERIALIZED 类型的字段才支持持久化。如果使用的表引擎支持物理存储（例如 TinyLog 表引擎)，那么这些列字段将会拥有物理存储。而 ALIAS 类型的字段不支持持久化，它的取值总是需要依靠计算产生，数据不会落到磁盘。

2、可以使用 ALTER 语句修改默认值，例如:

ALTER TABLE [db_name.]table_name MODIFY COLOMN col_name DEFAUET value

修改动作并不会影响数据表内先前已经存在的数据，但是默认值的修改有诸多限制，例如在 MergeTree 表引擎中，它的主键字段是无法被修改的；而某些表引擎则完全不支持修改（例如 TinyLog）。

四、临时表

创建临时表的方法是在普通表的基础之上添加 TEMPORARY 关键字

相比普通表而言，临时表有如下两点特殊之处：

• 它的生命周期是会话绑定的，所以它只支持 Memory 表引擎，如果会话结束，数据表就会被销毁;
• 临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数;
• 临时表的优先级是大于普通表的。当临时表和普通表表名称相同的时候，会优先读取临时表的数据

 五、分区表

不是所有的表引擎都支持分区，目前只有合并树（mergeTree）家族系列的表引擎才支持数据分区

1、创建分区

创建方式如下  案例：将日期转化成了年月分区

CREATE TABLE partition_v1 (ID String,URL String,EventDate Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY ID

写入数据

INSERT INTO partition_v1 VALUES ('a1', 'www.a1.com', '2019-05-01'), ('a2', 'www.a2.com', '2019-06-02')

通过system.parts系统表查询数据表的分区状态

SELECT table, partition, path FROM system.parts WHERE table = 'partition_v1'

 可以看到建立了两个分区，且每个分区对应一个独立的文件目录，所以当查询时过滤分区可以直接跳过不满足条件的分区

2、删除指定分区

ALTER TABLE table_name DROP PARTITION partition_expr
案例如下：
ALTER TABLE partition_v1 DROP PARTITION 201906

3、复制分区数据

 ClickHouse 支持将 A 表的分区数据复制到 B 表，语法如下

ALTER TABLE B REPLACE PARTITION partition_expr FROM A
案例如下：
假设有一个数据表 partition_v2，并且与之前 partition_v1 的分区键和表结构完全相同，那么如果想将 partition_v1 中 5 月份的数据导入到 partition_v2中，就可以这么做
ALTER TABLE partition_v2 REPLACE PARTITION 201905 FROM partition_v1

不过需要注意的是，并不是任意数据表之间都能够相互复制，它们还需要满足两个前提条件：

• 两张表需要拥有相同的分区键;
• 它们的表结构完全相同;

4、重置分区数据

如果数据表某一列的数据有误，需要将其重置为默认值，此时可以使用下面的语句实现:

ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr

首先如果声明了默认值表达式，那么以表达式为准；否则以相应数据类型的默认值为准，比如 String 类型的默认值就是空字符串。 

5、装载与卸载分区

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

ALTER TABLE table_name DETACH PARTITION partition_expr

假设有一个分区表 partition_v3，里面有很多月的数据，那么执行下面的语句就可以将该表中整个 8 月份的分区卸载。

ALTER TABLE partition_v3 DETACH PARTITION 201908

此时再次查询这张表，会发现其中 2019 年 8 月份的数据已经没有了。而进入 partition_v3 的磁盘目录，则可以看到被卸载的分区目录已经被移动到了 detached 目录中。

记住，一旦分区被移动到了 detached 子日录，就代表它已经脱离了 ClickHouse 的管理，ClickHouse 并不会主动清理这些文件。这此分区文件会一直存在，除非我们主动删除或者使用 ATTACH 语句重新装载它们。装载某个分区的完整语法如下所示：

ALTER TABLE table_name ATTACH PARTITION partition_expr

再次执行下面的语句，就可以将刚才已被卸载的 201908 分区重新装载回去:

ALTER TABLE partition_v3 ATTACH PARTITION 201908

六、视图

clickhouse有普通和物化视图两种，物化视图有独立的存储，普通的和关系型数据库的视图类似，只是一层简单的查询代理，创建普通视图的语法如下

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT...

物化视图需要指定表引擎，数据保存形式由表引擎决定，创建语法如下

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

 案例如下

-- 物化视图本质上可以看成是一张特殊的数据表，在创建的时候也需要指定引擎
CREATE MATERIALIZED VIEW girls_view_1 ENGINE=TinyLog()
AS SELECT id, name, age FROM girls;

注意：

1. 因为物化视图是可以存储数据的，所以当girls表被写入数据时，物化视图也会同步更新 
2. 物化视图只会同步创建后更新的数据，如果想在创建的时候就把数据同步过来需要POPULATE 使用案例如下

-- 只需要在 AS SELECT 的前面加上 POPULATE 即可
-- 此时表 girls 的数据，更准确的说是 SELECT 查询得到的结果集才会进入物化视图中
CREATE MATERIALIZED VIEW girls_view_1 ENGINE=TinyLog()
POPULATE AS SELECT id, name, age FROM girls;

视图可以用show table  和drop table命令来展示和删除，且视图名不能与表名重复

七、分布式DDL执行

将一条普通的 DDL 语句转换成分布式执行十分简单，只需加上 ON CLUSTER cluster_name 声明即可。例如，执行下面的语句后将会对 ch_cluster 集群内的所有节点广播这条 DDL 语句。

CREATE TABLE partition_v4 ON CLUSTER ch_cluster(ID String,URL String,EventDate Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY ID