记录了实习过程中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 | 取模 |
内置函数
执行顺序
- SELECT DISTINCT<select_list>
- FROM <left_table>
- JOIN <right_table>
- ON <join_condition>
- WHERE <where_condition>
- GROUP BY <group_by_list>
- HAVING <having_condition>
- ORDER BY <order_by_list>
- LIMIT <limit_number>
注意
- 对于巨大的表,应减少查询数量,避免全量查询
- 使用子查询代替join
- 当查询速度很慢时应:
- 确认数据规模
- 确认利用数据表的分区索引功能
- 确认没有使用全量关联
- 想办法减少数据查询规模(子查询,修改关联表顺序…)
优化
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 | 数字 |