9 Hive操作——HQL语言(DML、Join、Hive函数）

一、DML语法之加载数据

在Hive中建表成功之后，就会在HDFS上创建一个文件夹，文件夹名字就是表名；
文件夹路径默认值是/user/hive/warehouse，可通过改参数修改

需要把数据文件移动到对应表文件夹下面， Hive表才能映射成功;
 最暴力的方式就是使用hadoop fs –put| -mv等方式直接移动
 但是，Hive官方推荐使用Load命令将数据加载到表中

注：本地文件系统指的是Hiveserver2服务所在机器（如Node1）的本地Linux文件系统，不是Hive客户端所在的本地文件系统

LOAD 语法规则：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;

练习Load Data From Local FS与Load Data From HDFS

一、建表

--step1:建表
--建表student_local 用于演示从本地加载数据
create table student_local(num int,name string,sex string,age int,dept string) 
row format delimited fields terminated by ',';

--建表student_HDFS 用于演示从HDFS加载数据
create external table student_HDFS(num int,name string,sex string,age int,dept string) 
row format delimited fields terminated by ',';

二、load加载数据

--建议使用beeline客户端 可以显示出加载过程日志信息
--step2:加载数据
-- 从本地加载数据 数据位于HS2（node1）本地文件系统 本质是hadoop fs -put上传操作
LOAD DATA LOCAL INPATH '/root/hivedata/students.txt' INTO TABLE student_local;
--从HDFS加载数据 数据位于HDFS文件系统根目录下 本质是hadoop fs -mv 移动操作
--先把数据上传到HDFS上 hadoop fs -put /root/hivedata/students.txt /
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;

#insert加载数据

也可使用insert语句把查询的结果插入到另一张表中

insert+select表示：将查询的结果插入到指定表中，需要保证查询结果列的数目和需要插入表列数目一致

INSERT INTO TABLE tablename select_statement1 FROM from_statement;

--step1:创建一张源表student
drop table if exists student;
create table student(num int,name string,sex string,age int,dept string)
row format delimited
fields terminated by ',';
--加载数据
load data local inpath '/root/hivedata/students.txt' into table student;
--step2：创建一张目标表 只有两个字段
create table student_from_insert(sno int,sname string);
--使用insert+select插入数据到新表中
insert into table student_from_insert select num,name from student;
select *
from student_insert1;

二、DML语法之查询数据

写查询SQL的首先要确定的事是你查询谁（FROM)，表名和列名不区分大小写
数据准备：

附件资料中有一份数据文件《us-covid19-counties.dat》，记录了2021-01-28美国各个县累计新冠确诊病例数和累计死亡病例数

创建表t_usa_covid19
drop table if exists t_usa_covid19;
CREATE TABLE t_usa_covid19(
count_date string,
county string,
state string,
fips int,
cases int,
deaths int)
row format delimited fields terminated by ",";
--将源数据load加载到t_usa_covid19表对应的路径下
load data local inpath '/root/hivedata/us-covid19-counties.dat' into table t_usa_covid19;

查询语法结构：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[LIMIT [offset,] rows];

小技巧：DataGrip里面表的位置CTRL+Q查看表的建表信息

1.DISTINCT去重

--多个字段distinct 整体去重
select distinct county,state from t_usa_covid19;

2.查询常数返回

select 1 from tablename --查出来的所有数据都是1

3.WHERE语句

--where条件中使用函数：找出州名字母长度超过10位的有哪些
select * from t_usa_covid19 where length(state) >10 ;

注意：Where后不能跟聚合函数

select state,sum(deaths) from t_usa_covid19 where sum(deaths) >100 group by state;
-- --报错 SemanticException:Not yet supported place for UDAF ‘sum'

聚合函数要使用它的前提是结果集已经确定。而WHERE子句还处于“确定”结果集的过程中，因而不能使用聚合函数。

--可以使用Having实现
select state,sum(deaths) from t_usa_covid19 group by state having sum(deaths) > 100;

4.聚合操作

聚合（Aggregate）操作函数，如： Count、 Sum、 Max、 Min、 Avg等函数。
聚合函数的特点是不管原始数据有多少行，经过聚合操作只返回一条数据，这一条数据就是聚合的结果

 常见聚合操作

AVG(column)	返回某列的平均值
COUNT(column)	返回某列的行数（不包括 NULL 值）
COUNT(*)	返回被选行数
MAX(column)	返回某列的最高值
MIN(column)	返回某列的最低值
SUM(column)	返回某列的总和

COUNT(*) 会包含空值NULL      COUNT(字段）不包含空值

--统计美国总共有多少个县county
select count(county) from t_usa_covid19;

注意：COUNT(字段)是数人头数；SUM(字段)是求和

5.GROUP BY语句

用于结合聚合函数， 根据一个列或多个列对结果集进行分组；如果没有group by语法，则表中的所有行数据当成一组。

select state,count(county) from t_usa_covid19 where count_date = "2021-01-28" group by state;

--再想看一下每个县的死亡病例数，我们猜想很简单呀 把deaths字段加上返回 真实情况如何呢？
select state,count(county),deaths from t_usa_covid19 where count_date = "2021-01-28" group by state;
--很尴尬 sql报错了org.apache.hadoop.hive.ql.parse.SemanticException:Line 1:27 Expression not in GROUP BY key 'deaths'

--为什么会报错？？ 

--原因：WHERE执行顺序在GROUP BY 之前，而求和SUM又必须分完组之后才能按组求和。WHERE本来就叫做“分组前过滤”
--结论：出现在GROUP BY中select_expr的字段：要么是GROUP BY分组的字段；要么是被聚合函数应用的字段。因为聚合函数的本质就是多进一出，最终返回一个结果

--deaths不是分组字段 报错
--state是分组字段 可以直接出现在select_expr中

（不能翻译成每组的***)(在GROUP BY 存在时候，SELECT只能出现分组字段和聚合函数值)

--正确：
select state,count(county),sum(deaths) from t_usa_covid19 where count_date = "2021-01-28" group by state

6.HAVING

由于WHERE关键字无法与聚合函数一起使用，出现了HAVING子句

HAVING子句筛选分组后的各组数据,并且可以在Having中使用聚合函数，因为此时where， group by已经执行结束，结果集已经确定

--统计2021-01-28死亡病例数大于10000的州

--先where分组前过滤，再进行group by分组，分组后结果集确定 再使用having过滤
select state,sum(deaths) from t_usa_covid19 where count_date = "2021-01-28" group by state having sum(deaths) > 10000;
--给聚合函数列起个别名更好 即having直接引用结果过滤 不需要再单独计算一次SUM了
select state,sum(deaths) as cnts from t_usa_covid19 where count_date = "2021-01-28" group by state having cnts> 10000;

Having和Where的区别：
 where是在分组前对数据进行过滤，后面不可以使用聚合函数
 having是在分组后对数据进行过滤，后面可以使用聚合函数

7.LIMIT

LIMIT接受一个或两个数字参数
第一个参数指定要返回的第一行索引，第二个参数指定要返回的总行数

--返回结果集的第6~15行数据

select * from t_usa_covid19 where count_date = "2021-01-28" and state ="California" limit 5,10;
--注意 第一行索引是0

8.执行顺序

在查询过程中执行顺序： from > where > group（含聚合） > having >order > select；
1. 聚合语句(sum,min,max,avg,count)要比having子句优先执行
2. where子句执行优先级别优先于聚合语句(sum,min,max,avg,count)
 结合下面SQL感受：

--执行顺序
select state,sum(deaths) as cnts from t_usa_covid19
where count_date = "2021-01-28"
group by state
having cnts> 10000
limit 2;

9.Join

在Hive中，使用最多，最重要的两种join分别是：
inner join（内连接） 、 left join（左连接）

inner join 内连接
只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来

left join 左连接

左外连接(Left Outer Join)，其中outer可以省略， left outer join是早期的写法。
 join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表能关联上的显示返回，关联不上的显示null返回

三、Hive函数

Hive的函数分为两大类：内置函数（Built-in Functions）、用户定义函数UDF（User-Defined Functions）
1. 使用show functions查看当下可用的所有函数；（共289个）
2. 通过describe function extended funcname来查看函数的使用方式
 

内置函数可分为： 数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等；
用户定义函数根据输入输出的行数可分为3类： UDF、 UDAF、 UDTF。

用户定义函数UDF分类标准是根据函数输入输出的行数：
UDF（User-Defined-Function）普通函数，一进一出
UDAF（User-Defined Aggregation Function）聚合函数，多进一出
UDTF（User-Defined Table-Generating Functions）表生成函数，一进多出

UDF分类标准扩大化

UDF分类标准本来针对的是用户自己编写实现的函数。但UDF分类标准可以扩大到Hive的所有函数中：包括内置函数
 比如Hive官方文档中，内置聚合函数就被归为UDAF类型

 Hive常用内置函数

1）字符串函数

 •字符串长度函数：length
•字符串反转函数：reverse
•字符串连接函数：concat
•带分隔符字符串连接函数：concat_ws
•字符串截取函数： substr,substring

------------String Functions 字符串函数------------

select length("itcast");
select reverse("itcast");
select concat("angela","baby");

--带分隔符的字符串连接函数：concat_ws(separator, [string | array(string)]+)
select concat_ws('.', 'www', array('itcast', 'cn'));
--第一个参数指定分隔符为"."，拼接内容有一个"www"，第二个内容是个数组

--字符串截取函数： substr(str, pos[, len])
--第二个参数：从哪里开始截取（substr时第一个索引是1），第三个参数：截取多长
select substr("angelababy",-2);     --by
select substr("angelababy",2,2);    --ng

--分割字符串函数: split(str, regex)
select split('apache hive', ' ');  --["apache","hive"]

2）日期函数

数据里表示时间有两种方式：1）年月日  2）时间戳（1970年开始）

----------- Date Functions 日期函数 -----------------
--获取当前日期: current_date
select current_date();   --2022-3-10

--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();   --16328372313

--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");  --1231233445

--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');

--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');

--日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');   --213

--日期增加函数: date_add
select date_add('2012-02-28',10);   --2012-03-09

--日期减少函数: date_sub
select date_sub('2012-01-1',10)

3）数学函数

----Mathematical Functions 数学函数-------------
--取整函数: round 返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926);  --3

--指定精度取整函数: round(double a, int d) 返回指定精度的double类型（小数都用的double类型）
select round(3.1415926,4);  --3.1416（保留4位小数）

--取随机数函数: rand每次执行都不一样 返回一个0到1范围内的随机数
select rand();   --0.38237842

--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);  --每次都是0.213124
--种子相当于给我们取随机数的过程一种干扰，让每次都取同一个值

4）条件函数

主要用于条件判断、逻辑判断
 

-----Conditional Functions 条件函数------------------
--使用之前课程创建好的student表数据
select * from student limit 3;

--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
--为True返回第二个参数，为False返回第三个参数
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;
--应用：分数＞60及格，否则不及格

--空值转换函数: nvl(T value, T default_value)
第一个参数是要转换哪一行，第二个参数是如果为NULL则替换为的值
select nvl("allen","itcast");
select nvl(null,"itcast");

--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;