Hive的常用函数

1、系统内置函数
1．查看系统自带的函数
hive> show functions;
2．显示自带的函数的用法
hive> desc function upper;
3．详细显示自带的函数的用法
hive> desc function extended upper;

2、取值计算
1、取整函数：round
语法：round(double a)
返回值：BIGINT
说明：返回double类型的整数值部分（遵循四舍五入）
select round(3.14159260 from tableName;
select round(3.5) from tableName;
create table tableName as select round(9542.158) from tableName;
2、指定精度取整函数：round
语法：round(double a,int d)
返回值：DOUBLE
说明：返回指定精度d的double类型
select round(3.1415926,4) from tableName;
3、向下取整函数floor
语法：floor(double a)
返回值：BIGINT
说明：返回等于或者小于该double变量的最大的整数
select floor(3.1415926) from tableName;
select floor(25) from tableName;
4、向上取整函数：ceil
语法：ceil(double a)
返回值：BIGINT
说明：返回等于或者大于该double变量的最小的整数
select ceil(3.1415926) from tableName;
select ceil(46) from tableName;
5、向上取整函数：ceiling
语法：ceiling(double a)
返回值：BIGINT
说明：与ceil功能相同
select ceiling(3.1415926) from tableName;
select ceiling(46) from tableName;
6、取随机数函数：rand
语法：rand(),rand(int seed)
返回值：double
说明：返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列
select rand) from tableName;
select rand() from tableName;
select rand(100) from tableName;
select rand(100) from tableName;
2.3日期函数
1、UNIX时间戳转日期函数：from_unixtime
语法：from_unixtime(bigint unixtime[,string format])
返回值：string
说明：转化UNIX时间戳（从1970-01-01 ）到当前时区的时间格式
select from_unixtime(1323308943,'yyyyMMdd') from tableName;
2、获取当前UNIX时间戳函数
语法：unix_timestamp()
返回值：bigint
说明：获得当时时区的UNIX时间戳
select unix_timestamp() from tableName;
3、日期转UNIX时间戳函数：unix_timestamp
语法：unix_timestam(string date)
返回值：bigint
说明：转换格式为'yyyy-MM-dd HH:mm:ss"的日期到UMIX时间戳。如果转换失败，则返回0、
select unix_timestamp('2011-12-07 13:01:03') from tableName;
4、指定格式日期转UNIX时间戳函数：unix_timestamp
语法：unix_timestamp(string date,string pattern)
返回值：bigint
说明：转换pattern格式的日期到UNiX时间戳。如果转化失败，则返回0.
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from tableName;
5、日期时间转日期函数：to_date
语法：to_date(string datetime)
返回值：string
说明：返回日期时间字段中的日期部分。
select to_date("2011-12-08 10:03:01') from tableName
6、日期转年函数：year
语法：year(string date)
返回值：int
说明：返回日期中的年
select year('2011-12-08 10:03:01') from tableName;

select year('2012-12-08') from tableName;
7、日期转月函数：month
语法：month(string date)
返回值：int
说明：返回date或datetime中的月份
select month('2011-12-08 10:03:01') from tableName;
select month('2011-08-08') from tableName;

8、日期转天函数：day
语法：day(strign date)
返回值：int
说明：返回值日期中的天
select day('2011-12-08 10:03:01') from tableName;
select day('2011-12-24') from tableName;

9、日期转小时函数: hour

语法: hour (string date)
返回值: int
说明: 返回日期中的小时。

hive> select hour('2011-12-08 10:03:01') from tableName;
10

10、日期转分钟函数: minute

语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。

hive> select minute('2011-12-08 10:03:01') from tableName;
3

-- second 返回秒
hive> select second('2011-12-08 10:03:01') from tableName;
1

12、日期转周函数: weekofyear

语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。

hive> select weekofyear('2011-12-08 10:03:01') from tableName;
49

13、日期比较函数: datediff

语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。

hive> select datediff('2012-12-08','2012-05-09') from tableName;
213

14、日期增加函数: date_add

语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。

hive> select date_add('2012-12-08',10) from tableName;
2012-12-18

15、日期减少函数: date_sub

语法: date_sub (string startdate, int days)
返回值: string
说明: 返回开始日期startdate减少days天后的日期。

hive> select date_sub('2012-12-08',10) from tableName;
2012-11-28

2.4 条件函数（重点）

1、if函数：if

1、If函数: if

语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull
select if(1=2,100,200) from tableName;
select if(1=1,100,200) from tableName;
2、非空查找函数：COALESCE
语法：COALESCE(T v1,T v2,..)
返回值：T
说明：返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
select COALESCE(null,'100','50') from tableName;
3、条件判断函数：CASE
语法：CASE a WHEN b THEN c [WHEN d THEN e] * [ELSE f] END
返回值：T
说明：如果a等于b,那么返回c；如果a等于d，那么返回e；否则返回f
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;

select case 200 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;

4、条件判断函数：CASE
语法：CASE WHEN a THEN b [WHEN c THEN d] * [ELSSE e] END
返回值：T
说明：如果a为TRUE，则返回b；如果c为TRUE，则返回d;否则返回e
select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
select case when 1=1 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
2.5字符串函数
1、字符串长度函数length
语法：length(string A)
返回值：int
说明：返回字符串A的长度
select length('abcedfg') from tableName;
2、字符串反转函数：reverse
语法：reverse(string A)
返回值：string
说明：返回字符串A的反转结果
select reverse('abcedfg') from tableName;
3、字符串连接函数：concat
语法：concat(string A,string B..)
返回值：string
说明：返回输入字符串连接后的结果，支持任意个输入字符串
select concat('abc','def','gh') from tableName;
4、字符串连接并指定字符串分隔符：concat_ws
语法：concat_ws(string SEP,string A,string B..)
返回值：string
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
select concat_ws(',','abc','def','gh') from tableName;
5、字符串截取函数：substr
语法：substr(string A,int start),substring(string A,int start)
返回值：string
说明：返回字符串A从start位置到结尾的字符串
select substr('abcde',3) from tableName;
select substring('abcde',3) from tableName;
select substr('abcde',-1) from tableName;
6、字符串截取函数：substr,substring
语法：substr(string A,int start,int len),substring(string a,int start,int len)
返回值：string
说明：返回字符串A从start位置开始，长度为len的字符串
select substr('abcde',3,2) from tableName;
select substring('abcde',3,2) from tableName;
select substring('abcde',-3,2) from tableName;

7、字符串转大写函数：upper,ucase
语法：upper(string A) ucase(string A)
返回值：string
说明：返回字符串A的大写格式
select upper('abSed') from tableName;
select ucase('abSED') from tableName;

8、字符串转小写函数：lower,lcase
语法：lower(string A) lcase(string A)
返回值：string
说明：返回字符串A的小写格式
select lower('abSED') from tableName;
select ;case('abSED') from tableName;

9、去空格函数：trim
语法：trim(string A)
返回值：string
说明：去除字符串两边的空格
select trim(' ab c ') from tableNmae;

10、url解析函数 parse_url
语法：parse_url(string urlString,string partToExtract[,string keyToExtract])
返回值：string
说明：返回url中指定的部分。partToExtract的有效值为：HOSt，PATH，QUERY，REF，POTOCOL,AUTHORITY,FILE，and USERINFO
select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST')
from tableName;
select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')
from tableName;
11、json解析get_json_object
语法：get_json_object(string json_string,string path)
返回值：string
说明：解析json的字符串json_string，返回path指定的内容。如果输入的json字符串无效，那么返回NULL
select get_json_object('{"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} },"email":"amy@only_for_json_udf_test.net","owner":"amy"}','$.owner') from tableName;

12、重复字符串函数：repeat
语法：repeat(string str,int n)
返回值：string
说明：返回重复n次后的str字符串
select repeat('abc',5) from tableName;

13、分割字符串函数：split

语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str，会返回分割后的字符串数组

hive> select split('abtcdtef','t') from tableName;
["ab","cd","ef"]

2.6 集合统计函数

1、个数统计函数: count

语法: count(*), count(expr), count(DISTINCT expr[, expr_.])
返回值：Int
说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCT
expr[, expr_.])返回指定字段的不同的非空值的个数

hive> select count(*) from tableName;
20
hive> select count(distinct t) from tableName;
10

2、总和统计函数: sum

语法: sum(col), sum(DISTINCT col)
返回值: double
说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果

hive> select sum(t) from tableName;
100
hive> select sum(distinct t) from tableName;
70

3、平均值统计函数: avg

语法: avg(col), avg(DISTINCT col)
返回值: double
说明: avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值

hive> select avg(t) from tableName;
50
hive> select avg (distinct t) from tableName;
30

4、最小值统计函数: min

语法: min(col)
返回值: double
说明: 统计结果集中col字段的最小值

hive> select min(t) from tableName;
20

5、最大值统计函数: max

语法: max(col)
返回值: double
说明: 统计结果集中col字段的最大值
hive> select max(t) from tableName;
120

2.7 复合类型构建函数

1、Map类型构建: map

语法: map (key1, value1, key2, value2, …)
说明：根据输入的key和value对构建map类型
--建表
create table score_map(name string ,score map<string,int> ) row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':";
-- 创建数据内容如下并加载数据
cd /kkb/install/hivedatas/
vim score_map.txt

zhangsan 数学:80,语文:89,英语:95
lisi 语文:60,数学:80,英语:99

-- 加载数据到hive表当中去
load data local inpath '/kkb/install/hivedatas/score_map.txt' overwrite into table score_map;

-- map结构数据访问：
-- 获取所有的value：
select name,map_values(score) from score_map;

-- 获取所有的key：
select name,map_keys(score) from score_map;

-- 按照key来进行获取value值
select name,score["数学"] from score_map;

-- 查看map元素个数
select name,size(score) from score_map;

-- 构建一个map
select map(1, 'zs', 2, 'lisi');

2、Struct类型构建：struct
语法：struct(val1,val2,val3,..)
说明：根据输入的参数构建结构体struct类型，似于C语言中的结构体，内部数据通过X.X来获取，
--创建struct表
create table movie_score(name string,info struct<number:int,score:float>)
row format delimited fields terminated by "\t"
collection items terminated by ":";
--加载数据
cd /kkb/install/hivedatas/
vim struct.txt
--电影ABC，有12345评价过，打分7.4分
ABC 1254:7.4
DEF 256:4.9
XYZ 456:5.4
--加载数据
load data local inpath '/kkb/install/hivedatas/struct.txt" overwrite into table move_score;
--hive当中查询数据
select * from move_score;
select info.number,info.score from movie_score;

select struct(1,'anzhulababy','moon',1.68);

3、Array类型构建：array
语法：array(val1,val2,..)
说明：根据输入的参数构建数组array类型
create table person(name string,work_locations array<string>) row format delitmited fields terminated by '\t' collection iterms terminated by ',';
-- 加载数据到person表当中去
cd /kkb/install/hivedatas/
vim person.txt

-- 数据内容格式如下
biansutao beijing,shanghai,tianjin,hangzhou
linan changchun,chengdu,wuhan

-- 加载数据
hive > load data local inpath '/kkb/install/hivedatas/person.txt' overwrite into table person;

--查询所有数据
select * from person;
--按照下表索引进行查询
select work_locations[0] from person;
--查询所有集合数据
select work_locations from person;
--查询元素格式
select size(work_locations) from person;
--构建 array
select array(1,2,1);
select array(1,'a',1.0);
select array(1,2,1.0);

2.8 复杂类型长度统计函数

1、Map类型长度函数: size(Map<k .V>)

语法: size(Map<k .V>)
返回值: int
说明: 返回map类型的长度

hive> select size(map(1, 'zs', 2, 'anzhulababy')) from tableName;
2

2、array类型长度函数: size(Array<T>)

语法: size(Array<T>)
返回值: int
说明: 返回array类型的长度

hive> select size(t) from arr_table2;
4

3、类型转换函数

类型转换函数: cast
语法: cast(expr as <type>)
返回值: Expected "=" to follow "type"
说明: 返回转换后的数据类型

hive> select cast('1' as bigint) from tableName;
1

2.9 行转列

1、相关函数说明

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。
- 第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。
- 这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

2、数据准备

数据准备

name	constellation	blood_type
孙悟空	白羊座	A
老王	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
按住啦baby	射手座	A

3、需求

把星座和血型一样的人归类到一起。结果如下：

射手座,A            老王|按住啦baby
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋

4、创建表数据文件

node03服务器执行以下命令创建文件，注意数据使用\t进行分割

cd /kkb/install/hivedatas
vim constellation.txt

孙悟空 白羊座 A
老王  射手座 A
宋宋  白羊座 B       
猪八戒 白羊座 A
凤姐  射手座 A

5、创建hive表并导入数据

创建hive表并加载数据
create table person_info(name string,constellation string,blood_type string) row format delimited fields terminated by '\t';
加载数据
load data local inpath '/kkb/install/hivedatas/constellation.txt' into table person_info;
6、按需求查询数据
select t1.base,concat_ws('|',collect_st(t1.name)) name from (select name,concat(constellation,",",blood_type) base from person_info) t1 group by t1.base;
2.10列转行
1、函数说明
EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udft(expression) tableAlias AS columnAlias
解释：用于和split,explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
2、数据准备
数据内容如下，字段之间都是使用\t进行分割
cd /kkb/install/hivedatas
vim move.txt

《疑犯追踪》悬疑,动作,科幻,剧情
《Lie to me》悬疑,警匪,动作,心理,剧情
《战狼2》战争,动作,灾难
3、需求
将电影分类中的数据展开。结果如下：
《疑犯追踪》悬疑
《疑犯追踪》动作
《疑犯追踪》科幻
《疑犯追踪》剧情
《Lie to me》悬疑
《Lie to me》警匪
《Lie to me》动作
《Lie to me》心理
《Lie to me》剧情
《战狼2》战争
《战狼2》动作
《战狼2》灾难
4、创建hive表并导入数据
创建hive表
create table movie_info(movie string,categroy array<string>) row format delimited fields terminated by "\t" collection items terminated by ",";
加载数据
load data local inpath "/kkb/install/hivedatas/movie.txt" into table move_info;
5、按需求查询数据
select movie,catagory_name from move_info
lateral view exploade(cate gory) table_tmp as category_name;

2.11 lateral view、explode练习

1、explode函数将Map和Array字段数据进行拆分

explode还可以用于将hive一列中复杂的array或者map结构拆分成多行

-- 需求：现在有数据格式如下
zhangsan    child1,child2,child3,child4 k1:v1,k2:v2
lisi    child5,child6,child7,child8  k3:v3,k4:v4

-- 字段之间使用\t分割，需求将所有的child进行拆开成为一列
+----------+--+
| mychild  |
+----------+--+
| child1   |
| child2   |
| child3   |
| child4   |
| child5   |
| child6   |
| child7   |
| child8   |
+----------+--+

-- 将map的key和value也进行拆开，成为如下结果
+-----------+-------------+--+
| mymapkey  | mymapvalue  |
+-----------+-------------+--+
| k1        | v1          |
| k2        | v2          |
| k3        | v3          |
| k4        | v4          |
+-----------+-------------+--+

第一步：创建hive数据库
创建hive数据库
create database hive_explode;
use hive_explode;
第二步：创建hive表
create table hive_explode.t3(name string,children array<string>,address Map<string,string>) row format delimited fields terminated by '\t' collection items terminated by ',' map keys terminated by ':' stored as textFile;
第三步：加载数据
node03执行以下命令创建表数据文件
cd /kkb/install/hivedatas/
vim maparray
-- 数据内容格式如下
zhangsan child1,child2,child3,child4 k1:v1,k2:v2
lisi child5,child6,child7,child8 k3:v3,k4:v4
hive表当中加载数据
hive (hive_explode)> load data local inpath '/kkb/install/hivedatas/maparray' into table hive_explode.t3;

第四步：使用explode将hive当中数据拆开

将array当中的数据拆分开
select explode(children) AS myChild FROm hive_explode.t3;
将map当中的数据拆分开
select explode(address) AS (myMapKey,myMapValue) FROM hive_explode.t3;

2、使用explode拆分json字符串
需求：现在有一些数据格式如下：
a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]
其中字段与字段之间的分隔符是|
我们要解析得到所有的monthSales对应的值为以下这一列（行转列）
第一步：创建hive表
create table hive_explode.explode_lateral_view (area string,goods_id string,sale_info string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS textfile;
第二步：准备数据并加载数据
准备数据如下
cd /kkb/install/hivedatas
vim explode_json
a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

加载数据到hive表当中去
load data local inpath '/kkb/install/hivedatas/explode_json' overwrite into table hive_explode.explode_lateral_view;

第三步：使用explode拆分Array
select explode(split(goods_id,',')) as goods_id from hive_explode.explode_lateral_view;

第四步：使用explode拆解Map
select explode(split(area,',')) as area from hive_explode.explode_lateral_view;
第五步：拆解json字段
select explode(split(regexp_replace(regexp_replace(sale_info,'\[\{',''),'}]',''),'},\{')) as sale_info from hive_explode.explode_lateral_view;

然后我们想用get_json_object来获取key为monthSales的数据：
select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\[\{',''),'}]',''),'},\{')),'$.monthSales') as sale_info from hive_explode.explode_lateral_view;

-- 然后出现异常FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
-- UDTF explode不能写在别的函数内

-- 如果你这么写，想查两个字段，select explode(split(area,',')) as area,good_id from explode_lateral_view;
-- 会报错FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'good_id'
-- 使用UDTF的时候，只支持一个字段，这时候就需要LATERAL VIEW出场了
3、配合LATERAL VIEW使用
lateral view用于和split、explode等UDTF一起使用，能将一行数据拆分成多行数据
在此基础上可以对拆分的数据进行聚合
later view首先为原始表的每行调用UDTF,UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。
配合lateral view查询多个字段
select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,',')) goods as goods_id2;
其中LATeRAL VIEW explode(split(goods_id,','))goods相当于一个虚拟表，与原表explode_laterral_view笛卡尔积关联。
也可以多重使用，如下，也是三个笛卡尔积的结果
select goods_id2,sale_info,area2 from explode_lateral_view LATERAL VIEW explode(split(goods_id,',')) goods as goods_id2
LATERAL VIEW explode(split(area,','))area as area2;
最终，我们可以通过下面的句子，把这个json格式的一行数据，完全转换成二维表的方式展现
select get_json_object(concat('{',sale_info_1,'}'),' $.source') as source, get_json_object(concat('{',sale_info_1,'}'),'$ .monthSales') as monthSales, get_json_object(concat('{',sale_info_1,'}'),' $.userCount') as userCount, get_json_object(concat('{',sale_info_1,'}'),'$ .score') as score
from explode_lateral_view LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\[\{',''),'}]',''),'},\{'))sale_info as sale_info_1;

总结：Lateral View通常和UDTF一起出现，为了解决UDTF不允许在select字段的问题。
Multiple Lateral View可以实现类似笛卡尔乘积。
Outer关键字可以把不输出的UDTF的空结果，输出成NULL，防止丢失数据。

2.12 reflect函数
reflect函数可以支持在sql中调用java中的自带函数，秒杀一切udf函数。
1、使用java.lang.Math当中的Max求两列中最大值
创建hive表
create table test_udf(col1 int,col2 int) row format delimited fields terminated by ',';
准备数据并加载数据
cd /kkb/install/hivedatas
vim test_udf

1,2
4,3
6,4
7,5
5,6
加载数据
load data local inpath '/kkb/install/hivedatas/test_udf' overwrite into table test_udf;
使用java.lang.Math当中的Max求两列当中的最大值
select reflect("java.lang.Math","max",col1,col2) from test_udf;
2、不同记录执行不同的java内置函数
创建hive表
create table test_udf2(class_name string,method_name string,col1 int,col2 int) row format delimited fields terminated by ',';
准备数据
cd /export/servers/hivedatas
vim test_udf2

java.lang.Math,min,1,2
java.lang.Math,max,2,3
加载数据
load data local inpath '/kkb/install/hivedatas/test_udf2' overwrite into table test_udf2;
执行查询
select reflect(class_name,method_name,col1,col2) from test_udf2;
3、判断是否为数字

使用apache commons中的函数，commons下的jar已经包含在hadoop的classpath中，所以可以直接使用。
使用方式如下：
hive (hive_explode)> select reflect("org.apache.commons.lang.math.NumberUtils", "isNumber", "123");

2.13 分析函数—分组求topN

1、分析函数的作用

对于一些比较复杂的数据求取过程，我们可能就要用到分析函数
分析函数主要用于==分组求topN或者求取百分比，或者进行数据的切片==等等，我们都可以使用分析函数来解决

2、常用的分析函数

1、ROW_NUMBER()：

从1开始，按照顺序，给分组内的记录加序列；
- 比如，按照pv降序排列，生成分组内每天的pv名次,ROW_NUMBER()的应用场景非常多
- 再比如，获取分组内排序第一的记录;
- 获取一个session中的第一条refer等。

2、RANK() ：

生成数据项在分组中的排名，排名相等会在名次中留下空位

3、DENSE_RANK() ：

生成数据项在分组中的排名，排名相等会在名次中不会留下空位

4、CUME_DIST ：

小于等于当前值的行数/分组内总行数。比如，统计小于等于当前薪水的人数，所占总人数的比例

5、PERCENT_RANK ：

分组内当前行的RANK值/分组内总行数

6、NTILE(n) ：

用于将分组数据按照顺序切分成n片，返回当前切片值
如果切片不均匀，默认增加第一个切片的分布。
NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

3、需求描述

现有数据内容格式如下，分别对应三个字段，cookieid，createtime ，pv
求取每个cookie访问pv前三名的数据记录，其实就是分组求topN，求取每组当中的前三个值

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;" spellcheck="false" mdtype="fences" cid="n888">cookie1,2015-04-10,1
cookie1,2015-04-11,5
cookie1,2015-04-12,7
cookie1,2015-04-13,3
cookie1,2015-04-14,2
cookie1,2015-04-15,4
cookie1,2015-04-16,4
cookie2,2015-04-10,2
cookie2,2015-04-11,3
cookie2,2015-04-12,5
cookie2,2015-04-13,6
cookie2,2015-04-14,3
cookie2,2015-04-15,9
cookie2,2015-04-16,7</pre>

第一步：创建数据库表

在hive当中创建数据库表

第二步：准备数据并加载

node03执行以下命令，创建数据，并加载到hive表当中去

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" lang="shell" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;" spellcheck="false" mdtype="fences" cid="n898">cd /kkb/install/hivedatas
vim cookiepv.txt

cookie1,2015-04-10,1
cookie1,2015-04-11,5
cookie1,2015-04-12,7
cookie1,2015-04-13,3
cookie1,2015-04-14,2
cookie1,2015-04-15,4
cookie1,2015-04-16,4
cookie2,2015-04-10,2
cookie2,2015-04-11,3
cookie2,2015-04-12,5
cookie2,2015-04-13,6
cookie2,2015-04-14,3
cookie2,2015-04-15,9
cookie2,2015-04-16,7</pre>

加载数据到hive表当中去

第三步：使用分析函数来求取每个cookie访问PV的前三条记录

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;" spellcheck="false" mdtype="fences" cid="n904">select * from (
SELECT
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM cookie_pv
) temp where temp.rn1 <= 3;</pre>

2.14 hive自定义函数

1、自定义函数的基本介绍

Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）
根据用户自定义函数类别分为以下三种：
- UDF（User-Defined-Function）一进一出
- UDAF（User-Defined Aggregation Function）聚集函数，多进一出，类似于：count/max/min
- UDTF（User-Defined Table-Generating Functions）一进多出，如lateral view explode()

如lateral view explode()

官方文档地址

https://cwiki.apache.org/confluence/display/Hive/HivePlugins
编程步骤：

（1）继承org.apache.hadoop.hive.ql.UDF

（2）需要实现evaluate函数；evaluate函数支持重载；

注意事项

（1）UDF必须要有返回类型，可以返回null，但是返回类型不能为void；

（2）UDF中常用Text/LongWritable等类型，不推荐使用java类型；

2、自定义函数开发

第一步：创建maven java 工程，并导入jar包

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" lang="xml" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;" spellcheck="false" mdtype="fences" cid="n937"> <repositories>
<repository>
<id>cloudera</id>
<url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
</repository>
</repositories>

<dependencies>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>2.6.0-mr1-cdh5.14.2</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.6.0-cdh5.14.2</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-hdfs</artifactId>
<version>2.6.0-cdh5.14.2</version>
</dependency>

<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-mapreduce-client-core</artifactId>
<version>2.6.0-cdh5.14.2</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.1.0-cdh5.14.2</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>1.1.0-cdh5.14.2</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-cli</artifactId>
<version>1.1.0-cdh5.14.2</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.0</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>

</configuration>
</plugin>
</plugins>
</build></pre>

第二步：开发java类继承UDF，并重载evaluate 方法

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" lang="java" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;" spellcheck="false" mdtype="fences" cid="n939">package com.kkb.udf.MyUDF;

public class MyUDF extends UDF {
public Text evaluate(final Text s) {
if (null == s) {
return null;
}
//返回大写字母
return new Text(s.toString().toUpperCase());
}
}</pre>

第三步：项目打包

使用maven的package进行打包
将我们打包好的jar包上传到node03服务器的/kkb/install/hive-1.1.0-cdh5.14.2/lib 这个路径下

第四步：添加我们的jar包

重命名我们的jar包名称

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" lang="shell" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;" spellcheck="false" mdtype="fences" cid="n950">cd /kkb/install/hive-1.1.0-cdh5.14.2/lib
mv original-day_hive_udf-1.0-SNAPSHOT.jar udf.jar</pre>

hive的客户端添加我们的jar包

第五步：设置函数与我们的自定义函数关联

第六步：使用自定义函数

hive当中如何创建永久函数
在hive当中添加临时函数，需要我们每次进入hive客户端的时候都需要添加以下，退出hive客户端临时函数就会失效，那么我们也可以创建永久函数来让其不会失效
创建永久函数

<pre class="md-fences md-end-block ty-contain-cm modeLoaded" lang="sql" style="box-sizing: border-box; overflow: visible; font-family: var(--monospace); font-size: 0.9em; display: block; break-inside: avoid; text-align: left; white-space: normal; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(248, 248, 248); position: relative !important; border: 1px solid rgb(231, 234, 237); border-radius: 3px; padding: 8px 4px 6px; margin-bottom: 15px; margin-top: 15px; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;" spellcheck="false" mdtype="fences" cid="n966">-- 1、指定数据库，将我们的函数创建到指定的数据库下面
0: jdbc:hive2://node03:10000>use myhive;

-- 2、使用add jar添加我们的jar包到hive当中来
0: jdbc:hive2://node03:10000>add jar /kkb/install/hive-1.1.0-cdh5.14.2/lib/udf.jar;

-- 3、查看我们添加的所有的jar包
0: jdbc:hive2://node03:10000>list jars;

-- 4、创建永久函数，与我们的函数进行关联
0: jdbc:hive2://node03:10000>create function myuppercase as 'com.kkb.udf.MyUDF';

-- 5、查看我们的永久函数
0: jdbc:hive2://node03:10000>show functions like 'my';

-- 6、使用永久函数
0: jdbc:hive2://node03:10000>select myhive.myuppercase('helloworld');

-- 7、删除永久函数
0: jdbc:hive2://node03:10000>drop function myhive.myuppercase;

-- 8、查看函数
show functions like 'my';</pre>

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,185评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,445评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,684评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,564评论 1赞 284
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,681评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,874评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,025评论 3赞 408
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,761评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,217评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,545评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,694评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,351评论 4赞 332
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,988评论 3赞 315
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,778评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,007评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,427评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,580评论 2赞 349