定时从大数据平台同步HIVE数据到oracle

 大数据平台数据中心的计算结果存储在hive中，需要每天定时去从hive同步数据到oracle以供应用层程序使用。本文总结了完成这一同步过程的主要步骤和方法。 使用到的技术工具为：shell脚本（hive导出）+sqlldr（oracle导入）+crontab（定时执行）

一，在Hive数据仓库服务器上编写了导出脚本 ，为了实现复用，把配置内容和功能脚本拆开写etl_sync_oracle.sh，和sample.conf配置文件，内容如下：

etl_sync_oracle.sh

#!/bin/sh#################################################################################################this script is used to sync data from hive to oracle database;#this script has only one parameter: the configuration file#you could just change the sample.conf.##Dependencies: hive, sqlldr##################################################################################################oracle settingexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1export PATH=/usr/sbin:$PATHexport PATH=$ORACLE_HOME/bin:$PATHexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/libexport CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibexport NLS_LANG=AMERICAN_AMERICA.ZHS16GBKexport NLS_DATE_FORMAT='YYYY-MM-DD HH24-MI-SS'export TNS_ADMIN=$ORACLE_HOME/network/adminexport EDITOR=vimexport PS1="$LOGNAME@`hostname`:\$PWD #"#0 check conf file is existsqlloader_options="errors=10000"source $1#1.check if source data is ready#2.etl data and export#output file as next step input fileif [ -d /tmp/sync_task_dir ];then    echo ''else    mkdir -p /tmp/sync_task_dir/task_lockfitask_name=${db_usr}_#_${db_table}_`date '+%Y-%m-%d-%H-%M-%S'`task_lock=/tmp/sync_task_dir/task_lock/${db_usr}_#_${db_table}.lock#check task is runningif [ -f ${task_lock} ];then    echo [`date '+%Y-%m-%d %H:%M:%S'` ERROR ][sync task] - ${task_name}: error another task is running... Please check ${task_lock}    exit 1fiecho ${task_name} > ${task_lock}data_dir=/tmp/sync_task_dir/${task_name}mkdir -p ${data_dir}export_sql="INSERT OVERWRITE LOCAL DIRECTORY '${data_dir}' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ${sql}"hive -e "${export_sql}"#3.sync etl result to db#check data has exportedinfiles=''files=(${data_dir}/*)echo "files: ${#files[@]}"if [ ${#files[@]} -gt 0 ]; then# if [ -f ${data_dir}/000000_0 ];then    for filename in ${data_dir}/*;        do        mv ${filename} ${filename}.tsv        infiles="${infiles}          infile '${filename}.tsv'"    doneelse    echo [`date '+%Y-%m-%d %H:%M:%S'` ERROR ][sync task] - ${task_name}: hive export error    rm -f ${task_lock}    exit 2fictl_file="load data CHARACTERSET AL32UTF8${infiles}${db_write_mode} into table ${db_table}fields terminated by '\t'trailing nullcols(${tbl_columns})"echo ${ctl_file} > ${data_dir}/sqlldr_load.ctlsqlldr userid=${db_usr}/${db_pwd}@${db_tns} ${sqlloader_options} control=${data_dir}/sqlldr_load.ctl#TODO 需要判断hive结果数据与导入到Oracle数据是否一致,并加报警#4.delete temp file and lock filerm -rf ${data_dir}rm -f ${task_lock}

sample.conf

#this is a sample for configuration#sqldr ctl#sqldr ctldb_usr=wx#sqldr ctl#导入目标数据库用户名db_usr=wx#导入目标数据库密码db_pwd=xw*****#目标库地址db_tns=192.1.3.122/wskhdbdb_write_mode=truncate#目标表名称db_table=TB_CUSTOMERsqlloader_options="errors=10000"#hive数据源表列tbl_columns="BRANCH_CODE,CUSTOMER_NO,CLIENT_NAME,CLIENT_TYPE,CERTIFICATE_TYPE,CERTIFICATE_ID,OPEN_DATE,CLOSE_DATE,STATUS,AUTH_ID,"'CLIENT_LEVEL NULLIF(CLIENT_LEVEL="\\N")'",TEL,MOBILE,EMAIL,SEX,BIRTHDAY,"'CORP_RISK_LEVEL NULLIF(CORP_RISK_LEVEL="\\N")'",CUST_VALUE integer external terminated by whitespace"#数据源查询Hiveqlsql="selecttrim(BRANCH_CODE),trim(CUSTOMER_NO),CLIENT_NAME,trim(CLIENT_TYPE),trim(CERTIFICATE_TYPE),CERTIFICATE_ID,OPEN_DATE,CLOSE_DATE,STATUS,AUTH_ID,CLIENT_LEVEL,TEL,MOBILE,EMAIL,trim(SEX),BIRTHDAY,CORP_RISK_LEVEL,CUST_VALUEfromcrm.tb_customer" #hive数据源表

把以上两个文件存放在hive数据服务器上（本人存放在/home/team3/weimiantong/目录下）， etl_sync_oracle.sh文件除了注意oracle的环境变量路径外其他基本不需要修改，sample.conf 文件中配置了数据导出的目标库信息和导出的hive数据源的HQL语句，根据自己的需求修改修改注释的几处即可。
此时可以先手动执行脚本：
sh etl_sync_oracle.sh /home/team3/weimiantong/sample.conf

    注意：    （1）必须保证脚本执行机器已经正确安装配置了，hive，sqlldr和oracle的环境变量    （2）在/tmp/  目录下创建了sync_task_dir 临时存放从hive中导出的数据，必须保证hadoop用               户组对tmp目录具有读写权限，否则hive -e命令会抛异常执行失败。    （3）hive源数据服务器和oracle目标数据库网段必须是互通的。

如果执行成功，接下来配置定时导入

二，定时执行导入脚本

主要靠Linux的crontab来创建定时任务。

crontab -l 查看已经有哪些定时脚本
crontab -e 编辑任务 （命令与vi相同，按i进入编辑模式）

30 4 * * *  /home/team3/weimiantong/sync_stock_industry.sh>/home/team3/weimiantong/xftg/sync.log 2>&1

30 4 * * * 表示每天凌晨4点半执行此任务

sync_stock_industry.sh 脚本内容如下：

 sh etl_sync_oracle.sh /home/team3/weimiantong/sample.conf

esc退出，shift+： 然后wq! 保存即可！

注意执行 chmod 777 sync_stock_industry.sh，否则可能出现permission denied的问题

如果抛出hive异常：

[team3@CSZX-192-1-8-15 xftg]$ more sync.log /home/team3/weimiantong/etl_sync_oracle.sh: line 57: hive: command not foundfiles: 1mv: cannot stat `/tmp/sync_task_dir/xftg_#_T_ADVISER_COMM_STOCK_INDUSTRY_2017-04-19-13-28-01/*': No such file or directorySQL*Loader-500: Unable to open file (/tmp/sync_task_dir/xftg_#_T_ADVISER_COMM_STOCK_INDUSTRY_2017-04-19-13-28-01/*.tsv)SQL*Loader-553: file not foundSQL*Loader-509: System error: No such file or directorySQL*Loader: Release 11.2.0.4.0 - Production on Wed Apr 19 13:28:01 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved

解决方法：
在sync_stock_industry.sh 脚本中加入 source /etc/profile即可
最终sync_stock_industry.sh 内容如下

source ~/.bash_profile;source /etc/profile;cd /home/team3/weimiantong/ sh /home/team3/weimiantong/etl_sync_oracle.sh /home/team3/weimiantong/sample.conf

经过以上，遍可以把hive中表的数据crm.tb_customer每天凌晨4点定时导入到oracle中了