每日总结

企业异常发票分析

1.导入数据到hive

创建3表导入

create table zzsfp_hwmx3(fp_nid string,date_key string,hwmc string,ggxh string,dw string,sl double,dj double,je double,se double,spbm string)ROW format delimited fields terminated by ',';

create table zzsfp3(fp_nid string,xf_id string,gf_id string,je double,se double,jshj double,kpyf string,kprq string,zfbz string)ROW format delimited fields terminated by ',';

create table nsrxx1(hydm string,nsr_id string,djzclx_dm string,kydjrq string,xgrq string,label string)ROW format delimited fields terminated by ',';

load data local inpath '/opt/module/hive/zzsfp.csv' into table zzsfp;

 

 2.在hive中进行数据清洗，讲三表联动生成一个具有企业id，买入次数，卖出次数的表

create table nsrxx1(hydm string,xgrq string,nsr_id string,djzclx_dm string,label string,kydjrq string,buycnt string,sellcnt string)ROW format delimited fields terminated by ',';

导出到mysql并且生成csv文件

bin/sqoop export \

--connect "jdbc:mysql://hadoop102:3306/metastore?useUnicode=true&characterEncoding=utf-8" \

--username root \

--password 147258 \

--table nsrxx1 \

--num-mappers 1 \

--export-dir /user/hive/warehouse/nsrxx1 \

--input-null-string '\\N' \

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

--input-fields-terminated-by "\t"