hive稀缺大宽表去重

项目背景

hive下有一张表，是从多张表整合进来的稀缺大宽表，需要按id字段去重，保留最新的值不为空的数据，示例数据如下图，可以看到有很多NULL字段值

处理思路

使用pandas dataframe 内置 fillna 函数填补数据

代码

from impala.dbapi import connect
import pandas as pd

conn = connect(host='192.168.0.16', port=10000, user=None, password=None, database='default', auth_mechanism="PLAIN")
cur = conn.cursor()
cur.execute("desc default.test")
d = cur.fetchall()
cols = []
for i in d:
    cols.append(i[0])
    if i[0]=='pt':
        break

cur.execute('select * from default.test')
data = cur.fetchall()
df = pd.DataFrame(data, columns=cols)
newDf = df.sort_values(['id','sj']).fillna(method='ffill').drop_duplicates(subset=['id'], keep="last", inplace=False)
print(newDf)

去重效果如下：

其他说明

hive 表中可能存在 空字符 字段值，需要让它变成 NULL，这样 pandas 才能通过 isna() 识别到该字段是否需要填充，可以 通过下面的语句配置

# 测试表
drop table if exists default.test;
create table if not exists default.test (
    `id` string, `xm` string, `xb`string, `nl` string, `sj` string
) row format delimited fields terminated by ',' lines terminated by '\n' stored as textfile;
# 设置空值格式
alter table default.test set serdeproperties ('serialization.null.format'='');

样本数据

1,小明,,,20210101
1,,男,,20210102
1,,,19,20210103
2,,女,,20210101
2,小红,,,20210102
2,,,21,20210103
3,小东,,,20210101
3,,男,,20210102
3,,,22,20210103

上传数据到hdfs：hdfs dfs -put data /tmp
载入数据到hive表：load data inpath '/tmp/data' into table default.test;