hive官方函數解釋

hive官網函數大全地址： hive官網函數大全地址

Return Type	Name	Description
map	map(key1, value1, key2, value2, …)	Creates a map with the given key/value pairs.
array	map_values(Map<K.V>)	Returns an unordered array containing the values of the input map.
array	map_keys(Map<K.V>)	Returns an unordered array containing the keys of the input map.
map<string,string>	str_to_map(text[, delimiter1, delimiter2])	Splits text into key-value pairs using two delimiters. Delimiter1 separates text into K-V pairs, and Delimiter2 splits each K-V pair. Default delimiters are ‘,’ for delimiter1 and ‘:’ for delimiter2.
Tkey,Tvalue	explode(MAP<Tkey,Tvalue> m)	Explodes a map to multiple rows. Returns a row-set with a two columns (key,value) , one row for each key-value pair from the input map. (As of Hive 0.8.0.).

示例

1、map(key1, value1, key2, value2, …)

SELECT map('name', '張三', 'age', 20, 'gender', '男') AS student;
---結果：
student	
{"age":"20","gender":"男","name":"張三"}

2、map_values(Map<K.V>)

SELECT map_keys(map('name', '張三', 'age', 20, 'gender', '男')) AS keys;
---結果：
keys
["name","age","gender"]

3、map_values(Map<K.V>)

SELECT map_values(map('name', '張三', 'age', 20, 'gender', '男')) AS values;
---結果：
values	
["張三","20","男"]

4、str_to_map(str, delimiter1, delimiter2)
str_to_map 函數用于將一個字符串轉換為 Map 對象。具體來說，str_to_map 函數會將一個由鍵值對組成的字符串解析成一個 Map 對象，其中鍵和值之間使用指定的分隔符進行分隔。其中，str 是要轉換的字符串，delimiter1 是鍵值對之間的分隔符，delimiter2 是鍵和值之間的分隔符。默認情況下，delimiter1 的值是 ‘,’，delimiter2 的值是 ‘:’。

SELECT str_to_map('name:張三,age:20,gender:男', ',', ':') AS student;
---結果：
student	
{"age":"20","gender":"男","name":"張三"}SELECT str_to_map('name=張三,age=20,gender=男', ',', '=') AS student;
---結果：
student	
{"age":"20","gender":"男","name":"張三"}

5、explode (map)

select explode(map('A',10,'B',20,'C',30));
select explode(map('A',10,'B',20,'C',30)) as (key,value);
select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf;
select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf as key,value;
---上述四個結果均為：
key     value
A       10	
B       20	
C       30

實戰

給出一組學生數據，有名字，課程，等級，分數等字段，現在求每門課的情況，包含平均成績，及這門課包含哪些學生及學生的等級

with stud as
( select  'zhang3' as name ,'優' as grade  ,'math' as course ,'88' as score  union all select  'li4' as name ,'良' as grade  ,'math' as course ,'72' as scoreunion all select  'zhao6' as name ,'差' as grade  ,'math' as course ,'44' as scoreunion all select  'wang5' as name ,'優' as grade  ,'chinese' as course ,'80' as scoreunion all select  'zhao6' as name ,'優' as grade  ,'chinese' as course ,'55' as scoreunion all select  'tian7' as name ,'優' as grade  ,'chinese' as course ,'75' as score
)--sql1
select course, collect_set(concat(name,':',grade)) as collect , avg(score) from stud group by course;
---結果：
course             collect                                             avg(score)	
math        ["li4:良","zhao6:差","zhang3:優"]                           68.0
chinese     ["wang5:優","tian7:優","zhao6:優"]                          70.0
----sql2
select course, concat_ws(',',collect_set(concat(name,':',grade))) as strings , avg(score) from stud group by course;
---結果：
course                      strings                                        avg(score)
math             li4:良,zhao6:差,zhang3:優                                  68.0
chinese          wang5:優,tian7:優,zhao6:優                                 70.0
----sql3
select course, str_to_map(concat_ws(',',collect_set(concat(name,':',grade))),',',':') as maps , avg(score) from stud group by course;
---結果：
course                               maps                              avg(score)	
math                 {"li4":"良","zhang3":"優","zhao6":"差"}             68.0
chinese              {"tian7":"優","wang5":"優","zhao6":"優"}            70.0

注意：
第一種sql,collect 字段的類型是array;第二種sql,strings字段的類型是string;第三種sql,maps字段的類型是map;
問題來了，能否在第二種的基礎上，實現第一種和第三種的結果，且字段類型是string;
下面實現第二種轉化為第三種，實際上就是map格式轉換成json字符串；

with stud as
( select  'zhang3' as name ,'優' as grade  ,'math' as course ,'88' as score  union all select  'li4' as name ,'良' as grade  ,'math' as course ,'72' as scoreunion all select  'zhao6' as name ,'差' as grade  ,'math' as course ,'44' as scoreunion all select  'wang5' as name ,'優' as grade  ,'chinese' as course ,'80' as scoreunion all select  'zhao6' as name ,'優' as grade  ,'chinese' as course ,'55' as scoreunion all select  'tian7' as name ,'優' as grade  ,'chinese' as course ,'75' as score
)select 
course
,concat('{"',string2,'"}') as string3
from  
(select 
course
,regexp_replace(string1,'\\,','\\"\\,\\"') as string2
from  
(
select course,concat_ws(',', collect_list(concat_ws('":"', k,v) ) ) as string1
from (
select course, str_to_map(concat_ws(',',collect_set(concat(name,':',grade))),',',':') as maps , avg(score) 
from stud group by course
)test_map_1
lateral view outer explode(maps) kv as k,v
group by course
) tt 
) tm ---結果：
course                               string3                            	
math                 {"li4":"良","zhang3":"優","zhao6":"差"}           
chinese              {"tian7":"優","wang5":"優","zhao6":"優"}