[Hive基礎]-- 去除指定重複的資料舉例

Hive資料去重舉例：

Hql代碼  

INSERT overwrite TABLE store SELECT
  t.p_key,
  t.sort_word
FROM
  (
    SELECT
      p_key,
      sort_word,
      row_number () over (
        distribute BY p_key sort BY sort_word
      ) AS rn
    FROM
      store
  ) t
WHERE
  t.rn = 1;      

說明：

• p_key為去重所依據的key，sort_word表示多個p_key的排列順序，這個關鍵字将決定哪個p_key将留下。
• t 為子查詢的别名，Hive需要在每個子查詢後面加别名
• t.rn=1表示重複的資料隻保留第一個。
• distribute by 關鍵字指定分發的key，同一個key将分發到同一個reducer
• sort by 是單機範圍内排序，是以配合distribute by 就可以對某一個關鍵字排序

應用舉例：

表一資料：

201608030239462016-08-03 08:45:08.0 2000.040000.0 毛湖25456 5的 2016-08-03 00:00:00.0                                            20000.020160807
20160803024041 2016-08-03 10:45:13.03300.0 66000.0甯波 316772站 2016-08-05 08:00:00.0       趙2 17233123150   20540.0 20160807
20160803024001 2016-08-03 10:45:10.03300.0 66000.0甯波 316772站 2016-08-05 16:00:00.0       趙2 17233123150   20470.0 20160807
20160803024008 2016-08-03 10:45:11.03300.0 66000.0甯波 316772站 2016-08-06 16:00:00.0                                           20060.0 20160807
20160803024007 2016-08-03 10:45:11.03300.0 66000.0甯波 316772站 2016-08-06 08:00:00.0      趙2 17233123150    20280.0 20160807      

表二資料：

201608030239462016-08-03 08:45:08.0 2000.040000.0 毛湖   25456 5的2016-08-03 00:00:00.0            20000.020160807
20160803023987 2016-08-03 10:45:09.03300.0 66000.0甯波 316772站 2016-08-07 08:00:00.0              20460.0 20160807
20160803024041 2016-08-03 10:45:13.03300.0 66000.0甯波 316772站 2016-08-05 08:00:00.0     趙2 17233123150    20500.0 20160807
20160803024001 2016-08-03 10:45:10.03300.0 66000.0甯波 316772站 2016-08-05 16:00:00.0     趙2 17233123150    20580.0 20160807
20160803024008 2016-08-03 10:45:11.03300.0 66000.0甯波 316772站 2016-08-06 16:00:00.0              20060.0 20160807
20160803024007 2016-08-03 10:45:11.03300.0 66000.0甯波 316772站 2016-08-06 08:00:00.0     趙2 17233123150    20440.0 20160807      

表一和表二合并(union all)後的資料：

201608030239462016-08-03 08:45:08.0 2000.040000.0 毛湖25456 5的 2016-08-03 00:00:00.0                                            20000.020160807
20160803024041 2016-08-03 10:45:13.03300.0 66000.0甯波 316772站 2016-08-05 08:00:00.0趙2 17233123150 20540.0 20160807
20160803024001 2016-08-03 10:45:10.03300.0 66000.0甯波 316772站 2016-08-05 16:00:00.0趙2 17233123150 20470.0 20160807
20160803024008 2016-08-03 10:45:11.03300.0 66000.0甯波 316772站 2016-08-06 16:00:00.0                        20060.0 20160807
20160803024007 2016-08-03 10:45:11.03300.0 66000.0甯波 316772站 2016-08-06 08:00:00.0趙2 17233123150 20280.0 20160807

201608030239462016-08-03 08:45:08.0 2000.040000.0 毛湖    25456 5的2016-08-03 00:00:00.0             20000.020160807
20160803023987 2016-08-03 10:45:09.03300.0 66000.0甯波 316772站 2016-08-07 08:00:00.0                20460.0 20160807
20160803024041 2016-08-03 10:45:13.03300.0 66000.0甯波 316772站 2016-08-05 08:00:00.0趙2 17233123150    20500.0 20160807
20160803024001 2016-08-03 10:45:10.03300.0 66000.0甯波 316772站 2016-08-05 16:00:00.0趙2 17233123150    20580.0 20160807
20160803024008 2016-08-03 10:45:11.03300.0 66000.0甯波 316772站 2016-08-06 16:00:00.0                20060.0 20160807
20160803024007 2016-08-03 10:45:11.03300.0 66000.0甯波 316772站 2016-08-06 08:00:00.0趙2 17233123150    20440.0 20160807      

發現有重複第一列有資料，實作去重的語句：

select
 t.order_num,
 t.accounts_time,
 t.turnover_price,
 t.totals,
 t.source_name,
 t.receipt_addr_id,
 t.station_name,
 t.is_arrive,
 t.driver_name,
 t.driver_phone,
 t.truck_tou_no,
 t.truck_pai_no,
 t.buy_weigth_num,
 t.times
from     
 (
select
 order_num,
 accounts_time,
 turnover_price,
 totals,
 source_name,
 receipt_addr_id,
 station_name,
 is_arrive,
 driver_name,
 driver_phone,
 truck_tou_no,
 truck_pai_no,
 buy_weigth_num,
 times,  
row_number() over(distribute by order_num  sort by buy_weigth_num) as rn   
 from
 a_jj_aks_sm_m_h
)
 t  
 where
 t.rn=1
 ;      

注意：此處舉例是去除了單個字段(order_num )重複的資料，

      如果想去除所有字段重複的資料，那麼需要如下編寫

select
t.order_num,
t.accounts_time,
t.turnover_price,
t.totals,
t.source_name,
t.receipt_addr_id,
t.station_name,
t.is_arrive,
t.driver_name,
t.driver_phone,
t.truck_tou_no,
t.truck_pai_no,
t.buy_weigth_num,
t.times
from     
(
select
order_num,
accounts_time,
turnover_price,
totals,
source_name,
receipt_addr_id,
station_name,
is_arrive,
driver_name,
driver_phone,
truck_tou_no,
truck_pai_no,
buy_weigth_num,
times,  
row_number() over(distribute by order_num,accounts_time,turnover_price,totals,source_name,receipt_addr_id,station_name,is_arrive,driver_name,driver_phone,truck_tou_no,truck_pai_no,buy_weigth_num,times) as rn   
from
a_jj_aks_sm_m_h
)
t  
where
t.rn=1
;

/**注意：
 *“distribute by”後面需要添加所有字段
 * order_num,accounts_time,turnover_price,totals,source_name,receipt_addr_id,station_name,is_arrive,driver_name,driver_phone,truck_tou_no,truck_pai_no,buy_weigth_num,times
 */      

以上内容已經測試通過，可以根據實際更改，如有疑問，請留言！歡迎各位批評指教！