clickhouse较常用语法介绍

--1.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 ROLLUP|WITH CUBE] [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]
[SETTINGS ...]
[UNION ...]
[INTO OUTFILE filename]
[FORMAT format]

--2.with子句

--示例1：使用常量表达式作为“变量”
WITH '2019-08-01 15:23:00' as ts_upper_bound
SELECT *
FROM hits
WHERE
EventDate = toDate(ts_upper_bound) AND
EventTime <= ts_upper_bound;
--示例2：从SELECT子句列列表中逐出一个sum（bytes）表达式结果

WITH sum(bytes) as s
SELECT
formatReadableSize(s),
table
FROM system.parts
GROUP BY table
ORDER BY s;
--示例3：使用标量子查询的结果

/* this example would return TOP 10 of most huge tables */
WITH
(
SELECT sum(bytes)
FROM system.parts
WHERE active
) AS total_disk_usage
SELECT
(sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;
--示例4：在子查询中重用表达式

WITH test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;

 

--3.sample子句
(1) sample k
k表示因子系数，采样因子，取值范围[0,1]，若在0--1之间的小数则表示采样，若为0或者1则等同于不采样。

select CounterID from clicks sample 0.1
等同于：
select CounterID from clicks sample 1/10

 

(2)sample n
n表示采样的样本数量。n表示至少采样多少行数据。n=1表示不使用采样，n的范围从2到表的总行数。

select count() from clicks sample 10000;

 

(3)sample k offset n
表示按照因子系数和偏移量采样。

select CounterID,_sample_factor from clicks sample 0.4 offset 0.5 limit 1; -- 读取后半部分数据,从中抽样40%,结果只显示出1条
offset 0.5表示查询从数据序号偏移一半开始
sample 0.4表示采样40%的数据
limit 1 结果只显示一条

 

 注意：

sample子句提供了近似计算的功能，能够实现数据采样的功能，使查询仅仅返回采样数据而不是全部数据，从而有效减少查询负载。

sample子句的采样设计是一种幂等设计，即在数据发生变化的时候使用相同的采样规则能返回相同的数据。这种特性非常适合那些可以接受近似查询结果的场景。

 

--4.join子句
所有标准 SQL JOIN 支持类型:

INNER JOIN，只返回匹配的行。
LEFT OUTER JOIN，除了匹配的行之外，还返回左表中的非匹配行。
RIGHT OUTER JOIN，除了匹配的行之外，还返回右表中的非匹配行。
FULL OUTER JOIN，除了匹配的行之外，还会返回两个表中的非匹配行。
CROSS JOIN，产生整个表的笛卡尔积, “join keys” 是 不 指定。
ClickHouse中提供的其他联接类型:

LEFT SEMI JOIN 和 RIGHT SEMI JOIN,白名单 “join keys”，而不产生笛卡尔积。
LEFT ANTI JOIN 和 RIGHT ANTI JOIN，黑名单 “join keys”，而不产生笛卡尔积。
LEFT ANY JOIN, RIGHT ANY JOIN and INNER ANY JOIN, partially (for opposite side of LEFT and RIGHT) or completely (for INNER and FULL) disables the cartesian product for standard JOIN types.
ASOF JOIN and LEFT ASOF JOIN, joining sequences with a non-exact match.

使用SEMI LEFT JOIN时，使用右表中存在的key去过滤左表中的key，如果左表存在与右表相同的key，则输出。
使用SEMI RIGHT JOIN时，使用左表中存在的key去过滤右表中的key，如果右表中存在与左表相同的key，则输出。
换句话说，SEMI JOIN返回key在另外一个表中存在的记录行。
ANTI JOIN和SEMI JOIN相反，他返回的是key在另外一个表中不存在的记录行。
SEMI JOIN和ANTI JOIN都允许从两个表中获取数据。对于被过滤的表，返回的是与key相同的记录行。对于ANTI JOIN，另外一个表返回的是默认值，比如空值或0。

 

--5.into outfile子句
实现导出数据至本地文件

样例：

SELECT * FROM test.table_name INTO OUTFILE '/data/t_city.csv' FORMAT CSVWithNames;

只能在 command-line client and clickhouse-local 情景下使用，默认输出格式为TabSeparated
通出 format 命令可指定输出格式

--6.PREWHERE子句
prewhere 目前只适用于*MergeTree系列的表引擎，可以看做是对where的一种优化，和where语句的作用相同，用来过滤数据。
不同之处在于prewhere首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取select 声明的列字段来补全其余属性。
在某些场合下，prewhere语句比where语句处理的数据量更少性能更高。

样例:

select * from test.table_name prewhere col1='us';

 

不能自动优化情景：

使用常量表达式
使用默认值为alias类型的字段
包含了arrayJOIN，globalIn，globalNotIn或者indexHint的查询
select查询的列字段和where的谓词相同
使用了主键字段

--7.union子句
包括union all / union distinct / union 3命令格式，union all对结果不去重，union distinct对结果去重;

当只用 union 命令时，到底实现union all 或是 union distinct，由union_default_mode 决定，SET union_default_mode = 'DISTINCT' 模式

 

--8.limit子句
limit m ： 显示前m条数据

limit n , m : 相当 LIMIT m OFFSET n , 即从n+1条数据开始，共返回m条

 

--9.limit by子句
两种格式：

limit m by expressions
limit n,m by expressions
SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id; -- 每个id只显示前两条数据

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id; --每个id跳过第1条数据，显示第2,3条数据

--以下查询返回每domain, device_type对的前5个引荐来源网址，总共最多包含100行（LIMIT n BY + LIMIT）。

SELECT
domainWithoutWWW(URL) AS domain,
domainWithoutWWW(REFERRER_URL) AS referrer,
device_type,
count() cnt
FROM hits
GROUP BY domain, referrer, device_type
ORDER BY cnt DESC
LIMIT 5 BY domain, device_type
LIMIT 100

 

--10.order by
NaN和NULL排序顺序：

默认情况下或使用NULLS LAST修饰符：首先是值，然后是NaN，然后是NULL。

使用NULLS FIRST修饰符：首先NULL，然后是NaN，然后是其他值。

对于按字符串值排序，可以指定排序规则（比较）。示例：ORDER BY SearchPhrase COLLATE 'tr'-假设字符串是UTF-8编码的，则使用土耳其字母按升序按关键字排序，不区分大小写。

COLLATE可以为ORDER BY中的每个表达式分别指定或不指定。如果指定ASC或DESC，COLLATE则在其后指定。使用时COLLATE，排序始终不区分大小写

SELECT * FROM collate_test ORDER BY s ASC COLLATE 'en';

 运行示例：

--测试表：

CREATE TABLE test.tmp_uid_info
(
`uid` Int32,
`alias` Int32,
`sex` String,
`totalDate` Date,
`source` String,
`name` String
)
ENGINE = ReplicatedMergeTree('/clickhouse/test/tables/{shard}/tmp_uid_info', '{replica}')
PARTITION BY totalDate
ORDER BY uid
SETTINGS index_granularity = 8192

--添加新字段

--语法格式
alter table tb_name add column [IF NOT EXISTS] name [type] [default_expr]
[alter name_after]

-- 给测试表的末尾增加新字段.，对于数据表中已经存在旧数据⽽⾔，新追加的字段会使⽤默认值补全
alter table tmp_uid_info on cluster ck_cluster add column age String default '0'

--删除已有字段

-- 语法格式
alter table tb_name drop column [IF EXISTS] name

-- 删除测试表的age字段，注意字段被删除后，它的数据也会被连带删除
alter table default.tmp_uid_info on cluster ck_cluster drop column age;

--修改字段类型

-- 语法格式
alter table tmp_uid_info MODIFY COLUMN [IF EXISTS] name [type] [default_expr]
-- 修改uid的字段类型，由int32改成Int64，注意，使用String转成Int会报错
alter table default.tmp_uid_info on cluster ck_cluster modify column uid Int64;

--修改字段名

-- 语法格式
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name
-- 修改uid的字段类型，由int32改成Int64，注意，使用String转成Int会报错
alter table tmp_uid_info on cluster ck_cluster rename column name to username;

--给字段添加注释

-- 语法格式
alter table tb_name comment column [IF EXISTS] name 'comment'

-- 给测试表的name字段添加注释
alter table default.tmp_uid_info on cluster ck_cluster comment column name '姓名'

-- 即可查看到name的注释
DESCRIBE TABLE tmp_uid_info

┌─name──────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ uid │ Int32 │ │ │ │ │ │
│ alias │ Int32 │ │ │ │ │ │
│ sex │ String │ │ │ │ │ │
│ totalDate │ Date │ │ │ │ │ │
│ source │ String │ │ │ │ │ │
│ name │ String │ │ │ 姓名 │ │ │
│ age │ String │ DEFAULT │ '0' │ │ │ │
└───────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

--清空数据表，只是表内的数据全部清空，⽽不是直接删除这张表

--语法格式
truncate table [IF EXISTS] [db_name.]tb_name
-- 清空测试表数据
truncate table default.tmp_uid_info on cluster ck_cluster

--同步zk删除表

drop table if EXISTS default.ods_user_test on cluster ck_cluster sync;
drop table if EXISTS default.ods_user_test_all on cluster ck_cluster sync;

--通过system系统库的Parts表来查询分区信息

SELECT
partition_id,
name,
table,
database
FROM system.parts
WHERE (table = 'tmp_uid_info') AND (database = 'test')

┌─partition_id─┬─name─────────────┬─table────────┬─database─┐
│ 20101027 │ 20101027_0_0_0_2 │ tmp_uid_info │ test │
│ 20121027 │ 20121027_0_0_0_2 │ tmp_uid_info │ test │
│ 20131127 │ 20131127_0_0_0_2 │ tmp_uid_info │ test │
│ 20141127 │ 20141127_0_0_0_2 │ tmp_uid_info │ test │
│ 20161027 │ 20161027_0_0_0_2 │ tmp_uid_info │ test │
│ 20161127 │ 20161127_0_0_0_2 │ tmp_uid_info │ test │
│ 20171127 │ 20171127_0_0_0_2 │ tmp_uid_info │ test │
│ 20180127 │ 20180127_0_0_0_2 │ tmp_uid_info │ test │
│ 20181027 │ 20181027_2_2_0_4 │ tmp_uid_info │ test │
│ 20181227 │ 20181227_0_0_0_2 │ tmp_uid_info │ test │
│ 20190127 │ 20190127_0_0_0_2 │ tmp_uid_info │ test │
│ 20190227 │ 20190227_0_0_0_2 │ tmp_uid_info │ test │
│ 20191027 │ 20191027_2_2_0_4 │ tmp_uid_info │ test │
│ 20191127 │ 20191127_0_0_0_2 │ tmp_uid_info │ test │
│ 20201027 │ 20201027_0_0_0_2 │ tmp_uid_info │ test │
└──────────────┴──────────────────┴──────────────┴──────────┘


--根据条件删除测试表的数据：

alter table default.tmp_uid_info on cluster ck_cluster delete
where totalDate='2020-10-28'

--根据分区删除测试表的数据：

-- 根据分区键删除，测试表按日期分区
alter table default.tmp_uid_info on cluster ck_cluster drop partition '2020-10-27'

--手动合并数据表的分区

-- 单机
optimize table default.tmp_uid_info partition '2020-10-27' final
-- zk分发
optimize table default.tmp_uid_info on cluster ck_cluster partition '2020-10-27' final

--修改表名字

RENAME TABLE database.table1TO database.table2 on cluster ck_cluster;

 --11.分区表操作

目前只有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';

--12.视图

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;