一:准备好需要使用到的脚本
1.sqoop import 拉取数据,如
#!/bin/bash
sqoop import --connect "jdbc:sqlserver://192.168.11.11;databaseName=AIM_FI;username=***;password=2***" --table Achieve --driver com.microsoft.sqlserver.jdbc.SQLServerDriver --hive-import --hive-table ods.ods_AIM_FIN_Achieve --hive-overwrite --hive-drop-import-delims --delete-target-dir -m 1
sqoop import --connect "jdbc:sqlserver://192.168.11.12;databaseName=WAL_FI;username=***;password=2***" --table Achieve --driver com.microsoft.sqlserver.jdbc.SQLServerDriver --hive-import --hive-table ods.ods_WAL_FIN_Achieve --hive-overwrite --hive-drop-import-delims --delete-target-dir -m 1
sqoop import --connect "jdbc:sqlserver://192.168.11.13;databaseName=ANL_FI;username=***;password=2***" --table Achieve --driver com.microsoft.sqlserver.jdbc.SQLServerDriver --hive-import --hive-table ods.ods_ANL_FIN_Achieve--hive-overwrite --hive-drop-import-delims --delete-target-dir -m 1
2.hive中执行的sql脚本
注意sql脚本中,每句sql需要分号分隔开,无用语句记得删掉,按次序执行,如下示例:
insert overwrite table ods.ods_aim_wal_anl_base_regionhistories
select * from ods.ods_aim_base_regionhistories;
insert into table ods.ods_aim_wal_anl_base_regionhistories
select * from ods.ods_wal_base_regionhistories;
insert into table ods.ods_aim_wal_anl_base_regionhistories
select * from ods.ods_anl_base_regionhistories;
insert overwrite table ods.ods_resumeachieve_t_regionhistories_tmp1
select ChargeName, regexp_replace(regexp_replace(sub, '\\{', ''), '\\}', '') managebranch,StartDate,chargeno from (select * from ods.ods_aim_wal_anl_base_regionhistories lateral view explode(split(ManageBranchNos,'\\}\\{')) tmp as sub) tmp_a;
insert overwrite table ods.ods_aim_wal_anl_base_employees
select * from ods.ods_aim_base_employees;
insert into table ods.ods_aim_wal_anl_base_employees
select * from ods.ods_wal_base_employees;
insert into table ods.ods_aim_wal_anl_base_employees
select * from ods.ods_anl_base_employees;
insert overwrite table ods.ods_aim_wal_anl_base_branchs
select * from ods.ods_aim_base_branchs;
insert into table ods.ods_aim_wal_anl_base_branchs
select * from ods.ods_wal_base_branchs;
insert into table ods.ods_aim_wal_anl_base_branchs
select * from ods.ods_anl_base_branchs;
insert overwrite table ods.ods_aim_wal_anl_base_teams
select * from ods.ods_aim_base_teams;
insert into table ods.ods_aim_wal_anl_base_teams
select * from ods.ods_wal_base_teams;
insert into table ods.ods_aim_wal_anl_base_teams
select * from ods.ods_anl_base_teams;
INSERT
overwrite TABLE dws.dws_resumeachieve_t_result_tmp1
SELECT
B.RecomId,
A.EffectiveDate,
A.ParentId,
A.CAID,
A.ResumeName,
A.Amount,
O.workno,
O.email,
O.isleave,
O.leavedate,
O.branchno,
O.teamsno,
CASE
WHEN instr(O.branchno,'AIM')>0
THEN '埃'
WHEN instr(O.branchno,'WAL')>0
THEN '沃'
WHEN instr(O.branchno,'ANL')>0
THEN '安'
ELSE O.branchno
END brand,
br.name office,
abt.name team,
D.IncomeIds,
B.AddTime,
regexp_replace(regexp_replace(split(D.IncomeIds,'\\}\\{')[0], '\\{', ''), '\\}', '') IncomeIds0
,
regexp_replace(regexp_replace(split(D.IncomeIds,'\\}\\{')[1], '\\{', ''), '