基于互联网招聘信息统计与分析

#javajob1数据清洗 #薪资数据清洗 create table Money_source(money int); insert into table Money_source select sort_array(array(SELECT SUBSTRING_INDEX(money,'-',1) from javajob1;)) from ; #经验数据清洗 create table background_source(background int); create table background(background varchar(30),number int); #截取有经验限制的最低经验要求 insert into table background_source SELECT SUBSTRING_INDEX(jiangyan,'-',1) from javajob1; #javajob2数据清理 CREATE EXTERNAL TABLE new_JAVAjob2 (jobname string,name string,jingyan string,eduaction string,money string,adress string,tags string,times string) row format delimited fields terminated by ','; insert into table new_javajob2 select jobname,name,jingyan,eduaction,money,adress,tags,times from javajob2 where jobname like '%Java%'; #javajob1数据处理 #A.经验处理 #1.提取出经验不限的个数 insert into table background select '经验不限',count(jiangyan) from javajob1 where jiangyan like '%经验不限%'; #2.提取出最低经验大于4的 insert into table background select '五年以上',count(background) from background_source where background>4; #2.提取出最低经验小于3的 insert into table background select '三年以下',count(background) from background_source where background<=3; #B.薪资处理 create table money(source varchar(30),number int); insert into table money select '最大值',max(source) from money_source; select '最小值',min(source) from money_source; select '均值',avg(source) from money_source; #C.学历处理 create table education(education varchar(30),number int); insert into table education select '本科',count(education) from javajob1 where education like '%本科%'; insert into table education select '大专',count(education) from javajob1 where education like '%大专%'; insert into table education select '学历不限',count(education) from javajob1 where education like '%学历不限%'; insert into table education select '其他',count(education) from javajob1 where education not like '%学历不限%' and education not like '%本科%' and education not like '%大专%'; #javajob2数据处理 #A.城市比例 create table JAVA_chengshitongji(address varchar(30),jobnumber int); insert into table JAVA_chengshitongji select '成都',count(adress) from new_javajob2 where adress like '%成都%'; insert into table JAVA_chengshitongji select '上海',count(adress) from new_javajob2 where adress like '%上海%'; insert into table JAVA_chengshitongji select '北京',count(adress) from new_javajob2 where adress like '%北京%'; insert into table JAVA_chengshitongji select '广州',count(adress) from new_javajob2 where adress like '%广州%'; insert into table JAVA_chengshitongji select '深圳',count(adress) from new_javajob2 where adress like '%深圳%'; #B.职位比例 create table JAVA_zhiweitongji(position_category varchar(30),jobnumber int); insert into table JAVA_zhiweitongji select '高级开发师',count(jobname) from new_javajob2 where jobname like '%高级开发%'; insert into table JAVA_zhiweitongji select '中级开发师',count(jobname) from new_javajob2 where jobname like '%中级开发%'; insert into table JAVA_zhiweitongji select '初级开发师',count(jobname) from new_javajob2 where jobname like '%初级%'; insert into table JAVA_zhiweitongji select '架构师',count(jobname) from new_javajob2 where jobname like '%架构%'; #C.各职位城市比例 #高级 create table gaoji_chengshi(address varchar(30),jobnumber int); insert into table gaoji_chengshi select '成都',count(adress) from new_javajob2 where jobname like '%高级开发%' and adress like '%成都%'; select '北京',count(adress) from new_javajob2 where jobname like '%高级开发%' and adress like '%北京%'; select '上海',count(adress) from new_javajob2 where jobname like '%高级开发%' and adress like '%上海%'; select '广州',count(adress) from new_javajob2 where jobname like '%高级开发%' and adress like '%广州%'; select '深圳',count(adress) from new_javajob2 where jobname like '%高级开发%' and adress like '%深圳%'; #中级 create table zhongji_chengshi(address varchar(30),jobnumber int); insert into table zhongji_chengshi select '成都',count(adress) from new_javajob2 where jobname like '%中级开发%' and adress like '%成都%'; select '北京',count(adress) from new_javajob2 where jobname like '%中级开发%' and adress like '%北京%'; select '上海',count(adress) from new_javajob2 where jobname like '%中级开发%' and adress like '%上海%'; select '广州',count(adress) from new_javajob2 where jobname like '%中级开发%' and adress like '%广州%'; select '深圳',count(adress) from new_javajob2 where jobname like '%中级开发%' and adress like '%深圳%'; #初级 create table diji_chengshi(address varchar(30),jobnumber int); insert into table diji_chengshi select '成都',count(adress) from new_javajob2 where jobname like '%初级%' and adress like '%成都%'; select '北京',count(adress) from new_javajob2 where jobname like '%初级%' and adress like '%北京%'; select '上海',count(adress) from new_javajob2 where jobname like '%初级%' and adress like '%上海%'; select '广州',count(adress) from new_javajob2 where jobname like '%初级%' and adress like '%广州%'; select '深圳',count(adress) from new_javajob2 where jobname like '%初级%' and adress like '%深圳%'; #架构 create table jiagou_chengshi(address varchar(30),jobnumber int); insert into table jiagou_chengshi select '成都',count(adress) from new_javajob2 where jobname like '%架构%' and adress like '%成都%'; select '北京',count(adress) from new_javajob2 where jobname like '%架构%' and adress like '%北京%'; select '上海',count(adress) from new_javajob2 where jobname like '%架构%' and adress like '%上海%'; select '广州',count(adress) from new_javajob2 where jobname like '%架构%' and adress like '%广州%'; select '深圳',count(adress) from new_javajob2 where jobname like '%架构%' and adress like '%深圳%'; #导入mysql数据 #money bin/sqoop export \ --connect jdbc:mysql://hadoop004:3306/jobs?useUnicode=true\&characterEncoding=utf-8 \ --username root \ --password JJ12315kk. \ --table money \ --columns "source,number" \ --fields-terminated-by '\001' \ --export-dir /user/hive/warehouse/money \ --m 1 #background bin/sqoop export \ --connect jdbc:mysql://hadoop004:3306/jobs?useUnicode=true\&characterEncoding=utf-8 \ --username root \ --password JJ12315kk. \ --table background \ --columns "background,number" \ --fields-terminated-by '\001' \ --export-dir /user/hive/warehouse/background \ --m 1 #Education bin/sqoop export \ --connect jdbc:mysql://hadoop004:3306/jobs?useUnicode=true\&characterEncoding=utf-8 \ --username root \ --password JJ12315kk. \ --table education \ --columns "education,number" \ --fields-terminated-by '\001' \ --export-dir /user/hive/warehouse/education \ --m 1 #JAVA_chengshitongji create table JAVA_chengshitongji(address varchar(30),jobnumber int); bin/sqoop export \ --connect jdbc:mysql://hadoop004:3306/jobs?useUnicode=true\&characterEncoding=utf-8 \ --username root \ --password JJ12315kk. \ --table JAVA_chengshitongji \ --columns "address,jobnumber" \ --fields-terminated-by '\001' \ --export-dir /user/hive/warehouse/java_chengshitongji \ --m 1 #JAVA_zhiweitongji create table JAVA_zhiweitongji(position_category varchar(30),jobnumber int); bin/sqoop export \ --connect jdbc:mysql://hadoop004:3306/jobs?useUnicode=true\&characterEncoding=utf-8 \ --username root \ --password JJ12315kk. \ --table JAVA_zhiweitongji \ --columns "position_category,jobnumber" \ --fields-terminated-by '\001' \ --export-dir /user/hive/warehouse/jav