企业发票异常分析---查找只进不出和只出不进

今天进行的是企业发票异常分析中查找只进不出和只出不进的企业：

最终也是成功找到了加起来将近一万条的只进不出的企业和只出不进的企业：

下面是流程步骤和语句：

 

查询只进不出的企业：建表：create table wenti3(hydm string)row format delimited fields terminated by ',' STORED AS TEXTFILE;

 

查询并将查询结果进入表中：insert into table wenti3 select hydm as hydm from nsrxx where hydm not in (select gf_id from zzsfp) and hydm in (select xf_id from zzsfp);

 

表中数据：2194条

 

 

查询只出不进的企业：建表：create table wenti2(hydm string)row format delimited fields terminated by ',' STORED AS TEXTFILE;

查询并将查询结果进入表中：insert into table wenti2 select hydm as hydm from nsrxx where hydm not in (select xf_id from zzsfp) and hydm in (select gf_id from zzsfp);

表中数据：8579条

 

 然后还将结果导入到了mysql数据库中：

bin/sqoop export \

--connect jdbc:mysql://192.168.10.102:3306/user \

--username root \

--password 123456 \

--table wenti3 \

--num-mappers 1 \

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

--input-fields-terminated-by ","

 

 

 

bin/sqoop export \

--connect jdbc:mysql://192.168.10.102:3306/user \

--username root \

--password 123456 \

--table wenti2 \

--num-mappers 1 \

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

--input-fields-terminated-by ","