查询选课信息的简单示例

测试数据

[root@master hive]# cat tmp_data/phy_opt_course.data 
zhangsan    17181515    class101    badminton
lisi    17151716    class101    football
wangwu  17181717    class102    swimming
mike    17181718    class101    swimming
kate    17181719    class102    football
[root@master hive]# cat -A tmp_data/phy_opt_course.data 
zhangsan^I17181515^Iclass101^Ibadminton$
lisi^I17151716^Iclass101^Ifootball$
wangwu^I17181717^Iclass102^Iswimming$
mike^I17181718^Iclass101^Iswimming$
kate^I17181719^Iclass102^Ifootball$

建表语句

[root@master hive]# cat /opt/phy_opt_course.hive 
create table phy_opt_course(stname string, stID int, class string, opt_cour string)
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as textfile;
[root@master hive]# cat /opt/phy_opt_count.hive 
create table phy_opt_count
(opt_course string, course_count int)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;

加载数据

hive> load data local inpath './tmp_data/phy_opt_course.data'
    > into table phy_opt_course;
Loading data to table default.phy_opt_course
OK
Time taken: 2.41 seconds

执行计算并存入表中

hive> insert overwrite table phy_opt_count select phy_opt_course.opt_cour,count(phy_opt_course.stID) from phy_opt_course group by phy_opt_course.opt_cour;
hive> select * from phy_opt_count;
OK
badminton   1
football    2
swimming    2
Time taken: 0.168 seconds, Fetched: 3 row(s)