Hive高级查询
一.Hive的数据查询
1.基本数据查询
(1)SELECT基础
select用于映射符合指定查询条件的行
select查询类似于sql
(2)CTE和嵌套查询
-
CTE(Common Table Expression)
with t1 as(select ...) select * from t1
-
嵌套查询
select * from(select * from employee)a;
cte和嵌套可以转换
2.join关联查询
(1)基本概念
-
指对多表进行联合查询
-
JOIN用于将两个或多个表中的行组合在一起查询
-
类似于SQL JOIN,但是Hive仅支持等值连接,不支持非等值连接
内连接:INNER JOIN
外连接:OUTER JOIN
RIGHT JOIN, LEFT JOIN, FULL OUTER JOIN
交叉连接:CROSS JOIN
隐式连接:Implicit JOIN -
JOIN发生在WHERE子句之前
数据:
stu
id,name
1,zhangsan
2,lisi
3,wangwu
score
id,scores
2,90
3,80
4,87
各种连接后的情况
(2)内连接join
内连接:只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保
留下来
select * from stu join score on stu.id=score.id
结果:
2,lisi 2,90
3,wangwu 3,80
(3)左外连接 left join
左外连接:JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。
select * from stu left join score on stu.id=score.id
结果:
1,zhangsan null
2,lisi 90
3,wangwu 80
(4)右外连接right join
右外连接:JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。
select * from stu right join score on stu.id=score.id
结果:
2,lisi 90
3,wangwu 80
4,null 87
(5)全连接full join
全外连接:将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话,那么就使用 NULL 值替代。
select * from stu full join score on stu.id=score.id
结果:
1,zhangsan null
2,lisi 90
3,wangwu 80
4,null 87
-
cross full
select * from stu , score where stu.id=score.id
结果:
1,zhangsan 2,90
1,zhangsan 3,80
1,zhangsan 4,87
2,lisi 2,90
2,lisi 3,80
2,lisi 4,87
3,wangwu 2,90
3,wangwu 3,80
3,wangwu 4,87
(6)练习-SELECT及关联查询
分别实现以下需求
- 将顾客表、部门表、商品表数据存入Hive
- 查询顾客表中地区为“NY”所在城市为’New York’的用户
- 查询订单表中共有多少不同顾客下过订单
- 查询商品表中前5个商品
- 从顾客表中查询每位顾客的全名(分别使用CTE和子查询)
- 使用正则表达式匹配顾客表中ID、姓名与所在城市列
- 使用关联查询获取没有订单的所有顾客
--将顾客表、部门表、商品表数据存入Hive
load data local inpath'/root/test/customers.csv'into table customers;
load data local inpath '/root/test/departments.csv' into table departments;
load data local inpath '/root/test/products.csv' into table products;
--查询顾客表中地区为“NY”所在城市为'New York'的用户
select * from customers where customer_state='NY'and customer_city='New York';
--查询订单表中共有多少不同顾客下过订单
select count(distinct order_customer_id) from orders;
--查询商品表中前5个商品
select * from products limit5;
--从顾客表中查询每位顾客的全名(分别使用CTE和子查询)
select concat(customer_fname,' ',customer_lname) from customers;
--使用正则表达式匹配顾客表中ID、姓名与所在城市列
select * from customers
where customer_id rlike '[1,2,3][4,5,6]'
and customer_fname rlike '^Ma+[a,r]'
and customer_lname rlike '^S'
and customer_state rlike '^N'
and customer_city rlike 'Green' ;
--使用关联查询获取没有订单的所有顾客
select customer_fname from customers c where c.customer_id
not in
(select order_customer_id from orders);--这条语句记住顾客表需要取别名才能查出结果!!!
注:rlike支持java的正则表达式;使用子查询需要取别名
3.Hive join-mapjoin
- Mapjoin操作在Map端:
小表关联大表,可以进行不等值连接
小表放左边,大表放右边
- 开启mapjoin端操作:
set hive.auto.convert.join=true;
运行时自动连接转换成mapjoin
-
mapjoin操作不支持:
在union all,lateral view,group by/join/sort by/cluster by/distribute by
等操作后面
在union,join以及其他mapjoin之前
4.Hive集合操作(UNION)
(1)基本概念
- 所有子集数据必须具有相同的名称和类型
UNION ALL:合并后保留重复项
UNION:合并后删除重复项(v1.2之后) - 可以在顶层查询中使用(0.13.0之后)
ORDER BY, SORT BY, CLUSTER BY, DISTRIBUTE BY 和LIMIT适用于合并后的整个结果 - 集合其他操作可以使用JOIN/OUTER JOIN来实现
差集、交集
(2)示例
- union all:
select * from customers where customer_fname='Mary'
union all
select * from customers where customer_fname='Mary';
- 差集和交集
--差集
SELECT a.name FROM employee a LEFT JOIN employee_hr b
ON a.name = b.name WHERE b.name IS NULL;
--交集
SELECT a.name FROM employee a
JOIN employee_hr b ON a.name = b.name;
二.装载数据
1.向表中装载数据(Load)
(1)语法
hive>load data [local] inpath '/opt/datas/student.txt' [overwrite] into table
student [partition (partcol1=val1,…)];
(2)关键字含义
1)load data:表示加载数据
2)local:表示从本地加载数据到 hive 表;否则从 HDFS 加载数据到 hive 表
3)inpath:表示加载数据的路径
4)into table:表示加载到哪张表
5)student:表示具体的表
6)overwrite:表示覆盖表中已有数据,否则表示追加
7)partition:表示上传到指定分区
(3)实例
student.txt 文件内容
1001 zhangsan
1002 lishi
1003 wangwu
--(1)创建一张表
hive (default)> create table student(id string, name string) row format delimited
fields terminated by '\t';
--(2)加载本地文件到 hive
hive (default)> load data local inpath '/opt/datas/student.txt' into table
student;
--(3)加载 HDFS 文件到 hive 中
-- 上传文件到 HDFS
hive (default)> dfs -put /opt/datas/student.txt /kgc/hive;
-- 加载 HDFS 上数据
hive (default)>load data inpath '/kgc/hive/student.txt' into table student;
--(4)加载数据覆盖表中已有的数据
-- 上传文件到 HDFS
hive (default)> dfs -put /opt/datas/student.txt /kgc/hive;
-- 加载数据覆盖表中已有的数据
hive (default)>load data inpath '/kgc/hive/student.txt' overwrite into table
student;
2.装载数据(insert)
(1)基本语法
INSERT OVERWRITE/INTO TABLE tablename1
[PARTITION (partcol1=val1, partcol2=val2 ...)]
select fileds,... from tb_other;
- Hive支持从同一个表进行多次插入
- INSERT INTO中TABLE关键字是可选的
- INSERT INTO可以指定插入到哪些字段中
如:INSERT INTO t(x,y,z) - INSERT INTO table_name VALUES,支持插入值列表
- 数据插入必须与指定列数相同
示例:
注意: create table ctas_partitoned as select * from employee_partitioned;
通过CTAS创建的表虽然直接select的分区表,但不是分区表
hive> desc ctas_partitoned;
OK
name string
employe_id int
number string
year int
month int
Time taken: 0.053 seconds, Fetched: 5 row(s)
(2)通过查询语句插入
insert into ctas_employee select * from employee;
(3)多插入
from ctas_employee
insert overwrite table employee select *
insert overwrite table employee_internal select *;
(4)插入到分区
from ctas_patitioned
insert into table employee_partitioned