Hive.GROUPING SETS的“陷阱”

      之前整理了一下Hive 0.10版引進的GROUPING SETS子句特性，並作了簡單的句法使用體驗和資料驗證。但是當時沒有注意到稍微複雜一點的情況，然後，在實際使用過程中，妥妥地就中了一槍。

       這一槍發生在有JOIN操作的時候，情況是這樣的：我要對Hive表data_table的a, b, c這3個欄位去統計UV和VV這兩個資料，並需要由c上卷到b，再上卷到a的統計資料。同時，要對欄位b的值進行值映射，這是通過和另外一個專門描述b值的表進行JOIN來實現的。HQL語句如下：

select         t1.a_desc,         if(t1.group_bitvector= 1, '未區分', t2.b_desc) as b_desc,         if(t1.group_bitvector= 1 or t1.group_bitvector = 3, '未區分', t1.c_desc) as c_desc,         t1.group_bitvector,         t1.act_uv,         t1.act_vvfrom(         select                   a_desc,                   b,                   c_desc,                   grouping__id as group_bitvector,                   count(distinctuer_ID) as act_uv,                   (sum(if(vv_ID is null, 1, 0)) + sum(if(vv_ID = "", 1, 0)) + count(distinct if(vv_ID is not null, if(vv_ID != "", vv_ID, null), null))) as act_vv         from         (                   select                            (                                     case a                                               when 0 then 'str_val_1'                                               when 1 then 'str_val_2'                                               when 2 then 'str_val_3'                                               else 'str_val_4'                                     end                                )as a_desc,                            b,                            if(c= -2 or c = -1 or c = 9, 'c_desc1', 'c_desc2') as c_desc,                            uer_ID,                            vv_ID                   from data_table                   where a = xxx         ) t         group by a_desc, b, c_desc         grouping sets (a_desc,(a_desc, b),(a_desc, b, c_desc))) t1 join dim_table t2 on (t1.b = t2.b)

       跑出來資料，晃眼一看，是正常的，仔細一看就經不起眼睛的考驗了，group_bitvector這一列貌似缺了一個值，這樣GROUPING SETS下來，group_bitvector應該出現1, 3, 7這三個值，但是出來的資料竟然沒有1，也就是說，缺了最頂層的彙總（只對a進行求彙總）資料！

       用力想了想，才拍腦袋發現了這個微妙的錯誤，JOIN君扔掉了一些資料！GROUPING SETS不是會把GROUP BY子句中沒有參與彙總的列置為NULL麼，這些NULL值在JOIN的時候就被無情地拋棄了。。。這個時候，LEFT OUTER JOIN勇敢地站了出來，它說：只要用我替換原來的JOIN（內串連），就可以漂亮地消除了資料被過濾的問題：

 

select         t1.a_desc,         if(t1.group_bitvector= 1, '未區分', t2.b_desc) as b_desc,         if(t1.group_bitvector= 1 or t1.group_bitvector = 3, '未區分', t1.c_desc) as c_desc,         t1.group_bitvector,         t1.act_uv,         t1.act_vvfrom(         select                   a_desc,                   b,                   c_desc,                   grouping__id as group_bitvector,                   count(distinctuer_ID) as act_uv,                   (sum(if(vv_ID is null, 1, 0)) + sum(if(vv_ID = "", 1, 0)) + count(distinct if(vv_ID is not null, if(vv_ID != "", vv_ID, null), null))) as act_vv         from         (                   select                            (                                     case a                                               when 0 then 'str_val_1'                                               when 1 then 'str_val_2'                                               when 2 then 'str_val_3'                                               else 'str_val_4'                                     end                                )as a_desc,                            b,                            if(c= -2 or c = -1 or c = 9, 'c_desc1', 'c_desc2') as c_desc,                            uer_ID,                            vv_ID                   from data_table                   where a = xxx         ) t         group by a_desc, b, c_desc         grouping sets (a_desc,(a_desc, b),(a_desc, b, c_desc))) t1 left outer join dim_table t2 on (t1.b = t2.b)

       這個情境中，grouping__id這個函數的用處也顯現出來了，要是沒有它，缺資料的問題更容易被忽視。。。

       最後，如果是多重或多列進行JOIN，就尤其要注意這個問題了~~~利用LEFT OUTER JOIN和GROUPING__ID的返回值，還是你能夠很清晰無誤地表達查詢資料並進行GROUP組區分的需求。

       最後的最後，還有一顆小小的地雷：gouping__id的返回值竟然是字串類型，而不是直觀上看到的整型！有時候Hive會自動為我們處理整型和字串類型間的解析，畢竟字串的很多運算和整型運算很相似。但有時候Hive也不會給我們處理，比如在用case…when…then…end子句的時候，欄位資料類型必須嚴格相同，否則會報錯，——雖然這個錯誤提示很明顯，比較容易排查。