Hive数据操作
Hive数据操作是负责对数据库对象运行数据访问工作的指令集，通俗的讲它的功能就是操作数据，其中包括向数据表加载文件、写查询结果等。
在所有数据库系统中，查询语句是使用最频繁的，也是最复杂的，Hive中的Select语句与MySQL语法基本一致，且支持where、distinct、group by、order by、having、limit以及子查询等，下面是一个标准的Select语句语法格式：
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
JOIN table_other ON expr
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]]
[LIMIT number] 接下来针对上述语法格式中的关键字分别讲解。
- table_reference它可以是一张表，一个视图或者是一个子查询语句。
- where关键字作为可选参选，用于指定查询条件。
- distinct关键字用于剔除查询结果中重复的数据，如果没有定义则全部输出，默认为all；
- group by用于将查询结果按照指定字段进行分组。
- having作为可选参数，与group by关键字连用，它是将分组后的结果进行过滤；
- distribute by是根据指定字段分发到不同的reducer进行处理，且分发算法采用哈希散列，它类似MapReduce中的partition分区，通常结合sort by使用。
- sort by是在数据进入Reducer前完成排序，因此不是全局排序，如果设置mapred.reduce.tasks>1，则sort by只能保证每个reducer的输出有序，不保证全局有序。
- cluster by是一个分桶查询语句，根据指定的字段进行分桶，分桶数取决于设置reduces个数，并且分桶后，每桶数据都会进行排序；通俗的讲，如果distribute和sort的字段是同一个时，此时可以理解为distribute by+sort by= cluster by。
- order by用于将查询结果按照指定字段进行全局排序，因此输出文件只有一个，且只存在一个Reducer，那么当数据量很大时，就需要较长的计算时间。
在Hive中，HQL是不区分大小写的，但是关键字不能被缩写，也不能被分行，下面演示相关查询案例，首先准备数据dept.txt和emp.txt如文件1和文件2所示：
文件1 emp.txt
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10文件2 dept.txt
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700并根据两个结构化数据文件创建对应表结构，代码如下所示：
创建emp表
hive> create table emp(empno int,ename string,job string,mgr int,
hiredate string, sal double, comm double,deptno int)
row format delimited fields terminated by '\t';创建dept表
hive> create table dept(deptno int,dname string,loc int)
row format delimited fields terminated by '\t';创建表完成后，将数据文件移动到对应的表所在HDFS路径下，完成数据映射。
例1：基本查询
（1）全表查询：
hive> select * from emp;（2）选择特定字段查询：
hive> select deptno,dname from dept;（3）查询员工表总人数：
hive> select count(*) cnt from emp;（4）查询员工表总工资额：
hive> select sum(sal) sum_sal from emp;（5）查询5条员工表的信息
hive> select * from emp limit 5;例2：Where条件查询
（1）查询出薪水等于5000的所有员工
hive> select * from emp where sal =5000;（2）查询工资在500到1000的员工信息
hive> select * from emp where sal between 500 and 1000;（3）查询comm为空的所有员工信息
hive> select * from emp where comm is null;（4）查询工资是1500和5000的员工信息
hive> select * from emp where sal IN (1500, 5000);例3：Like和Rlike
RLike子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式来指定匹配条件。
（1）查找以2开头薪水的员工信息
hive> select * from emp where sal LIKE '2%';（2）查找第二个数值为2的薪水的员工信息
hive> select * from emp where sal LIKE '_2%';（3）查找薪水中含有2的员工信息
hive> select * from emp where sal RLIKE '[2]';例4：Group By语句
Group by语句通常会和聚合函数一起使用，按照一个或者多个列的结果进行分组，然后对每个组执行聚合操作。
（1）计算emp表每个部门的平均工资
hive> select t.deptno,avg(t.sal) avg_sal
from emp t group by t.deptno;（2）计算emp每个部门中每个岗位的最高薪水
hive> select t.deptno,t.job,max(t.sal) max_sal
from emp t group by t.deptno, t.job;例5：Having语句
Having和Where语句虽然都是根据条件进行筛选过滤，但是它们之间有许多不同之处。
- Where针对表中的列进行条件过滤，查询数据，Having针对查询结果中的列进行条件过滤，筛选数据；
- Where后面不能写分组函数，而Having后面可以使用分组函数。
- Having只用于Group by分组统计语句。
（1）求每个部门的平均工资
hive> select deptno, avg(sal) from emp group by deptno;（2）求每个部门的平均薪水大于2000的部门
hive> select deptno, avg(sal) avg_sal
from emp group by deptno having avg_sal > 2000;例6：Order by语句
Order by默认为升序（ASC），降序为（DESC）。
（1）查询员工信息按工资降序排列
hive> select * from emp order by sal desc;（2）按照部门和工资升序排序
hive> select ename, deptno, sal from emp order by deptno, sal;例7：Sort by语句
（1）设置reduce个数
hive> set mapreduce.job.reduces=3;（2）查看设置reduce个数
hive> set mapreduce.job.reduces;（3）根据部门编号降序查看员工信息
hive> select * from emp sort by empno desc;（4）将查询结果导入到文件中（按照部门编号降序排序）
hive> insert overwrite local directory '/root/sortby-result'
select * from emp sort by deptno desc;例8：Distribute By
Distribute By通常与Sort By结合使用，但是要注意Distribute By语句要写在Sort By语句之前，对于Distribute By进行测试，一定要分配多个Reduce进行处理，否则无法看到Distribute By的效果。
（1）先按照部门编号分区，再按照员工编号降序排序。
hive> set mapreduce.job.reduces=3;
hive> insert overwrite local directory '/root/distribute-result'
select * from emp distribute by deptno sort by empno desc;例9：Cluster By
Cluster by除了具有Distribute By的功能外还兼具Sort By的功能，但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。
以下两种写法等价
hive> select * from emp cluster by deptno;
hive> select * from emp distribute by deptno sort by deptno;例10：Join操作
在当前版本中，Hive只支持等值连接，因为非等值连接难以转化为MapReduce任务。
（1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门编号；
hive> select e.empno, e.ename, d.deptno, d.dname
from emp e join dept d on e.deptno = d.deptno;左外连接：Join操作符左边表中符合条件的所有记录将会被返回。
hive> select e.empno, e.ename, d.deptno
from emp e left join dept d on e.deptno = d.deptno;右外连接：Join操作符右边表中符合条件的所有记录将会被返回。
hive> select e.empno, e.ename, d.deptno
from emp e right join dept d on e.deptno = d.deptno;满外连接：返回所有表中符合条件的所有记录，如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
hive> select e.empno, e.ename, d.deptno
from emp e full join dept d on e.deptno = d.deptno;在使用Join语句时，如果想限制输出结果，可以在Join语句后面添加Where语句，进行过滤。
hive> select e.empno, e.ename, d.deptno
from emp e full join dept d on e.deptno = d.deptno where d.deptno=20;
隐藏目录
