hive:join操作

hiveのマルチテーブル接続は、複数のMR jobに変換され、各MR jobはhiveでJoinフェーズと呼ばれます.joinプログラムの最後のテーブルはできるだけ大きいテーブルであるべきで、joinの前の段階で生成したデータはReducerのbufferに存在するため、streamの一番後ろのテーブルを通じて、直接Reducerからバッファされた中間データの結果を読み出して、後ろの大きいテーブルと接続する時、bufferからキャッシュのkeyを読み取るだけで、大きいテーブルの中の指定のkeyと接続して、速度はもっと速くて、メモリバッファのオーバーフローを避けることができます.

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1);　

また、いくつかのhint情報によってjoin操作を啓発することもできます.すなわち、そのテーブルを大きなテーブルとして指定することもできます.

SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

これにより,表bとcに対してjoinを先に行う.　
hive自体がサポートするサブクエリは非常に限られており、Hiveはwhere句のサブクエリをサポートせず、サブクエリがfromにのみ表示されるようにします.
間違った書き方:

insert into table branch_atmzc_sum
Select  XT_OP_TRL, SA_TX_DT," - ",b.cr_tx_amt- a.cr_tx_amt as cr_tx_amt from branch_atmzc a join  branch_atmzc b  on (a.XT_OP_TRL = b.XT_OP_TRL and a.SA_TX_DT = b.SA_TX_DT and a.tran_cd = 'ATM ' and b.tran_cd = 'ATM '), counts from branch_atmzc
group by XT_OP_TRL, SA_TX_DT,cr_tx_amt,counts;

正しい書き方:

insert into table branch_atmzc_sum
Select a.XT_OP_TRL, a.SA_TX_DT," - ",b.cr_tx_amt- a.cr_tx_amt ,b.counts+a.counts  from branch_atmzc a join  branch_atmzc b  on (a.XT_OP_TRL = b.XT_OP_TRL and a.SA_TX_DT = b.SA_TX_DT and a.tran_cd = 'ATM ' and b.tran_cd = 'ATM ')

 
さまざまな接続方法の応用と効率について詳しく説明します.
hiveのjoinタイプとMRのいくつかの方法をカプセル化して実現し、その代表的なjoin on、left semi joinは使用頻度が最も高い.
join onはcommon join(shuffle join/reduce join)に属し、left semi joinはmap join(broadcast join)の変異体に属する.実現上の原理には違いがある.

Common Join

最も一般的なjoin、すなわちreduce side joinは、mapreduce jobによって最も非効率的な方法で完成する.
実装原理:まず大きなテーブルと小さなテーブルをそれぞれmap操作し、map shuffleの段階でmap output keyごとにtable_name_tag_prefix + join_column_valueですが、partitionを行うときはjoinしか使用しません.column_牙列缺损
各reduceはすべてのmapから送られてきたsplitを受け入れ、reduceのshuffle段階でmap output keyの前のtable_name_tag_prefixは捨てる比較を行う.reduceの個数は小さなテーブルの大きさで決めることができるので、ノードごとのreduceは必ず小さなテーブルのsplitをメモリに入れてhashtableにすることができる.次に、大きなテーブルの各レコードを1つ1つ比較する.

条件に基づくLEFTOUTER JOIN最適化

左接続の場合、左テーブルに表示されるjoinフィールドはすべて保持され、右テーブルに接続されていないものはすべて空です.where条件付きjoin文の場合、次のようになります.

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

実行順序は、まず2つのテーブルのjoinを完了し、where条件でフィルタリングすることで、join時に大量の結果が出力され、時間がかかります.
さらに最適化:where条件をonの後に置きます.たとえば、次のようにします.

SELECT a.val, b.val FROM a LEFT OUTER JOIN b
ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07'

このようにjoinでは,条件を満たさない記録を先にフィルタリングし,より効果的である.

左半接続（LEFTSEMI JOIN）

半接続を採用した理由は、reduce side joinに対して、機械間での伝送量が非常に大きく、map端でjoin操作を必要としないデータをフィルタリングできれば、IOを節約し、効率を高めることができるからである.
実装原理:小さなテーブルを選択し、File 1と仮定し、joinに関与する抽出可能なテーブルをFile 3に保存します.一般的にはメモリに直接入れることができます.mapフェーズでは、DistributedCacheを使用してFile 3を各TaskTrackerにコピーし、File 2にFile 3に記録されていないフィルタリング、join操作に参加しない、残りのreduce操作はreduce side joinと同じです.
原理に基づいて、よりよく理解します.
(1)LEFTSEMI JOIN句を用いた後,右の表はJOIN操作以外では見えなくなり,表bはon句の後にしか現れず,selectとwhere文には現れなくなり,右の表に相当してjoin keyのみが関連計算に関与した.
(2)右の表で繰り返し(key)を扱う場合,left semi joinはin(keySet)の関係で左の表は直接スキップし,join onであればずっと遍歴する.
左半分の接続はIN/EXISTSのようなクエリー文で、以下のように比較されます.

SQL：
SELECT a.key, a.value FROM a WHERE a.key IN (SELECT b.key FROM b);
Hive：
SELECT a.key, a.val FROM a LEFT SEMI JOIN b ON (a.key = b.key)

注意:bはその小さなテーブルで、bにはjoin keyだけが演算に参加し、selectとwhereのフィルタには表示されません.
サブクエリについては、Hiveのサポートは次のとおりです.

バージョンでは、FROM句のサブクエリのみがサポートされています.

のバージョン0.13では、WHERE句のサブクエリもサポートされています.

join onとleft semi onの比較出力特殊状況:

Left semi join:
select     a.level2,  a.name2,
        cast((a.alipay_fee) as double) as pay,
        cast(0 as double) as pay2
        from test1 a
        left semi join
       test2 b
      on (a.level2 = b.cat_id2
         and a.brand_id = b.brand_id
       and b.cat_id2 > 0
         and b.brand_id > 0
         and b.max_price = 0 )

Join on:
select     a.level2,  a.name2,
        cast((a.alipay_fee) as double) as pay,
        cast(0 as double) as pay 2
        from test1 a
   join   test2 b
     on (a.level2 = b.cat_id2
         and a.brand_id = b.brand_id)
  where  b.cat_id2 > 0
         and b.brand_id > 0
         and b.max_price = 0

トラップ:統計的に結果が一致しません.これは、サブテーブルのtest 2 bに重複するデータがあるため、トラップです.join onの場合、a,bテーブルは2つのレコードに関連付けられ、onで条件が一致します.
一方、left semi joinを使用する場合、Aテーブルのレコードは、Bテーブルに該当条件が発生すると返され、Bテーブルのレコードを検索し続けることはないので、重複があっても複数のレコードは生成されません.
したがって、多くの場合、2つの方法は対等であり、重複した記録がある場合にのみ、注意してください.

Map Side Join 

MapJoinは、Mapタスクの出力後、Reducerノードにデータをコピーする必要がなく、ネットワークノード間でのデータ転送のオーバーヘッドを低減します.複数のテーブルが接続されています.1つのテーブルだけが大きい場合、他のテーブルが小さい場合、JOIN操作はMapのみを含むJobに変換されます.たとえば、

SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a JOIN b ON a.key = b.key;

従って、テーブルaの各mapについて、テーブルbのデータを完全に読み取ることができる.ここで、表aとbはFULL OUTER JOIN、RIGHT OUTER JOINを許さない.

BUCKET Map Side JOIN

まず、2つのテーブルaとbのDDLを見てみましょう.テーブルaは:

CREATE TABLE a(key INT, othera STRING)
CLUSTERED BY(key) INTO 4 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
STORED AS SEQUENCEFILE;

表bは、

CREATE TABLE b(key INT, otherb STRING)
CLUSTERED BY(key) INTO 32 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
STORED AS SEQUENCEFILE;

現
a.keyとb.keyに基づいてJOIN操作を行う場合、JOIN列はBUCKET列でもあり、JOIN文は次のようになります.

SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a JOIN b ON a.key = b.key

また、表aには4個のBUCKETがあり、表bには32個のBUCKETがあり、デフォルトでは、表aの各BUCKETについて、表bの各BUCKETを取得してJOINを行うが、今回は一定のオーバーヘッドが発生する.表bの中でJOIN条件を満たすBUCKETだけが表aのBUCKETに本当に接続されるからである.このデフォルト動作は最適化でき、デフォルトJOIN動作を変更することで、変数を設定するだけです.

set hive.optimize.bucketmapjoin = true

このように、JOINのプロセスは、テーブルaのBUCKET 1がテーブルbのBUCKET 1とJOINするだけであり、テーブルbの他のBUCKET 2~32は考慮されない.上記のテーブルに同じBUCKETがある場合、32個で並べ替えられている場合、すなわち、テーブル定義においてCLUSTERDBY(key)の後に以下の制約が追加される.

SORTED BY(key)

上記JOIN文では、Sort-Marge-Bucket(SMB)JOINを実行します.同様に、デフォルトの動作を変更するパラメータを設定する必要があります.JOINを最適化するには、関連するBUCKETのみを巡回すればいいです.

sethive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

set hive.optimize.bucketmapjoin = true

set hive.optimize.bucketmapjoin.sortedmerge = true;

　　
例:

SELECT
	t1. ,
	COUNT(DISTINCT (IF(t2. ID IS NULL, NULL, t1. ID))) AS KEEP_UV
FROM
(
	SELECT
		 ,
		 ID
	FROM  
	WHERE (`DATE` >= 20140201 AND `DATE` <= 20140228)
) t1
LEFT OUTER JOIN
(
	SELECT
		 ,
		 ID
	FROM  
	WHERE (`DATE` >= 20140101 AND `DATE` <= 20140131)
) t2 ON (t1.  = t2.  AND t1. ID = t2. ID)
GROUP BY t1. 

自分のテーブルに含まれるフィールド情報は多く、時間スパンが大きい.このようなIN/EXISTSサブクエリ(正確には、ここでは非相関サブクエリ)の効率的な実装は、LEFTSEMI JOINである.
       LEFT SEMI JOIN implements the uncorrelated IN/EXISTS subquery semantics in an efficient way.
left join 

SELECT
	 ,
	COUNT(DISTINCT t1. ID) AS KEEP_UV
FROM
(
	SELECT
		 ,
		 ID
	FROM  
	WHERE (`DATE` >= 20140201 AND `DATE` <= 20140228)
) t1
LEFT SEMI JOIN
(
	SELECT
		 ,
		 ID
	FROM  
	WHERE (`DATE` >= 20140101 AND `DATE` <= 20140131)
) t2 ON (t1.  = t2.  AND t1. ID = t2. ID)
GROUP BY  

 
参照リンク:ここ、ここ
構成パラメータもhive最適化の重要な側面です.ここを参照してください.
転載先:https://www.cnblogs.com/kxdblog/p/4042418.html