ClickHouse（三）SQL语法

1.SQL基本语法

1.1.CREATE

1.1.1.CREATE DATABASE

数据库引擎：数据库目前一共支持5种引擎，如下所示：

• Ordinary：默认引擎，在绝大多数情况下我们都会使用默认引擎，使用时无须刻意声明。在此数据库下可以使用任意类型的表引擎。
• Dictionary：字典引擎，此类数据库会自动为所有数据字典创建它们的数据表
• Memory：内存引擎，用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除。
• Lazy：日志引擎，此类数据库下只能使用Log系列的表引擎
• MySQL:MySQL引擎，此类数据库下会自动拉取远端MySQL中的数据，并为它们创建MySQL表引擎的数据表

在绝大多数情况下都只需使用默认的数据库引擎。

默认数据库的实质是物理磁盘上的一个文件目录，所以在语句执行之后，ClickHouse便会在安装路径下创建数据库的文件目录:

ll /var/lib/clickhouse/data

与此同时，在metadata路径下也会一同创建用于恢复数据库的文件,在库的目录下又有恢复表的SQL语句：

ll /var/lib/clickhouse/metadata

1.1.2.CREATE TABLE

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

参数说明：

参数	意义
DEFAULT expr	默认值，用法与SQL类似。
MATERIALIZED expr	物化表达式，被该表达式指定的列不能被INSERT，因为它总是被计算出来的。 对于INSERT而言，不需要考虑这些列。 另外，在SELECT查询中如果包含星号，此列不会被查询。
ALIAS expr	别名

表的三种创建方式：

1.直接创建

2.创建一个与其他表具有相同结构的表：

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

3.使用指定的引擎创建一个与 SELECT 子句的结果具有相同结构的表，并使用 SELECT子 句的结果填充它：

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

1.1.3.表字段

表字段支持三种默认值表达式的定义方法，分别是DEFAULT、MATERIALIZED和ALIAS。无论使用哪种形式，表字段一旦被定义了默认值，它便不再强制要求定义数据类型，因为 ClickHouse 会根据默认值进行类型推断。如果同时对表字段定义了数据类型和默认值表达式，则以明确定义的数据类型为主

默认值表达式的三种定义方法之间也存在着不同之处，可以从如下三个方面进行比较：

1、数据写入：在数据写入时，只有DEFAULT类型的字段可以出现在INSERT语句中。而MATERIALIZED和ALIAS都不能被显式赋值，它们只能依靠计算取值。例如试图为MATERIALIZED类型的字段写入数据，将会得到如下的错误

DB::Exception: Cannot insert column URL,because it is MATERIALIZED column..

2、数据查询：在数据查询时，只有DEFAULT类型的字段可以通过SELECT *返回。而MATERIALIZED和ALIAS类型的字段不会出现在SELECT *查询的返回结果集中。

3、数据存储：在数据存储时，只有DEFAULT和MATERIALIZED类型的字段才支持持久化。如果使用的表引擎支持物理存储（例如TinyLog表引擎），那么这些列字段将会拥有物理存储。而ALIAS类型的字段不支持持久化，它的取值总是需要依靠计算产生，数据不会落到磁盘。

可以使用ALTER语句修改默认值，例如：

ALTER TABLE [db_name.]table MODIFY COLUMN col_name DEFAULT value;

修改动作并不会影响数据表内先前已经存在的数据。但是默认值的修改有诸多限制，例如在合并树表引擎中，它的主键字段是无法被修改的；而某些表引擎则完全不支持修改（例如 TinyLog）。

1.1.4.临时表

ClickHouse 也有临时表的概念，创建临时表的方法是在普通表的基础之上添加 TEMPORARY 关键字，它的完整语法如下所示：

CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name (
    name1 [type][DEFAULT|MATERIALIZED|ALIAS expr],
    name2 [type][DEFAULT|MATERIALIZED|ALIAS expr]
)

相比普通表而言，临时表有如下几点特殊之处：

1. 它的生命周期是会话绑定的，所以它只支持Memory表引擎，如果会话结束，数据表就会被销毁；
2. 临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数。
3. 临时表的优先级是大于普通表的。当两张数据表名称相同的时候，会优先读取临时表的数据。
4. 在ClickHouse的日常使用中，通常不会刻意使用临时表。它更多被运用在ClickHouse的内部，是数据在集群间传播的载体。

1.1.5.分区表

数据分区（partition）和数据分片（shard）是完全不同的两个概念。数据分区是针对本地数据而言的，是数据的一种纵向切分。而数据分片是数据的一种横向切分。数据分区对于一款OLAP数据库而言意义非凡：借助数据分区，在后续的查询过程中能够跳过不必要的数据目录，从而提升查询的性能。合理地利用分区特性，还可以变相实现数据的更新操作，因为数据分区支持删除、替换和重置操作。假设数据表按照月份分区，那么数据就可以按月份的粒度被替换更新。
分区虽好，但不是所有的表引擎都可以使用这项特性，目前只有合并树（MergeTree）家族系列的表引擎才支持数据分区。接下来通过一个简单的例子演示分区表的使用方法。首先由 PARTITION BY 指定分区键，例如下面的数据表 my_table_partition1 使用了日期字段作为分区键，并将其格式化为年月的形式：

create table my_table_partition1(
    id String,
    url String,
    eventTime Date
) engine = MergeTree()
partition by toYYYYMM(eventTime)
order by id;

可以通过以下SQL查询表的分区状态：

select table, partition, path from system.parts where table = 'my_table_partition1';

1.2.ClickHouse视图

ClickHouse 拥有普通和物化两种视图，其中物化视图拥有独立的存储，而普通视图只是一层简单的查询代理。创建普通视图的完整语法如下所示：

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ...

物化视图支持表引擎，数据保存形式由它的表引擎决定，创建物化视图的完整语法如下所示：

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

物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新。POPULATE修饰符决定了物化视图的初始化策略：如果使用了POPULATE修饰符，那么在创建视图的过程中，会连带将源表中已存在的数据一并导入，如同执行了SELECT INTO一般；反之，如果不使用POPULATE修饰符，那么物化视图在创建之后是没有数据的，它只会同步在此之后被写入源表的数据。物化视图目前并不支持同步删除，如果在源表中删除了数据，物化视图的数据仍会保留。

1.3. ALTER

ALTER 只支持 MergeTree 系列、Merge 和 Distributed 引擎的表，基本语法：

ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|MODIFY|COMMENT COLUMN ...

参数解析：

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

1.4.Partition

查看表的分区信息

select partition_id, name, table, database, path from system.parts where table = 'my_table_partition1';

删除分区

alter table my_table_partition1 drop partition '202005';

复制分区

alter table my_table_partition2 replace partition '202005' from my_table_partition1;

卸载和装载分区

表分区可以通过DETACH语句卸载，分区被卸载后，它的物理数据并没有删除，而是被转移到了当前数据表目录的detached子目录下。而装载分区则是反向操作，它能够将detached子目录下的某个分区重新装载回去。卸载与装载这一对伴生的操作，常用于分区数据的迁移和备份场景。

记住，一旦分区被移动到了detached子目录，就代表它已经脱离了ClickHouse的管理，ClickHouse并不会主动清理这些文件。这些分区文件会一直存在，除非我们主动删除或者使用ATTACH语句重新装载它们。

--卸载
ALTER TABLE tb_name DETACH PARTITION partition_expr
--装载
ALTER TABLE tb_name ATTACH PARTITION partition_expr

1.5.CHECK TABLE

检查表中的数据是否损坏，他会返回两种结果：0 - 数据已损坏；1 - 数据完整

check table mt_table;

 该命令只支持 Log，TinyLog 和 StripeLog 引擎。

1.6.分布式DDL执行

ClickHouse支持集群模式，一个集群拥有1到多个节点。CREATE、ALTER、DROP、RENMAE及TRUNCATE这些DDL语句，都支持分布式执行。这意味着，如果在集群中任意一个节点上执行DDL语句，那么集群中的每个节点都会以相同的顺序执行相同的语句。这项特性意义非凡，它就如同批处理命令一样，省去了需要依次去单个节点执行DDL的烦恼。

将一条普通的DDL语句转换成分布式执行十分简单，只需加上ON CLUSTER cluster_name声明即可。例如，执行下面的语句后将会对集群内的所有节点广播这条DDL语句：

-- 分布式建库
create database if not exists nxdb4 on CLUSTER perftest_3shards_1replicas;
-- 分布式建表
CREATE TABLE nx_table_partition3 ON CLUSTER perftest_3shards_1replicas(
    ID String,
    URL String,
    EventTime Date
)ENGINE=MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID;

1.7.update 和 delete

        ClickHouse提供了DELETE和UPDATE的能力，这类操作被称为Mutation（突变）查询，它可以看作ALTER语句的变种。

1. Mutation操作适用于批量数据的修改和删除
2. 不支持事务 一旦语句被提交执行就会立刻对现有的数据产生影响，无法回滚。
3. Mutation操作执行是一个异步的过程，语句提交会立即返回，但是不代表具体逻辑已经执行完毕，具体的执行记录需要在system.mutations系统表查询。

        虽然可以实现修改和删除，但是和一般的OLTP数据库不一样，Mutation语句是一种很“重”的操作，而且不支持事务。

        “重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。

        由于操作比较“重”，所以Mutation语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成。

create database if not exists mydb;
use mydb;

--创建表
CREATE TABLE city(
    id UInt8,
    country String,
    area String,
    province String,
    city String,
create_time datetime DEFAULT now()
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY id;

-- 插入数据
insert into city(id,country,area,province,city) VALUES
(1,'China','North','Hubei','wuhan'),
(2,'China','South','Guangdong','guangzhou'),
(3,'China','South','Guangdong','shenzhen'),
(4,'China','North','Beijing','Beijing'),
(5,'China','South','Shanghai','Shanghai');

--数据查询
select id, country, area, province, city, create_time from city;

--update 操作
ALTER TABLE city UPDATE area='South' WHERE city='wuhan';

--delete操作
ALTER TABLE city DELETE WHERE city='guangzhou';

查看数据所在目录：

[root@hadoop1 mydb]# cd /var/lib/clickhouse/data/mydb/city/
[root@hadoop1 city]# ll -a
total 24
drwxr-x---. 6 root root  156 Jun  5 17:07 .
drwxr-x---. 3 root root   18 Jun  5 17:07 ..
drwxr-x---. 2 root root 4096 Jun  5 17:07 202106_1_1_0
drwxr-x---. 2 root root 4096 Jun  5 17:07 202106_1_1_0_2
drwxr-x---. 2 root root 4096 Jun  5 17:07 202106_1_1_0_3
drwxr-x---. 2 root root    6 Jun  5 17:07 detached
-rw-r-----. 1 root root    1 Jun  5 17:07 format_version.txt
-rw-r-----. 1 root root  109 Jun  5 17:07 mutation_2.txt
-rw-r-----. 1 root root   96 Jun  5 17:07 mutation_3.txt

可以发现在执行了update，delete操作之后数据目录会生成文件mutation_2.txt，mutation_3.txt。此外还有在同名的目录下在末尾增加了_2 （由update产生），_3（由delete产生）的后缀。可以看到mutation_2.txt（由update产生，存储的执行的SQL语句及操作时间，如：UPDATE area = \'South\' WHERE city = \'wuhan\'）和mutation_3.txt （由delete产生）是日志文件，完整的记录了update和delete操作语句和时间。当执行optimize table mydb.city final;后会生成202106_1_1_1_3的目录

mutation_id：生成对应的日志文件用于记录相关的信息。数据删除的过程是以数据表的每个分区目录为单位，将所有目录重写为新的目录，在目录的命名规则是在原有的名称上加上

block_numbers.number：数据的在重写的过程中会将所需要删除的数据去掉。旧的数据并不会立即删除，而是被标记为非激活状态(active =0)，等到MergeTree引擎的下一次合并动作触发的时候，这些非活动目录才会被真正的从物理上删除。