高效展平Hive嵌套JSON并生成指定输出（兼容Spark SQL 2.4）

输入数据 #

Hive表存储的嵌套JSON数据结构如下：

COL1   JSON_STRING
1      {"COL2": {"REFA": "9", "REFB": "9"}, "COL3": {"REFA": "80000.0000", "REFB": "80001.0000"}, "COL4": {"REFA": "0.0000", "REFB": "0.0000"}}

期望输出 #

需要将数据转换为以下长表格式：

COL1  REF           COL2     COL3    COL4
1     REFA          9        80000   0
1     REFB          9        80001   0
1     DIFFERENCE    0        -1      0

当前实现及痛点 #

已通过多次LATERAL VIEW json_tuple展平JSON，但得到的是宽表结构，且不确定解析效率：

SELECT  
COL1,
COL2_REFA,COL2_REFB,(COL2_REFA-COL2_REFB) COL2_DIFFERENCE,
COL3_REFA,COL3_REFB,(COL3_REFA-COL3_REFB) COL3_DIFFERENCE,
COL4_REFA,COL4_REFB,(COL4_REFA-COL4_REFB) COL4_DIFFERENCE,
FROM  
TABLE T1 
LATERAL VIEW json_tuple(T1.JSON_STRING,'COL2','COL3','COL4') T2 AS `COL2`,`COL3`,`COL4`
LATERAL VIEW json_tuple(T2.COL2,'REFA','REFB') T3 AS `COL2_REFA`,`COL2_REFB`
LATERAL VIEW json_tuple(T2.COL3,'REFA','REFB') T4 AS `COL3_REFA`,`COL3_REFB`
LATERAL VIEW json_tuple(T2.COL4,'REFA','REFB') T5 AS `COL4_REFA`,`COL4_REFB`

当前宽表结果：

COL1    COL2_REFA   COL2_REFB   COL2_DIFF   COL3_REFA   COL3_REFB   COL3_DIFF   COL4_REFA   COL4_REFB   COL4_DIFF
1       9           9           0           80000       80001       -1          0           0           0

需求：

• 高效实现期望的长表输出
• 兼容Spark SQL 2.4版本

1. 高效解析嵌套JSON #

Spark SQL 2.4中，from_json比多次json_tuple更高效——它只需一次JSON解析即可提取完整嵌套结构，避免重复解析开销。

步骤1：定义JSON Schema #

先明确嵌套JSON的结构Schema：

-- 生成并提取Schema字符串
SELECT schema_of_json('{"COL2": {"REFA": "9", "REFB": "9"}, "COL3": {"REFA": "80000.0000", "REFB": "80001.0000"}, "COL4": {"REFA": "0.0000", "REFB": "0.0000"}}') AS json_schema_str;

得到的Schema字符串可直接使用：
struct<COL2:struct<REFA:string,REFB:string>,COL3:struct<REFA:string,REFB:string>,COL4:struct<REFA:string,REFB:string>>

步骤2：一次性解析JSON #

用from_json解析并展平数据，同时完成字符串到数值的转换：

WITH parsed_data AS (
  SELECT
    COL1,
    from_json(JSON_STRING, 'struct<COL2:struct<REFA:string,REFB:string>,COL3:struct<REFA:string,REFB:string>,COL4:struct<REFA:string,REFB:string>>') AS json_data
  FROM T1
),
flattened_data AS (
  SELECT
    COL1,
    cast(json_data.COL2.REFA AS int) AS COL2_REFA,
    cast(json_data.COL2.REFB AS int) AS COL2_REFB,
    cast(json_data.COL3.REFA AS int) AS COL3_REFA,
    cast(json_data.COL3.REFB AS int) AS COL3_REFB,
    cast(json_data.COL4.REFA AS int) AS COL4_REFA,
    cast(json_data.COL4.REFB AS int) AS COL4_REFB
  FROM parsed_data
)
SELECT * FROM flattened_data;

2. 行转列生成期望格式 #

使用Spark SQL 2.4原生支持的stack函数，将宽表转成长表，同时计算差值：

WITH parsed_data AS (
  SELECT
    COL1,
    from_json(JSON_STRING, 'struct<COL2:struct<REFA:string,REFB:string>,COL3:struct<REFA:string,REFB:string>,COL4:struct<REFA:string,REFB:string>>') AS json_data
  FROM T1
),
flattened_data AS (
  SELECT
    COL1,
    cast(json_data.COL2.REFA AS int) AS COL2_REFA,
    cast(json_data.COL2.REFB AS int) AS COL2_REFB,
    cast(json_data.COL3.REFA AS int) AS COL3_REFA,
    cast(json_data.COL3.REFB AS int) AS COL3_REFB,
    cast(json_data.COL4.REFA AS int) AS COL4_REFA,
    cast(json_data.COL4.REFB AS int) AS COL4_REFB
  FROM parsed_data
),
transposed_data AS (
  SELECT
    COL1,
    stack(3,
      'REFA', COL2_REFA, COL3_REFA, COL4_REFA,
      'REFB', COL2_REFB, COL3_REFB, COL4_REFB,
      'DIFFERENCE', (COL2_REFA - COL2_REFB), (COL3_REFA - COL3_REFB), (COL4_REFA - COL4_REFB)
    ) AS (REF, COL2, COL3, COL4)
  FROM flattened_data
)
SELECT COL1, REF, COL2, COL3, COL4
FROM transposed_data
ORDER BY COL1, CASE REF WHEN 'REFA' THEN 1 WHEN 'REFB' THEN 2 WHEN 'DIFFERENCE' THEN 3 END;

3. 性能优化要点 #

• 避免重复解析：用from_json一次性解析完整JSON结构，替代多次json_tuple调用，减少解析开销
• 明确Schema：提前定义Schema比自动推断更高效，还能避免类型转换错误
• 提前转换类型：在解析阶段完成字符串到数值的转换，避免后续计算重复转换

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