hadoop学习工作总结（四）之数据同步

数据同步到hive：

 

#!/bin/sh
source ExitCodeCheck.sh

dateStr=$1
queueName=$2
oracle_connection=$3
oracle_username=$4
oracle_password=$5
sqoop_import_path=$6
dataName=$7;

 
 
sqoop import -D mapred.job.queue.name=${queueName} \
--connect ${oracle_connection} \
--username ${oracle_username} \
--password ${oracle_password} \
--query "SELECT \
DEPTNO,\
ACCEPTNO,\
POLNO,\
CLIENTNO,\
REVERIFY_DECISION\
  from  LIFEDATA.POS_ACCEPT where       \$CONDITIONS " \
-m 1 --hive-table gbd_sx.${dataName}_LIFEDATA_POS_ACCEPT_DELTA \
--hive-drop-import-delims \
--fetch-size 10000 \
--hive-partition-key y,m,d \
--hive-partition-value ${dateStr:0:4},${dateStr:4:2},${dateStr:6:2} \
--target-dir ${sqoop_import_path}/${dataName}_LIFEDATA_POS_ACCEPT_DELTA \
--hive-overwrite \
--null-string '\\N' \
--null-non-string '\\N' \
--hive-import;
exitCodeCheck $?
 
 

数据同步到oracle

 

DROP table  jkdm_toa_custom_fund_info;
create table  jkdm_toa_custom_fund_info(
GBD_CLIENT_NO VARCHAR2(300),
GBD_MASTER_PARTY_ID VARCHAR2(300),
count_date VARCHAR2(300),
FUND_EFF_MON_VALUE number(20,2),
HOLD_FUND_ACCOUNT_CNT number(20,2),
FUND_ADD_FLAG VARCHAR2(300),
HOLD_FUND_PRODUCT_COUNT number(20,2),
FUND_QUAR_TRADE_CNT number(20,2),
FUND_QUAR_TRADE_AMT number(20,2),
FUND_DISTRIBUTE_ACTIVE_CNT number(20,2),
FUND_ACTIVE_TRANSFORM_CNT number(20,2),
FUND_YEAR_TRADE_CNT number(20,2),
FUND_YEAR_TRADE_AMT number(20,2),PT VARCHAR2(20))
partition by list(PT) (
partition P201407 values  ('201407'),
partition P201408 values  ('201408'),
partition P202406 values  ('201409')
);
comment on column jkdm_toa_custom_fund_info.GBD_CLIENT_NO is '客户号';
comment on column jkdm_toa_custom_fund_info.GBD_MASTER_PARTY_ID is '集团主客户号';
comment on column jkdm_toa_custom_fund_info.count_date is '统计日期';
comment on column jkdm_toa_custom_fund_info.FUND_EFF_MON_VALUE is '基金有效客户当月基金净值';
comment on column jkdm_toa_custom_fund_info.HOLD_FUND_ACCOUNT_CNT is '基金有效客户持有基金合同总数';
comment on column jkdm_toa_custom_fund_info.FUND_ADD_FLAG is '是否本月基金新增客户';
comment on column jkdm_toa_custom_fund_info.HOLD_FUND_PRODUCT_COUNT is '持有基金产品数量';
comment on column jkdm_toa_custom_fund_info.FUND_QUAR_TRADE_CNT is '基金最近三个月累计交易的次数';
comment on column jkdm_toa_custom_fund_info.FUND_QUAR_TRADE_AMT is '基金最近三个月次均交易的金额';
comment on column jkdm_toa_custom_fund_info.FUND_DISTRIBUTE_ACTIVE_CNT is '基金累计配送活动次数';
comment on column jkdm_toa_custom_fund_info.FUND_ACTIVE_TRANSFORM_CNT is '基金累计活动转化次数';
comment on column jkdm_toa_custom_fund_info.FUND_YEAR_TRADE_CNT is '基金最近一年累计交易的次数';
comment on column jkdm_toa_custom_fund_info.FUND_YEAR_TRADE_AMT is '基金最近一个年次均交易的金额';

 

CREATE OR REPLACE PACKAGE pkg_jk_trun_part IS
  /*
  *删除数据
  */
  PROCEDURE jk_tr_partion_table(id in varchar2,part_name in varchar2);

END pkg_jk_trun_part;

 

CREATE OR REPLACE PACKAGE BODY pkg_jk_trun_part IS
  /*
  * 删除数据
  */
  PROCEDURE jk_tr_partion_table(id in varchar2,part_name in varchar2) IS

    table_name_sql varchar2(400);
  begin

        table_name_sql := 'ALTER TABLE JKDMDATA.' || id||' truncate PARTITION '||part_name;

        execute immediate table_name_sql;
      commit;

  end jk_tr_partion_table;

END pkg_jk_trun_part;

 

 grant execute on  pkg_jk_trun_part  to jkdmopr;

grant select,insert,update,delete  on jkdm_toa_custom_fund_info to jkdmopr;

 

## truncate tmp表
sqoop eval -D mapred.job.queue.name=${queueName}  \
--connect jdbc:oracle:thin:@${jk_connection} \
--username ${jk_username}  \
--password ${jk_password}  \
--query " begin JKDMDATA.pkg_jk_trun_part.jk_tr_partion_table('jkdm_toa_custom_fund_info','P${dateStr:0:6}'); end; "  \

 

 

 

 

## 同步数据到oracle
sqoop export -D mapred.job.queue.name=${queueName} \
--connect jdbc:oracle:thin:@${jk_connection} \
--table JKDMDATA.jkdm_toa_custom_life_info \
--columns  GBD_CLIENT_NO,GBD_MASTER_PARTY_ID,count_date,LIFE_EFF_PAYMENT_AMT,LPMS_LIFE_ANNUAL_PAYMENT_AMT,LPMS_LIFE_BULK_PAYMENT_AMT,ELIS_ADD_FLAG,HOLD_ENDOWNMENT_PROD_FLAG,HOLD_MEDI_FEE_PROD_FLAG,HOLD_ACCID_INJURY_PROD_FLAG,HOLD_PENSION_PROD_FLAG,LIFE_PROD_COUNT,HOLD_LIFE_PROD_TYPE_COUNT,ELIS_DISTRIBUTE_ACTIVE_CNT,ELIS_TRANS_LINE_CNT,ELIS_ACTIVE_TRANSFORM_CNT,PT \
--username ${jk_username} \
--password ${jk_password} \
--input-fields-terminated-by '\001' \
--input-lines-terminated-by '\n' \
--input-null-non-string '\\N' \
--input-null-string  '\\N' \
--num-mappers 16 \
--export-dir /user/hive/warehouse/gbd_360_mask.db/tmp_jkdm_toa_custom_life_info \
--verbose \