教材第四章Hive SQL数据查询基础实验
提示：如果感觉sogou.500w.utf8文件太大，执行hive命令太耗时间，可以执行以下命令，截取前1万行数据，生成一个新的数据文件sogou.1w.utf8
[root@hive ~]# head -10000 sogou.500w.utf8 > sogou.1w.utf8
[root@hive ~]# wc -l sogou.1w.utf8
一 Hive SQL数据查询语句
1 select语句
首先查看表sogou500w的列字段属性
hive> desc sogou500w;
OK
ts string
uid string
keyword string
rank int
orders int
url string
查询表sogou500w的前10行数据
hive> select * from sogou500w limit 10;
OK
20111230000005 57375476989eea12893c0c3811607bcf 奇艺高清 1 1 http://www.qiyi.com/
20111230000005 66c5bb7774e31d0a22278249b26bc83a 凡人修仙传 3 1 http://www.booksky.org/BookDetail.aspx?
BookID=1050804&Level=1
20111230000007 b97920521c78de70ac38e3713f524b50 本本联盟 1 1 http://www.bblianmeng.com/
20111230000008 6961d0c97fe93701fc9c0d861d096cd9 华南师范大学图书馆 1 1 http://lib.scnu.edu.cn/
20111230000008 f2f5a21c764aebde1e8afcc2871e086f 在线代理 2 1 http://proxyie.cn/
20111230000009 96994a0480e7e1edcaef67b20d8816b7 伟大导演 1 1 http://movie.douban.com/review/1128960/
20111230000009 698956eb07815439fe5f46e9a4503997 youku 1 1 http://www.youku.com/
20111230000009 599cd26984f72ee68b2b6ebefccf6aed 安徽合肥365房产网 1 1 http://hf.house365.com/
20111230000010 f577230df7b6c532837cd16ab731f874 哈萨克网址大全 1 1 http://www.kz321.com/
20111230000010 285f88780dd0659f5fc8acc7cc4949f2 IQ数码 1 1 http://www.iqshuma.com/
查询sogou500w的前10行数据中的uid和keyword两列
hive> select uid, keyword from sogou500w limit 10;
OK
57375476989eea12893c0c3811607bcf 奇艺高清
66c5bb7774e31d0a22278249b26bc83a 凡人修仙传
b97920521c78de70ac38e3713f524b50 本本联盟
6961d0c97fe93701fc9c0d861d096cd9 华南师范大学图书馆
f2f5a21c764aebde1e8afcc2871e086f 在线代理
96994a0480e7e1edcaef67b20d8816b7 伟大导演
698956eb07815439fe5f46e9a4503997 youku
599cd26984f72ee68b2b6ebefccf6aed 安徽合肥365房产网
f577230df7b6c532837cd16ab731f874 哈萨克网址大全
285f88780dd0659f5fc8acc7cc4949f2 IQ数码
2 where语句
过滤出指定关键词的数据行
hive>select * from sogou500w where keyword like "金刚";
hive>select * from sogou500w where keyword like "哥斯拉";
hive>select * from sogou500w where keyword like "奥特曼";
说明：这个数据集中的电影很多，可以随意查询有没有你喜欢的电影^_^
3 group by 语句
统计“金刚”相关电影的搜索次数
hive> select count(*) from sogou500w where keyword like "金刚";
Query ID = root_20210913110601_007e83ef-4077-4f31-a36f-2147cc72d2f2
。。。。。。。。。。。。。。。
Hadoop job information for Stage-1: number of mappers: 3; number of reducers: 1
2021-09-13 11:06:11,135 Stage-1 map = 0%, reduce = 0%
。。。。。。。。。。。。。。。。。
2021-09-13 11:06:51,970 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 16.48 sec
。。。。。。。。。。。。。。。
OK
44
Time taken: 51.137 seconds, Fetched: 1 row(s)
说明：执行MapReduce任务统计关键词包含金刚的数据行数（类似WordCont词频统计）
尝试执行以下Hive SQL语句
hive> select * from sogou500w group by keyword;
FAILED: SemanticException [Error 10025]: Expression not in GROUP BY key ts
说明：报错原因是在Group by 子句中，Select 查询的列要么是group by语句后面跟的列，要么是聚合函数（比如 sum、count 等）计算过的列结果，不支持直接引用非group by后面跟的列
hive>select keyword from sogou500w group by keyword;
。。。。。。。。。。。。。。。。。。。。。。。。。。。
Time taken: 85.989 seconds, Fetched: 1490588 row(s)
说明：数据表中包含的keyword总共1490588个，执行MapReduce统计的输出结果太多无法全部显示出来
可以把Hive SQL的执行结果输出重定向到一个Linux系统本地文件中：
在Linux终端执行命令 hive -e 'select keyword from sogou.sogou500w group by keyword;' >> /root/keyword.txt
说明：将select keyword from sogou.sogou500w group by keyword命令的执行结果输出重定向到文本文件keyword.txt中；发现用户的搜索关键字五花八门，仔细看会有惊喜哦！
提示:由于重定向生成的文件keyword.txt也很大，包含的内容非常多，可在Linux 终端执行less keyword.txt命令，并按住【Page Down】向下翻页；还可在Linux 终端执行less +90p -n keyword.txt命令 定位到从文件90%的位置开始浏览；还可以在Linux终端执行wc -l keyword.txt查看执行结果文件的行数
统计每个用户使用sougou搜索引擎的搜索次数
hive> select uid, count(*) from sogou500w group by uid;
。。。。。。。。。。。。。。。。。。
ffffa3e4f25df45a41de36f7c1a76cf3 22
ffffae732f5e37f9f78388f46b359037 9
ffffc5267ae75e264f779a165e892bc6 2
ffffe6a022459e249cb707a5228fa814 1
Time taken: 112.359 seconds, Fetched: 1352664 row(s)
说明：按用户uid分组，统计每个uid出现的行数，每一行都是一条搜索记录；
由于上面命令的输出结果太多，终端无法全部显示；
可把Hive SQL的执行结果输出重定向到一个Linux系统本地文件中：
在Linux终端执行命令 hive -e 'select uid, count(*) from sogou.sogou500w group by uid;' >> /root/search.txt
在Linux终端执行命令 less search.txt 查看结果文件的内容，执行wc -l search.txt查看文件行数，即执行搜索的用户总数
统计搜索关键词“金刚”的每个用户的搜索次数
直接进行过滤查询：
hive> select uid, keyword, count(*) from sogou.sogou500w where keyword like '金刚' group by uid, keyword;
搜索结果：
2d1522b0b63fd199bb13b27d9e3023b9 金刚 2
579b0ad1a16537068aa33779bb172b31 金刚 2
a51d08b51c0a5c4fc8fa1bbcdb1a8e38 金刚 5
d37a725a4a0a7fe9a31ed50e06f32826 金刚 4
f3a222196d3edb33a41b46d7da1f7fcd 金刚 2
f447c77ee6290af9724427b12b055c80 金刚 1
1b5b616202f4f2da575958173cee70ef 金刚 3
a79c48e51875aacd03ac3a7693fe5bb7 金刚 1
dd329653cf0bd3f63aeca6b7d19cc3c0 金刚 4
ed3f47260a69ee169b314262a21d9d7f 金刚 1
3d8db165b6ff86d5484e9ad8ef9e87e4 金刚 1
47e19c2b86745b68f3ee88b400963580 金刚 1
60e659c4e0246887fd8777b07c1e47b3 金刚 1
627380ae3e0caa660a8ec9c538f40453 金刚 1
76a1bc34a62b66320df32df4e672bb73 金刚 1
8c80009b4d813019f964d57aca0adc17 金刚 4
9e6c69c2a60261f231de2d22a65938cc 金刚 1
a0d86ef493cbccd1b4529cf93cea216f 金刚 3
a7938bad9b95d15a9471b0c7201752b9 金刚 2
c80b11bfefb2a2ad8c898d8f69a6c00f 金刚 1
e9ba92f5c4b9d374eac43c81bb226b97 金刚 2
ef52cdd841b0bcaf602e989f3b0b08ec 金刚 1使用子查询进行过滤查询：
hive> select t.uid, t.keyword, count(*) from (select * from sogou.sogou500w where keyword like '金刚') t group by t.uid, t.keyword;
说明：先查询出keyword等于'金刚'的所有数据行，构成一个临时表t，然后在临时表t中再进行统计查询
2d1522b0b63fd199bb13b27d9e3023b9 金刚 2
579b0ad1a16537068aa33779bb172b31 金刚 2
a51d08b51c0a5c4fc8fa1bbcdb1a8e38 金刚 5
d37a725a4a0a7fe9a31ed50e06f32826 金刚 4
f3a222196d3edb33a41b46d7da1f7fcd 金刚 2
f447c77ee6290af9724427b12b055c80 金刚 1
1b5b616202f4f2da575958173cee70ef 金刚 3
a79c48e51875aacd03ac3a7693fe5bb7 金刚 1
dd329653cf0bd3f63aeca6b7d19cc3c0 金刚 4
ed3f47260a69ee169b314262a21d9d7f 金刚 1
3d8db165b6ff86d5484e9ad8ef9e87e4 金刚 1
47e19c2b86745b68f3ee88b400963580 金刚 1
60e659c4e0246887fd8777b07c1e47b3 金刚 1
627380ae3e0caa660a8ec9c538f40453 金刚 1
76a1bc34a62b66320df32df4e672bb73 金刚 1
8c80009b4d813019f964d57aca0adc17 金刚 4
9e6c69c2a60261f231de2d22a65938cc 金刚 1
a0d86ef493cbccd1b4529cf93cea216f 金刚 3
a7938bad9b95d15a9471b0c7201752b9 金刚 2
c80b11bfefb2a2ad8c898d8f69a6c00f 金刚 1
e9ba92f5c4b9d374eac43c81bb226b97 金刚 2
ef52cdd841b0bcaf602e989f3b0b08ec 金刚 1说明：以上两种查询语句的查询结果是相同的
进一步查询，统计出搜索关键词“金刚”的每个用户的搜索次数大于2次的结果
select uid, keyword, count(*) as cnt from sogou.sogou500w where keyword like '金刚' and cnt>2 group by uid, keyword;
说明：上述SQL语句语法错误，无法执行，原因是count(*)是统计以后的结果，不能作为where语句的查询条件，where语句是先于group by语句执行
改成使用子查询进行过滤查询：
hive> select t.* from (select uid, keyword, count(*) as cnt from sogou.sogou500w where keyword like '金刚' group by uid, keyword) t where cnt >2;
a51d08b51c0a5c4fc8fa1bbcdb1a8e38 金刚 5
d37a725a4a0a7fe9a31ed50e06f32826 金刚 4
1b5b616202f4f2da575958173cee70ef 金刚 3
dd329653cf0bd3f63aeca6b7d19cc3c0 金刚 4
8c80009b4d813019f964d57aca0adc17 金刚 4
a0d86ef493cbccd1b4529cf93cea216f 金刚 3
说明：子查询的结果t包含搜索关键词“金刚”的每个用户的搜索次数，对结果t再进行where条件过滤查询
4 having 语句
having语句可以对group by语句的分组结果进行条件过滤，能够简化SQL语句，无需使用子查询。
使用having语句，统计出搜索关键词“金刚”的每个用户的搜索次数大于2次的结果
hive>select uid, keyword, count(*) as cnt from sogou.sogou500w where keyword like '金刚' group by uid, keyword having cnt>2;
a51d08b51c0a5c4fc8fa1bbcdb1a8e38 金刚 5
d37a725a4a0a7fe9a31ed50e06f32826 金刚 4
1b5b616202f4f2da575958173cee70ef 金刚 3
dd329653cf0bd3f63aeca6b7d19cc3c0 金刚 4
8c80009b4d813019f964d57aca0adc17 金刚 4
a0d86ef493cbccd1b4529cf93cea216f 金刚 3说明：使用having语句的查询结果，和前面使用子查询的查询结果完全相通
5 order by语句
order by语句可以对某列进行全局排序，或者对统计结果进行全局排序
统计所有用户的搜索次数，并列出搜索次数前10位的用户
hive> select uid, count(*) as cnt from sogou.sogou500w group by uid order by cnt desc limit 10;
。。。。。。。。。。。。。。。。。。。。。。。
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
02a8557754445a9b1b22a37b40d6db38 11528
cc7063efc64510c20bcdd604e12a3b26 2571
9faa09e57c277063e6eb70d178df8529 2226
7a28a70fe4aaff6c35f8517613fb5c67 1292
b1e371de5729cdda9270b7ad09484c4f 1277
c72ce1164bcd263ba1f69292abdfdf7c 1120
2e89e70371147e04dd04d498081b9f61 837
06c7d0a3e459cab90acab6996b9d6bed 720
b3c94c37fb154d46c30a360c7941ff7e 676
beb8a029d374d9599e987ede4cf31111 676
Time taken: 126.58 seconds, Fetched: 10 row(s)
也可以在Linux终端执行命令 hive -e 'select uid, count(*) as cnt from sogou.sogou500w group by uid order by cnt desc;' > search_order.txt 将结果保存文本文件中
5 sort by语句
having语句是局部排序，只会对每个reducer任务中对数据进行排序，而非全局有序
统计所有用户的搜索次数，并列出搜索次数前10位的用户
hive> select uid, count(*) as cnt from sogou.sogou500w group by uid sort by cnt desc limit 10;
。。。。。。。。。。。。。。。。。。。。。。。。
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
02a8557754445a9b1b22a37b40d6db38 11528
cc7063efc64510c20bcdd604e12a3b26 2571
9faa09e57c277063e6eb70d178df8529 2226
7a28a70fe4aaff6c35f8517613fb5c67 1292
b1e371de5729cdda9270b7ad09484c4f 1277
c72ce1164bcd263ba1f69292abdfdf7c 1120
2e89e70371147e04dd04d498081b9f61 837
06c7d0a3e459cab90acab6996b9d6bed 720
beb8a029d374d9599e987ede4cf31111 676
b3c94c37fb154d46c30a360c7941ff7e 676
Time taken: 154.256 seconds, Fetched: 10 row(s)
说明：如果有多个reduce任务，那么sort by语句的执行结果和order by不一定完全相同，但是本实验中Hadoop Job的第二阶段只有一个reduce任务，所以本试验中sort by语句和order by与语句执行结果恰好相同
也可以在Linux终端执行命令 hive -e 'select uid, count(*) as cnt from sogou.sogou500w group by uid sort by cnt desc;' > search_sort.txt 将结果保存文本文件中
经测试发现：执行oder by 和 sort by的结果是一样的，原因是
Hadoop job information for Stage-2: number of mappers: 1; number of reducers: 1
在排序阶段只运行了一个reduce任务，因此全局排序和局部排序的结果是一样的
二 Hive SQL多表连接查询
1 创建员工表emp和部门表dept
创建数据库scott
hive>create database if not exists scott location '/scott.db';
说明：创建数据库scott，并指定该数据库在HDFS对应的目录路径/scott.db，不再使用默认路径/user/hive/warehouse/scott.db
创建数据表emp
hive> create table emp(empno varchar(50), ename varchar(30), job varchar(50), mgr varchar(50), sal double, deptno varchar(50)) row format delimited fields terminated by ',' location '/scott/emp';
说明：创建数据表emp，并指定该数据表在HDFS对应的目录路径/scott.db/emp，不再使用默认路径/user/hive/warehouse/scott.db/emp
hive> desc emp;
OK
empno varchar(50)
ename varchar(30)
job varchar(50)
mgr varchar(50)
sal double
deptno varchar(50)
说明：empno员工编号，ename员工姓名， job员工职位，mgr上司编号，sal薪资，deptno所在部门编号
创建emp.csv数据文件，在Linux终端执行vi emp.csv，编辑文件内容如下：
7654,lucyc,clerk,7764,8000,10
7894,tomiy,saleman,7764,8000,20
7454,smith,clerk,7764,8000,20
7654,zhang,saleman,9809,8000,20
7123,liyan,analysit,9809,8000,30
7764,qiaof,manager,0000,8000,30
2342,xiezc,clerk,7764,8000,40
9809,dengy,manager,0000,8000,40加载数据文件emp.csv到emp表
hive> load data local inpath '/root/emp.csv' into table emp;
说明：该命令会将Linux本地文件/root/emp.csv上传到HDFS的/scott.db/emp/emp.csv目录中，而不会上传到Hive默认路径下面的目录中
查看数据表emp的内容
hive> select * from emp;
OK
7654 lucyc clerk 7764 8000.0 10
7894 tomiy saleman 7764 8000.0 20
7454 smith clerk 7764 8000.0 20
7654 zhang saleman 9809 8000.0 20
7123 liyan analysit 9809 8000.0 30
7764 qiaof manager 0000 8000.0 30
2342 xiezc clerk 7764 8000.0 40
9809 dengy manager 0000 8000.0 40
创建数据表emp
hive> create table dept(deptno varchar(50), dname varchar(30), loc varchar(30)) row format delimited fields terminated by ',' location '/scott/dept';
hive> desc dept;
OK
deptno varchar(50)
dname varchar(30)
loc varchar(30)
说明：deptno部门编号，dname部门名称，loc部门地址
创建dept.csv数据文件，在Linux终端执行vi dept.csv，编辑文件内容如下：
10,technology,beijing
30,sales,shenzhen
40,human,shanghai
50,product,xian
60,logistics,beijing
70,presale,guangzhou
80,aftermarket,shenzhen加载数据文件dept.csv到dept表
hive> load data local inpath '/root/dept.csv' into table dept;
查看数据表emp的内容
hive> select * from dept;
OK
10 technology beijing
30 sales shenzhen
40 human shanghai
50 product xian
60 logistics beijing
70 presale guangzhou
2 内连接查询
hive> select * from emp inner join dept;
说明：没有任何连接条件的内连接，输出结果是8*7=56行，6+3=9列的大表（笛卡尔积全集）
hive>select * from emp inner join dept on emp.deptno = dept.deptno;
7654 lucyc clerk 7764 8000.0 10 10 technology beijing
7123 liyan analysit 9809 8000.0 30 30 sales shenzhen
7764 qiaof manager 0000 8000.0 30 30 sales shenzhen
2342 xiezc clerk 7764 8000.0 40 40 human shanghai
9809 dengy manager 0000 8000.0 40 40 human shanghai
说明：有连接条件(on emp.deptno = dept.deptno)的内连接，输出结果是5行，9列的子表（笛卡尔积全集的一个子集）
相当于执行 select * from emp,dept where emp.deptno = dept.deptno;
hive>select * from emp inner join dept on emp.deptno = dept.deptno where dept.deptno = 30;
7123 liyan analysit 9809 8000.0 30 30 sales shenzhen
7764 qiaof manager 0000 8000.0 30 30 sales shenzhen
说明：有连接条件(on emp.deptno = dept.deptno where dept.deptno = 30)的内连接，输出结果是2行，9列的子表
hive>select * from emp inner join dept on emp.deptno = dept.deptno where dept.deptno != 30;
7654 lucyc clerk 7764 8000.0 10 10 technology beijing
2342 xiezc clerk 7764 8000.0 40 40 human shanghai
9809 dengy manager 0000 8000.0 40 40 human shanghai
说明：有连接条件(on emp.deptno = dept.deptno where dept.deptno != 30)的内连接，输出结果是3行，9列的子表
总结：内连接inner join一般都包含紧跟on关键字的连接条件；如果没有on连接条件，将会查询出执行内连接的两张表的笛卡尔积全集；内连接inner join相当于执行多表查询select * from emp,dept where emp.deptno = dept.deptno;
3 自然连接查询
hive>select * from emp natural join dept;
说明：经测试发现，没有连接条件的自然连接的结果和内连接完全相同，输出结果也是8*7=56行，6+3=9列的大表（笛卡尔积全集）
hive>select * from emp natural join dept on emp.deptno = dept.deptno;
说明：该语句执行时会语法报错，原因是自然连接不允许紧跟ON关键字的连接条件
hive> select * from emp natural join dept where emp.deptno = '10';
说明：该语句执行时会语法报错，原因是自然连接where语句不允许出现连接表emp中的列
hive> select * from emp natural join dept where dept.deptno = '10';
说明：该语句执行成功，原因是自然连接where语句只允许出现被连接表dept中的列
总结：经测试发现，自然连接和内连接没有太大的本质区别，区别是1）自然连接不能跟ON关键字；2）自然连接中的where语句不能包含左表（连接表）中的列，可以包含右表（被连接表）中的列
4 外连接
1）左外连接
hive> select * from emp left outer join dept;
说明：没有任何连接条件的左外连接，输出结果和内连接完全相同，输出结果也是8*7=56行，6+3=9列的大表（笛卡尔积全集）
hive> select * from emp left outer join dept on emp.deptno = dept.deptno;
7654 lucyc clerk 7764 8000.0 10 10 technology beijing
7894 tomiy saleman 7764 8000.0 20 NULL NULL NULL
7454 smith clerk 7764 8000.0 20 NULL NULL NULL
7654 zhang saleman 9809 8000.0 20 NULL NULL NULL
7123 liyan analysit 9809 8000.0 30 30 sales shenzhen
7764 qiaof manager 0000 8000.0 30 30 sales shenzhen
2342 xiezc clerk 7764 8000.0 40 40 human shanghai
9809 dengy manager 0000 8000.0 40 40 human shanghai
说明：有连接条件(emp.deptno = dept.deptno)的左外连接，输出结果是8行，9列的子表，其中对于右表dept不符合连接条件的行全部写入空值NULL
2）右外连接
hive> select * from emp right outer join dept;
说明：没有任何连接条件的左外连接，输出结果和内连接完全相同，输出结果也是8*7=56行，6+3=9列的大表（笛卡尔积全集）
hive> select * from emp right outer join dept on emp.deptno = dept.deptno;
7654 lucyc clerk 7764 8000.0 10 10 technology beijing
7123 liyan analysit 9809 8000.0 30 30 sales shenzhen
7764 qiaof manager 0000 8000.0 30 30 sales shenzhen
2342 xiezc clerk 7764 8000.0 40 40 human shanghai
9809 dengy manager 0000 8000.0 40 40 human shanghai
NULL NULL NULL NULL NULL NULL 50 product xian
NULL NULL NULL NULL NULL NULL 60 logistics beijing
NULL NULL NULL NULL NULL NULL 70 presale guangzhou
NULL NULL NULL NULL NULL NULL 80 aftermarket shenzhen
说明：有连接条件(emp.deptno = dept.deptno)的右外连接，输出结果是9行，9列的子表，其中对于左表emp不符合连接条件的行全部写入空值NULL
3）全外连接
hive> select * from emp full outer join dept;
说明：没有任何连接条件的全外连接，输出结果和内连接完全相同，输出结果也是8*7=56行，6+3=9列的大表（笛卡尔积全集）
hive> select * from emp full outer join dept on emp.deptno = dept.deptno;
7654 lucyc clerk 7764 8000.0 10 10 technology beijing
7654 zhang saleman 9809 8000.0 20 NULL NULL NULL
7454 smith clerk 7764 8000.0 20 NULL NULL NULL
7894 tomiy saleman 7764 8000.0 20 NULL NULL NULL
7764 qiaof manager 0000 8000.0 30 30 sales shenzhen
7123 liyan analysit 9809 8000.0 30 30 sales shenzhen
9809 dengy manager 0000 8000.0 40 40 human shanghai
2342 xiezc clerk 7764 8000.0 40 40 human shanghai
NULL NULL NULL NULL NULL NULL 50 product xian
NULL NULL NULL NULL NULL NULL 60 logistics beijing
NULL NULL NULL NULL NULL NULL 70 presale guangzhou
NULL NULL NULL NULL NULL NULL 80 aftermarket shenzhen
说明：有连接条件(emp.deptno = dept.deptno)的全外连接，输出结果是12行，9列的子表，其中对于左表emp和右边dept不符合连接条件的行全部写入空值NULL
2667
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
