一些常用的 hive sql 函数

1. with...as 语句

with 语句，允许 hive 定义一个 sql 片段，供整个 sql 使用，会将这个片段产生的结果集保存在内存中，后续的 sql 均可以访问这个结果集，作用与视图或临时表类似；

with 语句，相当于建立了一张临时虚拟表，但是不会被物理创建，用完即销毁；

with 语句，可以将业务接耦，每一个 with 语句，单独成为一个子模块，最后使用基础表将它们串联起来；

这里必须要整体作为一条 sql 查询，即 with as 语句后不能加分号，不然会报错；

语法限制：

1）with...as...必须和其他 sql 一起使用（可以定义一个 with 但在后续语句中不使用他），如果定义了 with 子句，但其后没有跟 select 查询，则会报错；

2）with...as...是一次性的；

with temp_table as ( select * from xxx ) select * from temp;

with temp1 as ( select * from xxx) , temp2 as ( select * from xxx) select * from temp1,temp2;

2. lateral view（行转列）、explode

lateral view 用于和 split、explode 等 UDTF 一起使用的，能将一行数据拆分成多行数据，大数据培训在此基础上可以对拆分的数据进行聚合，lateral view 首先为原始表的每行调用 UDTF（操作作用于单个数据行，并且产生多个数据行，返回拆分值，一对多），UDTF 会把一行拆分成一行或者多行，lateral view 在把结果组合，产生一个支持别名表的虚拟表。

split(str, regex) ：按规则截取字符串，返回数组

explode 函数（行转列）：

1）explode 函数以 array 类型数据输入，将数组中的元素拆分成多行显示；

2）array 函数是将一列输入转换成一个数组输出；

3）hive 的表生成函数只是生成了一种数据的展示方式，而无法在表中产生一个其他的列；

4）因此这块需要使用 LATERAL VIEW 功能来进行处理，LATERAL VIEW 将 explode 生成的结果当做一个视图来处理；

5）a 表示表示 UDTF 函数转换的虚拟表的名称，browse_product 表示虚拟表的虚拟字段名称

select * from jindi_data.ods_es_search_copyright_reg_index_df lateral view explode(split(gids, ';')) a as browse_product where pt = 20210510

--多个 lateral view

col1 col2

[1, 2] ['a', 'b', 'c']

[3, 4] ['d', 'e', 'f']

select aa,bb from temptable lateral view explode(col1) a as aa lateral view explode(col2) b as bb;

3. coalesce 函数

将控制替换成其他值，返回第一个非空值

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

select coalesce(success_cnt, 1) from table1

--当 success_cnt 为 null 值的时候，将返回 1，否则将返回 success_cnt 的真实值

select coalesce(success_cnt,period,1) from table2

--当 success_cnt 不为 null，那么无论 period 是否为 null，都将返回 success_cnt 的真实值（因为 success_cnt 是第一个参数）

--当 success_cnt 为 null，而 period 不为 null 的时候，返回 period 的真实值。只有当 success_cnt 和 period 均为 null 的时候，将返回 1

4. collect_list、collect_set 函数（列转行）

都是将分组中的某列转为一个数组返回，不同的是 collect_list 不去重而 collect_set 去重。

还可以利用 collect 突破 group by 的限制，Hive 中在 group by 查询的时候要求出现在 select 后面的列都必须是出现在 group by 后面的，即 select 列必须是作为分组依据的列，但是有的时候想根据 A 进行分组然后随便取出每个分组中的一个 B。

select username, collect_list(video)[0] from visit_video group by username

5. get_json_object（提取 json 格式中的数据）

get_json_object(string json_string，stringpath)

说明：第一个参数为 json 对象变量，第二个参数使用 $表示 json 变量标识，然后用.或[]读取对象或数组，如果 json 无效，返回 null，每次只能返回一个数据项

举例：

data = {"store": {"fruit":[{"size":8,"name":"apple"}, {"size":9,"name":"pear"}],

             "bicycle":{"price":20,"color":"yellow"}
              },
           "email":"amy@123.com",
            "owner":"amy"
    }

--获取单层 json

select get_json_object(data,"$.owner") from table

--获取多层 json

select get_json_object(data,"$.store.fruit") from table

--获取数组值[ ]

select get_json_object(data,"$.store.fruit[0]") from table

--获取所有 name 值

select get_json_object('[[{"gid":"1289809956","name":"湛江市公安局"}],[{"gid":"1289809956","name":"湛江市公安局"}],[{"gid":"1289809956","name":"湛江市公安局"}]]', '$.[].[].name');

6. regexp_extract（字符串正则表达式解析函数）

语法： regexp_extract(string subject, string pattern, int index)

index：0 是显示与之匹配的整个字符串，1 是显示第一个括号里面的，2 是显示第二个括号里面的字段

在有些情况下要使用转义字符（双斜杠'\'）

7. array_contains（判断 array 是否含有某个元素）

select array_contains(applicant_other_2,'ABC') as flag from table

8. 日期函数

date_add(string startdate, int days) 日期加天数

date_sub(string startdate, int days) 日期减天数

datediff(string enddate, string startdate) 返回两个日期差，int 类型

unix_timestamp(string date) 返回值: bigint，转换格式为"yyyy-MM-dd HH:mm:ss"的日期到 unix 时间戳，如果转化失败，则返回 0

9. concat_ws（带分隔符字符串连接函数）

concat_ws(string SEP, string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，SEP 表示各个字符串间的分隔符。