更新hive表

前言

hive表的更新, 需要对原表的完全重写. 或者说, hive表结构的设计, 本身的优势在于, 结合高扩展性实现的近乎无限的容量, 它应对数据变化是十分低效的
所以设计数仓时, 如果大量数据有更新的需求, 就应该考虑hive+的架构模式

但是数仓基本成型之后, 改变架构的成本很高. 这时候, 虽然效率较低, 但依旧可以使用hive完成数据的更新.
数据可以完成更新的前提是有更新标识, 常见的更新标识是updatetime.

补充
阿里代码规范建议, 表格的设计一定要包括3个工具列: 自增的`id`, 创建时间`createtime`和更新时间`updatetime`. 而且, 这三列的列名要全局一致.
要注意, 这里`id`就像idea右侧显示的行数, 它也只是简单记录, 并不一定要作为主键.
借助这三个工具列, 批量处理数据库时, 才能有一个坚实方便的支撑点.

如果是少量数据, 直接全量导入+overwrite就可以达到效果. 即使无法获取到全量的数据, 也可以union all + 分组top1完成更新.
但更常见的场景是, 原数据量很大, 更新的数据量很小, 这样以上两种思路就都不适用.
如果更新的频率也很慢, 可以使用拉链表记录变化
如果更新的频率较快, 建议使用日分区, 也就是下述第二种的简化方案

传统拉链表

如果要记录历史变化, 一般会借助拉链表

1. 原表为old_table
   id|updatetime|createtime
   -|-|-
   1|2019-06-26 12:00:00|2019-06-26 12:00:00
   2|2019-06-26 13:00:00|2019-06-26 13:00:00
   3|2019-06-26 14:00:00|2019-06-26 14:00:00

2. 通过条件查询, where updatetime>${yourtime}, 来取出更新和增量的混合数据. 这样就有了待更新的表, 称它为update_table. 如下
   id|updatetime|createtime
   -|-|-
   1|2019-06-27 14:00:00|2019-06-26 12:00:00
   4|2019-06-27 13:00:00|2019-06-27 13:00:00
   5|2019-06-27 15:00:00|2019-06-27 15:00:00

3. 拉链表logs_table就是在原表基础上增加失效时间disabletime,
   id|updatetime|createtime|disabletime
   -|-|-
   1|2019-06-26 12:00:00|2019-06-26 12:00:00|9999-12-31 23:59:59
   2|2019-06-26 13:00:00|2019-06-26 13:00:00|9999-12-31 23:59:59
   3|2019-06-26 14:00:00|2019-06-26 14:00:00|2019-06-28 00:00:00
   3|2019-06-27 15:00:00|2019-06-26 14:00:00|9999-12-31 23:59:59

4. 关联查询, 更新拉链表到新表.
   这里有几个细节:
   (1) 可以谓词下推, 谓词下推不会让logs_table数据缺失
   (2) 注意union和union all
   (3) 过程中要读取logs_table, 因此不能直接写回logs_table, 否则会导致锁区锁表
   (4) 可以对拉链表定期快照, 只取最新数据, 以此减少数据冗余

INSERT OVERWRITE TABLE new_logs_table

  SELECT A.id
  , A.updatetime
  , A.createtime
    -- 修正数据的失效时间为当日凌晨
  , CASE WHEN B.user_num IS NOT NULL 
    THEN '2019-06-28 00:00:00'
    ELSE A.disabletime
  END AS disabletime
    FROM logs_table AS A
    LEFT JOIN update_table AS B
      -- 谓词下推
      ON A.disabletime = '9999-12-31 23:59:59'
      AND A.id = B.id
  UNION ALL
    SELECT C.id
    , C.updatetime
    , C.createtime
    , '9999-12-31 23:59:59' AS disabletime
    FROM update_table AS C
;

简化

保留历史记录不一定要靠拉链表. 每日将更新后的数据存入当日分区, 也能实现类似功能
coalesce方法用于获取第一个非null的字段

INSERT OVERWRITE TABLE temp_table
  SELECT A.id
  , coalesce(B.updatetime, A.updatetime)
  , coalesce(B.createtime, A.createtime)
  FROM old_table AS A
  FULL OUTER JOIN update_table AS B
  ON A.id = B.id
;

批处理

以上都需要一一指定字段, 如果要批量更新多表呢
思路如下, 有待优化

oldData
  .join(updatedData, Seq(Global.COL_COMMON_KEY), "left_outer")
  .withColumn("flag",
    when(updatedData("utime") isNull, 1))
      when(updatedData(Global.COL_COMMON_UPDATE_TIME) isNull, 1))
  .where("flag=1")
  .select(oldData("*"))
  .union(updatedData)

INSERT OVERWRITE TABLE new_table
SELECT * 
FROM(
  SELECT A.*
  , case when b.updatetime is null then 1 end as flag
    FROM old_table AS A
    LEFT JOIN update_table AS B
    ON A.id = B.id
  UNION
    SELECT c.*
    , 1 AS flag
    FROM update_table AS C
) AS T
where flag = 1