hadoop hive 与 Oracle 互相导入数据

最新推荐文章于 2022-07-13 18:07:39 发布

weixin_33713707

最新推荐文章于 2022-07-13 18:07:39 发布

阅读量628

点赞数

文章标签：大数据数据库 python

原文链接：https://my.oschina.net/fengyunfu/blog/807308

版权

2019独角兽企业重金招聘Python工程师标准>>>

#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'

转载于:https://my.oschina.net/fengyunfu/blog/807308