当前服务器上创建表(单节点)
创建新表具有几种种语法形式，具体取决于用例。默认情况下，仅在当前服务器上创建表。分布式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)
默认值表达式的定义方法之间也存在着不同之处，可以从如下三个方面进行比较。
- 数据写入：在数据写入时，只有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>
...
下表引擎支持压缩：
- MergeTree family：支持列压缩编解码器，并通过压缩设置选择默认压缩方法。
- Log family：默认情况下使用压缩方法，并支持列压缩编解码器lz4。
- Set：仅支持默认压缩。
- Join：仅支持默认压缩。
目前clickhouse支持的压缩算法
- 通用编码
- None：无压缩
- LZ4：默认的压缩算法,缺省值也是使用默认的压缩算法
- LZ4HC[(level)]：z4高压缩率压缩算法版本, level默认值为9,支持[112],推荐选用[49]
- ZSTD[(level)]：zstd压缩算法，level默认值为1，支持[1~22]
- 特殊编码
- LowCardinality：枚举值小于1w的字符串
- Delta：时间序列类型的数据，不会对数据进行压缩
- T64：比较适合Int类型数据
- DoubleDelta：适用缓慢变化的序列:比如时间序列，对于递增序列效果很好
- Gorilla：使用缓慢变化的数值类型
特殊编码与通用的压缩算法相比，区别在于，通用的LZ4和ZSTD压缩算法是普适行的，不关心数据的分布特点，而特殊编码类型对于特定场景下的数据会有更好的压缩效果。
资料分享
参考文章
- ClickHouse(01)什么是ClickHouse,ClickHouse适用于什么场景
- ClickHouse(02)ClickHouse架构设计介绍概述与ClickHouse数据分片设计
- ClickHouse(03)ClickHouse怎么安装和部署
- ClickHouse(04)如何搭建ClickHouse集群
- ClickHouse(05)ClickHouse数据类型详解
- ClickHouse(06)ClickHouse建表语句DDL详细解析
- ClickHouse(07)ClickHouse数据库引擎解析
- ClickHouse(08)ClickHouse表引擎概况
- ClickHouse(09)ClickHouse合并树MergeTree家族表引擎之MergeTree详细解析
- ClickHouse(10)ClickHouse合并树MergeTree家族表引擎之ReplacingMergeTree详细解析
- ClickHouse(11)ClickHouse合并树MergeTree家族表引擎之SummingMergeTree详细解析
网友评论