继上一篇大数据技术之Hive (二)，今天更新第三篇，本篇主要分享Hive的查询和函数。
基本查询(Select...From)
全表查询
hive (default)> select * from emp;
在这里我还是要推荐下我自己建的大数据学习交流qq裙： 957205962， 裙 里都是学大数据开发的，如果你正在学习大数据 ，小编欢迎你加入，大家都是软件开发党，不定期分享干货（只有大数据开发相关的），包括我自己整理的一份2018最新的大数据进阶资料和高级开发教程，欢迎进阶中和进想深入大数据的小伙伴
hive (default)> select empno, ename from emp;
注意:
1）SQL 语言大小写不敏感
2）SQL 可以写在一行或者多行
3）关键字不能被缩写也不能分行
4）各子句一般要分行写
5）使用缩进提高语句的可读性
列别名
重命名一个列，紧跟列名，也可以在列名和别名之间加入关键字‘AS’
hive (default)> select ename AS name, deptno dn from emp;
算术运算符
例如
hive (default)> select sal +1 from emp;
常用函数
求总行数(count)
hive (default)> select count(*) cnt from emp;
求工资的最大值(max)
hive (default)> select max(sal) max_sal from emp;
求工资的最小值(min)
hive (default)> select min(sal) min_sal from emp;
求工资的总和(sum)
hive (default)> select sum(sal) sum_sal from emp;
求工资的平均值(avg)
hive (default)> select avg(sal) avg_sal from emp;
Limit 语句
典型的查询会返回多行数据，LIMIT 子句用于限制返回的行数
hive (default)> select * from emp limit 5;
Where 语句
1、使用 WHERE 子句，将不满足条件的行过滤掉
2、WHERE 子句紧随 FROM 子句
查询出薪水大于 1000 的所有员工
hive (default)> select * from emp where sal >1000;
比较运算符(Between/In/ Is Null)
下面表中描述了谓词操作符，这些操作符同样可以用于 JOIN...ON 和 HAVING 语句中。
1、查询出薪水等于 5000 的所有员工
hive (default)> select * from emp
where sal =5000;
2、查询工资在 500 到 1000 的员工信息
hive (default)> select * from emp where sal between 500 and 1000;
3、查询 comm 为空的所有员工信息
hive (default)> select * from emp where comm is null;
4、查询工资是 1500 和 5000 的员工信息
hive (default)> select * from emp where sal IN (1500, 5000);
Like 和 RLike
1、使用 LIKE 运算选择类似的值
2、选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
3、RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大 的语言来指定匹配条件。
在这里我还是要推荐下我自己建的大数据学习交流qq裙： 957205962， 裙 里都是学大数据开发的，如果你正在学习大数据 ，小编欢迎你加入，大家都是软件开发党，不定期分享干货（只有大数据开发相关的），包括我自己整理的一份2018最新的大数据进阶资料和高级开发教程，欢迎进阶中和进想深入大数据的小伙伴
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]';
逻辑运算符(And/Or/Not)
1、查询薪水大于 1000，部门是 30
hive (default)> select * from emp where sal>1000 and deptno=30;
2、查询薪水大于 1000，或者部门是 30
hive (default)> select * from emp where sal>1000 or deptno=30;
3、查询除了 20 部门和 30 部门以外的员工信息
hive (default)> select * from emp where deptno not IN(30, 20);
Group By 语句
GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组， 然后对每个组执行聚合操作。
1、计算 emp 表每个部门的平均工资
hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
2、计算 emp 每个部门中每个岗位的最高薪水
hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno,
t.job;
Having 语句
having 与 where 不同点：
1、where 针对表中的列发挥作用，查询数据;having 针对查询结果中的列发挥作用，筛选数据。
2、where 后面不能写分组函数，而 having 后面可以使用分组函数。
3、having 只用于 group by 分组统计语句。
求每个部门的平均薪水大于 2000 的部门
求每个部门的平均工资
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;
Join 语句
等值 Join
Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接，不支持非等值连接。
1、根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门编号;
hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d one.deptno = d.deptno;
表的别名
别名的好处
1、使用别名可以简化查询。
2、使用表名前缀可以提高执行效率。
合并员工表和部门表
hive (default)> select e.empno, e.ename, d.deptno
from emp e
join dept d
on e.deptno = d.deptno;
内连接
内连接:只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno =d.deptno;
左外连接
左外连接:JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。
hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno =d.deptno;
右外连接
右外连接:JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。
hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno =d.deptno;
满外连接
满外连接:将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定 字段没有符合条件的值的话，那么就使用 NULL 值替代。
hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno =d.deptno;
多表连接
注意:连接 n 个表，至少需要 n-1 个连接条件。例如:连接三个表，至少需要两个连 接条件。
创建位置表
create table if not exists default.location( loc int,
loc_name string
)
row format delimited fields terminated by '\t';
导入数据
hive (default)> load data local inpath '/opt/module/datas/location.txt' into table
default.location;
多表连接查询
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 总是按照从左到右的 顺序执行的。
笛卡尔积
1、笛卡尔集会在下面条件下产生:
1）省略连接条件
2）连接条件无效
3）所有表中的所有行互相连接
2)例子
hive (default)> select empno, deptno from emp, dept;
FAILED: SemanticException Column deptno Found in more than One Tables/Subqueries
连接谓词中不支持 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;
以上语句是错误的，or只能在过滤条件中
排序
全局排序(Order By)
Order By:全局排序，一个 MapReduce
1）使用 ORDER BY 子句排序
ASC(ascend): 升序(默认)
DESC(descend): 降序
2）ORDER BY 子句在 SELECT 语句的结尾
例子
1）查询员工信息按工资升序排列
hive (default)> select * from emp order by sal;
2）查询员工信息按工资降序排列
hive (default)> select * from emp order by sal desc;
按照别名排序
按照员工薪水的 2 倍排序
hive (default)> select ename, sal*2 twosal from emp order by twosal;
多个列排序按照部门和工资升序排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal ;
每个 MapReduce 内部排序(Sort By)
Sort By:每个 MapReduce 内部进行排序，对全局结果集来说不是排序
1）设置 reduce 个数
hive (default)> set mapreduce.job.reduces=3;
2）查看设置 reduce 个数
hive (default)> set mapreduce.job.reduces;
3）根据部门编号降序查看员工信息
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;
分区排序(Distribute By)
在这里我还是要推荐下我自己建的大数据学习交流qq裙： 957205962， 裙 里都是学大数据开发的，如果你正在学习大数据 ，小编欢迎你加入，大家都是软件开发党，不定期分享干货（只有大数据开发相关的），包括我自己整理的一份2018最新的大数据进阶资料和高级开发教程，欢迎进阶中和进想深入大数据的小伙伴
Distribute By:类似 MR 中 partition，进行分区，结合 sort by 使用。
注意，Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by的效果。
先按照部门编号分区，再按照员工编号降序排序。
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;
Cluster By
当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是倒序排 序，不能指定排序规则为 ASC 或者 DESC。
1）以下两种写法等价
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;
注意:按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一 个分区里面去。
分桶及抽样查询
分桶表数据存储
分区针对的是数据的存储路径;
分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理 的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
1)先创建分桶表，通过直接导入数据文件的方式
创建分桶表
create table stu_buck(id int, name string) clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';
查看表结构
hive (default)> desc formatted stu_buck;
导入数据到分桶表中
hive (default)> load data local inpath '/opt/module/datas/student.txt' into table stu_buck;
发现并没有分成 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_buck
select id, name from stu cluster by(id);
发现还是只有一个分桶
需要设置一个属性
hive (default)>set hive.enforce.bucketing=true;
hive (default)> set mapreduce.job.reduces=-1;
hive (default)>insert into table stu_buck select id, name from stu cluster by(id);
查询分桶的数据
hive (default)> select * from stu_buck;
分桶抽样查询
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结 果。Hive 可以通过对表进行抽样来满足这个需求。
查询表 stu_buck 中的数据。
hive (default)> 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 of4)，表示总共抽取(4/4=)1 个 bucket 的数据，抽取第 4 个 bucket 的数据。
注意:x 的值必须小于等于 y 的值，否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than
denominator in sample clause for table stu_buck
数据块抽样
Hive 提供了另外一种按照百分比进行抽样的方式，这种是基于行数的，按照输入路径下的数据块百分比进行的抽样。
hive (default)> select * from stu tablesample(0.1 percent) ;
提示:这种抽样方式不一定适用于所有的文件格式。另外，这种抽样的最小抽样单元是 一个 HDFS 数据块。因此，如果表的数据大小小于普通的块大小 128M 的话，那么将会返回 所有行。
函数
系统自带的函数
1）查看系统自带的函数
hive> show functions;
2）显示自带的函数的用法
hive> desc function upper;
3）详细显示自带的函数的用法
hive> desc function extended upper;
自定义函数
1）Hive 自带了一些函数，比如:max/min 等，但是数量有限，自己可以通过自定义 UDF来方便的扩展。
2）当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义 函数(UDF:user-defined function)。
3）根据用户自定义函数类别分为以下三种:
1、UDF(User-Defined-Function)
一进一出
2、UDAF(User-Defined Aggregation Function)
聚集函数，多进一出
类似于:count/max/min
3、UDTF(User-Defined Table-Generating Functions)
一进多出
如 lateral view explore()
4）官方文档地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins
5）编程步骤:
1、继承 org.apache.hadoop.hive.ql.UDF
2、需要实现 evaluate 函数;evaluate 函数支持重载;
3、在 hive 的命令行窗口创建函数
a)添加 jar
add 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;
6）注意事项
UDF 必须要有返回类型，可以返回 null，但是返回类型不能为 void;
自定义 UDF 函数开发案例
1）创建一个 java 工程，并创建一个 lib 文件夹
2）将 hive 的 jar 包解压后，将 apache-hive-1.2.1-bin\lib 文件下的 jar 包都拷贝到 java 工程中。
3）创建一个类
4)打成 jar 包上传到服务器/opt/module/jars/udf.jar
5)将 jar 包添加到 hive 的 classpath
hive (default)> add jar /opt/module/datas/udf.jar;
6)创建临时函数与开发好的 java class 关联
hive (default)> create temporary function my_lower as "com.motongxue.hive.Lower";
在这里我还是要推荐下我自己建的大数据学习交流qq裙： 957205962， 裙 里都是学大数据开发的，如果你正在学习大数据 ，小编欢迎你加入，大家都是软件开发党，不定期分享干货（只有大数据开发相关的），包括我自己整理的一份2018最新的大数据进阶资料和高级开发教程，欢迎进阶中和进想深入大数据的小伙伴
7)即可在 hql 中使用自定义的函数 strip
hive (default)> select ename, my_lower(ename) lowername from emp;
这是Hive的第三部分，之后会更新Hive的压缩、存储、优化，作为Hive的一个系列。
今天是2018的最后一个工作日，我也会抓紧在2019之前完成Hive整个系列的更新。
扫一扫
3355
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
