四、Hive数据仓库应用之Hive数据查询语言（一）（超详细步骤指导操作，WIN10，VMware Workstation 15.5 PRO，CentOS-6.7）_在hdfs上创建目录/bin/hive_data/staff

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

实验环境启动

在操作Hive数据仓库前，需要将相关服务启动，具体如下：
启动Hadoop高可用集群：
（1）启动集群中各个节点的Zookeeper服务，命令如下：

cd /export/servers/zookeeper-3.4.10/bin
./zkServer.sh start
1
2

启动后可查询角色，命令如下：

./zkServer.sh status
1

（2）启动集群各个节点监控NameNode的管理日志的JournalNode，命令如下：

hadoop-daemon.sh start journalnode
1

（3）在node-01上分别启动HDFS和YARN，命令如下：

start-dfs.sh
start-yarn.sh
1
2

启动mysql服务：

/etc/init.d/mysqld start
1

启动Hive远程模式：
（1）在node-01中进入安装包解压后目录，使用如下指令进入：

cd /export/servers/apache-hive-1.2.1-bin
1

在该目录下使用如下指令启动Hiveserver2服务：

bin/hiveserver2
1

（2）在node-02上进行Hive安装包解压目录，指令如下：

cd /export/servers/apache-hive-1.2.1-bin
1

在该路径下输入远程连接指令：

bin/beeline
1

按要求输入远程连接协议，连接到指定Hive服务（node-01）的主机口和端名（默认10000）

!connect jdbc:hive2://node-01:10000
1

紧接着按照提示输入node-01的账户名“root”和密码“123456”，自此远程连接成功。

一、SELECT句式分析

1、在虚拟机Node-01的目录/export/data/hive_data下执行“vi employess.txt”命令创建员工信息数据文件employess.txt，在数据文件employess.txt中添加如下内容。
创建文件指令：

cd /export/data/hive_data
vi employess.txt
1
2

添加内容：

Lilith Hardy,30,6000,50,Finance Department
Byron Green,36,5000,25,Personnel Department
Yvette Ward,21,4500,15.5,
Arlen Esther,28,8000,20,Finance Department
Rupert Gold,39,10000,66,R&D Department
Deborah Madge,41,6500,0,R&D Department
Tim Springhall,22,6000,36.5,R&D Department
Olga Belloc,36,5600,10,Sales Department
Bruno Wallis,43,6700,0,Personnel Department
Flora Dan,27,4000,35,Sales Department
1
2
3
4
5
6
7
8
9
10

2、上传文件前需要在HDFS创建目录/hive_data/employess，有关创建目录和上传数据文件的命令如下所示。

hdfs dfs -mkdir -p /hive_data/employess
hdfs dfs -put /export/data/hive_data/employess.txt /hive_data/employess
1
2

3、根据数据文件employess.txt的数据格式，在数据库hive_database中创建员工信息表employess_table。

CREATE EXTERNAL TABLE IF NOT EXISTS 
hive_database.employess_table(
staff_name STRING,
staff_age INT,
staff_salary FLOAT,
late_deduction FLOAT,
staff_dept STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS textfile
LOCATION '/hive_data/employess';
1
2
3
4
5
6
7
8
9
10
11
12

查看数据库中的表信息可看到新建的表。

4、查询员工信息表employess_table包含几种部门。

SELECT DISTINCT staff_dept from hive_database.employess_table;
1

5、查询每个部门平均薪资大于5000的部门。

SELECT staff_dept FROM employess_table GROUP BY staff_dept HAVING AVG(staff_salary) > 5000;
1

二、Hive运算符

1、关系运算符

查询员工信息表employess_table中员工年龄为36岁的员工信息。

SELECT * FROM hive_database.employess_table WHERE staff_age=36;
1

查询员工信息表employess_table中部门Personnel Department的员工信息。

SELECT * FROM hive_database.employess_table WHERE staff_dept LIKE "Per%";
1

查询员工信息表employess_table中员工姓名首字母为A或D的员工信息。

SELECT * FROM hive_database.employess_table WHERE staff_name RLIKE "^A|^D.*";
1

2、算术运算符

计算员工信息表employess_table中所有员工的实际工资。

SELECT staff_name,
staff_salary-late_deduction actual_salary  
FROM hive_database.employess_table;
1
2
3

计算员工信息表employess_table中每位员工每天的薪资，以单月工作日为20天计算。

SELECT staff_name,
staff_salary/20 everyday_salary  
FROM hive_database.employess_table;
1
2
3

3、逻辑运算符

查询员工信息表employess_table中薪资大于等于5000，并且薪资小于等于8000的员工信息。

SELECT * FROM hive_database.employess_table WHERE staff_salary >= 5000 AND staff_salary <= 8000;
1

4、复杂运算符

4.1 在虚拟机Node-01的目录/export/data/hive_data下执行“vi student_exam.txt”命令。创建学生考试成绩文件student_exam.txt，在数据文件student_exam.txt中添加如下内容。
创建文件指令：

cd /export/data/hive_data
vi student_exam.txt
1
2

添加内容如下：

Mandy,Peking University-Wuhan University-Nankai University,Chemistry:90-Physics:98-Biology:83,126-135-140
Jerome,Tsinghua University-Fudan University-Nanjing University,History:89-Politics:92-Geography:87,130-116-128
Delia,Nanjing University-Wuhan University-Nankai University,Chemistry:87-Physics:95-Biology:73,102-123-112
Ben,Tianjin Universit-Peking University-Fudan University,Chemistry:92-Physics:88-Biology:79,98-142-106
Carter,Tsinghua University-Fudan University-Tianjin Universit,History:90-Politics:91-Geography:80,109-111-140
Vivian,Fudan University-Nanjing University-Nankai University,Chemistry:83-Physics:86-Biology:90,120-140-132
1
2
3
4
5
6

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

hdfs dfs -mkdir -p /hive_data/student_exam
hdfs dfs -put /export/data/hive_data/student_exam.txt /hive_data/student_exam
1
2

4.3 根据数据文件student_exam.txt的数据格式，在数据库hive_database中创建学生考试成绩表student_exam_table，在虚拟机Node-02的客户端工具Beeline中执行如下命令：

CREATE EXTERNAL TABLE IF NOT EXISTS 
hive_database.student_exam_table(
student_name STRING,
intent_university ARRAY<STRING>,
humanities_or_sciences MAP<STRING, FLOAT>,
comprehensive STRUCT<chinese:FLOAT,maths:FLOAT,english:FLOAT>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
STORED AS textfile
LOCATION '/hive_data/student_exam';
1
2
3
4
5
6
7
8
9
10
11
12
13

4.4 查询学生考试成绩表student_exam_table中所有学生的第一个意向大学。

SELECT student_name,
intent_university[0] first 
FROM hive_database.student_exam_table;
1
2
3

4.5 查询学生考试成绩表student_exam_table中，学生的物理或历史成绩。

SELECT student_name,
humanities_or_sciences["Physics"] physics,
humanities_or_sciences["History"] history
FROM hive_database.student_exam_table;
1
2
3
4

4.6 查询学生考试成绩表student_exam_table中，所有学生的数学成绩。

SELECT student_name,
comprehensive.maths maths
FROM hive_database.student_exam_table;
1
2
3

三、公用表达式

获取部门R&D Department中薪资大于8000的员工信息。

WITH part1 AS (
SELECT * FROM 
hive_database.employess_table 
WHERE staff_dept="R&D Department")
SELECT * FROM part1 where part1.staff_salary > 8000;
1
2
3
4
5

四、分组操作

1、分组查询员工信息表employess_table的部门。

SELECT staff_dept FROM 
hive_database.employess_table 
GROUP BY staff_dept;
1
2
3

2、查询员工信息表employess_table中各部门包含的员工个数。

SELECT staff_dept, count(*) num
FROM hive_database.employess_table 
GROUP BY staff_dept;
1
2
3

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

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