3. ClickHouse 基本语法

一、DDL 基础

目前只有 MergeTree 系列的表引擎和 Distributed 表引擎支持 ALTER 操作，所以在进行 ALTER 操作的时候注意表的引擎。

CREATE TABLE tb_test2
(
    `id` Int8,
    `name` String comment '用户名' default 'VIP', --设置列的默认值 
)
ENGINE = MergeTree()
ORDER BY id ;

--添加字段
ALTER TABLE tb_test2 ADD COLUMN age UInt8;
ALTER TABLE tb_test2 ADD COLUMN gender String AFTER name;
-- 删除字段
ALTER TABLE tb_test2 DROP COLUMN age ;
-- 修改字段的数据类型
ALTER TABLE  tb_test2 MODIFY COLUMN gender UInt8 DEFAULT 0 ;
--修改 / 添加字段的注释
ALTER TABLE tb_test2 COMMENT COLUMN name '用户名';

-- 修改表名
RENAME TABLE tb_test1 TO t1 ;
-- 修改多张表名
RENAME TABLE tb_test2 TO t2, t1 TO tt1 ;
-- 移动表到另一数据库中
RENAME TABLE t2 TO test1.t;

二、分区表操作

目前只有 MergeTree 系列的表引擎支持数据分区

CREATE TABLE test_partition
(
  id String,
  ctime DateTime
)ENGINE=MergeTree()
PARTITION BY toYYYYMM(ctime)
ORDER BY(id);

1、查看表中的分区

SELECT partition_id, name, table ,partition, active
FROM system.parts 
WHERE table = 'test_partition' AND active = 1 ;

2、添加/删除分区

--删除分区：删除分区以后，分区中的所有的数据全部删除
ALTER TABLE test_partition DROP PARTITION '202105';

3、合并分区

OPTIMIZE TABLE test_partition FINAL;

4、复制分区

clickHouse 支持将 A 表的分区数据复制到 B 表，这项特性可以用于快速数据写入、多表间数据同步和备份等场景

复制分区需要满足两个前提条件：

两张表需要拥有相同的分区键
它们的表结构完全相同。

-- 创建表
CREATE TABLE test_partition1 AS test_partition;

-- 复制一张表的分区到另一张表中
ALTER TABLE test_partition1 REPLACE PARTITION '202106' FROM test_partition;

5、重置分区数据

如果数据表某一列的数据有误，需要将其重置为初始值，如果设置了默认值那么就是默认值数据，如果没有设置默认值，系统会给出默认的初始值

注意：不能重置主键和分区字段

ALTER TABLE test_partition1 CLEAR COLUMN name IN PARTITION '202105';

6、卸载/装载分区

使用场景：分区数据的迁移和备份

①卸载分区 detach

分区被卸载后，它的物理数据并没有删除，而是被转移到了当前数据表目录的 detached 子目录下。该目录已经脱离了 clickhouse 的管理，clickhouse 并不会主动清理这些文件。

ALTER TABLE test_partition DETACH PARTITION '202105';

②装载分区 attach

将 detached 子目录的某个分区重新装载回去。

ALTER TABLE test_partition ATTACH PARTITION '202105';

三、视图

1、普通视图

不会存储任何数据，只是一层简单的 select 查询映射，对查询性能不会有任何增强

CREATE VIEW test_view AS SELECT id, UPPER(name), role FROM tb_test;

2、物化视图

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

CREATE MATERIALIZED VIEW mater_test_view ENGINE=Log
POPULATE AS SELECT * FROM tb_test;

四、查询语法

1. WITH 子句

ClickHouse 支持 CTE（Common Table Expression，公共表表达式），以增强查询语句的表达

-- 1、定义变量
WITH POW(2, 2) AS res
SELECT POW(res, 2);

-- 2、调用函数
WITH toDate(create_time) AS bday
SELECT user_id, score, bday FROM test_a;

--3、定义子查询
WITH (
    SELECT user_id, score 
    FROM test_a LIMIT 1
) AS sub
SELECT user_id, score, sub FROM test_a;

2、from

SQL 是一种面向集合的编程语言，from 决定了程序从那里读取数据。

表中查询数据
子查询中查询数据
表函数中查询数据表函数参考

-- 1、file 数据文件必须在指定的目录下 /var/lib/clickhouse/user_files
SELECT * FROM file('demo.csv', 'CSV', 'id Int8, name String, age UInt8');

--2、numbers
SELECT * FROM numbers(2, 10);
SELECT toDate('2021-01-01') + number AS date FROM numbers(365);

--3、mysql CH 可以直接从 MySQL 服务中查询数据
SELECT * FROM mysql('localhost:3306', 'test', 't_sku', 'root', '123456');

--4、hdfs
SELECT * FROM hdfs('hdfs://hdfs1:9000/test', 'TSV', 'column1 UInt32, column2 UInt32')

3、关联查询

JOIN 子句可以对左右两张表的数据进行连接，这是最常用的查询子句之一。它的语法包含连接精度和连接类型两部分。

（1）连接精度

支持 ALL(默认)、ANY 和 ASOF 三种类型。可通过 join_default_strictness 配置参数修改默认的连接精度类型。

all：左表内的一行数据，在右表中有多行数据与之连接匹配，则返回右表中全部连接的数据。
any：左表内的一行数据，在右表中有多行数据与之连接匹配，则仅返回右表中第一行连接的数据。
asof：模糊连接，连接键之后追加定义一个模糊连接的匹配条件asof_column。

（2）连接类型

分为外连接（left/right/full）、内连接（inner）和交叉连接（cross）三种

4、array join

ARRAY JOIN 子句允许在数据表的内部，与数组或嵌套类型的字段进行 JOIN 操作，从而将一行数组展开为多行。类似于 hive 中的 explode 炸裂函数的功能。

在一条 select 语句中，只能存在一个 array join（使用子查询除外）

inner array join（默认）：排除掉了空数组
left aray join：在 inner array join 中被排除掉的空数组出现在了返回的结果集中
当同时对多个数组字段进行 array join 操作，查询的计算逻辑按行合并而不是产生笛卡尔积

DROP TABLE IF EXISTS test_arrayjoin;

CREATE TABLE test_arrayjoin
(
    id    String,
    hobby Array(String)
)ENGINE = Memory;

INSERT INTO test_arrayjoin 
VALUES
(1, ['eat','drink','sleep']),
(2, ['study','read','sport']),
(3, []);

SELECT
      id
     ,hobby
     ,hb
FROM test_arrayjoin
ARRAY JOIN hobby AS hb;

-- 同时对多个数组字段进行 array join 操作
SELECT
      id
     ,hobby
     ,hb
     ,arrayMap(x ->concat(x,'ABC'),hobby) as hobbyCon
     ,hyc
     ,arrayEnumerate(hobby) as hobbyEnum
     ,hbe
FROM test_arrayjoin
ARRAY JOIN hobby AS hb
,hobbyCon AS hyc
,hobbyEnum AS hbe;

5、with 模型

with cube：如果聚合键的个数为 n，则组合的个数为 2^n
with rollup：按照聚合键从右向左上卷数据，基于聚合函数生成分组小计和总计，如果聚合键的个数为 n，组合的个数为 n+1。
with totals：基于聚合函数对所有数据进行总计。

6、order by 子句

在 MergeTree 中指定 order by 之后，数据在各个分区内会按照其定义的规则排序，这是一种分区内的局部排序
order by 子句指定全局排序，对于 NULL 值的排序，目前 Clickhouse 有 null 值最后和 null 值优先两种策略

--null 值最后
SELECT arrayJoin([1,2,null,0/0,3]) AS v1 ORDER BY v1 DESC nulls LAST;

--null 值优先
SELECT arrayJoin([1,2,null,0/0,3]) AS v1 ORDER BY v1 DESC nulls FIRST;

7、limit by 子句

运行于 order by 之后和 limit 之前，能够按照指定分组，最多返回前 n 行数据，常用于 top n 的查询场景
limit 子句 limit by 子句可以同时使用

SELECT database, table, max(bytes_on_disk) AS bytes
FROM system.parts
GROUP BY database, table
ORDER BY database, bytes AS DESC
LIMIT 2 BY database;

--limit by 支持 offset 偏移量获取数据
SELECT database, table, max(bytes_on_disk) AS bytes
FROM system.parts
GROUP BY database, table
ORDER BY database, bytes AS DESC
LIMIT 3 OFFSET 1 BY database; -- LIMIT 1, 3 BY database

参考

https://zhuanlan.zhihu.com/p/381343285