基本查询
创建部门表
create table if not exists dept(deptno int,dname string,loc int)row format delimited fields terminated by '';
创建员工表
create table if not exists emp(empno int,ename string,job string,mgr int,hiredate string, sal double, comm double,deptno int)row format delimited fields terminated by '';
导入数据
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into tabledept;hive (default)> load data local inpath '/opt/module/datas/emp.txt' into table emp;
1.全表查询
hive (default)> select * from emp;
2.选择特定列查询
hive (default)> select empno, ename from emp;
注意:
(1)SQL 语言大小写不敏感。
(2)SQL 可以写在一行或者多行。
(3)关键字不能被缩写也不能分行。
(4)各子句一般要分行写。
(5)使用缩进提高语句的可读性。
Hive,对我们平常书写SQL很多地方是相同的,例如分组,排序,左连接,右连接等,不再介绍了,现在介绍他们不一样的地方。
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]';
连接谓词中不支持or
hive join目前不支持在on子句中使用谓词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;
排序
全局排序(Order By)
Order By:全局排序,只有一个Reducer
hive (default)> select ename, sal*2 twosal from emp order by twosal;
1.使用 ORDER BY 子句排序
ASC(ascend): 升序(默认)
DESC(descend): 降序
Sort By:对于大规模的数据集order by的效率非常低。在很多情况下,并不需要全局排序,此时可以使用sort by。
Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序,对全局结果集来说不是排序。
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 deptno desc;
4.将查询结果导入到文件中(按照部门编号降序排序)
hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result' select * from emp sort by deptno desc;
分区排序(Distribute By)
Distribute By: 在有些情况下,我们需要控制某个特定行应该到哪个reducer,通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition(自定义分区),进行分区,结合sort by使用。
对于distribute by进行测试,一定要分配多reduce进行处理,否则无法看到distribute by的效果。
案例实操:
(1)先按照部门编号分区,再按照员工编号降序排序。
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;
注意:
1. distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后,余数相同的分到一个区。
2. Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
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号部门分到一个分区里面去。
分桶及抽样查询
分桶表数据存储
分区提供一个隔离数据和优化查询的便利方式。不过,并非所有的数据集都可形成合理的分区。对于一张表或者分区,Hive 可以进一步组织成桶,也就是更为细粒度的数据范围划分。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
分区针对的是数据的存储路径;分桶针对的是数据文件。
1.先创建分桶表,通过直接导入数据文件的方式
(1)数据准备
student.txt
(2)创建分桶表
create table stu_buck(id int, name string)clustered by(id) into 4 bucketsrow format delimited fields terminated by '';
(3)查看表结构
hive (default)> desc formatted stu_buck;Num Buckets: 4
(4)导入数据到分桶表中
hive (default)> load data local inpath '/opt/module/datas/student.txt' into table stu_buck;
(5)查看创建的分桶表中是否分成4个桶
发现并没有分成4个桶。是什么原因呢?
2.创建分桶表时,数据通过子查询的方式导入
(1)先建一个普通的stu表
create table stu(id int, name string)row format delimited fields terminated by '';
(2)向普通的stu表中导入数据
load data local inpath '/opt/module/datas/student.txt' into table stu;
(3)清空stu_buck表中数据
truncate table stu_buck;select * from stu_buck;
(4)导入数据到分桶表,通过子查询的方式
insert into table stu_buckselect id, name from stu;
(5)发现还是只有一个分桶
(6)需要设置一个属性
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;
![29c70f4102b6e8e4a2344f3eb50eba69.png](https://i-blog.csdnimg.cn/blog_migrate/b6c2d93917be629077830975b8735cbd.jpeg)
(7)查询分桶的数据
hive (default)> select * from stu_buck;OKstu_buck.id stu_buck.name1004 ss41008 ss81012 ss121016 ss161001 ss11005 ss51009 ss91013 ss131002 ss21006 ss61010 ss101014 ss141003 ss31007 ss71011 ss111015 ss15
分桶规则:
根据结果可知:Hive的分桶采用对分桶字段的值进行哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中
分桶抽样查询
对于非常大的数据集,有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。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开始抽取,如果需要取多个分区,以后的分区号为当前分区号加上y。例如,table总bucket数为4,tablesample(bucket 1 out of 2),表示总共抽取(4/2=)2个bucket的数据,抽取第1(x)个和第3(x+y)个bucket的数据。
注意:x的值必须小于等于y的值,否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck
Hive的窗口函数
1.相关函数说明
OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW:当前行
n PRECEDING:往前n行数据
n FOLLOWING:往后n行数据
UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n,default_val):往前第n行数据
LEAD(col,n, default_val):往后第n行数据
NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。注意:n必须为int类型。
2.数据准备:name,orderdate,cost
jack,2017-01-01,10tony,2017-01-02,15jack,2017-02-03,23tony,2017-01-04,29jack,2017-01-05,46jack,2017-04-06,42tony,2017-01-07,50jack,2017-01-08,55mart,2017-04-08,62mart,2017-04-09,68neil,2017-05-10,12mart,2017-04-11,75neil,2017-06-12,80mart,2017-04-13,94
3.需求
(1)查询在2017年4月份购买过的顾客及总人数
(2)查询顾客的购买明细及月购买总额
(3)上述的场景, 将每个顾客的cost按照日期进行累加
(4)查询每个顾客上次的购买时间
(5)查询前20%时间的订单信息
4.创建本地business.txt,导入数据
vi business.txt
5.创建hive表并导入数据
create table business(name string, orderdate string,cost int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';load data local inpath "/opt/module/datas/business.txt" into table business;
6.按需求查询数据
(1)查询在2017年4月份购买过的顾客及总人数
select name,count(*) over () from business where substring(orderdate,1,7) = '2017-04' group by name;
(2)查询顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;
(3)上述的场景, 将每个顾客的cost按照日期进行累加
select name,orderdate,cost, sum(cost) over() as sample1,--所有行相加 sum(cost) over(partition by name) as sample2,--按name分组,组内数据相加 sum(cost) over(partition by name order by orderdate) as sample3,--按name分组,组内数据累加 sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合 sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行 sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行 from business;
rows必须跟在Order by 子句之后,对排序的结果进行限制,使用固定的行数来限制分区中的数据行数量
(4)查看顾客上次的购买时间
select name,orderdate,cost, lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2 from business;
(5)查询前20%时间的订单信息
select * from ( select name,orderdate,cost, ntile(5) over(order by orderdate) sorted from business) twhere sorted = 1;
Rank
1.函数说明
RANK() 排序相同时会重复,总数不会变
DENSE_RANK() 排序相同时会重复,总数会减少
ROW_NUMBER() 会根据顺序计算
2.数据准备
![64c9609a95f2ec0c4cfb01fee7c50d31.png](https://i-blog.csdnimg.cn/blog_migrate/50dac33df53d9d540c9c81eae5c9a5ef.jpeg)
3.需求
计算每门学科成绩排名。
4.创建本地score.txt,导入数据
vi score.txt
5.创建hive表并导入数据
create table score(name string,subject string, score int) row format delimited fields terminated by "";load data local inpath '/opt/module/datas/score.txt' into table score;
6.按需求查询数据
select name,subject,score,rank() over(partition by subject order by score desc) rp,dense_rank() over(partition by subject order by score desc) drp,row_number() over(partition by subject order by score desc) rmpfrom score;name subject score rp drp rmp孙悟空 数学 95 1 1 1宋宋 数学 86 2 2 2婷婷 数学 85 3 3 3大海 数学 56 4 4 4宋宋 英语 84 1 1 1大海 英语 84 1 1 2婷婷 英语 78 3 2 3孙悟空 英语 68 4 3 4大海 语文 94 1 1 1孙悟空 语文 87 2 2 2婷婷 语文 65 3 3 3宋宋 语文 64 4 4 4
系统内置函数
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.exec.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)注意事项
(1)UDF必须要有返回类型,可以返回null,但是返回类型不能为void;
自定义UDF函数
1.创建一个Maven工程Hive
2.导入依赖
org.apache.hivehive-exec1.2.1
3.创建一个类
public class Lower extends UDF {public String evaluate (final String s) {if (s == null) {return null;}return s.toLowerCase();}}
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 mylower as "com.test.hive.Lower";
7.即可在hql中使用自定义的函数
hive (default)> select ename, mylower(ename) lowername from emp;