Hadoop生态系统之Hive

最新推荐文章于 2023-09-12 11:19:57 发布

我亦无它

最新推荐文章于 2023-09-12 11:19:57 发布

阅读量1.3k

点赞数 4

分类专栏：大数据之Hadoop 文章标签： hive

本文链接：https://blog.csdn.net/Child2/article/details/104829375

版权

大数据之Hadoop 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

本文将从Hive的大方向上及Hive细节使用这两个方向来阐述：
1、Hive是什么？hive在Hadoop生态系统中充当什么角色？为什么需要学习使用Hive
2、如何使用Hive及代码演示

一宏观剖析

1 Hive是个啥？

摘自百度百科

hive是基于Hadoop构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop分布式文件系统中的数据：可以将结构化的数据文件映射为一张数据库表，并提供完整的SQL查询功能；可以将SQL语句转换为MapReduce任务运行，通过自己的SQL查询分析需要的内容，这套SQL简称Hive SQL，使不熟悉mapreduce的用户可以很方便地利用SQL语言‘查询、汇总和分析数据。而mapreduce开发人员可以把自己写的mapper和reducer作为插件来支持hive做更复杂的数据分析。它与关系型数据库的SQL略有不同，但支持了绝大多数的语句如DDL、DML以及常见的聚合函数、连接查询、条件查询。它还提供了一系列的进行数据提取转化加载，用来存储、查询和分析存储在Hadoop中的大规模数据集，并支持UDF（User-Defined Function）、UDAF(User-Defnes AggregateFunction)和USTF（User-Defined Table-Generating Function），也可以实现对map和reduce函数的定制，为数据操作提供了良好的伸缩性和可扩展性。

2 为啥要学习hive？

笔者个人认为：hive就是基于hadoop的一个数据仓库工具，可以对仓库进行数据提取，转换等一些列操作，其实我们写一个mapreduce程序同样也可以完成此类操作，但是为什么要学习hive和使用hive呢？因为它简单啊~
回想一下我们写一条SQL语句才多少代码量，一个MR程序多少代码量，而且通过hive可以使MR变得更加简单，所以hive是十分适合对数据仓库进行统计的一门工具，但是hive不提供实时查询功能和基于行级的数据更新操作，它更适合应用在基于大量不可变的批处理作业中如网络日志分析等，如果你使用hive去查询一个几百M的数据集那可能需要几分钟的时间延迟。
所以到这里，我想大部分人应该都明白hive是啥了吧，其实写hive就相当于在写MR程序，只不过更简单了，因为hive它能帮你把写的hql语句翻译成MR能看懂的语句来执行MR程序，达到殊途同归的效果。

二基本使用

这里笔者就直接将平时写的笔记CV上来啦，若有不到之处还请指正。~

1 Hive入门

-- “-e”不进入hive的交互窗口执行sql语句
bin/hive -e "sql";

-- “-f”执行脚本中sql语句
bin/hive -f /opt/module/datas/hive.sql

-- 在hive cli命令窗口中访问hdfs
dfs -ls /;

-- 在hive cli命令窗口执行shell命令
! ls /opt/module/datas;

-- 在hive cli命令窗口查看当前所有的配置信息
set;

-- 命令行添加参数
hiveconf param=value;
bin/hive -hiveconf mapred.reduce.tasks=10;

-- 查看参数设置
set mapred.reduce.tasks;

-- 在HQL中使用声明参数
hive (default)> set mapred.reduce.tasks=100;

-- 查看参数设置
hive (default)> set mapred.reduce.tasks;
注:命令行和HQL中设置仅对本次hive启动有效

-- 设置MR以Local模式运行
set hive.exec.mode.local.auto=true;

连接方式：使用Beeline连接Hive

默认hiveserver2是前台运行,设置后台运行
hiveserver2 &
启动beeline后,创建连接
beeline
!connect 'jdbc:hive2://hadoop102:10000'

连接方式：使用Java程序连接Hive

<!-- Maven依赖,导入hive-jdbc驱动 -->
<dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>1.2.1</version>
 </dependency>

注:hive还依赖于hadoop,还需要将hadoop依赖也加入

//1 注册驱动  可选，只要导入hive-jdbc.jar，会自动注册驱动
//Class.forName("org.apache.hive.jdbc.HiveDriver");

//2 创建连接  url,driverClass,username,password
        Connection connection = DriverManager.getConnection("jdbc:hive2://$userhost:10000", "$user", " ");

//3 准备sql
        String sql="select * from table_name";

//4 执行查询
	    PreparedStatement ps = connection.prepareStatement(sql);

//5 遍历结果集
		while (resultSet.next()){

         	System.out.println("column_name:" + resultSet.getString("column_name")+"   column_name:" + resultSet.getInt("column_name"));
        }

//6 关闭资源
		resultSet.close();
         ps.close();
         connection.close();

Hql语句执行顺序：

from
where
group by
select
having
order by

2 DDL（数据定义语言）

2.1 DDL之库操作

创建数据库

 

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
  
 -- 示例:
  CREATE DATABASE IF NOT EXISTS db_hive2
  COMMENT 'myDB'
  LOCATION '/'

删除数据库

DROP DATABASE [IF EXISTS] database_name [CASCADE];

	注:删除库时，是两步操作
	1.在mysql的DBS表中删除库的元数据
   	2.删除hdfs上库存放的路径
   	3.库中有表需要加上CASCADE

修改数据库


ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);   
-- (Note: SCHEMA added in Hive 0.14.0)
	注:修改DBPROPERTIES属性,同名的属性会覆盖，不存在的属性会新增

ALTER DATABASE database_name SET OWNER [USER|ROLE] user_or_role;   
-- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
	注:更改库的所有者

ALTER DATABASE database_name SET LOCATION hdfs_path; 
-- (Note: Hive 2.2.1, 2.4.0 and later)
	注:更改location路径

查询库结构


ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);   
-- (Note: SCHEMA added in Hive 0.14.0)
	注:修改DBPROPERTIES属性,同名的属性会覆盖，不存在的属性会新增

ALTER DATABASE database_name SET OWNER [USER|ROLE] user_or_role;   
-- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
	注:更改库的所有者

ALTER DATABASE database_name SET LOCATION hdfs_path; 
-- (Note: Hive 2.2.1, 2.4.0 and later)
	注:更改location路径

2 DDL之表操作

创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
-- 列的信息
(col_name data_type [COMMENT col_comment], ...)
-- 表的注释
[COMMENT table_comment] 
-- 是否是分区表，及指定分区字段,分区字段不能和列的字段相同
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
-- 指定表中的数据在分桶时以什么字段进行分桶操作
[CLUSTERED BY (col_name, col_name, ...) 
-- 表中的数据在分桶时，以什么字段作为排序的字段
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
-- 表中数据每行的格式，指定分隔符等
[ROW FORMAT row_format]
-- 如果向表中插入数据时，数据以什么格式存储
[STORED AS file_format] 
-- 表在hdfs上存储的位置
[LOCATION hdfs_path]
-- 指定表的某些属性
[TBLPROPERTIES]
-- 列分隔符
[row format delimited fields terminated by ',']
-- 数组分隔符
[collection items terminated by '_']
-- map分隔符
[map keys terminated by ':';]
-- 行分隔符
[lines terminated by '\n'];

-- 根据查询结果创建表
CREATE TABLE [IF NOT EXITS] table_name AS SELECT col_name, col_name FROM table_name;

-- 根据已经存在的表结构创建表
CREATE TABLE [IF NOT EXITS] table_name LIKE table_name;

删除表

	-- 删除表
DROP TABLE [IF EXISTS] table_name;
	-- 清空表数据(表必须是管理表)
TRUNCATE TABLE table_name;
	-- 删除分区表
ALTER TABLE table_name DROP PARTITION(col_name=val),PARTITION(clo_name=val)...;

修改表

	-- 注:修改表名
ALTER TABLE table_name RENAME TO new_table_name;
	-- 注:要更改表的注释，必须更改的comment属性TBLPROPERTIES
ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);	
	-- 注:修改表的某个属性
ALTER TABLE table_name SET TBLPROPERTIES('属性名'='属性值')
	-- 注:替换表的所有列
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 
	-- 注:添加列,字段位置在所有列后面(partition列前)
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...) 
	--注:添加或修改表约束

查询表结构

-- 查看表的基本[详细]属性
DESC [EXTENDED] table_name;
-- 查看表的建表语句
SHOW CREATE TABLE table_name;
-- 查询表的结构化信息
DESC FORMATTED table_name;
-- 查看表的分区信息
SHOW PARTITIONS table_name；
-- 查看reduceTask数量
SET mapreduce.job.reduces;

管理表和外部表的转换

ALTER TABLE table_name SET TBLPROPERTIES('EXTERNAL'='TRUE')

ALTER TABLE table_name SET TBLPROPERTIES('EXTERNAL'='FALSE')

3 DDL之分区表

手动添加分区

ALTER TABLE table_name ADD PARTITION(column_name=partition_value);

注:手动创建分区,不仅可以生成分区目录,还会生成分区的元数据

修复分区元数据

MSCK REPAIR TABLE table_name;

4 DDL之分桶表

分桶表导入数据

分桶和MR中的分区是一个概念,指在向表中使用insert 语句导入数据时,insert语句会翻译为一个MR程序,MR程序在运行时,可以根据分桶的字段,对数据进行分区.
如何实现分桶:
1.使用Insert的方式向表中导入数据,只有insert会运行MR.
2.reduceTask的个数需要大于1
3.分桶字段基于表中已有字段进行选取

创建分桶表

1.创建临时表bucktable_tmp
2.向临时表中添加数据
3.打开强制分桶的开关:
SET hive.enforce.bucketing=true;
4.需要让reduceTask的个数=桶数，默认reduceTask的个数为-1，-1代表由hive自动根据情况设置reduceTask的数量
SET mapreduce.job.reduces=-1
5.如果需要执行排序,提前打开强制排序开关
SET hive.enforce.sorting=true;
6.导入数据
INSERT INTO|OVERWRITE TABLE  bucktable_name SELECT * FROM  bucktabletmp_name

抽样查询

基于分桶表进行抽样查询,表必须是分桶表
SELECT * FROM table_name tablesample(BUCKET x OUT OF y ON clu_name);

注:
	x: 代表从当前的第几桶开始抽样(0<x<=y)
	y:  z/y 代表一共抽多少桶
	z: 一共分了z桶
	 从第x桶开始抽，当y<=z每间隔y桶抽一桶，直到抽满 z/y桶

3 DML（数据操作语言）

1 数据导入

通过Load向表中加载数据

-- 使用load的方式不仅可以帮我们将数据上传到分区目录，还可以自动生成分区的元数据
LOAD DATA [LOCAL] INPATH '$path' [OVERWRITE] INTO TABLE table_name [PARTITION (partcol1=val1,…)];
	
	注:overwrite表示覆盖表中已有数据,否则表示追加
	   partition表示上传到指定分区
	   分区的元数据存放在metastore.PARTITIONS表中

通过INSERT查询语句向表中导入数据

-- 1.插入数据
INSERT INTO|OVERWRITE TABLE  table_name [PARTITION (partcol1=val1,…)] VALUES(col1_val1,...);

-- 2.查表结果数据插入单张表
INSERT INTO|OVERWRITE TABLE table_name [PARTITION (partcol1=val1,…)]
             SELECT 语句;
             
-- 3.查表结果插入数据插入多张表
FROM table_name
INSERT INTO|OVERWRITE TABLE table_name [PARTITION (partcol1=val1,…)]
SELECT col_name,...
INSERT INTO|OVERWRITE TABLE table_name [PARTITION (partcol1=val1,…)]
SELECT col_name,...

通过put向Location加载数据

-- 1.上传数据到HDFS指定加载数据路径(location)

注: 
	1.默认location路径
		hive会显示的在默认数据仓库路径下创建一个和表名相同的目录用来存放表的数据
	2.自定义location路径
		hive会自动扫描路径下的所有文件,读取到表中.如果目标路径不存在,hive会帮忙创建一条路径,当在路径目录下上传指定文件,在hive中执行查询语句时hive会从指定目录下加载数据到表中

通过Import导入数据到表中

IMPORT TABLE table_name [PARTITION (partcol1=val1,…)] FROM 'inpath';

2 数据导出

通过insert导出数据

INSERT OVERWRITE [LOCAL] DIRECTORY 'inpath'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  
SELECT 语句;

Export导出到HDFS上

EXPORT TABLE table_name TO 'outpath';

HiveShell命令导出

hive -e 'sql语句;' > $outpath/$file;

4 排序

Order by

Order by 代表全排序
	全排序: 对整个数据集进行排序,要求只能有一个reduceTask
	-- 降序
SELECT * FROM table_name ORDER BY col_name,... desc;

Sort by

sort by 代表部分排序
	部分排序: 设置多个reduceTask，每个reduceTask对所持有的分区的数据进行排序,每个分区内部整体有序
手动修改mapreduce.job.reduces,告诉hive需要启动多少个reducetask
SET mapreduce.job.reduces=n;

-- 部分排序
SELECT * FROM table_name SORT BY col_name;

Distribute by

Distribute by 代表指定使用什么字段进行分区
	结合sort by一起使用,Distribute by必须写在sort by之前,先分区再排序

-- 例: 按照部门号，对同一个部门的薪水进行降序排序，每一个部门生成一个统计的结果文件
insert overwrite local directory '/home/atguigu/sortby' row format delimited fields terminated by '\t'  select * from emp Distribute by deptno sort by sal desc ;

Cluster by

Cluster by 代表按照指定字段进行分区和排序且排序方式升序
	Distribute by和Sort by的一种简化,分区字段和排序字段相同且不支持降序

5 函数

1 一进一出（UDF）

NVL 空字段赋值

NVL函数基本介绍: 给值为null的数据赋值,它的格式是NVL(string, replace_with)
	如果string为NULL,则返回replace_with的值
	如果string不为NULL,则返回string的值
	如果两个参数都为NULL,则返回NULL	
注: replace_with可以是表中的字段也可以是用户自定义的字段

例: 查询员工的comm,如果为NULL则用领导id 代替
SELECT NVL(comm,mgr_id) from emp;

时间函数

-- DATE_FORMAT() 格式化时间
SELECT DATE_FORMAT('2018-08-08','yyyy-MM-dd');
2018-08-08

-- unix_timestamp():返回当前或指定时间的时间戳	
select unix_timestamp();
1583852845

-- from_unixtime()：将时间戳转为日期格式
select from_unixtime(1583852845);
2020-03-10 23:07:25

-- current_date()：当前日期
select current_date();
2020-03-10

-- current_timestamp()：当前的日期加时间
select current_timestamp();
2020-03-10 23:19:08.106

-- to_date('expr')：抽取日期部分
select to_date('2020-03-10');
2020-03-10

-- year/month/day/hour/minute/second('expr'): 获取年/月/日/时/分/秒
select year('2018-08-08');
2018 

-- weekofyear('expr')：当前时间是一年中的第几周
select weekofyear('2008-08-08');
32

-- dayofmonth('expr')：当前时间是一个月中的第几天
select dayofmonth('2008-08-08');
8

-- * months_between('expr1','expr2')： 两个日期间的月份，前-后
select months_between('2008-05-05','2008-08-08');
-3.09677419

-- * add_months('expr1','expr2')：日期加减月
select add_months('2008-08-08',1);
2008-09-08

-- * datediff('expr1','expr2')：两个日期相差的天数，前-后
select datediff('2008-08-08','2008-05-08');
92

-- * date_add('expr1','expr2')：日期加天数
select date_add('2008-08-08',30);
2008-09-07

-- * date_sub('expr1','expr2')：日期减天数
select date_sub('2008-08-08',30);
2008-07-09 

-- * last_day('expr')：日期的当月的最后一天
select last_day('2008-08-08');
2008-08-31

CASE WHEN 函数

CASE 
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	...
	ELSE 结果3 END;
CASE 字段名
	WHEN 条件1 THEN 结果1
	WHEN 条件2 THEN 结果2
	...
	ELSE 结果3 END;

例: 求不同部门男女各多少人
select dept_id,
sum(case sex when '男' then 1 else 0 end) sum_0,
sum(case sex when '女' then 1 else 0 end) sum_1,
count(*) num_2
from emp_sex
group by dept_id;

--注: 这个例子体现出 在执行完分组操作以后,每一条聚合函数都对该组进行了一轮遍历得到一个聚合结果

类似三元运算符,做单层判断
IF(condition,result1,result2)
例:
SELECT empno,ename,sal,IF(sal<1500,'Poor Gay','Rich Gay') from emp;

取整函数

-- ceil(num)：向上取整
select ceil(0.5);
1

-- floor：向下取整
select floor(0.5);
0

-- round(num): 四舍五入
select round(0.3);
0.0

字符串操作函数

-- upper('expr')：转大写
select upper('abc');
ABC 

-- lower('expr')：转小写
select lower('ABC');
abc

-- length('expr'): 字符串长度
select length('abc');
3

-- * trim('expr'): 前后去空格
select trim(' ab c ');
ab c

-- * regexp_replace(str, regexp, rep):使用正则表达式匹配目标字符串，匹配成功后替换
注: 用rep替换所有匹配regexp的str子字符串
select regexp_replace('abacadae','a','0');
0b0c0d0e 

-- substr('expr',num): 返回字符串expr从num位置到结尾的字符串 
select substr('abcde',3);
cde

--substr('expr',num,len): 返回字符串expr从num位置开始长度为len的字符串
select substr('abcd',2,2);
bc

-- repeat('expr',num): 返回重复num次后的expr字符串
select repeat('hello',3);
hellohellohello

-- ascii('expr'): 返回字符串expr第一个字符的ASCII码值
select ASCII('abc');
97


-- lpad(str,len,pad)：向左补齐
返回str，用pad左填充到len长度
注:到指定长度,拼接后的长度超过len优先从pad[0]依次取出拼接
select lpad('abc',5,'abc');
ababc

-- rpad(str,len,pad)： 向右补齐
select rpad('abc',5,'abc');
abcab

-- split('expr','pat'): pat字符串分割expr，返回分割后的字符串序列 
select split('a,b,c,d,e',',');
["a","b","c","d","e"]

-- find_in_set（string str，string strList）: 返回str在strlist第一次出现的位置
注: strlist是用逗号分割的字符串。如果没有找到该str字符，则返回0  
select find_in_set('cc','aa,bb,dd,cc,ee');
4

集合操作函数

-- size(a)：集合（map和list）中元素的个数
select size(map ('first','JOIN','last','Doe'));
2

-- map_keys(map)：返回map中的key
select map_keys(map ('first','JOIN','last','Doe'));
["first","last"]

--  map_values(map): 返回map中的value
select map_values(map ('first','JOIN','last','Doe'));
["JOIN","Doe"] 

-- * array_contains(array, value): 判断array中是否包含某个元素
select array_contains(array(1,2,3,4,5), 5);
true

-- sort_array(array(obj1, obj2,...))： 将array中的元素排序
select sort_array(array(5,6,4,1,2,8,9,0));
[0,1,2,4,5,6,8,9]

2 多进一出（UDAF）

Collect 行转列函数

collect_set (col) / collect_list(col)
基本介绍: collect函数只接受基本数据类型,主要作用是将某字段的值汇总,产生Array类型字段,该函数只能接受一列参数,一般配合group by函数直接汇总分组数据.
功能: 获取group by 后面没有的字段
区别: set会进行去重,list不会

CONCAT函数

-- hive的collect函数只能接受一个参数,所以我们必须通过hive的concat函数先指定格式合并再collect
CONCAT(string1/col,string2/col,...)
基本介绍: 返回输入字符串连接后的结果,如果有一个参数为NULL，则返回的结果为NULL。
功能介绍: 按照指定格式合并分组内全部行记录
例: select concat('aaa','bbb');
aaabbb

-- 用collect函数合并时结果字段为数组形式，很多时候我们并不希望是这种格式，所以可以通过concat_ws去改变
CONCAT_WS('separator',str1/col,str2/col,...)
基本介绍: 特殊形式的CONCAT(),两个参数之间加上特定的分隔符,返回的是用指定分隔符连接参数的字符串,如果字符串为null,则返回null,参数为null则忽略该参数
例: select month,day,concat_ws(',',collect_list(cookieid)) cw from test2 group by month,day

3 一进多出（UDTF）

explode 列转行函数

基本介绍:
explode()接收一个数组(或一个map)作为输入并将数组(map)的元素作为单独的行输出,即explode函数的参数只能是数组或map类型
基本使用:
UDTF一般有两种使用方法,一种直接放到select后面单独执行,一种和lateral view(侧写)一起使用
注:
第一种方法在select中使用时
--不可以添加其他字段使用:
select a, explode_map(properties) as (col1,col2) from src(报错)
--不可以嵌套调用：
select explode_map(explode_map(properties)) from src(报错)
--不可以和group by/cluster by/distribute by/sort by一起使用：
select explode_map(properties) as (col1,col2) from src group by col1, col2(报错)
例:select explode(array('A','B','C'));
col
A
B
C
例:select explode(map('A',10,'B',20,'C',30)) as (my_key,my_value);
my_key    my_value
A    10
B    20
C    30

lateral view

lateralview 与用户自定义表生成函数（UDTF）（例如 explode）结合使用，UDTF 为每个输入行生成零个或多个输出行。lateralview 首先将 UDTF 应用于基础表的每一行，然后将结果输出行与输入行连接起来形成具有所提供表别名的虚拟表。

例:
basetable.col1    basetable.col2
[1,2]    ["a","b","c"]
[3,4]    ["d","e","f"]
-- 查询语句:
SELECT myCol1, col2 FROM basetable
	LATERAL VIEW explode(col1) myTable1 AS myCol1;       
mycol1    col2
1    ["a","b","c"]
2    ["a","b","c"]
3    ["d","e","f"]
4    ["d","e","f"]

from 子句可以具有多个 lateralview 子句。后续的 lateralview 子句可以引用 lateralview 左侧表中的任何列。
例:
-- 查询语句:
SELECT myCol1, myCol2 FROM baseTable
	LATERAL VIEW explode(col1) myTable1 AS myCol1
	LATERAL VIEW explode(col2) myTable2 AS myCol2;
mycol1    mycol2
1    a
1    b
1    c
2    a
2    b
2    c
3    d
3    e
3    f
4    d
4    e
4    f

4 窗口函数

开窗函数

-- 窗口函数 = 函数 + 窗口
LEAD: 用来返回当前行以下行的数据
--	语法: LEAD(列名 [,offset] [,default])
	offset是偏移量，默认为1
	default： 取不到值就使用默认值代替
LAG: 用来返回当前行以上行的数据
--	语法:LAG(列名 [,offset] [,default])
	offset是偏移量，默认为1
	default： 取不到值就使用默认值代替
FIRST_VALUE: 返回指定列的第一个值
--	语法:FIRST_VALUE(列名[,false是否忽略null值])
LAST_VALUE:返回指定列的最后一个值
--	语法： LAST_VALUE(列名[,false是否忽略null值])
标准聚集函数:
	MAX(),MIN(),AVG(),COUNT(),SUM()
分析排名函数:
	RANK(): 允许并列,并列后跳号
	DENSE_RANK: 连续,允许并列,并列不跳号
	ROW_NUMBER: 连续,不并列,不跳号
	CUME_DIST: 当前值以上的所有值,占总数据集的比例
	PERCENT_RANK: rank()-1/总数据集-1
	NTILE: 将窗口中的数据平均分配到X个组中,返回当前数据的组号
	select *,RANK() over(order by score)rank_num1,
	DENSE_RANK over(order by score)DENSE_RANK_num2,
	ROW_NUMBER over(order by score)ROW_NUMBER_num3,
	CUME_DIST over(order by score)CUME_DIST_num4,
	PERCENT_RANK over(order by score)PERCENT_RANK_num5
	from score;

窗口

窗口: 函数在运行时,计算结果集的范围
窗口函数指以上开窗函数在运算时,可以自定义一个窗口(计算的范围)
-- WINDOW CLAUSE (窗口子句)
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
名词解释:
CURRENT ROW：当前行
num PRECEDING: 往前n行(包含其间所有)
num FOLLOWING: 往后n行(包含其间所有)
UNBOUNDED PRECEDING: 表示从前面的起点
UNBOUNDED FOLLOWING: 表示到后面的终点

注:
1.当over()既没有写order by,也没有写window子句,此时窗口默认等同于上无边界到下无边界（整个数据集）

2.当over()中指定了order by 但是没有指定 window 子句,此时窗口默认等同于上无边界到当前行

    	--(整个组)
4.支持Over(),但是不支持在over中定义windows子句的函数:
	Ranking functions: Rank, NTile, DenseRank, CumeDist, PercentRank.
	Lead and Lag functions

基本使用

开窗函数 OVER([PARTITION BY 字段1,字段2] [ORDER BY 字段 ASC|DESC] [WINDOW CLAUSE])
名词解释:
OVER(): 指定分析函数的数据窗口大小，这个数据窗口大小随行的变化而变化
partition by(可选):根据某些字段对整个数据集进行分区
order by: 对分区或整个数据集中的数据按照某个字段进行排序
注: 如果对数据集进行了分区，那么窗口的范围不能超过分区的范围,即窗口必须在区内指定

案例实操之聚集函数

+----------------+---------------------+----------------+--+
| business.name  | business.orderdate  | business.cost  |
+----------------+---------------------+----------------+--+
| jack           | 2017-01-01          | 10             |
| tony           | 2017-01-02          | 15             |
| jack           | 2017-02-03          | 23             |
| tony           | 2017-01-04          | 29             |
| jack           | 2017-01-05          | 46             |
| jack           | 2017-04-06          | 42             |
| tony           | 2017-01-07          | 50             |
| jack           | 2017-01-08          | 55             |
| mart           | 2017-04-08          | 62             |
| mart           | 2017-04-09          | 68             |
| neil           | 2017-05-10          | 12             |
| mart           | 2017-04-11          | 75             |
| neil           | 2017-06-12          | 80             |
| mart           | 2017-04-13          | 94             |
+----------------+---------------------+----------------+--+

--查询在2017年4月份购买过的顾客及总人数

思路分析:
1.指定2017年4月,即需要使用到条件过滤
2.在4月份购买过的顾客可能有重复,需要用到分组将相同的顾客分到同一组
3.需要统计4月份的总人数,需要用到count开窗函数,窗口大小为表的第一行到最后一行的数量

select name,count(*)over()
from business
where year(orderdate)=2017 and month(orderdate)=4
group by name;

-- 查询顾客的购买明细及月购买总额

思路分析:
1.需要对每一个顾客按月进行分区
2.每个顾客的购买明细为一列
3.使用sum开窗函数统计求和,窗口大小为表中当前组的第一列到当前组的最后一列
4.Over子句之后第一个提到的就是Partition By.Partition By子句也可以称为查询分区子句，非常类似于Group By，都是将数据按照边界值分组，而Over之前的函数在每一个分组之内进行，如果超出了分组，则函数会重新计算

select *,sum(cost) over(partition by name,substring(orderdate,1,7)) total_cast
from business;

-- 查询顾客的购买明细要将cost按照日期进行累加

思路分析:
1.对顾客分区
2.区内按日期进行排序
3.开窗函数sum

select *,sum(cost) over(partition by name ORDER BY orderdate asc) total_cost
from business;

-- 查询顾客的购买明细及顾客上次的购买时间

思路分析:
1.对顾客分区,日期排序
2.开窗函数LAG

select *,LAG(orderdate,1,'无') over(partition by name order by orderdate) up_day
from business;

-- 查询顾客的购买明细及顾客下次的购买时间

思路分析:
1.对顾客按name分区,日期排序
2.开窗函数LEAD

select *,LEAD(orderdate,1,'无') over(partition by name order by orderdate) up_day
from business;

-- 查询顾客的购买明细及顾客本月第一次购买的时间

思路分析:
1.对顾客按name和月份分区,日期按月排序
2.开窗函数 FIRST_VALUE(列名，[false是否忽略null值])

select *,FIRST_VALUE(orderdate) over(partition by name,substring(orderdate,1,7) order by orderdate) first_day
from business;

-- 查询顾客的购买明细及顾客本月最后一次购买的时间

思路分析:
1.对顾客按name和月份分区,日期按月排序
2.开窗函数 LAST_VALUE(列名，[false是否忽略null值])

select *,LAST_VALUE(orderdate) over(partition by name,substring(orderdate,1,7) order by orderdate) last_day
from business;

-- 查询顾客的购买明细及顾客最近三次cost花费

思路分析:
1.对顾客按name分区,按日期排序
2.开窗函数sum
3.窗口大小因最近这个需求不明确分两种情况
	- a.最近为上一条,当前一条和下一条的花费
	- b.最近为前三条的花费
a.
select *,sum(cost) over(partition by name order by orderdate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum_3
from business;

b.
select *,sum(cost) over(partition by name order by orderdate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) sum_3
from business;

-- 查询前20%时间的订单信息

思路分析:
1.按日期排序
2.开窗函数CUME_DIST()
3.取出表中小于等于0.2的数据
select * 
from
(select name,orderdate,cost,CUME_DIST() over(order by orderdate) CUME_DIST_num
from business)t1
where CUME_DIST_num <= 0.2;

思路二: 将按日期排序后的表分成5个区,取出分区1当中的即为前20%的订单信息
	这样做不太精确,因为均分以后会存在取余的问题,导致分区内多一条数据
select *
from
(select name,orderdate,cost,NTILE(5) over(order by orderdate) NTILE_num
from business)t1
where NTILE_num=1;

hive就先到这里，后续有好的项目笔者在更新~

我亦无它

关注

4
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
Hadoop生态系统之Hive

本文将从Hive的大方向上及Hive细节使用这两个方向来阐述：1、Hive是什么？hive在Hadoop生态系统中充当什么角色？为什么需要学习使用Hive2、如何使用Hive及代码演示一宏观剖析1 Hive是个啥？摘自百度百科hive是基于Hadoop构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop分布式文件系统中的数据：可以将结构化的数据文件映...
复制链接

扫一扫