hive 分区_Hive的学习，Hive的查询与自定义函数

基本查询

创建部门表

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;

(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．数据准备

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;