Hive学习之路 （十一）Hive的5个面试题详解大数据

用户名，月份，访问次数

A,2015-01,5 
A,2015-01,15 
B,2015-01,5 
A,2015-01,8 
B,2015-01,25 
A,2015-01,5 
A,2015-02,4 
A,2015-02,6 
B,2015-02,10 
B,2015-02,5 
A,2015-03,16 
A,2015-03,22 
B,2015-03,23 
B,2015-03,10 
B,2015-03,1

use myhive; 
create external table if not exists t_access( 
uname string comment '用户名', 
umonth string comment '月份', 
ucount int comment '访问次数' 
) comment '用户访问表'  
row format delimited fields terminated by ","  
location "/hive/t_access"; 

load data local inpath "/home/hadoop/access.txt" into table t_access;

select * from t_access;

--求当月访问次数 
create table tmp_access( 
name string, 
mon string, 
num int 
);  
 
insert into table tmp_access  
select uname,umonth,sum(ucount) 
 from t_access t group by t.uname,t.umonth;

select * from tmp_access;

create view tmp_view as  
select a.name anme,a.mon amon,a.num anum,b.name bname,b.mon bmon,b.num bnum from tmp_access a join tmp_access b  
on a.name=b.name; 
 
select * from tmp_view;

select anme,amon,anum,max(bnum) as max_access,sum(bnum) as sum_access  
from tmp_view  
where amon>=bmon  
group by anme,amon,anum;

use myhive; 
CREATE TABLE `course` ( 
  `id` int, 
  `sid` int , 
  `course` string, 
  `score` int  
) ;

// 插入数据 
// 字段解释：id, 学号， 课程， 成绩 
INSERT INTO `course` VALUES (1, 1, 'yuwen', 43); 
INSERT INTO `course` VALUES (2, 1, 'shuxue', 55); 
INSERT INTO `course` VALUES (3, 2, 'yuwen', 77); 
INSERT INTO `course` VALUES (4, 2, 'shuxue', 88); 
INSERT INTO `course` VALUES (5, 3, 'yuwen', 98); 
INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);

create view tmp_course_view as 
select sid, case course when "shuxue" then score else 0 end  as shuxue,   
case course when "yuwen" then score else 0 end  as yuwen from course;   
 
select * from tmp_course_view;

create view tmp_course_view1 as 
select aa.sid, max(aa.shuxue) as shuxue, max(aa.yuwen) as yuwen from tmp_course_view aa group by sid;   
 
select * from tmp_course_view1;

select * from tmp_course_view1 where shuxue > yuwen;

2010012325

2010012325表示在2010年01月23日的气温为25度

create table tmp_weather as  
select substr(data,1,4) years,substr(data,5,2) months,substr(data,7,2) days,substr(data,9,2) temp from weather;

select * from tmp_weather;

create table tmp_year_weather as  
select substr(data,1,4) years,max(substr(data,9,2)) max_temp from weather group by substr(data,1,4);

select * from tmp_year_weather;

select * from tmp_year_weather a join tmp_weather b on a.years=b.years and a.max_temp=b.temp;

id course  
1,a  
1,b  
1,c  
1,e  
2,a  
2,c  
2,d  
2,f  
3,a  
3,b  
3,c  
3,e

create table t_course(id int,course string) 
row format delimited fields terminated by ",";

load data local inpath "/home/hadoop/course/course.txt" into table t_course;

id    a    b    c    d    e    f 
1     1    1    1    0    1    0 
2     1    0    1    1    0    1 
3     1    1    1    0    1    0

select collect_set(course) as courses from id_course;

set hive.strict.checks.cartesian.product=false; 
 
create table id_courses as select t1.id as id,t1.course as id_courses,t2.course courses  
from  
( select id as id,collect_set(course) as course from id_course group by id ) t1  
join  
(select collect_set(course) as course from id_course) t2;

select id, 
case when array_contains(id_courses, courses[0]) then 1 else 0 end as a, 
case when array_contains(id_courses, courses[1]) then 1 else 0 end as b, 
case when array_contains(id_courses, courses[2]) then 1 else 0 end as c, 
case when array_contains(id_courses, courses[3]) then 1 else 0 end as d, 
case when array_contains(id_courses, courses[4]) then 1 else 0 end as e, 
case when array_contains(id_courses, courses[5]) then 1 else 0 end as f  
from id_courses;

a,01,150 
a,01,200 
b,01,1000 
b,01,800 
c,01,250 
c,01,220 
b,01,6000 
a,02,2000 
a,02,3000 
b,02,1000 
b,02,1500 
c,02,350 
c,02,280 
a,03,350 
a,03,250

use class; 
create table t_store( 
name string, 
months int, 
money int 
)  
row format delimited fields terminated by ",";

load data local inpath "/home/hadoop/store.txt" into table t_store;

create table tmp_store1 as  
select name,months,sum(money) as money from t_store group by name,months; 
 
select * from tmp_store1;

create table tmp_store2 as  
select a.name aname,a.months amonths,a.money amoney,b.name bname,b.months bmonths,b.money bmoney from tmp_store1 a  
join tmp_store1 b on a.name=b.name order by aname,amonths; 
 
select * from tmp_store2;

select aname,amonths,amoney,sum(bmoney) as total from tmp_store2 where amonths >= bmonths group by aname,amonths,amoney;