HIVE_Hive Function_STR_TO_MAP 函数详解

参考文章 ：

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  |
+------+