掌握Hive函数[1]：从基础到高级应用

目录

 函数简介

 单行函数

 算术运算函数

 数值函数

 字符串函数

 日期函数

 流程控制函数

 集合函数

 案例演示

---

 函数简介

Hive将常用的逻辑封装成函数供用户使用，类似于Java中的函数。这样做的好处是可以避免用户反复编写相同的逻辑代码，可以直接调用这些函数。重点在于用户需要知道函数的名称及其功能。Hive提供了大量的内置函数，这些函数可以大致分为以下几类：单行函数、聚合函数、炸裂函数（Explode函数）和窗口函数。

以下命令可用于查询Hive内置函数的相关信息：

1. 查看系统内置函数

   hive> show functions;

2. 查看内置函数用法

   hive> desc function upper;

3. 查看内置函数详细信息

   hive> desc function extended upper;

 单行函数

单行函数的特点是一进一出，即输入一行数据，输出一行数据。

 算术运算函数

• 加法 A + B
• 减法 A - B
• 乘法 A * B
• 除法 A / B
• 取模 A % B
• 位与 A & B
• 位或 A | B
• 位异或 A ^ B
• 位非 ~A

案例实操： 查询所有员工的薪水后加1显示。

hive (default)> select sal + 1 from emp;

 数值函数

• round: 四舍五入

  hive> select round(3.3);   3

• ceil: 向上取整

  hive> select ceil(3.1);   4

• floor: 向下取整

  hive> select floor(4.8);  4

 字符串函数

• substring: 截取字符串
  • 语法一：substring(string A, int start)
  • 语法二：substring(string A, int start, int len)
• replace: 替换

  hive> select replace('lzl', 'a', 'A');

• regexp_replace: 正则替换

  hive> select regexp_replace('100-200', '(\\d+)', 'num');

• regexp: 正则匹配

  hive> select 'dfsaaaa' regexp 'dfsa+';

• repeat: 重复字符串

  hive> select repeat('123', 3);

• split: 字符串切割

  hive> select split('a-b-c-d','-');

• nvl: 替换null值

  1hive> select nvl(null,1);

• concat: 拼接字符串

  hive> select concat('beijing','-','shanghai','-','shenzhen');

• concat_ws: 以指定分隔符拼接字符串

  hive> select concat_ws('-',array('beijing','shenzhen','shanghai'));

• get_json_object: 解析json字符串

  hive> select get_json_object('[{"name":"大海海","sex":"男","age":"25"},{"name":"小宋宋","sex":"男","age":"47"}]','$.[0]');

 日期函数

• unix_timestamp: 返回当前或指定时间的时间戳

  hive> select unix_timestamp('2022/08/08 08-08-08','yyyy/MM/dd HH-mm-ss');

• from_unixtime: 转化UNIX时间戳到时间格式

  hive> select from_unixtime(1659946088);

• current_date: 当前日期

  hive> select current_date;

• current_timestamp: 当前的日期加时间，并且精确到毫秒

  hive> select current_timestamp;

• month: 获取日期中的月

  hive> select month('2022-08-08 08:08:08');

• day: 获取日期中的日

  hive> select day('2022-08-08 08:08:08');

• hour: 获取日期中的小时

  hive> select hour('2022-08-08 08:08:08');

• datediff: 两个日期相差的天数

  hive> select datediff('2021-08-08','2022-10-09');

• date_add: 日期加天数

  hive> select date_add('2022-08-08',2);

• date_sub: 日期减天数

  hive> select date_sub('2022-08-08',2);

• date_format: 将标准日期解析成指定格式字符串

  hive> select date_format('2022-08-08','yyyy年-MM月-dd日');

 流程控制函数

• case when: 条件判断函数

  hive> select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from table_name;

• if: 条件判断

  hive> select if(10 > 5,'正确','错误');

 集合函数

• size: 集合中元素的个数

  hive> select size(friends) from test;

• map: 创建map集合

  hive> select map('xiaohai',1,'dahai',2);

• map_keys: 返回map中的key

  hive> select map_keys(map('xiaohai',1,'dahai',2));

• map_values: 返回map中的value

  hive> select map_values(map('xiaohai',1,'dahai',2));

• array: 声明array集合

  hive> select array('1','2','3','4');

• array_contains: 判断array中是否包含某个元素

  hive> select array_contains(array('a','b','c','d'),'a');

• sort_array: 将array中的元素排序

  hive> select sort_array(array('a','d','c'));

• struct: 声明struct中的各属性

  hive> select struct('name','age','weight');

• named_struct: 声明struct的属性和值

  hive> select named_struct('name','xiaosong','age',18,'weight',80);

 案例演示

1. 数据准备

   • 表结构

     name	sex	birthday	hiredate	job	salary	bonus	friends	children

   • 建表语句

     create  table  employee(
         name string,  --姓名
         sex  string,  --性别
         birthday string, --出生年月
         hiredate string, --入职日期
         job string,   --岗位
         salary double, --薪资
         bonus double,  --奖金
         friends array<string>, --朋友
         children map<string,int> --孩子
     );

   • 插入数据

     hive> insert into employee  values(...);

2. 需求

   • 统计每个月的入职人数

     select
       month(replace(hiredate,'/','-')) as month,
       count(*) as cn
     from
       employee
     group by
       month(replace(hiredate,'/','-'));

   • 查询每个人的年龄（年 + 月）

     -- 转换日期
     select
       name,
       replace(birthday,'/','-') birthday
     from
       employee;
     
     -- 求出年和月
     select
       name,
       year(current_date())-year(t1.birthday) year,
       month(current_date())-month(t1.birthday) month
     from
       (
         select
           name,
           replace(birthday,'/','-') birthday
         from
           employee
       )t1;
     
     -- 根据月份正负决定年龄
     select
       name,
       concat(if(month>=0,year,year-1),'年',if(month>=0,month,12+month),'月') age
     from
       (
         select
           name,
           year(current_date())-year(t1.birthday) year,
           month(current_date())-month(t1.birthday) month
         from
           (
             select
               name,
               replace(birthday,'/','-') birthday
             from
               employee
           )t1
       )t2;

   • 按照薪资，奖金的和进行倒序排序，如果奖金为null，置为0

     select
       name,
       salary + nvl(bonus,0) sal
     from
       employee
     order by
       sal desc;

   • 查询每个人有多少个朋友

     select 
       name,
       size(friends) cnt
     from 
       employee;

   • 查询每个人的孩子的姓名

     select 
       name,
       map_keys(children) ch_name
     from 
       employee;

   • 查询每个岗位男女各多少人

     select
       job,
       sum(if(sex='男',1,0)) male,
       sum(if(sex='女',1,0)) female
     from
       employee
     group by 
       job;