客快物流大数据项目（九十七）：ClickHouse的SQL语法

2022-12-28 17:15:48 浏览数 (2)

​ClickHouse的SQL语法

一、常用的SQL命令

作用

SQL

列出所有数据库

show databases;

进入某一个数据库

use dbName;

列出数据库中所有的表

show tables;

创建数据库

create database [if not exists] dbName;

删除数据库

drop database dbName;

创建表

create [temporary] table [if not exists] tableName [ON CLUSTER cluster] ( fieldName dataType ) engine = EngineName(parameters);

清空表

truncate table tableName;

删除表

drop table tableName;

创建视图

create view view_name as select ...

创建物化视图

create [MATERIALIZED] view [if not exists] [db.]tableName [to [db.]name] [engine=engine] [populate] as select ...

二、​​​​​​​​​​​​​​select查询语法

ClickHouse中完整select的查询语法如下（除了SELECT关键字和expr_list以外，蓝色的字句都是可选的）：

代码语言：javascript复制
SELECT [DISTINCT] expr_list
    [FROM [db.]table | (subquery) | table_function] [FINAL]
    [SAMPLE sample_coeff]
    [ARRAY JOIN ...]
    [GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
    [PREWHERE expr]
    [WHERE expr]
    [GROUP BY expr_list] [WITH TOTALS]
    [HAVING expr]
    [ORDER BY expr_list]
    [LIMIT [n, ]m]
    [UNION ALL ...]
    [INTO OUTFILE filename]
    [FORMAT format]
    [LIMIT n BY columns]

如果查询中不包含DISTINCT，GROUP BY，ORDER BY子句以及IN和JOIN子查询，那它将仅使用O(1)数量的内存来完全流式的处理查询，否则这个查询将消耗大量的内存，除非你指定了这些系统配置：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。它们规定了可以使用外部排序（将临时表存储到磁盘中）以及外部聚合，目前系统不存在关于Join的配置。

  • DISTINCT子句

如果使用了DISTINCT子句，则会对结果中的完全相同的行进行去重。在GROUP BY不包括聚合函数，并对全部SELECT部分都包含在GROUP BY中时的作用一样。但该子句与GROUP BY子句存在以下几点不同：

可以与GROUP BY配合使用；

当不存在ORDER BY子句但存在LIMIT子句时，查询将在同时满足DISTINCT与LIMIT的情况下立即停止查询；

在处理数据的同时输出结果，并不是等待整个查询全部完成。

在SELECT表达式中存在Array类型的列时，不能使用DISTINCT。

  • FROM子句

如果查询中不包含FROM子句则会读取system.one。 system.one中仅包含一行数据（此表实现了与其他数据库管理系统中的DUAL相同的功能）。

代码语言：javascript复制
select 1 as id,'zhangsan' as name

select 1 as id,'zhangsan' as name from system.one

是等价的。

FROM子句规定了将从哪个表、或子查询、或表函数中读取数据；同时ARRAY JOIN子句和JOIN子句也可以出现在这里。

可以使用包含在括号里的子查询来替代表，在这种情况下，子查询的处理将会构建在外部的查询内。不同于SQL标准，子查询后无需指定别名。为了兼容，你可以在子查询后添加‘AS 别名’，但是指定的名字不能被使用在任何地方。

也可以使用表函数来代替表，有关信息，参见“表函数”。

执行查询时，在查询中列出的所有列都将从对应的表中提取数据；如果你使用的是子查询的方式，则任何在外部查询中没有使用的列，子查询将从查询中忽略它们；如果你的查询没有列出任何的列（如SELECT count() FROM t），则将额外的从表中提取一些列（最好的情况下是最小的列），以便计算行数。

最后的FINAL修饰符仅能够被使用在SELECT from CollapsingMergeTree场景中。当你为FROM指定了FINAL修饰符时，你的查询结果将会在查询过程中被聚合。需要注意的是，在这种情况下，查询将在单个流中读取所有相关的主键列，同时对需要的数据进行合并。这会导致查询更慢。在大多数情况下，你应该避免使用FINAL修饰符。

  • SAMPLE子句

SAMPLE是ClickHouse中的近似查询处理，它只能工作在MergeTree*系列的表中，并且在创建表时需要显示指定采样表达式。

SAMPLE子句可以使用SAMPLE k来表示，其中k可以是0到1的小数值，或者是一个足够大的正整数值。当k为0到1的小数时，查询将使用k作为百分比选取数据。

例如，SAMPLE 0.1查询只会检索数据总量的10%。当k为一个足够大的正整数时，查询将使用'k'作为最大样本数。例如，SAMPLE 1000查询只会检索最多1000行数据，使用相同的采样率得到的结果总是一致的。

  • ARRAY JOIN子句

ARRAY JOIN子句可以帮助查询进行与数组和nested数据类型的连接。它有点类似arrayJoin函数，但它的功能更广泛。ARRAY JOIN本质上等同于INNERT JOIN数组。

创建tbl_test_array_join表：

create table tbl_test_array_join(str String, arr Array(Int8)) engine=Memory;

插入数据：

insert into tbl_test_array_join(str,arr) values('a',[1,3,5]),('b',[2,4,6]);

使用ARRAY JOIN：

select str,arr,arrItem from tbl_test_array_join ARRAY JOIN arr as arrItem;

  • JOIN 子句

JOIN子句用于连接数据，作用与SQL的JOIN的定义相同。需要注意的是JOIN与ARRAY JOIN没有任何关系。

可以使用具体的tableName来代替<left_subquery>与<right_subquery>。这与使用SELECT * FROM table子查询的方式相同。除非你的表是[Join](../operations/table_engines/join.md支持的JOIN类型：INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN和CROSS JOIN。默认的OUTER关键字可以省略不写。

在使用ALL修饰符对JOIN进行修饰时，如果右表中存在多个与左表关联的数据，那么系统则将右表中所有可以与左表关联的数据全部返回在结果中。这与SQL标准的JOIN行为相同。

在使用ANY修饰符对JOIN进行修饰时，如果右表中存在多个与左表关联的数据，那么系统仅返回第一个与左表匹配的结果。如果左表与右表一一对应，不存在多余的行时，ANY与ALL的结果相同。

可以在会话中通过设置join_default_strictness来指定默认的JOIN修饰符。

当使用普通的JOIN时，查询将被发送给远程的服务器，并在这些远程服务器上生成右表并与它们关联，即右表来自于各个服务器本身。

当使用GLOBAL ... JOIN，首先会在请求服务器上计算右表并以临时表的方式将其发送到所有服务器。这时每台服务器将直接使用它进行计算。建议从子查询中删除所有JOIN不需要的列。当执行JOIN查询时，因为与其他阶段相比没有进行执行顺序的优化：JOIN优先于WHERE与聚合执行。因此，为了显示的指定执行顺序，建议使用子查询的方式执行JOIN。子查询不允许设置别名或在其他地方引用它们。USING中指定的列必须在两个子查询中具有相同的名称，而其他列必须具有不同的名称。可以通过使用别名的方式来更改子查询中的列名。USING子句使用的是等值连接。

右表（子查询的结果）将会保存在内存中。如果没有足够的内存，则无法运行JOIN。

只能在查询中指定一个JOIN。若要运行多个JOIN，你可以将它们放入子查询中。每次运行相同的JOIN查询，都会重新计算（不缓存结果）。为了避免这种情况，可以使用‘Join’引擎，它是一个预处理的Join数据结构，总是保存在内存中。在一些场景下，使用IN代替JOIN将会得到更高的效率。在各种类型的JOIN中，最高效的是ANY LEFT JOIN，然后是ANY INNER JOIN，效率最差的是ALL LEFT JOIN以及ALL INNER JOIN。

  • WHERE子句

如果使用WHERE子句, 则在该子句中必须包含一个UInt8类型的表达式。这个表达是是一个带有比较和逻辑的表达式，它会在所有数据转换前用来过滤数据。如果在支持索引的数据库表引擎中，这个表达式将被评估是否使用索引。

  • PREWHERE子句

PREWHERE子句与WHERE子句的意思大致相同，在一个查询中如果同时指定PREWHERE和WHERE，在这种情况下，PREWHERE优先于WHERE。

当使用PREWHERE时，首先只读取PREWHERE表达式中需要的列。然后在根据PREWHERE执行的结果读取其他需要的列。

如果在过滤条件中有少量不适合索引过滤的列，但是它们又可以提供很强的过滤能力。这时使用PREWHERE能减少数据的读取。但PREWHERE字句仅支持*MergeTree系列引擎，不适合用于已经存在于索引中的列，因为当列已经存在于索引中的情况下，只有满足索引的数据块才会被读取。如果将'optimize_move_to_prewhere'设置为1时，但在查询中不包含PREWHERE，则系统将自动的把适合PREWHERE表达式的部分从WHERE中抽离到PREWHERE中。

  • GROUP BY子句

如果使用了GROUP BY子句，则在该子句中必须包含一个表达式列表。其中每个表达式将会被称之为“key”。SELECT，HAVING，ORDER BY子句中的表达式列表必须来自于这些“key”或聚合函数。被选择的列中不能包含非聚合函数或key之外的其他列。如果查询表达式列表中仅包含聚合函数，则可以省略GROUP BY子句，这时会假定将所有数据聚合成一组空“key”。

GROUP BY子句会为遇到的每一个不同的key计算一组聚合函数的值。在GROUP BY子句中不支持使用Array类型的列。常量不能作为聚合函数的参数传入聚合函数中，例如sum(1)。

  • WITH TOTALS修饰符

如果使用了WITH TOTALS修饰符，你将会在结果中得到一个被额外计算出的行。在这一行中将包含所有key的默认值（零或者空值），以及所有聚合函数对所有被选择数据行的聚合结果。

  • GROUP BY使用外部存储设备

你可以在GROUP BY中允许将临时数据转存到磁盘上来限制对内存的使用。 max_bytes_before_external_group_by这个配置确定了在GROUP BY中启动将临时数据转存到磁盘上的内存阈值。如果你将它设置为0（这是默认值），这项功能将被禁用。如果使用时建议把max_memory_usage设置为max_bytes_before_external_group_by的2倍。

  • LIMIT子句

LIMIT m用于在查询结果中选择前m行数据；LIMIT n, m 用于在查询结果中选择从n行开始的m行数据，但n和m这两个参数必须是正整数。如果没有指定ORDER BY子句，则结果的顺序是不确定的。

  • LIMIT N BY子句

LIMIT N BY子句和LIMIT没有关系，LIMIT N BY COLUMNS子句可以用来在每一个COLUMNS分组中求得最大的N行数据。我们可以将它们同时用在一个查询中。LIMIT N BY子句中可以包含任意多个分组字段表达式列表。

  • HAVING子句

HAVING子句可以用来过滤GROUP BY之后的数据，类似于WHERE子句。WHERE与HAVING不同之处在于WHERE在聚合前(GROUP BY)执行，HAVING在聚合后执行。如果不存在聚合，则不能使用HAVING。

  • ORDER BY子句

如果使用ORDER BY子句，则该子句中必须存在一个表达式列表，表达式列表中每一个表达式都可以分配一个DESC（降序）或ASC（升序），如果没有显示指定则默认以ASC方式进行排序。当对浮点类型的列排序时，不管排序的顺序如何，如果使用升序排序时，NaNs好像比所有值都要大。如果使用降序排序时，NaNs好像比所有值都小。

  • FORMAT子句

'FORMAT format'子句用于指定返回数据的格式，使用它可以方便的转换或创建数据的转储。如果不存在FORMAT子句，则使用默认的格式，这将取决与DB的配置以及所使用的客户端。对于批量模式的HTTP客户端和命令行客户端而言，默认的格式是TabSeparated。对于交互模式下的命令行客户端，默认的格式是PrettyCompact（它有更加美观的格式）。

当使用命令行客户端时，数据以内部高效的格式在服务器和客户端之间进行传递。客户端将单独的解析FORMAT子句，以帮助数据格式的转换，会减轻网络和服务器的负载。

  • UNION ALL子句

仅支持UNION ALL，不支持其他UNION规则(如UNION DISTINCT)。如果需要使用UNION DISTINCT，可以使用UNION ALL中包含SELECT DISTINCT的子查询的方式。UNION ALL中的查询可以同时运行，它们的结果将被混合到一起，这些查询的结果必须相同（列的数量和类型）。列名不同也是允许的，在这种情况下最终结果的列名将从第一个查询中获取。UNION会为查询之间进行类型转换。例如，如果组合的两个查询中包含相同的字段，并且是类型兼容的Nullable和non-Nullable，则结果将会将该字段转换为Nullable类型的字段。

作为UNION ALL查询的部分不能包含在括号内。ORDER BY与LIMIT子句应该被应用在每个查询中，而不是最终的查询中。如果需要做最终结果转换则需要将UNION ALL作为一个子查询包含在FROM子句中。

三、insert into语法

ClickHouse中完整insert的主要用于向系统中添加数据, 语法如下:

  • 语法1：
代码语言：javascript复制
INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23)...

使用语法1时，如果表存在但要插入的数据不存在，如果有DEFAULT表达式的列就根据DEFAULT表达式填充值。如果没有DEFAULT表达式的列则填充零或空字符串。如果strict_insert_defaults=1（开启了严格模式）则必须在insert中写出所有没定义DEFAULT表达式的列。

  • 语法2：
代码语言：javascript复制
INSERT INTO [db.]table [(c1, c2, c3)] FORMAT format_name data_set

使用语法2时，数据可以是ClickHouse支持的任何输入输出格式传递给INSERT，但format_name必须显示的指定。

  • 语法3：
代码语言：javascript复制
INSERT INTO [db.]table [(c1, c2, c3)] FORMAT Values (v11, v12, v13)...

语法3所用的输入格式就与语法1中INSERT ... VALUES的中使用的输入格式相同。

  • 语法4：
代码语言：javascript复制
INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...

语法4是使用SELECT的结果写入到表中，select中的列类型必须与table中的列类型位置严格一致，列名可以不同，但类型必须相同。

  • 注意

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

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

如果在写入的数据中包含多个月份的混合数据时，将会显著的降低INSERT的性能。为了避免这种情况，可以让数据总是以尽量大的batch进行写入，如每次写入100000行；数据在写入ClickHouse前预先的对数据进行分组。

在进行INSERT时将会对写入的数据进行一些处理，按照主键排序，按照月份对数据进行分区、数据总是被实时的写入、写入的数据已经按照时间排序，这几种情况下，性能不会出现下降。

四、​​​​​​​​​​​​​​alter语法

ClickHouse中的ALTER只支持MergeTree系列，Merge和Distributed引擎的表

基本语法：

代码语言：javascript复制
ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|MODIFY COLUMN ...

参数解析：

  • ADD COLUMN – 向表中添加新列
  • DROP COLUMN – 在表中删除列
  • MODIFY COLUMN – 更改列的类型

案例演示：

  • 创建一个MergerTree引擎的表
代码语言：javascript复制
CREATE TABLE mt_table (
                          date  Date,
                          id UInt8,
                          name String
) ENGINE=MergeTree() partition by toYYYYMMDD(date) order by id settings index_granularity=8192;

  • 向表中插入一些值
代码语言：javascript复制
insert into mt_table values ('2022-12-01', 1, 'zhangsan');
insert into mt_table values ('2022-09-01', 2, 'lisi');
insert into mt_table values ('2022-12-03', 3, 'wangwu');

  • 在末尾添加一个新列age
代码语言：javascript复制
:)alter table mt_table add column age UInt8
:)desc mt_table
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ date │ Date   │              │                    │
│ id   │ UInt8  │              │                    │
│ name │ String │              │                    │
│ age  │ UInt8  │              │                    │
└──────┴────────┴──────────────┴────────────────────┘
:) select * from mt_table
┌───────date─┬─id─┬─name─┬─age─┐
│ 2022-09-01 │  2 │ lisi │   0 │
└────────────┴────┴──────┴─────┘
┌───────date─┬─id─┬─name─────┬─age─┐
│ 2022-12-01 │  1 │ zhangsan │   0 │
│ 2022-12-03 │  3 │ wangwu   │   0 │
└────────────┴────┴──────────┴─────┘

  • 更改age列的类型
代码语言：javascript复制
:)alter table mt_table modify column age UInt16
:)desc mt_table

┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ date │ Date   │              │                    │
│ id   │ UInt8  │              │                    │
│ name │ String │              │                    │
│ age  │ UInt16 │              │                    │
└──────┴────────┴──────────────┴────────────────────┘

  • 删除刚才创建的age列
代码语言：javascript复制
:)alter table mt_table drop column age
:)desc mt_table
┌─name─┬─type───┬─default_type─┬─default_expression─┐
│ date │ Date   │              │                    │
│ id   │ UInt8  │              │                    │
│ name │ String │              │                    │
└──────┴────────┴──────────────┴────────────────────┘
数据库 sql大数据

0 人点赞