HIVE作為數(shù)據(jù)倉庫處理常用工具，如同RDBMS關(guān)系型數(shù)據(jù)庫中標(biāo)準(zhǔn)SQL語法一樣，Hive SQL也內(nèi)置了不少系統(tǒng)函數(shù)，滿足于用戶在不同場景下的數(shù)據(jù)分析需求，以提高開發(fā)SQL數(shù)據(jù)分析的效率。
我們可以使用show functions查看當(dāng)下版本支持的函數(shù)，并且可以通過describe function extended funcname來查看函數(shù)對應(yīng)的使用方式和方法，下面我們將描述HIVE SQL中常用函數(shù)的高階使用場景。

1、行轉(zhuǎn)列(explode)

如下活動列表：tb_activities

活動ID	活動名稱列表
1	雙111,國慶,元旦
2	黃金周,國慶,元旦

希望轉(zhuǎn)換為列類型活動表：tb_activitity

活動ID	活動名稱
1	雙11
1	國慶
1	元旦
2	黃金周
2	國慶
2	元旦

使用到Hive內(nèi)置一個(gè)非常著名的UDTF函數(shù)，名字叫做explode函數(shù)，中文戲稱為“爆炸函數(shù)”，可以炸開數(shù)據(jù)轉(zhuǎn)換為多行。

insert into table tb_activitity select id,activity from tb_activities 
lateral view explode(split(activities,','))enum_tmp as activity;

2、列轉(zhuǎn)行

如上1所示，希望從tb_activity轉(zhuǎn)換為tb_activities，通過collect_set()方法和group by id 將列轉(zhuǎn)換為行，實(shí)現(xiàn)如下：

 select id, concat_ws(',',collect_set(activity)) as activities from tb_activity group by id;

3、排名(rank())

可以通過rank() 方法的使用，實(shí)現(xiàn)對指定列進(jìn)行排名，輸出排名結(jié)果。例如商品總數(shù)表：t_item_sum，需要實(shí)現(xiàn)排名功能：

item_id	item_sum
1001	20
1002	12
1003	62
1004	15

期望得到：

item_id	item_sum	rank
1003	62	1
1001	20	2
1004	15	3
1002	12	4

代碼實(shí)現(xiàn)如下：

select item_id,item_sum,rank()over(order by item_sum desc) as rank from t_item_sum;

4、分組去重

在查詢數(shù)據(jù)時(shí)如果有重復(fù)，我們可以使用用distinct 去除重復(fù)值，但使用 distinct 只能去除所有查詢列都相同的記錄，如果某個(gè)字段不同，distinct 就無法去重。這時(shí)我們可以用 row_number()over(partitioon by column1 order by column2) 先進(jìn)行分組。
例如：有活動表數(shù)據(jù)列“活動id，用戶id，活動名稱，客戶群組，過期時(shí)間”，希望按照”活動id，活動名稱，客戶群組”去重，取最新一條數(shù)據(jù)。

id	user_id	activity	cust_group	expired_at
BCP015	1001	春節(jié)活動A	高價(jià)值	2023-10-05
BCP015	1001	春節(jié)活動A	高價(jià)值	2023-10-15
BCP015	1001	春節(jié)活動A	高價(jià)值	2023-10-28
BCP025	1002	春節(jié)活動B	中價(jià)值	2023-10-05
BCP025	1002	春節(jié)活動B	中價(jià)值	2023-10-25
BCP030	1003	春節(jié)活動C	中價(jià)值	2023-10-25

期望得到：

id	user_id	activity	cust_group	expired_at
BCP015	1001	春節(jié)活動A	高價(jià)值	2023-10-28
BCP025	1002	春節(jié)活動B	中價(jià)值	2023-10-25
BCP030	1003	春節(jié)活動C	中價(jià)值	2023-10-25

使用row_number()over(partitioon by) 分組去重。

  select tt1.* from(select id, user_id, activity,cust_group,row_number() over(partition by concat(id,activity,cust_group)order by expired_at desc)as row_num from tb_acitivity_full)tt1 where tt1.row_num=1;

5、指標(biāo)統(tǒng)計(jì)

GROUPING SETS,GROUPING__ID,CUBE,ROLLUP，這幾個(gè)hive分析函數(shù)通常用于OLAP中，不能累加，而且需要根據(jù)不同維度上鉆（ roll up ）和下鉆（ drill down ）的指標(biāo)統(tǒng)計(jì)，比如，分小時(shí)、天、月的UV數(shù)。上鉆是沿著維度的層次向上聚集匯總數(shù)據(jù)，下鉆是在分析時(shí)加深維度，對數(shù)據(jù)進(jìn)行層層深入的查看。通過逐層下鉆，數(shù)據(jù)更加一目了然，更能充分挖掘數(shù)據(jù)背后的價(jià)值，及時(shí)做出更加正確的決策。

OLAP函數(shù)	使用說明
GROUPING SETS	根據(jù)不同的維度組合進(jìn)行聚合，等價(jià)于將不同維度的GROUP BY結(jié)果集進(jìn)行UNION ALL
GROUPING__ID	表示結(jié)果屬于哪一個(gè)分組集合，屬于虛字段
CUBE	可根據(jù)GROUP BY的維度的所有組合進(jìn)行聚合
ROLLUP	作為CUBE的子集，以最左側(cè)的維度為主，從該維度進(jìn)行層級聚合

如4所示，希望對指標(biāo)值進(jìn)行統(tǒng)計(jì)，期望結(jié)果：

indicator	enum_value	count
id	BCP015	3
id	BCP025	2
id	BCP030	1
activity	春節(jié)活動A	3
activity	春節(jié)活動B	2
activity	春節(jié)活動C	1
cust_group	高價(jià)值	3
cust_group	中價(jià)值	3

通過grouping__id 內(nèi)層SQL處理結(jié)果,表2：

groupId	id	activity	cust_group	uv
1728	id_BCP015	NULL	NULL	3
1724	id_BCP025	NULL	NULL	2
1723	id_BCP030	NULL	NULL	1
2728	NULL	activity_春節(jié)活動A	NULL 3
2724	NULL	activity_春節(jié)活動B	NULL 2
2723	NULL	activity_春節(jié)活動C	NULL 1
3723	NULL	NULL	cust_group_高價(jià)值	3
3724	NULL	NULL	cust_group_中價(jià)值	3

select split(coalesce(id,activity,cust_group),'\\_')[0] as indicator,coalesce(split(id, '\\_')[1],split(activity, '\\_')[1],split(cust_group, '\\_')[1],) as enum_valuesum(uv) as countfrom (-- 內(nèi)層SQL處理結(jié)果，對應(yīng)上表2select grouping__id as groupId,concat('id|',id) as id,concat('activity|', activity) as activity,concat('cust_group|',cust_group) as cust_group,count(*) as uvfrom tb_acitivity_fullgroup by concat('id|',id),concat('activity|', activity),concat('cust_group|',cust_group)grouping sets(concat('id|',id),concat('activity|', activity),concat('cust_group|',cust_group))as tt1group by split(coalesce(id,activity,cust_group),'\\_')[0],coalesce(split(id, '\\_')[1],split(activity, '\\_')[1],split(cust_group, '\\_')[1],);

6、JSON數(shù)據(jù)處理

JSON數(shù)據(jù)作為數(shù)據(jù)存儲和數(shù)據(jù)處理中最常見的結(jié)構(gòu)化數(shù)據(jù)格式之一，許多場景下都會將數(shù)據(jù)以JSON格式存儲在文件系統(tǒng)(HDFS/MINIO等)中，當(dāng)構(gòu)建數(shù)據(jù)倉庫時(shí)，對JSON格式的數(shù)據(jù)進(jìn)行處理和分析，就需要在Hive中使用對應(yīng)函數(shù)對JSON格式的數(shù)據(jù)進(jìn)行解析讀取。
例如，JSON格式的數(shù)據(jù)如下：

商品ID	商品名稱	額外信息
1001	IP15	“fixedIntegral”:200, “source”:“wechat”,“stages”:12}

獲取商品可使用的固定積分：

select get_json_object(extra_json, '$fixedIntegral) as integral
from t_items;

7、替換

7.1 translate 函數(shù)用法

select translate('abcdef', 'adc', '19') tb_translate_exe
輸出：
1b9ef

• translate(input,from,to)
• input：輸入字符串
• from：需要匹配的字符
• to ：用哪些字符來替換被匹配到的字符
  注意點(diǎn)：這里from的字符與to字符在位置上存在一 一對應(yīng)關(guān)系，也就是from中每個(gè)位置上的字符用to中對應(yīng)位置的字符替換。

7.1 regexp_replace 函數(shù)

正則替換

SELECT  aa,REGEXP_REPLACE(aa, '[a-z]', '')    -- 替換所有字母,REGEXP_REPLACE(aa, '[abc]', '')    -- 替換指定字母,REGEXP_REPLACE(aa, '[^abc]', '')    -- 替換所有非字母,REGEXP_REPLACE(aa, '[0-9]', '')    -- 替換所有數(shù)字,REGEXP_REPLACE(aa, '[\\s\\S]', '')    -- 替換空白符、換行，\\s：是匹配所有空白符，包括換行，\\S：非空白符，不包括換行。,REGEXP_REPLACE(aa, '\\w', '')    -- 替換所有字母、數(shù)字、下劃線。等價(jià)于 [A-Za-z0-9_],REGEXP_REPLACE(aa, '[-8+]', '')    -- 只替換-8這個(gè)字符,REGEXP_REPLACE(aa, '[-8*]', '')    -- 替換-8、-、8這幾個(gè)字符
FROM    (SELECT  '5e40b2b8-0916-42c0-899a-eaf4b2df 5268' AS aaUNION ALLSELECT  'c81b5906-38d7-482c-8b66-be5d3359cbf6' AS aaUNION ALLSELECT  '8856fd0a-2337-4605-963f-0d0d059b1937' AS aa) t
;