【Hive复杂数据类型和函数】全网总结最全的Hive函数

一、复杂数据类型的建表语句

1、array

create table arr1(
  name string,
  scores array<int>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','   -- 集合的分隔符

2、map

create table map1(
    name string,
    scores map<string,int>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';

3、struct

create table if not exists struct1(
name string,
score struct<chinese:int,math:int,english:int,natrue:int>
)
row format delimited 
fields terminated by '\t'
collection items terminated by ',';

二、Hive函数

1、炸裂函数explode（行转列）

select name,subject,chengji 
from map1 
lateral view explode(scores) myscore as subject,chengji;

lateral view explode(scores) 是一个炸出来的虚拟表，myscore是虚拟表的别名，subject,chengji为炸出来列的别名

2、日期函数

current_date();   -- 当前系统日期 ：yyyy-MM-dd
current_timestamp();  -- 当前系统日期：yyyy-MM-dd HH:mm:ss.ms
unix_timestamp();  -- 日期转时间戳，可以指定输入日期格式
from_unixtime();  -- 时间戳转日期，可以指定输出日期格式
datediff();  -- 求两个时间的差值
last_day();  -- 求本月的最后一天
to_date();  --字符串转成日期格式
date_format();  -- 日期转成字符串格式

3、字符串函数

concat(subject,":",cj)  -- 将subject和cj字段用：拼接起来
collect_list()   -- 将括号里面的数据存放到一个list集合中，有序的
collect_set()    -- 将括号里面的数据存放到一个set集合中，无序的
concat_ws(',',list)   -- 将list集合中的数据以逗号拼接起来，变成字符串
str_to_map()    -- 将字符串变为map集合
replace('a,b,c,d,f',',','&');  -- 替换

4、类型转换函数

cast(‘123’ as int); – 将字符串类型转换为 int 类型

5、其他函数

nvl();  -- 相当于mysql中的ifnull()
get_json_object('{"name":"jack","age":19}','$.age');  --从json中获取指定的值
parse_url('http://www.baidu.com/path1/path2?k1=v1&k2=v2','HOST');  
-- 解析一个字符串中的url参数 第二个参数可以是 HOST（域名）、 PROTOCOL（协议）、PATH（路径）、QUERY（参数）以及值
coalesce();  -- 返回第一个不为空的数据

6、窗口函数

既要明细信息，也要聚合信息，就直接开窗

语法：

distribute by ... sort by = partition by ... order by

window 子句：

如果要对窗口的结果做更细粒度的划分,那么就使用window子句,常见的有下面几个
preceding：往前
following：往后
currect row ：当前行
unbounded preceding ：起点
unbounded following ：终点

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 t_order;

7、序列函数

ntile函数：

ntile 是Hive很强大的一个分析函数。可以看成是：它把有序的数据集合 平均分配 到 指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1

select name,orderdate,cost,
ntile(3) over(partition by name) -- 按照name进行分组,在分组内将数据切成3份
from t_order

lag和lead函数：

lag返回当前数据行的前第n行的数据 语法：lag(colName,n[,default value]): 取字段的前第n个值。如果为null,显示默认值 lead返回当前数据行的后第n行的数据

first_value和last_value函数：

first_value 取分组内排序后，截止到当前行，第一个出现的值 last_value 分组内排序后，截止到当前行，最后一个出现的值

8、排名函数

row_numbe()：

row_number从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列

效果如下：
98 1
97 2
97 3
96 4
95 5
95 6

没有并列名次情况，顺序递增

rank()：

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

效果如下：
98 1
97 2
97 2
96 4
95 5
95 5
94 7
有并列名次情况，顺序跳跃递增
dense_rank():

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

效果如下：
98 1
97 2
97 2
96 3
95 4
95 4
94 5
有并列名次情况，顺序递增

9、自定义函数

需要java代码 去extends GenericUDF类，并实现里面的三个方法

hive自定义函数中无法返回集合数据类型