记录了实习过程中Hive SQL常用方法

工具

Hive Sql

特点：

• 通过类 SQL 来分析大数据，而避免了写 MapReduce Java 程序来分析
  数据，这样使得分析数据更容易。
• Hive 本身并不提供数据的存储功能
• Hive 是将数据映射成数据库和一张张的表，库和表的元数据信息一般存在关系型数据库上
• 能够存储很大的数据集，并且对数据完整性、格式要求并不严格。
• 不适用于实时计算和响应，使用于离线分析

常用Hive语法

查看数据库中的表

show tables in <db> 

查看表结构

-- 显示col_name和data_type
desc <db.table> 

-- 显示10条记录
select * from <db.table> limit 10

查找select

select <col_name1, col_name2, ...>  -- 需要查找的列名
from <db.table>                     -- 从哪个表
where <condition>                   -- 筛选条件
limit <num>                         -- 限制返回的条数

注： Hive只允许在select from后出现子查询

-- 支持
select * 
from 
(
    select * from <table> where <condition>
)

-- 不支持
select
(
    select * from <table1> where <condition>
)
from <table2>

分组group by

select <col_name1, col_name2, ...>  
from <db.table>                     
where <condition>
group by <col_name1, col_name2, ...> -- 按照列名分组                   
limit <num>                         

having

注：

• having 语句内可以使用聚合函数，where 子句中不能
• having 语句在group by语句之后；SQL会在分组之后计算having语句。where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使where条件显示特定的行。
• hive在group by xx having count()是不支持去重计数的。

join

注1： Hive 只支持等值连接

-- 支持
select <A.col_name1,...,B.col_name1,...>
from A
left join B
on A.id = B.id

-- 不支持
select <A.col_name1,...,B.col_name1,...>
from A
left join B
on A.id != B.id

注2： on 后面的表达式不支持 or

-- 支持
select <A.col_name1,...,B.col_name1,...>
from A
left join B
on A.id = B.id and A.date = B.date

-- 不支持
select <A.col_name1,...,B.col_name1,...>
from A
left join B
on A.id != B.id or A.date = B.date

left join——A (AB)

select <col_name1, col_name2, ...>  
from <table_A> A                    
left join <table_B> B
on A.key = B.key                       

right join——B (AB)

select <col_name1, col_name2, ...>  
from <table_A> A                    
right join <table_B> B
on A.key = B.key                       

inner join—— A B

select <col_name1, col_name2, ...>  
from <table_A> A                    
inner join <table_B> B
on A.key = B.key                       

full join——A B

select <col_name1, col_name2, ...>  
from <table_A> A                    
full join <table_B> B
on A.key = B.key                       

排序

order by

对查询结果进行全局排序，但是如果结果数据量大，那就会造成 Reduce 执行相当漫长。

sort by

在每个 reduce 中进行排序，是一个局部排序，但是全局上不一定是排好序的。

操作符

操作符	说明
A=B	A 等于 B 就返回 true，适用于各种基本类型
A<=>B	都为NULL返回true,其他同=
A<>B 或者 A!=B	不等于
A (not) between B and C	筛选 A 的值(不)处于 B 和 C 之间
A is (not) NULL	A (不) 是NULL

算术运算

运算符	描述
A+B	相加
A-B	相减
A/B	相除
A*B	相乘
A%B	取模

内置函数

• 官方文档
• 中文文档

执行顺序

1. SELECT DISTINCT<select_list>
2. FROM <left_table>
3. JOIN <right_table>
4. ON <join_condition>
5. WHERE <where_condition>
6. GROUP BY <group_by_list>
7. HAVING <having_condition>
8. ORDER BY <order_by_list>
9. LIMIT <limit_number>

注意

1. 对于巨大的表，应减少查询数量，避免全量查询
2. 使用子查询代替join
3. 当查询速度很慢时应：
   • 确认数据规模
   • 确认利用数据表的分区索引功能
   • 确认没有使用全量关联
   • 想办法减少数据查询规模(子查询,修改关联表顺序…)

优化

join优化

• 过滤后再join，减少参与join的数量
• 用子查询代替join
• 小表join大表

order by优化

• 再最终结果上进行order by
• 如果需求是取排序后前 N 条数据，那么可以使用 distribute by 和 sort by 在各个 reduce 上进行排序后取前 N 条，然后再对各个 reduce 的结果集合并后在一个 reduce中全局排序，再取前 N 条，因为参与全局排序的Order By 的数据量最多有 reduce 个数*N，所以速度很快

附

缩写	含义
db	数据库名称
table	表名称
col_name	列名称
condition	条件
num	数字