Clickhouse20.1.6.13如何定义数据表及表的基本操作

概要

我们开始介绍DDL操作及定义数据的方法。DDL查询提供了数据表的创建、修改和删除操作，是最常用的功能之一。

1.数据库

数据库起到了命名空间的作用，可以有效规避命名冲突的问题，也为后续的数据隔离提供了支撑。任何一张数据表，都必须归属在某个数据库之下。创建数据库的完整语法如下所示：

CREATE DATABASE IF NOT EXISTS db_name [ENGINE=engine]

其中，IF NOT EXISTS表示如果已经存在一个同名的数据库，则会忽略后续的创建过程；[ENGINE = engine]表示数据库所使用的引擎类型。
数据库目前一共支持5种引擎，如下所示：
❑ Ordinary：默认引擎，在绝大多数情况下我们都会使用默认引擎，使用时无须刻意声明。在此数据库下可以使用任意类型的表引擎。
❑ Dictionary：字典引擎，此类数据库会自动为所有数据字典创建它们的数据表。
❑ Memory：内存引擎，用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除。
❑ Lazy：日志引擎，此类数据库下只能使用Log系列的表引擎。
❑ MySQL:MySQL引擎，此类数据库下会自动拉取远端MySQL中的数据，并为它们创建MySQL表引擎的数据表。
在绝大多数情况下都只需使用默认的数据库引擎。例如执行下面的语句，即能够创建属于我们的第一个数据库：

# 创建数据库
create database db_test;
# 查看所有数据库
show databases;

默认数据库的实质是物理磁盘上的一个文件目录，所以在语句执行之后，ClickHouse便会在安装路径下创建db_test数据库的文件目录(暂且为空目录)

与此同时，在metadata路径下也会一同创建用于恢复数据库的DB_TEST.sql文件：

[root@ck1 metadata]# cat db_test.sql 
ATTACH DATABASE _ UUID 'd4ce8ab2-383c-4296-8c6a-256ff8c84bda'
ENGINE = Atomic

使用USE可以实现在多个数据库之间进行切换，而通过SHOW TABLES查询可以查看当前数据库的数据表列表。删除一个数据库，则需要用到下面的DROP语句：

DROP DATABASE IF EXISTS db_name;

2.数据表

ClickHouse数据表的定义语法，是在标准SQL的基础之上建立的，所以熟悉数据库的读者们在看到接下来的语法时，应该会感到很熟悉。ClickHouse目前提供了三种最基本的建表方法，其中，第一种是常规定义方法，它的完整语法如下所示：

CREATE TABLE [IF NOT EXISTS] [db_name].TABLE_NAME(
     name1 type [DEFAULT|MATERIALIZED|ALIAS expr],
     name2 type [DEFAULT|MATERIALIZED|ALIAS expr],
     ...
)ENGINE=engine

使用[db_name.]参数可以为数据表指定数据库，如果不指定此参数，则默认会使用default数据库。例如执行下面的语句：

# 切换数据库
use db_test;
# 创建表
CREATE TABLE hist_v1
(
    `title` String,
    `url` String,
    `eventime` DateTime
)
ENGINE = Memory

上述语句将会在db_test数据库下创建一张内存表。注意末尾的ENGINE参数，它被用于指定数据表的引擎。表引擎决定了数据表的特性，也决定了数据将会被如何存储及加载。例如示例中使用的Memory表引擎，是ClickHouse最简单的表引擎，数据只会被保存在内存中，在服务重启时数据会丢失。
第二种定义方法是复制其他表的结构，具体语法如下所示：

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

这种方式支持在不同的数据库之间复制表结构，例如下面的语句：

# 创建新的数据库
CREATE DATABASE db_test_new
# 创建数据表
CREATE TABLE IF NOT EXISTS db_test_new.hist_v1 AS db_test.hist_v1
ENGINE = TinyLog

上述语句将会把db_test.hist_v1的表结构原样复制到db_test_new.hist_v1，并且ENGINE表引擎可以与原表不同。

第三种定义方法是通过SELECT子句的形式创建，它的完整语法如下：

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

在这种方式下，不仅会根据SELECT子句建立相应的表结构，同时还会将SELECT子句查询的数据顺带写入，例如执行下面的语句：

CREATE TABLE IF NOT EXISTS hist_v1_1
ENGINE = Memory AS
SELECT
    title,
    eventime
FROM hist_v1

上述语句会将SELECT * FROM hits_v1的查询结果一并写入数据表。

ClickHouse和大多数数据库一样，使用DESC查询可以返回数据表的定义结构:

DESCRIBE TABLE hist_v1

如果想删除一张数据表，则可以使用下面的DROP语句：

DROP TABLE [IF EXISTS] [db_name.]table_name

3. 默认值表达式

表字段支持三种默认值表达式的定义方法，分别是DEFAULT、MATERIALIZED和ALIAS。无论使用哪种形式，表字段一旦被定义了默认值，它便不再强制要求定义数据类型，因为ClickHouse会根据默认值进行类型推断。如果同时对表字段定义了数据类型和默认值表达式，则以明确定义的数据类型为主，例如下面的例子：

CREATE TABLE IF NOT EXISTS dfv_1
(
    `id` String,
    `c1` DEFAULT 1000,
    `c2` String DEFAULT c1
)
ENGINE = TinyLog

c1字段没有定义数据类型，默认值为整型1000; c2字段定义了数据类型和默认值，且默认值等于c1，现在写入测试数据：

insert into dfv_1(id) values('A1001');
SELECT * FROM dfv_1

Query id: 8a3250d3-d6b9-4288-a5e7-8f341e0013df

┌─id────┬───c1─┬─c2───┐
│ A1001 │ 1000 │ 1000 │
└───────┴──────┴──────┘

1 rows in set. Elapsed: 0.004 sec.

在写入之后执行以下查询:

SELECT
    c1,
    c2,
    toTypeName(c1),
    toTypeName(c2)
FROM dfv_1

Query id: 8e0a2e83-9e29-4b90-8a23-b46738d9e8fa

┌───c1─┬─c2───┬─toTypeName(c1)─┬─toTypeName(c2)─┐
│ 1000 │ 1000 │ UInt16         │ String         │
└──────┴──────┴────────────────┴────────────────┘

1 rows in set. Elapsed: 0.005 sec. 

由查询结果可以验证，默认值的优先级符合我们的预期，其中c1字段根据默认值被推断为UInt16；而c2字段由于同时定义了数据类型和默认值，所以它最终的数据类型来自明确定义的String。

---

默认值表达式的三种定义方法之间也存在着不同之处，可以从如下三个方面进行比较。
（1）数据写入：在数据写入时，只有DEFAULT类型的字段可以出现在INSERT语句中。而MATERIALIZED和ALIAS都不能被显式赋值，它们只能依靠计算取值。
（2）数据查询：在数据查询时，只有DEFAULT类型的字段可以通过SELECT *返回。而MATERIALIZED和ALIAS类型的字段不会出现在SELECT *查询的返回结果集中。
（3）数据存储：在数据存储时，只有DEFAULT和MATERIALIZED类型的字段才支持持久化。如果使用的表引擎支持物理存储（例如TinyLog表引擎），那么这些列字段将会拥有物理存储。而ALIAS类型的字段不支持持久化，它的取值总是需要依靠计算产生，数据不会落到磁盘。
可以使用ALTER语句修改默认值，例如：

ALTER TABLE [db_name.]table_name  MODIFY COLUMN column_name DEFAULT value

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

4. 临时表

ClickHouse也有临时表的概念，创建临时表的方法是在普通表的基础之上添加TEMPORARY关键字，它的完整语法如下所示：

CREATE TEMPORARY TABLE [IF NOT EXISTS] [db_name].TABLE_NAME(
     name1 type [DEFAULT|MATERIALIZED|ALIAS expr],
     name2 type [DEFAULT|MATERIALIZED|ALIAS expr],
     ...
)

相比普通表而言，临时表有如下两点特殊之处：
❑ 它的生命周期是会话绑定的，所以它只支持Memory表引擎，如果会话结束，数据表就会被销毁；
❑ 临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数。

临时表的优先级是大于普通表的。当两张数据表名称相同的时候，会优先读取临时表的数据。

5. 分区表

数据分区（partition）和数据分片（shard）是完全不同的两个概念。数据分区是针对本地数据而言的，是数据的一种纵向切分。而数据分片是数据的一种横向切分。数据分区对于一款OLAP数据库而言意义非凡：借助数据分区，在后续的查询过程中能够跳过不必要的数据目录，从而提升查询的性能。合理地利用分区特性，还可以变相实现数据的更新操作，因为数据分区支持删除、替换和重置操作。假设数据表按照月份分区，那么数据就可以按月份的粒度被替换更新。
分区虽好，但不是所有的表引擎都可以使用这项特性，目前只有合并树（MergeTree）家族系列的表引擎才支持数据分区。接下来通过一个简单的例子演示分区表的使用方法。首先由PARTITION BY指定分区键，例如下面的数据表partition_v1使用了日期字段作为分区键，并将其格式化为年月的形式：