Hive查询之排序

一、查询语句注意事项

1、where子句中不能使用字段别名

2、like和rlike

1）使用LIKE运算选择类似的值

2）选择条件可以包含字符或数字:

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

3）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

4）案例实操

       （1）查找以2开头薪水的员工信息

hive (default)> select * from emp where sal LIKE '2%';

       （2）查找第二个数值为2的薪水的员工信息

hive (default)> select * from emp where sal LIKE '_2%';

       （3）查找薪水中含有2的员工信息

hive (default)> select * from emp where sal RLIKE '[2]';

3、支持满连接

满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno  = d.deptno;

4、★连接谓词中不支持or

hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno  = d.deptno or e.ename=d.ename;   错误的

二、排序

1、全局排序( order by)

Order By：全局排序，一个Reducer

使用 ORDER BY 子句排序:

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

ORDER BY 子句在SELECT语句的结尾

2、Sort By    每个MapReduce内部排序

Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。一般结合的是Distributed by使用

sort by 是单独在各自的reduce中进行排序，所以并不能保证全局有序，一般和distribute by 一起执行，而且distribute by 要写在sort by前面

如果mapred.reduce.tasks=1和order by效果一样，如果大于1会分成几个文件输出每个文件会按照指定的字段排序，而不保证全局有序。

举例：

1）．设置reduce个数

hive (default)> set mapreduce.job.reduces=3;

2）．查看设置reduce个数

hive (default)> set mapreduce.job.reduces;

3）．根据部门编号降序查看员工信息

会根据empno的hash值进入不同的reduce中，每个reduce然后在排序

hive (default)> select * from emp sort by empno desc;

4）．将查询结果导入到文件中（按照部门编号降序排序）

hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result'    select * from emp sort by deptno desc;

3、Distribute By分区排序（Distribute By）

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

   注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

用distribute by 会对指定的字段按照hashCode值对reduce的个数取模，然后将任务分配到对应的reduce中去执行

就是在mapreduce程序中的patition分区过程，默认根据指定key.hashCode()&Integer.MAX_VALUE%numReduce 确定处理该任务的reduce

案例：

hive (default)> set mapreduce.job.reduces=3;

hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

写入本地文件只能用overwrite，不能用into

4、Cluster By

distribute by 和 sort by 合用就相当于cluster by，但是cluster by 不能指定排序为asc或 desc 的规则，只能是desc倒序排列。

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

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

1）以下两种写法等价

hive (default)> select * from emp cluster by deptno;

hive (default)> select * from emp distribute by deptno sort by deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。