一些常用的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('[[{"id":"123","name":"苹果"}],[{"id":"456","name":"香蕉"}],[{"id":"789","name":"西瓜"}]]', '$.[*].[*].name');

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

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

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

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

7. regexp_replace（正则表达式替换函数）

语法：regexp_replace(string a, string b, string c)

将字符串a中的符合java正则表达式b的部分替换为c，部分情况下要使用转义字符

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

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

9. 日期函数

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

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

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

返回值: string

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