数据同步工具Sqoop--功能例子
数据同步工具Sqoop--功能例子
数据中心开发过程中主要使用的数据同步工具为Sqoop。Sqoop是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库(mysql)间进行数据的传递，可以将一个关系型数据库（例如：MySQL ,Oracle等）中的数据导进到Hadoop的HDFS中，也可以将HDFS的数据导进到关系型数据库中。
下面就数据中心开发时涉及到Sqoop的核心功能（即抽数和推数）所实现的具体功能进行具体代码举例分析。
(一)、Sqoop抽数
1.标签：全量抽取
举例：全量抽取裁判文书(从Mysql导入hive)
代码目录：dc/etl/ods/shell/ods_cb_judgement_dd.sh
具体代码：
sudo -u impala sqoop import
--connect ${db_dbsyn_lira1_url}
--username ${db_dbsyn_lira1_username}
--password ${db_dbsyn_lira1_password}
--table cb_judgement
--hive-import
--hive-overwrite
--hive-database fin_dw
--hive-table ods_cb_judgement_dd
--hive-partition-key dt
--hive-partition-value `date -d "1 days ago" +%Y%m%d` --hive-drop-import-delims -m 1
--null-string '\\N'
--null-non-string '\\N'
【注释】
--hive-partition-value `date -d "1 days ago" +%Y%m%d`
导入数据，指定分区的值为T-1。因为离线跑批用，一般默认T-1数据，所以分区日期会设置成当天日期-1。
--null-string '\\N'
--null-non-string '\\N'
如果数据中含有换行符，会导致抽取过来的数据折行，所以需要去除数据中的此类字符。
2.标签：全量抽数
举例：全量抽取企业信息(从oracle导入hive)
代码目录：dc/etl/ods/shell/ods_ent_info_dd.sh
具体代码：
sudo -u impala sqoop import
--connect ${db_ora11g_url}
--username ${db_ora11g_username}
--password ${db_ora11g_password}
--table ENT_INFO
--hive-import
--hive-overwrite
--hive-database fin_dw
--hive-table ods_ent_info_dd
--hive-partition-key dt
--hive-partition-value `date -d "1 days ago" +%Y%m%d` --hive-drop-import-delims -m 1
--null-string '\\N'
--null-non-string '\\N'
3.标签：增量抽取 自定义SQL
举例：增量抽取外部数据查询历史(从mysql导入hive)
代码目录：dc/etl/ods/shell/ods_eds_query_history2_di.sh
具体代码：
sudo -u impala sqoop import
--connect ${db_crservice_ws1_url}
--username ${db_crservice_ws1_username}
--password ${db_crservice_ws1_password}
--query "select QueryId,TransCode,TransInfo,QueryAccount,WSStatus,WSDetail,ErrMsg,DataSource,relaDSTrans,retDataNum,RequestAddr,Begintime,Endtime,transkeyword from eds_query_history2 where begintime>='${yesterday}' and begintime<'${today}' and \$CONDITIONS"
--target-dir eds_query_history2
--hive-import
--hive-overwrite
--hive-database fin_dw
--hive-table ods_eds_query_history2_di
--hive-drop-import-delims
--hive-partition-key dt
--hive-partition-value `date -d "1 days ago" +%Y%m%d` -m 1 --null-string '\\N'
--null-non-string '\\N'
--split-by QueryId
【注释】
--query "select QueryId,TransCode,TransInfo,QueryAccount,WSStatus,WSDetail,ErrMsg,DataSource,relaDSTrans,retDataNum,RequestAddr,Begintime,Endtime,transkeyword from eds_query_history2 where begintime>='${yesterday}' and begintime<'${today}' and \$CONDITIONS"
查询导入，可以写自定义SQL，其中里面的时间字段作为增量时间戳字段，query后使用的是双引号，则$CONDITIONS前必须加转义符，防止shell识别为自己的变量。
4.标签：增量抽取
举例：税务数据停业注销增量抽取(从mysql导入hive)
代码目录：dc/etl/ods/shell/ods_tax_suspend_dd.sh
具体代码：
sudo -u impala sqoop import
--connect ${db_dbsyn1_url}
--username ${db_dbsyn1_username}
--password ${db_dbsyn1_password}
--table tax_suspend
--hive-import
--hive-overwrite
--hive-database fin_dw
--hive-table ods_tax_suspend_dd
--hive-partition-key dt
--hive-partition-value `date -d "1 days ago" +%Y%m%d` --hive-drop-import-delims -m 1
--null-string '\\N'
--null-non-string '\\N'
--boundary-query 'select min(SERIALNO),max(SERIALNO) from tax_suspend'
【注释】
--boundary-query 'select min(SERIALNO),max(SERIALNO) from tax_suspend'
边界查询，导入的数据为该参数的值（一条sql语句）所执行的结果区间内的数据。
(二)、Sqoop推数
1.标签：全量推数
举例：从hive到mysql
代码目录：dc/etl/app/shell/app_rpt_shareholder_invest_dd
具体代码：
sudo -u hdfs sqoop export
--connect ${db_findata_url}
--username ${db_findata_username}
--password ${db_findata_password}
--table app_rpt_shareholder_invest_dd
--hcatalog-database fin_dw
--hcatalog-table app_rpt_shareholder_invest_dd
--null-string '\\N'
--null-non-string '\\N'
2.标签：增量推数
举例：增量推送诉讼全库查询数据（从hive导出到mysql)
代码目录：dc/etl/app/shell/app_api_court_announ_di.sh
具体代码：
beeline -u $hive_fin_dw_url
-n $hive_fin_dw_username
-p `sh /home/etl/impala.sh`
-e "set mapreduce.map.memory.mb=4096;
set mapreduce.map.java.opts=-Xmx3686m;
insert overwrite directory '/tmp/app_api_court_announ_di'
select serial_no,
ent_name,
announ_type,
undertake_court_name,
defendant,
cast(publish_date as string),
src_website,
case_no,
cast(create_case_date as string),
plaintiff,
agent,
case_class,
target_name,
target_type,
target_amt,
tel,prov,
city,
case_reason,
cast(coll_date as string),
judge_result,
payer,
payee,
endorser,
holder,
cast(ticket_date as string),
bill_numer,
exp_date,
succ_party,
impt_third_party,
undertake_dept,
secretary,
chiefjudge,
judge,
cast(check_date as string),
org_code,
cast(dw_ins_date as string),
'$hubdate',
formatted_ent_name
from app_api_court_announ_di where dt>='$dt' and dt != 'his' and dw_ins_date>='$sdate'"
【注释】
使用Hive新的命令行客户端工具Beeline，将自定义SQL数据插入到临时HDFS文件，字段分隔符默认为’\001’。
sudo -u hdfs sqoop export
--connect ${db_cr_hub1_url}
--username ${db_cr_hub1_username}
--password ${db_cr_hub1_password}
--update-mode allowinsert
--update-key "serial_no,ent_name"
--table APP_API_COURT_ANNOUN_DD
--export-dir /tmp/app_api_court_announ_di
--fields-terminated-by '\001'
--input-null-string '\\N'
--input-null-non-string '\\N' -m 1
【注释】
--update-mode allowinsert
allowinsert为增量更新模式：更新并允许插入。
--input-null-non-string '\\N' -m 1
目前并行度超过1会引起推送的Mysql结果表死锁，统一先设置成1。
--update-key "serial_no,ent_name"
采用sqoop-export插入数据的时候，如果数据已经存在了，插入会失败,所以使用--update-key，它会认为每个数据都是更新，避免插入数据失败。
3.标签：清除数据
举例：清空MySQL一周前的数据
代码目录：dc/etl/app/shell/app_api_ent_management_hit_di.sh
sqoop eval
--connect ${db_cr_hub1_url}
--username ${db_cr_hub1_username}
--password ${db_cr_hub1_password}
--query "delete from app_api_ent_management_hit_di where dw_ins_date <= '$week_ago'"
【注释】
想在Sqoop export从hive抽取数据到mysql关系型数据库，先操作mysql关系型数据库，把目标表的数据清除，这时使用sqoop eval参数。
其他常见功能-功能例子
1.标签：监控
举例：质量监控诉讼监控服务数据
代码目录：dc/etl_part/app/shell/app_api_court_announ_hit_di.sh
具体代码：
moniting-shell
--impala-host=$impalad_host
--impala-db=fin_dw
--rule-db-host=$db_amarmonitor_ip
--job-name=app_api_court_announ_hit_di
--table-name=app_api_court_announ_hit_di
2.标签：分析函数 去重
举例：筛选舆情重复数据
代码目录：dc/etl/dwd/sql/dwd_fei_ent_sent_dd.sql
具体代码：
row_number() over(partition by getmd5(regexp_replace(matchedobj,'\n|\r|\t|\n\r|\s| ||\\(|\\)|（|）|\\[|\\]|【|】|{|}','')),article_id,ruleno
order by id desc, decode(sentiment, '负面', 1, '中性', 2, '正面', 3, '', 4)) rn
【注释】
一般会使用分析函数row_number() over(partition by order by)来筛选；此例中用到了getmd5(自定义UDF)，是因为在去重字段中如果含有中文字符字段会导致去重不干净(Impala SQL Bug)
3.标签：行转列函数 清洗
举例：按顿号拆分被执行人名称并一转多行
代码目录：dc/etl/ods/sql/m_ods_cb_executed_all_dd_02.sql
具体代码：
select *
from (select *
from t_m_ods_cb_executed_all_dd_02
) a lateral view explode(split(deal_pname, '、')) t as split_pname;
【注释】
lateral view explode(split(deal_pname, '、')) t as split_pname
Hive SQL有支持一转多行的函数。
4.标签：类型转换 正则匹配
举例：排污收费信息欠缴金额由String转换到Double
代码目录：dc/etl/dwd/sql/dwd_rsk_env_pollution_charge_dd.sql
具体代码：
case
when regexp_like(owepollutioncharge,'亿')
then cast(regexp_extract(regexp_replace(owepollutioncharge,'[\\,+\\s]+',''),'-?[\\d+.]+',0) as double) * 100000000
else cast(regexp_extract(regexp_replace(owepollutioncharge,'[\\,+\\s]+',''),'-?[\\d+.]+',0) as double) * 10000
end as owepollutioncharge
【注释】
根据字段中实际中文单位，如“万元”、“亿元”，来判断抽取实际数字，并最终换算到单位“元”。
5.标签：空值判断
举例：企业环保中近一年环保记录总条数
代码目录：dc/etl/dm/sql/dm_cri_rsk_ent_env_ds.sql
具体代码：
nvl(b.num2,0) + nvl(c.num2,0) + nvl(d.num2,0) + nvl(e.num2,0) + nvl(f.num2,0) + nvl(g.num2,0) + nvl(h.num2,0) + nvl(i.num2,0)
as y1_env_tot_rec_cnt
【注释】
该汇总指标需要合并各类型数据指标，务必要使用函数nvl(,0)，否则如果其中有值是null将会引起汇总值也为null。
6.标签：条件判断 更新数据
举例：获取债股舆情中fin_security.info_an_basinfo增删改数据
代码目录：dc/etl/dwd/sql/m_dwd_fei_sec_ent_announ_di_01.sql
具体代码：
insert overwrite t_m_dwd_fei_sec_ent_announ_di_01_01
select eid,
infocode,
eitime,
eutime,
eisdel,
regexp_replace(nvl(noticetitle, ''), '\n|\r|\t|\n\r|\\s| | ', ''),
publishdate,
noticedate,
sourcename,
sourceurl,
publishtype,
importlevel,
attachname,
attachtype,
Language
from fin_security.info_an_basinfo;
【注释】
将最新数据插入临时表t_m_dwd_fei_sec_ent_announ_di_01_01
insert overwrite table m_dwd_fei_sec_ent_announ_di_01
select coalesce(a.infocode, b.infocode) as infocode,
case
when b.eid is null then
'I'
when b.eid is not null and a.eid is not null and
a.eutime > b.eutime and a.eisdel = '1' then
'D'
when b.eid is not null and a.eid is not null and
a.eutime > b.eutime and (a.eisdel = '0' or a.eisdel is null) then
'U'
end as data_status
from t_m_dwd_fei_sec_ent_announ_di_01_01 a
left outer join t_m_dwd_fei_sec_ent_announ_di_01_02 b
on b.eid = a.eid
where b.eid is null
or (b.eid is not null and a.eid is not null and a.eutime > b.eutime and
(nvl(a.noticetitle, '') != nvl(b.noticetitle, '') or
nvl(a.publishdate, '') != nvl(b.publishdate, '') or
nvl(a.noticedate, '') != nvl(b.noticedate, '') or
nvl(a.sourcename, '') != nvl(b.sourcename, '') or
nvl(a.sourceurl, '') != nvl(b.sourceurl, '') or
nvl(a.publishtype, '') != nvl(b.publishtype, '') or
nvl(a.importlevel, '') != nvl(b.importlevel, '') or
nvl(a.attachname, '') != nvl(b.attachname, '') or
nvl(a.eisdel, '') != nvl(b.eisdel, '') or
nvl(a.attachtype, '') != nvl(b.attachtype, '')));
【注释】
t_m_dwd_fei_sec_ent_announ_di_01_02存的是上次的快照数据，利用最新数据与之比较获取增删改数据。
7.标签：分析函数 正则函数 去重
举例：筛选公告重复数据
代码目录：
dc/etl/dwd/sql/dwd_rsk_court_exec_brk_prom_ent_dd.sql
具体代码：
rank() over(partition by getmd5(regexp_replace(nvl(ent_name, ''), '\n|\r|\t|\n\r|\\s| | |\\(|\\)|（|）|\\[|\\]|【|】|{|}', '')) order by nvl(version, -1) desc) order_flag
from t_dwd_rsk_court_exec_brk_prom_ent_dd_02 a0
【注释】
分析函数rank() over (partition by order by)分区排序筛选-；正则表达式实现清洗；getmd5()函数实现去重。
8.标签：聚合函数 条件函数 清洗
举例：获取数据质量状态
代码目录：dc/etl_part/ods/sql/ods_tax_abnormal_dd.sql
select
dataserialno,
min(decode(status,'N','0','Checking','0','1')) as status
from fin_dw.impala_check
浙公网安备 33010602011771号