通常在hive中进行数据处理,处理好的数据再推送到oracle中使用,需要保持两边数据一致,再推送的时候最好的方式是sqoop的merge形式全量推送,但这样消耗的资源、时间很长,可以只推送新增、更新、删除的数据给oracle,之后再再oracle中对目标表再单独的执行一段merge程序进行更新,筛选出这些数据的sql可参考下面这样的示例:
其中:from 使用多模式插入
tel_m存储 新增、更新的数据
tel_d存储需要删除的数据、无效数据
from( select pre.tel_num as pre_tel_num,
pre.shield_code as pre_shield_code,
pre.shield_reason as pre_shield_reason,
new.tel_num,
new.shield_code,
new.shield_reason
from (select tel_num,
shield_code,
shield_reason,
pa_tcims_concat(tel_num,shield_code,shield_reason) as all_columns
from database_name.view_risk_tel where op_new = '${pre_new}') pre
full outer join (select tel_num,