数据同步问题

Hive在实际工作中主要用于构建离线数据仓库，定期的从各种数据源中同步采集数据到Hive中，经过分层转换提供数据应用。例如，每天需要从MySQL中同步最新的订单信息、用户信息、店铺信息等到数据仓库中，进行订单分析、用户分析。

例如：MySQL中有一张用户表：tb_user，每个用户注册完成以后，就会在用户表中新增该用户的信息，记录该用户的id、手机号码、用户名、性别、地址等信息。

hive 更新sql hive表更新采用什么方式_hive 更新sql

 每天都会有用户注册，产生新的用户信息，我们每天都需要将MySQL中的用户数据同步到Hive数据仓库中，在做用户分析时，需要对用户的信息做统计分析，例如统计新增用户的个数、总用户个数、用户性别分布、地区分布、运营商分布等指标。

在实现数据仓库数据同步的过程中，我们必须保证Hive中的数据与MySQL中的数据是一致的，这样才能确保我们最终分析出来的结果是准确的，没有问题的，但是在实现同步的过程中，这里会面临一个问题：如果MySQL中的数据发生了修改，Hive中如何存储被修改的数据？

假设user表中目前由10条数据，在第二天MySQL中增加了两名新的用户，同时原来的用户也有一名修改的情况。那么，新增的数据会直接加载到hive表中，而更新的数据如何存储在hive表中。

对此，有以下三种解决方案：

  1. 将更新的数据直接覆盖原数据，这样会没由历史数据。
  2. 每次数据改变，就根据日期构建一份全量的快照表，这样会导致大量无用数据的冗余。
  3. 构建拉链表，通过时间标记发生变化的数据的每种状态的时间周期。

拉链表的设计

拉链表的设计是将更新的数据进行状态记录，没有发生更新的数据不进行状态存储，用于存储所有数据在不同时间上的所有状态，通过时间进行标记每个状态的生命周期，查询时，根据需求可以获取指定时间范围状态的数据，默认用9999-12-31等最大值来表示最新状态。

实现过程：

Step1：增量采集所有的新增数据（增加的数据和变化的数据）放入到一张增量表中。

Step2：创建一张临时表，用于将老的拉链表与增量表进行合并。

Step3：将临时表的数据覆写至拉链表中。

拉链表的实现示例

Step1：创建拉链表并加载数据

Create table zipper (
userId string,
Phone string,
Nick string,
Gender string,
Addr string,
startTime string,
endTime string
)row format delimited fields terminated by ‘\t’;
Load data local inpath ‘/home/offcn/tmp/zipper.txt’ into table zipper;
Step2：创建增量表
Create table zipper_update(
userId string,
Phone string,
Nick string,
Gender string,
Addr string,
startTime string,
EndTime string
)row format delimited fields terminated by ‘\t’;
加载更新数据
Load data local inpath ‘/home/offcn/tmp/update.txt’ into table zipper_update;
Step3: 创建临时表合并数据
Create temporary table tmp_zipper(
userId string,
Phone string,
Nick string,
Gender string,
Addr string,
startTime string,
EndTime string
)row format delimited fields terminated by ‘\t’;
合并拉链表与增量表
Insert overwrite table tmp_zipper
Select userId,phone,nick,gender,addr,startTime,endTime
From zipper_update
Union all
Select a.userId,a.phone,a.nick,a.gender,a.addr,a.startTime,
If (b.userId is null or a.endTime<’9999-12-31’,a.endTime,date_sub(b.startTime,1))
As endTime
From zipper a left join zipper_update b
On a.userId=b.userId;
Step4: 生成最新的拉链表
Insert overwrite table zipper
Select * from tmp_zipper;