SQL 语法

概述

ByteHouse 的 SQL 语法即为 ClickHouse SQL。ClickHouse SQL 和 ANSI SQL （标准 SQL）的语法基本一致，绝大多数语法没有差异。关于不兼容 ANSI SQL 的部分，请官网文档参考：ClickHouse SQL语法与ANSI SQL的差异。
ClickHouse 支持的语法类型包括：

• SELECT
• INSERT INTO
• CREATE
• ALTER
• SYSTEM
• SHOW
• .....

我们在本文档中仅列出常用的 SELECT，CREATE，ALTER，INSERT INTO，DROP。其余语法可参考 社区文档。

前置概念

由于 ByteHouse 的分布式设计理念与一般数据库不同。在了解语法前，请先学习 架构概述，并了解以下两个概念：

本地表，分布式表逻辑表

• 分布式表（Distributed Table）起到分发查询、收集查询结果的作用。表引擎为 Distributed（引擎详情）
  • 对分布式表发起 Select 查询，会返回所有分片的数据。
  • 对分布式表进行 Insert Into 插入，则会根据分布式表的 Sharding_key 规则，将数据异步插入匹配的节点中。
• 本地表（Local Table）起到数据存储的作用。即引擎为 *MergeTree的表（常用的包含 HaMergeTree，HaUniqueMergeTree）。本地表的命名通常为 分布式表名_local。
  • 对本地表直接发起 Select 查询，只会返回这个节点的数据。
  • 对本地表进行 Insert Into 插入，会绕过分布式表的分片规则，直接插入到对应节点中。
• 逻辑表（Logical Table）为一组相互关联的本地表与分布式表的集合。ByteHouse 控制台会将关联的表自动组装为逻辑表。对于上层应用用户，可以仅仅暴露逻辑表，而不暴露本地表、分布式表的复杂概念。

on cluster语法

为了充分利用每个节点的计算和存储能力，需要您将 DDL/DML 语句发送到每个节点上。系统提供了一种非常方便的语法，即 on cluster <cluster_name>，将其加在 DDL/DML 语句上，即可实现将语句发送到每个节点，完成各节点上的库表元数据同步。

查询 SELECT

SELECT 语法用于执行数据检索。 默认情况下，将请求的数据返回给客户端。

语法

[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]

每个可选子句的具体内容与使用技巧在 ClickHouse 社区文档中介绍，请参阅对应部分：

• WITH 子句
• FROM 子句
• SAMPLE 子句
• JOIN 子句
• PREWHERE 子句
• WHERE 子句
• GROUP BY 子句
• LIMIT BY 子句
• HAVING 子句
• DISTINCT 子句
• LIMIT 子句
• UNION ALL 子句
• INTO OUTFILE 子句
• FORMAT 子句

示例

摘自 ClickHouse 官网。查询数据集为 New Your Taxi 数据集示例。
SQL 示例如下：

--统计不同出租车类型的打车次数
SELECT cab_type, count(*) 
    FROM trips_mergetree 
    GROUP BY cab_type;

--统计不同乘客数量的平均金额        
SELECT passenger_count, avg(total_amount) 
    FROM trips_mergetree 
    GROUP BY passenger_count;

--统计每年不同乘客数量的总打车次数
SELECT passenger_count, toYear(pickup_date) AS year, count(*) 
    FROM trips_mergetree 
    GROUP BY passenger_count, year;

--统计每年不同乘客数量不同打车距离的总打车次数，按年份与打车次数倒序排列
SELECT passenger_count, toYear(pickup_date) AS year, round(trip_distance) AS distance, count(*)
    FROM trips_mergetree
    GROUP BY passenger_count, year, distance
    ORDER BY year, count(*) DESC;

插入 INSERT INTO

概述

INSERT INTO 语句主要用于向系统中添加数据。
SELECT 语法用于执行数据检索。 默认情况下，将请求的数据返回给客户端。
基本语法格式:

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

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

• 如果存在DEFAULT表达式，根据DEFAULT表达式计算被填充的值。
• 如果没有定义 DEFAULT 表达式，则填充零或空字符

使用SELECT的结果写入

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

写入目标表的列与 SELECT 的列是一一对应的，尽管它们在 SELECT 表达式与目标表中的名称可能是不同的。
如果需要，会对插入数据执行对应的类型转换。

从文件写入数据

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

使用上面的语句可以从客户端的文件上读取数据并插入表中。

• Format 可选：ClickHouse 输入输出格式；
• COMPRESSION 可选：系统默认会去读文件的拓展名作为文件的压缩方式，或者也可以在 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    │
└────┴──────┘

这种语法比较适合小文件（≤200MB）的导入，优势是操作简单。但如果需要实现大文件的高效导入，请参考 批式导入。

创建 CREATE

新建库

该语句用于根据指定名称创建 Atomic 引擎（默认引擎）的数据库。

CREATE DATABASE [IF NOT EXISTS] db_name ON CLUSTER cluster_name

如果语句中存在IF NOT EXISTS，则当数据库已经存在时，该语句不会返回任何错误。
若创建 MaterializeMySQL 引擎，请参考 通过MaterializedMySQL导入。

新建表

对于CREATE TABLE，默认的语法为：

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

具体引擎的语法差异，请参考：HaMergeTree，HaUniqueMergeTree，Distributed。

复制表结构

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

创建一个与db2.name2具有相同结构的表，如不填 Engine，则复制的表默认和原表使用相同的表引擎。

复制表结构并填充数据

CREATE TABLE [IF NOT EXISTS] [db.]table_name On Cluster cluster_name
    ENGINE = engine AS SELECT ...

使用指定的引擎创建一个与SELECT子句的结果具有相同结构的表，并使用SELECT子句的结果填充它。这个语法仅适合本地表，请注意 SELECT子句中指定本地表。

修改 ALTER

ALTER 操作有多种形式。以下仅列出对表结构的修改操作。语法示例如下：

ALTER TABLE [db].name ON CLUSTER cluster_name
    ADD|DROP|RENAME|CLEAR|COMMENT|{MODIFY|ALTER}|MATERIALIZE COLUMN ...

增加列

ALTER TABLE [db].name ON CLUSTER cluster_name
    ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [codec] [AFTER name_after]

使用指定的name, type, codec 以及 default_expr，往表中增加新的列。
添加列仅仅是改变原有表的结构。不会对已有数据产生影响。执行完 ALTER后磁盘中也不会出现新的数据，当数据块完成合并后，磁盘中才会出现该列的数据。

删除列

ALTER TABLE [db].name ON CLUSTER cluster_name
    DROP COLUMN [IF EXISTS] name

通过指定 name 删除列。从文件系统中删除数据。由于是删除列的整个文件，该语句几乎是立即执行完成的。

ALTER TABLE visits DROP COLUMN browser

修改列

ALTER TABLE [db].name ON CLUSTER cluster_name
    MODIFY COLUMN [IF EXISTS] name [type] [default_expr] [TTL]

该语句可以改变 name 列的属性：

• Type（列类型）
• Default expression（默认值）
• TTL，有关修改列TTL的示例，请参见 Column TTL.

如果只改变了默认表达式，该语句几乎不会做任何复杂操作，几乎是立即执行完成的。
但当改变列的类型时，列的值也被转换了，如同对列使用 toType 函数一样。改变列的类型是唯一的复杂型动作 - 它改变了数据文件的内容。对于大型表，执行起来要花费较长的时间。因此，该操作不建议在生产环境下进行。

ALTER 操作限制

• 不支持对 MergeTree 家族表引擎的主键（Primary Key）或者采样键（Sampling Key）中的列进行删除。
• ALTER 操作会阻塞对表的所有读写操作。换句话说，当一个大的 SELECT 语句和 ALTER同时执行时，ALTER会等待，直到 SELECT 执行结束。与此同时，当 ALTER 运行时，新的 SQL 语句将会等待。
• 对于不存储数据的表（例如 Distributed 表）， ALTER 仅仅改变了自身的表结构，不会改变从属的表结构。

删除现有实体。 如果指定了IF EXISTS子句，如果实体不存在，这些查询不会返回错误。

DROP DATABASE

删除db数据库中的所有表，然后删除db数据库本身。

DROP DATABASE [IF EXISTS] db ON CLUSTER cluster_name

DROP TABLE

删除数据表。

DROP [TEMPORARY] TABLE [IF EXISTS] [db.]name ON CLUSTER cluster