创建数据库 (CREATE DATABASE)

CREATE DATABASE [IF NOT EXISTS] db_name [ENGINE=Cnch]

参数说明：
IF NOT EXISTS：​如果db_name数据库已经存在，则 ByteHouse 不会创建新数据库并且：

• 如果指定了子句，则不会引发异常。
• 如果未指定子句，则抛出异常。

示例

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

  CREATE DATABASE IF NOT EXISTS test;
  

• 创建数据库使用可选库引擎，当前只支持设置为 Cnch。

  CREATE DATABASE IF NOT EXISTS test ENGINE=Cnch;
  

创建表 (CREATE TABLE)

语法

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 = CnchMergeTree()
ORDER BY expr
[PARTITION BY expr]
[CLUSTER BY (column, expression, ...) INTO value1 BUCKETS SPLIT_NUMBER value2 WITH_RANGE]
[PRIMARY KEY expr]
[UNIQUE KEY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...]
[COMMENT='COMMENT']

示例

CREATE TABLE IF NOT EXISTS test.createTable(
id UInt32,
name String DEFAULT '',
CONSTRAINT constraint1 CHECK id > 10
) 
ENGINE=CnchMergeTree
ORDER BY id

配置参数

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 计算得出。

CREATE TABLE [db.]test
(
id UInt64,
updated_at DateTime DEFAULT now(),
updated_at_date Date DEFAULT toDate(updated_at)
)
ENGINE = CnchMergeTree
ORDER BY id;

INSERT INTO test (id) Values (1);

SELECT * FROM test;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│1 │ 2023-02-24 17:06:46 │2023-02-24 │
└────┴─────────────────────┴─────────────────┘

MATERIALIZED

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

CREATE TABLE [db.]test
(
id UInt64,
updated_at DateTime MATERIALIZED now(),
updated_at_date Date MATERIALIZED toDate(updated_at)
)
ENGINE = CnchMergeTree
ORDER BY id;

INSERT INTO test Values (1);

SELECT * FROM test;
┌─id─┐
│1 │
└────┘

SELECT id, updated_at, updated_at_date FROM test;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│1 │ 2023-02-24 17:08:08 │2023-02-24 │
└────┴─────────────────────┴─────────────────┘

SELECT * FROM test SETTINGS asterisk_include_materialized_columns=1;
┌─id─┬──────────updated_at─┬─updated_at_date─┐
│1 │ 2023-02-24 17:08:08 │2023-02-24 │
└────┴─────────────────────┴─────────────────┘

ALIAS

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

CREATE TABLE [db.]test
(
id UInt64,
size_bytes Int64,
size String Alias formatReadableSize(size_bytes)
)
ENGINE = CnchMergeTree
ORDER BY id;

INSERT INTO test Values (1, 4678899);

SELECT id, size_bytes, size FROM test;
┌─id─┬─size_bytes─┬─size─────┐
│1 │4678899 │ 4.46 MiB │
└────┴────────────┴──────────┘

SELECT * FROM test SETTINGS asterisk_include_alias_columns=1;
┌─id─┬─size_bytes─┬─size─────┐
│1 │4678899 │ 4.46 MiB │
└────┴────────────┴──────────┘

设计分区键（PARTITION BY)

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

CREATE TABLE test.partitionByClause
(
VisitDate Date,
Hour UInt8,
ClientID UUID
)
ENGINE = CnchMergeTree
PARTITION BY VisitDate
ORDER BY Hour;

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

CREATE TABLE test.partitionByClause
(
VisitDate Date,
Hour UInt8,
ClientID UUID
)
ENGINE = CnchMergeTree
PARTITION BY (VisitDate,Hour)
ORDER BY Hour;

设计排序键（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 排序

CREATE TABLE IF NOT EXISTS test.orderByClause (id UInt32, val UInt32) ENGINE=CnchMergeTree() ORDER BY id;
INSERT INTO TABLE test.orderByClause VALUES (1,5),(1,15),(2,5),(2,15);
SELECT * FROM test.orderByClause ORDER BY id ASC, val DESC;

示例2：按 NULL FIRST排序

CREATE TABLE IF NOT EXISTS test.orderByClause (id UInt32, val Nullable(UInt32)) ENGINE=CnchMergeTree() ORDER BY id;
INSERT INTO test.orderByClause VALUES (1,2),(2,NULL),(3,2),(3,3),(3,NULL);
SELECT * FROM test.orderByClause ORDER BY val DESC NULLS FIRST;

示例3：按COLLATE排序

CREATE TABLE IF NOT EXISTS test.orderByClause (x UInt32, s Nullable(String)) ENGINE=CnchMergeTree ORDER BY x;
INSERT INTO test.orderByClause VALUES (1,'bca'),(2,NULL),(3,'ABC'),(4,'123a'),(5,'abc'),(6,NULL),(7,'BCA');
SELECT * FROM test.orderByClause ORDER BY s ASC COLLATE 'ru';

设计主键(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），可以考虑把一个新的查询常用列附加到主键中，这样可以过滤更多的数据。
• 过长的主键会对插入性能和内存消耗有负面影响，但对查询性能没有影响。

示例

CREATE TABLE IF NOT EXISTS test.sampleByClause 
(
id UInt32
) 
ENGINE=CnchMergeTree 
ORDER BY id 
SAMPLE BY id;

唯一键(UNIQUE KEY)

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

CREATE TABLE test.uniqueKeyClause
(
`event_date` Date,
`order_id` UInt64,
`state` UInt32,
`amount` UInt64
)
ENGINE = CnchMergeTree
PARTITION BY event_date
ORDER BY state
UNIQUE KEY order_id;

-- rows with the same key will replace previous rows
INSERT INTO test.uniqueKeyClause 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);

SELECT * FROM test.uniqueKeyClause 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 │
└────────────┴──────────┴───────┴────────┘

INSERT INTO test.uniqueKeyClause VALUES
('2021-03-01', 1002, 2, 200),
('2021-03-02', 1001, 2, 400),
('2021-03-02', 1002, 1, 300);

SELECT * FROM test.uniqueKeyClause 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

CREATE TABLE test.uniqueKeyClause
(
`event_date` Date,
`region` UInt64,
`state` String,
`amount` UInt64
)
ENGINE = CnchMergeTree
PARTITION BY event_date
ORDER BY state
UNIQUE KEY (region, state);

CONSTRAINT

Bytehouse 在语法上支持 CONSTRAINT，但除 NOT NULL 外，并不会强制执行这些约束。

• 唯一性约束（UNIQUE）：如果您的业务逻辑强依赖于列的唯一性，请使用 UNIQUE KEY 而非 UNIQUE CONSTRAINT。
• 外键约束（FOREIGN KEY）：外键约束仅用于查询优化，不会进行数据完整性校验。

分桶 Bucketing (Cluster By)

分桶常用于以下场景：

1. 通用场景: 数据分布不均匀
   * 定义及原理：当分区无法实现数据的均匀分布时，可以利用分桶字段。 分桶字段保证一列数据均匀分布在集群的每个节点下。 这可以最大限度地提高查询的集群性能。 分区字段的合理设置也有助于解决数据倾斜问题，保证数据分布更加均匀。
   * 字段限制：不支持 Nullable。
   * 配置建议：选择分组依据中经常出现的字段。
   * 表创建成功后，该字段不允许修改列类型。
2. 特定场景：重复数据删除速度慢
   * 定义和原理：当设置了Unique Key并且单个分区中的数据过多（例如超过1亿行）时，数据摄取的速度将会受到影响。 这是因为需要获取锁才能进行重复数据删除。 在这种情况下，您可以将分区划分为存储桶以提高数据摄取速度。
   * 字段限制：不支持 Nullable。
   * 配置建议：Bucket Key需要与Unique Key相同。 （每个桶应小于1000万行）

分桶应用案例
假设某 ByteHouse 订阅用户启用了六个计算节点，由于单个分区的数据量较大，超过2亿条记录，应用程序经常根据c1和c2字段进行聚合和连接操作。 因此，决定使用桶表进行优化。 桶表的设计选项如下：

• 分桶键（ CLUSTER Key ）选择：选择c1和c2列作为分桶键。
• 桶（Bucket）数：取节点数的两倍：12。

-- 创建带有分桶的表 create table with bucketing
create or replace table table_01 (c1 timestamp, c2 string, c3 number) cluster by (to_date(c1), c2) INTO 12 BUCKETS;

-- 将桶添加到现有数据中 add bucket to existing data
ALTER TABLE t CLUSTER BY (column, expression, ...) INTO 64 BUCKETS

-- 按多列将桶添加到集群中 add bucket to cluster by multiple columns
ALTER TABLE t CLUSTER BY sipHash(a,b,c) INTO 64 BUCKETS

-- 添加或更改集群属性 Add or change the cluster properties
ALTER TABLE t MODIFY CLUSTER BY (column, expression, ...) INTO 64 BUCKETS

采样

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

列和表的 TTL

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

CREATE TABLE test.ttlClause
(
VisitDate Date,
Hour UInt8,
ClientID UUID
)
ENGINE = CnchMergeTree
ORDER BY Hour
PARTITION BY VisitDate
TTL VisitDate + INTERVAL 1 DAY；

如果您需要基于字符串类型字段实现 TTL，需在建表时使用 toDate 函数先将其转换为 Date 或 DateTime 类型，再结合该转换后的日期时间列设置 TTL。toDate 函数用法请参见toDate。

CREATE TABLE test.ttlClause ( 
 start_date STRING, -- 字符串类型的日期字段 
 Hour UInt8,
 ClientID UUID
) ENGINE = CnchMergeTree 
ORDER BY Hour 
TTL toDate(start_date) + INTERVAL 1 DAY; -- 将字符串转换为 Date 类型后设置 TTL

压缩

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。

多编解码器

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

举例参考：

CREATE TABLE codec_example
(date Date CODEC(Delta, ZSTD),
ts DateTime CODEC(LZ4HC),
float_value Float32 CODEC(NONE),
double_value Float64 CODEC(LZ4HC(9))
)
ENGINE = CnchMergeTree
PARTITION BY tuple()
ORDER BY date

表引擎

表引擎即表的类型，决定了：

• 数据的组织和存储方式
• 索引的方式以及索引类型
• 支持哪些查询以及如何支持
• 一些其他特定的功能和配置

ByteHouse 云数仓版最常用的表引擎是 CnchMergeTree，除此之外也有其他特殊类型的表引擎包括 Hive外表、Kafka表等。本文重点分享 CnchMergeTree 表引擎的原理。

CNCHMergeTree 表引擎

CNCHMergeTree 是最常用的表引擎，核心思想和LSM-Tree类似，数据按分区键(partition by)进行分区，然后排序键(order by)进行有序存储。主要有如下特点：

• 逻辑分区

如果指定了分区键的话，数据会按分区键划分成了不同的逻辑数据集（逻辑分区，Partition)。
每一个逻辑分区可以存在零到多个数据片段（DataPart）。如果查询条件可以裁剪分区，通常可以加速查询。如果没有指定分区键，全部数据都在一个逻辑分区里。

• 数据片段

数据片段里的数据按排序键排序。每个数据片段还会存在一个min/max索引，来加速分区选择。

• 数据颗粒（Granule）

每个数据片段被逻辑的分割成颗粒（granule），默认的Granule为8192行（由表的index_granularity配置决定）。颗粒是 ByteHouse 中进行数据查询时的最小不可分割数据集。每个颗粒的第一行通过该行的主键值进行标记， ByteHouse 会为每个数据片段创建一个索引文件来存储这些标记。对于每列，无论它是否包含在主键当中，ByteHouse 都会存储类似标记。这些标记让您可以在列文件中直接找到数据。Granule作为ByteHouse 稀疏索引的索引目标，也是在内存中进行数据扫描的单位。

• 后台 Merge

后台任务会定时对同一个分区的DataPart进行合并，并保持按排序键有序。后台的合并减少了 Part 的数目，以便更高效存储，并提升了查询性能。

MySQL兼容性

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

CREATE TABLE `table1` (
`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

CREATE TABLE table1 (
`uid` Int64 NOT NULL,
`phone` String NULL DEFAULT NULL,
`no1` Int64 NULL DEFAULT NULL,
`appleid` String NULL DEFAULT NULL
)
ENGINE = CnchMergeTree
ORDER BY tuple(uid)
UNIQUE KEY tuple(uid)
SETTINGS partition_level_unique_keys = 0, storage_policy = 'cnch_default_hdfs', allow_nullable_key = 1, storage_dialect_type = 'MYSQL', index_granularity = 8192

DDL转换逻辑

可以注意到以下转换逻辑

CREATE TABLE AS SELECT（CTAS）

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

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

如果表已经存在并且指定了IF NOT EXISTS子句，则查询不会执行任何操作。在查询中的ENGINE子句之后可以有其他子句。
示例

CREATETABLE DB2.unique_user_events (
user_id UInt32,
event_time DateTime,
event_type String,
event_data String
) ENGINE = CnchMergeTree()
ORDER BY user_id 
PRIMARY KEY user_id 
UNIQUE KEY event_time;

INSERT into DB2.unique_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 DB2.unique_user_events(user_id,event_time,event_type) values(10112,'2024-07-28 05:11:58','ERROR');
INSERT into DB2.unique_user_events(user_id,event_time,event_data) values(10112,'2024-07-28 05:11:58','this is a bug');

CREATE TABLE IF NOT EXISTS DB2.table_name2(user_id UInt32,event_time DateTime,event_data String) 
ENGINE = CnchMergeTree 
ORDER BY user_id 
AS 
select user_id,event_time,event_data from DB2.unique_user_events limit 100;

select * from DB2.table_name2;

创建视图（CREATE VIEW）

普通视图（View）不存储任何实际数据，仅用于保存一条预设的查询语句。在读取视图数据时，该预设查询会被自动作为子查询，嵌入到最终执行 SQL 的 FROM 子句中运行。
ByteHouse 云数仓版引擎 v2.3 之前的版本中，普通视图（View）的执行权限依赖于视图自身的读权限和底表的读权限，但在一些场景中，用户希望仅处理视图权限而无需关心底表权限。ByteHouse 云数仓版从 v2.3 开始，引入了视图 SQL Security 功能，为视图增加了定义者（DEFINER）和调用者（INVOKER）来控制视图的执行行为，适用于需要向不同用户提供受限数据访问权限的场景。
定义者（DEFINER）、调用者（INVOKER） 和不使用 SQL Security 功能的差异说明如下：

视图 SQL Security 仅为 ByteHouse 引擎在执行视图查询过程中所做的底表权限代理，实际查询视图的用户自身所获取的权限并不会因此改变。对于已创建的视图，ByteHouse 引擎默认使用 INVOKER 角色，您可参考下文中的修改视图 SQL Security 小节，调整视图 SQL Security。
本节将介绍如何创建和管理视图，并重点说明如何使用 SQL Security 功能进行精细化的权限控制。

语法

创建视图

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]view_name
[DEFINER = { `user` | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }]
AS SELECT ...

参数说明

查询视图

语法与普通查询相同。

SELECT a, b, c FROM view_name

修改视图 SQL Security

您可使用 ALTER 语句调整视图的使用权限。

• 将视图 SQL Security 从 INVOKER 调整为 DEFINER 时，建议同时调整 DEFINER 和 SQL SECURITY，以获得准确信息，这是因为仅调整其中一项时，ByteHouse 会自动触发另一项的重置，重置时使用默认值。
• 将视图 SQL Security 从 DEFINER 调整为 INVOKER时，仅需设置 SQL SECURITY INVOKER。

# 方式 1
ALTER TABLE [db.]view_name 
MODIFY SQL SECURITY  { DEFINER | INVOKER }
[DEFINER = { user | CURRENT_USER }]

# 方式 2
ALTER TABLE [db.]view_name 
MODIFY [DEFINER = {user | CURRENT_USER }]
SQL SECURITY { DEFINER | INVOKER }

为用户授予视图相关的权限

您可以使用 GRANT 语句为用户授予视图相关的权限。

• 创建视图的权限
  创建视图需要 CREATE VIEW 权限。如果您在创建视图时需要使用 SQL Security 功能，且执行创建视图（CREATE VIEW）操作的用户与指定的 DEFINER 不相同，则需为执行创建视图的用户授予 SET DEFINER 权限。
  示例：

  GRANT CREATE VIEW ON [db.]view_name TO user;
  
  GRANT SET DEFINER ON another_user TO user;
  

  参数说明

  参数	是否必填	参数说明
  CREATE VIEW	是	创建视图的权限。
  SET DEFINER ON	是	为用户授予在创建视图 schema 中指定 DEFINER = user 的权限。获得该权限的用户才能够在创建视图时指定 DEFINER 信息，并且只能设置为已获取 SET DEFINER 权限的用户名。 如果执行创建视图（CREATE VIEW）的用户与创建视图语句中 DEFINER 参数指定的用户名相同，则无需显式授予 SET DEFINER 权限。 如果创建视图时，需要将 DEFINER 指定为其他用户，则必须为该用户显式授予 SET DEFINER 权限。如果未授予该权限，则该用户在创建视图时，将没有权限使用 DEFINER = user 和 SQL SECURITY。 请确保您在 GRANT SET DEFINER 中授权的用户名和创建视图中 DEFINER = xxx 的用户名一致，否则用户将无法获取对应的 DEFINER 使用权限。
  TO user	是	指定授予权限的用户。该参数值支持设置为 user 或 CURRENT_USER： user：该参数值由火山引擎账号 ID 和用户 ID 拼接而成，格式为 {volcano_ID}.{bytehouse_ID}，需使用反引号（````）包裹，示例：21xxxxxx68.45xxxx79。火山引擎账号 ID 和用户 ID 获取方式如下： 火山引擎账号 ID：在 ByteHouse 云数仓版控制台上，单击右上角的用户头像，查看并复制账号 ID。 用户 ID：在 ByteHouse 云数仓版控制台上，单击顶部权限管理，单击用户，在用户列表中，单击用户 ID 以复制 ID。 CURRENT_USER：表示当前执行语句的用户。

• 使用视图的权限
  仅为用户授予视图的SELECT权限。

  GRANT SELECT ON [db.]view_name TO user;
  

• 删除与修改视图的权限
  删除视图仅需为用户授予 DROP VIEW 权限。

  GRANT DROP VIEW ON [db.]view_name TO user;
  

  修改视图需要为用户授予 ALTER VIEW MODIFY SQL SECURITY 和 SET DEFINER 权限。SET DEFINER 权限的授予请参考上述“创建视图的权限”小节。

  GRANT ALTER VIEW MODIFY SQL SECURITY ON [db.]view_name TO user;
  
  -- 也可以为用户授予 ALTER VIEW 权限集合
  GRANT ALTER VIEW ON [db.]view_name TO user;
  

示例

为了方便演示不同用户的权限，以下示例中使用管理员、ViewAdmin、ViewUser 三个用户进行说明，实际使用时请使用实际的用户名（火山引擎 ID 和 ByteHouse ID 拼接而成）替换占位符，获取方式请参见上述创建视图小节的参数说明。

• 管理员：系统管理员（AccountAdmin），拥有最高权限。
• ViewAdmin：视图管理员。
• ViewUser：视图使用者。

操作步骤：

1. 创建测试用户和底表。

   -- 使用管理员账号创建数据库、表
   -- 创建测试数据库
   CREATE DATABASE IF NOT EXISTS test_db;
   
   -- 创建底表
   CREATE TABLE test_db.employee (
       id INT,
       name STRING,
       salary INT
   ) ENGINE = MergeTree() ORDER BY id;
   
   -- 插入测试数据
   INSERT INTO test_db.employee VALUES (1, 'Alice', 5000), (2, 'Bob', 6000);
   
   -- 为 ViewAdmin 用户授予底表访问权限和视图管理基础权限
   GRANT SELECT ON test_db.employee TO ViewAdmin;
   GRANT CREATE VIEW ON test_db.* TO ViewAdmin;
   -- 可选，此示例中使用 ViewAdmin 用户创建视图且 DEFINER 为 ViewAdmin 用户，则无需显示指定 SET DEFINER 权限
   GRANT SET DEFINER ON ViewAdmin TO ViewAdmin;
   

2. 创建不同权限模式的视图。

   • 创建 SQL SECURITY DEFINER 视图

     -- 切换到 ViewAdmin 用户执行
     CREATE VIEW test_db.employee_view_definer
     DEFINER = ViewAdmin
     SQL SECURITY DEFINER
     AS SELECT id, name FROM test_db.employee; -- 只暴露部分字段
     
     -- 为 ViewUser 用户授予视图查询权限（无需授予底表权限）
     GRANT SELECT ON test_db.employee_view_definer TO ViewUser;
     

     验证结果：

     -- 使用 ViewUser 用户查询视图，可成功查询
     SELECT * FROM test_db.employee_view_definer;
     -- 结果：
     -- 1        Alice
     -- 2        Bob
     
     -- 使用 ViewUser 用户直接查询底表，由于无权限，查询失败
     SELECT * FROM test_db.employee;
     -- 报错：拒绝访问
     

   • 创建 SQL SECURITY INVOKER 视图

     -- 切换到 ViewAdmin 用户执行
     CREATE VIEW test_db.employee_view_invoker
     SQL SECURITY INVOKER
     AS SELECT id, salary FROM test_db.employee;
     
     -- 仅授予 ViewUser 用户视图权限（不授予底表权限）
     GRANT SELECT ON test_db.employee_view_invoker TO ViewUser;
     

     验证结果：

     -- 使用 ViewUser 用户查询视图，由于缺少底表权限，查询失败
     SELECT * FROM test_db.employee_view_invoker;
     
     -- 使用管理员账号为 ViewUser 用户补充授予底表权限后再次查询，可以查询成功
     GRANT SELECT ON test_db.employee TO ViewUser;
     SELECT * FROM test_db.employee_view_invoker;
     -- 结果：
     -- 1        5000
     -- 2        6000
     

   • 创建不使用 SQL Security 的视图

     -- 切换到 ViewAdmin 用户执行，视图默认使用 SQL SECURITY INVOKER
     CREATE VIEW test_db.employee_view_normal
     AS SELECT id, name FROM test_db.employee; 
     
     -- 仅授予 ViewUser 用户视图查询权限
     GRANT SELECT ON test_db.employee_view_normal TO ViewUser;
     

     验证结果：

     -- 使用 ViewUser 用户查询视图，由于缺少底表权限，查询失败
     SELECT * FROM test_db.employee_view_normal;
     -- 报错：拒绝访问
     
     -- 使用 ViewUser 用户直接查询底表，由于缺少底表权限，查询失败
     SELECT * FROM test_db.employee;
     -- 报错：拒绝访问
     

     此时，为 ViewUser 授予底表的查询权限，则 ViewUser 可以查询到视图数据。

     -- 使用管理员账号授予 ViewUser 用户底表查询权限
     GRANT SELECT ON test_db.employee TO ViewUser;
     
     -- 再次使用 ViewUser 用户执行视图查询，可查询成功
     SELECT * FROM test_db.employee_view_normal;
     -- 结果：
     -- 1        Alice
     -- 2        Bob
     

3. 修改视图权限模式。

   --使用管理员账号授予 ViewAdmin 用户修改视图的权限
   GRANT ALTER VIEW MODIFY SQL SECURITY ON test_db.employee_view_definer TO ViewAdmin;
   
   -- 使用 ViewAdmin 用户将 DEFINER 视图改为 INVOKER 模式
   ALTER TABLE test_db.employee_view_definer
   MODIFY SQL SECURITY INVOKER;
   
   -- 使用 ViewUser 用户查询视图
   SELECT * FROM test_db.employee_view_definer;
   -- 若未授予 ViewUser 底表权限，此时会报错
   

4. 删除视图。

   -- 使用管理员账号授予 ViewAdmin 用户删除权限
   GRANT DROP VIEW ON test_db.employee_view_definer TO ViewAdmin;
   
   -- 使用 ViewAdmin 用户删除视图
   DROP VIEW test_db.employee_view_definer;
   

常见异常及解决办法

以下报错示例以 ViewCreator、ViewUser 用户为例，展示了常见的权限异常。

缺少 SET DEFINER 权限

常见报错：

ViewCreator: Not enough privileges. To execute this query it's necessary to have grant SET DEFINER ON ViewAdmin

原因：
查询用户权限，发现该用户缺少 SET DEFINER 权限。

解决方法：
为异常用户授予 SET DEFINER 权限，并且指定的 DEFINER 用户名应该为查询中指定的 DEFINER。
示例：

GRANT SET DEFINER ON ViewCreator TO ViewCreator;

缺少 ALTER VIEW MODIFY SQL SECURITY 权限

常见报错：

ViewCreator: Not enough privileges. To execute this query it's necessary to have grant ALTER VIEW MODIFY SQL SECURITY ON testdb.test_view3

原因：
查询用户权限，发现该用户没有修改视图的权限。

解决方法：
为用户授予 ALTER VIEW 的权限。示例：

GRANT ALTER VIEW ON db_name.view_name TO ViewCreator;

执行视图查询时缺少底表权限

常见报错：

ViewUser: Not enough privileges. To execute this query it's necessary to have grant SELECT(tag, value) ON testdb.test_table

原因：
查询视图 schema，发现执行权限为 INVOKER，视图使用者 ViewUser 没有底表权限。

解决方法：

• 方式 1：修改视图，将执行权限的参数值修改为 DEFINER，同时需确保 DEFINER 指定的用户拥有底表权限。

  ALTER TABLE db_name.view_name
  MODIFY SQL SECURITY DEFINER
  DEFINER = `user`;
  

• 方式 2：为该查询用户授予底表权限。

  GRANT SELECT ON db_name.table_name TO ViewUser;
  

创建物化视图（CREATE MATERIALIZED VIEW）

详见物化视图章节。

创建字典（CREATE DICTIONARY）

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

• 内置字典为 ClickHouse 预定义字典内容；
• 外部扩展字典提供多种灵活的自定义的数据源定义，当前支持 MySQL、 PostgreSQL和 ClickHouse 。字典数据会定期更新，支持动态加载。

本节重点介绍如何操作 ByteHouse 外部扩展字典。更多字典功能介绍，请参考数据字典。

创建字典表

您可以直接在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})

使用字典表

1. 查询字典数据
   通过使用dictGet / dictGetOrDefault 语句和函数获取数据字典中的数据。

   dictGet('db.dict_name', 'attr_name', id_expr)
   dictGetOrDefault('db.dict_name', 'attr_name', id_expr, default_value_expr)
   

   说明

   dictGet函数的第一个参数的格式必须类似于 db.dict_name，因为字典缓存通过该字段来索引。

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

   DROP DICTIONARY [IF EXISTS] [db.]dictionary_name;
   

3. 查询字典

   SHOW DICTIONARIES [FROM <db>]; 
   

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

4. 查询创建字典语句

   SHOW CREATE DICTIONARY [db.] dictionary_name
   

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

创建动态分区表

ByteHouse 支持分区级别动态设置分桶数量，您可通过使用表达式设置分桶键，并使用 settings 参数创建动态分区表。具体示例请参见Bucket Table 动态扩容最佳实践。

注意事项

该功能当前正在灰度发布中，若使用中遇到问题，可提交工单或联系 ByteHouse 团队。

语法

CREATE TABLE t(name1 type1, name2 type2, ...)
engine=CnchMergeTree()
PARTITION BY ...
CLUSTER BY EXPRESSION expr INTO 4 BUCKETS
ORDER BY ...
[UNIQUE KEY ...]
SETTINGS enable_recluster_multiple_parts=1, enable_scale_bucket_optimization= 1

参数说明如下：

相关文档

• Bucket 表动态扩缩容请参考Bucket Table 动态扩容最佳实践。
• 唯一键表使用 bucket 去重优化最佳实践请参考Bucket 去重优化。