Introduction:In the previous article, the incremental data in Oracle10g is imported into hive0.13.1, and the master table in hive is updated. http://blog.csdn.net/u010967382/article/details/38735381 describes the principle of incremental update hive table and sqoop, hive command, based on the content of the previous article, this article implements shell script writing, which can be used for actual projects with slight modifications.
* ** You are welcome to reprint it. Please specify the source ***
Http://blog.csdn.net/u010967382/article/details/38824327
Shell script
#! /Bin/bash
# Please set the synchronize interval, unit is hour. update_interval = 24
# Please set the RDBMS connection paramsrdbms_connstr = "JDBC: oracle: thin :@192.168.0.147: 1521: orclgbk "rdbms_username =" SP "partition =" Fulong "rdbms_table =" omp_service "rdbms_columns =" ID, SERVICE_NAME, service_process, create_time, enable_org, enable_platform, if_del"
# Please set the hive paramshive_increment_table = "service_tmp" hive_full_table = "service_all"
#---------------------------------------------------------
# Import icrement data in RDBMS into hiveenddate = $ (date '+ % Y/% m/% d % H: % m: % s ') startdate = $ (date '+ % Y/% m/% d % H: % m: % s'-d'-' + $ {update_interval} + 'hours ')
$ Sqoop_home/bin/sqoop import -- connect $ {region} -- username $ {rdbms_username} -- password $ {rdbms_pwd} -- table $ {rdbms_table} -- columns "$ {rdbms_columns }"-- where "create_time> to_date ('$ {startdate }', 'yyyy-mm-dd hh24: MI: ss') and create_time <to_date ('$ {enddate}', 'yyyy-mm-dd hh24: MI: ss ') "-- hive-import -- hive-overwrite -- hive-Table $ {hive_increment_table}
#---------------------------------------------------------
# Update the old full data table to latest Status $ hive_home/bin/hive-e "insert overwrite table $ {hive_full_table} select * from $ {hive_increment_table} Union all select. * From $ {hive_full_table} A left Outer Join $ {hive_increment_table} B on. service_code = B. service_code where B. service_code is null ;"
Note:The command format for executing hive hql in shell scripts is hive-e "Select ..."
Cron scriptAdd a scheduled task to execute the script 0 2 ***/home/Fulong/Shell/datasync. Sh at every day.
[Ganzhou] shell script for synchronous update of hive data
