地表最强系列之Hive的高级查询语句

SELECT基础

• SELECT用于映射符合指定查询条件的行
• Hive SELECT是数据库标准SQL的子集
  1.使用方法类似于MySQL
  2.关键字和MySQL一样，不区分大小写
  3.limit子句
  4.where子句
  5.运算符、like、rlike
  6.group by子句
  7.having子句

Hive的语句的书写顺序

(1)select
(2)from
(3)join on
(4) where
(5)group by
(6)having
(7)distribute by/cluster by
(8) sort by
(9) order by
(10) limit
(11) union(去重不排序)/union all（不去重不排序）

• 演示示例

SELECT 1;
SELECT * FROM table_name;
SELECT id,name,age FROM people WHERE age>20;
SELECT * FROM employee WHERE name!='Lucy'  LIMIT 5;
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
select deptno, avg(sal) avg_sal from emp group by deptno having 
avg_sal > 2000;

语句的执行顺序

(1)from
(2)on
(3)join
(4)where
(5)group by
(6)having
(7)select
(8)distinct
(9)distribute by /cluster by
(10)sort by
(11) order by
(12) limit
(13) union /union all

CTE和嵌套查询

• CTE

-- CTE语法
WITH t1 AS (SELECT …) SELECT * FROM t1;
-- CTE演示
with tab1 as (select id,name,age from people) 
select * from tab1;

• 嵌套查询

SELECT * FROM (SELECT * FROM employee) a;

关联查询

• 内连接:展示的是两个表的公共部分

select * from student s join score sc on s.stu_id=s.stu_id;

• 外连接:
  外连接分为左连接和右连接和全连接
• 左连接:是以左边的表为主,拿右边的表和它连接.左表是不会有改动的
  2.右连接:是以右边的表为主,拿左边的表和它连接.右表是不会有改动的
  3.全连接:全连接分为union和union all
  使用union 时，完全相等的行，将会被合并，union all 不会将相等的行进行合并;
• 交叉连接

select * from table1 ,table2;

使用这种连接会出现笛卡尔积,建议不要使用;

• 隐式连接

Mapjoin

• 使用Mapjoin的场景:
  很小的一个表和一个大表进行join.小表的大小可以通过hive.mapjoin.smalltable.filesize来决定,该参数表示小表的总大小,默认值为25M.
• Mapjoin的优势:
  使用Map中的Combiner进行合并,省去的Shuffle和Reduce的过程,大大的提高了性能
• Mapjoin的过程:
  将小表放入缓存中,在客户端本地执行的Task中读取小表的数据,将其转换成一个HashTable的数据结构,并写入本地的文件中，之后将该文件加载到DistributeCache(分布式文件缓存)中，DistributeCache会将小表分发到各个节点上,然后启动MapTask扫描大表的数据,在Map阶段，根据DistributeCache中小表对应的HashTable的每一条记录去和大表的每一条记录关联，并直接输出结果。
  因为没有reduce使用输出的结果的个数和MapTask的个数有关(PS:MapTask的个数和逻辑分片的个数有关)
• MAPJOIN不支持的操作：
  在UNION ALL, LATERAL VIEW, GROUP BY/JOIN/SORT BY/CLUSTER BY/DISTRIBUTE BY等操作后面
  在UNION, JOIN 以及其他 MAPJOIN之前

装载数据

INSERT OVERWRITE TABLE test select ‘hello’; – 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’);

Hive数据插入文件

使用insert语句将数据插入/导出到文件
文件插入只支持OVERWRITE
支持来自同一个数据源/表的多次插入
LOCAL：写入本地文件系统
默认数据以TEXT格式写入，列由^A分隔
支持自定义分隔符导出文件为不同格式,CSV,JSON等

-- 从同一数据源插入本地文件，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的应用场景
  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';

Hive的数据排序

• Order by
  ORDER BY (ASC|DESC)类似于标准SQL
  1.只使用一个Reducer执行全局数据排序
  2.速度慢,应提前做好数据过滤
  3.支持使用CASE WHEN或表达式
  4.支持按位置编号排序
• sort by
  sort by 通常和DISTRIBUTE BY 两个联合使用,distribute by 是用来分区的,因为sort by可以使用多个reduce 来进行并行排序,这样使用distribute 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.当distribute by和sort by的字段相同时，可以使用cluster by代替。

select userid, age, salary
  from database.table
 cluster by age

上面代码中，意思是按照将数据按照age指定到同一个reducer中后，再在reducer内按照age进行排序。但是有一个限制就是，只能是生序排序，不能指定asc或者desc的排序方式。