hive中数据更新以及实现

问题

1. hive虽然已经支持更新操作但是效率低，所以要更新数据就要通过自己手动进行更新。
2. 拿业务数据来说，使用sqoop脚本是可以直接把全量数据抽取到hive中，sqoop也有两种增量抽取数据的方法，一种是基于自增列，一种是基于时间列。可以参考：sqoop增量导入的两种方式；但是这两种方法里面是只可以把新增的数据拉去过来的，变化的数据是没有抽取过来，所以我们使用的增量抽取是使用的表里面的修改时间和创建时间来进行增量抽取。
3. 抽取过来的数据就要考虑如何进行更新操作，因为抽取过来的数据既有新增也有变化。现在就要考虑到如何将变化的数据进行合并？

实现

考虑hive中储存历史状态变化的数据
使用拉链表（详细查看 拉链表制作）
以下实现方法是不考虑hive中储存历史状态变化的数据，只存储最新状态的数据。

1.使用窗口函数row_number进行实现

思路：

1. 首先抽取的表必须要有一个唯一主键，先根据创建时间和修改时间将新增的和变化的数据拉去过来到一张临时表
2. 将临时表和之前正式表进行union all操作
3. 使用row_number函数根据唯一主键进行分组，根据修改时间进行排序，取出修改时间最大的那条数据，更新到正式表既可以（中间有个问题就是dt分区怎么处理），最后加载到正式表的数据是使用到动态分区的既可以解决dt分区问题。

具体代码实现：
需求:现有一张订单表，需要每天抽取新增及变化的数据。

现mysql有一张表，表中使用完成时间和修改时间，表结构如下：

1、先将支付表的历史数据全量加载到hive表中，可以使用动态分区，我是只放到了一个分区中，具体代码如下

sqoop import \
--connect jdbc:mysql://IP地址:3306/database \
--username root \
--password root \
--query "SELECT * FROM jc_bill WHERE id!='0' AND \$CONDITIONS"  \
--target-dir /user/hive/warehouse/ods_cloud_app_vehicleuser.db/ods_jc_bill/dt=2020-03-09 \
--fields-terminated-by "\t" \
--delete-target-dir \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-import \
--m 1 \
--hive-partition-key dt \
--hive-partition-value 2020-03-09 \
--hive-database ods_cloud_app_vehicleuser \
--hive-overwrite \
--hive-table ods_jc_bill; 

2、新建一个临时表，将每天存放新增和变化的数据导入，就是根据完成时间和创建时间将数据抽取过来，具体代码如下：

sqoop import \
--connect jdbc:mysql://IP地址:3306/database \
--username root \
--password root \
--query "SELECT * FROM jc_bill WHERE date_format(finish_time,'%Y-%m-%d')='$do_date' or date_format(create_time,'%Y-%m-%d')='$do_date' AND \$CONDITIONS"  \
--target-dir /user/hive/warehouse/ods_cloud_app_vehicleuser_temp.db/ods_jc_bill_temp/dt=$do_date \
--fields-terminated-by "\t" \
--delete-target-dir \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-import \
--m 1 \
--hive-partition-key dt \
--hive-partition-value $do_date \
--hive-database ods_cloud_app_vehicleuser_temp \
--hive-overwrite \
--hive-table ods_jc_bill_temp; 

3、
（1）将临时表和正式表进行union all
（2）然后根据唯一主键id分组，根据时间倒序，
（3）取时间最新的那组数据，使用hive的动态分区再重新加载到hive表中。
具体代码如下：

set hive.exec.dynamici.partition=true;  --开启动态分区，默认是false 
set hive.exec.dynamic.partition.mode=nonstrict; --开启允许所有分区都是动态的，否则必须要有静态分区才能使用。
insert overwrite table ods_cloud_app_vehicleuser.ods_jc_bill partition(dt)
select
t2.id,
t2.order_no,
t2.status,
t2.finish_time,
t2.create_time,
t2.finish_status,
t2.amount,
t2.bill_no,
t2.refund_no,
t2.version,
t2.payment_no,
t2.orderitem_id,
t2.dt
from (
select  
t1.id id,
t1.order_no order_no,
t1.status status,
t1.finish_time finish_time,
t1.create_time create_time,
t1.finish_status finish_status,
t1.amount amount,
t1.bill_no bill_no,
t1.refund_no refund_no,
t1.version version,
t1.payment_no payment_no,
t1.orderitem_id orderitem_id,
t1.dt dt,
row_number() over(distribute by id sort by create_time desc) rn 
from (
select * from ods_cloud_app_vehicleuser.ods_jc_bill
union all
select * from ods_cloud_app_vehicleuser_temp.ods_jc_bill_temp) t1) t2
where t2.rn=1;

2.使用函数coalesce来实现

简介
语法: COALESCE(T.v1, T.v2, …) 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL

思路
首先抽取的表必须要有一个唯一主键，先根据创建时间和修改时间将新增的和变化的数据拉去过来到一张临时表，使用 COALESCE函数取值，如果临时表有数据就从临时表取，而不取正式表的数据，就实现了更新操作（简单思路）

代码实现
和上面1的步骤差不多，细节的话需要再考虑，大体上除了使用到的函数不一样，其余步骤应该差不多

如果考虑要存储历史状态变化的数据，需要使用拉链表
拉链表的制作方法已进行更新。
使用拉链表（详细查看 拉链表制作）

3使用max函数实现

前几步大概和上面差不多
1、需要有全量数据
2、每天抽取的增量数据
3、将全量数据和增量数据union all
4、使用max函数，取出同一个id中，时间最新的这个id，然后和原表进行join取出最新的全量数据，代码实现如下：


以上即可以实现代码的数据更新，有问题请指出，一起学习。