Hive函数介绍:
最初Hive函数分为 内置函数 和 用户自定义函数两大类, 其中用户自定义函数又被分为3类, 分别是:
内置函数: 属于Hive自带的.
用户自定义函数:
UDF: 全称叫 User Defined Functions, 普通函数, 即: 一进一出.
例如: select * from stu;
UDAF: 全称叫 User Defined Aggregation Functions, 聚合函数, 即: 多进一出.
例如: select count(id) from stu;
UDTF: 全称叫 User Defined Table-Generating Functions, 表生成函数, 即: 一进多出.
例如: select explode(array(11, 22, 33));
后来发现用 UDF, UDAF, UDTF来划分Hive函数实在是太方便了, 于是提出了1个词, 叫: 函数标准扩大化, 即:
UDF, UDAF, UDTF本来是形容用户自定义函数的, 现在, hive中的函数没有内置函数 和 自定义函数之分了, 取而代之的是: UDF, UDAF, UDTF
问题: 如果某个函数我不会用怎么办?
答案: 查看Hive针对于该函数的 说明文档.
-- 查看Hive所有的内置函数 show functions ; -- 查看函数的说明信息 describe function 函数; -- 简单信息 describe function extended 函数; -- 详细信息,包括 测试案例(有的没有)
相关函数:
字符串相关函数:
1. 字符串切割
-- spilt(参数1,参数2) 参数1: 要操作的参数. 参数2: 切割符
select spilt("aa,bb,cc",","); -- ["aa","bb","cc"]
-- substr(参数1,参数2,参数3) 和 substring(参数1,参数2,参数3)
-- 参1: 要操作的字符串. 参2: 起始索引, 参数3: 个数
-- 效果一样, 没有区别
select substr('2024-01-13 11:51:27', 1, 10);
select substring('2024-01-13 11:51:27', 1, 10);
2. 字符串拼接
-- concat() 拼接符默认为空
select concat('aa','bb','cc'); aabbcc
-- concat_ws(参数1,参数2) 可以指定拼接符 参数1:拼接符 参数2:要拼接的内容
select concat_ws('-','aa','bb','cc'); aa-bb-cc
3. 获取字符串长度
select length('abc');
4. 转大小写
select lower('ABC'); -- abc 转小写
select upper('abc'); -- ABC 转大写
5. 移除首尾空格
select trim(' aa bb '); -- 'aa bb'
6. 正则替换, 参数1:要被处理的字符串. 参数2:正则表达式. 参数3:用来替换的内容
select regexp_replace('100-200', '\\d+', '夯哥');
7. URL解析.
select parse_url('http://www.itcast.cn/path/p1.php?query=1', 'HOST'); -- www.itcast.cn, 主机名.
select parse_url('http://www.itcast.cn/path/p1.php?query=1', 'PATH'); -- /path/p1.php, 资源路径
select parse_url('http://www.itcast.cn/path/p1.php?username=admin01&password=pwd111', 'QUERY'); -- username=admin01&password=pwd111, 访问参数
select parse_url('http://www.itcast.cn/path/p1.php?username=admin01&password=pwd111', 'QUERY', 'username'); -- admin01, username参数值.
-- 8. 处理json字符串, 它是一种特定格式的字符串, 主要用于 传输数据的, 把数据封装成json字符串格式, 在各个框架中进行传输.
-- json解析函数：get_json_object(json_txt, path)
-- 注意: 整个json字符串用单引号'包裹, json字符串中的键, 值用双引号"包裹.
-- json字符串的格式: {键:值, 键: 值}
-- json数组的格式: [{键:值, 键: 值}, {键:值, 键: 值}, {键:值, 键: 值}] -- 索引从 0 开始.
select get_json_object('{"name":"杨过", "age":"18"}', '$.name'); -- 杨过, $表示json对象
select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[1].name');
日期相关:
select current_date(); -- 获取当前时间, 年月日格式
select unix_timestamp(); -- 获取当前时间, unix格式, 即: 从时间原点(1970-01-01 00:00:00)截止到当前时间的 秒值.
select unix_timestamp('2024/01/12', 'YYYY/mm/dd'); -- 根据指点的时间, 获取其unix时间.
-- 根据秒值(unix时间) 获取其对应的 日期.
select from_unixtime(1684477644); -- 2023-05-19 06:27:24
-- 获取年月日.
select year('2023-05-19 14:31:02'); -- 2023
select month('2023-05-19 14:31:02'); -- 5
select day('2023-05-19 14:31:02'); -- 19
-- 时间相关操作, 增加, 减少, 比较.
select date_add('2023-05-19', 2); -- 2023-05-21
select date_add('2023-05-19', -2); -- 2023-05-17
select datediff('2023-05-19', '2023-05-20'); -- 前 - 后, -1
-- 扩展, 计算某年的2月有多少天.
-- 思路: 把时间设置为该年的3月1日, 然后往前推1天, 判断该天是月中的第几天, 即为结果.
select day(date_add('2023-03-01', -1));
select dayofmonth(date_add('2024-03-01', -1)); 数字相关函数:
select rand(); -- 0.0 ~ 1.0, 包左不包右. select round(4.3); -- 四舍五入, 原理: +0.5, 然后求地板数. select abs(-10); -- 求绝对值 select ceil(5.3); -- 天花板数, 比该数大的所有数字中, 最小的那个整数(包括本身) 6 select ceil(5.0); -- 天花板数, 比该数大的所有数字中, 最小的那个整数(包括本身) 5 select floor(5.6); -- 地板数, 比该数小的所有数字中, 最大的那个整数(包括本身) 5 -- 自己实现 四舍五入. select `floor`(5.3 + 0.5); -- 5
非空校验 相关函数:
-- if函数(条件表达式, 值1, 值2), 先执行条件表达式, 看起结果是否成立, 成立执行值1, 否则执行值2
select if(5 > 3, '郑州', '新乡'); -- 郑州
-- 非空校验.
select isnull('zz'); -- false
select isnull(null); -- true
select isnotnull('zz); -- true
select isnotnull(null); -- false
-- 空值转换, 判断第1个值是否为null, 如果不是就获取第1个值, 否则就用第2个值(类似于 默认值)
select nvl('zz', '张三'); -- zz
select nvl(null, '张三'); -- 张三
-- 非空查找 coalesce(值1, 值2....), 获取参数列表中, 第1个非null值
select coalesce(null, null, null); -- null
select coalesce(null, 11, 22); -- 11
-- case when转换.
select
case
when 5 > 3 then '大于'
when 5 < 3 then '小于'
else '等于'
end as eq ;
-- 如果只做等于判断, 可以变形为:
select
case 5
when 1 then '周1'
when 3 then '周3'
when 5 then '周5'
else '不知道周几, 睡迷糊了!'
end as week;
杂项函数 相关函数:
-- 类型转换相关.
select cast(10.3 as int); -- 10
select cast(10 as string); -- '10'
select cast('12.3' as double); -- 12.3
select cast('12.3a' as double); -- null
-- 获取哈希值.
select hash('张三'); -- -838675700
select mask_hash('张三'); -- 1d841bc0ee98309cb7916670b7f0fdef5f4c35150711a41405ef3633b56322cf
-- mask()函数, 脱敏函数, 顺序是: 大写字母, 小写字母, 数字
select mask('abc123ABC'); -- xxxnnnXXX, 默认: 大写字母X, 小写字母x, 数字n
select mask('abc123ABC', '大','小', '*'); -- xxxnnn***, 指定: 大写字母 大, 小写字母 小, 数字*
-- 对数组元素排序, 默认: 升序.
select sort_array(array(11, 33, 55, 22)); -- [11,22,33,55]
-- 加密函数.
select md5('pwd111'); -- 130353326a7bfab601f57757033b5b4a
行列转换入门:
-- 1. 建表, 添加表数据, 源数据格式为: "Chicago Bulls,1991|1992|1993|1996|1997|1998"
-- 处理后, 格式为: "Chicago Bulls", ""
create table the_nba_championship(
team_name string, -- 队名
champion_year array -- 夺冠年份
) row format delimited
fields terminated by ',' -- 切割后, 数据格式为: "Chicago Bulls", "1991|1992|1993|1996|1997|1998"
collection items terminated by '|'; -- 切割后, 数据格式为: "Chicago Bulls", ["1991", "1992", "1993", "1996", "1997", "1998"]
-- 2. 查看表数据.
select * from the_nba_championship;
-- 3. 需求, 把上述的数据, 按照 队名, 年份(只有1个值)的方式做打印, 例如:
-- 实际开发中: 侧视图 lateral view() 一般会和 explode() 结合使用.
/*
列名: team_name champion_year
第1行: Chicago Bulls 1991
第2行: Chicago Bulls 1992
第3行: Chicago Bulls 1993
...
*/
-- 3.1 想办法对数据进行炸开, 这里用到炸裂函数 explode();
select explode(array('aa', 'bb', 'cc'));
-- 3.2 对夺冠年份进行 炸裂操作.
select explode(champion_year) from the_nba_championship;
-- 3.3 到这, 我们发现数据(夺冠年份)已经炸开了, 那么直接和原表一起查询即可. 如下:
select team_name, explode(champion_year) from the_nba_championship; -- 报错
-- 4. 最终版, 通过侧视图存储 炸裂后的内容, 然后和原表数据一起查询即可.
-- 侧视图解释: lateral view, 理解为: 临时的存储炸裂的内容.
-- 侧视图格式: lateral view 炸裂函数 侧视图名 as 炸裂后的列名.
select a.team_name, b.champion_year from the_nba_championship a
lateral view explode(champion_year) b as champion_year; 行列转换之: 行转列:
-- 1. 建表, 上传源文件.
create table row2col2(
col1 string,
col2 string,
col3 int
)row format delimited fields terminated by '\t';
-- 2. 查询表数据.
select * from row2col2;
-- 3. 实现: 行转列.
/*
行转列涉及到的函数:
数据采集函数:
collect_list() 采集数据, 可重复, 有序
collect_set() 采集数据, 唯一, 无序.
数据拼接函数:
concat() 拼接符: 默认为空.
concat_ws() 拼接符: 可以自定义.
*/
select concat(1, 2, 3);
select concat_ws('-', 1, 2, 3); -- 报错, concat_ws()拼接的内容必须是字符串形式.
select concat_ws('-', cast(1 as string), cast(2 as string), cast(3 as string)); -- 报错, concat_ws()拼接的内容必须是字符串形式.
-- 最终写法: 行转列代码实现.
select
col1, col2,
-- cast(col3 as string) 把 col3内容转成 字符串, 方便后续 concat_ws()拼接.
-- collect_list(cast(col3 as string)) 把 col3列的内容, 全部采集到, 有序, 可重复.
-- concat_ws('-', collect_list(cast(col3 as string))) 把 上述的内容, 按照-, 拼接到一起.
concat_ws('-', collect_list(cast(col3 as string))) as col3
from row2col2 group by col1, col2; 行列转换之: 列转行:
-- 1. 建表, 上传源文件.
create table col2row2(
col1 string,
col2 string,
col3 string
)row format delimited fields terminated by '\t';
-- 2. 查询表数据.
select * from col2row2;
-- 3. 实现: 列转行.
-- 3.1 把 col3列给炸开.
select explode(split(col3, ',')) from col2row2; -- '1,2,3' => ['1', '2', '3']
-- 3.2 侧视图 lateral view + explode()炸裂函数实现 列转行.
select t1.col1, t1.col2, t2.col3 from col2row2 t1
lateral view explode(split(col3, ',')) t2 as col3;
处理json字符串:
-- 思路1: 先用字符串把json内容存储下来, 然后对其进行处理.
-- 源数据格式: '{"device":"device_30","deviceType":"kafka","signal":98.0,"time":1616817201390}'
-- 1. 建表, 上传表数据.
create table test1_json(
json string
);
-- 2. 查询表数据.
select * from test1_json;
-- 3. 处理json.
-- 3.1 get_json_object()函数处理, 只能逐个处理, 属于 UDF函数, 一进一出.
select
get_json_object(json, '$.device') as device,
get_json_object(json, '$.deviceType') as deviceType,
get_json_object(json, '$.signal') as signal,
get_json_object(json, '$.time') as `time`
from test1_json;
-- 3.2 json_tuple()函数处理, 批量处理, 属于 UDTF函数, 一进多出.
select
json_tuple(json, 'device', 'deviceType', 'signal', 'time') as (device, deviceType, signal, `time`)
from test1_json;
-- 3.3 上述json_tuple()的变形写法, 可以用侧视图(lateral view)来存储处理后的数据, 进行查询.
select device, deviceType, signal, `time` from test1_json
-- 侧视图格式 侧视图存储的内容 侧视图名字 侧视图中每一项(列)的别名
lateral view json_tuple(json, 'device', 'deviceType', 'signal', 'time') lv as device, deviceType, signal, `time`;
-- 思路2: 在建表的时候, 直接对 json字符串进行处理.
-- 1. 建表(直接对json进行处理, 采用指定的SerDe类, 即: JsonSerDe), 上传表数据.
create table test2_json(
device string,
deviceType string,
signal string,
`time` string
) row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'; -- 如果不用默认的LazySimpleSerDe类, 采用指定SerDe类处理.
-- 2. 查询表数据.
select * from test2_json;
cte表达式
概述:
全称叫 Common Table Expression, 公共表表达式, 用来(临时)存储表结果的, 后续可以重复使用.
格式:
with CTE表达式的别名 as (
被CTE所存储的内容, 即: SQL查询语句
)
select ... from cte表达式别名;
-- 1. CTE表达式入门.
with t1 as (
select * from stu
)
select * from t1;
-- 2. from风格.
with t1 as (
select * from stu
)
from t1 select name, age;
-- 3. 链式写法(链式编程)
with t1 as (select * from stu),
t2 as (select * from t1 where id > 95010),
t3 as (select id, name, gender,age from t2 where id > 95010)
select name, gender from t3;
-- 4. CTE表达式结合 union 使用.
with t1 as (
select * from stu
)
select * from t1
union all -- 合并, 不去重.
select * from t1 limit 3; -- 44条 => 3条
-- 5. 用表 把 CTE的结果 永久存储.
create table hg1 as
with t1 as (
select * from stu
)
select id, name, age from t1;
select * from hg1;
-- 6. 用视图 把 CTE的结果 "永久"存储.
create view hg2 as
with t1 as (
select * from stu
)
select id, name, age from t1;
select * from hg2;
窗口函数相关
概述:
窗口函数指的是 over()函数, 它可以结合特定的函数一起使用, 完成不同的功能.
目的/作用:
窗口函数 = 给表新增一列, 至于新增的内容是什么, 取决于窗口函数和什么函数一起使用.
格式:
能和窗口函数一起使用的函数 over(partition by 分组字段 order by 排序字段 rows between 起始行 and 结束行)
能和窗口函数一起使用的函数解释:
聚合函数: count(), sum(), max(), min(), avg()
排序函数: row_number(), rank(), dense_rank(), ntile()
其它函数: lag(), lead(), first_value(), last_value()
注意:
1. 窗口函数相当于给表新增一列, 至于新增的内容是什么, 取决于窗口函数和什么函数一起使用.
2. 如果不写partition by, 表示: 统计表中所有的数据, 如果写了 表示统计组内所有的数据.
3. 如果不写order by, 表示: 统计组内所有的数据, 如果写了, 表示统计组内第一行截止到当前行的数据.
4. rows between表示统计的范围, 它可以写的关键字如下:
unbounded preceding 第一行
unbounded following 最后一行
n preceding 向上几行
n following 向下几行
current row 当前行
5. ntile(数字)表示几分之几, 里边的数字表示把数据分成几份, 如果不够分, 优先参考最小分区.
例如: 7条数据分成3份, 则最终结果为: 1, 1, 1 2, 2 3, 3
-- ------------------------------------- 以下是窗口函数案例 窗口 + 聚合 --------------------------------
-- 补充概述: PV: Page View 页面浏览量, UV: user view, 用户访问数 IP: ip(独立访客)访问数
-- 例如: 我通过我电脑的谷歌浏览器访问了京东的10个页面, 通过Edge浏览器访问了京东的7个页面, 请问: PV, UV, IP分别是多少?
-- 答案: IP: 1个, UV: 2个, PV: 17个
-- 案例2: 演示 窗口函数 + 聚合函数一起使用.
-- 需求：求出网站总的pv数 所有用户所有访问加起来
-- 如果over()里边什么都不写, 默认操作的是: 表中 该列所有的数据.
select *, sum(pv) over() as total_pv from website_pv_info; -- 不写partition by 统计表中所有的数据.
-- 需求: 求出每个用户总pv数
-- 方式1: sum() + group by 一起使用.
select cookieid, sum(pv) total_pv from website_pv_info group by cookieid;
-- 方式2: 聚合函数 + 窗口函数一起使用.
-- 细节: 如果写了partition by(表示分组): 则默认操作 组内所有的数据.
select *, sum(pv) over(partition by cookieid) as total_pv from website_pv_info; -- 写partition by 统计组内所有的数据.
-- 细节: 如果写了order by(表示排序): 则默认操作 组内第一行 至 当前行的数据.
select *, sum(pv) over(partition by cookieid order by createtime) as total_pv from website_pv_info; -- 写order by 统计组内 第一行 截止到 当前行的数据..
-- 上述的代码, 等价于如下的内容:
select *, sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row ) as total_pv from website_pv_info;
-- 需求: 统计每个cookieID的pv(访问量), 只统计: 当前行及 向前3行 向后1行
select * from website_pv_info;
select *, sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following ) as total_pv from website_pv_info;
-- ------------------------------------- 以下是窗口函数案例 窗口 + 排序 --------------------------------
-- 需求: 根据点击量(pv)做排名, 组内排名.
-- 这里的排序函数指的是: row_number(), rank(), dense_rank(), 它们都可以做排名, 不同的是, 对相同值的处理结果.
-- 例如: 数据是100, 90, 90, 60, 则: row_number是: 1, 2, 3, 4, rank: 1, 2, 2, 4, dense_rank: 1, 2, 2, 3
select
*,
row_number() over (partition by cookieid order by pv desc) rn,
rank() over (partition by cookieid order by pv desc) rk,
dense_rank() over (partition by cookieid order by pv desc) drk
from website_pv_info;
-- 需求: 根据cookieID进行分组, 获取每组点击量最高的前4名数据, 这个就是经典的案例: 分组求TopN
-- Step1: 根据cookieID进行分组, 根据点击量进行排名.
select
*,
dense_rank() over (partition by cookieid order by pv desc) drk
from website_pv_info where drk <= 4; -- 报错.
-- 细节: where只能筛选表中已经有的列(数据)
-- Step2: 把上述的查询结果当做一张表, 然后从中获取我们要的数据即可.
with t1 as (
select
*,
dense_rank() over (partition by cookieid order by pv desc) drk
from website_pv_info
)
select * from t1 where drk <= 4;
-- ntile(数字,表示分成几份) 采用均分策略, 每份之间的差值不超过1, 优先参考最小的那个部分, 即: 7分成3份, 则是: 3, 2, 2
select
*,
ntile(3) over (partition by cookieid order by pv desc) nt
from website_pv_info;
-- 需求: 按照cookieid分组, 按照点击量降序排列, 只要每组前三分之一的数据.
with t1 as (
select
*,
ntile(3) over (partition by cookieid order by pv desc) nt
from website_pv_info
)
select * from t1 where nt = 1;
select * from website_pv_info;
-- ------------------------------------- 以下是窗口函数案例 窗口 + 其它 --------------------------------
-- 1. LAG 用于统计窗口内往上第n行值
-- 需求: 显示用户上一次的访问时间, 格式: lag(字段, n, 默认值) 向上获取字段的第n个值, 如果没有写写默认值, 找不到就是null, 如果写了默认值, 找不到就用默认值.
select
*,
-- 向上1个, 找到就显示, 找不到就显示为 null
lag(createtime) over(partition by cookieid order by createtime) `lag1`,
-- 向上2个, 找到就显示, 找不到就用默认值: '2023-05-20 10:52:05'
lag(createtime, 2, '2023-05-20 10:52:05') over(partition by cookieid order by createtime) `lag2`
from website_url_info;
-- 根据cookieID分组, createtime升序排序, 获取当前行 向上2行的createtime列的值, 找不到就用默认值(夯哥)填充.
-- 2. LEAD 用于统计窗口内往下第n行值
select
*,
lead(createtime) over(partition by cookieid order by createtime) `lead1`,
lead(createtime, 2, '夯哥') over(partition by cookieid order by createtime) `lead2`
from website_url_info;
-- 3. FIRST_VALUE 取分组内排序后，截止到当前行，第一个值
select
*,
first_value(createtime) over(partition by cookieid order by createtime) `first_value`
from website_url_info;
-- 4. LAST_VALUE 取分组内排序后，截止到当前行，最后一个值
select
*,
last_value(createtime) over(partition by cookieid order by createtime) `last_value`
from website_url_info;