HIVE 筆記

3. Join 發生在 WHERE 子句之前。如果你想限制 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'

 會 join a 表到 b 表（OUTER JOIN），列出 a.val 和 b.val 的記錄。WHERE 從句中可以使用其他列作為過濾條件。但是，如前所述，如果 b 表中找不到對應 a 表的記錄，b 表的所有列都會列出 NULL，包括 ds 列。也就是說，join 會過濾 b 表中不能找到匹配 a 表 join key 的所有記錄。這樣的話，LEFT OUTER 就使得查詢結果與 WHERE 子句無關了。解決的辦法是在 OUTER JOIN 時使用以下語法：

  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 階段過濾過的，所以不會存在上述問題。這一邏輯也可以應用於 RIGHT 和 FULL 型別的 join 中。

4.  是 IN/EXISTS 子查詢的一種更高效的實現。Hive 當前沒有實現 IN/EXISTS 子查詢，所以你可以用 LEFT SEMI JOIN 重寫你的子查詢語句。LEFT SEMI JOIN 的限制是， JOIN 子句中右邊       的表只能在 ON 子句中設定過濾條件，在 WHERE 子句、SELECT 子句或其他地方過濾都不行。
    SELECT a.key, a.value

      FROM a

     WHERE a.key in (SELECT b.key FROM B);

     可以被重寫為：

     SELECT a.key, a.val

     FROM a LEFT SEMI JOIN b on (a.key = b.key)

5.  SQL標準中，任何對null的操作（數值比較，字串操作等）結果都為null。Hive對null值處理的邏輯和標準基本一致，除了Join時的特殊邏輯。

    這裡的特殊邏輯指的是，Hive的Join中，作為Join key的欄位比較，null=null是有意義的，且返回值為true。檢查以下查詢：

    查詢中，t_weblog表中uid為空的記錄將和t_user表中uid為空的記錄做連線，即l.uid = u.uid=null成立。

    如果需要與標準一致的語義，我們需要改寫查詢手動過濾null值的情況：

   實踐中，這一語義區別也是經常導致資料傾斜的原因之一。

6.  count(distinct ),在資料量大的情況下，效率較低，如果是多count(distinct )效率更低，因為count(distinct)是按group by 欄位分組，按distinct欄位排序，
    一般這種分佈方式是很傾斜的，比如男uv,女uv，淘寶一天30億的pv，如果按性別分組，分配2個reduce,每個reduce處理15億資料。

   因此，distribute by 經常和 sort by 配合使用。


8.  合併mapreduce操作
     

        Multi-group by是Hive的一個非常好的特性，它使得Hive中利用中間結果變得非常方便。例如：

        FROM (SELECT a.status, b.school, b.gender

                FROM status_updates a JOIN profiles b
                    ON (a.userid = b.userid and a.ds='2009-03-20' )

                 ) subq1

        INSERT OVERWRITE TABLE gender_summary  PARTITION(ds='2009-03-20') 

        SELECT subq1.gender, COUNT(1) GROUP BY subq1.gender

        INSERT OVERWRITE TABLE school_summary  PARTITION(ds='2009-03-20')

        SELECT subq1.school, COUNT(1) GROUP BY subq1.school

       上述查詢語句使用了Multi-group by特性連續group by了2次資料，使用不同的group by key。這一特性可以減少一次MapReduce操作。


9.  空值資料傾斜問題：
    
        解決方法1： user_id為空的不參與關聯

        Select * From log a

        Join bmw_users b

        On a.user_id is not null

        And a.user_id = b.user_id

        Union all

        Select * from log a

        where a.user_id is null;

        解決方法2 ：賦與空值分新的key值

        Select *  

        from log a 

        left outer join bmw_users b 

        on case when a.user_id is null then concat(‘dp_hive’,rand() ) else a.user_id end = b.user_id; 

        結論：方法2比方法效率更好，不但io少了，而且作業數也少了。方法1 log讀取兩次，jobs是2。方法2 job數是1 。這個最佳化適合無效id(比如-99,’’,null等)產生的傾斜問題。把空值的key變成一個字串           加上隨機數，就能把傾斜的資料分到不同的reduce上 ,解決資料傾斜問題。附上hadoop通用關聯的實現方法（關聯透過二次排序實現的，關聯的列為parition key,關聯的列c1和表的tag組成排序的group         key,根據parition key分配reduce。同一reduce內根據group key排序）


10.  不同資料型別關聯的資料傾斜
        場景：一張表s8的日誌，每個商品一條記錄，要和商品表關聯。但關聯卻碰到傾斜的問題。s8的日誌中有字串商品id,也有數字的商品id,型別是string的，但商品中的數字id是bigint的。猜測問題的原              因 是把s8的商品id轉成數字id做hash來分配reduce，所以字串id的s8日誌，都到一個reduce上了，解決的方法驗證了這個猜測。

        解決方法：把數字型別轉換成字串型別

        Select * from s8_log a

        Left outer join r_auction_auctions b

        On a.auction_id = cast(b.auction_id as string);

11. 合併小檔案
     檔案數目過多，會給 HDFS 帶來壓力，並且會影響處理效率，可以透過合併 Map 和 Reduce 的結果檔案來消除這樣的影響：

      hive.merge.mapfiles = true 是否和並 Map 輸出檔案，預設為 True

      hive.merge.mapredfiles = false 是否合併 Reduce 輸出檔案，預設為 False

      hive.merge.size.per.task = 256*1000*1000 合併檔案的大小

12.資料傾斜解決：

原語句： 雖然為空的不參與關聯但還是會將所有為空的交給一個reduce處理  最終（加隨機數打散）：