招聘网站—Hive数据分析

招聘网站—Hive数据分析

第1关：统计最热门的十种职业（招聘人数最多）

#进入hive

hive

#在hive中创建数据库 mydb

create database mydb;

#使用数据库 mydb

use mydb;

#创建表 recruitcleaned 并使用"/t"分割字段

create table recruitcleaned(job_name string,city string, salary string, workingExp string, edu string, company_name string, company_size string, welfare string, skill string) row format delimited fields terminated by '\t' stored as textfile;

#将本地清洗后的数据导入recruitcleaned中

load data local inpath '/data/workspace/myshixun/data/advertise.txt' into table recruitcleaned;

#创建 recruitersnumbers 表存放最热门的十种职业的数据

create table recruitersnumbers(job_name string, count int) row format delimited fields terminated by '\t' stored as textfile;

#将结果存入至 recruitersnumbers 表中

insert overwrite table recruitersnumbers select job_name,count(*) count from recruitcleaned group by job_name order by count desc limit 10;

第2关：分析学历相同的情况下每种职位的平均薪资

#创建 edulevelsalary 表

create table edulevelsalary(edu string, job_name string,salary double) row format delimited fields terminated by '\t' stored as textfile;

#将结果存入至 edulevelsalary 表中

insert overwrite table edulevelsalary select edu,job_name,avg(salary)salary from recruitcleaned group by edu,job_name;

第3关：统计各个职业要求具备的技能及其出现的次数

#创建 jobskill 表
create table jobskill(job_name string, skill string,count int) row format delimited fields terminated by '\t' stored as textfile;


#将结果存入至 edulevelsalary 表中
insert overwrite table jobskill select job_name,skill, count(*) count from (select job_name,subview.* from recruitcleaned lateral view explode(split(skill,'\\|')) subview as skill)t1 group by job_name,skill order by job_name,count;

第4关：分析每种职业在各个薪资等级需要招聘的人数

#创建 jobsalarylevel 表

create table jobsalarylevel(job_name string, salary string,count int) row format delimited fields terminated by '\t' stored as textfile;

#将结果存入至 edulevelsalary 表中

insert overwrite table jobsalarylevel select job_name,salary,count(*) count from (select job_name,case when  0<=recruitcleaned.salary and recruitcleaned.salary< 10 then 'primary' when  10<=recruitcleaned.salary and recruitcleaned.salary< 20 then 'intermediate' when recruitcleaned.salary>=20 then 'senior' else 'other' END as salary from recruitcleaned ) t1 group by job_name,salary;