小表join顺序和广播问题

最新推荐文章于 2023-07-20 12:01:37 发布
最新推荐文章于 2023-07-20 12:01:37 发布
阅读量1k 收藏 4
点赞数 1
分类专栏： 大数据 spark 文章标签： 大数据 spark 分布式
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/L13763338360/article/details/127734970
版权

1. 问题背景

最近CRM平台上报的数据，DI ingestion老出问题，需要做一个CRM平台的上报数据与后台统计数据的基础对账功能。大概步骤如下：

  1. CRM用flink + hive方式，ingestion到近实时日志表
  2. 统计后台昨天entry的所有task，及每个task的用户数
  3. 根据后台统计的task，统计日志表昨天每个task用户数
  4. 后台统计的每个task用户数与日志表统计的用户数进行对比

发现在第3步，即统计昨天每个task的用户数时，spark任务OOM了。

2. 问题分析

示例代码

explain
with be as (
  select
    task_tab.region as region,
    journey_tab.journey_name as journey_name,
    task_tab.journey_id as journey_id,
    task_tab.version_id as version_id,
    task_tab.id as task_id,
    task_tab.total_user_count as be_user_count
  from table_xx1 as task_tab
  left join table_xx2 as journey_tab on journey_tab.id = task_tab.journey_id and journey_tab.region = task_tab.region
  left join table_xx3 as version_tab on version_tab.id = task_tab.version_id and version_tab.region = task_tab.region
  where task_tab.create_time >= unix_timestamp('2022-11-03', 'yyyy-MM-dd')
    and task_tab.create_time < unix_timestamp('2022-11-04', 'yyyy-MM-dd')
    and get_json_object(version_tab.schedule_setting,'$.repeatSetting.frequency') = 1
),
de as (
  select
    be.region as region,
    be.journey_name as journey_name,
    be.journey_id as journey_id,
    be.version_id as version_id,
    be.task_id as task_id,
    count(distinct user_id) as de_user_count
  from be
  left join table_xx4 as tmp on tmp.grass_region = be.region
    and tmp.journey_id = be.journey_id
    and tmp.task_id = be.task_id
    and tmp.grass_date = '2022-11-03'
  group by 1,2,3,4,5
)
select
  be.region,
  be.journey_name,
  be.journey_id,
  be.version_id,
  be.task_id,
  be.be_user_count,
  de.de_user_count
from be
left join de on de.region = be.region and de.task_id = be.task_id
;

发现一直卡在下面这段SQL：

select
  be.region as region,
  be.journey_name as journey_name,
  be.journey_id as journey_id,
  be.version_id as version_id,
  be.task_id as task_id,
  count(distinct user_id) as de_user_count
from be
left join table_xx4 as tmp on tmp.grass_region = be.region
  and tmp.journey_id = be.journey_id
  and tmp.task_id = be.task_id
  and tmp.grass_date = '2022-11-03'
group by 1,2,3,4,5

统计分析发现，其实临时表be的数据量只有几十条，ods_crm_di_user_task_rt_live虽然过滤完还有20亿条左右的数据，理论上来说会进行小表广播优化，不应该那么慢，看yarn ui分析，发现大量spill disk和shuffle操作。

初步分析有两个问题：一方面是大小表join顺序问题，二是物理计划解析对临时表无法识别大小。临时表be没有被判断出是小表，无法进行优化并广播，直接采用普通的left join，详细分析看下面。

2.1. 临时小表join大表与broadcast

2.1.1 临时小表left join大表：不广播

explain sql查看原来sql物理计划，left join底层实际是用SortMergeJoin，没有按预期进行小表广播，没有用预期的BroadcastHashJoin。

2.1.2. 临时小表inner join大表：不广播

left join改成inner join不影响最终想要的结果，看看物理执行计划，inner join底层还是SortMergeJoin，还是没有按预期优化成BroadcastHashJoin。

 2.1.3. 大表left join临时小表：不广播

代码示例如下，调整大小表left join顺序，大表在前，小表在后，也还是无法将SortMergeJoin优化成BroadcastHashJoin！

2.2. BroadcastHashJoin与hint用法

2.2.1. 临时小表left join大表 + hint广播：不广播

示例代码

explain
with be as (
    select
        task_tab.region as region,
        journey_tab.journey_name as journey_name,
        task_tab.journey_id as journey_id,
        task_tab.version_id as version_id,
        task_tab.id as task_id,
        task_tab.total_user_count as be_user_count
    from table_xx1 as task_tab
    left join table_xx2 as journey_tab on journey_tab.id = task_tab.journey_id and journey_tab.region = task_tab.region
    left join table_xx3 as version_tab on version_tab.id = task_tab.version_id and version_tab.region = task_tab.region
    where task_tab.create_time >= unix_timestamp('2022-11-03', 'yyyy-MM-dd')
        and task_tab.create_time < unix_timestamp('2022-11-04', 'yyyy-MM-dd')
        and get_json_object(version_tab.schedule_setting,'$.repeatSetting.frequency') = 1
),
de as (
    select
        /*+ BROADCAST(be) */
        be.region as region,
        be.journey_name as journey_name,
        be.journey_id as journey_id,
        be.version_id as version_id,
        be.task_id as task_id,
        count(distinct user_id) as de_user_count
    from be
    left join table_xx4 as tmp on tmp.grass_region = be.region
        and tmp.journey_id = be.journey_id
        and tmp.task_id = be.task_id
        and tmp.grass_date = '2022-11-03'
    group by 1,2,3,4,5
)
select
    be.region,
    be.journey_name,
    be.journey_id,
    be.version_id,
    be.task_id,
    be.be_user_count,
    de.de_user_count
from be
left join de on de.region = be.region and de.task_id = be.task_id
;

2.2.2 临时小表inner join大表 + hint广播：广播

示例代码

explain
with be as (
    select
        task_tab.region as region,
        journey_tab.journey_name as journey_name,
        task_tab.journey_id as journey_id,
        task_tab.version_id as version_id,
        task_tab.id as task_id,
        task_tab.total_user_count as be_user_count
    from table_xx1 as task_tab
    left join table_xx2 as journey_tab on journey_tab.id = task_tab.journey_id and journey_tab.region = task_tab.region
    left join table_xx3 as version_tab on version_tab.id = task_tab.version_id and version_tab.region = task_tab.region
    where task_tab.create_time >= unix_timestamp('2022-11-03', 'yyyy-MM-dd')
        and task_tab.create_time < unix_timestamp('2022-11-04', 'yyyy-MM-dd')
        and get_json_object(version_tab.schedule_setting,'$.repeatSetting.frequency') = 1
),
de as (
    select
        /*+ BROADCAST(be) */
        be.region as region,
        be.journey_name as journey_name,
        be.journey_id as journey_id,
        be.version_id as version_id,
        be.task_id as task_id,
        count(distinct user_id) as de_user_count
    from be
    inner join table_xx4 as tmp on tmp.grass_region = be.region
        and tmp.journey_id = be.journey_id
        and tmp.task_id = be.task_id
        and tmp.grass_date = '2022-11-03'
    group by 1,2,3,4,5
)
select
    be.region,
    be.journey_name,
    be.journey_id,
    be.version_id,
    be.task_id,
    be.be_user_count,
    de.de_user_count
from be
left join de on de.region = be.region and de.task_id = be.task_id
;

2.2.3. 大表left join临时小表 + hint广播：广播

示例代码

explain
with be as (
    select
        task_tab.region as region,
        journey_tab.journey_name as journey_name,
        task_tab.journey_id as journey_id,
        task_tab.version_id as version_id,
        task_tab.id as task_id,
        task_tab.total_user_count as be_user_count
    from table_xx1 as task_tab
    left join table_xx2 as journey_tab on journey_tab.id = task_tab.journey_id and journey_tab.region = task_tab.region
    left join table_xx3 as version_tab on version_tab.id = task_tab.version_id and version_tab.region = task_tab.region
    where task_tab.create_time >= unix_timestamp('2022-11-03', 'yyyy-MM-dd')
        and task_tab.create_time < unix_timestamp('2022-11-04', 'yyyy-MM-dd')
        and get_json_object(version_tab.schedule_setting,'$.repeatSetting.frequency') = 1
),
de as (
    select
        /*+ BROADCAST(be) */
        be.region as region,
        be.journey_name as journey_name,
        be.journey_id as journey_id,
        be.version_id as version_id,
        be.task_id as task_id,
        count(distinct user_id) as de_user_count
    from table_xx4 as tmp
    inner join be on tmp.grass_region = be.region
        and tmp.journey_id = be.journey_id
        and tmp.task_id = be.task_id
        and tmp.grass_date = '2022-11-03'
    group by 1,2,3,4,5
)
select
    be.region,
    be.journey_name,
    be.journey_id,
    be.version_id,
    be.task_id,
    be.be_user_count,
    de.de_user_count
from be
left join de on de.region = be.region and de.task_id = be.task_id
;

2.3 中间小表join大表与broadcast

将临时小表落地到中间表

drop table temp;
create table temp
select
    task_tab.region as region,
    journey_tab.journey_name as journey_name,
    task_tab.journey_id as journey_id,
    task_tab.version_id as version_id,
    task_tab.id as task_id,
    task_tab.total_user_count as be_user_count
from table_xx1 as task_tab
left join table_xx2 as journey_tab on journey_tab.id = task_tab.journey_id and journey_tab.region = task_tab.region
left join table_xx3 as version_tab on version_tab.id = task_tab.version_id and version_tab.region = task_tab.region
where task_tab.create_time >= unix_timestamp('2022-11-03', 'yyyy-MM-dd')
    and task_tab.create_time < unix_timestamp('2022-11-04', 'yyyy-MM-dd')
    and get_json_object(version_tab.schedule_setting,'$.repeatSetting.frequency') = 1
;

2.3.1. 中间表小left join大表：不广播

示例代码

explain

select

    tmp.grass_region as region,

    tmp.journey_name as journey_name,

    tmp.journey_id as journey_id,

    tmp.version_id as version_id,

    tmp.task_id as task_id,

    count(distinct user_id) as de_user_count

from temp as be

left join table_xx4 as tmp on tmp.grass_region = be.region

    and tmp.journey_id = be.journey_id

    and tmp.task_id = be.task_id

    and tmp.grass_date = '2022-11-03'

    and tmp.grass_region != 'ID'

group by 1,2,3,4,5;

2.3.1. 中间表小inner join大表：广播

示例代码

explain

select

    tmp.grass_region as region,

    tmp.journey_name as journey_name,

    tmp.journey_id as journey_id,

    tmp.version_id as version_id,

    tmp.task_id as task_id,

    count(distinct user_id) as de_user_count

from temp as be

inner join table_xx4 as tmp on tmp.grass_region = be.region

    and tmp.journey_id = be.journey_id

    and tmp.task_id = be.task_id

    and tmp.grass_date = '2022-11-03'

    and tmp.grass_region != 'ID'

group by 1,2,3,4,5;

2.3.3. 大表left join中间小表：广播

示例代码

explain

select

    tmp.grass_region as region,

    tmp.journey_name as journey_name,

    tmp.journey_id as journey_id,

    tmp.version_id as version_id,

    tmp.task_id as task_id,

    count(distinct user_id) as de_user_count

from table_xx4 as tmp

inner join temp as be on tmp.grass_region = be.region

    and tmp.journey_id = be.journey_id

    and tmp.task_id = be.task_id

    and tmp.grass_date = '2022-11-03'

    and be.task_id is not null

group by 1,2,3,4,5;

3. 问题总结

  1.  临时表：复杂sql生成的临时表，哪些只有少量数据，spark-sql并不会识别临时表大小并进行优化和广播
  2. left join顺序：小表left join大表，无论如何也不会对小表进行广播；想要广播小表，必须大表left join小表
  3. hint显式广播：临时小表inner join大表、大表left join临时小表，可以用hint显示指定广播，达到SortMergeJoin优化成BroadcastHashJoin的目的
  4. 中间表替换临时表：临时小表落地到中间表，中间小表inner join或者大表、大表left join中间小表，无需hint显示指定广播，spark-sql都可以自动识别优化，SortMergeJoin优化成BroadcastHashJoin
L13763338360
关注
  • 1
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
查询优化难点：Join顺序选择 避免误区
12-14
Join的操作每次都只能在两个表之间进行，因此，如果一个查询要Join的表有N个，那么，需要进行（N-1）次的Join操作，即：先第1个表和第2个表Join，然后Join的结果在和第3个表进行Join，以此类推。
MySQL笔记
weixin_44003193的博客
11-25 166
MySQL 1.修改密码: mysqladmin -uroot -p密码 password 新密码 2.表？table table是数据库的基本组成单元，所有的数据以表格的形式组织，可读性强。 一个表包含行和列： 行：被称为数据(data) 列：被称为字段(column) 学号(int) 姓名(varchar) 年龄(int) ------------------------------...
SQL语句之分组查询--GROUP BY（group by）
热门推荐
traveler-leon的博客
04-21 4万+
sql语句select之分组查询（group by） 分组查询 语法： select 分组函数，列（要求出现在group by的后面） from 表 where 筛选条件 group by 分组的列表 order by 子句 注意：查询列表比较特殊，要求是分组函数和group by后的字段相同 特点：分组查询中的筛选条件分为两类： 分组前筛选： 数据源是原始表，用where，放在group by前面，因为在分组前筛选 分组后筛选：数据源是分组后的结果集 ，用having，放在group by，因为在分组后进
看一遍就理解：数据库group by详解
程序员小灰的博客
07-20 5769
前言 大家好，我是程序员小灰。日常开发中，我们经常会使用到group by。亲爱的小伙伴，你是否知道group by的工作原理呢？group by和having有什么区别呢？group by的优化思路是怎样的呢？使用group by有哪些需要注意的问题呢？本文将跟大家一起来学习，攻克group by~使用group by的简单例子group by 工作原理group by + where 和 ha...
T-SQL 高级应用之数据汇总（聚合函数之2 group by） .
SelfImprovement
10-28 3700
1、概述2、原始表3、简单Group By4、Group By 和 Order By5、Group By中Select指定的字段限制6、Group By All7、Group By与聚合函数8、Having与Where的区别9、Compute 和 Compute By 1、概述 “Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分
想要弄懂GROUP BY看这一篇就够了
mffandxx的博客
12-22 1953
一、前言 group by 关键字，不管是工作中还是面试都会经常被用到，所以弄懂它是非常有必要的。要弄懂group by 那我们就得联合着：聚合函数、group by 、having 一块讲解。 讲之前我们先准备一张表： 二、聚合函数 为了讲好group by我们必须先将聚合函数，所以大家别着急，一步步往下看。 聚合函数是将“若干行数据”经过计算后聚合成“一行数据” 常用的聚合函数如下： 1.MAX：返回某列的最大值 2.MIN(column) 返回某列的最高值 3.COUNT(column) 返回某
MySQL数据库---分组查询（group by）
不会编程的派大星的博客
09-11 938
一：分组函数的语句顺序 1 SELECT ... 2 FROM ... 3 WHERE ... 4 GROUP BY ... 5 HAVING ... 6 ORDER BY ... 二：WHERE和HAVING筛选条件的区别 数据源 位置 关键字 WHERE 原始表 ORDER BY语句之前 WHERE
mysql group by 1 2 3,mysql group by 分组
weixin_34160181的博客
03-17 2097
mysql>select*from employee_tbl;+----+--------+---------------------+--------+| id | name | date | singin |+----+--------+---------------------+--------+| 1 | 小明 | 2016-04-22 15...
数据库group by用法
Chen_Qi_Tao的博客
06-05 4937
group by是对表中每一列数据的分类的集合，例如一个表中有一个列名为a的一列，这一列中有1，1，2，2，3这五个数据，在使用group by a之后，返回的值应该使1、2、3， group by 经常与min,max,avg一起用。 通常我们会在group by后面加上having ,having的意思是对集合中的每一项进行限制,比如group by a having sum(a)>3;...
group by分组使用
weixin_43808717的博客
06-02 1687
1、聚合函数与group by的联系 严格意义来说聚合函数也多多少少跟group by分组存在着一些关系，甚至有些教科书上聚合函数有一些其他的名字，分组函数、多行处理函数… 其实聚合函数使用时虽然明面上没有使用group by进行分组，但是可以理解为使用group by将所有的数据分为了一组然后在使用聚合函数！ 二者在sql中的执行顺序：一定是聚合函数在group by之后进行执行！ 2、group by的使用 2.1、使用案例一 查询最高工资与查询每个工作岗位的最高工资 查询最高工资
Spark广播变量
macfei的博客
09-26 317
广播的目的 广播变量是为了实现mapside join ,可以将Driver端的数据广播到属于该application的Executor，然后通过Driver广播变量返回的引用，获取事先广播到Executor的数据。 mapside join 当连接的两个表是一个比较大的表和一个比较小的时候，我们把比较小的table直接放到内存中去，然后再去对比较大的表格进行map操作。join就发生在map操作的时候，每当扫描一个大的table中的数据，就要去查看小表的数据，哪条与之相符，继而进行连接，这里的join并不
Yii2中使用join、joinwith多表关联查询
12-19
现在有客户表、订单表、图书表、作者表， 客户表Customer (id customer_name） 订单表Order (id order_name customer_id book_id) 图书表 (id book_name author_id) 作者表 (id author_name) 模型定义 下面是这4个个...
解决在laravel中leftjoin带条件查询没有返回右表为NULL的问题
10-16
今天小编就为大家分享一篇解决在laravel中leftjoin带条件查询没有返回右表为NULL的问题，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧
mysql使用from与join两表查询的区别总结
09-09
主要给大家介绍了关于mysql使用from与join两表查询的区别的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
Mysql join联表及id自增实例解析
09-14
主要介绍了Mysql join联表及id自增实例解析,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
SQL语句中的Group BY分组查询
weixin_46223196的博客
11-18 2973
Group BY 分组 group by 语句根据一个或多个列对结果集进行分组，一般情况下，是用与数据的统计或计算，配合聚合函数使用 统计 users 表中 男女生人数 – 普通查询 select count(*) from users where sex = '女'; select count(*) from users where sex = '男'; – 分组查询 select sex,count(*) from users group by sex; +------+----------+ |
查询语句 group by 分组
weixin_50077855的博客
10-14 7390
查询语句 group by 分组 建表语句在最后 group by 关键字可以根据一个或多个字段对查询结果进行分组 group by 一般都会结合Mysql聚合函数来使用 如果需要指定条件来过滤分组后的结果集，需要结合 having 关键字；**原因：**where不能与聚合函数联合使用 并且 where 是在 group by 之前执行的 group by 的语法格式 GROUP BY <字段名>[,<字段名>,<字段名>] 先确认测试表里有什么数据，方便后面例
SparkSQL中的三种Join及其具体实现（broadcast join、shuffle hash join和sort merge join）
mojolang
02-11 2139
1.小表对大表（broadcast join） 将小表的数据分发到每个节点上，供大表使用。executor存储小表的全部数据，一定程度上牺牲了空间，换取shuffle操作大量的耗时，这在SparkSQL中称作Broadcast Join Broadcast Join的条件有以下几个： *被广播的表需要小于 spark.sql.autoBroadcastJoinThreshold 所配置的值，默认是10M （或者加了broadcast join的hint） *基表不能被广播，比如 left outer
mysql group by 1 2 3_mysql group by 用法解析(详细)
weixin_39834281的博客
01-19 5877
在使用mysql时，有时需要查询出某个字段不重复的记录，虽然mysql提供 有distinct这个关键字来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是 distinct只能返回它的目标字段，而无法返回其它字段，这个问题让我困扰了很久，用distinct不能解决的话，我只有用二重循环查询来解决，而 这样对于一个数据量非常大的站来说，无...
mybatis多表查询inner join和left join执行顺序
最新发布
07-29
在mybatis多表查询中，inner join和left join的执行顺序是根据表的连接顺序来确定的。在使用mybatis-plus插件进行连表查询时，可以通过leftJoin、rightJoin和innerJoin方法来指定连接的表和连接条件。[1]根据引用[2]中的示例SQL语句，可以看出leftJoin和innerJoin是按照它们在查询语句中的顺序执行的。在这个例子中，先执行了leftJoin连接user表和user_address表，然后再执行了innerJoin连接user_address表和area表。因此，inner join和left join的执行顺序是根据它们在查询语句中的顺序来确定的。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值