Hive中LEFT OUTER JOIN和INNER JOIN连用时数据缺失问题

已于 2022-10-14 15:45:59 修改
阅读量2.3k 收藏 6
点赞数 1
分类专栏： Hive SQL 文章标签： hive 数据仓库
于 2022-10-14 11:39:41 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhangliushi/article/details/127314661
版权
Hive 同时被 2 个专栏收录
17 篇文章 0 订阅
订阅专栏
SQL
15 篇文章 0 订阅
订阅专栏

山重水复疑无路，柳暗花明又一村。
——陆游《游山西村》

文章目录

前言

JOIN连接是SQL常用的关联方式，但他们之前连用时可能会出现数据缺失的情况，本文分享生产中的bug案例，目前已有解决方案，具体原因为个人理解，如有错误，请各位小伙伴解答。

一、具体场景

Hive建表时，需要用到left outer join加上inner join，当他们连用时，发现数据缺失严重。

SELECT
    t1.arrange_date
   ,t2.fmaterialnumber                                                                                     AS new_material_code
   ,t1.fbillno                                                                                             AS bill_code
   ,MAX(t2.ftaxprice)                                                                                      AS price_unit
   ,SUM(t2.frealqty)                                                                                       AS sale_out_qty
   ,SUM(t2.fallamount_lc)                                                                                  AS sale_out_price_total_rmb
   ,IF((t1.arrange_date <= '2021-05-31' AND t1.fbillno IN ('OUSO20210319321696', 'OUSO20210322323127'))
       ,'Lumidept00165', t1.fsaledeptnumber)                                                               AS dept_code
   ,t1.salesman_code
   ,t2.fmainid                                                                                             AS main_id
   ,t1.system_source
   ,t1.fcustnumber
   ,t2.warehouse_code
   ,t1.fbilltypenumber
   ,t2.delivery_order_id
   ,t1.f_aqa_recaddr
   ,t1.fsaleorgid                                                                                          AS sales_organization
   ,SUM(t2.fcostamount_lc)                                                                                 AS cost_rmb
   ,t2.fsrcbillno
   ,t1.fbilltypename
   ,t2.fentryid
   ,t1.fbasecurrnumber                                                                                     AS currency_code
   ,t2.funitnumber                                                                                         AS unit
   ,SUM(t2.famount)                                                                                        AS sale_out_amt_total
   ,SUM(t2.famount_lc)                                                                                     AS sale_out_amt_total_rmb
   ,t1.fsettleorgid
FROM
(
    SELECT
        t3.fid
       ,t3.fbillno
       ,t3.system_source
       ,t3.arrange_date
       ,t3.fsaledeptnumber
       ,t3.salesman_code
       ,t3.fcustnumber
       ,t3.fbilltypenumber
       ,t3.fbilltypename
       ,t3.f_aqa_recaddr
       ,t3.fsaleorgid
       ,t3.fbasecurrnumber
       ,t4.fsettleorgid
    FROM
    (
        SELECT
            fid
           ,fbillno
           ,system_source
           ,arrange_date
           ,fsaledeptnumber
           ,salesman_code
           ,fcustnumber
           ,fbilltypenumber
           ,fbilltypename
           ,f_aqa_recaddr
           ,fsaleorgid
           ,fbasecurrnumber
        FROM tmp.xxx
        WHERE rn = 1
        AND is_delete IS NOT TRUE
    ) t3
    LEFT OUTER JOIN
    (
        SELECT
            fmainid
           ,fsettleorgid
        FROM tmp.yyy
        WHERE rn = 1
        AND is_delete IS NOT TRUE
    ) t4
    ON t3.fid = t4.fmainid
) t1
INNER JOIN
(
    SELECT
        fmainid
       ,ftaxprice
       ,frealqty
       ,fallamount_lc
       ,fmaterialnumber
       ,delivery_order_id
       ,warehouse_code
       ,fcostamount_lc
       ,fsrcbillno
       ,fentryid
       ,funitnumber
       ,famount
       ,famount_lc
    FROM tmp.zzz
    WHERE rn = 1
    AND is_delete IS NOT TRUE
) t2
ON t1.fid = t2.fmainid
GROUP BY
    t1.arrange_date
   ,t2.fmaterialnumber
   ,t1.fbillno
   ,t1.fsaledeptnumber
   ,t1.salesman_code
   ,t2.fmainid
   ,t1.system_source
   ,t1.fcustnumber
   ,t1.fbilltypenumber
   ,t2.delivery_order_id
   ,t1.f_aqa_recaddr
   ,t1.fsaleorgid
   ,t2.warehouse_code
   ,t2.fsrcbillno
   ,t1.fbilltypename
   ,t2.fentryid
   ,t1.fbasecurrnumber
   ,t2.funitnumber
   ,t1.fsettleorgid
;

在这里插入图片描述
在这里插入图片描述

二、问题分析思路

  1. 将逻辑分步跑，发现在inner join后数据开始不正确。
  2. 将inner join换为left outer join结果也依然是数据缺失。
  3. 网上博文启发，Inner join和Outer join一起使用的注意点，如下图所示。
  4. 查各关联条件发现为同一关联条件，但各表中关联条件的数据量并不一致，就像下图案例一样，因为inner join的关联条件宠物类型是需要有宠物才会有相应宠物类型，有一个人没有养宠物，那他实际上就不会有相应的宠物类型，这时关联会出现数据缺失。
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
-- 每个人养宠物的情况
select
   t1.personname 
   ,t2.petname
from
(
    select
        personid 
        ,personname   
    from people
) t1
left outer join
(
    select
        ownerid 
        ,petname 
    from pets 
) t2
on t1.personid = t2.ownerid
;

在这里插入图片描述

-- 再匹配上相应宠物类型
select
    t3.personname
    ,t3.petname
    ,t4.pettype
from 
(
select
   t1.personname 
   ,t2.petname
   ,t2.pettypeid
from
(
    select
        personid 
        ,personname   
    from people
) t1
left outer join
(
    select
        ownerid 
        ,petname
        ,pettypeid 
    from pets 
) t2
on t1.personid = t2.ownerid
) t3
inner join 
(
    select
        pettypeid
        ,pettype 
    from pettypes
) t4
on t3.pettypeid = t4.pettypeid
;

在这里插入图片描述
在这里插入图片描述

三、解决方案

  1. 将left outer join提到外层
SELECT
    t3.arrange_date
   ,t3.new_material_code
   ,t3.bill_code
   ,t3.price_unit
   ,t3.sale_out_qty
   ,t3.sale_out_price_total_rmb
   ,t3.dept_code
   ,t3.salesman_code
   ,t3.main_id
   ,t3.system_source
   ,t3.fcustnumber
   ,t3.warehouse_code
   ,t3.fbilltypenumber
   ,t3.delivery_order_id
   ,t3.f_aqa_recaddr
   ,t3.sales_organization
   ,t3.cost_rmb
   ,t3.fsrcbillno
   ,t3.fbilltypename
   ,t3.fentryid
   ,t3.currency_code
   ,t3.unit
   ,t3.sale_out_amt_total
   ,t3.sale_out_amt_total_rmb
   ,t4.fsettleorgid
FROM
(
    SELECT
        t1.arrange_date
       ,t2.fmaterialnumber                                                                                     AS new_material_code
       ,t1.fbillno                                                                                             AS bill_code
       ,MAX(t2.ftaxprice)                                                                                      AS price_unit
       ,SUM(t2.frealqty)                                                                                       AS sale_out_qty
       ,SUM(t2.fallamount_lc)                                                                                  AS sale_out_price_total_rmb
       ,IF((t1.arrange_date <= '2021-05-31' AND t1.fbillno IN ('OUSO20210319321696', 'OUSO20210322323127'))
           ,'Lumidept00165', t1.fsaledeptnumber)                                                               AS dept_code
       ,t1.salesman_code
       ,t2.fmainid                                                                                             AS main_id
       ,t1.system_source
       ,t1.fcustnumber
       ,t2.warehouse_code
       ,t1.fbilltypenumber
       ,t2.delivery_order_id
       ,t1.f_aqa_recaddr
       ,t1.fsaleorgid                                                                                          AS sales_organization
       ,SUM(t2.fcostamount_lc)                                                                                 AS cost_rmb
       ,t2.fsrcbillno
       ,t1.fbilltypename
       ,t2.fentryid
       ,t1.fbasecurrnumber                                                                                     AS currency_code
       ,t2.funitnumber                                                                                         AS unit
       ,SUM(t2.famount)                                                                                        AS sale_out_amt_total
       ,SUM(t2.famount_lc)                                                                                     AS sale_out_amt_total_rmb
    FROM
    (
        SELECT
            fid
           ,fbillno
           ,system_source
           ,arrange_date
           ,fsaledeptnumber
           ,salesman_code
           ,fcustnumber
           ,fbilltypenumber
           ,fbilltypename
           ,f_aqa_recaddr
           ,fsaleorgid
           ,fbasecurrnumber
        FROM tmp.xxx
        WHERE rn = 1
        AND is_delete IS NOT TRUE
    ) t1
    INNER JOIN
    (
        SELECT
            fmainid
           ,ftaxprice
           ,frealqty
           ,fallamount_lc
           ,fmaterialnumber
           ,delivery_order_id
           ,warehouse_code
           ,fcostamount_lc
           ,fsrcbillno
           ,fentryid
           ,funitnumber
           ,famount
           ,famount_lc
        FROM tmp.yyy
        WHERE rn = 1
        AND is_delete IS NOT TRUE
    ) t2
    ON t1.fid = t2.fmainid
    GROUP BY
        t1.arrange_date
       ,t2.fmaterialnumber
       ,t1.fbillno
       ,t1.fsaledeptnumber
       ,t1.salesman_code
       ,t2.fmainid
       ,t1.system_source
       ,t1.fcustnumber
       ,t1.fbilltypenumber
       ,t2.delivery_order_id
       ,t1.f_aqa_recaddr
       ,t1.fsaleorgid
       ,t2.warehouse_code
       ,t2.fsrcbillno
       ,t1.fbilltypename
       ,t2.fentryid
       ,t1.fbasecurrnumber
       ,t2.funitnumber
) t3
LEFT OUTER JOIN
(
    SELECT
        fmainid
       ,fsettleorgid
    FROM tmp.zzz
    WHERE rn = 1
    AND is_delete IS NOT TRUE
) t4
ON t3.main_id = t4.fmainid
;
  1. 先形成临时表再inner join的话结果是正确的。
-- 形成临时表
CREATE TABLE IF NOT EXISTS tmp.aaa
AS
SELECT
    t3.fid
   ,t3.fbillno
   ,t3.system_source
   ,t3.arrange_date
   ,t3.fsaledeptnumber
   ,t3.salesman_code
   ,t3.fcustnumber
   ,t3.fbilltypenumber
   ,t3.fbilltypename
   ,t3.f_aqa_recaddr
   ,t3.fsaleorgid
   ,t3.fbasecurrnumber
   ,t4.fsettleorgid
FROM
(
    SELECT
        fid
       ,fbillno
       ,system_source
       ,arrange_date
       ,fsaledeptnumber
       ,salesman_code
       ,fcustnumber
       ,fbilltypenumber
       ,fbilltypename
       ,f_aqa_recaddr
       ,fsaleorgid
       ,fbasecurrnumber
    FROM tmp.xxx
    WHERE rn = 1
    AND is_delete IS NOT TRUE
) t3
LEFT OUTER JOIN
(
    SELECT
        fmainid
       ,fsettleorgid
    FROM tmp.yyy
    WHERE rn = 1
    AND is_delete IS NOT TRUE
) t4
ON t3.fid = t4.fmainid
;

--从临时表中取数据关联
SELECT
    t1.arrange_date
   ,t2.fmaterialnumber                                                                                     AS new_material_code
   ,t1.fbillno                                                                                             AS bill_code
   ,MAX(t2.ftaxprice)                                                                                      AS price_unit
   ,SUM(t2.frealqty)                                                                                       AS sale_out_qty
   ,SUM(t2.fallamount_lc)                                                                                  AS sale_out_price_total_rmb
   ,IF((t1.arrange_date <= '2021-05-31' AND t1.fbillno IN ('OUSO20210319321696', 'OUSO20210322323127'))
       ,'Lumidept00165', t1.fsaledeptnumber)                                                               AS dept_code
   ,t1.salesman_code
   ,t2.fmainid                                                                                             AS main_id
   ,t1.system_source
   ,t1.fcustnumber
   ,t2.warehouse_code
   ,t1.fbilltypenumber
   ,t2.delivery_order_id
   ,t1.f_aqa_recaddr
   ,t1.fsaleorgid                                                                                          AS sales_organization
   ,SUM(t2.fcostamount_lc)                                                                                 AS cost_rmb
   ,t2.fsrcbillno
   ,t1.fbilltypename
   ,t2.fentryid
   ,t1.fbasecurrnumber                                                                                     AS currency_code
   ,t2.funitnumber                                                                                         AS unit
   ,SUM(t2.famount)                                                                                        AS sale_out_amt_total
   ,SUM(t2.famount_lc)                                                                                     AS sale_out_amt_total_rmb
   ,t1.fsettleorgid
FROM
(
    SELECT
        fid
       ,fbillno
       ,system_source
       ,arrange_date
       ,fsaledeptnumber
       ,salesman_code
       ,fcustnumber
       ,fbilltypenumber
       ,fbilltypename
       ,f_aqa_recaddr
       ,fsaleorgid
       ,fbasecurrnumber
       ,fsettleorgid
       from tmp.aaa
) t1
INNER JOIN
(
    SELECT
        fmainid
       ,ftaxprice
       ,frealqty
       ,fallamount_lc
       ,fmaterialnumber
       ,delivery_order_id
       ,warehouse_code
       ,fcostamount_lc
       ,fsrcbillno
       ,fentryid
       ,funitnumber
       ,famount
       ,famount_lc
    FROM tmp.zzz
    WHERE rn = 1
    AND is_delete IS NOT TRUE
) t2
ON t1.fid = t2.fmainid
GROUP BY
    t1.arrange_date
   ,t2.fmaterialnumber
   ,t1.fbillno
   ,t1.fsaledeptnumber
   ,t1.salesman_code
   ,t2.fmainid
   ,t1.system_source
   ,t1.fcustnumber
   ,t1.fbilltypenumber
   ,t2.delivery_order_id
   ,t1.f_aqa_recaddr
   ,t1.fsaleorgid
   ,t2.warehouse_code
   ,t2.fsrcbillno
   ,t1.fbilltypename
   ,t2.fentryid
   ,t1.fbasecurrnumber
   ,t2.funitnumber
   ,t1.fsettleorgid
;

总结

山重水复疑无路，柳暗花明又一村。出自宋代陆游的《游山西村》，描绘的是当地路漫漫，长途跋涉以为前方没有路要放弃时，突然发现不远的地方有个村庄的场景，带有一种豁然开朗的喜悦。
当我们面临复杂的SQL关联时，因为SQL内部机制会导致结果和我们想象中的不一样，这时不妨换个思路，可能就会“柳暗花明又一村”了。

张六十zhangliushi
关注
  • 1
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
深入浅出数据仓库中SQL性能优化之Hive篇
01-30
Hive查询生成多个mapreducejob，一个mapreducejob又有map，reduce，spill，shuffle，sort等多个阶段，所以针对hive查询的优化可以大致分为针对MR中单个步骤的优化，针对MR全局的优化以及针对整个查询的优化。一个Hive查询生成多个MapReduceJob，一个MapReduceJob又有Map，Reduce，Spill，Shuffle，Sort等多个阶段，所以针对Hive查询的优化可以大致分为针对MR中单个步骤的优化（其中又会有细分），针对MR全局的优化，和针对整个查询（多MRJob）的优化，下文会分别阐述。在开始之前，先把MR
hive sql + left join 数据缺失
02-29
hive sql + left join 数据缺失
sql中的inner join、outer join、left join和right join的区别
最新发布
qq_42453993的博客
03-13 188
Inner Join只返回两个表都有的记录，而Left Join、Right Join和Outer Join则会返回至少一个表中的所有记录，并在另一个表中找不到匹配项时填充NULL值。例如，如果你想要获取某个表中的所有记录，并查看它们与另一个表的关联情况，那么可能会选择使用Left Join或Right Join。外连接包括左外连接（Left Outer Join）、右外连接（Right Outer Join）和完整外部连接（Full Outer Join）。只返回两个表中都有的记录，即两个表的交集部分。
Hive—调优策略（SQL优化，join基础优化）
qq_43408367的博客
08-16 456
第一个job会将map端数据随机输入reducer，每个reducer做部分聚合，相同的key就会分布在不同的reducer中。Map端进行聚合操作的条目数，通过 hive.groupby.mapaggr.checkinterval 参数也可以设置map端预聚合的行数阈值，超过该值就会分拆job，默认值10W。map join的配置项是 hive.auto.convert.join ，默认值true。缺失的项很多，在做join时这些空值就会非常集中，拖累进度【空值的字段是连接字段】。PS:倾斜均衡配置项。
hive&sql-LEFT JOIN之后，数据量为啥比左表还要少
DCHAO的博客
07-13 3328
a表 LEFT JOIN B表，结果表的数据量正常应该是=a表或者>a表。但有种情况，结果表会比a表少。 逻辑很简单，就是用两个表同一天的分区数据做关联，但是结果却比 a 表的数据少了。当把唯一一个 “where” 换成 “and” 后，结果便正确了。这个原因其实是过滤数据的对象不同。 下面用 MySQL 创建示例来进行说明， 只用on，是我们想要的 结果还是以左表为准，只是右边只有 dept_id = 2 的数据。 所以，LEFT JOIN时，无论ON的条件如何，左表不会被过滤，只会过滤右表。ON仅
Hive优化
君子性非异也 善假于物也
01-12 313
Hive 优化 核心思想：把Hive SQL 当做Mapreduce程序去优化 以下SQL不会转为Mapreduce来执行 select仅查询本表字段 where仅对本表字段做条件过滤 Explain 显示执行计划 EXPLAIN [EXTENDED] query Hive运行方式： 本地模式 集群模式 本地模式 开启本地模式： set hive
Hive关联时丢失数据问题和常用的Hive SQL参数设置
redamancy_dec的博客
02-20 1783
针对结果的发生，本文从以下方面分析原因及提供解决方案： 右表没有匹配的数据；关联键数据类型不匹配； 受count列null值影响；Hive版本问题，在某些版本中，左连可能导致右表为null； 数据倾斜 。 并在文末附属了`Hive SQL常用参数设置`的说明。
关于hive3多表leftjoin导致数据丢失问题及解决方案
weixin_44203240的博客
11-29 2972
关于hive3多表leftjoin导致数据丢失问题及解决方案
hive left join 丢失数据
大风车
08-30 6324
最近在写hql取数据的时候，发现不同的写法会模糊left join 和 join，使得得出的结果不是预期的。 分别列出三段hql供参考： 1、下面是第一段hql，将相关联的两张表（or 多张表）的条件都写在最后的where下： select a.cola1,a.cola2,b.colb1 from tablea a left join tableb b on a.id=b.id where and a.dt='20200826' and b.dt='20200826' ; 结果：这样的写法会使
hive查询结果缺少数据——某些union all和join情况下会缺少数据
帅成一匹马
05-05 662
在使用hive sql 进行join 关联时,查询结果明显缺少数据，或于presto或其他查询渠道查询结果不一致时。原因可能是orc文件在加工过程中造成的部分。在使用hive sql 进行。all查询结果时，缺少数据。丢失，关闭元数据优化就可以了。
哪些情况会导致hive表丢数据
yy的博客
03-12 349
spark hive
hive 中join和Group的优化
01-07
group by 优化 set hive.map.aggr = true;... //解决数据倾斜的万能钥匙 当map阶段运行不了的时候，可以设置 set hive.map.aggr = false; 说明 设置hive.map.aggr=true，提高HiveQL聚合的执行性能。 set hive.ma
达观数据分析平台架构和Hive实践
02-26
Hadoop于2006年1月28日诞生，至今已有10年，它改变了企业对数据的存储、处理和分析的过程，加速了大数据的发展，形成了自己的极其火爆的技术生态圈，并受到非常广泛的应用。在2016年Hadoop十岁生日之际，InfoQ策划了...
使用kettle将mysql中的数据导入到hive中
01-07
先说说思路：1是直接从mysql输出至hive中，另外一个是先将数据搞到hdfs中，再load一下，当然这只是textfile存储格式而言，若是ORC等其他列式存储的话建议先搞一个textfile格式的中间表，再insert into table select ...
关系型数据库设计之从E-R图绘制到数据库表设计
zhangliushi的博客
10-28 6007
本文介绍数据库设计的过程，供各位小伙伴参考。包含E-R图绘制，E-R模型转化为关系模型等。
HiveQL获取一年后日期计算同比及2月闰月特殊情况处理
zhangliushi的博客
08-31 2635
ON CONCAT(t1.sale_date, '-01') = CAST(CONCAT(t2.sale_date, '-01')AS DATE) + INTERVAL 1 YEAR ON CONCAT(t1.sale_date, '-01') = ADD_MONTHS(CONCAT(t2.sale_date, '-01'),12)
PostgreSQL常用命令
zhangliushi的博客
04-14 2099
pgsql常用命令
HiveQL炸裂函数案例总结分享
zhangliushi的博客
04-15 984
当某个字段含有多个元素，需要把多个元素分解开时，可以使用炸裂函数，形成一对多的形式。
hive中 left join 和inner join 有什么区别
04-13
左连接（left join）与内连接（inner join）在Hive中的主要区别在于左连接会包括左表中所有的行，即使右表中没有匹配的行，而内连接仅会包括两个表中都有匹配的行。 具体来说，左连接操作将左表和右表中匹配的行组合在一起，同时保留左表中没有匹配到的行，而内连接操作仅将两个表中都有匹配的行组合在一起。因此，如果需要包括左表中所有的行，就需要使用左连接。 举例来说，如果有一个学生表和一个成绩表，需要查询所有学生的成绩，包括那些成绩表中没有的学生，则可以使用左连接将两个表连接起来，保留所有的学生行，即使成绩表中没有匹配的学生。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

张六十zhangliushi

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值