Introduction:The previous article, Sqoop1.4.4, implements the import of incremental data from oracle10g into Hive0.13.1 and updates the primary table http://www.linuxidc.com/Linux/2014-09/106282 in hive. The HTM describes the principle of incremental update of hive tables and the Sqoop,hive command, based on the content of the previous article to implement the shell script, a little modification can be used in the actual project.
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" rdbms_pwd= " 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 ${rdbms_connstr}--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 A.* from ${hive_full_table} A left outer join ${hive_increment_table} b on A.service_code = B.service_code where b.service_code is null; "
Note:The command format for executing hive hql in a shell script is Hive-e "SELECT ..."
Cron ScriptAdd a scheduled task every day 2 o'clock in the morning execute the script 0 2 * * */home/fulong/shell/datasync.sh
A Hadoop cluster-based hive installation http://www.linuxidc.com/Linux/2013-07/87952.htm
The difference between table and appearance in Hive http://www.linuxidc.com/Linux/2013-07/87313.htm
Hadoop + Hive + Map +reduce cluster installation Deployment http://www.linuxidc.com/Linux/2013-07/86959.htm
Hive Native Standalone mode installation http://www.linuxidc.com/Linux/2013-06/86104.htm
Hive Learning WordCount Word statistics http://www.linuxidc.com/Linux/2013-04/82874.htm
Hive Runtime architecture and configuration Deployment http://www.linuxidc.com/Linux/2014-08/105508.htm
[Goto] Shell script for synchronizing hive data updates
