拿美团offer，HIve基础篇(补)

01

Hive的查询

1.基本查询

1）全表查询

hive (default)> select * from emp;

2）选择特定列查询

hive (default)> select empno, ename from emp;

注意：

（1）SQL 语言大小写不敏感。

（2）SQL 可以写在一行或者多行

（3）关键字不能被缩写也不能分行

（4）各子句一般要分行写。

（5）使用缩进提高语句的可读性。

3）列别名

（1）查询名称和部门

hive (default)> select ename AS name, deptno dn from emp;

4）算数运算符

查询出所有员工的薪水后加 1 显示。

hive (default)> select sal +1 from emp;

5）常用函数

求总行数（count）

select count(*) cnt from emp; 

求工资的最大值（max）

select max(sal) max_sal from emp; 

求工资的最小值（min）

select min(sal) min_sal from emp; 

求工资的总和（sum）

select sum(sal) sum_sal from emp; 

求工资的平均值（avg）

select avg(sal) avg_sal from emp;

6）Limit 语句

典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数。

select * from emp limit 5;

2.Where语句

（1）使用 WHERE 子句，将不满足条件的行过滤掉。

（2）WHERE 子句紧随 FROM 子句。

（3）实例

查询出薪水大于 1000 的所有员工

hive (default)> select * from emp where sal >1000;

1）比较运算符(Between/In/ Is Null)

这些操作符同样可以用于 JOIN…ON 和 HAVING 语句中。

2）举几个例子：

查询 comm 为空的所有员工信息

hive (default)> select * from emp where comm is null;

查询工资是 1500 和 5000 的员工信息

hive (default)> select * from emp where sal IN (1500, 5000);

3）Like 和 RLike

使用 LIKE 运算选择类似的值

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

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

_ 代表一个字符。

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]';

4）逻辑运算符

查询薪水大于 1000，或者部门是 30

hive (default)> select * from emp where sal>1000 or deptno=30;

查询除了 20 部门和 30 部门以外的员工信息

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 分组统计语句。

求每个部门的平均工资

hive (default)> select deptno, avg(sal) from emp group by deptno;

求每个部门的平均薪水大于 2000 的部门

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）多表连接

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

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 倍排序

select ename, sal*2 twosal from emp order by twosal;

3）多个列排序

按照部门和工资升序排序

select ename, deptno, sal from emp order by deptno, sal ;

4）每个 MapReduce 内部排序（Sort By）

Sort By：每个 MapReduce 内部进行排序，对全局结果集来说不是排序。

（1）设置 reduce 个数

set mapreduce.job.reduces=3;

（2）查看设置 reduce 个数

set mapreduce.job.reduces; 

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

select * from emp sort by empno desc;

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

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）先按照部门编号分区，再按照员工编号降序排序。

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）先创建分桶表，通过直接导入数据文件的方式

创建分桶表

create table stu_buck(id int, name string)clustered by(id)into 4 bucketsrow format delimited fields terminated by '\t';

查看表结构

desc formatted stu_buck;Num Buckets: 4

导入数据到分桶表中

load data local inpath '/opt/module/datas/student.txt' into table stu_buck;

查看创建的分桶表中是否分成 4 个桶

发现并没有分成 4 个桶。是什么原因呢？

（2）创建分桶表时，数据通过子查询的方式导入

先建一个普通的 stu 表

create table stu(id int, name string)row format delimited fields terminated by '\t';

向普通的 stu 表中导入数据

load data local inpath '/opt/module/datas/student.txt' into table stu;

清空 stu_buck 表中数据

truncate table stu_buck;select * from stu_buck;

导入数据到分桶表，通过子查询的方式

insert into table stu_buckselect id, name from stu;

发现还是只有一个分桶

需要设置一个属性

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;

查询分桶的数据

hive (default)> select * from stu_buck;

2）分桶抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

查询表 stu_buck 中的数据。

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.系统自带的函数

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-bin\lib 文件下的 jar 包都拷贝到 java 工程中。

3）创建一个类

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

add jar /opt/module/datas/udf.jar; 

6）创建临时函数与开发好的 java class 关联

create temporary function udf_lower as "com.doit.hive.Lower"; 

7）即可在 hql 中使用自定义的函数 strip

select ename, udf_lower(ename) lowername from emp;