我们知道在Hive0.14版本之前是不支持update和delete操作的,之后的Hive数据表必须要满足一定的条件,比如ORC存储、ACID支持等,才可以进行update和delete操作,本篇文章讲一下传统的hive数据表如果通过写SQL的方式实现数据的更新。
1.这里新建两张表student10,student10_temp(临时表,可看成业务库),建表语句如下:
//学生信息表create table student10(id string,name string,age string) row format delimited fields terminated by',' stored as textfile;//学生信息表临时表,业务关系数据库的变化,一般先导入临时表create table student10_temp(id string,name string,age string) row format delimited fields terminated by',' stored as textfile;
2.数据准备,可理解成业务库更新前全量数据,文件test.txt,数据如下:
[root@salver158 ~]# cat /tmp/test.txt 1,name1,162,name2,173,name3,18
3.这里先将test.txt中的数据加载到student10_temp表中,执行命令:
hive> load data local inpath '/tmp/test.txt' into table student10_temp;Loading data to table lujs.student10_tempTable lujs.student10_temp stats: [numFiles=1, numRows=0, totalSize=33, rawDataSize=0]OKTime taken: 1.275 seconds
执行insert into将student10_temp,全量插入到student10表中:
hive> insert into table student10 select id,name,age from student10_temp;
到这里两张表数据相同,这就相当于我们生产上,第一次进行了全量数据的导入;这时候我们更新了student10_temp表(这里模拟业务关系数据库中删除一条数据,插入了两条数据),这里直接加载文件test1.txt:
[root@salver158 tmp]# cat /tmp/test1.txt 2,name2,173,name3,5104,name4,195,name5,20
其实这个student10_temp一般代表的是关系型数据库的变化,然后可通过sqoop等数据抽取工具,全量抽取关系数据库已变化的数据到student10_temp临时表中。
hive> select * from student10_temp;OK2 name2 173 name3 5104 name4 195 name5 20
4.我们可以通过SQL进行数据比对,然后把非增量、非更新的数据覆盖到student10表中,执行完SQL后可保证student10和student10_temp数据不重复(这里表必须要有主键,不然没法关联)
找出非增量、非更新数据,一般就是已删除数据,执行命令:
select b.id,b.name,b.age from student10 a left join student10_temp b on a.id=b.id where b.id is nul
将非增量、非更新数据覆盖到student10表,执行命令:
insert overwrite table student10 select b.id,b.name,b.age from student10 a left join student10_temp b on a.id=b.id where b.id is null
执行完后,表中只有非增量、非更新数据,一般就是业务库已删除数据,这里我们可以选择保留做个标识;
5.经过步骤4的比对去重之后,则可以把临时表数据加载进来,执行命令:
hive> insert into table student10 select id,name,age from student10_temp;
查看最终数据,包含非增量、非更新数据,以及更新和增量数据:
hive> select * from student10;OK1 name1 162 name2 173 name3 5104 name4 195 name5 20Time taken: 0.436 seconds, Fetched: 5 row(s)
我这里只是简单的一种数据更新方案,中间可能问题,数据更新之前最后都要做好备份,以免丢失数据。
补充SQL:
1.比对新增、更新数据SQL,这里可以执行步骤4之前查看,可在student10表中添加一个标识字段来标识,是已删除、更新、还是新增数据,这里就不在演示,有兴趣自己去研究下:
hive> select a.id,a.name,a.age,CASE WHEN b.id IS null THEN '新增' ELSE '更新' END FROM student10_temp a LEFT JOIN student10 b on a.id=b.id;Query ID = root_20200310194355_d5428597-77a7-40e6-8b56-6b636c3e137eTotal jobs = 1Launching Job 1 out of 1Status: Running (Executing on YARN cluster with App id application_1583204243863_0026)-------------------------------------------------------------------------------- VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED--------------------------------------------------------------------------------Map 1 .......... SUCCEEDED 1 1 0 0 0 0Map 2 .......... SUCCEEDED 1 1 0 0 0 0--------------------------------------------------------------------------------VERTICES: 02/02 [==========================>>] 100% ELAPSED TIME: 6.59 s --------------------------------------------------------------------------------OK2 name2 17 更新3 name3 510 更新4 name4 19 新增5 name5 20 新增