程序员经验分享-hwhale

Hive中Inner join、Outer join、Full join中on与where的执行计划与结果区别

2023-11-19

背景

本文主要讨论hive(版本2.3)中，不同join方式下on条件和where条件的区别，同时关注hive中如何执行语句。比如谓词下推，就是其中一种优化技术。

原表

person表

person.id       person.name     person.age
3       mili    21
4       tom     19
5       mike    18
8       nul     20

account表

account.id      account.account
1       2222
2       2323
3       1111
4       1212
5       5555
6       6666

Inner join

通常情况下简写为join
inner join，查询条件在on中和where中，执行结果上并没有不同。
比如，下面几个查询语句执行结果相同

select * from person join account on person.id=account.id and person.id=4;
select * from person join account on person.id=account.id and account.id=4;
select * from person join account on person.id=account.id where person.id=4;
select * from person join account on person.id=account.id where account.id=4;
select * from person join account on person.id=account.id and person.id=4 and account.id=4;
select * from person join account on person.id=account.id where person.id=4 and account.id=4;

执行结果：

person.id       person.name     person.age      account.id      account.account

4       tom     19      4       1212

通过explain查看上述执行语句转换的mapreduce任务，所有的任务都被转化为相同的执行计划： 

hive (test)> explain select * from person join account on person.id=account.id and person.id=4 and account.id=4;
OK
Explain
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:
        person 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        person 
          TableScan
            alias: person
            Statistics: Num rows: 1 Data size: 38 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id = 4) (type: boolean)
              Statistics: Num rows: 1 Data size: 38 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 4 (type: int)
                  1 4 (type: int)
  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: account
            Statistics: Num rows: 5 Data size: 42 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id = 4) (type: boolean)
              Statistics: Num rows: 2 Data size: 16 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Inner Join 0 to 1
                keys:
                  0 4 (type: int)
                  1 4 (type: int)
                outputColumnNames: _col1, _col2, _col7
                Statistics: Num rows: 2 Data size: 17 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: 4 (type: int), _col1 (type: string), _col2 (type: int), 4 (type: int), _col7 (type: int)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 2 Data size: 17 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 2 Data size: 17 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
Time taken: 0.253 seconds, Fetched: 62 row(s)

不管是account表还是person表，TableScan中均包含“predicate: (id = 4)”这条查询条件，这就意味着读取2个表的数据是先根据条件筛选数据，然后再做join操作。
总结：inner join中筛选条件在on或where中，没有差异，因为hive在底层做了逻辑方面的优化，不同的语句被转化为相同的执行任务。

Outer join

以left outer join为例, 通常简写为left join. on条件不会影响左表返回的结果，仅影响右表，where条件会影响左表返回结果。

# sql 1.1
select * from person left join account on person.id=account.id and person.id=4;

执行结果：

person.id       person.name     person.age      account.id      account.account
3       mili    21      NULL    NULL
4       tom     19      4       1212
5       mike    18      NULL    NULL
8       nul     20      NULL    NULL

执行计划：
无论左表还是右表，均全量读取。

# sql 1.2
select * from person left join account on person.id=account.id and account.id=4;

执行结果：

同sql 1.1

执行计划：

hive (test)> explain select * from person left join account on person.id=account.id and account.id=4;
OK
Explain
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:
        account 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        account 
          TableScan
            alias: account
            Statistics: Num rows: 5 Data size: 42 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id = 4) (type: boolean)
              Statistics: Num rows: 2 Data size: 16 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 id (type: int)
                  1 id (type: int)
  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: person
            Statistics: Num rows: 1 Data size: 38 Basic stats: COMPLETE Column stats: NONE
            Map Join Operator
              condition map:
                   Left Outer Join0 to 1
              keys:
                0 id (type: int)
                1 id (type: int)
              outputColumnNames: _col0, _col1, _col2, _col6, _col7
              Statistics: Num rows: 2 Data size: 17 Basic stats: COMPLETE Column stats: NONE
              Select Operator
                expressions: _col0 (type: int), _col1 (type: string), _col2 (type: int), _col6 (type: int), _col7 (type: int)
                outputColumnNames: _col0, _col1, _col2, _col3, _col4
                Statistics: Num rows: 2 Data size: 17 Basic stats: COMPLETE Column stats: NONE
                File Output Operator
                  compressed: false
                  Statistics: Num rows: 2 Data size: 17 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
Time taken: 0.349 seconds, Fetched: 59 row(s)

左表全量读取，右表仅读取account.id=4的数据。

# sql 1.3
select * from person left join account on person.id=account.id and person.id=4 and account.id=4;

执行结果：

同sql 1.2

执行计划：
同sql 1.2。
等价于person.id加不加都不影响结果和执行过程。

# sql 1.4
select * from person left join account on person.id=account.id where person.id=4;

执行结果：

person.id       person.name     person.age      account.id      account.account
4       tom     19      4       1212

执行计划：

hive (test)> explain select * from person left join account on person.id=account.id where person.id=4;
OK
Explain
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:
        account 
          Fetch Operator
            limit: -1
      Alias -> Map Local Operator Tree:
        account 
          TableScan
            alias: account
            Statistics: Num rows: 5 Data size: 42 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id = 4) (type: boolean)
              Statistics: Num rows: 2 Data size: 16 Basic stats: COMPLETE Column stats: NONE
              HashTable Sink Operator
                keys:
                  0 id (type: int)
                  1 id (type: int)
  Stage: Stage-3
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: person
            Statistics: Num rows: 1 Data size: 38 Basic stats: COMPLETE Column stats: NONE
            Filter Operator
              predicate: (id = 4) (type: boolean)
              Statistics: Num rows: 1 Data size: 38 Basic stats: COMPLETE Column stats: NONE
              Map Join Operator
                condition map:
                     Left Outer Join0 to 1
                keys:
                  0 id (type: int)
                  1 id (type: int)
                outputColumnNames: _col1, _col2, _col6, _col7
                Statistics: Num rows: 2 Data size: 17 Basic stats: COMPLETE Column stats: NONE
                Select Operator
                  expressions: 4 (type: int), _col1 (type: string), _col2 (type: int), _col6 (type: int), _col7 (type: int)
                  outputColumnNames: _col0, _col1, _col2, _col3, _col4
                  Statistics: Num rows: 2 Data size: 17 Basic stats: COMPLETE Column stats: NONE
                  File Output Operator
                    compressed: false
                    Statistics: Num rows: 2 Data size: 17 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
Time taken: 0.228 seconds, Fetched: 62 row(s)

执行计划表明，无论是左表还是右表，均在读取数据时做了筛选，然后执行join。

# sql 1.5
select * from person left join account on person.id=account.id where account.id=4;

执行结果：

同sql 1.4

执行计划：
无论左表还是右表，均全量读取。

# sql 1.6
select * from person left join account on person.id=account.id where person.id=4 and account.id=4;

执行结果：

同sql 1.4

执行计划：
同sql1.4。

Full join

# sql 2.1
select * from person full join account on person.id=account.id;

执行结果：

person.id       person.name     person.age      account.id      account.account
NULL    NULL    NULL    1       2222
NULL    NULL    NULL    2       2323
3       mili    21      3       1111
4       tom     19      4       1212
5       mike    18      5       5555
NULL    NULL    NULL    6       6666
8       nul     20      NULL    NULL

执行计划：
左表与右表均全量读取。

# sql 2.2
select * from person full join account on person.id=account.id and person.id=4;

执行结果：

person.id       person.name     person.age      account.id      account.account
NULL    NULL    NULL    1       2222
NULL    NULL    NULL    2       2323
3       mili    21      NULL    NULL
NULL    NULL    NULL    3       1111
4       tom     19      4       1212
5       mike    18      NULL    NULL
NULL    NULL    NULL    5       5555
NULL    NULL    NULL    6       6666
8       nul     20      NULL    NULL

执行计划：
左表与右表均全量读取。

# sql 2.3
select * from person full join account on person.id=account.id and account.id=4;

执行结果：

同sql 2.2

执行计划：
同sql 2.2.

# sql 2.4
select * from person full join account on person.id=account.id where person.id=4;

执行结果：

person.id       person.name     person.age      account.id      account.account
4       tom     19      4       1212

执行计划：
左表与右表均全量读取。

# sql 2.5
select * from person full join account on person.id=account.id where account.id=4;

执行结果：

同sql 2.4

执行计划：
同sql 2.4.

# sql 2.6
select * from person full join account on person.id=account.id where person.id=4 and account.id=4;

执行结果：

同sql 2.4

执行计划：
同sql 2.4.

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

Data Warehouse

hive

大数据

sql

Hive中Inner join、Outer join、Full join中on与where的执行计划与结果区别 的相关文章

  • JDBC 时间戳和日期 GMT 问题

    我有一个 JDBC 日期列 如果我使用 getDate 则会得到 date 仅部分2009 年 10 月 2 日但如果我使用 getTimestamp 我会得到完整的 date 2009 年 10 月 2 日 13 56 78 890 这正

  • Oracle - 获取星期几

    今天是星期二 为什么当我运行这个 SQL 语句时 它说今天不是星期二 SELECT CASE WHEN TO CHAR sysdate Day Tuesday THEN Its Tuesday ELSE Its Not Tuesday EN

  • SQL Server 中的嵌套事务

    sql server 允许嵌套事务吗 如果是的话那么交易的优先级是什么 来自 SQL Server 上的 MSDN 文档 嵌套交易 http msdn microsoft com en us library ms189336 SQL 90

  • 验证 sql/oracle 中的电子邮件/邮政编码字段

    对于以下方面的一些建议将不胜感激 是否可以通过 oracle 中的 sql 中的某种检查约束来验证电子邮件和邮政编码字段 或者我怀疑 pl sql 带有正则表达式的这种事情 Thanks 这是电子邮件地址的正则表达式语法 包括引号 a zA

  • 我的 SQL 表设置为允许该列为 NULL，但是当我运行它时，它说它不能为 NULL。什么/为什么/如何？

    所以我在这里遇到了很奇怪的困境 我的 SQL 表设置为允许 ZipCode 列为空 如下所示 CREATE TABLE dbo Companies CompanyId BIGINT IDENTITY 1 1 NOT NULL PRIMARY

  • SQL命令文本到DataSet的直接方法

    如果我有 sql 命令 获取数据集的最直接途径是什么 string sqlCommand SELECT FROM TABLE string connectionString blahblah DataSet GetDataSet sqlCo

  • sql server 2008 对 exec 语句的限制

    我只需要仔细检查 t sql 中的 EXEC 命令是否有字符限制 如果我有一个带有 varchar max 的变量并使用 EXEC 执行命令 你认为这样可以吗 thanks 应该没问题 根据这篇 MSDN 文章 http msdn micr

  • Postgres LIMIT/OFFSET 奇怪的行为

    我正在使用 PostgreSQL 9 6 我有一个这样的查询 SELECT anon 1 id AS anon 1 id anon 1 is valid AS anon 1 is valid anon 1 first name AS ano

  • 对于返回超过1个值的SQL select，当Id为GUID时它们如何排序？

    我想知道 SQL Server 如何对查询返回的数据进行排序 并且各个表的 Id 列都是 uniqueidentifier 类型 我在创建所有 GUID 时使用 NHibernate GuidComb 并执行以下操作 Sheet sheet

  • 如何在蜂巢中的每个组中按计数 desc 进行排序？

    这是 HQL select A B count as cnt from test table group by A B order by cnt desc 示例输出如下 a1 b1 5 a2 b1 3 a1 b2 2 a2 b2 1 但我想

  • 如何比较行内的重叠值？

    我似乎对这个 SQL 查询有问题 SELECT FROM appts WHERE timeStart gt timeStart AND timeEnd lt timeEnd AND dayappt boatdate 时间格式为军用时间 物流

  • 唯一约束与唯一索引？

    之间有区别吗 CREATE TABLE p product no integer name text UNIQUE price numeric and CREATE TABLE p product no integer name text

  • 如何将 sql 数据输出到 QCalendarWidget

    我希望能够在日历小部件上突出显示 SQL 数据库中的一天 就像启动程序时突出显示当前日期一样 在我的示例中 它是红色突出显示 我想要发生的是 当用户按下突出显示的日期时 数据库中日期旁边的文本将显示在日历下方的标签上 这是我使用 QT De

  • Magento 设置脚本中的 ALTER TABLE 不使用 SQL

    乔纳森 戴 https stackoverflow com users 336905 jonathan day says 更新不应采用以下形式 SQL命令 我没遇到过 任何 DDL 或 DML 语句不能 通过 Magento 的配置执行 结

  • 最近邻居的 Postgis SQL

    我正在尝试计算最近的邻居 为此 我需要传递一个参数来限制与邻居的最大距离 例如 半径1000米内最近的邻居是哪些 我做了以下事情 我用数据创建了表 id name latitude longitude 之后 我执行了以下查询 SELECT

  • MySQL LIKE %string% 不够宽容。我还有什么可以用的吗？

    我有一位客户询问他们的搜索是否可以搜索公司名称 这些名称可以根据用户输入以多种格式进行搜索 例如数据库中存储的公司是 A J R Kelly Ltd 如果用户搜索 一个 J R Kelly 被发现 使用

  • 如何对多行的一列值求和？

    我有这个表 我想添加几行的 change 列的值 或者更准确地说 从 ne 值为零的行到 ne 值为零的下一行 不是第二个本身 任何答案将不胜感激 rn date ne change 0 2008 12 07 0 10330848398 1

  • 如何引用下一行的数据？

    我正在 PostgreSQL 9 2 中编写一个函数 对于股票价格和日期的表 我想计算每个条目较前一天的百分比变化 对于最早一天的数据 不会有前一天 因此该条目可以简单地为 Nil 我知道WITH声明可能不应该高于IF陈述 到目前为止 这就

  • hive sql查找最新记录

    该表是 create table test id string name string age string modified string 像这样的数据 id name age modifed 1 a 10 2011 11 11 11 1

  • java库维护数据库结构

    我的应用程序一直在开发 所以偶尔 当版本升级时 需要创建 更改 删除一些表 修改一些数据等 通常需要执行一些sql代码 是否有一个 Java 库可用于使我的数据库结构保持最新 通过分析类似 db structure version 信息并执

随机推荐

热门标签