CREATE 语句可用于创建数据库、表、视图、字典等数据资源。

语法

CREATE DATABASE [IF NOT EXISTS] db_name 
[ON CLUSTER cluster] 
[ENGINE = engine(...)] 
[COMMENT 'Comment']

参数说明

示例

• 创建数据库 sample_db，使用默认库引擎。

  CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;
  

• 创建数据库 sample_db，使用可选库引擎，当前只支持 Atomic。

  CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster ENGINE=Atomic;
  

语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name (
  name1 [type1] [NULL|NOT NULL] [DEFAULT|ALIAS expr1 | MATERIALIZED expr1] [COMMENT 'string'][compression_codec][TTL expr1],
  name2 [type2] [NULL|NOT NULL] [DEFAULT|ALIAS expr2 | MATERIALIZED expr1] [COMMENT 'string'] [compression_codec] [TTL expr2],
  ...
) 
ENGINE = engine(...)
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[UNIQUE KEY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...]
[COMMENT='COMMENT']

示例

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_table ON CLUSTER sample_cluster;

-- 3. 创建表
CREATE TABLE IF NOT EXISTS sample_db.sample_table ON CLUSTER sample_cluster (
  id UInt32,
  name String DEFAULT '',
  CONSTRAINT constraint1 CHECK id > 10
)
ENGINE = MergeTree
ORDER BY id;

参数说明

table_name, name, type

NULL 或 NOT NULL 修饰符

在列定义中，数据类型之后的 NULL 和 NOT NULL 修饰符允许或不允许该列为可为空 Nullable。
如果数据类型不可为空，并且指定了 NULL，则该列将被视为可空；如果指定了 NOT NULL，则不会。
例如，INT NULL 与 Nullable(INT) 相同。如果数据类型是可为空，并且指定了 NULL 或 NOT NULL 修饰符，则会抛出异常。

默认值（DEFAULT VALUES）

列描述可以指定一个默认值表达式，形式为 DEFAULT expr、MATERIALIZED expr 或 ALIAS expr。示例：URLDomain String DEFAULT domain(URL)。
表达式 expr 是可选的。如果省略表达式，则必须显式指定列类型，并且

• 数值列的默认值为 0；
• 字符串列的默认值为 ' '（空字符串）；
• 数组列的默认值为 [ ]（空数组）；
• 日期列的默认值为 1970-01-01；
• 可空列的默认值为 NULL。

如果省略默认值列的列类型声明，系统将从 expr 的类型推断。例如，列 EventDate DEFAULT toDate(EventTime) 的类型将被推断为 date。
如果同时指定了数据类型和默认值表达式，系统将隐式插入类型转换函数，将表达式结果转换为指定的类型。例如：Hits UInt32 DEFAULT 0 在内部表示为 Hits UInt32 DEFAULT toUInt32(0)。
默认值表达式 expr 可以引用任意表列和常量。ByteHouse 检查表结构的变更不会在表达式计算中引入循环。对于 INSERT 操作，系统将检查表达式是否可解——即计算所需的所有列均已提供。

DEFAULT

普通默认值。如果在 INSERT 查询中未指定此类列的值，则其值将根据 expr 计算得出。

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_table ON CLUSTER sample_cluster;

-- 3. 创建表
CREATE TABLE IF NOT EXISTS sample_db.sample_table ON CLUSTER sample_cluster (
  id UInt64,
  updated_at DateTime DEFAULT now(),
  updated_at_date Date DEFAULT toDate(updated_at)
)
ENGINE = MergeTree
ORDER BY id;

-- 4. 插入数据
INSERT INTO sample_db.sample_table (id) Values (1);

-- 5. 查询数据
SELECT * FROM sample_db.sample_table;
┌─id─┬──────updated_at─┬─updated_at_date─┐
│1     │ 2023-02-24 17:06:46 │2023-02-24 │
└────┴──────────┴─────────────────┘

ALIAS

计算列（同义词）。此类列的值不会存储在表中，也无法向其中插入值。
当 SELECT 查询显式引用此类列时，其值是从 expr 在查询时计算得出的。默认情况下，SELECT * 不包含 ALIAS 列，可通过设置 asterisk_include_alias_columns 禁用此行为。
使用 ALTER 查询添加新列时，这些列的旧数据不会被写入。在读取没有新列值的旧数据时，默认情况下会即时计算表达式。如果运行这些表达式需要查询中未指明的不同列，这些列也会被额外读取，但仅针对需要的那部分数据块。
如果向表中添加新列后，修改其默认表达式，旧数据的取值也会改变（适用于未存储在磁盘上的数据）。请注意，当运行后台合并时，缺少某些列数据的部分会在合并部分中写入这些列的数据。
嵌套数据结构中的元素无法设置默认值。

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_table ON CLUSTER sample_cluster;

-- 3. 创建表
CREATE OR REPLACE TABLE sample_db.sample_table ON CLUSTER sample_cluster (
    id UInt64,
    size_bytes Int64,
    size String ALIAS formatReadableSize(size_bytes)
)
ENGINE = MergeTree
ORDER BY id;

-- 4. 插入测试数据
INSERT INTO sample_db.sample_table VALUES (1, 4678899);

-- 5. 验证数据插入
SELECT id, size_bytes, size FROM sample_db.sample_table;
┌─id─┬─size_bytes─┬─size─────┐
│  1  │    4678899   │ 4.46 MiB    │
└───┴────────┴────────┘

-- 6. 通配符查询演示
SELECT * FROM sample_db.sample_table SETTINGS asterisk_include_alias_columns=1;
┌─id─┬─size_bytes─┬─size───┐
│  1  │    4678899   │ 4.46 MiB │
└───┴────────┴──────┘

MATERIALIZED

物化表达式。此类列的值在插入行时会根据指定的物化表达式自动计算。在INSERT操作期间，不能显式指定这些列的值。
此外，这种类型的默认值列不会包含在 SELECT * 的结果中。这是为了保持 SELECT * 的结果总是可以使用 INSERT 插入回表中的不变性。此行为可以通过设置 asterisk_include_materialized_columns 来禁用。

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_table ON CLUSTER sample_cluster;

-- 3. 创建带有物化列表
CREATE TABLE sample_db.sample_table ON CLUSTER sample_cluster (
    id UInt64,
    updated_at DateTime MATERIALIZED now(),
    updated_at_date Date MATERIALIZED toDate(updated_at)
)
ENGINE = MergeTree
ORDER BY id;

-- 4. 插入数据
INSERT INTO sample_db.sample_table Values (1);

-- 5. 基础查询验证
SELECT * FROM sample_db.sample_table;
┌─id─┐
│1    │
└───┘

-- 6. 显示查询物化列
SELECT id, updated_at, updated_at_date FROM sample_db.sample_table;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│1    │ 2023-02-24 17:08:08         │2023-02-24        │
└───┴─────────────────┴───────────┘

-- 7. 通配符查询演示
SELECT * FROM sample_db.sample_table SETTINGS asterisk_include_materialized_columns=1;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│1    │ 2023-02-24 17:08:08         │2023-02-24        │
└───┴─────────────────┴───────────┘

COMMENT

为列或表添加备注信息。

压缩

compression_codec 字段可以用于配置编解码器，该配置为可选项，默认值为 LZ4。
ByteHouse 支持通用目的编码和特定编码，通用编解码器更像默认编解码器（LZ4, ZTSD）及其修改版本。特定编解码器是为了利用数据的特定特征使压缩更有效而设计的。

通用编码

• NONE：无压缩。
• LZ4：默认值，无损极速压缩算法。
• LZ4HC[(level)]：具有可配置级别的 LZ4HC 高压缩率算法。level 默认值为 9，支持值 [1 ~ 12]，推荐选用 [4 ~ 9]。
• ZSTD[(level)]：具有可配置级别的 ZSTD 压缩算法。level 默认值为 1，支持 [1 ~ 22]。

特定编码算法

• Delta(delta_bytes) : 增量编码，即保留第一位并存储后续每两个值之间差值的算法。默认值为 sizeof(type)， 可选值为 1、2、4 或 8，若设置为其他值则视为 1。

多编解码器

• 使用上述多个编解码器。压缩将根据编解码器声明的顺序进行，解压则按相反的顺序进行。

示例：

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_table ON CLUSTER sample_cluster;

-- 3. 创建带有压缩属性的表
CREATE TABLE sample_db.sample_table ON CLUSTER sample_cluster (
  date Date CODEC(Delta, ZSTD),
  ts DateTime CODEC(LZ4HC),
  float_value Float32 CODEC(NONE),
  double_value Float64 CODEC(LZ4HC(9))
)
ENGINE = MergeTree
PARTITION BY tuple()
ORDER BY date

ENGINE

指定表引擎，配置详情及示例请参见新建数据库/表：基于DDL模板。

设计排序键（ORDER BY）

可以是一组列的元组或任意的表达式。 例如: ORDER BY (OrderID, Date)。
如果不需要排序，可以使用 ORDER BY tuple()，DataPart 将按照数据插入的顺序存储。
语法

[columnExpr] [ASCENDING|ASC|DESCENDING|DESC] [NULLS [FIRST|LAST] [COLLATE STRING_LITERAL]

• [ASCENDING|ASC|DESCENDING|DESC]：确定排序方向。如果未指定方向，则默认为 ASC。
• [NULLS [FIRST|LAST]：确定 NaN 和 NULL 的排序顺序。
  • 默认情况下或使用 NULLS LAST 修饰符时：先是值，然后是 NaN，最后是 NULL。
  • 使用 NULLS FIRST 修饰符时：先是 NULL，然后是 NaN，最后是其他值。
• [COLLATE STRING_LITERAL]：对于按 String 值排序，可以指定排序规则。
  • 排序规则支持 LowCardinality、Nullable、Array 和 Tuple。
  • 使用 COLLATE 时，排序始终不区分大小写。
  • 仅建议在对少量行进行最终排序时使用 COLLATE，因为使用 COLLATE 的排序效率低于按字节的正常排序。

示例 1：按多个 orderExpr 排序

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_orderByTable ON CLUSTER sample_cluster;

-- 3. 创建带有排序键的表
CREATE TABLE IF NOT EXISTS sample_db.sample_orderByTable ON CLUSTER sample_cluster (
  id UInt32,
  val UInt32
)
ENGINE = MergeTree()
ORDER BY id;

-- 4. 插入数据
INSERT INTO sample_db.sample_orderByTable VALUES (1, 5), (1, 15), (2, 5), (2, 15);

-- 5. 查询数据，按多个 orderExpr 排序
SELECT * FROM sample_db.sample_orderByTable ORDER BY id ASC, val DESC;

示例 2：按 NULL FIRST 排序

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_orderByTable ON CLUSTER sample_cluster;

-- 3. 创建带有排序键的表
CREATE TABLE IF NOT EXISTS sample_db.sample_orderByTable ON CLUSTER sample_cluster (
  id UInt32,
  val Nullable(UInt32)
)
ENGINE = MergeTree()
ORDER BY id;

-- 4. 插入数据
INSERT INTO sample_db.sample_orderByTable VALUES (1, 2), (2, NULL), (3, 2), (3, 3), (3, NULL);

-- 5. 查询数据，并按 NULL FIRST 排序
SELECT * FROM sample_db.sample_orderByTable ORDER BY val DESC NULLS FIRST;

示例 3：按 COLLATE 排序

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_orderByTable ON CLUSTER sample_cluster;

-- 3. 创建带有排序键的表
CREATE TABLE IF NOT EXISTS sample_db.sample_orderByTable ON CLUSTER sample_cluster (
  x UInt32,
  s Nullable(String)
)
ENGINE = MergeTree
ORDER BY x;

-- 4. 插入数据
INSERT INTO sample_db.sample_orderByTable VALUES 
  (1, 'bca'), (2, NULL), (3, 'ABC'), 
  (4, '123a'), (5, 'abc'), (6, NULL), (7, 'BCA');

-- 5. 查询数据，并按 COLLATE 排序
SELECT * FROM sample_db.sample_orderByTable ORDER BY s ASC COLLATE 'ru';

设计分区键（PARTITION BY)

分区键定义分区，分区是在一个表中通过指定的规则划分而成的逻辑数据集。可以按任意标准进行分区，如按日期。为了减少需要操作的数据，每个分区都是分开存储的。查询时，ByteHouse 尽量使用这些分区的最小子集。建表时候通过 PARTITION BY expr 子句指定。分区键可以是表中列的任意表达式。例如，指定按月分区，表达式为 toYYYYMM(date)；或者按表达元组，如(toMonday(date), EventType)等。
需要注意，表中分区表达式计算出的取值范围不能太大（推荐不超过一万），太多分区会占用比较大的内存以及带来比较多的 IO 和计算开销。
合理的设计分区键可以极大减少查询时需要扫描的数据量，一般考虑将查询中最常用的条件同时取值范围不超过一万的列设计为分区键（如日期等）。

示例 1：由 columnExpr 定义的分区键

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_partitionByTable ON CLUSTER sample_cluster;

-- 3. 创建由 columnExpr 定义的分区键表
CREATE TABLE sample_db.sample_partitionByTable ON CLUSTER sample_cluster (
  VisitDate Date,
  Hour UInt8,
  ClientID UUID
)
ENGINE = MergeTree
PARTITION BY VisitDate
ORDER BY Hour;

示例 2：在 columnExpr 的元组中定义分区键

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_partitionByTable ON CLUSTER sample_cluster;

-- 3. 创建带有分区键的表
CREATE TABLE sample_db.sample_partitionByTable ON CLUSTER sample_cluster (
  VisitDate Date,
  Hour UInt8,
  ClientID UUID
)
ENGINE = MergeTree
PARTITION BY (VisitDate, Hour)
ORDER BY Hour;

设计主键（PRIMARY KEY）

默认情况不需要显式指定，ByteHouse 将使用排序键作为主键。当有特殊场景主键和排序键不一致时，主键必须为排序键的最左前缀。如排序键为(OrderID, Date)，主键必须为OrderID，不能为 Date。
ByteHouse 会在主键上建立以 Granule 为单位的稀疏索引，与之对比，所谓稠密索引则是每一行都会建立索引信息。
如果查询条件能匹配主键索引的最左前缀，通过主键索引可以快速过滤出可能需要读取的数据颗粒，相比扫描整个 DataPart，通常要高效很多。
另外需要注意，PRIMARY KEY不能保证唯一性，所以可以插入主键重复的数据行。
分区（PARTITION BY）和主键（PRIMARY KEY）是两种不同的加速数据查询的方式，定义的时候应当尽量错开使用不同的列来定义两者，来覆盖更多的查询场景。例如 order by 的第一个列一定不要重复放到 partition by 里。下面是如何选择主键的一些考虑：

• 是否是查询条件里常用的列；
• 不是非分区键的第一个列；
• 这个列的选择性，例如性别、是/否这种可选值太少的列不建议放入主键中；
• 假如现在的主键是（a，b)，如果在大多数情况下给定（a，b）对应的数据范围很大（包含多个Granule），可以考虑把一个新的查询常用列附加到主键中，这样可以过滤更多的数据；
• 过长的主键会对插入性能和内存消耗有负面影响，但对查询性能没有影响。

示例

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_table ON CLUSTER sample_cluster;

-- 3. 创建表
CREATE TABLE IF NOT EXISTS sample_db.sample_table ON CLUSTER sample_cluster (
  id UInt32
) 
ENGINE=MergeTree 
ORDER BY id
PRIMARY KEY id;

唯一键索引（UNIQUE KEY）

主键（PRIMARY KEY）不能保证去重，如果有唯一键去重的需求，需要在建表时设置唯一键索引。设置唯一键之后，ByteHouse 提供 upsert 更新写语义，可以根据唯一键高效更新数据行，或者在upsert的时候通过设置虚拟列 delete_flag=1 ，可以用来删除指定的 key。查询自动返回每个唯一键的最新值。详情可参考 HaUniqueMergeTree 表的 upsert 功能。
唯一键可以是一组列的元组或任意的表达式，如UNIQUE KEY (product_id, sipHash64(city))。
通过唯一键查询时会用上唯一键索引过滤数据加速查询，所以通常主键可以设置和唯一键不一样列，覆盖更多的查询条件。不过如果要使用部分列更新功能的话，是需要唯一键为排序键的最左前缀。

示例 1：单字段 Unique Key

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_uniqueKeyTable ON CLUSTER sample_cluster;

-- 3. 创建唯一键表
CREATE TABLE sample_db.sample_uniqueKeyTable ON CLUSTER sample_cluster (
  `event_date` Date,
  `order_id` UInt64,
  `state` UInt32,
  `amount` UInt64
)
ENGINE = HaUniqueMergeTree
PARTITION BY event_date
ORDER BY state
UNIQUE KEY order_id;

-- 4. 插入数据（包含重复键），具有相同键的行将会替换之前的行
INSERT INTO sample_db.sample_table VALUES
('2021-03-01', 1001, 1, 100),
('2021-03-01', 1002, 1, 200),
('2021-03-01', 1001, 2, 100),
('2021-03-02', 1001, 1, 400);

-- 5. 查询数据验证，相同 order_id 的记录被合并，保留最后插入的值
SELECT * FROM sample_db.sample_table ORDER BY event_date, order_id;
┌─event_date─┬─order_id─┬─state─┬─amount─┐
│ 2021-03-01 │ 1001 │ 2 │100 │
│ 2021-03-01 │ 1002 │ 1 │200 │
│ 2021-03-02 │ 1001 │ 1 │400 │
└────────────┴──────────┴───────┴────────┘

-- 6. 插入更多测试数据继续验证
INSERT INTO sample_db.sample_table VALUES
('2021-03-01', 1002, 2, 200),
('2021-03-02', 1001, 2, 400),
('2021-03-02', 1002, 1, 300);

-- 7. 最终结果验证，所有重复 order_id 的记录均被合并为最新版本
SELECT * FROM sample_db.sample_table ORDER BY event_date, order_id;
┌─event_date─┬─order_id─┬─state─┬─amount─┐
│ 2021-03-01 │ 1001 │ 2 │100 │
│ 2021-03-01 │ 1002 │ 2 │200 │
│ 2021-03-02 │ 1001 │ 2 │400 │
│ 2021-03-02 │ 1002 │ 1 │300 │
└────────────┴──────────┴───────┴────────┘

示例 2：多字段 Unique Key

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_uniqueKeyTable ON CLUSTER sample_cluster;

-- 3. 创建有多字段的唯一键表
CREATE TABLE sample_db.sample_uniqueKeyTable ON CLUSTER sample_cluster (
  `event_date` Date,
  `region` UInt64,
  `state` String,
  `amount` UInt64
)
ENGINE = HaUniqueMergeTree
PARTITION BY event_date
ORDER BY state
UNIQUE KEY (region, state);

采样（SAMPLE BY）

用于抽样的表达式，该配置为可选项。
如果要用抽样表达式，主键中必须包含这个表达式。例如： SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))。

列和表的 TTL

指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项。表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：TTL date + INTERVAl 1 DAY。

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_ttlTable ON CLUSTER sample_cluster;

-- 3. 创建带有 TTL 的表
CREATE TABLE sample_db.sample_ttlTable ON CLUSTER sample_cluster (
  VisitDate Date,
  Hour UInt8,
  ClientID UUID
)
ENGINE = MergeTree
ORDER BY Hour
PARTITION BY VisitDate
TTL VisitDate + INTERVAL 1 DAY;

SETTINGS

配置 MergeTree settings，可参考社区文档：MergeTree 表配置。

MySQL 兼容性

ByteHouse 兼容了大部分 MySQL 的 DDL，可以直接使用 MySQL 的建表语句在 ByteHouse 中执行。请参考以下样例：

MySQL

CREATE TABLE `sample_table` (
  `uid` bigint NOT NULL AUTO_INCREMENT,
  `phone` varchar(255) DEFAULT NULL,
  `no1` bigint DEFAULT NULL,
  `appleid` varchar(255) DEFAULT NULL,
  
  PRIMARY KEY (`uid`) USING BTREE,
  UNIQUE KEY `no1` (`no1`) USING BTREE,
  KEY `idx_apple_id` (`appleid`) USING BTREE,
  KEY `phone` (`phone`) USING BTREE
)
ENGINE = InnoDB
AUTO_INCREMENT = 60937910
DEFAULT CHARSET = utf8mb3;

ByteHouse

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_table ON CLUSTER sample_cluster;

-- 3. 创建带有 TTL 的表
CREATE TABLE sample_db.sample_table ON CLUSTER sample_cluster (
  `uid` Int64 NOT NULL,
  `phone` String NULL DEFAULT NULL,
  `no1` Int64 NULL DEFAULT NULL,
  `appleid` String NULL DEFAULT NULL
)
ENGINE = HaUniqueMergeTree
ORDER BY tuple(uid)
UNIQUE KEY tuple(uid)
SETTINGS 
  partition_level_unique_keys = 0,
  allow_nullable_key = 1,
  index_granularity = 8192;

DDL 转换逻辑

可以注意到以下转换逻辑

语法

创建一个表，该表的结构与 SELECT 查询的结果类似，使用指定的引擎 engine，并使用 SELECT 的数据填充它。

CREATE TABLE [IF NOT EXISTS] [db.]table_name (
    name1 [type1],
    name2 [type2],
    ...
) 
ENGINE = MergeTree 
ORDER BY expr 
[PARTITION BY expr] 
AS SELECT ...

参数说明

示例

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.user_events ON CLUSTER sample_cluster;

-- 3. 创建主表
CREATE TABLE sample_db.user_events ON CLUSTER sample_cluster (
  user_id UInt32,
  event_time DateTime,
  event_type String,
  event_data String
)
ENGINE = MergeTree()
ORDER BY user_id
PRIMARY KEY user_id;

-- 4. 插入数据（修正目标表名）
INSERT INTO sample_db.user_events (user_id, event_time, event_type, event_data)
VALUES (10112, '2024-08-28 05:11:58', 'ERROR', 'this is a bug');

INSERT INTO sample_db.user_events (user_id, event_time, event_type)
VALUES (10112, '2024-07-28 05:11:58', 'ERROR');

-- 注意：此插入语句缺少 event_type 列（非 NULL 列），会导致错误
INSERT INTO sample_db.user_events (user_id, event_time, event_data)
VALUES (10112, '2024-07-28 05:11:58', 'this is a bug');

-- 5. 创建子表（修正源表名和 AS 子句语法）
CREATE TABLE IF NOT EXISTS sample_db.user_events2 ON CLUSTER sample_cluster (
  user_id UInt32,
  event_time DateTime,
  event_data String
)
ENGINE = MergeTree
ORDER BY user_id
AS SELECT 
  user_id, 
  event_time, 
  event_data 
FROM sample_db.user_events 
LIMIT 100;

-- 6. 查询子表
SELECT * FROM sample_db.user_events2;

ByteHouse 支持普通视图和物化视图。

普通视图

普通视图不存储任何数据，只是在每次访问时从另一个表执行读取。换句话说，普通视图只不过是一个保存的查询。 从视图中读取时，此保存的查询用作 FROM 子句中的子查询.

语法

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [tableIdentifier] [UUID uuid] AS [selectUnionStmt]

参数说明

示例

创建视图

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 删除现存表
DROP TABLE IF EXISTS sample_db.sample_table ON CLUSTER sample_cluster;

-- 3. 创建表
CREATE TABLE sample_db.sample_table ON CLUSTER sample_cluster (
  `order_by_column` String
)
ENGINE = MergeTree
ORDER BY (`order_by_column`)

-- 4. 基于已创建的表创建视图
CREATE VIEW sample_view AS SELECT * FROM sample_db.sample_table

查询视图

SELECT a, b, c FROM sample_db.sample_view

物化视图

详情请参见物化视图。

ByteHouse 数据字典分为内置字典和外部扩展字典：

• 内置字典为 ClickHouse 预定义字典内容。
• 外部扩展字典提供多种灵活、自定义的数据源定义，包括本地文件、ClickHouse 表、可通过 HTTP 访问的远程文件、其他数据库源（ODBC、MySQL、ClickHouse、MongoDB、Redis、PostgreSQL）。

创建字典表

您可以直接在 SQL 工作台使用 DDL 语句来新建字典表。

CREATE DICTIONARY [IF NOT EXISTS] [db.]dictionary_name (
  key1 type1[DEFAULT|EXPRESSION expr1] [HIERARCHICAL|INJECTIVE|IS_OBJECT_ID],
  key2 type2[DEFAULT|EXPRESSION expr2] [HIERARCHICAL|INJECTIVE|IS_OBJECT_ID],
  attr1 type2 [DEFAULT|EXPRESSION expr3],
  attr2 type2 [DEFAULT|EXPRESSION expr4]
)
PRIMARY KEY key1, key2
SOURCE(SOURCE_NAME(
  [param1 value1 ... paramN valueN]
))
LAYOUT(LAYOUT_NAME([param_name param_value]))
LIFETIME({MIN min_val MAX max_val | max_val})

参数解释

其他数据库源配置示例

• ODBC

  SOURCE(ODBC(
      db 'DatabaseName'
      table 'SchemaName.TableName'
      connection_string 'DSN=some_parameters'
      invalidate_query 'SQL_QUERY'
      query 'SELECT id, value_1, value_2 FROM db_name.table_name'
  ))
  

• MySQL

  SOURCE(MYSQL(
      port 3306
      user 'clickhouse'
      password 'qwerty'
      replica(host 'example01-1' priority 1)
      replica(host 'example01-2' priority 1)
      db 'db_name'
      table 'table_name'
      where 'id=10'
      invalidate_query 'SQL_QUERY'
      fail_on_connection_loss 'true'
      query 'SELECT id, value_1, value_2 FROM db_name.table_name'
  ))
  

• ClickHouse

  SOURCE(CLICKHOUSE(
      host 'example01-01-1'
      port 9000
      user 'default'
      password ''
      db 'default'
      table 'ids'
      where 'id=10'
      secure 1
      query 'SELECT id, value_1, value_2 FROM default.ids'
  ));
  

• MongoDB

  SOURCE(MONGODB(
      host 'localhost'
      port 27017
      user ''
      password ''
      db 'test'
      collection 'dictionary_source'
      options 'ssl=true'
  ))
  

• Redis

  SOURCE(REDIS(
      host 'localhost'
      port 6379
      storage_type 'simple'
      db_index 0
  ))
  

• PostgreSQL

  SOURCE(POSTGRESQL(
      port 5432
      host 'postgresql-hostname'
      user 'postgres_user'
      password 'postgres_password'
      db 'db_name'
      table 'table_name'
      replica(host 'example01-1' port 5432 priority 1)
      replica(host 'example01-2' port 5432 priority 2)
      where 'id=10'
      invalidate_query 'SQL_QUERY'
      query 'SELECT id, value_1, value_2 FROM db_name.table_name'
  ))
  

查询字典表

dictGet

dicGet 用于获取映射值。

dictGet('dict_name', attr_names, id_expr)
dictGetOrDefault('dict_name', attr_names, id_expr, default_value_expr)
dictGetOrNull('dict_name', attr_name, id_expr)

参数说明：

• dict_name — 字典名称，创建字典时指定的名称，字符串常量。
• attr_names — 读取的值所在的列名。同时获取多个值字段，使用 Tuple 类型，只获取一个字段名时使用 String。
• id_expr — 字典的键的具体值。支持 UInt64，或者是 Tuple 类型，可以参考以下的示例。

dictHas

dicHas 用户判断键字典是否存在。

dictHas('dict_name', id_expr)
-- 返回值是1/0

示例

示例 1：创建 ClickHouse 字典表

以下示例演示了 ClickHouse 字典源表，该表位于用户的同表集群中，可以使用 DICTIONARY 表直接访问。

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample_db ON CLUSTER sample_cluster;

-- 2. 创建字典
CREATE DICTIONARY sample_db.sample_dict ON CLUSTER sample_cluster (
    id UInt64,
    name String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(DB 'sample_db' TABLE 'sample_dict'))
LAYOUT(FLAT())
LIFETIME(MIN 0 MAX 180)

示例 2：创建 MySQL 字典表

以下示例演示了 MySQL 字典源表，使用时请使用实际的 MySQL 连接信息替换对应的占位符。

-- 1. 创建库
CREATE DATABASE IF NOT EXISTS sample ON CLUSTER sample_cluster;

-- 2. 创建字典
CREATE DICTIONARY IF NOT EXISTS sample.user_dict on cluster sample_cluster
(
    `user_id` UInt64,
    `username` String,
    `age` UInt8 DEFAULT 0,
    `gender` UInt8 DEFAULT 0,
    `last_login` DateTime
)
PRIMARY KEY user_id
SOURCE(MySQL(
    host 'mysql_host.example.com'
    port 3306
    user 'dict_reader'
    password 'secure_password'
    db 'user_db'
    table 'users'
))
LAYOUT(FLAT())
LIFETIME(MIN 300 MAX 900);

示例 3：使用字典表

• 查询字典数据
  您可以通过使用上述字典查询函数获取数据字典中的数据。

  -- 使用单个键查询单个字段的值
  SELECT dictGet('sample_dict', 'user', toUInt64(1001));
  
  -- 使用单个键查询多个字段的值
  SELECT dictGet('sample_dict', ('user', 'age', 'gender'), toUInt64(1001));
  
  -- 使用多个键查询对应的单个字段值
  SELECT dictGet('sample_dict', 'age', (toUInt64(1001), 'user1'));
  
  -- 使用多个键查询对应的多个字段值
  SELECT dictGet('sample_dict', ('age', 'gender'), tuple(toUInt64(1001), 'user1'));
  
  -- 如果查询的字段类型是 String，可以使用：
  SELECT dictGetString('sample_dict', 'user', toUInt64(1001));
  
  -- 如果查询的字段不存在，则返回指定的默认值
  SELECT dictGetOrDefault('sample_dict', 'user', toUInt64(1005), 'NULL');
  
  -- 使用多个键查询对应的字段如果不存在，则返回 Null
  SELECT dictGetOrNull('sample_dict', ('age', 'gender'), (toUInt64(1001), 'user1'));
  
  -- 判断键为键是否存在对应的字段：
  SELECT dictHas('sample_dict', toUInt64(1001));
  

• 删除字典
  ByteHouse 将删除 Catalog 中该字典的记录。

  DROP DICTIONARY IF EXISTS sample_db.sample_dict;
  

• 查询字典
  如果未指定 FROM 子句，则查询将返回当前数据库中的词典列表。

  SHOW DICTIONARIES FROM sample_db; 
  

• 查询创建字典语句。

  SHOW CREATE DICTIONARY sample_db.sample_dict;
  

  该查询语句返回一个字符串类型的“statement”列，该列包含一个值，用于创建指定对象的 CREATE 查询语句。