Hive如何自动识别JSON列键并展开值关联ID，无需手动指定键

核心思路 #

利用Hive的from_json函数将JSON字符串直接解析为map<string, array<string>>类型，无需手动指定键名，再通过两次lateral view explode分别展开map的键值对、以及值对应的数组元素即可，可适配任意数量、任意名称的JSON键场景。

适用版本说明 #

Hive 2.3.0及以上版本（from_json函数从该版本开始官方支持）

完整可执行SQL #

SELECT
  m.key AS `Key`,
  v.val AS `Value`,
  t.ID
FROM input_df t
-- 第一步：按JSON结构解析为Map类型，自动识别所有键
LATERAL VIEW FROM_JSON(
  t.my_col,
  'map<string, array<string>>'
) AS json_map
-- 第二步：展开Map，得到每个键对应的数组值
LATERAL VIEW EXPLODE(json_map) m AS key, value_arr
-- 第三步：展开数组，得到每个单独的元素值
LATERAL VIEW EXPLODE(m.value_arr) v AS val;

输出结果验证 #

针对你提供的测试数据，执行上述SQL后输出结果如下：

低版本Hive兼容方案 #

如果你使用的Hive版本低于2.3.0、不支持from_json函数，可以用正则解析的方式实现相同效果：

SELECT
  k.key AS `Key`,
  v.val AS `Value`,
  t.ID
FROM input_df t
-- 剥离JSON外层大括号，拆分所有键值对字符串
LATERAL VIEW EXPLODE(split(regexp_replace(t.my_col,'^\\{|\\}$',''), '",')) AS kv_str
-- 提取键名，去除多余引号
LATERAL VIEW EXPLODE(array(regexp_extract(kv_str, '^"([^"]+)"', 1))) AS k(key)
-- 提取数组内容，拆分得到单个元素
LATERAL VIEW EXPLODE(split(regexp_extract(kv_str, '\\[([^\\]]+)\\]',1), '","')) AS v(val)
-- 去除元素值多余引号
LATERAL VIEW EXPLODE(array(regexp_replace(v.val, '"',''))) AS v(val);

内容的提问来源于stack exchange，提问作者Shan