Hive或Spark查询使用explode处理嵌套JSON设备测量数据的性能问题

问题核心痛点 #

• 原有多LATERAL VIEW explode方案会生成笛卡尔积，单条原始数据最多膨胀为2^15=32768行，50万行规模下性能完全不达标
• 扩展性差，新增设备会进一步放大数据膨胀问题

最优解决方案：行级高阶函数处理（无需展开数组） #

利用Hive 2.3及以上版本支持的高阶数组函数直接对每个设备的测量数组做排序取首，全程行内处理无数据膨胀，50万行数据可秒级跑完。

实现逻辑 #

1. 用get_json_object提取每个设备的测量数组，通过from_json将JSON数组转为Hive结构化数组类型
2. 用array_sort对数组按date字段降序排序，排序后第一个元素即为max(date)对应的最新测量数据
3. 直接提取排序后数组首个元素的value和date属性作为输出字段

完整SQL示例 #

SELECT
  -- 提取公司ID
  get_json_object(json_string, '$.company_id') as company_id,
  -- 处理device_1最新数据
  d1_latest.value as device_1_value,
  d1_latest.date as device_1_date,
  -- 处理device_2最新数据
  d2_latest.value as device_2_value,
  d2_latest.date as device_2_date,
  -- 其余device_3到device_15按相同逻辑补全即可
  ...
  d15_latest.value as device_15_value,
  d15_latest.date as device_15_date
FROM (
  SELECT
    json_string,
    -- 预计算每个设备的最新测量记录：数组按date倒序后取第一个元素
    array_sort(
      from_json(get_json_object(json_string, '$.device_1.measurements'), 'array<struct<value:string,date:string>>'),
      (a, b) -> if(a.date > b.date, -1, 1)
    )[0] as d1_latest,
    array_sort(
      from_json(get_json_object(json_string, '$.device_2.measurements'), 'array<struct<value:string,date:string>>'),
      (a, b) -> if(a.date > b.date, -1, 1)
    )[0] as d2_latest,
    -- 其余device_3到device_15按相同逻辑补全即可
    ...
    array_sort(
      from_json(get_json_object(json_string, '$.device_15.measurements'), 'array<struct<value:string,date:string>>'),
      (a, b) -> if(a.date > b.date, -1, 1)
    )[0] as d15_latest
  FROM T
) t

注意事项 #

• 如果测量数组可能为空，可加if(size(数组)=0, null, 数组[0])做兼容处理
• from_json中的字段类型（value、date的类型）需和实际JSON中的数据类型匹配，如date为时间戳可改为bigint类型
• 若Hive版本低于2.3不支持高阶函数，可自定义UDF实现数组取最新值的逻辑，性能依然远高于explode方案

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