Shell script for synchronous update of Hive data

The previous article Sqoop1.4.4 import incremental data from Oracle10g to Hive0.13.1 and update the master table in Hive describes the principle of incremental update of Hive tables and Sq

The previous article Sqoop1.4.4 import incremental data from Oracle10g to Hive0.13.1 and update the master table in Hive describes the principle of incremental update of Hive tables and Sq

Introduction:

In the previous article Sqoop1.4.4, import incremental data from Oracle10g to Hive0.13.1 and update the master table in Hive.

Shell script

#! /Bin/bash

# Please set the synchronize interval, unit is hour.

Update_interval = 24

# Please set the RDBMS connection params

Rdbms_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 params

Hive_increment_table = "SERVICE_TMP"

Hive_full_table = "service_all"

#---------------------------------------------------------

# Import icrement data in RDBMS into Hive

Enddate = $ (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 script

Add a scheduled task to execute the script at every day.

0 2 ***/home/fulong/shell/dataSync. sh

Hadoop cluster-based Hive Installation

Differences between Hive internal tables and external tables

Hadoop + Hive + Map + reduce cluster installation and deployment

Install in Hive local standalone Mode

WordCount word statistics for Hive Learning

Hive operating architecture and configuration and deployment

Hive details: click here
Hive: click here