clickhouse的SQL参考——（二）create

参考资料

https://clickhouse.tech/docs/en/sql-reference/statements/create/

版本:v20.11

General Purpose Codecs

specialized codecs

临时表（Temporary Tables）

CREATE SETTINGS PROFILE

举例

总览

create查询将创建以下类型之一：

CREATE DATABASE

创建一个新数据库

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

子句

IF NOT EXISTS

如果数据库名字db_name已经存在，那么clickhouse不会创建一个新数据库

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

ON CLUSTER

ClickHouse在指定集群的所有服务器上创建db_name数据库。 Distributed DDL 更多详细信息。

ENGINE

MySQL引擎允许您从远程MySQL服务器检索数据。

默认情况下，ClickHouse使用自己的数据库引擎（Ordinary）。

另外还有Lazy引擎、Memory引擎、Dictionary引擎。

CREATE TABLE

创建一个新表。根据使用情况，此查询可以具有各种语法形式。

默认情况下，仅在当前服务器上创建表。Distributed DDL （分布式DDL）通过ON CLUSTER创建，该子句将单独描述。

语法形式

使用显示语法结构

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

如果db没有指定，则在当前数据库进行创建，如果指定db，则在db数据库进行创建，使用括号中的语句创建，并使用指定的engine引擎。

表的结构是 [列说明、二级索引和约束] 的列表。

如果引擎支持主键，那么主键将作为表引擎的参数。

在最简单情况下，列描述是名称+类型。例如：RegionID UInt32

也可以为默认值定义表达式。（参见下文）

使用其他表的结构

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]

创建一张与另一个表具有相同结构的表。

可以为新表指定其他引擎。如果未指定引擎，则将使用与旧表相同的引擎。

使用表函数返回的结构

CREATE TABLE [IF NOT EXISTS] [db.]table_name AS table_function()
CREATE TABLE [IF NOT EXISTS] [db.]table_name ENGINE = engine AS SELECT ...

用表函数返回的结构和数据创建一个表。

使用select查询返回的表结构创建表，可以指定engine，并使用select查询返回的值作为内容插入表中。

在所有情况下，如果指定了IF NOT EXISTS，那么如果表已经存在，查询将不会返回错误。在这种情况下，查询将不会执行任何操作。

查询中的ENGINE子句之后可以有其他子句。参阅表引擎描述table engines.

默认值

列描述可以通过以下方式指定默认值的表达式：

DEFAULT expr
MATERIALIZED expr
ALIAS expr

举例：URLDomain String DEFAULT domain(URL).

如果没有指定默认值表达式，那么对于不同的数据类型：

数字（numbers）:0
字符串（strings）：空字符串
数组（arrays）：空数组
日期（date）: 1970-01-01
时间戳（DateTime）:zero unix timestamp
空值（Nullable）：NULL

如果定义了默认表达式，则列类型是可选的。如果没有明确定义的类型，则使用默认的表达式类型。

示例：EventDate DEFAULT toDate(EventTime)，‘Date’ 类型将用于‘EventDate’ 列

如果显式定义了数据类型和默认表达式，则将使用类型转换函数将此表达式转换为指定的类型。

示例：Hits UInt32 DEFAULT 0 和 Hits UInt32 DEFAULT toUInt32(0)含义相同

可以将默认表达式定义为表常量和列中的任意表达式。创建和更改表格结构时，它会检查表达式是否不包含循环。对于INSERT，它将检查表达式是否可解析。

DEFAULT子句

默认值。如果INSERT查询未指定相应的列，则将通过计算对应列的表达式来填充。

MATERIALIZED子句

Materialized表达式。INSERT查询无法指定，是经过计算得到的列值，不能插入。

对于不带有列名列表的insert查询，不考虑这些列。

此外，在SELECT查询中使用星号时，该列也不会返回。这是为了使用INSERT将使用SELECT *获得的数据转储插入表中时，不指定列名也可以保证数据不变。

ALIAS子句

别名列，可以指定当前列是哪些表达式的别名，查询时可用，插入不可用。

这样的列根本没有存储在表格中。它的值不能插入到表中，并且在SELECT查询中使用星号时，也不会返回。

可以在SELECT中使用它。

（剩下一部分的描述很模糊，我没有搞明白）

https://clickhouse.tech/docs/en/sql-reference/statements/create/table/

约束（Constraints）

属于列描述，用来定义约束。

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可以是任何boolen类型表达式。如果为表定义了约束，则将在INSERT查询中针对每一行检查每个约束。如果不满足所有约束条件，服务器将引发约束条件名称和检查表达式的异常。

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

TTL 表达式

定义值的存储时间。只能为MergeTree系列表指定。有关详细说明，请参见TTL for columns and tables.

列压缩

默认情况下，ClickHouse应用lz4压缩方法。对于MergeTree引擎系列，您可以在服务器配置的“compression”部分中更改默认压缩方法。

您还可以在CREATE TABLE查询中为每个单独的列定义压缩方法。

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>
...

压缩编码省略将引用默认设置，取决于不同的设置和数据类型，示例：value UInt64 CODEC（Default）

也可以从列中删除当前的编解码器，并使用config.xml中的默认压缩：

ALTER TABLE codec_example MODIFY COLUMN float_value CODEC(Default);

编码可以组合在管道中，例如：CODEC(Delta, Default).

要为您的项目选择最佳的编解码器组合，请通过参考New Encodings to Improve ClickHouse Efficiency 以提高ClickHouse效率。需要注意的一件事是CODEC不能应用于ALIAS列类型。

您无法使用lz4之类的外部实用工具解压缩ClickHouse数据库文件。应该使用特殊的clickhouse-compressor实用程序。

以下表引擎支持压缩：

MergeTree 家族. 支持压缩编码，并支持从配置中读取“compression”的默认压缩方法
Log 家族. 默认情况下使用lz4压缩方法，并支持列压缩编解码器。
Set. 仅支持默认压缩。
Join. 仅支持默认压缩。

ClickHouse支持通用编解码器（general purpose codecs）和专用编解码器（specialized codecs.）。

General Purpose Codecs

Codecs:

NONE — 不压缩
LZ4 —默认使用无损数据压缩算法。
LZ4HC[(level)] —具有可配置级别的LZ4 HC（高压缩）算法。默认级别：9.设置<=0时使用默认，可能的值[1, 12]
ZSTD[(level)] —ZSTD压缩算法具有可配置的级别。可能的级别：[1，22]。默认值：1

高压缩级别对于非对称场景非常有用，例如一次压缩，反复解压缩。更高的级别意味着更好的压缩和更高的CPU使用率。

specialized codecs

这些编码旨在通过数据的专门特征来提供更有效的压缩。其中一些编码器，自身不会压缩数据，相反会为通用编解码器准备数据，与没有做这种准备的情况相比，压缩效果更好。

Specialized codecs:

Delta(delta_bytes) ——原始值被两个相邻值的差代替，但第一个值保持不变。最多使用delta_bytes来存储增量值，因此delta_bytes是原始值的最大大小。可能的delta_bytes值：1、2、4、8。如果sizeof(type) 不是1,2,4,8，那么delta_bytes是1.

DoubleDelta——计算增量的增量并将其以紧凑的二进制形式写入。对于具有恒定步幅的单调序列（例如时间序列数据），可以实现最佳压缩率。可以与任何固定宽度类型一起使用。实现Gorilla TSDB中使用的算法，并将其扩展为支持64位类型。为32字节增量使用1个额外的位：5位前缀而不是4位前缀。更多信息参见Gorilla: A Fast, Scalable, In-Memory Time Series Database.

Gorilla——计算当前值和先前值之间的XOR，并以紧凑的二进制形式编写。存储一系列缓慢变化的浮点值时非常有效，因为当相邻值是二进制相等时，可以获得最佳压缩率。实现Gorilla TSDB中使用的算法，并将其扩展为支持64位类型。更多信息参见Gorilla: A Fast, Scalable, In-Memory Time Series Database.

T64 —压缩方法，用于裁剪整数数据类型（包括Enum，Date和DateTime）中未使用的高位值。在其算法的每个步骤中，编解码器均采用64个值的块，将它们放入64x64位矩阵，对其进行转置，裁剪未使用的值位，然后将其余值作为序列返回。未使用的位是在使用压缩的整个数据部分中最大值和最小值之间没有区别的位。

Gorilla TSDB中使用DoubleDelta和Gorilla编解码器作为其压缩算法的组成部分。Gorilla的算法在时间戳随序列缓慢变化的情况下有效。 DoubleDelta编解码器有效地压缩了时间戳，而Gorilla编解码器则有效地压缩了值。例如，要获取有效存储的表，可以按以下配置创建它：

CREATE TABLE codec_example
(
    timestamp DateTime CODEC(DoubleDelta),
    slow_values Float32 CODEC(Gorilla)
)
ENGINE = MergeTree()

临时表（Temporary Tables）

ClickHouse支持具有以下特征的临时表：

会话结束或者连接断开，临时表都会消失。
临时表仅使用Memory引擎。
无法为临时表指定数据库。它是在数据库外部创建的。
无法在所有群集服务器上（通过使用ON CLUSTER）创建带有分布式DDL查询的临时表：此表仅存在于当前会话中。
如果一个临时表与另一个表具有相同的名称，并且查询指定表名而不指定DB，则将使用该临时表。
对于分布式查询处理，查询中使用的临时表将传递到远程服务器。

CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
)

在大多数情况下，不是手动创建临时表，而是在将外部数据用于查询或分布式（GLOBAL）IN时创建。

可以使用ENGINE = Memory的表代替临时表。但是使用ENGINE = Memory的表会留下表结构，临时表表结构都不会留下。

CREATE VIEW

创建一个新视图。有两种类型的视图：普通（normal）视图和实体（Materialized）视图。

普通（normal）视图

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] AS SELECT ...
--普通视图不会存储任何数据。 
--他们只是在每次访问时从另一个表中读取数据。 
--从视图读取时，保存的查询在FROM子句中用作子查询。

--举例来说，假设您已经创建了一个视图：
CREATE VIEW view AS SELECT ...

--运行一个查询:
SELECT a, b, c FROM view

--此查询和使用子查询完全等效
SELECT a, b, c FROM (SELECT ...)

实体（Materialized）视图

CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

实体视图会存储由相应的SELECT查询转换的数据。

当创建不带TO [db].[table]的视图时，您必须指定ENGINE –用于存储数据的表引擎。

当创建带TO [db].[table]的视图时不得使用POPULATE。

实体视图的使用方式为：当插入到一张表中数据时，插入的这部分数据会将转换后的结果插入视图中。

ClickHouse中的物化视图的实现更像是插入触发器。如果视图查询中存在某种汇总，则仅应用于新插入的数据批次。对源表的现有数据进行的任何更改（例如更新，删除，删除分区等）都不会更改实例化视图。

SELECT查询可以包含DISTINCT，GROUP BY，ORDER BY，LIMIT...请注意，相应的转换是对插入数据的每个块独立进行的。例如，如果设置了GROUP BY，则数据将在插入过程中聚合，但仅在单个插入数据包中聚合。数据将不会进一步汇总。（使用独立执行数据聚合的引擎（例如SummingMergeTree）时是例外情况。）

在物化视图上执行ALTER查询存在局限性，因此可能不方便。

如果使用了TO [db.]name，那么可以DETACH 视图，对目标表运行ALTER，然后附加先前分离（DETACH）的视图。

视图看起来与普通表相同。例如，它们在SHOW TABLES查询的结果中列出。

要删除视图，请使用DROP TABLE。

POPULATE修饰符

如果指定POPULATE，当创建实体视图时，表中的数据也会插入到视图中，就像语句CREATE TABLE ... AS SELECT ...一样。

如果不指定POPULATE，视图将仅包含创建后插入表中的数据。我们不建议使用POPULATE，因为在视图创建期间插入表中的数据不会被插入其中。

CREATE DICTIONARY

用给定的结构（structure），源（source），布局（layout）和生存期（lifetime）创建一个新的外部字典（ external dictionary ）。

CREATE DICTIONARY [IF NOT EXISTS] [db.]dictionary_name [ON CLUSTER cluster]
(
    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})

外部字典结构由属性组成。字典属性的指定类似于表列。唯一需要指定的属性是它的类型，所有其他属性都可以具有默认值。

ON CLUSTER子句允许在群集上创建字典，Distributed DDL.

根据字典布局，可以将一个或多个属性指定为字典键。

有关更多信息，请参见外部词典部分。

CREATE USER

创建一个用户帐户。

CREATE USER [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster_name]
    [IDENTIFIED [WITH {NO_PASSWORD|PLAINTEXT_PASSWORD|SHA256_PASSWORD|SHA256_HASH|DOUBLE_SHA1_PASSWORD|DOUBLE_SHA1_HASH}] BY {'password'|'hash'}]
    [HOST {LOCAL | NAME 'name' | REGEXP 'name_regexp' | IP 'address' | LIKE 'pattern'} [,...] | ANY | NONE]
    [DEFAULT ROLE role [,...]]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

ON CLUSTER子句允许在群集上创建用户，请参阅Distributed DDL.

身份验证

用户识别有多种方式：

IDENTIFIED WITH no_password
IDENTIFIED WITH plaintext_password BY 'qwerty'
IDENTIFIED WITH sha256_password BY 'qwerty' or IDENTIFIED BY 'password'
IDENTIFIED WITH sha256_hash BY 'hash'
IDENTIFIED WITH double_sha1_password BY 'qwerty'
IDENTIFIED WITH double_sha1_hash BY 'hash'

用户主机

用户主机是可以与ClickHouse服务器建立连接的主机。可以通过以下方式在HOST查询部分中指定主机：

HOST IP 'ip_address_or_subnetwork' — 用户只能从指定的IP地址或子网连接到ClickHouse服务器。例如：HOST IP '192.168.0.0/16'，HOST IP '2001：DB8 :: / 32'。在生产环境中使用时，仅指定主机IP元素（IP地址及其掩码），因为使用host和host_regexp可能会导致额外的延迟。
HOST ANY — 用户可以从任何位置进行连接。这是默认选项。
HOST LOCAL — 用户只能在本地连接。
HOST NAME 'fqdn' — 可以将用户主机指定为FQDN。例如，HOST NAME “ mysite.com”。
HOST NAME REGEXP 'regexp' — 指定用户主机时可以使用pcre正则表达式。例如，HOST NAME REGEXP '.*\.mysite\.com'.
HOST LIKE 'template' — 允许您使用LIKE运算符过滤用户主机。例如，HOST LIKE '%' 等同于HOST ANY，HOST LIKE'％.mysite.com' 过滤mysite.com域中的所有主机。

指定主机的另一种方法是在用户名后使用@语法。例子：

CREATE USER mira@'127.0.0.1' — 等同于HOST IP 语法.
CREATE USER mira@'localhost' — 等同于 HOST LOCAL 语法.
CREATE USER mira@'192.168.%.%' — 等同于 HOST LIKE 语法..

ClickHouse将user_name @'address'视为一个整体用户名。因此，从技术上讲，您可以创建具有相同user_name和不同address的多个用户。但是不建议这么做。

举例

--创建有密码保护的用户
CREATE USER mira HOST IP '127.0.0.1' IDENTIFIED WITH sha256_password BY 'qwerty'

--创建有两个身份的用户
CREATE USER john DEFAULT ROLE role1, role2

--修改用户角色为全部
ALTER USER user DEFAULT ROLE ALL

--修改用户角色为除了role1, role2的全部
ALTER USER john DEFAULT ROLE ALL EXCEPT role1, role2

CREATE ROLE

创建一个新角色。角色是一组特权。分配了一个角色的用户将获得该角色的所有特权。

CREATE ROLE [IF NOT EXISTS | OR REPLACE] name
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]

管理角色

可以为一个用户分配多个角色。用户可以通过SET ROLE语句以任意组合应用分配的角色。特权的最终范围是所有已应用角色的所有特权的组合集合。

在用户登录时应用默认角色。要设置默认角色，请使用SET DEFAULT ROLE语句或ALTER USER语句。

如果有权限直接被赋予了用户账户，那么该用户的权限将是它拥有的权限+它所具备的角色的权限。

要撤消角色，请使用REVOKE语句。

要删除角色，请使用DROP ROLE语句。删除的角色会自动从分配了该角色的所有用户和角色中撤消。

举例

--创建角色accountant
--赋予该角色对数据库db使用SELECT的权限
CREATE ROLE accountant;
GRANT SELECT ON db.* TO accountant;

--将角色分配给用户mira：
GRANT accountant TO mira;

--分配角色后，用户可以应用它并执行允许的查询。
SET ROLE accountant;
SELECT * FROM db.*;

CREATE ROW POLICY

创建一个行策略，控制用户能从表中读取的内容。

CREATE [ROW] POLICY [IF NOT EXISTS | OR REPLACE] policy_name [ON CLUSTER cluster_name] ON [db.]table
    [AS {PERMISSIVE | RESTRICTIVE}]
    [FOR SELECT]
    [USING condition]
    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]

ON CLUSTER子句允许在群集上创建行策略，请参阅Distributed DDL.

AS 子句

使用此部分，您可以创建许可或限制性策略。

Permissive policy 授予对行的访问权限。允许使用OR将不同的针对同一张表的政策组合在一起

Restrictive policy 限制对行的访问权限。允许使用AND将不同的针对同一张表的政策组合在一起

如果您设置Restrictive policy 但未设置Permissive policy ，则用户将无法从表中获取任何行。

TO 子句

在“ TO”部分，您可以提供角色和用户的混合列表，例如CREATE ROW POLICY ... TO accountant, john@localhost.

举例

CREATE ROW POLICY filter ON mydb.mytable FOR SELECT USING a<1000 TO accountant, john@localhost

CREATE ROW POLICY filter ON mydb.mytable FOR SELECT USING a<1000 TO ALL EXCEPT mira

CREATE QUOTA

创建可以分配给用户或角色的配额。

CREATE QUOTA [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster_name]
    [KEYED BY {'none' | 'user name' | 'ip address' | 'client key' | 'client key or user name' | 'client key or ip address'}]
    [FOR [RANDOMIZED] INTERVAL number {SECOND | MINUTE | HOUR | DAY | WEEK | MONTH | QUARTER | YEAR}
        {MAX { {QUERIES | ERRORS | RESULT ROWS | RESULT BYTES | READ ROWS | READ BYTES | EXECUTION TIME} = number } [,...] |
         NO LIMITS | TRACKING ONLY} [,...]]
    [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]

ON CLUSTER子句允许在群集上创建配额，请参阅分布式DDL。

举例

将当前用户的查询数限制为，15个月内最多123个查询：

CREATE QUOTA qA FOR INTERVAL 15 MONTH MAX QUERIES 123 TO CURRENT_USER

CREATE SETTINGS PROFILE

创建可以分配给用户或角色的设置配置文件。

CREATE SETTINGS PROFILE [IF NOT EXISTS | OR REPLACE] TO name [ON CLUSTER cluster_name]
    [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | INHERIT 'profile_name'] [,...]

举例

--创建profile max_memory_usage_profile，
--该profil具有max_memory_usage设置,并具有最小最大约束
--将其分配给用户robin：
CREATE SETTINGS PROFILE max_memory_usage_profile SETTINGS max_memory_usage = 100000001 MIN 90000000 MAX 110000000 TO robin

clickhouse的SQL参考——（二）create

参考资料

总览

CREATE DATABASE

子句

IF NOT EXISTS

ON CLUSTER

ENGINE

CREATE TABLE

语法形式

使用显示语法结构

使用其他表的结构

使用表函数返回的结构

默认值

DEFAULT子句

MATERIALIZED子句

ALIAS子句

约束（Constraints）

TTL 表达式

列压缩

General Purpose Codecs

specialized codecs

临时表（Temporary Tables）

CREATE VIEW

普通（normal）视图

实体（Materialized）视图

POPULATE修饰符

CREATE DICTIONARY

CREATE USER

身份验证

用户主机

举例

CREATE ROLE

管理角色

举例

CREATE ROW POLICY

AS 子句

TO 子句

举例

CREATE QUOTA

举例

CREATE SETTINGS PROFILE

举例

浏览过的版块