实验一、
Hive内置函数
1、聚合函数
1.1 统计员工信息表employess_table中的部门数。
SELECT
COUNT(DISTINCT staff_dept) dept_num
FROM
hive_database.employess_table;
1.2 获取商品销售表sales_table中每个省份销售额最高的城市。
SELECT
province,SUM(sales_amount) province_amount
FROM
hive_database.sales_table GROUP BY province;
1.3 获取商品销售表sales_table中每个省份销售额最高的城市。
SELECT province,city,sales_amount
FROM
hive_database.sales_table
WHERE sales_amount IN(
SELECT MAX(sales_amount)
FROM
hive_database.sales_table
GROUP BY province);
1.4 获取商品销售表sales_table中每个省份包含的城市。
SELECT province,COLLECT_SET(city) city_set
FROM
hive_database.sales_table
GROUP BY province;
2、数学函数
获取商品销售表sales_table中每个省份的平均销售额。
SELECT province,ROUND(AVG(sales_amount),2)
FROM
hive_database.sales_table GROUP BY province;
3、集合函数
判断学生考试成绩表student_exam_table中意向大学填写了Peking University的学生。
SELECT student_name,
ARRAY_CONTAINS(intent_university,"Peking University")
FROM
hive_database.student_exam_table;
4、类型转换函数
获取员工信息表employess_table中员工薪资的整数数据。
SELECT staff_salary,CAST(staff_salary AS INT)
FROM
hive_database.employess_table;
5、日期函数
在数据库
hive_database中创建日期表
date_table。
CREATE EXTERNAL TABLE IF NOT EXISTS
hive_database.
date_table(
start_
date STRING,
end_
date STRING
)
ROW
FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS textfile
LOCATION '/
hive_data/test_
date';
向日期表
date_table中插入两条数据。
INSERT INTO TABLE
hive_database.
date_table VALUES
("2020-01-08 12:23:43","2020-06-16 06:13:23"),
("2020-05-25 10:11:22","2020-11-28 11:53:03");
5.1 计算日期表
date_table中，开始日期与结束日期相差的月以及开始日期与当前日期相差的月。
SELECT
start_
date,
end_
date,
CURRENT_TIMESTAMP() now_
date,
ROUND(MONTHS_BETWEEN(CURRENT_TIMESTAMP(),start_
date),1) between_now_
date,
ROUND(MONTHS_BETWEEN(end_
date,start_
date),1) between_start_end_
date
FROM
hive_database.
date_table;
5.2 在日期表
date_table中，将开始日期延迟7天，结束日期提前5天。
SELECT
start_
date,
end_
date,
DATE_ADD(start_
date,7) add_start
date,
DATE_SUB(end_
date,5) sub_end
date
FROM
hive_database.
date_table;
5.3 将日期表
date_table中，开始日期的时间格式化为yyyy/MM/dd HH的形式。
SELECT
start_
date,
DATE_
FORMAT(start_
date,'yyyy/MM/dd HH')
format_
date
FROM
hive_database.
date_table;
6、条件函数
6.1 根据员工信息表employess_table中员工年龄数据，判断员工属于中年还是青年。
SELECT staff_name,
IF(staff_age >= 40,"middle age","youth")
FROM employess_table;
6.2 根据员工信息表employess_table中员工薪资数据，判断员工薪资级别。
SELECT staff_name,CASE
WHEN staff_salary < 5000 THEN "low"
WHEN staff_salary >= 5000
AND staff_salary < 8000 THEN "middle"
ELSE "high"
END
FROM
hive_database.employess_table;
7、字符串函数
7.1 将商品销售表sales_table中省份名和城市名拼接在一起。
SELECT CONCAT_WS(':',province,city) FROM
hive_database.sales_table;
7.2 去除员工信息表employess_table中员工姓名中的空格。
SELECT staff_name,
REGEXP_REPLACE(staff_name,'\\s','')
FROM
hive_database.employess_table;
7.3 获取员工信息表employess_table中员工的姓氏。
SELECT SUBSTR(staff_name, INSTR(staff_name,' ')) FROM
hive_database.employess_table;
7.4 重命名员工信息表部门名称，去除部门名称后的“ Department”字符串，在部门名
称前添加“DEPT-”字符串。
SELECT staff_dept,
LPAD(SUBSTR(staff_dept, 0, (LENGTH(staff_dept)-LENGTH(SUBSTR(staff_dept, INSTR(staff_dept,' '))))),
(LENGTH(staff_dept)-LENGTH(SUBSTR(staff_dept, INSTR(staff_dept,' ')))+5),'DEPT-')
FROM
hive_database.employess_table;
8、表生成函数
8.1 拆分学生成绩表student_exam_table的意向大学数据。
SELECT
intent_university,university_new
FROM
hive_database.student_exam_table
LATERAL VIEW explode(intent_university)
intent_university AS university_new;
8.2 拆分学生成绩表student_exam_table的文综或理综数据。
SELECT
humanities_or_sciences,key,value
FROM
hive_database.student_exam_table
LATERAL VIEW explode(humanities_or_sciences)
humanities_or_sciences AS key,value;
8.3 拆分URL地址数据。
SELECT
PARSE_URL_TUPLE("https://www.sogou.com/sogou?prs=5&rfg=1",'HOST','PATH','QUERY') AS (host,path,query);
实验二、
Hive自定义函数
UDF（用户自定义函数）：
1、打开eclipse，选择新建一个Maven项目，配置Maven项目的组织名（GroupId）和项目工程名（ArtifactId），分别设置为“cn.itcast”和“
HIveFunction”。
2、项目中的XML文件pom.xml用于管理Maven项目依赖的配置文件，本项目需要在配置文件pom.xml中添加用于开发
Hive程序的依赖。其中添加的依赖需要与
Hive版本对应。
<dependencies>
<!--
Hive依赖-->
<dependency>
<groupId>org.apache.
hive</groupId>
<artifactId>
hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
</dependencies>
之后点击保存即可，需要等待后台自动下载相关依赖包，界面右下角可查看下载进度，中间不要停止，否则会导致报错。
3、选中并右击项目
HiveFunction 中的“java”目录，在弹出的菜单栏中依次选择“New→Package”，从而新建Package包，将名称命名为cn.itcast.
hive。
4、选中包“cn.itcast.
hive”并单击鼠标右键，在弹出的菜单中依次选择“New→Java Class”新建Java类，将Java类命名为
hiveUDF。
5、编写UDF类，在类
hiveUDF中实现比较两列数值是否相等，具体内容如下。
package cn.itcast.
hive;
import org.apache.
hadoop.
hive.ql.exec.UDF;
public class
hiveUDF extends UDF {
public String evaluate(int col1,float col2){
if (col1>col2){
return "max:"+col1+",diffe:"+(col1-col2);
}else if(col1<col2){
return "max:"+col2+",diffe:"+(col2-col1);
}else {
return "0";
}
}
}
6、右键点击新建的UDF类，选择“Export→JAR file”封装导出jar包，之后勾选需要导出的“
hiveUDF”类文件，设置jar包导出位置, 封装的jar包名称为“
hive_UDF.jar”
7、在虚拟机node-01中创建目录/export/jar/，在目录/export/jar/中执行“rz”命令，将
hive_UDF.jar上传到虚拟机node-01目录/export/jar/下。
mkdir -p /export/jar/
cd /export/jar/
8、在虚拟机node-02中使用
Hive客户端工具Beeline，远程连接虚拟机node-01的
HiveServer2服务操作
Hive，将虚拟机node-01中目录/export/jar/下的
hive_UDF.jar添加到
Hive中。之后执行“LIST JARS;”命令，查看当前
Hive中包含的jar包。
ADD JAR /export/jar/
hive_UDF.jar;
LIST JARS;
9、在
Hive客户端工具Beeline中，创建临时函数CompareSize。执行“SHOW FUNCTIONS LIKE 'Com*';”命令，查看创建的函数CompareSize，若不指定子句LIKE，则会查询
Hive的所有函数包括内置函数。
CREATE TEMPORARY FUNCTION CompareSize AS 'cn.itcast.
hive.
hiveUDF';
SHOW FUNCTIONS LIKE 'Com*';
10、使用函数CompareSize，比较员工信息表employess_table中列employess_table和staff_age的值，命令如下。
SELECT CompareSize(staff_age,late_deduction) FROM
hive_database.employess_table;
UDTF（用户自定表生成函数）：
1、UDTF（用户自定表生成函数）的创建、封装、上传过程和UDF（用户自定义函数）完全一致，在上个实验创建的Maven工程的基础上进行类文件的新建即可，无需再次添加依赖信息。
主要区别是创建的java类文件名称为“
hiveUDTF”，封装的jar包名称为“
hive_UDTF.jar”类文件的具体内容见提供的文件“
hiveUDTF.java”。
2、在虚拟机node-02中使用
Hive客户端工具Beeline，远程连接虚拟机node-01的
HiveServer2服务操作
Hive，将虚拟机node-01中目录/export/jar/下的
hive_UDTF.jar添加到
Hive中。之后执行“LIST JARS;”命令，查看当前
Hive中包含的jar包。
ADD JAR /export/jar/
hive_UDTF.jar;
LIST JARS;
3、创建临时函数spiltname。执行SHOW FUNCTIONS LIKE 'spilt*'命令，查看创建的函数spiltname，若不指定子句LIKE，则会查询
Hive的所有函数包括内置函数。
CREATE TEMPORARY FUNCTION spiltname AS 'cn.itcast.
hive.
hiveUDTF';
SHOW FUNCTIONS LIKE 'spilt*';
4、使用函数spiltname，将员工信息表employess_table中员工姓名拆分为两列。
SELECT spiltname(staff_name) FROM
hive_database.employess_table;
UDAF（用户自定聚合函数）：
1、UDAF（用户自定聚合函数）的创建、封装、上传过程和UDF（用户自定义函数）完全一致，在上个实验创建的Maven工程的基础上进行类文件的新建即可，无需再次添加依赖信息。
主要区别是创建的java类文件有两个，名称分别为“
hiveUDAFCollect”和“
hiveUDAFMain”，封装jar包时需要将这两个类文件进行封装，同时jar包名称为“
hive_UDAF.jar”。两个类文件具体内容见提供的文件“
hiveUDAFCollect.java”和“
hiveUDAFMain.java”。
2、在虚拟机node-02中使用
Hive客户端工具Beeline，远程连接虚拟机node-01的
HiveServer2服务操作
Hive，将虚拟机node-01中目录/export/jar/下的
hive_UDAF.jar添加到
Hive中。
添加jar包并创建：
ADD JAR /export/jar/
hive_UDAF.jar;
3、创建临时函数collectstr。执行SHOW FUNCTIONS LIKE ' collect *'命令，查看创建的函数collectstr，若不指定子句LIKE，则会查询
Hive的所有函数包括内置函数。
CREATE TEMPORARY FUNCTION collectstr AS 'cn.itcast.
hive.
hiveUDAFCollect';
SHOW FUNCTIONS LIKE 'collect*';
4、使用函数collectstr，将学生成绩表student_exam_table中所有学生姓名合并到一
行数据中。
SELECT collectstr(student_name) from
hive_database.student_exam_table;
本文探讨了如何使用DATE_FORMAT函数在SQL中处理日期，通过两个示例展示了不同日期格式下如何获取年份：2021年到2022年的过渡，适合初学者理解日期处理在信息技术中的应用。
扫一扫
2693
到【灌水乐园】发言
