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

2023年 12月 11日 1602点热度 1人点赞 0条评论

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

创建新表具有几种种语法形式,具体取决于用例。默认情况下,仅在当前服务器上创建表。分布式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') 

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

ClickHouse表函数

从选择查询创建

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)

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

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

下表引擎支持压缩:

  • MergeTree family:支持列压缩编解码器,并通过压缩设置选择默认压缩方法。
  • Log family:默认情况下使用压缩方法,并支持列压缩编解码器lz4。
  • Set:仅支持默认压缩。
  • Join:仅支持默认压缩。

目前clickhouse支持的压缩算法

  1. 通用编码

* None:无压缩
* LZ4:默认的压缩算法,缺省值也是使用默认的压缩算法
* LZ4HC[(level)]:z4高压缩率压缩算法版本, level默认值为9,支持[1~12],推荐选用[4~9]
* ZSTD[(level)]:zstd压缩算法,level默认值为1,支持[1~22]

  1. 特殊编码

* LowCardinality:枚举值小于1w的字符串
* Delta:时间序列类型的数据,不会对数据进行压缩
* T64:比较适合Int类型数据
* DoubleDelta:适用缓慢变化的序列:比如时间序列,对于递增序列效果很好
* Gorilla:使用缓慢变化的数值类型

特殊编码与通用的压缩算法相比,区别在于,通用的LZ4和ZSTD压缩算法是普适行的,不关心数据的分布特点,而特殊编码类型对于特定场景下的数据会有更好的压缩效果。

资料分享

ClickHouse经典中文文档分享

系列文章

张飞的猪

选择远方,风雨兼程,专注于web3.0及其前沿的技术。

文章评论