Hive查询-SELECT基础
select用于映射符合指定查询条件的行
Hive SELECT是数据库标准SQL子集
使用方法类似于MySQL
select 1;
select [distinct] column_name_list from table_name;
select * from table_name;
select * from employee where name != 'Lucy' limit 5;
CTE和嵌套查询
CTE(Common Table Expression)
CTE语法:
with t1 as (select...) select * from t1
嵌套查询
嵌套查询示例:
select * from (select * from employee) a
Hive查询-进阶语句
列匹配正则表达式
SET hive.support.quoted.identifiers = none;
SELECT `^o.*` FROM offers;
虚拟列
两个连续下划线,用于数据验证
INPUT__FILE__NAME:Mapper Task的输入文件名称
BLOCK__OFFSET__INSIDE__FILE:当前全局文件位置
Hive JOIN-关联查询
指对多表进行联合查询
JOIN用于将两个或多个表中的行组合在一起查询
类似于SQL JOIN,但是Hive仅支持等值连接
内连接:INNER JOIN
外连接:OUTER JOIN
RIGHT JOIN,LEFT JOIN,FULL OUTER JOIN
交叉连接:CROSS JOIN
隐式连接:Implicit JOIN
JOIN发生在WHERE子句之前
Example:
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
Hive JOIN-MAPJOIN
MapJoin操作在Map端完成
小表关联大表
可进行和不等值连接
开启join操作
set hive.auto.convert.join=true(默认值)
运行时自动将连接转换为MAPJOIN
MAPJOIN操作不支持
在UNION ALL,LATERAL VIEW, GROUP BY/JOIN/SORT BY/CLUSTER BY/DISTRIBUTE BY等操作后面
在UNION, JOIN 以及其他 MAPJOIN之前
HIve集合操作(UNION)
所有子集数据必须具有相同的名称和类型
UNION ALL:合并后保留重复项
UNION:合并后删除重复项(v1.2之后)
可以在顶层查询中使用(0.13.0之后)
ORDER BY,SORT BY,CLUSTER BY,DISTRIBUTE BY和LIMIT适用于合并后的整个结果
集合其他操作可以使用JOIN/OUTER JOIN来实现
差集、交集
//MINUS
SELECT a.name
FROM employee a
LEFT JOIN employee_hr b
ON a.name = b.name
WHERE b.name IS NULL;
//INTERCEPT
SELECT a.name
FROM employee a
JOIN employee_hr b
ON a.name = b.name;
装载数据:LOAD移动数据
LOAD用于在Hive中移动数据
原始数据被移动到目标表/分区,不再存在于原始位置(对于hdfs目录下的文件)
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);
LOACAL:指定文件位于本地文件系统
OVERWRITE表示覆盖现有数据
INSERT表插入数据-1
使用INSERT语句将数据插入表/分区
-- INSERT支持OVERWRITE(覆盖)和INTO(追加)
INSERT OVERWRITE/INTO TABLE tablename1
[PARTITION (partcol1=val1, partcol2=val2 ...)]
select fileds,... from tb_other;
Hive支持从同一个表进行多次插入
INSERT INTO中TABLE关键字时可选的
INSETRT INTO可以指定撤入到哪些字段中
insert into t