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

梦里梦外; 2023-01-17 12:58 23阅读 0赞

# 概要 #

我们开始介绍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;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI2MzczNTg_size_16_color_FFFFFF_t_70]  
默认数据库的实质是物理磁盘上的一个文件目录，所以在语句执行之后，ClickHouse便会在安装路径下创建db\_test数据库的文件目录(暂且为空目录)  
![在这里插入图片描述][20210429161109769.png]  
与此同时，在metadata路径下也会一同创建用于恢复数据库的DB\_TEST.sql文件：  
![在这里插入图片描述][20210429161320308.png]

[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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI2MzczNTg_size_16_color_FFFFFF_t_70 1]  
上述语句将会在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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI2MzczNTg_size_16_color_FFFFFF_t_70 2]  
上述语句将会把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

![在这里插入图片描述][20210429164850232.png]  
上述语句会将SELECT \* FROM hits\_v1的查询结果一并写入数据表。

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

DESCRIBE TABLE hist_v1

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI2MzczNTg_size_16_color_FFFFFF_t_70 3]

如果想删除一张数据表，则可以使用下面的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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI2MzczNTg_size_16_color_FFFFFF_t_70 4]  
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）:  
![在这里插入图片描述][2021042917080825.png]

## 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使用了日期字段作为分区键，并将其格式化为年月的形式：

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI2MzczNTg_size_16_color_FFFFFF_t_70]: /images/20221021/41f8e88f52d14e6db8c00a8356ea46e7.png
[20210429161109769.png]: /images/20221021/b253315d9a314a4fa8e04d59005f197c.png
[20210429161320308.png]: /images/20221021/7c55ab009d0e4caab02ae1847dfe9fcb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI2MzczNTg_size_16_color_FFFFFF_t_70 1]: /images/20221021/bb5e375d04964e9da82d4d9b305041d8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI2MzczNTg_size_16_color_FFFFFF_t_70 2]: /images/20221021/0e4774857af845f3963da47f5a59f995.png
[20210429164850232.png]: /images/20221021/95261273fddc4c46a006e9d898d110cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI2MzczNTg_size_16_color_FFFFFF_t_70 3]: /images/20221021/111bb6fa0ee5425c90dd1dc924eca6ad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI2MzczNTg_size_16_color_FFFFFF_t_70 4]: /images/20221021/483765f4095f4e63931ac292e9c7f709.png
[2021042917080825.png]: /images/20221021/01b3600aadbf4f8a9e2bf6d23c2ba7ab.png