Hive(二)--join关联查询及数据装载、交换、排序

Hive查询

• select语句
  使用方法与mysql类似

SELECT 1;
SELECT [DISTINCT] column_nam_list FROM table_name;
SELECT * FROM table_name;
SELECT * FROM employee WHERE name!='Lucy'  LIMIT 5;

• CTE（Common Table Expression）
  语法：WITH t1 AS (SELECT …) SELECT * FROM t1
• 嵌套查询

SELECT * FROM (SELECT * FROM employee) a;

join关联查询

• 内连接：inner join

• 外连接：[right、left、full]outer join

• 交叉连接：cross join
  即两张表的笛卡尔积

• 隐式连接：implicit join
  从Hive0.13.0开始支持Implicit join，允许from子句去join以逗号分隔的表，省略掉join关键字，如下：
  select * from t1, t2, t3where t1.id= t2.id and t2.id = t3.id

• mapjoin
  mapjoin适用于小表关联大表的情况，它是对hive sql的优化，mapjoin会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map是进行了join操作，省去了reduce运行的效率也会高很多。此外mapjoin可以非等值连接。在hive执行set hive.auto.convert.join = true后会自动将连接转为mapjoin

• 集合操作（UNION）
  UNION ALL：合并并保留重复项
  UNION：合并后删除重复项
  对于其他集合操作（差集、交集），可通过各种join实现

数据装载

• load移动数据

// 原始数据被移动到目标表/分区，不再存在于原始位置
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);

其中 local 关键字指代linux本地，没有local即代表hdfs路径；overwrite为覆盖
建立order_item2表，用csv文件装载数据

// 创建表
create table order_item2(
order_item_id string,
order_item_order_id string,
order_item_product_id string,
order_item_quantity string,
order_item_total_price string,
order_item_product_price string)
row format serde
'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties(
"separatorChar"=",",
"escapeChar"="\\")
// 装载数据
load data inpath '/test/order_items.csv' 
overwrite into table order_item2;

• 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');

• 表数据插入文件

// 从同一数据源插入本地文件，hdfs文件，表
from ctas_employee
insert overwrite local directory '/tmp/out1'  select * // linux本地
insert overwrite directory '/tmp/out1' select * // hdfs
insert overwrite table employee_internal select *; //表
// 以指定格式插入数据
insert overwrite directory '/tmp/out3'
row format delimited fields terminated by ','
select * from ctas_employee;

注意：文件插入只支持OVERWRITE

数据交换（import/export）

应用场景：
①常用于数据迁移场景
②除数据库，可导入导出所有数据和元数据

• export导出数据

// 以下两句中的path代指hdfs路径
export table employee to 'path'
export table employee_partitioned partition (year=2014, month=11) to 'path' 

• import导入数据

// 以下两句中的path代指hdfs路径
import table employee from 'path';
import table employee_partitioned partition (year=2014, month=11) FROM 'path';

数据排序

• order by
  只使用一个Reducer执行全局数据排序
  速度慢,应提前做好数据过滤
  支持使用CASE WHEN或表达式
  支持按位置编号排序
• sort by
  sort by对每个Reducer中的数据进行排序，能够实现局部排序。
  当Reducer数量设置为1时，等于order by
• distribute by
  distribute by类似于标准SQL中的group by，能够确保具有匹配列值的行被分区到相同的Reducer，其不会对每个Reducer的输出进行排序，并且通常使用在SORT BY语句之前

例：

// 以评估分数倒序对员工进行排序，并经同一部门的员工分到同一个reducer分区中
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

聚合运算

group by用于分组
having：对group by聚合结果的条件过滤

• 基础聚合
  即聚合函数与group by一同使用，用法与mysql类似，不过多赘述
• 高级集合
  grouping sets
  实现对同一数据集进行多重group by操作
  本质是多个group by进行union all操作