查询数据
(1)SELECT基础
- SELECT用于映射符合指定查询条件的行
- Hive SELECT 是数据库标准SQL的子集
使用方法类似于
(2)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子句之前
(5)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:计算维度层次级别上的聚合