如何将Hive的str_to_map函数转换为BigQuery的键值对实现

由于BigQuery没有原生Map类型，我们可以通过JSON对象或数组+STRUCT两种方式模拟键值对结构，实现按键提取值的需求。核心是正确拆分键值对（仅用第一个冒号分隔键和值，后续冒号保留为值的一部分）。

方法1：用JSON对象模拟Map（最接近Hive用法） #

将字符串转换为JSON对象后，使用JSON_EXTRACT_SCALAR按键取值，语法和Hive的map[key]逻辑一致。

WITH input_data AS (
  SELECT 'cars:0,kids:143,cats:1,lost:0,win:1,chances:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0,missed:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0' AS str
)
SELECT
  -- 生成JSON格式的键值对对象
  JSON_OBJECT(
    SELECT AS STRUCT 
      REGEXP_EXTRACT(kv_pair, r'^([^:]+)') AS key,
      REGEXP_EXTRACT(kv_pair, r'^[^:]+:(.*)') AS value
    FROM UNNEST(SPLIT(str, ',')) AS kv_pair
  ) AS key_value_map,
  
  -- 提取指定键的值（示例：cars和chances）
  JSON_EXTRACT_SCALAR(key_value_map, '$.cars') AS cars_value,
  JSON_EXTRACT_SCALAR(key_value_map, '$.chances') AS chances_value
FROM input_data;

方法2：用数组+STRUCT存储键值对 #

将键值对存储为STRUCT数组，通过子查询过滤键名来提取对应值，适合需要多次查询不同键的场景。

WITH input_data AS (
  SELECT 'cars:0,kids:143,cats:1,lost:0,win:1,chances:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0,missed:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0' AS str
),
key_value_structs AS (
  SELECT
    ARRAY(
      SELECT STRUCT(
        REGEXP_EXTRACT(kv_pair, r'^([^:]+)') AS key,
        REGEXP_EXTRACT(kv_pair, r'^[^:]+:(.*)') AS value
      )
      FROM UNNEST(SPLIT(str, ',')) AS kv_pair
    ) AS key_value_array
  FROM input_data
)
SELECT
  key_value_array,
  
  -- 提取指定键的值
  (SELECT value FROM UNNEST(key_value_array) WHERE key = 'cars') AS cars_value,
  (SELECT value FROM UNNEST(key_value_array) WHERE key = 'chances') AS chances_value
FROM key_value_structs;

关键逻辑说明 #

• 拆分规则：使用正则REGEXP_EXTRACT匹配第一个冒号前的内容作为键，第一个冒号后的所有内容作为值，确保值中包含的冒号不会被错误拆分。
• 两种方案对比：
  • JSON方案：语法简洁，接近Hive的Map使用习惯，适合单次查询少量键的场景。
  • 数组STRUCT方案：原生BigQuery类型，性能更优，适合需要多次查询不同键或复杂处理的场景。

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