Hive从小时表中删除重复数据

最笨的羊羊

于 2023-07-01 00:02:01 发布

阅读量293

点赞数 1

分类专栏：日常分享专栏文章标签： Hive 从小时表中删除重复数据

本文链接：https://blog.csdn.net/zhengzaifeidelushang/article/details/131485319

版权

日常分享专栏专栏收录该内容

452 篇文章 71 订阅

订阅专栏

Hive从小时分区中删除重复数据

一、小时分区数据去重
二、重写小时分区数据

一、小时分区数据去重

小时分区数据去重后，写入到hive临时表中

with to_json_map as (
select distinct _track_id
    ,time
    ,distinct_id
    ,to_json(lib) as lib
    ,event
    ,to_json(properties) as properties
    ,_flush_time
    ,map_id
    ,user_id
    ,login_id
    ,anonymous_id
    ,recv_time
    ,to_json(extractor) as extractor
    ,project_id
    ,project
    ,ver
    ,type
    ,device_id
    from ods_tracking_data_kafka_prod.sensor_tracking_kafka_nioapp_prod_1h_i
    where datetime = '2023061417'
),

from_json_map as (
    select 
     _track_id as track_id
    ,time
    ,distinct_id
    ,from_json(lib,'map<string,string>') as lib
    ,event
    ,from_json(properties,'map<string,string>') as properties
    from to_json_map
)
insert overwrite tmp.app_prod_20230614
    partition (datetime = '2023061417')
select * from from_json_map

二、重写小时分区数据

从临时表中把数据写回小时分区中

insert overwrite ods_tracking_data_kafka_prod.sensor_tracking_kafka_nioapp_prod_1h_i
    partition (datetime = '2023061417')
select 
    track_id as _track_id
    ,time
    ,distinct_id
    ,lib
    ,event
    ,properties
from tmp.app_prod_20230614 
where datetime = '2023061417'