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

主要執(zhí)行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在執(zhí)行上是等價的.

而NOT IN需要分以下2種情況:

  1. 如果子查詢的結果中沒有NULL,則返回結果和NOT EXISTS,LEFT JOIN是一樣的,只不過會多2個stage
  2. 如果子查詢的結果中有NULL,則返回的結果為空,這點不同于NOT EXISTS,LEFT JOIN
最后編輯于 ：
?著作權歸作者所有,轉載或內容合作請聯系作者
  • 人面猴
    序言：七十年代末残炮，一起剝皮案震驚了整個濱河市吃警，隨后出現的幾起案子陌兑，更是在濱河造成了極大的恐慌列赎，老刑警劉巖诡右，帶你破解...
    沈念sama閱讀 219,490評論 6贊 508
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現場離奇詭異渐扮，居然都是意外死亡柑贞，警方通過查閱死者的電腦和手機嫂伞，發(fā)現死者居然都...
    沈念sama閱讀 93,581評論 3贊 395
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門孔厉，熙熙樓的掌柜王于貴愁眉苦臉地迎上來拯钻，“玉大人，你說我怎么就攤上這事撰豺》喟悖” “怎么了？”我有些...
    開封第一講書人閱讀 165,830評論 0贊 356
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵污桦，是天一觀的道長亩歹。 經常有香客問我，道長凡橱，這世上最難降的妖魔是什么小作？ 我笑而不...
    開封第一講書人閱讀 58,957評論 1贊 295
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮稼钩，結果婚禮上顾稀，老公的妹妹穿的比我還像新娘。我一直安慰自己坝撑，他們只是感情好静秆，可當我...
    茶點故事閱讀 67,974評論 6贊 393
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布。 她就那樣靜靜地躺著绍载，像睡著了一般诡宗。 火紅的嫁衣襯著肌膚如雪滔蝉。 梳的紋絲不亂的頭發(fā)上击儡，一...
    開封第一講書人閱讀 51,754評論 1贊 307
  • 城市分裂傳說
    那天，我揣著相機與錄音蝠引，去河邊找鬼阳谍。 笑死，一個胖子當著我的面吹牛螃概，可吹牛的內容都是我干的矫夯。 我是一名探鬼主播，決...
    沈念sama閱讀 40,464評論 3贊 420
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼吊洼，長吁一口氣：“原來是場噩夢啊……” “哼训貌！你這毒婦竟也來了？” 一聲冷哼從身側響起冒窍，我...
    開封第一講書人閱讀 39,357評論 0贊 276
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤递沪，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后综液，有當地人在樹林里發(fā)現了一具尸體款慨，經...
    沈念sama閱讀 45,847評論 1贊 317
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內容為張勛視角 年9月15日...
    茶點故事閱讀 37,995評論 3贊 338
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年谬莹，在試婚紗的時候發(fā)現自己被綠了檩奠。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片桩了。...
    茶點故事閱讀 40,137評論 1贊 351
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖埠戳，靈堂內的尸體忽然破棺而出井誉，到底是詐尸還是另有隱情，我是刑警寧澤整胃，帶...
    沈念sama閱讀 35,819評論 5贊 346
  • ?日本核電站爆炸內幕
    正文 年R本政府宣布送悔，位于F島的核電站，受9級特大地震影響爪模，放射性物質發(fā)生泄漏欠啤。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點故事閱讀 41,482評論 3贊 331
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一屋灌、第九天 我趴在偏房一處隱蔽的房頂上張望洁段。 院中可真熱鬧，春花似錦共郭、人聲如沸祠丝。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,023評論 0贊 22
  • 一樁弒父案除嘹，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽写半。三九已至，卻和暖如春尉咕，著一層夾襖步出監(jiān)牢的瞬間叠蝇，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 33,149評論 1贊 272
  • 情欲美人皮
    我被黑心中介騙來泰國打工年缎， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留悔捶，地道東北人。 一個月前我還...
    沈念sama閱讀 48,409評論 3贊 373
  • 代替公主和親
    正文 我出身青樓单芜，卻偏偏與公主長得像蜕该，于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子洲鸠，可洞房花燭夜當晚...
    茶點故事閱讀 45,086評論 2贊 355

推薦閱讀更多精彩內容