四、Hive数据仓库应用之Hive数据查询语言（二）（超详细步骤指导操作，WIN10，VMware Workstation 15.5 PRO，CentOS-6.7）

Hive远程模式部署参考：
一、Hive数据仓库应用之Hive部署（超详细步骤指导操作，WIN10，VMware Workstation 15.5 PRO，CentOS-6.7）

Hive数据查询语言参考：
四、Hive数据仓库应用之Hive数据查询语言（一）（超详细步骤指导操作，WIN10，VMware Workstation 15.5 PRO，CentOS-6.7）

五、排序操作

1、在虚拟机Node-01的目录/export/data/hive_data下执行“vi sales.txt”命令，创建商品销售额数据文件sales.txt，在数据文件sales.txt中添加如下内容。
创建文件指令如下：

cd /export/data/hive_data
vi sales.txt

添加内容如下：

SiChuan,ChengDu,34631
SiChuan,MianYang,54516
SiChuan,LeShan,41288
SiChuan,DeYang,13492
SiChuan,PanZhiHua,13492
SiChuan,YaAn,64473
SiChuan,DuJiangYan,12464
SiChuan,LuZhou,29712
HuBei,WuHan,16564
HuBei,YiBin,87007
HuBei,XiaoGan,59840
HuBei,JinZhou,86992
HuBei,HuangGang,12906
HuBei,XiangYang,64564
HuBei,EnShi,23074
HeBei,ShiJiaZhuang,73612
HeBei,BaoDing,68192
HeBei,TangShan,29889
HeBei,XingTai,86107
HeBei,LangFang,70897
HeBei,ChengDe,30378

2、将数据文件sales.txt上传到HDFS的/hive_data/sales目录中，上传文件前需要在HDFS上创建目录/hive_data/sales，有关创建目录和上传数据文件的命令如下：

hdfs dfs -mkdir -p /hive_data/sales
hdfs dfs -put /export/data/hive_data/sales.txt /hive_data/sales

3、根据数据文件sales.txt的数据格式，在数据库hive_database中创建商品销售sales_table。
在虚拟机Node-02的客户端工具Beeline中执行如下命令：

CREATE EXTERNAL TABLE IF NOT EXISTS 
hive_database.sales_table(
province STRING,
city STRING,
sales_amount FLOAT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS textfile
LOCATION '/hive_data/sales';

4、查询商品销售表sales_table中销售额排名前10的省份及城市。

SELECT * FROM hive_database.sales_table ORDER BY sales_amount DESC LIMIT 10;

5、分别通过SORT BY子句和ORDER BY子句对商品销售表sales_table中不同省份内每个城市的销售额进行排序。

SELECT * FROM hive_database.sales_table ORDER BY province DESC,sales_amount DESC;
SELECT * FROM hive_database.sales_table SORT BY province DESC,sales_amount DESC;

SORT BY子句和ORDER BY子句的排序结果相同。

6、通过手动设置参数的方式将Reducer个数调整为3，再次执行SORT BY子句排序的命令。

set mapred.reduce.tasks=3;
SELECT * FROM hive_database.sales_table SORT BY province DESC,sales_amount DESC;

7、可以在SORT BY子句中添加DISTRIBUTE BY子句根据分区字段province实现分区排序。

SELECT * FROM hive_database.sales_table DISTRIBUTE BY province SORT BY province DESC,sales_amount DESC;

六、UNION语句

1、在虚拟机Node-01的目录/export/data/hive_data下执行“vi students.txt”命令，创建学生数据文件students.txt。
创建文件指令如下：

cd /export/data/hive_data
vi student.txt

学生数据文件添加内容如下：

301,student1
302,student2
305,student3
303,student4
302,student5
303,student6
301,student7
303,student8
302,student9
301,student10

创建教师数据文件teacher.txt。

vi teacher.txt

教师数据文件添加内容如下:

301,teacher01
302,teacher02
303,teacher03
304,teacher04

2、将数据文件students.txt上传到HDFS的/hive_data/student目录中，上传文件前需要在HDFS上创建目录/hive_data/student；将数据文件teacher.txt上传到HDFS的/hive_data/teacher目录中，上传文件前需要在HDFS上创建目录/hive_data/teacher。

hdfs dfs -mkdir -p /hive_data/student
hdfs dfs -put /export/data/hive_data/student.txt /hive_data/student
hdfs dfs -mkdir -p /hive_data/teacher
hdfs dfs -put /export/data/hive_data/teacher.txt /hive_data/teacher

3、根据数据文件students.txt的内容，在数据库hive_database中创建学生名单表students_table。

CREATE EXTERNAL TABLE IF NOT EXISTS 
hive_database.students_table(
class STRING,
student_name STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS textfile
LOCATION '/hive_data/student';

4、根据数据文件teacher.txt的内容，在数据库hive_database中创建教师名单表teacher_table。

CREATE EXTERNAL TABLE IF NOT EXISTS 
hive_database.teacher_table(
class STRING,
teacher_name STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS textfile
LOCATION '/hive_data/teacher';

5、将班级301中包含的学生与教师合并在一起。

SELECT student_name calss301
FROM hive_database.students_table 
WHERE class = "301"  
UNION 
SELECT teacher_name calss301
FROM hive_database.teacher_table 
WHERE class = "301";

七、JOIN语句

1、内连接

使用JOIN语句的内连接，连接学生名单表和教师名单表。

SELECT t1.class,t2.class,t1.student_name,t2.teacher_name
FROM hive_database.students_table t1 
INNER JOIN 
hive_database.teacher_table t2
ON t1.class = t2.class;

2、左外连接

使用JOIN语句的左外连接，连接学生名单表和教师名单表。

SELECT t1.class,t2.class,t1.student_name,t2.teacher_name
FROM hive_database.students_table t1 
LEFT OUTER JOIN 
hive_database.teacher_table t2
ON t1.class = t2.class;

3、右外连接

使用JOIN语句的右外连接，连接学生名单表和教师名单表。

SELECT t1.class,t2.class,t1.student_name,t2.teacher_name
FROM hive_database.students_table t1 
RIGHT OUTER JOIN 
hive_database.teacher_table t2
ON t1.class = t2.class;

4、全外连接

使用JOIN语句的全外连接，连接学生名单表和教师名单表。

SELECT t1.class,t2.class,t1.student_name,t2.teacher_name
FROM hive_database.students_table t1 
FULL OUTER JOIN 
hive_database.teacher_table t2
ON t1.class = t2.class;

5、左半连接

使用JOIN语句的左半连接，连接学生名单表和教师名单表。

SELECT t1.class,t1.student_name
FROM hive_database.students_table t1 
LEFT SEMI JOIN 
hive_database.teacher_table t2
ON t1.class = t2.class;

6、笛卡尔积连接

使用JOIN语句的笛卡儿积连接，连接学生名单表和教师名单表。

SELECT t1.class,t1.student_name
FROM hive_database.students_table t1 
CROSS JOIN
hive_database.teacher_table t2;

八、抽样查询

1、随机抽样

随机从学生名单表students_table中抽取三名学生。

SELECT student_name 
FROM hive_database.students_table 
DISTRIBUTE BY RAND() 
SORT BY RAND() LIMIT 3;

2、分桶抽样

将学生名单表的数据按照班级分为三个桶，查询第一个桶中的数据。

SELECT * FROM 
hive_database.students_table  
TABLESAMPLE(BUCKET 1 OUT OF 3 ON class);

3、数据块抽样

抽取学生名单表50%的数据，具体命令如下。

SELECT * FROM 
hive_database.students_table 
TABLESAMPLE(50 PERCENT);

抽取学生名单表3行数据，具体命令如下。

SELECT * FROM 
hive_database.students_table 
TABLESAMPLE(3 ROWS);

抽取学生名单表1Byte数据，具体命令如下。

SELECT * FROM 
hive_database.students_table 
TABLESAMPLE(1B);

参考文献：黑马程序员.Hive数据仓库应用[M].北京：清华大学出版社,2021.

后续学习链接：
五、Hive数据仓库应用之Hive函数（一）（超详细步骤指导操作，WIN10，VMware Workstation 15.5 PRO，CentOS-6.7）