Hive进阶

一、子查询

--子查询是指在一个完整的查询语句中，嵌套若干个不同功能的小查询，从而完成复杂的编写形式
--Hive只支持WHERe 和 FROM 字句中的子查询
--并且WHERe 子查询只支持IN,NOT TN,EXISTS 和NOT EXISTS四种情况

--查询年龄大于20的学生平均年龄

SELECt AVG(age)
FROM (
   SELECt age
   FROM student 
   WHERe age > 20
)a;

--查询zhangsan老师教授的课程名称

SELECt a.course_name
FROM score a
WHERe EXISTS ( --EXISTS如果子句存在数据就执行外层，不存在则不执行
	SELECt b.name
	FROM teacher b
	WHERe b.name = 'zhangsan' AND b.id = a.teacher_id
);

SELECt a.course_name
FROM score a
WHERe a.teacher_id IN (
	SELECt b.id
	FROM  teacher b
	WHERe b.name = 'zhangsan'
)

二、行转列

--求学生每门课程成绩，展示格式
--1 80-90-xx-xx-xx
--2 XX-XX-XX-XX-XX

--COLLECT_SET/COLLECT_LIST: 把聚合后的列值放到集合中
--CONCAT_WS:取出字符串集合中的每个元素，使用指定分隔符分隔
--CAST:hive中转换字符串类型的 

SELECt student_id 
	,CONCAT_Ws('-',COLLECT_LIST(CAST(score AS STRING)))
FROM score
GROUP BY student_id;

--1 80 90 xx xx
--2 80 90 xx xx
--CASE...WHEN...THEN...END 

SELECt student_id
    ,SUM(CASE WHEN course_name = 'yuwen'THEN score ELSE 0 END) `语文`
    ,SUM(CASE WHEN course_name = 'shuxue 'THEN score ELSE 0 END) `数学`
FROM score
GROUP BY student_id;


SELECt student_id
    ,SUM(CASE 
    		WHEN course_name = 'yuwen'  --如果列名不是yuwen
    		THEN 0            --加0
    		ELSE score        --否则加score
    	END) `语文`
    ,SUM(CASE 
    		WHEN course_name = 'shuxue'
    		THEN 0 
    		ELSE score 
    	END) `数学`
FROM score
GROUP BY student_id;

 三、列转行

--建一个名叫doc的表，字段text  类型String

CREATE TABLE IF NOT EXISTS doc(
    text    STRING
);

LOAD DATA LOCAL INPATH '/home/hadoop/hive/doc.txt' OVERWRITE INTO TABLE doc;

SELECt * FROM doc;

--SPLIT:按照指定分隔符切分字符串

SELECt SPLIT(text,' ')FROM doc;

--EXPLODE:把集合中的每个元素放入单行

SELECt EXPLODE(SPLIT(text,' ')) FROM doc;

SELECt a.word,count(*)
FROM(
    SELECt EXPLODE(SPLIT(text,' ')) word FROM doc
)a
GROUP BY a.word

--排名函数，求TopN 

--RANK:值相同时，名次会重复，总数不变；比如，1,2,2,2,5
--DENSE_RANK:值相同时，名次会重复，总数变少；比如，1,2,2,2,3
--ROW_NUMBER:值相同时，名词按照顺序不重复，总数不变；比如，1,2,3,4,5

EXPLAIN SELECt student_id,score
	,RANK() OVER(ORDER BY score DESC) RANK
    ,DENSE_RANK() OVER(ORDER BY score DESC) DENSE_RANK 
    ,ROW_NUMBER() OVER(ORDER BY score DESC) ROW_NUMBER
FROM score;

四、UDF

-- UDF：用户自定义普通函数，通过编写 Java 代码完成
-- UDAF：自定义聚合函数
-- UDTF：自定义表生成函数
-- 需求：根据学生的生日计算他的星座

-- 1.配置 Hive 的开发环境
-- (1)解压 Hive 压缩包到 D 盘根目录
-- (2)在 Eclipse 创建普通 Java 项目
-- (3)通过 Add External Archives 把 hive 目录下的 lib 目录下的所有 jar 导入

-- 2.编写 UDF 代码
-- (1)创建类继承 UDF 类
-- (2)创建 evaluate 方法，参数就是操作的列值，返回值就是操作结果
-- (3)在 evaluate 方法实现自定义函数逻辑

-- 3.使用UDF
-- (1)打包代码为 jar 文件
-- (2)上传 jar 文件到 Linux
-- (3)在 Hive 中添加这个 jar 文件
-- (4)为 UDF 起一个简短的别名
-- (5)使用别名调用函数

ADD JAR /home/hadoop/UDFZodiacSign.jar;  --把jar包添加到hive
CREATE TEMPORARY FUNCTION zs AS 'com.udf.UDFZodiacSign';  --给这个包起一个简短的别名
SELECt id,name,zs(birthday) FROM student;

SELECt * FROM score;

五、保存查询结果

--1.保存到hdfs

INSERT OVERWRITE DIRECTORY "/result/student_cs"
SELECt id,name
FROM student
WHERe college = 'cs' AND clazz = 'a1';

--2.保存到本地

INSERT OVERWRITE LOCAL DIRECTORY "/home/hadoop/result/student_cs"
ROW FORMAT DELIMITED
FIELDS TERMINATED BY' '
SELECt id,name
FROM student
WHERe college = 'cs' AND clazz = 'a1';

 六、脚本

--通过脚本实现统计学生年龄大于 20的人数

INSERT OVERWRITE LOCAL DIRECTORY "/home/hadoop/result/student_age_count"
SELECt COUNT(*) FROM student WHERe age > 20;

-- hive -e在不启动hiveserver2服务器的前提下执行HiveQL语句

-- 在shell脚本编写hive -e命令

-- 在hive -e命令的参数中编写需要执行的HQL语句

--用户执行shell脚本，shell脚本执行hive -e,hive -e执行HQL