查询

查询语法：

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

基本查询

演示用表：

即 oracle 数据库自带的 scott 演示表

  1. create table if not exists dept(
  2.     deptno int,
  3.     dname string,
  4.     loc string
  5. )
  6. row format delimited 
  7. fields terminated by ',';
  9. create table if not exists emp(
  10.     empno int,
  11.     ename string,
  12.     job string,
  13.     mgr int,
  14.     hiredate string,
  15.     sal double,
  16.     comm double,
  17.     deptno int
  18. )
  19. row format delimited
  20. fields terminated by ',';

emp表数据：

  1. 7369,SMITH,CLERK,7902,1980-12-17,800,,20
  2. 7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
  3. 7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
  4. 7566,JONES,MANAGER,7839,1981-04-02,2975,,20
  5. 7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
  6. 7698,BLAKE,MANAGER,7839,1981-05-01,2850,,30
  7. 7782,CLARK,MANAGER,7839,1981-06-09,2450,,10
  8. 7788,SCOTT,ANALYST,7566,1987-04-19,3000,,20
  9. 7839,KING,PRESIDENT,,1981-11-17,5000,,10
  10. 7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
  11. 7876,ADAMS,CLERK,7788,1987-05-23,1100,,20
  12. 7900,JAMES,CLERK,7698,1981-12-03,950,,30
  13. 7902,FORD,ANALYST,7566,1981-12-03,3000,,20
  14. 7934,MILLER,CLERK,7782,1982-01-23,1300,,10

dept表数据：

  1. 10,ACCOUNTING,NEW YORK
  2. 20,RESEARCH,DALLAS
  3. 30,SALES,CHICAGO
  4. 40,OPERATIONS,BOSTON

载入数据：

  1. load data local inpath '/home/tengyer/data/dept.txt' into table dept;
  2. load data local inpath '/home/tengyer/data/emp.txt' into table emp;

基本查询语法：

大小写不敏感

  1. -- 同SQL的普通查询
  2. select * from emp;
  3. select empno,ename from emp;
  5. -- 指定别名（同SQL），可以使用as，也可以不写
  6. select empno a, ename as b from emp;
  8. -- 计算
  9. select sal + 1 from emp;

算术运算符：

运算符 描述
A+B A和B相加
A-B A和B相减
A*B A和B相乘
A/B A除以B
A%B A对B取余
A&B A和B按位与
A|B A和B按位或
A^B A和B按位异或
~A 按位反

常用函数

类似SQL：

  • count：求总行数
  • max：取最大值
  • min：求最小值
  • sum：求和
  • avg：求平均值

    limit语句

典型的查询会返回多行数据，可以使用 limit 限制返回的行数：

  1. -- 返回5行数据
  2. select * from emp limit 5;
  4. -- 从第3行开始，返回3行数据
  5. select * from emp limit 2,3;

where语句

类似SQL中的where：

  1. select * from emp where sal >1000;

操作符：

操作符 支持的数据类型 描述
A=B 基本数据类型 A等于B则返回TRUE；否则返回FALSE
A<=>B 基本数据类型 A和B同时为Null，则返回TRUE；否则返回FALSE
A<>B
、A!=B		 基本数据类型 A或者B为Null则返回NULL；
A不等于B返回TRUE，否则返回FALSE
A<B 基本数据类型 A或者B为NULL，则返回NULL；
如果A小于B，则返回TRUE，反之返回FALSE
A<=B 基本数据类型 A或者B为Null，则返回NULL；
如果A小于等于B，则返回TRUE，反之返回FALSE
A>B 基本数据类型 A或者B为Null，则返回NULL；
如果A大于B，则返回TRUE，反之返回FALSE
A>=B 基本数据类型 A或者B为Null，则返回NULL;
如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] between B AND C 基本数据类型 如果A，B或者C任一为NULL，则结果为NULL。
如果A的值大于等于B，而且小于等于C，则结果为TRUE，反之为FALSE
A IS NULL 所有数据类型 如果A等于NULL则返回TRUE，否则返回FALSE
A IS NOT NULL 所有数据类型 如果A不为NULL则返回TRUE，否则返回FALSE
IN(数值1， 数值2) 所有数据类型 使用IN
运算显示列表中的值
A [NOT] LIKE B STRING类型 B是一个SQL下的简单正则表达式（也叫通配符模式），如果A与其匹配的话，则返回TRUE；反之返回FALSE。
表达式类似SQL的表达式：xxx%
、%xxx
、%xxx%
、xxx_
A RLIKE B
, A REGEXP B		 STRING类型 B是基于java的正则表达式，如果A与其匹配，则返回TRUE，反之返回FALSE。
匹配使用的是 jdk 中的正则表达式接口实现的，因为正则也依据其中的规则。

Group By分组

类似SQL中的 group by、having语句：

  1. select deptno,avg(sal) 
  2. from emp 
  3. group by deptno
  4. having avg(sal) > 2000;

Join语句

支持通常的SQL的join语句：

  1. -- 内连接：join / inner join
  2. select e.ename,d.dname 
  3. from emp e
  4. join dept d on e.deptno=d.deptno;
  6. -- from多个表进行内连接
  7. select d.deptno, d.dname, e.ename 
  8. from emp e,dept d 
  9. where e.deptno=d.deptno
  11. -- 左外连接：left join / left outer join
  12. select d.deptno, d.dname, e.ename
  13. from dept d
  14. left join emp e on  e.deptno=d.deptno;
  16. -- 右外连接：right join / right outer join
  17. select e.ename,d.dname ,d.deptno
  18. from emp e
  19. right join dept d on e.deptno=d.deptno;
  21. -- 满连接：full join / full outer join
  22. select e.ename,d.dname ,d.deptno
  23. from emp e
  24. full join dept d on e.deptno=d.deptno;

Hive 也支持多表连接：Hive对每对JOIN连接的对象启动一个MapReduce任务，先启动一个MapReduce Job 对表1和表2进行连接，然后再启动一个MapReduce Job 将第一个MapReduce job 的输出和表3进行连接。

特别的：当多个表进行join连接时，如果每个 on 子句都使用相同的连接键，那么只会产生一个 MapReduce job。

Order By排序

类似SQL的 order by语句。

Order by是全局排序，只有一个Reducer。即使在MR中设置了多个Reducer，最后也只有一个Reducer。

hive设置Reducer个数：set mapreduce.job.reduces=需要设置的个数;

排序规则：

  • ASC：升序（默认）
  • DESC：降序
    1. select *from emp order by sal;

因为Order By只有一个Reducer，所以数据量比较大时，MR任务会失败，所以一般不会直接写order by语句。

Sort By 排序

对于大规模的数据集，order by的效率非常低，在很多情况下，并不需要全局排序，此时可以使用 sort by。

sort by 为每个 Reducer 产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。

设置Reducer个数：

  1. -- 设置3个Reducer
  2. set mapreduce.job.reduces=3;

根据部门编号降序查看员工信息：

  1. select * from emp sort by deptno desc;

将查询结果导入文件（按部门编号降序排序）：

  1. insert overwrite local directory '/home/tengyer/data/sortresult'
  2. row format delimited
  3. fields terminated by ','
  4. select *from emp sort by deptno desc;

因为设置了3个Reducer，所以最后会生成3个结果文件。

在这3个结果文件内部，deptno是按降序排序的。但是对于全局，这3个结果文件是无序的。

这3个结果文件内的数据的分配默认是随机的。

Distribute by 分区

有些情况下，我们需要控制某个特定行应该到哪个Reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。

distribute by类似 MR 中partition（自定义分区），进行分区，结合 sort by使用。

distribute by语句位置在sort by语句之前。

示例：
先按照部门编号进行分区，然后按照员工编号进行降序排序。即：只关心部门内的员工号有序，无需使用order by进行全局排序

  1. -- 设置3个Reducer。distribute by 必须设置多个Reducer才能看到效果
  2. set mapreduce.job.reduces=3;
  4. -- 使用deptno进行分区，分区内使用sort by对empno进行降序排序
  5. insert overwrite local directory '/home/tengyer/data/distributeresult'
  6. row format delimited fields terminated by ','
  7. select * from emp
  8. distribute by deptno
  9. sort by empno desc;

distribute by的分区规则是根据分区字段的hash码和reduce的个数进行取模，余数相同的分到同一个分区。

所以如果两个deptno的hash对reducer个数取的余数是相同的，那么这两个deptno会跑到同一个文件中。

Cluster by

当 distribute by 和 sort by字段相同时，可以使用 cluster by方式。

cluster by除了具有 distribute by的功能外，还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

以下两条SQL等价：

  1. -- 使用cluster by
  2. select * from emp
  3. cluster by deptno;
  5. -- 使用distribute by + sort by
  6. select * from emp
  7. distribute by deptno
  8. sort by deptno;