oracle 直接导入 hive,hadoop hive 与 Oracle 互相导入数据

运维开发网 https://www.qedev.com

2020-03-11 10:41

出处：网络

#1.在Oracle上建立要处理的表

create table SOURCE_TABLE_NAME as

SELECT t.*,rownum as row_num FROM SOURCE_TABLE_NAME_O t ;

alter table SOURCE_TABLE_NAME

add constraint SOURCE_TABLE_NAME_P primary key (ROW_NUM)

#1.在Oracle上建立要处理的表

create table SOURCE_TABLE_NAME as

SELECT t.*,rownum as row_num FROM SOURCE_TABLE_NAME_O t ;

alter table SOURCE_TABLE_NAME

add constraint SOURCE_TABLE_NAME_P primary key (ROW_NUM);

#2.在Oracle上建立处理结果表

DEST_TABLE_NAME

#3.oracle导入到hadoop

nohup \

sqoop import \

--hive-import \

--connect jdbc:oracle:thin:@192.168.1.14:1521:oradb1 \

--username USER1 \

--password "密码" \

--verbose -m 32 \

--table SOURCE_TABLE_NAME \

--hive-table db_hive.SOURCE_TABLE_NAME \

--fields-terminated-by '\t' \

--lines-terminated-by '\n' &

#4.计算

hive -e "drop table db_hive.DEST_TABLE_NAME ;

create table db_hive.DEST_TABLE_NAME row format delimited fields terminated by '\t' STORED AS TEXTFILE as \

select \

max(Dn) as Dn, \

EutranCellTdd_uk , \

max(EutranCellTdd_name) as EutranCellTdd_name, \

max(GsmRelation) as GsmRelation, \

adj_uk, \

max(adj_name) as adj_name, \

max(EnbFunction_uk) as EnbFunction_uk, \

max(EnbFunction_name) as EnbFunction_name, \

max(ManagedElement_uk) as ManagedElement_uk, \

max(ManagedElement_name) as ManagedElement_name, \

max(omc_uk) as omc_uk, \

max(omc_name) as omc_name, \

sum(HO_ToGsmAttOutPerRelation) as HO_ToGsmAttOutPerRelation, \

sum(HO_ToGsmSuccOutPrepPerRelation) as HO_ToGsmSuccOutPrepPerRelation, \

sum(HO_ToGsmSuccOutPerRelation) as HO_ToGsmSuccOutPerRelation, \

max(VENDOR_UK) as VENDOR_UK, \

max(VENDOR_NAME) as VENDOR_NAME, \

max(city_name) as city_name \

from db_hive.SOURCE_TABLE_NAME \

group by EutranCellTdd_uk, adj_uk ;"

#5.hadoop导出oracle(需要清空目标表DEST_TABLE_NAME)

sqoop export \

--connect jdbc:oracle:thin:@192.168.1.14:1521:oradb1 \

--username USER1 \

--password "密码" \

--table DEST_TABLE_NAME \

--export-dir /user/hive/warehouse/db_hive.db/DEST_TABLE_NAME \

--input-fields-terminated-by '\t' \

--input-lines-terminated-by '\n' \

--null-string '\\N' \

--null-non-string '\\N'

分享到

0

0

0