Hive update实现方案V1.0

Hive update实现方案

1.问题

由于hive数仓的特性，不容许数据进行修改，造成hive中的数据更新活着删除很困难的问题，自hive 0.11版本之后，hive也尝试在测试环境允许进行update和delte操作，但这些操作还不成熟，不敢在生产环境放心使用，其中也有一样不足。所以就需要找一种可靠的方案实现hive的数据更新或者删除。

2.方案

2.1.创建数据表

创建两张数据结构一模一样的hive数据表TEST、TEST_TEMP，其中TEST表的存储格式为“ORCFILE”（性能高），TEST_TEMP 表的存储格式为“TEXTFILE”（方便数据加载）。

ID NAME AGE

主键姓名年龄

create table TEST_TEMP (

id string,

name string,

age string

) comment '临时表'

partitioned by (y string,m string,d string)

row format delimited fields terminated by',' stored as textfile

create table TEST (

id string,

name string,

age string

) comment '最终表'

row format delimited fields terminated by','

stored as orcfile

2.2.初始化

1.通过hive数据load的方式先把数据加载到TEST_TEMP表中

（此处也可以通过sqoop进行数据抽取，不再详述）。

load data local inpath '/home/hadoop/a.txt'

overwrite intotable TEST_TEMP

2.通过hive insert overwrite的方式把临时表的数据加载到最终

表TEST中。

insertintotable TEST

select id,name,age from TEST_TEMP

2.3.日常

1.通过hive数据load的方式先把数据加载到TEST_TEM表中

（此处也可以通过sqoop进行数据抽取，不再详述）。

load data local inpath '/home/hadoop/b.txt'

overwrite intotable TEST_TEMP

2.通过数据比对方式，找出非更新和非增量的数据，人后把这

部分数据覆盖到TEST表中，即保证TEST中的数据和

TEST_TEMP中的没有重复(前提是表中必须有主键)。

INSERT OVERWRITE TABLE TEST

SELECT id,name,age FROM TEST a LEFT JOIN

TEST_TEMP b on a.id=b.id WHERE b.id is null;

注：上述语句其实就是not in的逻辑，如果日常数据上包含增、

删、改标识，则只需在关联时在TEST_TEMP表上加条件判

断即可。

3.经过第二步的数据比对重复处理之后，则可以直接把

TEST_TEMP中的数据追加（append）到TEST表。

insertintotable TEST

select id,name,age from TEST_TEMP;

****************增、改实现****************

注：实现比对出增、改标识

SELECT

a.id,

,

a.age,

CASEWHEN b.id ISnullTHEN'新增'ELSE'更新'END

FROM

TEST_TEMP a

LEFTJOIN TEST b on

a.id =

b.id;

数据插入

insertintotable TEST

SELECT

a.id,

,

a.age,

CASEWHEN b.id ISnullTHEN'新增'ELSE'更新'END

FROM

TEST_TEMP a

LEFTJOIN TEST b on

a.id =

b.id;

3.总结

上述的方案在逻辑上虽然解决了hive的更新操作，但只是抛砖引玉，在实际的环境可能还要进行一些表分区等优化措施。针对方案的第二点，实际也可能存在安全问题，如果这一步未执行成功那怎么回退？是在做overwrite之前做备份还是copy一份新的数据join，需要结合实际项目情况进行决策。