[Hive基础]-- 去除指定重复的数据举例

原创

high2011 2022-11-03 14:19:24 博主文章分类：Hive ©著作权

©著作权归作者所有：来自51CTO博客作者high2011的原创作品，请联系作者获取转载授权，否则将追究法律责任

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
 */