暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 快到飞起的分析数据库ClickHouse笔记-查询

快到飞起的分析数据库ClickHouse笔记-查询

见白 2020-12-29
843

ClickHouse有2类解析器，一类是完整SQL解析器（递归式解析器），一类是数据格式解析器（快速流式解析器）。除了 INSERT 查询，其它情况下仅使用完整SQL解析器。因此ClickHouse基本兼容sql语法，同时ClickHouse也具有其独特的查询函数。

1. 数据查询

废话不多说，直接切入主题。

1.1 with子句

        支持cte，即公用表表达式。如：

select pow(pow(2,2),3)

        相当于 ：

with pow(2,2) as a select pow(a,3)


        1.定义变量

with 10 as start

select * from table where num > start;


        2.调用函数

with sum(num) as bytes

select database,formatReadableSize(bytes) as format from table group by database


        3.定义子查询

        需注意with的子查询语句只能返回一行。

with (

select sum(num) from table 

) as total_bytes

select database,(sum(num) / total_bytes) as usage from table group by database


        4.子查询中重复使用with

        对结果集中的usage再次使用取整函数。

with (

round(usage)

) as usage_v1

select database,usage,usage_v1

from (

with (

select sum(num) from table 

) as total_bytes

select database,(sum(num) / total_bytes) as usage from table group by database

)

1.2 from子句

        表示从何处读数据。对象包括：

  • 数据表

  • 子查询

  • 表函数

        比如从函数numbers()读取：

select number from numbers(5)

1.3 sample子句

        数据采样，取部分数据进行近似计算。采用幂等设计，数据不变化，多次采样结果相同。适用MergeTree系列引擎的数据表，在建表时声明。

        需注意以下几点：

  • 按照intHash32(UserID)分布后的结果采样查询

  • sample by表达式必须同时包含在主键声明中

  • sample key 必须是int类型，否则查询报错

create table new_table(

CounterID UInt64,

UserID UInt64,

EventDate DATE

) ENGINE = MergeTree()

partition by toYYYYMM(EventDate)

order by (CounterID, intHash32(UserID))

sample by intHash32(UserID)


        1.SAMPLE factor

        按因子系数采样，取值支持0～1之间的小数。如果factor设置为0或者1，则效果等同于不进行数据采样。

select CounterID from new_table sample 0.1

select CounterID from new_table sample 1/10 （十进制）

        查询结果需要乘以采样系数。如果因子系数为0.1，那么采样系数为10

select CounterID * 10 from new_table sample 0.1

select CounterID,_sample_factor from new_table sample 0.1

select CounterID * any(_sample_factor) from new_table sample 0.1


        2.sample rows

        按样本数量采样，rows大于1或大于总行数等同于不使用采样。采样数据的最小粒度是由index_granularity索引粒度决定的。即设置一个小于索引粒度或者较小的rows值没有什么意义，应该设置一个较大的值。

select CounterID from new_table sample 10000


        3.sample factor offset n

        按因子系数和偏移量采样。factor表示采样因子，n表示偏移多少数据后才开始采样，它们两个的取值都是0～1之间的小数。

在计算OFFSET偏移量后，按照SAMPLE比例采样出现了溢出，则数据会被自动截断。

        比如，从数据的二分之一处开始，按0.1的系数采样数据：

select CounterID from new_table sample 0.1 offset 0.5

1.4 array join子句

        数组元素的行转列。注意一条select中只能有一条array join。

        比如，下面演示的是将数组的元素按列输出：

food [1,2,3] --->

food 1

food 2

food 3


        1.INNER ARRAY JOIN

        数组被展开成了多行，并且排除掉了空数组。如果为原有的数组字段添加一个别名，则能够访问展开前的数组字段。

select title,value,v from new_table array join value as v;


        2.LEFT ARRAY JOIN

        支持LEFT连接策略。数组被展开成了多行，并且不会排除空数组。同时对多个数组字段进行ARRAY JOIN操作时，查询的计算逻辑是按行合并而不是产生笛卡儿积。

select title,value,v from new_table left array join value as v;


select title,value,v,arrayMap(x -> x*2,value) as mapv,v_1 from new_table left array join value as v , mapv as v_1;


*    嵌套数据类型的本质是数组,array join支持嵌套数据类型

        创建嵌套类型：

create table new_table(

title String,

nest Nested(

v1 UInt32,

v2 UInt32

)

)ENGINE = Log

        往上表中写入数据：

insert into new_table values ('food',[1,2,3],[10,20,30]),('fruit',[4,5],[40,50]),('meat',[],[])

        写入的数据需要注意：（1）同一行数据各数组长度对齐，（2）多行之间长度无限制。对该表进行查询：

select title,nest.v1,nest.v2 from new_table;


title nest.v1 nest.v2

food [1,2,3] [10,20,30]

fruit [4,5] [40,50]

meat [] []


select title,nest.v1,nest.v2 from new_table array join nest

select title,nest.v1,nest.v2 from new_table array join nest.v1,nest.v2

1.5 join子句

        join子句连接精度分为ALL（默认）、ANY和ASOF三种，连接类型分为外连接、内连接和交叉连接三种。


        1.连接精度

        all：如果左表内的一行数据，在右表中有多行数据与之连接匹配，则返回右表中全部连接的数据。

A all inner join B on


        any：如果左表内的一行数据，在右表中有多行数据与之连接匹配，则仅返回右表中第一行连接的数据。

A any inner join B on


        asof：模糊连接，它允许在连接键之后追加定义一个模糊连接的匹配条件asof_column。且仅返回右表中第一行连接匹配的数据。

A asof inner join B on A.id=B.id and a.time = b.time

等价于：A.id=B.id and a.time >= b.time


*    支持使用USING的简写形式

A asof inner join B using(id,time)

        还需注意：（1）sof_colum必须是整型、浮点型和日期型这类有序序列的数据类型；（2）asof_colum不能是数据表内的唯一字段，即连接键（JOIN KEY）和asof_colum不能是同一个字段。


        2.连接类型

        交集、并集、笛卡尔积或其他。


        inner（交集）

        INNER JOIN表示内连接，在查询时会以左表为基础逐行遍历数据，然后从右表中找出。与左边连接的行，它只会返回左表与右表两个数据集合中交集的部分，其余部分都会被排除。


        OUTER（可省略该修饰符）

        OUTER JOIN表示外连接，它可以进一步细分为左外连接（LEFT）、右外连接（RIGHT）和全外连接（FULL）三种形式。

  • left：左表全部返回，右表补充左表属性，没有匹配则填充默认值。

  • right：右表全部返回，左表补充右表属性，没有匹配则填充默认值。底层实现：内部进行inner连接计算交集，并记录右表未被连接的行追加至交集尾部。

  • full：返回左右表的并集，没有匹配泽填充默认值。底层实现：内部进行left连接计算左表，并记录右表未被连接的行追加至交集尾部。


        CROSS（交叉连接，笛卡尔积）

        返回左右表的笛卡尔积，不需要声明join key，结果包含所有组合。底层实现：以左表为基础，逐行与右表全集相乘。


        3.多表连接

        多表查询转换为两两连接的形式。关联查询会被转换为指定的连接查询，规则为：（在复杂业务查询场景下无法确定最终的意图，不建议使用）

（1）转换为cross join：查询语句不包含where

select a.id,b.name,c.date from tb1 as a,tb2 as b,tb3 as c;

（2）转换为inner join：查询语句包含where

select a.id,b.name,c.date from tb1 as a,tb2 as b,tb3 as c where a.id=b.id and a.id=c.id;


        4.join 注意事项

（1）性能

        优化性能，遵循左大右小。因为无论哪种连接方式，右表会全部加载。

        join查询没有缓存支持，每次查询都需要重新连接。建议使用上层应用缓存或者join表引擎。

        大量维度属性补全的查询场景，建议使用字典代替join查询。多表查询，两两连接会滚雪球，中间表越来越大。


（2）空值策略和简写形式

        空值策略join_use_nulls参数指定，默认为0，使用数据类型的默认值；为1，使用Null填充。

        join key简写，连接字段相同时使用using

select a.id,b.name from tb1 as a join tb2 as b on a.id=b.id;

select a.id,b.name from tb1 as a join tb2 as b using id;

1.6 where和prewhere

        1.WHERE子句基于条件表达式来实现数据过滤。

        如果过滤条件恰好是主键字段，则能够进一步借助索引加速查询（是否启用索引的依据）。


        2.PREWHERE只能用于MergeTree系列的表引擎。

首先只会读取PREWHERE指定的列字段数据，用于数据过滤的条件判断。待数据过滤之后再读取SELECT声明的列字段以补全其余属性。处理的数据量更少，性能更高。


        3.where自动优化为prewhere

        默认情况是自动进行优化的，也可以使用以下操作命令强制开闭该功能：

set optimize_move_to_prewhere=0 强制关闭自动优化，

set optimize_move_to_prewhere=1 强制开启自动优化（默认）。

        也存在不会自动优化的情况：

  • 使用常量表达式

  • 使用默认值为alias类型的字段

  • 包含了arrayJoin、globalIn、globalNotIn或者indexHint的查询

  •  SELECT查询的列字段与WHERE谓词相同

  • 使用了主键字段

        虽然不会自动优化，但是以上场景依然可以手动使用prewhere。

1.7 group by 聚合查询

        Group By聚合是ClickHouse最凸显性能的地方。

        Group By后面声明聚合字段，一般声明后只能使用这些字段，一些场合也可以使用any、max、min等聚合函数访问其他字段。可以配合WITH ROLLUP、WITH CUBE和WITHTOTALS三种修饰符获取额外的汇总信息。在Group by中null值将作为特定值处理。


        1.修饰词

        WITH ROLLUP

        ROLLUP能够按照聚合键从右向左上卷数据，基于聚合函数依次生成分组小计和总计。

        比如：

select project,appid,count() from lpm.lpmclientpoint where day='2020-06-01' group by project,appid with rollup order by project

        其将会生成一行所有数据总量的信息，一行对project分组的总数信息。


        WITH CUBE

        基于聚合键之间所有的组合生成小计信息。如果设聚合键的个数为n，则最终小计组合的个数为2的n次方。

        比如：

select project,appid,count() from lpm.lpmclientpoint where day='2020-06-01' group by project,appid with cube order by project

        其将会生成一行所有数据总量的信息，一行对project分组的总数信息，一行对appid分组的总数信息。


        WITH TOTALS

        基于聚合函数对所有数据进行总计，结果附加了一行Totals汇总合计。


        2.HAVING子句

        与Group By同时出现，在聚合计算之后实现二次过滤数据。where是先筛选数据再聚合过滤，having是先聚合再过滤，where效率比having要高。


        3.ORDER BY子句

        表属性中的排序决定了分区，如果在查询时数据跨越了多个分区，则它们的返回顺序是无法预知的，每一次查询返回的顺序都可能不同。在这种情形下，如果需要数据总是能够按照期望的顺序返回，就需要借助ORDER BY子句来指定全局顺序。

        如果不写asc、desc，默认使用升序asc。

        比如：

select * from new_table order by a asc,b desc;

等价于：select * from new_table order by a,b desc; （未声明则使用asc）


*    针对null值排序策略：

  • nulls last

    默认，null排在后面。顺序为 value、NaN、NULL

  • null first

    null排在最前。顺序为NULL、NaN、其他值（value）


        4.LIMIT BY子句

        最多返回前n行数据，TOP N的查询场景。by后面的列名用于选取数量。limit 3 by a （选取出a的排名前三的数据，多个分组后，可能有好几个数据会返回，但是a只有三种类型）

        LIMIT BY也支持跳过OFFSET偏移量获取数据。


        5.LIMIT子句

        用法如下：

limit n 返回前n行

limit n offset m        从m行开始返回n行

limit m,n 从m行开始返回n行

        limit确保每次返回数据唯一，需要和order by一起使用。


        6.SELECT子句

        放在开头，最后执行。

        比如，基于正则查询：

select columns('^n'),column('^p') from new_table;

        返回以n及包含p开头的列字段。


        7.DISTINCT子句

        去重。但相比于group by，dinstinct的执行计划更简单。

select distinct a from new_table; （返回a的所有去重值）

select name from new_table group by a;

        当DISTINCT与ORDER BY同时使用时，其执行的优先级是先DISTINCT后ORDER BY。并且所有NULL值归为一组。


        8.UNION ALL子句

        联合左右两边的两组子查询，将结果一并返回。一次查询可以声明多次的UNION ALL。UNION ALL不能直接使用其他子句（例如ORDER BY、LIMIT等），这些子句只能在它联合的子查询中使用。

首先，列字段的数量必须相同；其次，列字段的数据类型必须相同或相兼容；最后，列字段的名称可以不同，查询结果中的列名会以左边的子查询为准。

        目前ClickHouse只支持UNION ALL子句，如果想得到UNION DISTINCT子句的效果，可以使用嵌套查询来变相实现。

select distinct name from 

(

select name,v1 from table_1

union all

select title,v1 from table_2

)

2. SQL执行计划

2.1 查看SQL执行计划

        clickhouse可以借助后台服务日志，来达到查看sql执行计划的目的，进一步我们可以根据执行计划对sql语句进行优化。

        客户端打印查询日志：

clickhouse-client -h 127.0.0.1 --send_logs_level=trace <<< 'select * from new_table' > /dev/null

        

        具体的操作为：

  • 通过将ClickHouse服务日志设置到DEBUG或者TRACE级别，可以变相实现EXPLAIN查询，以分析SQL的执行日志。

  • 需要真正执行了SQL查询，ClickHouse才能打印计划日志，所以如果表的数据量很大，最好借助LIMIT子句以减小查询返回的数据量。

  • 在日志中，分区过滤信息部分：selected xxx parts by date。其中by date是固定的，无论我们的分区键是什么字段，这里都不会变。这是由于在早期版本中，MergeTree分区键只支持日期字段。

  • 不要使用SELECT * 全字段查询。

  • 尽可能利用各种索引（分区索引、一级索引、二级索引），这样可避免全表扫描。

2.2 查看全字段全表扫描

        打印查询日志中的相关关键信息：

  • Expression：查询线程

  • key condition：unknow 未使用主键索引

  • MinMax index condition:unknow 未使用分区索引

  • selected 12 parts 扫描分区数及MarkRange

  • read 80000 rows 读取的数据行数及大小

  • MemoryTracker：peak memory usage （for query） 消耗内存峰值

2.3 查看单字段全表扫描

        读取部分字段相比于读取全部字段，内存峰值会降低很多。通过以下语句可以验证：

clienthouse-client -h 127.0.0.1 --send_logs_level=trace <<< 'select a from new_table' > /dev/null


clienthouse-client -h 127.0.0.1 --send_logs_level=trace <<< 'select * from new_table' > /dev/null

        虽然都是会扫描所有分区，读取所有数据，但是结果集只有a列数据，比全集少很多。

2.4 查看使用分区索引

        通过增加where子句，可以使用分区索引。以下语句可以验证：

select a from new_table where date='2020-12-01'

        查看打印的查询日志，可以看到where自动优化为prewhere，查询中启动分区索引，只扫描部分分区。

2.5 使用主键索引

        启用主键索引，分区扫描减少，markrange减少。可以通过以下语句在打印日志中查看到：

select a from new_table where date='2020-12-01' and ID=110;（ID为索引关键词）





*    下期是关于ClickHouse集群相关的高可用配置。





参考资料：

[1] Yandex.clickhouse官方文档[EB/OL]：https://clickhouse.tech/docs/en/

[2] 朱凯.ClickHouse原理解析与应用实践[M]








数据库clickhouse
文章转载自见白，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论