ClickHouse SQL 语法极简教程
ClickHouse SQL 语法极简教程
ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。
在传统的行式数据库系统中，数据按如下顺序存储：
| Row | WatchID | JavaEnable | Title | GoodEvent | EventTime |
|---|---|---|---|---|---|
| #0 | 89354350662 | 1 | Investor Relations | 1 | 2016-05-18 05:19:20 |
| #1 | 90329509958 | 0 | Contact us | 1 | 2016-05-18 08:10:20 |
| #2 | 89953706054 | 1 | Mission | 1 | 2016-05-18 07:38:00 |
| #N | … | … | … | … | … |
处于同一行中的数据总是被物理的存储在一起。
常见的行式数据库系统有：MySQL、Postgres和MS SQL Server。
在列式数据库系统中，数据按如下的顺序存储：
| Row: | #0 | #1 | #2 | #N |
|---|---|---|---|---|
| WatchID: | 89354350662 | 90329509958 | 89953706054 | … |
| JavaEnable: | 1 | 0 | 1 | … |
| Title: | Investor Relations | Contact us | Mission | … |
| GoodEvent: | 1 | 1 | 1 | … |
| EventTime: | 2016-05-18 05:19:20 | 2016-05-18 08:10:20 | 2016-05-18 07:38:00 | … |
这些示例只显示了数据的排列顺序。来自不同列的值被单独存储，来自同一列的数据被存储在一起。
常见的列式数据库有： Vertica、 Paraccel (Actian Matrix，Amazon Redshift)、 Sybase IQ、 Exasol、 Infobright、 InfiniDB、 MonetDB (VectorWise， Actian Vector)、 LucidDB、 SAP HANA、 Google Dremel、 Google PowerDrill、 Druid、 kdb+。
不同的数据存储方式适用不同的业务场景，数据访问的场景包括：进行了何种查询、多久查询一次以及各类查询的比例；每种类型的查询(行、列和字节)读取多少数据；读取数据和更新之间的关系；使用的数据集大小以及如何使用本地的数据集；是否使用事务,以及它们是如何进行隔离的；数据的复制机制与数据的完整性要求；每种类型的查询要求的延迟与吞吐量等等。
系统负载越高，依据使用场景进行定制化就越重要，并且定制将会变的越精细。没有一个系统能够同时适用所有不同的业务场景。如果系统适用于广泛的场景，在负载高的情况下，要兼顾所有的场景，那么将不得不做出选择。是要平衡还是要效率？
OLAP场景的关键特征
- 绝大多数是读请求
- 数据以相当大的批次(> 1000行)更新，而不是单行更新;或者根本没有更新。
- 已添加到数据库的数据不能修改。
- 对于读取，从数据库中提取相当多的行，但只提取列的一小部分。
- 宽表，即每个表包含着大量的列
- 查询相对较少(通常每台服务器每秒查询数百次或更少)
- 对于简单查询，允许延迟大约50毫秒
- 列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)
- 处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)
- 事务不是必须的
- 对数据一致性要求低
- 每个查询有一个大表。除了他以外，其他的都很小。
- 查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中
很容易可以看出，OLAP场景与其他通常业务场景(例如,OLTP或K/V)有很大的不同， 因此想要使用OLTP或Key-Value数据库去高效的处理分析查询场景，并不是非常完美的适用方案。例如，使用OLAP数据库去处理分析请求通常要优于使用MongoDB或Redis去处理分析请求。
列式数据库更适合OLAP场景的原因
列式数据库更适合于OLAP场景(对于大多数查询而言，处理速度至少提高了100倍)，下面详细解释了原因(通过图片更有利于直观理解)：
行式
Row oriented
列式
Column oriented
看到差别了么？下面将详细介绍为什么会发生这种情况。
输入/输出
- 针对分析类查询，通常只需要读取表的一小部分列。在列式数据库中你可以只读取你需要的数据。例如，如果只需要读取100列中的5列，这将帮助你最少减少20倍的I/O消耗。
- 由于数据总是打包成批量读取的，所以压缩是非常容易的。同时数据按列分别存储这也更容易压缩。这进一步降低了I/O的体积。
- 由于I/O的降低，这将帮助更多的数据被系统缓存。
例如，查询«统计每个广告平台的记录数量»需要读取«广告平台ID»这一列，它在未压缩的情况下需要1个字节进行存储。如果大部分流量不是来自广告平台，那么这一列至少可以以十倍的压缩率被压缩。当采用快速压缩算法，它的解压速度最少在十亿字节(未压缩数据)每秒。换句话说，这个查询可以在单个服务器上以每秒大约几十亿行的速度进行处理。这实际上是当前实现的速度。
CPU
由于执行一个查询需要处理大量的行，因此在整个向量上执行所有操作将比在每一行上执行所有操作更加高效。同时这将有助于实现一个几乎没有调用成本的查询引擎。如果你不这样做，使用任何一个机械硬盘，查询引擎都不可避免的停止CPU进行等待。所以，在数据按列存储并且按列执行是很有意义的。
有两种方法可以做到这一点：
-
向量引擎：所有的操作都是为向量而不是为单个值编写的。这意味着多个操作之间的不再需要频繁的调用，并且调用的成本基本可以忽略不计。操作代码包含一个优化的内部循环。
-
代码生成：生成一段代码，包含查询中的所有操作。
这是不应该在一个通用数据库中实现的，因为这在运行简单查询时是没有意义的。但是也有例外，例如，MemSQL使用代码生成来减少处理SQL查询的延迟(只是为了比较，分析型数据库通常需要优化的是吞吐而不是延迟)。
请注意，为了提高CPU效率，查询语言必须是声明型的(SQL或MDX)， 或者至少一个向量(J，K)。 查询应该只包含隐式循环，允许进行优化。
ClickHouse有2类解析器：
完整SQL解析器（递归式解析器），以及
数据格式解析器（快速流式解析器）.
除了 INSERT 查询，其它情况下仅使用完整SQL解析器。
INSERT查询会同时使用2种解析器：
INSERT INTO t VALUES (1, 'Hello, world'), (2, 'abc'), (3, 'def')
含INSERT INTO t VALUES 的部分由完整SQL解析器处理，包含数据的部分 (1, 'Hello, world'), (2, 'abc'), (3, 'def') 交给快速流式解析器解析。通过设置参数 input_format_values_interpret_expressions，你也可以对数据部分开启完整SQL解析器。
当 input_format_values_interpret_expressions = 1 时，ClickHouse优先采用快速流式解析器来解析数据。如果失败，ClickHouse再尝试用完整SQL解析器来处理，就像处理SQL expression 一样。
数据可以采用任何格式。当CH接收到请求时，服务端先在内存中计算不超过 max_query_size 字节的请求数据（默认1 mb），然后剩下部分交给快速流式解析器。
当 INSERT 语句中使用 Values 格式时，看起来数据部分的解析和解析SELECT 中的表达式相同，但并不是这样的。 Values 格式有非常多的限制。
空白
sql语句的语法结构部分之间（标识符之间、部分符号之间、包括sql的起始和结束）可以有任意的空白字符，这些空字符类型包括：空格字符，tab制表符，换行符，CR符，换页符等。
注释
ClickHouse支持SQL风格或C语言风格的注释：
SQL风格的注释以 -- 开始，直到行末，-- 后紧跟的空格可以忽略
C语言风格的注释以 /* 开始，以 / 结束，可以跨行，同样可以省略 / 后的空格
关键字
以下场景的关键字是大小写不敏感的：
标准SQL。例如，SELECT, select 和 SeLeCt 都是允许的
在某些流行的RDBMS中被实现的关键字，例如，DateTime 和 datetime是一样的
你可以在系统表 system.data_type_families 中检查某个数据类型的名称是否是大小写敏感型。
和标准SQL相反，所有其它的关键字都是 大小写敏感的，包括函数名称。
关键字不是保留的；它们仅在相应的上下文中才会被认为是关键字。如果你使用和关键字同名的 标识符 ，需要使用双引号或反引号将它们包含起来。例如：如果表 table_name 包含列 "FROM"，那么 SELECT "FROM" FROM table_name 是合法的
标识符
标识符包括：
集群、数据库、表、分区、列的名称
函数
数据类型
表达式别名
变量名可以被括起或不括起，后者是推荐做法。
没有括起的变量名，必须匹配正则表达式 ^[a-zA-Z_][0-9a-zA-Z_]*$，并且不能和 关键字相同，合法的标识符名称：x，1，X_y__Z123等。
如果想使用和关键字同名的变量名称，或者在变量名称中包含其它符号，你需要通过双引号或反引号，例如： "id", id
字符
字符包含数字，字母，括号，NULL值等字符。
数字
数字类型字符会被做如下解析：
首先，当做64位的有符号整数，使用函数 strtoull
如果失败，解析成64位无符号整数，同样使用函数 strtoull
如果还失败了，试图解析成浮点型数值，使用函数 strtod
最后，以上情形都不符合时，返回异常
数字类型的值类型为能容纳该值的最小数据类型。
例如：1 解析成 UInt8型，256 则解析成 UInt16。更多信息，参见 数据类型
例如: 1, 18446744073709551615, 0xDEADBEEF, 01, 0.1, 1e100, -1e-100, inf, nan.
字符串
ClickHouse只支持用单引号包含的字符串。
特殊字符可通过反斜杠进行转义。下列转义字符都有相应的实际值： \b, \f, \r, \n, \t, \0, \a, \v, \xHH。其它情况下，以 \c形式出现的转义字符，当c表示任意字符时，转义字符会转换成c。这意味着你可以使用 '和\。该值将拥有String类型。
在字符串中，你至少需要对 ' 和 \ 进行转义。单引号可以使用单引号转义，例如 'It's' 和 'It''s' 是相同的。
复合字符串
数组都是使用方括号进行构造 [1, 2, 3]，元组则使用圆括号 (1, 'Hello, world!', 2)
从技术上来讲，这些都不是字符串，而是包含创建数组和元组运算符的表达式。
创建一个数组必须至少包含一个元素，创建一个元组至少包含2个元素
当元组出现在 SELECT 查询的 IN 部分时，是一种例外情形。查询结果可以包含元组，但是元组类型不能保存到数据库中（除非表采用 内存表引擎）
NULL值
代表不存在的值。
为了能在表字段中存储NULL值，该字段必须声明为 空值 类型。
根据数据的格式（输入或输出），NULL值有不同的表现形式。更多信息参见文档 数据格式
在处理 NULL时存在很多细微差别。例如，比较运算的至少一个参数为 NULL ，则该结果也是 NULL 。与之类似的还有乘法运算, 加法运算,以及其它运算。更多信息，请参阅每种运算的文档部分。
在语句中，可以通过 IS NULL 以及 IS NOT NULL 运算符，以及 isNull 、 isNotNull 函数来检查 NULL 值
函数
函数调用的写法，类似于一个标识符后接被圆括号包含的参数列表（可能为空）。与标准SQL不同，圆括号是必须的，不管参数列表是否为空。例如： now()。
函数分为常规函数和聚合函数（参见“Aggregate functions”一章）。有些聚合函数包含2个参数列表，第一个参数列表中的参数被称为“parameters”。不包含“parameters”的聚合函数语法和常规函数是一样的。
运算符
在查询解析阶段，运算符会被转换成对应的函数，使用时请注意它们的优先级。例如：
表达式 1 + 2 * 3 + 4 会被解析成 plus(plus(1, multiply(2, 3)), 4).
数据类型及数据库/表引擎
CREATE 语句中的数据类型和表引擎写法与变量或函数类似。
换句话说，它们可以包含或不包含用括号包含的参数列表。更多信息，参见“数据类型,” “数据表引擎” 和 “CREATE语句”等章节
表达式别名
别名是用户对表达式的自定义名称
expr AS alias
AS — 用于定义别名的关键字。可以对表或select语句中的列定义别名(AS 可以省略）
例如, SELECT table_name_alias.column_name FROM table_name table_name_alias.
在 CAST函数 中，AS有其它含义。请参见该函数的说明部分。
expr — 任意CH支持的表达式.
例如, SELECT column_name * 2 AS double FROM some_table.
alias — expr 的名称。别名必须符合 标识符 语法.
例如, SELECT "table t".column_name FROM table_name AS "table t".
用法注意
别名在当前查询或子查询中是全局可见的，你可以在查询语句的任何位置对表达式定义别名
别名在当前查询的子查询及不同子查询中是不可见的。例如，执行如下查询SQL: SELECT (SELECT sum(b.a) + num FROM b) - a.a AS num FROM a ,ClickHouse会提示异常 Unknown identifier: num.
如果给select子查询语句的结果列定义其别名，那么在外层可以使用该别名。例如, SELECT n + m FROM (SELECT 1 AS n, 2 AS m).
注意列的别名和表的别名相同时的情形，考虑如下示例：
CREATE TABLE t
(
a Int,
b Int
)
ENGINE = TinyLog()
SELECT
argMax(a, b),
sum(b) AS b
FROM t
Received exception from server (version 18.14.17):
Code: 184. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: Aggregate function sum(b) is found inside another aggregate function in query.
在这个示例中，先声明了表 t 以及列 b。然后，在查询数据时，又定义了别名 sum(b) AS b。由于别名是全局的，ClickHouse使用表达式 sum(b) 来替换表达式 argMax(a, b) 中的变量 b。这种替换导致出现异常。
星号
select查询中，星号可以代替表达式使用。详情请参见“select”部分
表达式
表达式是函数、标识符、字符、使用运算符的语句、括号中的表达式、子查询或星号。它也可以包含别名。
表达式列表是用逗号分隔的一个或多个表达式。
反过来，函数和运算符可以将表达式作为参数。
