hadoop - Hive 中 LEFT OUTER JOIN 的全表扫描问题

Question

我正在尝试对配置单元中的 2 个表执行 LEFT OUTER JOIN 操作。可以理解，我们在连接的情况下包括了过滤条件和连接条件，从 where 条件中模仿它们以避免全表扫描。引用:https://gist.github.com/randyzwitch/9abeb66d8637d1a0007c

尽管这样做，我的查询还是产生了大量的映射器和缩减器，就好像它在进行全表扫描一样。

这是我的查询和解释计划。我不擅长理解这个解释计划。 m.date_id 和 d.REC_CREATED_DATE 是各自表中的分区列，因此它实际上应该只扫描这些分区。

任何改进我的查询的建议都会有很大帮助。

hive> EXPLAIN SELECT m.execution_id
> ,m.operation_name
> ,m.return_code
> ,m.explanation
> ,d.REC_CREATED_DATE
> FROM web_log_master m  LEFT OUTER JOIN web_log_detail d
> on (m.execution_id = d.execution_id AND m.date_id='2015-07-14' and d.REC_CREATED_DATE='2015-07-14') ;
OK
ABSTRACT SYNTAX TREE:
  (TOK_QUERY (TOK_FROM (TOK_LEFTOUTERJOIN (TOK_TABREF (TOK_TABNAME web_log_master) m) (TOK_TABREF (TOK_TABNAME web_log_detail) d) (and (AND (= (. (TOK_TABLE_OR_COL m) execution_id) (. (TOK_TABLE_OR_COL d) execution_id)) (= (. (TOK_TABLE_OR_COL m) date_id) '2015-07-14')) (= (. (TOK_TABLE_OR_COL d) REC_CREATED_DATE) '2015-07-14')))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_SELEXPR (. (TOK_TABLE_OR_COL m) execution_id)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL m) operation_name)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL m) return_code)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL m) explanation)) (TOK_SELEXPR (. (TOK_TABLE_OR_COL d) REC_CREATED_DATE)))))

STAGE DEPENDENCIES:
  Stage-4 is a root stage , consists of Stage-1
  Stage-1
  Stage-0 is a root stage

STAGE PLANS:
  Stage: Stage-4
  Conditional Operator

  Stage: Stage-1
Map Reduce
  Alias -> Map Operator Tree:
    d
      TableScan
        alias: d
        Reduce Output Operator
          key expressions:
                expr: execution_id
                type: string
          sort order: +
          Map-reduce partition columns:
                expr: execution_id
                type: string
          tag: 1
          value expressions:
                expr: rec_created_date
                type: string
    m
      TableScan
        alias: m
        Reduce Output Operator
          key expressions:
                expr: execution_id
                type: string
          sort order: +
          Map-reduce partition columns:
                expr: execution_id
                type: string
          tag: 0
          value expressions:
                expr: execution_id
                type: string
                expr: operation_name
                type: string
                expr: return_code
                type: string
                expr: explanation
                type: string
                expr: date_id
                type: string
  Reduce Operator Tree:
    Join Operator
      condition map:
           Left Outer Join0 to 1
      condition expressions:
        0 {VALUE._col0} {VALUE._col1} {VALUE._col2} {VALUE._col3}
        1 {VALUE._col3}
      filter predicates:
        0 {(VALUE._col13 = '2015-07-14')}
        1
      handleSkewJoin: false
      outputColumnNames: _col0, _col1, _col2, _col3, _col19
      Select Operator
        expressions:
              expr: _col0
              type: string
              expr: _col1
              type: string
              expr: _col2
              type: string
              expr: _col3
              type: string
              expr: _col19
              type: string
        outputColumnNames: _col0, _col1, _col2, _col3, _col4
        File Output Operator
          compressed: false
          GlobalTableId: 0
          table:
              input format: org.apache.hadoop.mapred.TextInputFormat
              output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

  Stage: Stage-0
    Fetch Operator
      limit: -1


Time taken: 13.616 seconds, Fetched: 90 row(s)

Answer 1

映射器和缩减器的数量取决于作业是否可并行化以及集群的容量。如果你有很多机器，你会得到更多的映射器和缩减器。如果你有更少的机器，你得到的就会更少。如果作业不可并行化，那么您将获得一个 reducer，就像这里的情况一样:

select count(distinct column) from x;

以这种方式编写时需要单个 reducer 。

事实上，您希望许多映射器和缩减器都在工作。这就是 map 缩小比例的方式。可以说，人多手轻。无论如何，您的左外部连接都按预期工作。