Hive高级查询

Hive高级查询

匹配正则表达式

语法：

regexp_extract(string subject,string pattern,int index)

返回值：string

其中：subject需要处理的字符串

pattern需要匹配的正则表达式

index 对返回的结果进行限制：0表示显示与之匹配的整个字符串，1返回第一个括号里的内容，2返回第二个括号里面的内容。

匹配字符串中的数字

1、匹配其中的一个数字（匹配到数字立即返回结果，后面的数字不在做匹配操作）

hive> SELECT regexp_extract('zhong34234der333','([0-9])',1);
OK
3
Time taken: 0.234 seconds, Fetched: 1 row(s)

2、匹配多个数字（匹配到第一个多个数字的组合立即返回结果，后面的数字组合不在匹配）

hive> SELECT regexp_extract('zhong34234der333','([0-9]+)',1);
OK
34234
Time taken: 0.624 seconds, Fetched: 1 row(s)

3、匹配字符串中的多个词语但是不匹配指定的词语

hive> SELECT regexp_extract('98元汉堡王套餐','((?!汉堡王)汉堡|套餐)',1);
OK
套餐
Time taken: 0.223 seconds, Fetched: 1 row(s)
hive> SELECT regexp_extract('98元汉堡王套餐','((?!汉堡王)汉堡)',1);
OK
 
Time taken: 0.177 seconds, Fetched: 1 row(s)

Join关联

指对多表进行联合查询
JOIN用于将两个或多个表中的行组合在一起查询
类似于SQL JOIN，但是Hive仅支持等值连接
JOIN发生在WHERE子句之前

等值join

(1)概念:Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接，不支持非等值连接

--根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门编号
hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

(2)表的别名
使用别名可以简化查询
使用表名前缀可以提高执行效率

-- 合并员工表和部门表
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

内连接

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

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

左外连接（左连接）

JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

hive (default)> select * from 
emp_basic eb left join emp_psn ep on eb.emp_id = ep.emp_id limit 5;

右外连接（右连接）

右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。

hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

全外连接

将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代。

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d 
on e.deptno = d.deptno;

关于内连接、左连接、右连接、全连接的区分

(1)内连接
Area C = Circle1 JOIN Circle2
(2)左连接
Area A = Circle1 LEFT OUTER JOIN Circle2
(3)右连接
Area B = Circle1 RIGHT OUTER JOIN Circle2
(4)全连接
AUBUC = Circle1 FULL OUTER JOIN Circle2

MapJoin

(1)MapJoin操作在Map端完成
①小表关联大表
②可进行不等值连接
(2)开启MapJoin（默认开启）

set hive.auto.convert.join = true（默认值）

运行时自动将连接转换为MAPJOIN
(3)MAPJOIN操作不支持
①在UNION ALL, LATERAL VIEW, GROUP BY/JOIN/SORT BY/CLUSTER BY/DISTRIBUTE BY等操作后面
②在UNION, JOIN 以及其他 MAPJOIN之前

MAPJOIN操作不支持：

在UNION ALL, LATERAL VIEW, GROUP BY/JOIN/SORT BY/CLUSTER BY/DISTRIBUTE BY等操作后面

在UNION, JOIN 以及其他 MAPJOIN之前

Union

并集

所有子集数据必须具有相同的名称和类型
可以在顶层查询中使用
ORDER BY, SORT BY, CLUSTER BY, DISTRIBUTE BY 和LIMIT适用于合并后的整个结果
(1)union
合并后删除重复项

select * from emp_basic eb where eb.company = 'Roomm'
union
select * from emp_basic eb where eb.company = 'Cogilith'
order by emp_id;

(2)union all
合并后保留重复项

select * from emp_basic eb where eb.company = 'Roomm'
union all
select * from emp_basic eb where eb.company = 'Cogilith'
order by emp_id;

交集(join)

SELECT
name
FROM employee a
JOIN employee_hr b
ON a.name = b.name;

3.差集

SELECT
name
FROM employee a
LEFT JOIN employee_hr b
ON a.name = b.name
WHERE b.name IS NULL;

装载数据

原始数据被移动到目标表/分区，不再存在于原始位置

LOAD DATA LOCAL INPATH '/home/dayongd/Downloads/employee.txt' 
OVERWRITE INTO TABLE employee;
-- LOCAL表示文件位于本地，OVERWRITE表示覆盖现有数据
LOAD DATA LOCAL INPATH '/home/dayongd/Downloads/employee.txt' 
OVERWRITE INTO TABLE employee_partitioned  PARTITION (year=2014, month=12);
-- 没有LOCAL,文件位于HDFS文件系统中
LOAD DATA INPATH '/tmp/employee.txt'  
OVERWRITE INTO TABLE employee_partitioned PARTITION (year=2017, month=12);

LOCAL：指定文件位于本地文件系统
OVERWRITE表示覆盖现有数据

load

文件->表
load data local inpath “本地路径” into table 表名
load data inpath ‘HDFS路径’ into table 表名
表->文件
insert overwrite local directory “本地路径” select * from 表名
表->表
insert overwrite table 新表名 select * from 表名

export和import

export(导出)和import(导入)
1.export:
export table 表名 to ‘HDFS路径’
export table 表名 partition(字段) to ‘HDFS路径’
2.import:
【注意：表名必须是新的】
import table 表名 from ‘HDFS路径’
import table 表名 partition(字段) from ‘HDFS路径’

GROUP BY 分组

group by 语句

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列进行分组，然后对每个组执行聚合操作。
示例:

-- 计算 emp 表每个部门的平均工资
hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
-- 计算 emp 每个部门中每个岗位的最高薪水
hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;

having语句

having和where的不同点:
(1)where 针对表中的列发挥作用，查询数据；having 针对查询结果中的列发挥作用，筛选数据。
(2)where 后面不能写聚合函数，而 having 后面可以使用聚合函数。
(3)having 只用于 group by 分组统计语句。
示例:

-- 求每个部门的平均工资
hive (default)> select deptno, avg(sal) from emp group by deptno;
-- 求每个部门的平均薪水大于 2000 的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

CTE 和嵌套查询

CTE 为 Hive 区别于 MySQL 的查询用法，嵌套查询和 MySQL 类似。
1.CTE(Common Table Expression)
(1)CTE语法

WITH t1 AS (SELECT …) SELECT * FROM t1;

(2)CTE示例

with tab1 as (select id,name,age from people) 
select * from tab1;

2.嵌套查询

SELECT * FROM (SELECT * FROM employee) a;

distribute by和sort by

Distribute分散数据

distribute by col
按照col列把数据分散到不同的reduce

sort by排序

每个 MapReduce 内部进行排序,对全局结果集来说不是排序。
不是全局排序，其在数据进入 reducer 前完成排序。
排序列必须出现在SELECT column列表中
(1)设置 reduce 个数
当Reducer数量设置为1时，等于ORDER BY

hive (default)> set mapreduce.job.reduces=3;

(2)查看设置 reduce 个数

hive (default)> set mapreduce.job.reduces;

Distribute By

类似 MR 中 partition，进行分区，结合 sort by 使用
注意，Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

hive (default)> set mapreduce.job.reduces=3;
hive (default)> select * from emp distribute by deptno sort by empno desc;

CLUSTER BY

当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是倒序排序，不能指定排序规则为 ASC 或者 DESC。
以下两种写法等价：

select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;

注意:
(1)不支持ASC|DESC
(2)排序列必须出现在SELECT column列表中
(3)为了充分利用所有的Reducer来执行全局排序，可以先使用CLUSTER BY，然后使用ORDER BY

Order By

全局排序，只有一个 reducer，会导致当输入规模较大时，需要较长的计算时间，结果全局有序。
ORDER BY 子句在 SELECT 语句的结尾。
(1)ASC（ascend）: 升序（默认）

--查询员工信息按工资升序排列
hive (default)> select * from emp order by sal;

(2)DESC（descend）: 降序

-- 查询员工信息按工资降序排列
hive (default)> select * from emp order by sal desc;

(3)按照别名排序

-- 按照员工薪水的 2 倍排序
hive (default)> select ename, sal*2 twosal from emp order by twosal;

(4)多个列排序

-- 按照部门和工资升序排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal ;

distribute by和group by对比

都是按照key值划分数据
都适用reduce操作
唯一不同的是，distribute by只是单纯的分散数据，而group by把相同key的数据聚集在一起，后续必须是聚合操作

order by与sort by

order by:是全局排序
sort by:只是确保每个reduce上面输出的数据有序，如果只有一个reduce,和order by作用一样

distribute by和sort by使用场景

map输出的文件大小不均
reduce输出的文件大小不均
小文件过多
文件超大

窗口函数

排序

rank() over():不同分组排序不连续
dense_rank() over():不同分组排序连续
percent_rank() over():百分比
row_number() over():行号
NTILE(2)：分片

聚合

max() over()
min() over()
count() over()
sum() over()
avg() over()

分析

lead()
功能：用于取窗口内往下第n行值，以当前行为参照。
这个函数最多传入3个参数，第一个参数指定想要得到值的列，第二个参数（可选参数，默认为1）表示往下第几行，第三个参数（可选参数，默认为NULL）用于指定默认值。

lag()
功能：用于取窗口内往上第n行值，以当前行为参照。
这个函数最多传入3个参数，第一个参数指定想要得到值的列，第二个参数（可选参数，默认为1）表示往上第几行，第三个参数（可选参数，默认为NULL）用于指定默认值。

first_value()
功能：返回分组内排序后，截止到当前行的第一个值。
这个函数最多需两个参数，第一个参数指定想要得到值的列，第二个参数是个布尔值（可选参数，默认为false），如果设置true，跳过null值。

last_value()
功能：返回分组内排序后，截止到当前行的最后一个值。
这个函数最多需两个参数，第一个参数指定想要得到值的列，第二个参数是个布尔值（可选参数，默认为false），如果设置true，跳过null值。

窗口定义
RANK、NTILE、DENSE_RANK、CUME_DIST、PERCENT_RANK、LEAD、LAG和ROW_NUMBER函数不支持与窗口子句一起使用

窗口函数定义

RANK、NTILE、DENSE_RANK、CUME_DIST、PERCENT_RANK、LEAD、LAG和ROW_NUMBER函数不支持与窗口子句一起使用

行窗口定义

SELECT
name, dept_num AS dept, salary AS sal,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) win1,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) win2,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) win3,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) win4,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) win5,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND CURRENT ROW) win6,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) win7,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) win8,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) win9,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) win10,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) win11,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS 2 PRECEDING) win12
FROM employee_contract  ORDER BY dept, name;

范围窗口定义

范围窗口是取分组内的值在指定范围区间内的行
该范围值/区间必须是数字或日期类型
目前只支持一个ORDER BY列

SUM(close) RANGE BETWEEN 500 PRECEDING AND 1000 FOLLOWING
-- 假设当前close值为3000，语句将包含分区内范围从2500到4000的行
--实例
SELECT name, dept_num AS dept, salary AS sal,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) win1,
salary - 1000 as sal_r_start,salary as sal_r_end,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name RANGE BETWEEN 1000 PRECEDING AND CURRENT ROW) win13
FROM employee_contract ORDER BY dept, name;