ClickHouse(06)ClickHouse建表语句DDL详细解析

原创

张飞的猪大数据分享

发布于 2024-03-09 10:17:53

1800

发布于 2024-03-09 10:17:53

文章被收录于专栏：clickhouse入门张飞的猪大数据精选clickhouse入门

TOC

当前服务器上创建表(单节点)

创建新表具有几种种语法形式，具体取决于用例。默认情况下，仅在当前服务器上创建表。分布式DDL查询作为子句实现，该子句另外描述。

语法形式

使用显式架构

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

使用db_name.参数可以为数据表指定数据库，如果不指定此参数，则默认会使用default数据库。

末尾的ENGINE参数，它被用于指定数据表的引擎。表引擎决定了数据表的特性，也决定了数据将会被如何存储及加载。例如示例中使用的Memory表引擎，是ClickHouse最简单的表引擎，数据只会被保存在内存中，在服务重启时数据会丢失。

从相同结构的表复制创建

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

创建与另一个表具有相同结构的表。您可以为表指定其他引擎。如果未指定引擎，则将使用与表相同的引擎。

从表函数创建

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS table_function()

创建与指定表函数的结果相同的表。创建的表也将以与指定的相应表函数相同的方式工作。

什么是表函数？简单来说就是一个可以返回一张表的函数。下面是一个表函数的例子，from后面跟着的就是一个表函数。

CREATE TABLE Orders
ENGINE = MergeTree
ORDER BY OrderID AS
SELECT *
FROM mysql('10.42.134.136:4000', 'databas', 'Orders', 'root', '1234')

目前的表函数有下面几个，这里暂时不展开讲。

从选择查询创建

CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = engine AS SELECT ...

使用引擎创建具有类似于查询结果的结构的表，并使用来自的数据填充该表。创建的时候，还可以显式指定列说明。

使用IF NOT EXISTS，如果表已存在且已指定，则查询不会执行任何操作。

查询中的子句之后可以有其他子句。

分布式集群创建表

ClickHouse支持集群模式，一个集群拥有1到多个节点。CREATE、ALTER、DROP、RENMAE及TRUNCATE这些DDL语句，都支持分布式执行。这意味着，如果在集群中任意一个节点上执行DDL语句，那么集群中的每个节点都会以相同的顺序执行相同的语句。这项特性意义非凡，它就如同批处理命令一样，省去了需要依次去单个节点执行DDL的烦恼。

将一条普通的DDL语句转换成分布式执行十分简单，只需加上ON CLUSTER cluster_name声明即可。

例如，执行下面的语句后将会对ch_cluster集群内的所有节点广播这条DDL语句：

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

ch_cluster是集群的名称。

临时表

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

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

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

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

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

分区表

数据分区（partition）和数据分片（shard）是完全不同的两个概念。数据分区是针对本地数据而言的，是数据的一种纵向切分。而数据分片是数据的一种横向切分。借助数据分区，在后续的查询过程中能够跳过不必要的数据目录，从而提升查询的性能。

不是所有的表引擎都可以使用分区，目前只有合并树（MergeTree）家族系列的表引擎才支持数据分区。由PARTITION BY指定分区键，下面的数据表partition_00使用了日期字段作为分区键，并将其格式化为年月的形式：

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

创建表语句关键字解析

空值或非空修饰符

列定义中数据类型后面的修饰符可以指定允许或不允许其值为Null。

CREATE TABLE Orders
(

    `order_id` String,
    `created_at` Nullable(DateTime),
    `updated_at` Nullable(DateTime)
)
ENGINE = MergeTree
ORDER BY (order_id)
SETTINGS index_granularity = 8192

上面的例子中created_at和updated_at可以插入一个NULL值，反之不可以。

默认值表达式

[DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1]

表字段支持四种默认值表达式的定义方法，分别是DEFAULT、MATERIALIZED、EPHEMERAL和ALIAS。无论使用哪种形式，表字段一旦被定义了默认值，它便不再强制要求定义数据类型，因为ClickHouse会根据默认值进行类型推断。

例：URLDomain String DEFAULT domain(URL)

默认值表达式的定义方法之间也存在着不同之处，可以从如下三个方面进行比较。

数据写入：在数据写入时，只有DEFAULT类型的字段可以出现在INSERT语句中。而MATERIALIZED、EPHEMERAL和ALIAS都不能被显式赋值，它们只能依靠计算取值。例如试图为MATERIALIZED类型的字段写入数据，将会得到如下的错误。 DB::Exception: Cannot insert column URL, because it is MATERIALIZED column..
数据查询：在数据查询时，只有DEFAULT类型的字段可以通过SELECT *返回。而MATERIALIZED、EPHEMERAL和ALIAS类型的字段不会出现在SELECT *查询的返回结果集中。
数据存储：在数据存储时，只有DEFAULT和MATERIALIZED类型的字段才支持持久化。如果使用的表引擎支持物理存储（例如TinyLog表引擎），那么这些列字段将会拥有物理存储。而ALIAS、EPHEMERAL类型的字段不支持持久化，它的取值总是需要依靠计算产生，数据不会落到磁盘。
EPHEMERAL只能在CREATE语句的默认值中引用。

怎么理解这四种表达式呢？它其实就是列值的四种生成方式。DEFAULT是在插入的时候计算填充，MATERIALIZED和ALIAS是在查询的时候，或者说用到的时候填充，而EPHEMERAL，有点类似于我们在建表的时候，创建一个变量，一个代码块。

一般表达式

DEFAULT expr

正常默认值。如果INSERT查询未指定相应的列，则将通过计算相应的表达式来填充它。

物化表达式

MATERIALIZED expr

物化字段列。这样的字段不能在INSERT语句中指定值插入，因为这样的字段总是通过使用其他字段计算出来的。

临时表达式

EPHEMERAL expr

临时字段列。这样的列不存储在表中，不能被SELECT 查询，但可以在CREATE语句的默认值中引用。

别名表达式

ALIAS expr

字段别名。这样的列根本不存储在表中。其值不能插入到表中，并且在通过SELECT * 查询，不会出现在结果集。如果在查询分析期间扩展了别名，则可以在SELECT中使用它。

主键

您可以在创建表时定义主键。可以通过两种方式指定主键：

-- 内部定义
CREATE TABLE db.table_name
(
    name1 type1, name2 type2, ...,
    PRIMARY KEY(expr1[, expr2,...])]
)
ENGINE = engine;

-- 外部定义
CREATE TABLE db.table_name
(
    name1 type1, name2 type2, ...
)
ENGINE = engine
PRIMARY KEY(expr1[, expr2,...]);

警告:不能在一个查询中以两种方式组合。

约束

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    ...
    CONSTRAINT constraint_name_1 CHECK boolean_expr_1,
    ...
) ENGINE = engine

boolean_expr_1可以通过任何布尔表达式。如果为表定义了约束，则将针对查询中的每一行检查每个约束。如果不满足任何约束，服务器将引发包含约束名称和检查表达式的异常。INSERT添加大量约束可能会对大型查询的性能产生负面影响。

数据TTL

TTL即Time To Live，表达式正常为：TTL expr1。只能为合并树族表指定。

它表示数据的存活时间。在MergeTree中，可以为某个列字段或整张表设置TTL。当时间到达时，如果是列字段级别的TTL，则会删除这一列的数据；如果是表级别的TTL，则会删除整张表的数据；如果同时设置了列级别和表级别的TTL，则会以先到期的那个为主。

无论是列级别还是表级别的TTL，都需要依托某个DateTime或Date类型的字段，通过对这个时间字段的INTERVAL操作，来表述TTL的过期时间。

如下面的例子。

-- 表示数据的存活时间是time_col时间的3天之后。
TTL time_col + INTERVAL 3 DAY 上述

-- 表示数据的存活时间是time_col时间的1月之后
TTL time_col + INTERVAL 1 MONTH。

-- INTERVAL完整的操作包括SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER和YEAR。

列级别TTL

CREATE TABLE ttl_table_v1(
    id String,
    create_time DateTime,
    code String TTL create_time + INTERVAL 10 SECOND,
    type UInt8 TTL create_time + INTERVAL 10 SECOND
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id

表级别TTL

CREATE TABLE ttl_table_v2(
    id String,
    create_time DateTime,
    code String TTL create_time + INTERVAL 1 MINUTE,
    type UInt8
)ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY create_time
TTL create_time + INTERVAL 1 DAY

clickhouse压缩与编码

列压缩

我们可以每个单独列定义压缩方法,这样可以减少数据存储的空间。可以指定编解码器以引用默认压缩，这可能取决于运行时中的不同设置（和数据属性）

CREATE TABLE codec_example
(
    dt Date CODEC(ZSTD),
    ts DateTime CODEC(LZ4HC),
    float_value Float32 CODEC(NONE),
    double_value Float64 CODEC(LZ4HC(9)),
    value Float32 CODEC(Delta, ZSTD)
)
ENGINE = <Engine>
...

下表引擎支持压缩：