01
Hive的查询
1.基本查询
1）全表查询
代码语言：javascript复制hive (default)> select * from emp;2）选择特定列查询
代码语言：javascript复制hive (default)> select empno, ename from emp;注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。
3）列别名
（1）查询名称和部门
代码语言：javascript复制hive (default)> select ename AS name, deptno dn from emp;4）算数运算符
查询出所有员工的薪水后加 1 显示。
代码语言：javascript复制hive (default)> select sal 1 from emp;5）常用函数
求总行数（count）
代码语言：javascript复制select count(*) cnt from emp; 求工资的最大值（max）
代码语言：javascript复制select max(sal) max_sal from emp; 求工资的最小值（min）
代码语言：javascript复制select min(sal) min_sal from emp; 求工资的总和（sum）
代码语言：javascript复制select sum(sal) sum_sal from emp; 求工资的平均值（avg）
代码语言：javascript复制select avg(sal) avg_sal from emp;6）Limit 语句
典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。
代码语言：javascript复制select * from emp limit 5;2.Where语句
（1）使用 WHERE 子句，将不满足条件的行过滤掉。
（2）WHERE 子句紧随 FROM 子句。
（3）实例
查询出薪水大于 1000 的所有员工
代码语言：javascript复制hive (default)> select * from emp where sal >1000;1）比较运算符(Between/In/ Is Null)
这些操作符同样可以用于 JOIN…ON 和 HAVING 语句中。
2）举几个例子：
查询 comm 为空的所有员工信息
代码语言：javascript复制hive (default)> select * from emp where comm is null;查询工资是 1500 和 5000 的员工信息
代码语言：javascript复制hive (default)> select * from emp where sal IN (1500, 5000);3）Like 和 RLike
使用 LIKE 运算选择类似的值
选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
4）实例：
（1）查找以 2 开头薪水的员工信息
代码语言：javascript复制hive (default)> select * from emp where sal LIKE '2%';（2）查找第二个数值为 2 的薪水的员工信息
代码语言：javascript复制hive (default)> select * from emp where sal LIKE '_2%';（3）查找薪水中含有 2 的员工信息
代码语言：javascript复制hive (default)> select * from emp where sal RLIKE '[2]';4）逻辑运算符
查询薪水大于 1000，或者部门是 30
代码语言：javascript复制hive (default)> select * from emp where sal>1000 or deptno=30;查询除了 20 部门和 30 部门以外的员工信息
代码语言：javascript复制hive (default)> select * from emp where deptno not IN(30, 20);3.分组
1）Group By 语句
GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
2）having 与 where 不同点
（1）where 针对表中的列发挥作用，查询数据；having 针对查询结果中的列发挥作用，筛选数据。
（2）where 后面不能写分组函数，而 having 后面可以使用分组函数。
（3）having 只用于 group by 分组统计语句。
求每个部门的平均工资
代码语言：javascript复制hive (default)> select deptno, avg(sal) from emp group by deptno;求每个部门的平均薪水大于 2000 的部门
代码语言：javascript复制hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;4.Join语句（join看1001次重燃之前的文章）
1）内连接
2）左外连接
3）右外连接
4）满外连接
5）多表连接
代码语言：javascript复制hive (default)>
SELECT e.ename, d.deptno, l. loc_name
FROM emp e
JOIN dept d
ON d.deptno = e.deptno
JOIN location l
ON d.loc = l.loc;大多数情况下，Hive 会对每对 JOIN 连接对象启动一个 MapReduce 任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表 l;进行连接操作。
注意：为什么不是表 d 和表 l 先进行连接操作呢？这是因为 Hive 总是按照从左到右的顺序执行的。
6）笛卡尔积
笛卡尔集会在下面条件下产生:
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
7）连接谓词中不支持 or
代码语言：javascript复制select e.empno, e.ename, d.deptno
from emp e join dept d on e.deptno = d.deptno ore.ename=d.ename;
错误的5.排序
1）全局排序（Order By）
Order By：全局排序，一个 MapReduce
（1）使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
（2）ORDER BY 子句在 SELECT 语句的结尾。
2）按照别名排序
按照员工薪水的 2 倍排序
代码语言：javascript复制select ename, sal*2 twosal from emp order by twosal;3）多个列排序
按照部门和工资升序排序
代码语言：javascript复制select ename, deptno, sal from emp order by deptno, sal ;4）每个 MapReduce 内部排序（Sort By）
Sort By：每个 MapReduce 内部进行排序，对全局结果集来说不是排序。
（1）设置 reduce 个数
代码语言：javascript复制set mapreduce.job.reduces=3;（2）查看设置 reduce 个数
代码语言：javascript复制set mapreduce.job.reduces; （3）根据部门编号降序查看员工信息
代码语言：javascript复制select * from emp sort by empno desc;（4）将查询结果导入到文件中（按照部门编号降序排序）
代码语言：javascript复制insert overwrite local directory '/opt/module/datas/sortby-result'
select * from empsort by deptno desc;5）分区排序（Distribute By）
Distribute By：类似 MR 中 partition，进行分区，结合 sort by 使用。
注意，Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。
（1）先按照部门编号分区，再按照员工编号降序排序。
代码语言：javascript复制set mapreduce.job.reduces=3;insert overwrite local directory '/opt/module/datas/distribute-result'
select * from empdistribute by deptno sort by empno desc;6）Cluster By
当 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 号部门分到一个分区里面去。
6.分桶及抽样查询
1）分桶表数据存储
分区针对的是数据的存储路径；分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
（1）先创建分桶表，通过直接导入数据文件的方式
创建分桶表
代码语言：javascript复制create table stu_buck(id int, name string)clustered by(id)into 4 bucketsrow format delimited fields terminated by 't';查看表结构
代码语言：javascript复制desc formatted stu_buck;Num Buckets: 4导入数据到分桶表中
代码语言：javascript复制load data local inpath '/opt/module/datas/student.txt' into table stu_buck;查看创建的分桶表中是否分成 4 个桶
发现并没有分成 4 个桶。是什么原因呢？
（2）创建分桶表时，数据通过子查询的方式导入
先建一个普通的 stu 表
代码语言：javascript复制create table stu(id int, name string)row format delimited fields terminated by 't';向普通的 stu 表中导入数据
代码语言：javascript复制load data local inpath '/opt/module/datas/student.txt' into table stu;清空 stu_buck 表中数据
代码语言：javascript复制truncate table stu_buck;select * from stu_buck;导入数据到分桶表，通过子查询的方式
代码语言：javascript复制insert into table stu_buckselect id, name from stu;发现还是只有一个分桶
需要设置一个属性
代码语言：javascript复制hive (default)> set hive.enforce.bucketing=true;
hive (default)> set mapreduce.job.reduces=-1;
hive (default)> insert into table stu_buckselect id, name from stu;
查询分桶的数据
代码语言：javascript复制hive (default)> select * from stu_buck;2）分桶抽样查询
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。
查询表 stu_buck 中的数据。
代码语言：javascript复制select * from stu_buck tablesample(bucket 1 out of 4 on id);注：tablesample 是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
y 必须是 table 总 bucket 数的倍数或者因子。hive 根据 y 的大小，决定抽样的比例。例如，table总共分了 4 份，当 y=2 时，抽取(4/2=)2 个 bucket 的数据，当 y=8 时，抽取(4/8=)1/2 个 bucket 的数据。
x 表示从哪个 bucket 开始抽取。例如，table 总 bucket 数为 4，tablesample(bucket 4 out of 4)，表示总共抽取（4/4=）1 个 bucket 的数据，抽取第 4 个 bucket 的数据。
注意：x 的值必须小于等于 y 的值
3）数据块抽样
Hive 提供了另外一种按照百分比进行抽样的方式，这种是基于行数的，按照输入路径下的数据块百分比进行的抽样。
hive (default)> select * from stu tablesample(0.1 percent) ;
提示：这种抽样方式不一定适用于所有的文件格式。另外，这种抽样的最小抽样单元是一个 HDFS数据块。因此，如果表的数据大小小于普通的块大小 128M 的话，那么将会返回所有行。
02
Hive的函数
1.系统自带的函数
代码语言：javascript复制1）查看系统自带的函数
hive> show functions;
2）显示自带的函数的用法
hive> desc function upper;
3）详细显示自带的函数的用法
hive> desc function extended upper;2.自定义函数
1）Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF 来方便的扩展。
2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
3）根据用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）一进一出
（2）UDAF 聚集函数，多进一出类似于：count/max/min
（3）UDTF 一进多出如 lateral view explore()
4）注意事项
（1）UDF 必须要有返回类型，可以返回 null，但是返回类型不能为 void；
5）编程步骤
（1）继承 org.apache.hadoop.hive.ql.UDF
（2）需要实现 evaluate 函数；evaluate 函数支持重载；
（3）在 hive 的命令行窗口创建函数
a）添加 jaradd jar linux_jar_path
b）创建 function，
create [temporary] function [dbname.]function_name AS class_name;
（4）在 hive 的命令行窗口删除函数
Drop [temporary] function [if exists] [dbname.]function_name;
3.自定义UDF函数实例
1）创建一个 java 工程，并创建一个 lib 文件夹
2）将 hive 的 jar 包解压后，将 apache-hive-1.2.1-binlib 文件下的 jar 包都拷贝到 java 工程中。
3）创建一个类
代码语言：javascript复制package com.doit.hive;
import org.apache.hadoop.hive.ql.exec.UDF;
public class Lower extends UDF {
public String evaluate (final String s) {
if (s == null) {
return null;
}
return s.toString().toLowerCase();
}
}4）打成 jar 包上传到服务器/opt/module/jars/udf.jar
5）将 jar 包添加到 hive 的 classpath
代码语言：javascript复制add jar /opt/module/datas/udf.jar; 6）创建临时函数与开发好的 java class 关联
代码语言：javascript复制create temporary function udf_lower as "com.doit.hive.Lower"; 7）即可在 hql 中使用自定义的函数 strip
代码语言：javascript复制select ename, udf_lower(ename) lowername from emp;
