火山引擎:ClickHouse增强计划之“多表关联查询”

简介: 火山引擎:ClickHouse增强计划之“多表关联查询”

相信大家都对大名鼎鼎的ClickHouse有一定的了解了,它强大的数据分析性能让人印象深刻。但在字节大量生产使用中,发现了ClickHouse依然存在了一定的限制。例如:

• 缺少完整的upsert和delete操作

• 多表关联查询能力弱

• 集群规模较大时可用性下降(对字节尤其如此)

• 没有资源隔离能力

因此,我们决定将ClickHouse能力进行全方位加强,打造一款更强大的数据分析平台。后面我们将从五个方面来和大家分享,此前为大家介绍了字节是如何为ClickHouse补全更新删除能力的,本篇将详细介绍我们是如何加强ClickHouse多表关联查询能力。

大宽表的局限

数据分析的发展历程,可以看作是不断追求分析效率和分析灵活的过程。分析效率是非常重要的,但是并不是需要无限提升的。1秒返回结果和1分钟返回结果的体验是天壤之别,但是0.1秒返回结果和1秒返回结果的差距就没那么大了。因此,在满足了一定时效的情况下,分析的灵活性就显得额外重要了。

起初,数据分析都采用了固定报表的形式,格式更新频率低,依赖定制化的开发,查询逻辑是写死的。对于业务和数据需求相对稳定、不会频繁变化的场景来说固定报表确实就足够了,但是以如今的视角来看,完全固定的查询逻辑不能充分发挥数据的价值,只有通过灵活的数据分析,才能帮助业务人员化被动为主动,探索各数据间的相关关系,快速找到问题背后的原因,极大地提升工作效率。。

后面,基于预计算思想的cube建模方案被提出。通过将数据ETL加工后存储在cube中,保证领导和业务人员能够快速得到分析结果基础上,获得了一定的分析灵活性。不过由于维度固定,以及数据聚合后基本无法查询明细数据,依然无法满足Adhoc这类即席查询的场景需求。

近些年,以ClickHouse为代表的具备强大单表性能的查询引擎,带来了大宽表分析的风潮。所谓的大宽表,就是在数据加工的过程中,将多张表通过一些关联字段打平成一张宽表,通过一张表对外提供分析能力。基于ClickHouse单表性能支撑的大宽表模式,既能提升分析时效性又能提高数据查询和分析操作的灵活性,是目前非常流行的一种模式。

然而大宽表依然有它的局限性,具体有:

• 生成每一张大宽表都需要数据开发人员不小的工作量,而且生成过程也需要一定的时间

• 生成宽表会产生大量的数据冗余

刚才有提到,数据分析的发展历程可以看作是不断追求分析效率和分析灵活的过程,那么大宽表的下一个阶段呢?如果ClickHouse的多表关联查询能力足够强,是不是连“将数据打平成宽表”这个步骤也可以省略,只需要维护好对外服务的接口,任何业务人员的需求都现场直接关联查询就可以了呢?

ByteHouse是如何强化多表关联查询能力的?

ClickHouse 的执行模式相对比较简单,其基本查询模式分为 2 个阶段:

ByteHouse 进行多表关联的复杂查询时,采用分 Stage 的方式,替换目前 ClickHouse的2阶段执行方式。将一个复杂的 Query 按照数据交换情况切分成多个 Stage,Stage 和 Stage 之间通过 exchange 完成数据的交换,单个 Stage 内不存在数据交换。Stage 间的数据交换主要有以下三种形式:

• 按照单(多)个 key 进行 Shuffle

• 由 1 个或者多个节点汇聚到一个节点  (我们称为 gather)

• 同一份数据复制到多个节点(也称为 broadcast 或者说广播)

单个 Stage 执行会继续复用 ClickHouse 的底层的执行方式。

按照不同的功能切分不同的模块,设计目标如下:

  1. 各个模块约定好接口,尽量减少彼此的依赖和耦合。一旦某个模块有变动不会影响别的模块,例如 Stage 生成逻辑的调整不影响调度的逻辑。
  2. 模块采用插件的架构,允许模块根据配置灵活支持不同的策略。

根据数据的规模和分布,ByteHouse支持了多种关联查询的实现,目前已经支持的有:

  1. Shuffle Join,最通用的 Join
  2. Broadcast Join,针对大表 Join 小表的场景,通过把右表广播到左表的所有 worker 节点来减少左表的传输
  3. Colocate Join,针对左右表按照 Join key 保持相通分布的场景,减少左右表数据传输

Join 算子通常是 OLAP 引擎中最耗时的算子。如果想优化 Join 算子,可以有两种思路,一方面可以提升 Join 算子的性能,例如更好的 Hash Table 实现和 Hash 算法,以及更好的并行。另一方面可以尽可能减少参与 Join 计算的数据。

Runtime Filter 在一些场景特别是事实表 join 维度表的星型模型场景下会有比较大的效果。因为这种情况下通常事实表规模比较大,而大部分过滤条件都在维度表上,事实表可能要全量 join 维度表。Runtime Filter 的作用是通过在 Join 的 probe 端(就是左表)提前过滤掉那些不会命中 Join 的输入数据来大幅减少 Join 中的数据传输和计算,从而减少整体的执行时间。以下图为例,

改善后的效果

以SSB 100G测试集为例,不把数据打成大宽表的情况下,分别使用 ClickHouse 22.2.3.1版本和ByteHouse 2.0.1版本,在相同硬件环境下进行测试。(无数据表示无法返回结果或超过60s)

可以看到大多数测试中,ClickHouse都会发生报错无法返回结果的情况,而ByteHouse能够稳定的在1s内跑出结果。

只看SSB的多表测试有些抽象,下面从两个具体的case来看一下优化后的效果:。

Case1:Hash Join 右表为大表

经过优化后,query 执行时间从17.210s降低至1.749s。

lineorder 是一张大表,通过 shuffle 可以将大表数据按照 join key shuffle 到每个 worker 节点,减少了右表构建的压力。

SELECT
    sum(LO_REVENUE) - sum(LO_SUPPLYCOST) AS profit
FROM 
    customer
INNER JOIN
(
    SELECT
    LO_REVENUE,
    LO_SUPPLYCOST,
    LO_CUSTKEY
    from
    lineorder
    WHERE toYear(LO_ORDERDATE) = 1997 and toMonth(LO_ORDERDATE) = 1
) as lineorder
ON LO_CUSTKEY = C_CUSTKEY
WHERE C_REGION = 'AMERICA'

Case 2:5张表 Join(未开启runtime filter)

经优化后,query 执行时间从8.583s降低至4.464s。

所有的右表可同时开始数据读取和构建。为了和现有模式做对比,ByteHouse这里并没有开启 runtime filter,开启 runtime filter 后效果会更快。

SELECT                                                                                                                                              
    D_YEAR,                                                                                                                                         
    S_CITY,                                                                                                                                         
    P_BRAND,                                                                                                                                        
    sum(LO_REVENUE) - sum(LO_SUPPLYCOST) AS profit                                                                                                  
FROM ssb1000.lineorder                                                                                                                              
INNER JOIN                                                                                                                                   
(                                                                                                                                                   
    SELECT C_CUSTKEY                                                                                                                                
    FROM ssb1000.customer                                                                                                                           
    WHERE C_REGION = 'AMERICA'                                                                                                                      
) AS customer ON LO_CUSTKEY = C_CUSTKEY                                                                                                             
INNER JOIN                                                                                                                                   
(                                                                                                                                                   
    SELECT                                                                                                                                          
        D_DATEKEY,                                                                                                                                  
        D_YEAR                                                                                                                                      
    FROM date                                                                                                                             
    WHERE (D_YEAR = 1997) OR (D_YEAR = 1998)                                                                                                        
) AS dates ON LO_ORDERDATE = toDate(D_DATEKEY)                                                                                                      
INNER JOIN                                                                                                                                   
(                                                                                                                                                   
    SELECT                                                                                                                                          
        S_SUPPKEY,                                                                                                                                  
        S_CITY                                                                                                                                      
    FROM ssb1000.supplier                                                                                                                           
    WHERE S_NATION = 'UNITED STATES'                                                                                                                
) AS supplier ON LO_SUPPKEY = S_SUPPKEY                                                                                                             
INNER JOIN                                                                                                                                   
(                                                                                                                                                   
    SELECT                                                                                                                                          
        P_PARTKEY,                                                                                                                                  
        P_BRAND                                                                                                                                     
    FROM ssb1000.part                                                                                                                               
    WHERE P_CATEGORY = 'MFGR#14'                                                                                                                    
) AS part ON LO_PARTKEY = P_PARTKEY                                                                                                                 
GROUP BY                                                                                                                                            
    D_YEAR,                                                                                                                                         
    S_CITY,                                                                                                                                         
    P_BRAND                                                                                                                                         
ORDER BY                                                                                                                                            
    D_YEAR ASC,                                                                                                                                     
    S_CITY ASC,                                                                                                                                     
    P_BRAND ASC                                                                                                                                     
SETTINGS enable_distributed_stages = 1, exchange_source_pipeline_threads = 32 

经过多表关联查询能力的增强,ByteHouse能够更加全面的支撑各类业务,用户可以根据场景选择是否将数据打成大宽表,均能获得非常良好的分析体验。

之所以ByteHouse在多表关联场景表现如此出色,其中一大原因就是因为字节自研了查询优化器,弥补了社区ClickHouse的一大不足。查询优化器都能带来哪些体验上的优化?字节是如何实现查询优化器的?我们会在下一期为大家详细介绍。

相关文章
|
1月前
|
存储 SQL 缓存
优化ClickHouse查询性能:最佳实践与调优技巧
【10月更文挑战第26天】在大数据分析领域,ClickHouse 以其卓越的查询性能和高效的列式存储机制受到了广泛的关注。作为一名已经有一定 ClickHouse 使用经验的开发者,我深知在实际应用中,合理的表设计、索引优化以及查询优化对于提升 ClickHouse 性能的重要性。本文将结合我的实践经验,分享一些有效的优化策略。
106 3
|
6月前
|
存储 SQL 消息中间件
ClickHouse(12)ClickHouse合并树MergeTree家族表引擎之AggregatingMergeTree详细解析
AggregatingMergeTree是ClickHouse的一种表引擎,它优化了MergeTree的合并逻辑,通过将相同主键(排序键)的行聚合为一行并存储聚合函数状态来减少行数。适用于增量数据聚合和物化视图。建表语法中涉及AggregateFunction和SimpleAggregateFunction类型。插入数据需使用带-State-的聚合函数,查询时使用GROUP BY和-Merge-。处理逻辑包括按排序键聚合、在合并分区时计算、以分区为单位聚合等。常用于物化视图配合普通MergeTree使用。查阅更多资料可访问相关链接。
314 4
|
6月前
|
存储 SQL 算法
ClickHouse(13)ClickHouse合并树MergeTree家族表引擎之CollapsingMergeTree详细解析
CollapsingMergeTree是ClickHouse的一种表引擎,它扩展了`MergeTree`,通过折叠行来优化存储和查询效率。当`Sign`列值为1和-1的成对行存在时,该引擎会异步删除除`Sign`外其他字段相同的行,只保留最新状态。建表语法中,`sign`列必须为`Int8`类型,用来标记状态(1)和撤销(-1)。写入时,应确保状态和撤销行的对应关系以保证正确折叠。查询时,可能需要使用聚合函数如`sum(Sign * x)`配合`GROUP BY`来处理折叠后的数据。使用`FINAL`修饰符可强制折叠,但效率较低。系列文章提供了更多关于ClickHouse及其表引擎的详细解析。
226 1
|
5月前
|
负载均衡 数据管理
ClickHouse的分布式查询流程
ClickHouse的分布式查询流程
|
6月前
|
存储 关系型数据库 数据库
【DDIA笔记】【ch2】 数据模型和查询语言 -- 多对一和多对多
【6月更文挑战第7天】该文探讨数据模型,比较了“多对一”和“多对多”关系。通过使用ID而不是纯文本(如region_id代替"Greater Seattle Area"),可以实现统一、避免歧义、简化修改、支持本地化及优化搜索。在数据库设计中,需权衡冗余和范式。文档型数据库适合一对多但处理多对多复杂,若无Join,需应用程序处理。关系型数据库则通过外键和JOIN处理这些关系。文章还提及文档模型与70年代层次模型的相似性,层次模型以树形结构限制了多对多关系处理。为克服层次模型局限,发展出了关系模型和网状模型。
64 6
|
6月前
|
传感器 存储 SQL
ClickHouse(15)ClickHouse合并树MergeTree家族表引擎之GraphiteMergeTree详细解析
GraphiteMergeTree是ClickHouse用于优化Graphite数据存储和汇总的表引擎,适合需要瘦身和高效查询Graphite数据的开发者。它基于MergeTree,减少存储空间并提升查询效率。创建表时需包括Path、Time、Value和Version列。配置涉及pattern、regexp、function和retention,用于指定聚合函数和数据保留规则。文章还提供了建表语句示例和相关资源链接。
99 1
|
6月前
|
存储 SQL 关系型数据库
ClickHouse(11)ClickHouse合并树MergeTree家族表引擎之SummingMergeTree详细解析
`SummingMergeTree`是`MergeTree`引擎的变种,它合并相同主键的行并计算数值列的总和,从而节省存储空间和加速查询。通常与`MergeTree`配合使用,存储聚合数据以避免数据丢失。创建`SummingMergeTree`表时,可选参数`columns`指定要汇总的数值列。未指定时,默认汇总所有非主键数值列。注意,聚合可能不完整,查询时需用`SUM`和`GROUP BY`。文章还介绍了建表语法、数据处理规则以及对嵌套数据结构和`AggregateFunction`列的处理。查阅更多ClickHouse相关内容可访问相关链接。
246 5
|
6月前
|
SQL 人工智能 关系型数据库
【DDIA笔记】【ch2】 数据模型和查询语言 -- 文档模型中Schema的灵活性
【6月更文挑战第8天】网状模型是层次模型的扩展,允许节点有多重父节点,但导航复杂,需要预知数据库结构。关系模型将数据组织为元组和关系,强调声明式查询,解耦查询语句与执行路径,简化了访问并通过查询优化器提高效率。文档型数据库适合树形结构数据,提供弱模式灵活性,但在Join支持和访问局部性上不如关系型。关系型数据库通过外键和Join处理多对多关系,适合高度关联数据。文档型数据库的模式灵活性体现在schema-on-read,写入时不校验,读取时解析,牺牲性能换取灵活性。适用于不同类型或结构变化的数据场景。
53 0
|
6月前
|
SQL JSON NoSQL
【DDIA笔记】【ch2】 数据模型和查询语言 -- 关系模型与文档模型
【6月更文挑战第6天】关系模型是主流数据库模型,以二维表形式展示数据,支持关系算子。分为事务型、分析型和混合型。尽管有其他模型挑战,如网状和层次模型,但关系模型仍占主导。然而,随着大数据增长和NoSQL的出现(如MongoDB、Redis),强调伸缩性、专业化查询和表达力,关系模型的局限性显现。面向对象编程与SQL的不匹配导致“阻抗不匹配”问题,ORM框架缓解但未完全解决。文档模型(如JSON)提供更自然的嵌套结构,适合表示复杂关系,具备模式灵活性和更好的数据局部性。
57 0
|
6月前
|
存储 SQL 算法
ClickHouse(14)ClickHouse合并树MergeTree家族表引擎之VersionedCollapsingMergeTree详细解析
VersionedCollapsingMergeTree是ClickHouse的一种优化引擎,扩展了MergeTree,支持多线程异步插入和高效的数据折叠。它通过Sign和Version列处理对象状态的变化,Sign表示行的状态(正向或撤销),Version追踪状态版本。引擎自动删除旧状态,减少存储占用。在查询时,需注意可能需使用GROUP BY和聚合函数确保数据折叠,因为ClickHouse不保证查询结果已折叠。文章还提供了建表语法、使用示例和相关资源链接。
180 0
下一篇
DataWorks