Hive函数详解

函数分类

Hive函数划分介绍:
? ? 函数介绍:
? ? ? ? 函数就是开发者定义好的规则, 我们只要调用指定的函数, 传入对应的参数, 就能获取对应的结果, 我们无需关心函数内部是如何执行的, 只要会调用即可.
? ? ? ? 可以把函数理解为是人们研发的机器, 不同的机器所需的原材料(参数)是不一样的, 传入不同的原材料(参数), 就会获取不同的结果.
? ? 概述:
? ? ? ? 最初Hive的函数分为 内置函数 和 用户自定义函数两大类, 而用户自定义函数又分为 UDF, UDAF, UDTF三种.
? ? ? ? 后来人们(程序员)发现用 UDF, UDAF, UDTF来划分hive函数太方便精准了, 于是在Hive2.X版本中提出了1个概念: 函数标准扩大化.
? ? ? ? 即: 从Hive2.4X开始, Hive中的函数就只有: UDF, UDAF, UDTF三种了.
? ? 分类:
? ? ? ? UDF: ? ?全称 user defined functions, 也叫: 普通函数.
? ? ? ? ? ? 核心: 一进一出. ?即: 给函数10行数据, 处理之后, 返回10行.
? ? ? ? ? ? 例如:?

select abs(-10);    -- 10
select round(10.123, 2);    -- 10.12

? ? ? ? UDAF: ? 全称 user defined Aggregate functions, 也叫: 聚合函数.
? ? ? ? ? ? 核心: 多进一出, 即: 给函数10行数据, 处理之后, 返回1行.
? ? ? ? ? ? 例如:

select count(sid) from student;

? ? ? ? UDTF: ? 全称 user defined Table Generating functions, 也叫: 表生成函数.
? ? ? ? ? ? 核心: 一进多出, 即: 给函数1条, 它返回多条.
? ? ? ? ? ? 例如:?

select explode(array('aa', 'bb', 'cc')); 

如果某个函数不会用, 可以查看下它的说明文档,
官网网址:?https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-MathematicalFunctions

函数基础语法:

show functions ;        -- 查看hive所有的函数, hive中符号也是函数, 只不过是: 函数名为符号而已.
describe function abs;              -- 查看函数的说明文档(简单信息)
describe function extended abs;     -- 查看函数的说明文档(详细信息).

---

Hive函数-字符串相关函数

1.sqlit函数

演示:split(参数1,参数2)函数, 参数1:要切割的字符串,参数2:切割符(支持正则)

select split('a1b2c3d', '1');           -- ["a","b2c3d"], 按照数字1对字符串切割
select split('a1b2c3d', '[0-9]');       -- ["a","b","c","d"], 按照数字对字符串切割
select split('a1b2c3d', '\\d+');      -- ["a","b","c","d"], 按照数字对字符串切割

2.concat()函数

concat()函数,默认的拼接符数:' ',可以拼接任意类型

select concat('-', 'aa', 'bb', 11, 'false'); -- -aabb11false

3.concat_ws()函数

?concat_ws()函数, 可以指定拼接符. 参1: 拼接符号, 参2.... 要拼接的内容.

select concat_ws('-', 'aa', 'bb', 'cc', array('x', 'y', 'z')); -- 只能拼字符串, 或者字符串数组      aa-bb-cc-x-y-z

4.trim()函数

trim()函数, 移除两端空格.

select trim('    aa   bb     ');           -- 'aa   bb'

5.substr()函数

substr()函数, 参1: 要操作的字符串, 参2:从几开始截取,默认从1开始计数. ?参3: 截取几个.

select substr('hello world hive', 7);    -- world hive, 从7开始, 截取到结尾.
select substr('hello world hive', 7, 5);    -- world hive, 从7开始, 截取5个
select substr('hello world hive', -4);    -- hive, 从-4索引(从右往左数, 从-1开始)
select substr('hello world hive', 13);    -- hive, 从13索引开始.

select substr('2024-01-14 10:16:19', 1, 10);       -- 年月日
select substr('2024-01-14 10:16:19', 6, 2);        -- 月
select substr('2024-01-14 10:16:19',9, 2);         -- 日

6. substring()函数

substring()函数, 效果同substr()函数一样

select substring('2024-01-14 10:16:19', 1, 10);       -- 年月日

7.lower()函数

select lower('ABC123');     -- abc123

8.upper()函数

select upper('abc123');     -- ABC123

9.regexp_replace()函数

regexp_replace()函数, 正则替换, 参1: 要操作的字符串, 参2: 正则表达式, 参3: 替换后的内容.

select regexp_replace('aa11bb22cc33dd', '\\d+', '#');

10.parse_url()函数

parse_url()函数, 从URL地址中解析出指定的内容. ?参1: url地址, 参2: 要解析出的内容, 例如: HOST, PATH, QUERY

--                          域名          文件路径                                   参数
select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'HOST'); -- www.itcast.cn
select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'PATH'); -- /index.html
select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'QUERY'); -- username=root&password=123456'
select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'QUERY', 'username'); -- root

11.get_json_object()函数

get_json_object(), 处理json字符串的. ? 参1: json字符串, 参2: 从json中解析出对应内容, 可以用 $代表整个json字符串 或者 json数组.

json字符串介绍:
? ? 概述:
? ? ? ? 它是一种特殊规则的字符串, 由键值对组成. 只要用双引号包裹.
? ? 格式:
? ? ? ? {"键":"值", "键":"值"}
? ? 作用:
? ? ? ? 一般是前后端传输数据的, 跨域传输, 例如: 前端把数据封装好 然后传入到 后台代码中.
? ? 细节:
? ? ? ? json字符串也有数组形式: ?[{"键":"值", "键":"值"}, {"键":"值", "键":"值"}...]

select get_json_object('{"name":"乔峰", "age":31}', '$.name');    -- 乔峰
select get_json_object("{'name':'乔峰', 'age':31}", '$.name');    -- null, json字符串用双引号包裹.
-- json数组
select get_json_object('[{"name":"乔峰", "age":31}, {"name":"虚竹", "age":29}]', '$.[1].name');    -- 虚竹, 索引从0开始.

---

Hive函数--日期相关函数

1. 获取当前的时间戳.

select `current_timestamp`();       -- 2024-01-14 10:57:23.763000000

2. 获取当前的时间.

select `current_date`();    -- 2024-01-14

3. 获取当前的秒值

即: 从时间原点(1970年1月1日 00:00:00) 至 当前时间的秒值

select unix_timestamp();                        -- 1705201180
select unix_timestamp('1970-01-01 00:00:01');   -- 1, 获取时间原点 ~ 指定时间的秒值.

4. 把秒值转成对应的时间.

select from_unixtime(1705201180);       -- 2024-01-14 02:59:40

5. 根据时间戳, 获取指定的日期: 年-月-日

select to_date(`current_timestamp`());

6. 计算下你活了多少天.

? ? 当前时间的秒值 ?- ? 你出生那天的秒值

select unix_timestamp() - unix_timestamp('2002-08-07 00:00:00');                    -- 676523149秒
select (unix_timestamp() - unix_timestamp('2002-08-07 00:00:00')) / 3600 / 24;      -- 7830.129537037036....

7. 计算两个日期的差值.

select datediff('2024-01-10 00:00:00', '2024-01-05 00:00:00');  --  结果: 前 - 后, 5

8. 从日期中获取对应的内容.

select year('2024-01-10 13:14:21');
select month('2024-01-10 13:14:21');
select day('2024-01-10 13:14:21');
select hour('2024-01-10 13:14:21');
select minute('2024-01-10 13:14:21');
select second('2024-01-10 13:14:21');
select quarter('2024-01-10 13:14:21');  

9. 偏移指定的时间.

select date_add('2024-01-10 13:14:21', 2);    -- 往后推2天, 2024-01-12
select date_sub('2024-01-10 13:14:21', -2);   -- 往后推2天, 2024-01-12,      subtract: 减法

select date_add('2024-01-10 13:14:21', -2);  -- 往前推2天, 2024-01-08
select date_sub('2024-01-10 13:14:21', 2);   -- 往前推2天, 2024-01-08,      subtract: 减法

---

Hive函数--数字相关

1. 求绝对值abs()

select abs(-10);    -- 10

2. 四舍五入?round()

select round(10.312, 2);    -- 10.31
select round(10.315, 2);    -- 10.32

3. 随机数rand()

select rand();      -- 0.0 ~ 1.0之间, 包左不包右.
-- 新需求: 获取1个 1 ~ 100之间的随机数
select int(rand() * 100) + 1;   -- rand() * 100 获取 0.0 ~ 100.0 之间, 包左不包右的数字.

4. ceil() 天花板数

比这个数字大的所有数字中, 最小的那个整数.

select ceil(10.3);  -- 11
select ceil(10.0);  -- 10

5. floor() 地板数.

select floor(10.3);  -- 10
select floor(10.0);  -- 10

6. pow(a, b)

计平方值, a的b次幂

select pow(3, 2);   -- 9

---

Hive函数--转换相关

1. if()函数

做判断的, 条件成立返回值1, 条件不成立返回值2. ?参1: 关系表达式, 参2: 值1, 参3: 值2

select if(5 > 3, '成立', '不成立');

2. isnull() 判断是否为空

select isnull('');      -- false
select isnull(null);    -- true

3. isnotnull() 判断是否不为空.

select isnotnull('');      -- true
select isnotnull(null);    -- false

4. nvl() 空值转换

判断参1是否为空, 不为空就返回其值, 为空就返回值2.

select nvl('乔峰', '虚竹');     -- 乔峰
select nvl(null, '虚竹');      -- 虚竹

5. coalesce(值1, 值2...)

获取众多参数中的第1个非空值.

select coalesce(null, null, null);              -- null
select coalesce(null, null, 1, 2, 3);           -- 1
select coalesce(null, 'y', null, 'a', '10');    -- y
select coalesce('a', '10', 'b', '20');          -- 'a'

6. case when

select
    case
        when 5 > 3 then '大于'
        when 5 < 3 then '小于'
        else '等于'
    end as col1;

7. cast()函数, 类型转换.

select cast(10.3 as int);   -- 10
select cast(10.6 as int);   -- 10
select cast(10 as string);  -- '10'
select cast('123' as int);  -- 123, 字符串 -> 数字
select cast('123' as boolean);  -- true, 非空 '', 非0, 非null
select cast('' as boolean);  -- false

---

Hive函数--脱敏,加密相关

1. 获取哈希值.

select hash('abc');     -- 96354

2. 加密相关函数.

select md5('Aa1');    -- 698d51a19d8a121ce581499d7b701668
select sha1('Aa1');   -- f55cd2f8d3186c960aa86782452979118760e96d
select sha2('Aa1', 512);   -- 2907b40d2cb5527fe522b1c97f90dbf49423dd0cfd411f5d91528bc38c0c2ee9515d981816149d138ffa9d2ac80ef1d0435dc0213bdb85b2f85f446c4a035f3f
describe function extended sha2;
select crc32('Aa1');       -- 2736874553

3. 脱敏相关.

select mask('xyzABC123');       --结果为: xxxXXXnnn,  默认顺序是: 大写字母X, 小写字母x, 数字n
-- 需求: 我想指定小写字母用 小 来替换.   参1: 要脱敏的字符串.  参2: 大写字母替换符, 参3: 小写字母替换符, 参4: 数字替换符.
select mask('xyzABC123', 'X', '小');  -- xxx小小小nnn

4. 只脱敏前几个字符.

select mask_first_n('ABCabc123', 4);        -- XXXxbc123 只处理前 4 个字符.
select mask_show_first_n('ABCabc123', 4);   -- ABCaxxnnn 除了前 4 个字符, 其它都处理.

?5. 需求: 把13112345678 => 131****5678

select concat(substr('13112345678', 1, 3), mask(substr('13112345678', 4, 4), 'X', 'x', '*'), substr('13112345678', -4));

6. sort_array()函数

对数组进行排序, 属于: 集合函数.

 select sort_array(array(11, 33, 22, 55));      -- [11,22,33,55]

---

Hive函数--其它函数

1. 查看当前的用户

select current_user();      -- root

2. 查看当前在使用的数据库.

select current_database();

3. 查看当前的hive版本.

select version();       -- 3.1.2 

---