0%

查询数据

查询数据

hive查询语法除了某些hive本身的一些特性之外,和sql语法大致相同,与sql大致相同的大体过一下,主要说明一下hive特性的一些查询

基本语法

1
2
3
4
5
6
7
8
9
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]

hive支持全表扫描、查询特定列、带条件扫描以及别名设置

1
2
3
4
5
6
7
8
-- 全表扫描
select * from `dept`;
-- 查询特定列
select deptno from `dept`;
-- 带条件扫描
select * from `dept` where deptno = 10;
-- 别名设置
select deptno as no from `dept`;

支持limit

1
select * from emp limit 2,2;

支持范围查询

1
2
3
4
-- between  and
select * from emp where sal between 20000 and 50000;
-- in
select * from emp where sal IN (1500, 5000);

支持查询null值

1
select * from emp where sal is null;

支持多个条件组合

1
select * from emp where job = SALESMAN and sal > 1000;

支持模糊查询

1
2
3
-- % 代表零个或多个字符(任意个字符)
-- _ 代表一个字符
select * from emp where ename LIKE 'A%';

支持分组以及聚合

1
2
3
select deptno, avg(sal) avg_sal from emp group by deptno;

select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

支持join连接

1
2
3
4
5
6
7
8
-- 内连接
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
-- 左外连接
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
-- 右外连接
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
-- 全外连接
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

支持排序

在sql中有order by,hive中也支持order by,但是由于hive的数据存储为hdfs,底层其实是将sql语句转换为MapReduce程序,使用order by时只会使用一个reducer进行全局排序

1
2
3
4
select * from emp order by sal;

-- 根据日志可以看到使用的reducer只有一个
-- Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1

而为了可以高效的进行排序,hive又有一个不同于sql的排序方式sort by,为每一个reducer产生一个排序文件,每个reducer进行内部排序,但是这种方式对于全局来说并不是全局有序的

1
select * from emp sort by sal desc;

支持分区

在使用多个reducer的时候,默认情况下是随机的,如上述排序使用sort by,数据时随机分给reducer的,导致数据没有什么实际意义,hive提供了distribute by来进行分区,然后再结合sort by进行使用,就可以得到有意义的一些数据

1
2
3
-- 根据部门进行分区,相同的部门分到同一个reducer中执行,就可以得到部门中的薪资排序
-- 这里的distribute by一定要在sort by之前
select deptno,ename,sal from emp distribute by deptno sort by sal desc;

distribute by 的分区规则是根据分区字段的 hash 值与 reduce 的个数进行模除后,余数相同的分到一个区

如果分区字段和排序字段相同的话,可以使用cluster by

1
2
3
-- 下列两种方式是等价的
select deptno,ename,sal from emp distribute by deptno sort by deptno;
select deptno,ename,sal from emp cluster by deptno;