Hive高级查询

查询数据

（１）SELECT基础

• SELECT用于映射符合指定查询条件的行
• Hive SELECT 是数据库标准SQL的子集
  使用方法类似于

（２）CTE和嵌套查询

• CTE(Common Table Expression)
  with t1 as (select ...) select *　from　t1

• 嵌套查询
  select * from(select * from employee) a

(3)进阶语句

• 列匹配正则表达式
  set hive.support.quoted.identifiers=none;
select^o.*from offers;

• 虚拟列(Virtual Columns)
  两个连续下划线,用于数据验证
  INPUT__FILE__NAME：Mapper Task的输入文件名称
  BLOCK__OFFSET__INSIDE__FILE：当前全局文件位置

(4)关联查询

• 指对多表进行联合查询
• JOIN用于将两个或多个表中的行组在一起查询
• 类似于SQL JOIN,但是Hive仅支持等值连接
  内连接：INNER JOIN
  外连接：OUTER JOIN
  (RIGHT JOIN, LEFT JOIN, FULL OUTER JOIN)
  交叉连接：CROSS JOIN
  隐式连接：Implicit JOIN
• JOIN发生在WHERE子句之前

（５）MAPJOIN

• MapJoin操作在Map端完成
  小表关联大表
  可进行不等值连接

• 开启join操作
  set hive.auto.convert.join=true(默认值)
  运行时自动将连接转换为uMAPJOIN

• MAPJOIN
  在UNION ALL, LATERAL VIEW, GROUP BY/JOIN/SORT BY/CLUSTER BY/DISTRIBUTE BY等操作后面
  在UNION, JOIN 以及其他 MAPJOIN之前

Hive集合操作(UNION)

• 所有子集数据必须具有相同的名称和类型
  UNION ALL:合并后保留重复项
  UNION:合并后删除重复项

• 可以在顶层查询中重复使用

• ORDER BY, SORT BY, CLUSTER BY, DISTRIBUTE BY 和LIMIT适用于合并后的整个结果

• 集合其他操作可以使用JOIN/OUTER JOIN来实现,如差集,交集

装载数据

(1)LOAD移动数据

• LOAD用于在Hive中移动数据
  原始数据被移动到目标表/分区,不再存在于原始位置
• LOCAL:指定文件位于本地文件系统
• OVERWRITE表示覆盖现有数据

(2)INSERT表插入数据
使用INSERT语句将数据插入表/分区
(INSERT 支持OVERWRITE覆盖和INTO追加)
Hive支持从同一个表进行多次插入
INSERT INTO中TABLE关键字是可选的
INSERT INTO可以指定插入到哪些字段中
(如：INSERT INTO t(x,y,z))
INSERT INTO table_name VALUES，支持插入值列表
数据插入必须与指定列数相同

INSERT OVERWRITE TABLE test select 'hello'; -- INSERT不支持的写法
insert into employee select * from ctas_employee; -- 通过查询语句插入
-- 多插入
from ctas_employee
insert overwrite table employee select *
insert overwrite table employee_internal select *;
-- 插入到分区
from ctas_patitioned 
insert overwrite table employee PARTITION (year, month)
select *,'2018','09';
-- 通过指定列插入(insert into可以省略table关键字)
insert into employee(name) select 'John' from test limit 1;
-- 通过指定值插入
insert into employee(name) value('Judy'),('John');

(3)数据插入文件

-- 从同一数据源插入本地文件，hdfs文件，表
from ctas_employee
insert overwrite local directory '/tmp/out1'  select *
insert overwrite directory '/tmp/out1' select *
insert overwrite table employee_internal select *;
-- 以指定格式插入数据
insert overwrite directory '/tmp/out3'
row format delimited fields terminated by ','
select * from ctas_employee;
-- 其他方式从表获取文件
hdfs dfs -getmerge <table_file_path>

(4)数据交换 - IMPORT/EXPORT

• IMPORT和EXPORT用于数据导入和导出
  常用于数据迁移场景
  除数据库,可导入导出所有数据和元数据

• 使用EXPORT导出数据
  EXPORT TABLE employee TO '/tmp/output3';

• 使用IMPORT导入数据
  IMPORT TABLE employee FROM '/tmp/output3';

(5)数据排序

• ORDER BY（ASC｜DESC）类似于标准SQL
  只使用一个Reducer执行全局数据排序
  速度慢,应提前做好数据过滤
  支持使用CASE WHEN或表达式
  支持按位置编号排序

• SORT BY对每个Reducer中的数据进行排序
  当Reducer数量设置为1时，等于ORDER BY
  排序列必须出现在SELECT column列表中

• DISTRIBUTE BY类似于标准SQL中的GROUP BY
  确保具有匹配列值的行被分区到相同的Reducer
  不会对每个Reducer的输出进行排序
  通常使用在SORT BY语句之前

• CLUSTER BY = DISTRIBUTE BY + SORT BY
  不支持ASC|DESC
  排序列必须出现在SELECT column列表中
  为了充分利用所有的Reducer来执行全局排序，可以先使用CLUSTER BY，然后使用ORDER BY

(5)聚合运算

• group by:用于分组
  Hive基本内置聚合函数与GROUP BY一起使用
  如果没有指定GROUP BY子句，则默认聚合整个表
  除聚合函数外，所选的其他列也必须包含在GROUP BY中
  GROUP BY支持使用CASE WHEN或表达式
  支持按位置编号分组
  set hive.groupby.orderby.position.alias=true

• having:对group by 聚合结果的条件过滤
  可以避免在GROUP BY之后使用子查询
  HAVING之后可以使用表达式，不建议

• 基础聚合
  使用内置聚合函数进行数据聚合
  max, min, count, sum, avg
  max(distinct col)、avg(distinct col)等
  collect_set, collect_list：返回每个组列中的对象集/列表
  与GROUP BY一起使用，可应用于列或表达式
  没有GROUP BY则按所有列聚合
  select的列必须包含在GROUP BY中
  对NULL的聚合为0
  select count(null) = 0

• 高级聚合

• GROUPING SETS
  实现对同一数据集进行多重GROUP BY操作
  本质是多个GROUP BY进行UNION ALL操作
  

• GROUP BY WITH CUBE|ROLLUP
  CUBE：对分组列进行所有可能组合的聚合
  ROLLUP：计算维度层次级别上的聚合