百度360必应搜狗淘宝本站头条
mqtt服务器xftp6mybatiscollectionkeyerrorc#var
当前位置：网站首页 > 热门文章 > 正文

关于HiveSQL 常见的LEFT JOIN误区，你知道吗

bigegpt 2024-08-16 14:24 2 浏览


写在前面

很多时候，由于SQL逻辑复杂，加之对SQL执行逻辑理解不透彻，很容易产生一些莫名其妙的结果，这些结果看似不符合预期，殊不知这就是真实结果。本文整理了几个常见的SQL问题，我们在实际书写SQL脚本时，需要多加注意，希望本文对你有所帮助。

关于LEFT JOIN

外连接是我们书写SQL时经常使用的多表连接方式，使用起来也是十分的简单。值得注意的是，越是简单的东西，越是容易被忽略细节。通常我们都是这样理解LEFT JOIN的：

语义是满足Join on条件的直接返回，但不满足情况下，需要返回Left Outer Join的left 表所有列，同时右表的列全部填null

上述对于LEFT JOIN的理解是没有任何问题的，但是里面有一个误区：谓词下推。具体看下面的实例：

假设有如下的三张表：

--建表
create table t1(id int, value int) partitioned by (ds string);
create table t2(id int, value int) partitioned by (ds string);
create table t3(c1 int, c2 int, c3 int);
--数据装载，t1表
insert overwrite table t1 partition(ds='20220120') select '1','2022';
insert overwrite table t1 partition(ds='20220121') select '2','2022';
insert overwrite table t1 partition(ds='20220122') select '2','2022';
--数据装载，t2表
insert overwrite table t2 partition(ds='20220120') select '1','120';

当我们执行如下的SQL查询时，会返回什么数据呢？

SELECT  *
FROM    t1
LEFT JOIN t2
ON      t1.id = t2.id
AND     t1.ds = '20220120'
;

结果1：

1 2022 20220120 1 120 20220120

结果2：

1 2022 20220120 1 120 20220120
2 2022 20220121 NULL NULL NULL
1 2022 20220122 NULL NULL NULL

相信对于很多初学者，甚至是一个有开发经验的人来说，会认为结果1是正确的返回结果。其实结果1的并不是正确的结果，真正的返回值是结果2.

是不是跟预期的结果不一致呢？很多初学者会认为上述查询SQL中AND t1.ds = '20220120'会进行谓词下推，从而得到结果2。其实，SQL本身的语义不是这样的，如果需要获取结果1的数据，正确的查询方式是下面这样：

--方式1:
SELECT  *
FROM    t1
LEFT OUTER JOIN t2
ON      t1.id = t2.id
WHERE   t1.ds = '20220120'
;
--方式2:
SELECT  *
FROM    (
            SELECT  *
            FROM    t1
            WHERE   ds = '20220120'
        ) t1
LEFT OUTER JOIN t2
ON      t1.id = t2.id
;

细心的你看出差异了吗？重点是在WHERE t1.ds = '20220120'过滤条件上，最上面的查询方式是ON t1.ds = '20220120'，所以按照LEFT JOIN的语义，如果没有过滤条件，那么左表的数据应该全部返回，右表匹配不上则补null。

执行计划

我们先来看看没有谓词下推的查询SQL的执行计划

正常LEFT JOIN

  • 查看执行计划
EXPLAIN
SELECT  *
FROM    t1
LEFT JOIN t2
ON      t1.id = t2.id
AND     t1.ds = '20220120'
;
  • 执行计划结果
hive> EXPLAIN
    > SELECT  *
    > FROM    t1
    > LEFT JOIN t2
    > ON      t1.id = t2.id
    > AND     t1.ds = '20220120'
    > ;
OK
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3
STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        $hdt$_1:t2
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        $hdt$_1:t2
          TableScan
            alias: t2
            Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: int), value (type: int), ds (type: string)
              outputColumnNames: _col0, _col1, _col2
              Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                filter predicates:
                  0 {(_col2 = '20220120')}
                  1
                keys:
                  0 _col0 (type: int)
                  1 _col0 (type: int)
  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t1
            Statistics: Num rows: 3 Data size: 18 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: int), value (type: int), ds (type: string)
              outputColumnNames: _col0, _col1, _col2
              Statistics: Num rows: 3 Data size: 18 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Left Outer Join0 to 1
                filter predicates:
                  0 {(_col2 = '20220120')}
                  1
                keys:
                  0 _col0 (type: int)
                  1 _col0 (type: int)
                outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
                Statistics: Num rows: 3 Data size: 19 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  Statistics: Num rows: 3 Data size: 19 Basic stats: COMPLETE Column stats: NONE
                  table:
                      input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                      output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                      serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
      Local Work:
        Map Reduce Local Work
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

从上面的执行计划可以看出：总共有3个stage，

STAGE DEPENDENCIES: Stage-4 is a root stage Stage-3 depends on stages: Stage-4 Stage-0 depends on stages: Stage-3

其中stage4是map任务读取t2表，将t2表加载成HashTable，用于map端join。t2表数据量为1行。

Select Operator expressions: id (type: int), value (type: int), ds (type: string) outputColumnNames: _col0, _col1, _col2 Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats: NONE HashTable Sink Operator

stage3是map任务读取t1表数据并执行map端join。t1表数量为3行，可见并没有进行过滤操作。

Map Operator Tree:
TableScan
alias: t1
Statistics: Num rows: 3 Data size: 18 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: id (type: int), value (type: int), ds (type: string)
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 3 Data size: 18 Basic stats: COMPLETE Column stats: NONE

Stage-0进行结果输出，最终并未执行过滤操作。

Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: ListSink

谓词下推的LEFT JOIN

  • 查看执行计划
EXPLAIN
SELECT  *
FROM    t1
LEFT OUTER JOIN t2
ON      t1.id = t2.id
WHERE   t1.ds = '20220120'
;
  • 执行计划结果
STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-3 depends on stages: Stage-4
  Stage-0 depends on stages: Stage-3
STAGE PLANS:
  Stage: Stage-4
    Map Reduce Local Work
      Alias -> Map Local Tables:
        $hdt$_1:t2
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        $hdt$_1:t2
          TableScan
            alias: t2
            Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: int), value (type: int), ds (type: string)
              outputColumnNames: _col0, _col1, _col2
              Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 _col0 (type: int)
                  1 _col0 (type: int)
  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t1
            Statistics: Num rows: 1 Data size: 6 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: int), value (type: int)
              outputColumnNames: _col0, _col1
              Statistics: Num rows: 1 Data size: 6 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Left Outer Join0 to 1
                keys:
                  0 _col0 (type: int)
                  1 _col0 (type: int)
                outputColumnNames: _col0, _col1, _col3, _col4, _col5
                Statistics: Num rows: 1 Data size: 6 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: _col0 (type: int), _col1 (type: int), '20220120' (type: string), _col3 (type: int), _col4 (type: int), _col5 (type: string)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
                  Statistics: Num rows: 1 Data size: 6 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 1 Data size: 6 Basic stats: COMPLETE Column stats: NONE
                    table:
                        input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                        output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                        serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
      Local Work:
        Map Reduce Local Work
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

从上面的执行计划可以看出：总共有3个stage，

STAGE DEPENDENCIES: Stage-4 is a root stage Stage-3 depends on stages: Stage-4 Stage-0 depends on stages: Stage-3

其中stage4是map任务读取t2表，将t2表加载成HashTable，用于map端join。t2表数据量为1行。

TableScan
alias: t2
Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: id (type: int), value (type: int), ds (type: string)
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 1 Data size: 5 Basic stats: COMPLETE Column stats: NONE
HashTable Sink Operator

stage3是map任务读取t1表数据并执行map端join。t1表数量为1行，执行了过滤操作。

TableScan
alias: t1
Statistics: Num rows: 1 Data size: 6 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: id (type: int), value (type: int)
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 6 Basic stats: COMPLETE Column stats: NONE
Map Join Operator
condition map:
Left Outer Join0 to 1
keys:
0 _col0 (type: int)
1 _col0 (type: int)
outputColumnNames: _col0, _col1, _col3, _col4, _col5
Statistics: Num rows: 1 Data size: 6 Basic stats: COMPLETE Column stats: NONE

Stage-0进行结果输出，最终并未执行过操作。

Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: ListSink

总结

本文主要结合具体的使用示例，对HiveSQL的LEFT JOIN操作进行了详细解释。主要包括两种比较常见的LEFT JOIN方式，一种是正常的LEFT JOIN，也就是只包含ON条件，这种情况没有过滤操作，即左表的数据会全部返回。另一种方式是有谓词下推，即关联的时候使用了WHERE条件，这个时候会会对数据进行过滤。所以在写SQL的时候，尤其需要注意这些细节问题，以免出现意想不到的错误结果。

数仓|几种SQL隐藏的错误，你遇到过吗？

数仓规范|使SQL更易于阅读的几个小技巧

相关推荐

了解Linux目录，那你就了解了一半的Linux系统

大到公司或者社群再小到个人要利用Linux来开发产品的人实在是多如牛毛，每个人都用自己的标准来配置文件或者设置目录，那么未来的Linux则就是一团乱麻，也对管理造成许多麻烦。后来，就有所谓的FHS（F...

Linux命令，这些操作要注意!（linux命令?）

刚玩Linux的人总觉得自己在演黑客电影，直到手滑输错命令把公司服务器删库，这才发现命令行根本不是随便乱用的，而是“生死簿”。今天直接上干货，告诉你哪些命令用好了封神！喜欢的一键三连，谢谢观众老爷！！...

Linux 命令速查手册:这 30 个高频指令，拯救 90% 的运维小白!

在Linux系统的世界里，命令行是强大的武器。对于运维小白而言，掌握一些高频使用的Linux命令，能极大提升工作效率，轻松应对各种系统管理任务。今天，就为大家奉上精心整理的30个Linu...

linux必学的60个命令(linux必学的20个命令)

以下是Linux必学的20个基础命令：1.cd：切换目录2.ls：列出文件和目录3.mkdir：创建目录4.rm：删除文件或目录5.cp：复制文件或目录6.mv：移动/重命名文件或目录7....

提高工作效率的--Linux常用命令，能够决解95%以上的问题

点击上方关注，第一时间接受干货转发，点赞，收藏，不如一次关注评论区第一条注意查看回复：Linux命令获取linux常用命令大全pdf+Linux命令行大全pdf为什么要学习Linux命令？1、因为Li...

15 个实用 Linux 命令（linux命令用法及举例）

Linux命令行是系统管理员、开发者和技术爱好者的强大工具。掌握实用命令不仅能提高效率，还能解锁Linux系统的无限潜力，本文将深入介绍15个实用Linux命令。ls-列出目录内容l...

Linux 常用命令集合（linux常用命令全集）

系统信息arch显示机器的处理器架构(1)uname-m显示机器的处理器架构(2)uname-r显示正在使用的内核版本dmidecode-q显示硬件系统部件-(SMBIOS/DM...

Linux的常用命令就是记不住，怎么办?

1.帮助命令1.1help命令#语法格式：命令--help#作用:查看某个命令的帮助信息#示例:#ls--help查看ls命令的帮助信息#netst...

Linux常用文件操作命令（linux常用文件操作命令有哪些）

ls命令在Linux维护工作中，经常使用ls这个命令，这是最基本的命令，来写几条常用的ls命令。先来查看一下使用的ls版本#ls--versionls(GNUcoreutils)8.4...

Linux 常用命令（linux常用命令）

日志排查类操作命令查看日志cat/var/log/messages、tail-fxxx.log搜索关键词grep"error"xxx.log多条件过滤`grep-E&#...

简单粗暴收藏版:Linux常用命令大汇总

号主：老杨丨11年资深网络工程师，更多网工提升干货，请关注公众号：网络工程师俱乐部下午好，我的网工朋友在Linux系统中，命令行界面（CLI）是管理员和开发人员最常用的工具之一。通过命令行，用户可...

「Linux」linux常用基本命令（linux常用基本命令和用法）

Linux中许多常用命令是必须掌握的，这里将我学linux入门时学的一些常用的基本命令分享给大家一下，希望可以帮助你们。总结送免费学习资料（包含视频、技术学习路线图谱、文档等）1、显示日期的指令：d...

Linux的常用命令就是记不住，怎么办?于是推出了这套教程

1.帮助命令1.1help命令#语法格式：命令--help#作用:查看某个命令的帮助信息#示例:#ls--help查看ls命令的帮助信息#netst...

Linux的30个常用命令汇总，运维大神必掌握技能!

以下是Linux系统中最常用的30个命令，精简版覆盖日常操作核心需求，适合快速掌握：一、文件/目录操作1.`ls`-列出目录内容`ls-l`（详细信息）|`ls-a`（显示隐藏文件）...

Linux/Unix 系统中非常常用的命令

Linux/Unix系统中非常常用的命令，它们是进行文件操作、文本处理、权限管理等任务的基础。下面是对这些命令的简要说明：**文件操作类：*****`ls`(list):**列出目录内容，显...

一周热门
最近发表
标签列表