查询数据

查询数据

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

基本语法

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支持全表扫描、查询特定列、带条件扫描以及别名设置

-- 全表扫描
select * from `dept`;
-- 查询特定列
select deptno from `dept`;
-- 带条件扫描
select * from `dept` where deptno = 10;
-- 别名设置
select deptno as no from `dept`;

支持limit

select * from emp limit 2,2;

支持范围查询

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

支持查询null值

select * from emp where sal is null;

支持多个条件组合

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

支持模糊查询

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

支持分组以及聚合

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连接

-- 内连接
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进行全局排序

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进行内部排序，但是这种方式对于全局来说并不是全局有序的

select * from emp sort by sal desc;

支持分区

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

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

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

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

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