一、ClickHouse SQL 语句

语句表示可以使用 SQL 查询执行的各种操作。每种类型的语句都有自己的语法和用法详细信息，这些语法和用法详细信息单独描述如下所示:

1、SELECT

SELECT 查询执行数据检索。 默认情况下，请求的数据返回给客户端，同时结合 INSERT INTO 可以被转发到不同的表。

语法

[WITH expr_list|(subquery)]
SELECT [DISTINCT] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[UNION ALL ...]
[INTO OUTFILE filename]
[FORMAT format]

所有子句都是可选的，但紧接在 SELECT 后面的必需表达式列表除外，更详细的请看 下面.

每个可选子句的具体内容在单独的部分中进行介绍，这些部分按与执行顺序相同的顺序列出:

• WITH 子句

• FROM 子句

• SAMPLE 子句

• JOIN 子句

• PREWHERE 子句

• WHERE 子句

• GROUP BY 子句

• LIMIT BY 子句

• HAVING 子句

• SELECT 子句

• DISTINCT 子句

• LIMIT 子句

• UNION ALL 子句

• INTO OUTFILE 子句

• FORMAT 子句

SELECT 子句

表达式 指定 SELECT 子句是在上述子句中的所有操作完成后计算的。 这些表达式的工作方式就好像它们应用于结果中的单独行一样。 如果表达式 SELECT 子句包含聚合函数，然后ClickHouse将使用 GROUP BY 聚合参数应用在聚合函数和表达式上。

如果在结果中包含所有列，请使用星号 (*）符号。 例如, SELECT * FROM ....

将结果中的某些列与 re2 正则表达式匹配，可以使用 COLUMNS 表达。

COLUMNS('regexp')

例如表:

CREATE TABLE default.col_names (aa Int8, ab Int8, bc Int8) ENGINE = TinyLog

以下查询所有列名包含 a 。

SELECT COLUMNS('a') FROM col_names

┌─aa─┬─ab─┐
│  1 │  1 │
└────┴────┘

所选列不按字母顺序返回。

您可以使用多个 COLUMNS 表达式并将函数应用于它们。

例如:

SELECT COLUMNS('a'), COLUMNS('c'), toTypeName(COLUMNS('c')) FROM col_names

┌─aa─┬─ab─┬─bc─┬─toTypeName(bc)─┐
│  1 │  1 │  1 │ Int8           │
└────┴────┴────┴────────────────┘

返回的每一列 COLUMNS 表达式作为单独的参数传递给函数。 如果函数支持其他参数，您也可以将其他参数传递给函数。 使用函数时要小心，如果函数不支持传递给它的参数，ClickHouse将抛出异常。

例如:

SELECT COLUMNS('a') + COLUMNS('c') FROM col_names

Received exception from server (version 19.14.1):Code: 42. DB::Exception: Received from localhost:9000. DB::Exception: Number of arguments for function plus doesn't match: passed 3, should be 2.

该例子中, COLUMNS('a') 返回两列: aa 和 ab. COLUMNS('c') 返回 bc 列。 该 + 运算符不能应用于3个参数，因此ClickHouse抛出一个带有相关消息的异常。

匹配的列 COLUMNS 表达式可以具有不同的数据类型。 如果 COLUMNS 不匹配任何列，并且是在 SELECT 唯一的表达式，ClickHouse则抛出异常。

星号

您可以在查询的任何部分使用星号替代表达式。进行查询分析、时，星号将展开为所有表的列（不包括 MATERIALIZED 和 ALIAS 列）。 只有少数情况下使用星号是合理的:

• 创建转储表时。

• 对于只包含几列的表，例如系统表。

• 获取表中列的信息。 在这种情况下，设置 LIMIT 1. 但最好使用 DESC TABLE 查询。

• 当对少量列使用 PREWHERE 进行强过滤时。

• 在子查询中（因为外部查询不需要的列从子查询中排除）。

在所有其他情况下，我们不建议使用星号，因为它只给你一个列DBMS的缺点，而不是优点。 换句话说，不建议使用星号。

极端值

除结果之外，还可以获取结果列的最小值和最大值。 要做到这一点，设置 extremes 设置为1。 最小值和最大值是针对数字类型、日期和带有时间的日期计算的。 对于其他类型列，输出默认值。分别的额外计算两行 – 最小值和最大值。 这额外的两行采用输出格式为 JSON*, TabSeparated*，和 Pretty* formats，与其他行分开。 它们不以其他格式输出。为 JSON* 格式时，极端值单独的输出在 extremes’ 字段。 为 TabSeparated* 格式时，此行来的主要结果集后，然后显示 ‘totals’ 字段。 它前面有一个空行（在其他数据之后）。 在 Pretty* 格式时，该行在主结果之后输出为一个单独的表，然后显示 ‘totals’ 字段。极端值在 LIMIT 之前被计算，但在 LIMIT BY 之后被计算. 然而，使用 LIMIT offset, size， offset 之前的行都包含在 extremes. 在流请求中，结果还可能包括少量通过 LIMIT 过滤的行.

备注

您可以在查询的任何部分使用同义词 (AS 别名）。

GROUP BY 和 ORDER BY 子句不支持位置参数。 这与MySQL相矛盾，但符合标准SQL。 例如, GROUP BY 1, 2 将被理解为根据常量分组 (i.e. aggregation of all rows into one).

实现细节

如果查询省略 DISTINCT, GROUP BY ， ORDER BY ， IN ， JOIN 子查询，查询将被完全流处理，使用O(1)量的RAM。 若未指定适当的限制，则查询可能会消耗大量RAM:

• max_memory_usage

• max_rows_to_group_by

• max_rows_to_sort

• max_rows_in_distinct

• max_bytes_in_distinct

• max_rows_in_set

• max_bytes_in_set

• max_rows_in_join

• max_bytes_in_join

• max_bytes_before_external_sort

• max_bytes_before_external_group_by

有关详细信息，请参阅部分 “Settings”. 可以使用外部排序（将临时表保存到磁盘）和外部聚合。

2、INSERT INTO

INSERT INTO 语句

INSERT INTO 语句主要用于向系统中添加数据.

查询的基本格式:

INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...

您可以在查询中指定要插入的列的列表，如：[(c1, c2, c3)]。您还可以使用列匹配器的表达式，例如*和/或修饰符，例如 APPLY， EXCEPT， REPLACE。

例如，考虑该表:

SHOW CREATE insert_select_testtable;

CREATE TABLE insert_select_testtable(    `a` Int8,    `b` String,    `c` Int8)ENGINE = MergeTree()ORDER BY a

INSERT INTO insert_select_testtable (*) VALUES (1, 'a', 1) ;

如果要在除了'b'列以外的所有列中插入数据，您需要传递和括号中选择的列数一样多的值:

INSERT INTO insert_select_testtable (* EXCEPT(b)) Values (2, 2);

SELECT * FROM insert_select_testtable;

┌─a─┬─b─┬─c─┐
│ 2 │   │ 2 │
└───┴───┴───┘
┌─a─┬─b─┬─c─┐
│ 1 │ a │ 1 │
└───┴───┴───┘

在这个示例中，我们看到插入的第二行的a和c列的值由传递的值填充，而b列由默认值填充。

对于存在于表结构中但不存在于插入列表中的列，它们将会按照如下方式填充数据：

• 如果存在DEFAULT表达式，根据DEFAULT表达式计算被填充的值。

• 如果没有定义DEFAULT表达式，则填充零或空字符串。

如果 strict_insert_defaults=1，你必须在查询中列出所有没有定义DEFAULT表达式的列。

数据可以以ClickHouse支持的任何 输入输出格式 传递给INSERT。格式的名称必须显示的指定在查询中：

INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_set

例如，下面的查询所使用的输入格式就与上面INSERT … VALUES的中使用的输入格式相同：

INSERT INTO [db.]table [(c1, c2, c3)] FORMAT Values (v11, v12, v13), (v21, v22, v23), ...

ClickHouse会清除数据前所有的空白字符与一个换行符（如果有换行符的话）。所以在进行查询时，我们建议您将数据放入到输入输出格式名称后的新的一行中去（如果数据是以空白字符开始的，这将非常重要）。

示例:

INSERT INTO t FORMAT TabSeparated11  Hello, world!22  Qwerty

在使用命令行客户端或HTTP客户端时，你可以将具体的查询语句与数据分开发送。更多具体信息，请参考«客户端»部分。

限制

如果表中有一些限制,，数据插入时会逐行进行数据校验，如果这里面包含了不符合限制条件的数据，服务将会抛出包含限制信息的异常，这个语句也会被停止执行。

使用SELECT的结果写入

INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...

写入与SELECT的列的对应关系是使用位置来进行对应的，尽管它们在SELECT表达式与INSERT中的名称可能是不同的。如果需要，会对它们执行对应的类型转换。

除了VALUES格式之外，其他格式中的数据都不允许出现诸如now()，1 + 2等表达式。VALUES格式允许您有限度的使用这些表达式，但是不建议您这么做，因为执行这些表达式总是低效的。

系统不支持的其他用于修改数据的查询：UPDATE, DELETE, REPLACE, MERGE, UPSERT, INSERT UPDATE。 但是，您可以使用 ALTER TABLE ... DROP PARTITION查询来删除一些旧的数据。

如果 SELECT 查询中包含了 input() 函数，那么 FORMAT 必须出现在查询语句的最后。

如果某一列限制了值不能是NULL，那么插入NULL的时候就会插入这个列类型的默认数据，可以通过设置 insert_null_as_default 插入NULL。

从文件向表中插入数据

INSERT INTO [db.]table [(c1, c2, c3)] FROM INFILE file_name [COMPRESSION type] FORMAT format_name

使用上面的语句可以从客户端的文件上读取数据并插入表中，file_name 和 type 都是 String 类型，输入文件的格式 一定要在 FORMAT 语句中设置。

支持读取压缩文件。默认会去读文件的拓展名作为文件的压缩方式，或者也可以在 COMPRESSION 语句中指明，支持的文件压缩格式如下：'none'， 'gzip'， 'deflate'， 'br'， 'xz'， 'zstd'， 'lz4'， 'bz2'。

这个功能在 command-line client 和 clickhouse-local 是可用的。

样例

echo 1,A > input.csv ; echo 2,B >> input.csv
clickhouse-client --query="CREATE TABLE table_from_file (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;"
clickhouse-client --query="INSERT INTO table_from_file FROM INFILE 'input.csv' FORMAT CSV;"
clickhouse-client --query="SELECT * FROM table_from_file FORMAT PrettyCompact;"

结果:

┌─id─┬─text─┐
│  1 │ A    │
│  2 │ B    │
└────┴──────┘

插入表函数

数据可以通过 table functions 方法插入。

INSERT INTO [TABLE] FUNCTION table_func ...

例如

可以这样使用remote 表函数:

CREATE TABLE simple_table (id UInt32, text String) ENGINE=MergeTree() ORDER BY id;INSERT INTO TABLE FUNCTION remote('localhost', default.simple_table)     VALUES (100, 'inserted via remote()');SELECT * FROM simple_table;

结果:

┌──id─┬─text──────────────────┐
│ 100 │ inserted via remote() │
└─────┴───────────────────────┘

性能的注意事项

在进行INSERT时将会对写入的数据进行一些处理，按照主键排序，按照月份对数据进行分区等。所以如果在您的写入数据中包含多个月份的混合数据时，将会显著的降低INSERT的性能。为了避免这种情况：

• 数据总是以尽量大的batch进行写入，如每次写入100,000行。

• 数据在写入ClickHouse前预先的对数据进行分组。

在以下的情况下，性能不会下降：

• 数据总是被实时的写入。

• 写入的数据已经按照时间排序。

也可以异步的、小规模的插入数据，这些数据会被合并成多个批次，然后安全地写入到表中，通过设置async_insert，可以使用异步插入的方式，请注意，异步插入的方式只支持HTTP协议，并且不支持数据去重。

3、CREATE

ALTER

SYSTEM

SHOW

GRANT

REVOKE

ATTACH

CHECK TABLE

DESCRIBE TABLE

DETACH

DROP

EXISTS

KILL

OPTIMIZE

RENAME

SET

SET ROLE

TRUNCATE

USE

EXPLAIN