一、Hive的数据查询(select)
select用于映射符合指定查询条件的行
Hive SELECT是数据库标准SQL的子集
使用方法类似于MySQL
SELECT 1;
SELECT [DISTINCT] column_nam_list FROM table_name;
SELECT * FROM table_name;
SELECT * FROM employee WHERE name!='Lucy' LIMIT 5;
CTE和嵌套查询
-- CTE语法
WITH t1 AS (SELECT …) SELECT * FROM t1
-- 嵌套查询示例
SELECT * FROM (SELECT * FROM employee) a;
进阶语句
列匹配正则表达式
SET hive.support.quoted.identifiers = none;
SELECT `^o.*` FROM offers;
虚拟列(Virtual Columns)
INPUT__FILE__NAME:Mapper Task的输入文件名称
BLOCK__OFFSET__INSIDE__FILE:当前全局文件位置
二、JOIN - 关联查询
指对多表进行联合查询
JOIN用于将两个或多个表中的行组合在一起查询
类似于SQL JOIN,但是Hive仅支持等值连接
内连接:INNER JOIN
外连接:OUTER JOIN RIGHT JOIN, LEFT JOIN, FULL OUTER JOIN
交叉连接:CROSS JOIN
隐式连接:Implicit JOIN
Area C = Circle1 JOIN Circle2
Area A = Circle1 LEFT OUTER JOIN Circle2
Area B = Circle1 RIGHT OUTER JOIN Circle2
AUBUC = Circle1 FULL OUTER JOIN Circle2
mapjoin 在Map端完成
小表关联大表 可进行不等值连接
set hive.auto.convert.join = true(默认值)
运行时自动将连接转换为MAPJOIN
三、集合操作union
所有子集数据必须具有相同的名称和类型
UNION ALL:合并后保留重复项
UNION:合并后删除重复项(v1.2之后)
装载数据:LOAD移动数据
LOAD用于在Hive中移动数据
原始数据被移动到目标表/分区,不再存在于原始位置
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)
装载数据:INSERT表插入数据
-- INSERT支持OVERWRITE(覆盖)和INTO(追加)
INSERT OVERWRITE/INTO TABLE tablename1
[PARTITION (partcol1=val1, partcol2=val2 ...)]
select fileds,... from tb_other;
hive支持从同一个表进行多次插入
INSERT INTO中TABLE关键字是可选的
INSERT INTO table_name VALUES,支持插入值列表
Hive数据插入文件
使用insert语句将数据插入/导出到文件
-- 从同一数据源插入本地文件,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>
Hive数据交换 - IMPORT/EXPORT
使用EXPORT导出数据
EXPORT TABLE employee TO '/tmp/output3';
EXPORT TABLE employee_partitioned partition (year=2014, month=11) TO '/tmp/output5';
使用IMPORT导入数据
IMPORT TABLE employee FROM '/tmp/output3';
IMPORT TABLE employee_partitioned partition (year=2014, month=11) FROM '/tmp/output5';
数据排序 - ORDER BY
ORDER BY (ASC|DESC)类似于标准SQL
只使用一个Reducer执行全局数据排序
速度慢,应提前做好数据过滤
支持使用CASE WHEN或表达式 支持按位置编号排序
set hive.groupby.orderby.position.alias=true;
select * from offers order by case when offerid = 1 then 1 else 0 end;
select * from offers order by 1;
SORT BY/DISTRIBUTE BY
ORT BY对每个Reducer中的数据进行排序
当Reducer数量设置为1时,等于ORDER BY
排序列必须出现在SELECT column列表中
DISTRIBUTE BY类似于标准SQL中的GROUP BY
确保具有匹配列值的行被分区到相同的Reducer
不会对每个Reducer的输出进行排序
通常使用在SORT BY语句之前
SELECT department_id , name, employee_id, evaluation_score
FROM employee_hr
DISTRIBUTE BY department_id SORT BY evaluation_score DESC;
CLUSTER BY
CLUSTER BY = DISTRIBUTE BY + SORT BY
不支持ASC|DESC
排序列必须出现在SELECT column列表中
为了充分利用所有的Reducer来执行全局排序,可以先使用CLUSTER BY,然后使用ORDER BY
聚合函数类似于mysql 就不多介绍
窗口函数是一组特殊函数
扫描多个输入行来计算每个输出值,为每行数据生成一行结果 可以通过窗口函数来实现复杂的计算和聚合
Function (arg1,..., arg n) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_clause>])
PARTITION BY类似于GROUP BY,未指定则按整个结果集
只有指定ORDER BY子句之后才能进行窗口定义
可同时使用多个窗口函数
过滤窗口函数计算结果必须在外面一层
按功能可划分为:排序,聚合,分析
排序窗口函数
ROW_NUMBER() RANK() DENSE_RANK() PERCENT_RANK() NTILE(2)
聚合函数
count() sum() avg() min() max()
分析函数
lead() lead lag first_value last_value
聚合函数作为窗口函数的用法
聚合函数 over (partition by order by )
窗口子句包括哪几部分定义,用什么固定写法
窗口子句只能作用于非窗口函数
一般作用于聚合函数指定over前面的函数的作用范围
rows between 开始 and 结束
range between 开始 and 结束
开始:unbounded preceding current row n preceding n following
结束:current row n preceding n following unbounded