Hive高级查询（join查询，装载数据（load，insert），数据导入导出（import，export），数据排序，聚合运算，窗口函数）（三）

一.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