参考文章 :
https://blog.csdn.net/yuanyangsdo/article/details/64441165
本文主要讲解下 HIVE 中 str_to_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. |
返回值类型 :
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.
Tips:
delimiter1 第一个分割符指的是 多个 Key-Value 之间的分割符
delimiter1 第二个分割符指的是 Key-Value 内部的分割符
案例
构造表 与 测试数据
USE data_warehouse_test;
CREATE TABLE IF NOT EXISTS explode_laterview_org(
day1_num BIGINT
,day2_num BIGINT
,day3_num BIGINT
,day4_num BIGINT
,day5_num BIGINT
,day6_num BIGINT
,day7_num BIGINT
,campaign_name STRING
,campaign_id BIGINT
);
INSERT OVERWRITE TABLE explode_laterview_org VALUES
(40, 20, 10, 4, 4, 2, 1, 'zoo', 2 )
,(100, 80, 53, 40, 7, 6, 5, 'moji', 3)
;
查询 1
SELECT STR_TO_MAP(
CONCAT(
'day1_num=',CAST (day1_num AS STRING),
'&day2_num=',CAST (day2_num AS STRING),
'&day3_num=',CAST (day3_num AS STRING),
'&day4_num=',CAST (day4_num AS STRING),
'&day5_num=',CAST (day5_num AS STRING),
'&day6_num=',CAST (day6_num AS STRING),
'&day7_num=',CAST (day7_num AS STRING)
)
,'&', '=')
FROM explode_laterview_org
;
相当于是把每条记录的 day1 -> day7 的留存构建了一个Map
结果
+----------------------------------------------------+
| _c0 |
+----------------------------------------------------+
| {"day1_num":"40","day2_num":"20","day3_num":"10","day4_num":"4","day5_num":"4","day6_num":"2","day7_num":"1"} |
| {"day1_num":"100","day2_num":"80","day3_num":"53","day4_num":"40","day5_num":"7","day6_num":"6","day7_num":"5"} |
+----------------------------------------------------+
查询2
对于 Map<String,String> 数据,可以通过 map_name['key_name'] 获取 Map 中指定 Key的 Value 数据。
示例:
SELECT STR_TO_MAP(
CONCAT(
'day1_num=',CAST (day1_num AS STRING),
'&day2_num=',CAST (day2_num AS STRING),
'&day3_num=',CAST (day3_num AS STRING),
'&day4_num=',CAST (day4_num AS STRING),
'&day5_num=',CAST (day5_num AS STRING),
'&day6_num=',CAST (day6_num AS STRING),
'&day7_num=',CAST (day7_num AS STRING)
)
,'&', '=')['day1_num']
FROM explode_laterview_org
;
结果:
+------+
| _c0 |
+------+
| 40 |
| 100 |
+------+