Hive函数

wxin_@

于 2022-10-20 19:59:33 发布

阅读量629

点赞数

文章标签：数据库 hive

本文链接：https://blog.csdn.net/m0_46410349/article/details/127421766

版权

1.HIVE常用函数

1.1类型转换

"hello"并不是一个数字类型所以会显示NULL

1.2切割

注：后面的直接写竖线就会将每个字母隔开

1.3 正则表达式截取字符串

正则表达式解析函数：regexp_extract
语法: regexp_extract(string subject, string pattern, int index)。将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
举例：select regexp_extract('hello<B>nice</B>haha','<B>(.*)</B>',1) ##返回值为the
注:在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则

1.4将字符串前后出现的空格去掉

去空格函数：trim
语法: trim(string A) 去除字符串两边的空格

1.5求指定列的聚合函数

总和统计函数: sum
语法: sum(col), sum(DISTINCT col)。sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果
平均值统计函数: avg
语法: avg(col), avg(DISTINCT col)。avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值
最小值统计函数: min
语法: min(col)。统计结果集中col字段的最小值
最大值统计函数: max
语法: max(col)。统计结果集中col字段的最大值

1.6拼接字符串

字符串连接函数：concat
语法: concat(string A, string B…)。返回输入字符串连接后的结果，支持任意个输入字符串

1.7字符串的截取

字符串截取函数：substr,substring
语法: substr(string A, int start),substring(string A, int start)。返回字符串A从start位置到结尾的字符串
举例：select substr(‘abcde’,3) from dual; ##返回值cde
字符串截取函数：substr,substring
语法: substr(string A, int start, int len),substring(string A, int start, int len)。返回字符串A从start位置开始，长度为len的字符串
举例：select substr(‘abcde’,3,2) from dual; ##返回值为cd

1.8炸裂函数

select explode(split("nice|good|well","\\|"));

实例

1.9日期处理函数

(1)date_format函数（根据格式整理日期）

yyyy-MM-dd HH:mm:ss

(2)date_add函数（加减日期）

日期增加函数: date_add
语法: date_add(string startdate, int days)。返回开始日期startdate增加days天后的日期。
举例：select date_add(‘2022-10-19’,-2) from dual; ##返回值为2020-10-19

(3)next_day函数

a.取当前天的下一个周一

b.取当前周的周一

c.last_day函数（求当月最后一天日期）

2.处理json数据

json解析函数：get_json_object
语法: get_json_object(string json_string, string path)

解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL

a.创建表，并load数据。

create table json(data string);

load data local inpath '/usr/datdadir/json.txt' into table json;

b. 查询json数据。

select get_json_object(data,'$.movie') as movie,

get_json_object(data,'$.rate') as rate

from json；

3.窗口函数

窗口聚合函数：SUM、AVG、MAX、MIN
以SUM为例
总结
功能：用于实现数据分区后的聚合
语法：fun_name(col1) over (partition by col2 order by col3)
示例：实现分区内的累加，其他的原理类似
不指定窗口时,不排序默认第一行到最后一行,排序默认第一行到当前行
指定窗口时 --rows between 起始位置 and 结束位置
N preceding：往前多少行
N following：往后多少行
current row：当前行
unbounded：起点或者终点，没有边界
unbounded preceding 表示从前面的起点
unbounded following：表示到后面的终点
注意：
分组列表，组内按排列顺序求累加和, 每组的相同level（排序字段）分享同一累加值。
如果level（排序字段）有相同，需要注意是否是你想要的结果，是否需要考虑指定窗口位置

3.1窗口分片

ntile(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
ntile不支持ROWS BETWEEN，

ntile(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均匀，默认增加第一个切片的分布
这个函数用什么应用场景呢?假如我们想要每位顾客购买金额前1/3的交易记录,我们便可以使用这个函数.

select name,orderdate,cost,
       ntile(3) over() as sample1 , --全局数据切片
       ntile(3) over(partition by name), -- 按照name进行分组,在分组内将数据切成3份
       ntile(3) over(order by cost),--全局按照cost升序排列,数据切成3份
       ntile(3) over(partition by name order by cost ) --按照name分组，在分组内按照cost升序排列,数据切成3份

3.2窗口排序

hive排序窗口函数三种方式：
row_number() over()
rank() over()
dense_rank() over()

总结：
row_number() over()：从1开始，按照顺序，生成分组内记录的序列
rank() over()：生成数据项在分组中的排名，排名相等会在名次中留下空位
dense_rank() over()：生成数据项在分组中的排名，排名相等会在名次中不会留下空位

3.3上下移动

LAG(col,n,DEFAULT)

用于将当前列往上移n行

第一个参数为列名。

第二个参数为往上第n行（可选，默认为1）。

第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）。

select 
  cookieid, 
  createtime, 
  url, 
  row_number() over (partition by cookieid order by createtime) as rn, 
  LAG(createtime,1,'1970-01-01 00:00:00') over (partition by cookieid order by createtime) as last_1_time, 
  LAG(createtime,2) over (partition by cookieid order by createtime) as last_2_time 
from cookie3;

LEAD(col,n,DEFAULT)
与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

select 
  cookieid, 
  createtime, 
  url, 
  row_number() over (partition by cookieid order by createtime) as rn, 
  LEAD(createtime,1,'1970-01-01 00:00:00') over (partition by cookieid order by createtime) as next_1_time,
  LEAD(createtime,2) over (partition by cookieid order by createtime) as next_2_time 
from cookie3;

3.4首尾值

FIRST_VALUE(url) over ()

分组排序后截至到当前行的第一个值。

FIRST_VALUE(url) over (partition by cookieid order by createtime desc) as last1

LAST_VALUE(url) over ()

分组排序后截至到当前行的最后一个值。

FIRST_VALUE(url) over (partition by cookieid order by createtime desc) as last2

select cookieid,createtime,url,
row_number() over (partition by cookieid order by createtime) as rn,
FIRST_VALUE(url) over (partition by cookieid order by createtime desc) as last1,
LAST_VALUE(url) over (partition by cookieid order by createtime desc) as last2
from cookie3;

注:如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果

4.自定义函数

4.1自定义UDF

(1)创建Maven项目，并导入依赖

  <dependencies>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>2.3.3</version>
            <exclusions>
                <exclusion>
                    <groupId>jdk.tools</groupId>
                    <artifactId>jdk.tools</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

(2)自定义一个java类继承UDF，重载 evaluate 方法

import org.apache.hadoop.hive.ql.exec.UDF;

public class ToLower extends UDF {
    public String evaluate(String field) {
        String result = field.toLowerCase();
        return result;
    }
}

(3)打成jar包上传到服务器