跳到主要内容
跳到主要内容

CREATE TABLE

创建新表。此查询可以根据用例具有各种语法形式。

默认情况下,表只在当前服务器上创建。分布式 DDL 查询通过 ON CLUSTER 子句实现,具体内容 单独描述

语法形式

使用显式模式

db 数据库中创建名为 table_name 的表,或者如果未设置 db,则在当前数据库中创建,表的结构在括号中指定,以及 engine 引擎。 表的结构是列描述、二级索引和约束的列表。如果引擎支持 主键,则将其作为表引擎的参数指示。

列描述在最简单的情况下为 name type。示例:RegionID UInt32

也可以为默认值定义表达式(见下文)。

如有必要,可以指定主键,包含一个或多个键表达式。

可以为列和表添加注释。

使用与其他表相似的模式

创建一个具有与另一表相同结构的表。可以为该表指定不同的引擎。如果未指定引擎,则将使用 db2.name2 表的相同引擎。

使用从其他表中克隆的模式和数据

创建一个具有与另一表相同结构的表。可以为该表指定不同的引擎。如果未指定引擎,则将使用 db2.name2 表的相同引擎。创建新表后,将所有来自 db2.name2 的分区附加到它。换句话说,db2.name2 的数据在创建时克隆到 db.table_name。此查询等同于以下内容:

从表函数

创建一个与指定的 表函数 结果相同的表。创建的表在工作原理上也与指定的相应表函数相同。

从 SELECT 查询

创建一个具有与 SELECT 查询结果类似的结构的表,使用 engine 引擎,并填充来自 SELECT 的数据。还可以显式指定列描述。

如果表已经存在并且指定了 IF NOT EXISTS,则查询不会执行任何操作。

查询中的 ENGINE 子句后可以有其他子句。有关如何创建表的详细文档,请参见 表引擎 的描述。

提示

在 ClickHouse Cloud 中,请将此分为两个步骤:

  1. 创建表结构
  1. 填充表

示例

查询:

结果:

NULL 或 NOT NULL 修饰符

在列定义的数据类型后使用 NULLNOT NULL 修饰符允许或不允许列是 Nullable

如果类型不是 Nullable 并且指定了 NULL,则将被视为 Nullable;如果指定了 NOT NULL,则不是。例如,INT NULLNullable(INT) 是相同的。如果类型为 Nullable,而指定了 NULLNOT NULL 修饰符,将抛出异常。

另见 data_type_default_nullable 设置。

默认值

列描述可以以 DEFAULT exprMATERIALIZED exprALIAS expr 的形式指定默认值表达式。例如:URLDomain String DEFAULT domain(URL)

表达式 expr 是可选的。如果省略,则必须显式指定列类型,对于数字列,默认值将为 0,对于字符串列,默认为 ''(空字符串),对于数组列,默认为 [](空数组),对于日期列,默认为 1970-01-01,或对于可空列,默认为 NULL

默认值列的列类型可以省略,在这种情况下,它将从 expr 的类型推断出。例如,EventDate DEFAULT toDate(EventTime) 的列类型将是日期。

如果同时指定了数据类型和默认值表达式,则会插入一个隐式类型强制转换函数,将表达式转换为指定的类型。例如,Hits UInt32 DEFAULT 0 在内部表示为 Hits UInt32 DEFAULT toUInt32(0)

默认值表达式 expr 可以引用任意表列和常量。ClickHouse 检查表结构的更改不会在表达式计算中引入循环。对于 INSERT,它检查表达式是否可解析——即它们可以从中计算的所有列都已传递。

DEFAULT

DEFAULT expr

正常默认值。如果在 INSERT 查询中未指定此列的值,则它将从 expr 计算出来。

示例:

MATERIALIZED

MATERIALIZED expr

物化表达式。这类列的值在插入行时会根据指定的物化表达式自动计算。插入时无法显式指定值。

此外,这类默认值列不包含在 SELECT * 的结果中。这是为了保持 SELECT * 的结果可以通过 INSERT 重新插入回表中的不变性。这种行为可以通过设置 asterisk_include_materialized_columns 来禁用。

示例:

EPHEMERAL

EPHEMERAL [expr]

临时列。这类列不存储在表中,因此无法从中进行 SELECT。临时列的唯一目的是为其他列构建默认值表达式。

未显式指定列时的插入将跳过此类列。这是为了保持 SELECT * 的结果可以通过 INSERT 重新插入回表中的不变性。

示例:

ALIAS

ALIAS expr

计算列(同义词)。此类列不存储在表中,因此无法向其中 INSERT 值。

当 SELECT 查询明确引用此类列时,值将在查询时间根据 expr 进行计算。默认情况下,SELECT * 排除 ALIAS 列。可以通过设置 asterisk_include_alias_columns 禁用此行为。

使用 ALTER 查询添加新列时,旧数据将不写入这些列。相反,在读取没有新列值的旧数据时,默认情况下将动态计算表达式。但是,如果运行表达式需要不同列,而这些列在查询中未指示,这些列还会被读取,但仅适用于需要的数据块。

如果向表中添加新列,但后来更改其默认表达式,则用于旧数据的值将会改变(对于未在磁盘上存储值的数据)。请注意,在运行后台合并时,缺少合并部分中一个列的数据将写入合并部分。

无法为嵌套数据结构中的元素设置默认值。

主键

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

  • 在列列表内
  • 在列列表外
提示

不能在一个查询中组合两种方式。

约束

除了列描述外,还可以定义约束:

CONSTRAINT

boolean_expr_1 可以是任何布尔表达式。如果为表定义了约束,将为每一行在 INSERT 查询中检查它们。如果未满足任何约束——服务器将引发带有约束名称和检查表达式的异常。

添加大量约束可能会对大型 INSERT 查询的性能产生负面影响。

ASSUME

ASSUME 子句用于在假定为真的表上定义 CONSTRAINT。此约束然后可以被优化器用来增强 SQL 查询性能。

以下是使用 ASSUME CONSTRAINT 创建 users_a 表的示例:

在这里,使用 ASSUME CONSTRAINT 断言 length(name) 函数总是等于 name_len 列的值。这意味着每当在查询中调用 length(name) 时,ClickHouse 可以将其替换为 name_len,因为它避免调用 length() 函数,这应该更快。

然后,当执行查询 SELECT name FROM users_a WHERE length(name) < 5; 时,ClickHouse 可以优化为 SELECT name FROM users_a WHERE name_len < 5; 这是由于 ASSUME CONSTRAINT。这可以使查询运行得更快,因为它避免了计算每一行 name 的长度。

ASSUME CONSTRAINT 不强制执行约束,它只通知优化器该约束是正确的。如果约束实际上不成立,则查询结果可能不正确。因此,只有当你确定约束是正确时,才应使用 ASSUME CONSTRAINT

TTL 表达式

定义值的存储时间。只能为 MergeTree 家族表指定。有关详细描述,请参见 列和表的 TTL

列压缩编码器

默认情况下,ClickHouse 在自管理版本中应用 lz4 压缩,在 ClickHouse Cloud 中应用 zstd

对于 MergeTree 引擎家族,可以在服务器配置的 compression 部分更改默认压缩方法。

还可以在 CREATE TABLE 查询中为每一列定义压缩方法。

可以指定 Default 编解码器以引用可能在运行时依赖于不同设置(和数据属性)的默认压缩。 示例:value UInt64 CODEC(Default) — 等同于缺少编解码器规范。

还可以从列中删除当前 CODEC,并使用来自 config.xml 的默认压缩:

编解码器可以结合在一起使用,例如,CODEC(Delta, Default)

提示

不能使用外部工具(如 lz4)解压 ClickHouse 数据库文件。相反,请使用特殊的 clickhouse-compressor 工具。

压缩支持以下表引擎:

  • MergeTree 家族。支持列压缩编码器并通过 compression 设置选择默认压缩方法。
  • Log 家族。默认使用 lz4 压缩方法,并支持列压缩编码器。
  • Set。仅支持默认压缩。
  • Join。仅支持默认压缩。

ClickHouse 支持通用编解码器和专用编解码器。

通用编解码器

NONE

NONE — 不压缩。

LZ4

LZ4 — 默认使用的无损 数据压缩算法。应用 LZ4 快速压缩。

LZ4HC

LZ4HC[(level)] — 可配置等级的 LZ4 HC(高压缩)算法。默认级别:9。设置 level <= 0 应用默认级别。可能的级别:[1, 12]。推荐级别范围:[4, 9]。

ZSTD

ZSTD[(level)] — 可配置 levelZSTD 压缩算法。可能的级别:[1, 22]。默认级别:1。

较高的压缩级别适用于不对称的场景,例如压缩一次,随后多次解压。更高的级别意味着更好的压缩和更高的 CPU 使用率。

ZSTD_QAT

Not supported in ClickHouse Cloud

ZSTD_QAT[(level)] — 可配置级别的 ZSTD 压缩算法,通过 Intel® QATlibIntel® QAT ZSTD Plugin 实现。可能的级别:[1, 12]。默认级别:1。推荐级别范围:[6, 12]。有一些限制适用:

  • ZSTD_QAT 默认情况下是禁用的,只能在启用配置设置 enable_zstd_qat_codec 后使用。
  • 对于压缩,ZSTD_QAT 尝试使用 Intel® QAT 卸载设备(QuickAssist Technology)。如果未找到这样的设备,它将回退到软件中的 ZSTD 压缩。
  • 解压总是以软件方式执行。

DEFLATE_QPL

Not supported in ClickHouse Cloud

DEFLATE_QPLDeflate 压缩算法 由 Intel® Query Processing Library 实现。有一些限制适用:

  • DEFLATE_QPL 默认情况下是禁用的,只能在启用配置设置 enable_deflate_qpl_codec 后使用。
  • DEFLATE_QPL 需要使用 SSE 4.2 指令编译的 ClickHouse 版本(默认情况下是这种情况)。有关更多详细信息,请参阅 使用 DEFLATE_QPL 构建 Clickhouse
  • DEFLATE_QPL 最适合于系统配备 Intel® IAA(内存分析加速器)卸载设备。有关更多详细信息,请参阅 加速器配置使用 DEFLATE_QPL 进行基准测试
  • DEFLATE_QPL 压缩的数据只能在与 SSE 4.2 启用的 ClickHouse 节点之间转换。

专用编解码器

这些编解码器旨在通过利用数据的特定特征使压缩更有效。其中一些编解码器本身不压缩数据,而是对数据进行预处理,使得使用通用编解码器的第二阶段压缩可以实现更高的数据压缩率。

Delta

Delta(delta_bytes) — 一种压缩方法,其中原始值被其两个相邻值间的差替换,除了保持不变的第一个值。最多使用 delta_bytes 来存储增量值,因此 delta_bytes 是原始值的最大大小。可能的 delta_bytes 值:1、2、4、8。默认值为 sizeof(type),如果等于 1、2、4 或 8。在其他情况下,它为 1。Delta 是一种数据准备编解码器,即它不能单独使用。

DoubleDelta

DoubleDelta(bytes_size) — 计算增量的增量并以紧凑的二进制形式写入。可能的 bytes_size 值:1、2、4、8,默认值为 sizeof(type),如果等于 1、2、4 或 8。在所有其他情况下,它为 1。对于具有恒定步幅的单调序列(如时间序列数据),可以获取最佳压缩率。可以与任何固定宽度类型一起使用。实现了 Gorilla TSDB 中使用的算法,并扩展以支持 64 位类型。对于 32 位增量,使用了 1 位额外的位:5 位前缀而不是 4 位前缀。有关更多信息,请参见 Gorilla: A Fast, Scalable, In-Memory Time Series Database 中的压缩时间戳。DoubleDelta 是一种数据准备编解码器,即它不能单独使用。

GCD

GCD() - 计算列中值的最大公约数 (GCD),然后将每个值除以 GCD。可以用于整数、十进制和日期/时间列。此编解码器非常适合于值以 GCD 倍数变化(增加或减少)的列,例如 24、28、16、24、8、24(GCD = 4)。GCD 是一种数据准备编解码器,即它不能单独使用。

Gorilla

Gorilla(bytes_size) — 计算当前浮点值与前一个浮点值之间的异或,并以紧凑的二进制形式写入。连续值之间的差异越小,即系列值变化越慢,压缩率越好。实现了 Gorilla TSDB 中使用的算法,并扩展以支持 64 位类型。可能的 bytes_size 值:1、2、4、8,默认值为 sizeof(type),如果等于 1、2、4 或 8。在所有其他情况下,它为 1。有关更多信息,请参见 Gorilla: A Fast, Scalable, In-Memory Time Series Database 第 4.1 节。

FPC

FPC(level, float_size) - 重复预测序列中的下一个浮点值,使用两个预测器中较好的一个,然后将实际值与预测值进行异或,并压缩结果的前导零。与 Gorilla 类似,这在存储缓慢变化的浮点值序列时非常有效。对于 64 位值(双精度),FPC 比 Gorilla 更快,对于 32 位值,效果可能有所不同。可能的 level 值:1-28,默认值为 12。可能的 float_size 值:4、8,默认值为 sizeof(type),如果类型为 Float。在其他情况下,它为 4。有关算法的详细描述,请参见 High Throughput Compression of Double-Precision Floating-Point Data

T64

T64 — 一种压缩方法,裁剪整数数据类型(包括 EnumDateDateTime)中值的未使用高位。在其算法的每一步中,编解码器选择 64 个值的块,将其放入 64x64 位矩阵中,转置,裁剪值的未使用位,然后将其作为序列返回。未使用的位是数据部分中的最大值和最小值之间不差异的位。

DoubleDeltaGorilla 编解码器在 Gorilla TSDB 中用作其压缩算法的组成部分。Gorilla 方法在存在一系列缓慢变化的值及其时间戳的场景中有效。 DoubleDelta 编解码器有效压缩时间戳,而 Gorilla 编解码器有效压缩值。例如,要获取有效存储的表,可以以以下配置创建它:

加密编解码器

这些编解码器实际上不压缩数据,而是对磁盘上的数据进行加密。仅在通过 encryption 设置指定加密密钥时可用。请注意,加密仅在编解码器管道的末尾是有意义的,因为加密数据通常无法以任何有意义的方式进行压缩。

加密编解码器:

AES_128_GCM_SIV

CODEC('AES-128-GCM-SIV') — 使用 AES-128 在 RFC 8452 GCM-SIV 模式加密数据。

AES-256-GCM-SIV

CODEC('AES-256-GCM-SIV') — 使用 AES-256 在 GCM-SIV 模式下加密数据。

这些编解码器使用固定的随机数,因此加密是确定性的。这使其兼容于如 ReplicatedMergeTree 之类的去重引擎,但存在一个弱点:当同一数据块被加密两次时,结果密文将完全相同,因此可以读取磁盘的对手可以看到这种相等性(尽管仅仅是相等性,而没有获取其内容)。

备注

大多数引擎,包括 "*MergeTree" 家族,在磁盘上创建索引文件时不会应用编解码器。这意味着明文将出现在磁盘上,如果加密列被索引。

备注

如果你在加密列中执行 SELECT 查询,并提到特定值(如在其 WHERE 子句中),该值可能会出现在 system.query_log 中。你可能要禁用日志记录。

示例

备注

如果需要应用压缩,必须显式指定。否则,仅对数据应用加密。

示例

临时表

备注

请注意,临时表没有复制。因此,无法保证插入临时表的数据在其他副本中可用。临时表的主要使用案例是在单个会话中查询或连接小型外部数据集。

ClickHouse 支持临时表,具有以下特性:

  • 当会话结束时,临时表将消失,包括连接丢失时。
  • 当未指定引擎时,临时表使用内存表引擎,它可以使用除 Replicated 和 KeeperMap 引擎外的任何表引擎。
  • 无法为临时表指定数据库。它是在数据库之外创建的。
  • 无法通过使用 ON CLUSTER 的分布式 DDL 查询在所有集群服务器上创建临时表:此表仅存在于当前会话中。
  • 如果临时表与其他表同名,并且查询指定列名而不指定数据库,则将使用临时表。
  • 对于分布式查询处理,在查询中使用 Memory 引擎的临时表将传递给远程服务器。

要创建临时表,请使用以下语法:

在大多数情况下,不手动创建临时表,而是在使用外部数据进行查询时,或者用于分布式 (GLOBAL) IN。有关更多信息,请参见适当的部分。

可以使用 ENGINE = Memory 的表代替临时表。

REPLACE TABLE

REPLACE 语句允许您以 原子方式 更新表。

备注

该语句支持 AtomicReplicated 数据库引擎,这两者分别是 ClickHouse 和 ClickHouse Cloud 的默认数据库引擎。

通常,如果需要从表中删除某些数据,可以创建新表,并通过 SELECT 语句填充该表,不检索不需要的数据,然后删除旧表并重命名新表。此方法在下面的示例中演示:

除了上述方法外,还可以使用 REPLACE(前提是您使用默认的数据库引擎)来实现相同的结果:

语法

备注

所有 CREATE 语句的语法形式也适用于此语句。调用不存在的表的 REPLACE 将导致错误。

示例

考虑以下表:

我们可以使用 REPLACE 语句清除所有数据:

或者我们可以使用 REPLACE 语句更改表结构:

COMMENT 子句

在创建表时,可以添加注释。

语法

示例

查询:

结果: