Hive 常用函数

最新推荐文章于 2024-05-24 11:54:36 发布

大小不少年

最新推荐文章于 2024-05-24 11:54:36 发布

阅读量1k

点赞数

文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/zhangzhanpu/article/details/130098499

版权

10.field.delim、serialization.format、serdeproperties

16.collect_list和collect_set

1.explode

Explode()函数是Hive的内置函数，也有人将其称为炸裂函数，此函数将array或map作为输入，
按行输出array或map中的元素，可搭配lateral view使用。 explode函数的输入只能为array或map

2.lateral view

Lateral view通常与生成器函数结合使用，比如explode，生成包含一行或多行的虚拟表

例：select user_id -- 原字段
                  ,user_layer -- 原字段
                  ,ordr_id -- 拆分字段
        from A   lateral view explode(ordr_set)tmp1 as ordr_id
注释：tmp1是虚拟表的表名，ordr_id是ordr_set拆分后的字段。

3.row_number

将针对SELECT语句返回的每一行，从1开始编号，赋予其连续的编号。
在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，
当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号

4.partition by

分析性函数的一部分，它能返回一个分组中的多条记录，partition by用于给结果集分组
，如果没有指定那么它把整个结果集作为一个分组
一般与partition by a order by b 一起使用（意思：基于a字段分组，基于b字段进行组内排序）

与group by的区别：

group by：主要体现的是分组后的整体，比如配合sum()、max()等函数的整体结果

partition by：主要体现分组后组内的个体

5.over（）

开窗函数over（）分析函数包含三个分析子句：分组子句(partition by)，排序子句(order by)，窗口子句(rows)

over（）里外两个order by的执行顺序：
①两者一致：sql语句中的排序将先执行,分析函数在分析时就不必再排序；
②两者不一致：sql语句中的排序将最后在分析函数分析结束后执行排序

6.substr()

截取字符串的“内容”，instr()函数是要截取的字符串在源字符串中的“位置"
substr(string,start,length)
string：指定的要截取的字符串
start ：规定在字符串的何处开始：正数 - 在字符串的指定位置开始；负数 - 在从字符串结尾的指定位置开始；0 - 在字符串中的第一个字符处开始；
length：指定要截取的字符串长度，缺省时返回字符表达式的值结束前的全部字符

7.to_unix_timestamp

根据所提供的字符串格式，将日期字符串格式化，从而得到你想要的日期格式。
有这么一个日期字符串：16/Mar/2017:12:25:01 +0800
把这个字符串转化成正常的日期格式（yyyy-MM-dd hh:mm:ss）
from_unixtime(to_unix_timestamp('16/Mar/2017:12:25:01 +0800', 'dd/MMM/yyy:HH:mm:ss Z'))

8.from_unixtime函数

语法：from_unixtime(timestamp ,date_format)
参数说明
timestamp :时间戳，可为一串数字，也可为字段。
date_format：时间格式，不填默认为%Y-%m-%d %H:%i:%s的格式。

9.with as

WITH AS短语，也叫做子查询部分，定义一个SQL片断后，该SQL片断可以被整个SQL语句所用到
with A as ( select * from user) A相当于构建的临时表，之后可用直接用这个表做查询或者关联查询

10.field.delim、serialization.format、serdeproperties

field.delim：是表的两个列字段之间的文件中的字段分隔符.

serialization.format：是文件序列化时表中两个列字段之间的文件中的字段分隔符

serdeproperties：创建table时，直接定义serdeproperties属性

11.get_json_object

get_json_object(string json_string, string path)
get单层：get_json_object(col,'$.timestamp') as `timestamp`,
get多层：get_json_object(col,'$.metadata.type') as metadata_type,
get数组：get_json_object(col,'$.metadata.type[0]') as service_ip_a

12.if

IF(expr1,expr2,expr3) IF(sva=1,"男","女")
expr1 是TRUE ，则 IF()的返回值为expr2; 否则返回值则为 expr3。

13.CASE WHEN

作为表达式的if也可以用CASE WHEN来实现：
CASE sva WHEN 1 THEN '男' ELSE '女' END

CASE sva WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END

14.concat_ws()函数

功能：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符
语法：concat_ws(separator, str1, str2, ...)
说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。
hive中也可以合并多个数组为一个字符串

15.group_concat()函数

功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
例：group_concat(id order by id desc separator '_')

16.collect_list和collect_set

它们都是将分组中的某列转为一个数组返回，collect_list函数不去重而collect_set函数会去重。
例：collect_list( 字段名a ) from 表 group by 字段名b
描述：通过字段b进行分组，将分组后每组的字段a作为一个数组返回

17.convert（）函数

convert(data_type(length),data,style)
data_type(length)转换的目标数据类型，data 需要转换的值，style规定的日期/时间输出格式。

18.cast()函数

CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型
语法：　CAST (expression AS data_type)
参数说明：
　　expression：任何有效的SQServer表达式。
　　AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
　　data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

19.hive 函数

①round(x,y)
round是四舍五入函数，无y值时默认保留整数;
y值大于零时，意为小数点后保留位数;
小于零时，是在整数的基础上四舍五入，比如round(129,-1)，结果为130

②floor(x) 向下取整
③ceiling(x) 或 ceil(x) 向上取整
④cast(x as y) 做类型装换，可以将x转换为y类型，如cast(123.4 as int)，结果为123，同18

20.Hive三个内置date函数：

①1. datediff（'endTime'，‘startTime’）
返回前后日期之间的天数差

②date_sub（‘yyyy-MM-dd’，n/-m）
返回初始日期n天前、m天后的日期

③ date_add（'yyyy-MM-dd'，n/-m）
返回初试日期n天后、m天前的日期

21.coalesce函数

coalesce(expression_1, expression_2, ...,expression_n)
依次参考各参数，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。

22.named_struct()

将括号内的数据生成一个map或者对象
例：
select named_struct("name","csdn","addr","beijing","age",12)
结果：{name:csdn,addr:beijing,age:12}
注意：当结构化数据K-V中Key有null值时，named_struct函数报错

23.to_json

嵌套named_struct()使用，生成一个json对象
例：
select to_json(named_struct("name","csdn","addr","beijing","age",12))
结果：{"name":"csdn","addr":"beijing","age":12}
注意：当结构化数据K-V中Value有null值时，有null值的K-V对在to_json中丢失

24.str_to_map

作用：使用两个分隔符将文本拆分为键值对。
语法：str_to_map(字符串参数, 分隔符1, 分隔符2)
分隔符1将文本分成K-V对，分隔符2分割每个K-V对。
例：select str_to_map('a=1,b=2,c=3', ',' ,'=');
结果：{a:1,b:2,c:3}

配合to_json使用：
select to_json(str_to_map('a=1,b=2,c=3',',','='));
结果：{"a":"1","b":"2","c":"3"}

25.json_tuple

作用：快速解析出json中多个key值对应的value
语法：json_tuple(json格式字符串, key1, key2, key3…)
例：select json_tuple('{"name":"i_mycode","age":18,"addr":"beijing"}','name','age','addr')
结果：i_mycode 18 beijing

26.translate 字符串的替换

作用：按照替换字符与被替换字符位置一一替换
语法：translate(input, from, to)
input：输入字符串【集是要被替换的字符串】
from：需要匹配的字符【即需要被替换的字符】，这里一定要注意是字符不是字符串
to ：用哪些字符来替换被匹配到的字符
注意点：这里from的字符与to字符在位置上存在一一对应关系，也就是from中每个位置上的字符用
to中对应位置的字符替换。
例1：
select translate("abcdef abcdef","abcdef","123456");
结果为：123456 123456
例2：当from长度大于 to长度
select translate("abcdef abcdef","abcdef","1234");
结果：1234 1234
例3：当from长度小于 to长度
select translate("abcdef abcdef","abcd","123456");
结果：1234ef 1234ef
例4：from中有重复字符
select translate("abcdef abcdef","aacd","1234");
结果：1b34ef 1b34ef