Hive 差集实现

1. IN query plan

1. NOT IN 

select * from id_full as a
where a.id not in (select id from id_incr where day=20180512)

STAGE DEPENDENCIES:
  Stage-4 is a root stage
  Stage-8 depends on stages: Stage-4 , consists of Stage-10, Stage-1
  Stage-10 has a backup stage: Stage-1
  Stage-7 depends on stages: Stage-10
  Stage-6 depends on stages: Stage-1, Stage-7 , consists of Stage-9, Stage-2
  Stage-9 has a backup stage: Stage-2
  Stage-5 depends on stages: Stage-9
  Stage-2
  Stage-1
  Stage-0 depends on stages: Stage-5, Stage-2

主要执行job的有以下3个, stage详细信息:

stage-4

Stage: Stage-4
  Map Reduce
    Map Operator Tree:
        TableScan
          alias: id_incr
          filterExpr: ((day = 20180512) and id is null) (type: boolean)
          Statistics: Num rows: 14933063 Data size: 15054692352 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: id is null (type: boolean)
            Statistics: Num rows: 7466531 Data size: 7527345671 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              Statistics: Num rows: 7466531 Data size: 7527345671 Basic stats: COMPLETE Column stats: NONE
              Group By Operator
                aggregations: count()
                mode: hash
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
                Reduce Output Operator
                  sort order: 
                  Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
                  value expressions: _col0 (type: bigint)
    Reduce Operator Tree:
      Group By Operator
        aggregations: count(VALUE._col0)
        mode: mergepartial
        outputColumnNames: _col0
        Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
        Filter Operator
          predicate: (_col0 = 0) (type: boolean)
          Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: 0 (type: bigint)
            outputColumnNames: _col0
            Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
            Group By Operator
              keys: _col0 (type: bigint)
              mode: hash
              outputColumnNames: _col0
              Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE
              File Output Operator
                compressed: false
                table:
                    input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                    serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

不太明白这个stage的意义,根据这个自己实现了一个sql可以给出类似的stage

select * from (
  select count(id) as cnt from id_incr where day=20180512 and id is null
) as a
where cnt = 0

stage-7

Stage: Stage-7
  Map Reduce
    Map Operator Tree:
        TableScan
          alias: a
          Statistics: Num rows: 4025608842 Data size: 8412504677023 Basic stats: COMPLETE Column stats: NONE
          Map Join Operator
            condition map:
                 Left Semi Join 0 to 1
            keys:
              0 
              1 
            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19
            Statistics: Num rows: 4428169822 Data size: 9253755345295 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
    Local Work:
      Map Reduce Local Work

有个mapjoin的动作,没有reduce

Stage-2

Stage: Stage-2
    Map Reduce
      Map Operator Tree:
          TableScan
            Reduce Output Operator
              key expressions: _col0 (type: string)
              sort order: +
              Map-reduce partition columns: _col0 (type: string)
              Statistics: Num rows: 4428169822 Data size: 9253755345295 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), _col14 (type: string), _col15 (type: string), _col16 (type: string), _col17 (type: string), _col18 (type: string), _col19 (type: string)
          TableScan
            alias: id_incr
            filterExpr: (day = 20180512) (type: boolean)
            Statistics: Num rows: 14933063 Data size: 15054692244 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: string)
              outputColumnNames: _col0
              Statistics: Num rows: 14933063 Data size: 15054692244 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: _col0 (type: string)
                sort order: +
                Map-reduce partition columns: _col0 (type: string)
                Statistics: Num rows: 14933063 Data size: 15054692244 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Outer Join0 to 1
          keys:
            0 _col0 (type: string)
            1 _col0 (type: string)
          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col24
          Statistics: Num rows: 4870986909 Data size: 10179131100451 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col24 is null (type: boolean)
            Statistics: Num rows: 2435493454 Data size: 5089565549180 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), _col14 (type: string), _col15 (type: string), _col16 (type: string), _col17 (type: string), _col18 (type: string), _col19 (type: string)
              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19
              Statistics: Num rows: 2435493454 Data size: 5089565549180 Basic stats: COMPLETE Column stats: NONE
              File Output Operator
                compressed: false
                Statistics: Num rows: 2435493454 Data size: 5089565549180 Basic stats: COMPLETE Column stats: NONE
                table:
                    input format: org.apache.hadoop.mapred.TextInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                    serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

map: 两个TableScan读取数据

reduce: 两表join,然后再predicate: _col24 is null (type: boolean)

2. IN examples

select * from (
    select stack(4, 
        'a', null, 'b', 'a'
    ) as key
) a
where a.key not in  (select key from (select stack(2, 'a', null) as key) b);

output:

# nothing

select * from (
    select stack(4, 
        'a', null, 'b', 'a'
    ) as key
) a
where a.key not in  ('a', null);

output:

# nothing

IN语句中只要有null就不会查出数据

3. EXISTS query plan

1. NOT EXISTS 

select * from id_full as a
where not exists (select id from id_incr as b where day=20180512 and a.id =b.id)

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

STAGE PLANS:
  Stage: Stage-4
    Conditional Operator

  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: a
            Statistics: Num rows: 4025608842 Data size: 8412504677023 Basic stats: COMPLETE Column stats: NONE
            Reduce Output Operator
              key expressions: id (type: string)
              sort order: +
              Map-reduce partition columns: id (type: string)
              Statistics: Num rows: 4025608842 Data size: 8412504677023 Basic stats: COMPLETE Column stats: NONE
              value expressions: _col1 (type: string), ....
          TableScan
            alias: b
            filterExpr: (day = 20180512) (type: boolean)
            Statistics: Num rows: 14933063 Data size: 15054692244 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: string)
              outputColumnNames: _col1
              Statistics: Num rows: 14933063 Data size: 15054692244 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: _col1 (type: string)
                sort order: +
                Map-reduce partition columns: _col1 (type: string)
                Statistics: Num rows: 14933063 Data size: 15054692244 Basic stats: COMPLETE Column stats: NONE
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Outer Join0 to 1
          keys:
            0 id (type: string)
            1 _col1 (type: string)
          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col25
          Statistics: Num rows: 4428169822 Data size: 9253755345295 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col25 is null (type: boolean)
            Statistics: Num rows: 2214084911 Data size: 4626877672647 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), _col14 (type: string), _col15 (type: string), _col16 (type: string), _col17 (type: string), _col18 (type: string), _col19 (type: string)
              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19
              Statistics: Num rows: 2214084911 Data size: 4626877672647 Basic stats: COMPLETE Column stats: NONE
              File Output Operator
                compressed: false
                Statistics: Num rows: 2214084911 Data size: 4626877672647 Basic stats: COMPLETE Column stats: NONE
                table:
                    input format: org.apache.hadoop.mapred.TextInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                    serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

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

map: 两个TableScan

reduce: 两表join,然后再predicate: _col25 is null (type: boolean)

4. EXISTS examples

select * from (
    select stack(4, 
        'a', null, 'b', 'a'
    ) as key
) a
where not exists  (select key from (select stack(2, 'a', null) as key) b where a.key = b.key)

output:

NULL
b

5. LEFT JOIN query plan

select * from id_full as a
left join (select id from id_incr as b where day=20180512) as b
on a.id = b.id
where b.id is null

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

STAGE PLANS:
  Stage: Stage-4
    Conditional Operator

  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: b
            filterExpr: (day = 20180512) (type: boolean)
            Statistics: Num rows: 14933063 Data size: 15054692244 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: id (type: string)
              outputColumnNames: _col0
              Statistics: Num rows: 14933063 Data size: 15054692244 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: _col0 (type: string)
                sort order: +
                Map-reduce partition columns: _col0 (type: string)
                Statistics: Num rows: 14933063 Data size: 15054692244 Basic stats: COMPLETE Column stats: NONE
          TableScan
            alias: a
            Statistics: Num rows: 4025608842 Data size: 8412504677023 Basic stats: COMPLETE Column stats: NONE
            Reduce Output Operator
              key expressions: id (type: string)
              sort order: +
              Map-reduce partition columns: id (type: string)
              Statistics: Num rows: 4025608842 Data size: 8412504677023 Basic stats: COMPLETE Column stats: NONE
              value expressions: id (type: string), ...
      Reduce Operator Tree:
        Join Operator
          condition map:
               Left Outer Join0 to 1
          keys:
            0 id (type: string)
            1 _col0 (type: string)
          outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col24
          Statistics: Num rows: 4428169822 Data size: 9253755345295 Basic stats: COMPLETE Column stats: NONE
          Filter Operator
            predicate: _col24 is null (type: boolean)
            Statistics: Num rows: 2214084911 Data size: 4626877672647 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), _col14 (type: string), _col15 (type: string), _col16 (type: string), _col17 (type: string), _col18 (type: string), _col29 (type: string), null (type: string)
              outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20
              Statistics: Num rows: 2214084911 Data size: 4626877672647 Basic stats: COMPLETE Column stats: NONE
              File Output Operator
                compressed: false
                Statistics: Num rows: 2214084911 Data size: 4626877672647 Basic stats: COMPLETE Column stats: NONE
                table:
                    input format: org.apache.hadoop.mapred.TextInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                    serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

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

可以看到这个stage和NOT EXISTS的stage是一样的

6. 结论

在求差集上NOT EXISTS和LEFT JOIN在执行上是等价的.

而NOT IN需要分以下2种情况:

  1. 如果子查询的结果中没有NULL,则返回结果和NOT EXISTS,LEFT JOIN是一样的,只不过会多2个stage
  2. 如果子查询的结果中有NULL,则返回的结果为空,这点不同于NOT EXISTS,LEFT JOIN
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 217,734评论 6赞 505
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,931评论 3赞 394
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 164,133评论 0赞 354
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,532评论 1赞 293
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,585评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,462评论 1赞 302
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,262评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,153评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,587评论 1赞 314
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,792评论 3赞 336
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,919评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,635评论 5赞 345
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,237评论 3赞 329
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,855评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,983评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,048评论 3赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,864评论 2赞 354

推荐阅读更多精彩内容