Oracle数据库Blob中JSON数据的Spark SQL Schema自动推断方法

核心思路 #

要处理Blob存储的JSON数据，需分两步实现：先将二进制Blob转换为JSON字符串，再通过Spark SQL内置函数自动推断Schema并解析JSON，之后即可对嵌套字段进行操作。

具体实现步骤 #

1. Blob转JSON字符串
   使用Spark SQL的binary_to_string函数，将Blob类型的response字段转换为可读的JSON字符串。如果JSON采用特定编码（如GBK），可指定编码参数：binary_to_string(response, 'UTF-8')。

2. 自动推断JSON Schema
   利用schema_of_json函数，传入一条非空的JSON字符串样本，让Spark自动生成完整的JSON Schema。该函数返回的字符串形式Schema，可直接用于from_json函数解析JSON。

3. 解析JSON并操作嵌套字段
   结合from_json和LATERAL VIEW EXPLODE，即可展开嵌套的user_data数组，实现后续SQL操作。

完整SQL示例 #

-- 通过CTE获取自动推断的JSON Schema
WITH inferred_schema AS (
    SELECT schema_of_json(binary_to_string(response)) AS json_schema_str
    FROM my_table
    WHERE response IS NOT NULL  -- 确保获取有效的JSON样本
    LIMIT 1
)
SELECT 
    t.id,
    t.request_timestamp,
    exploded_user.*
FROM my_table t
-- 解析JSON并展开user_data数组
LATERAL VIEW EXPLODE(
    from_json(binary_to_string(t.response), (SELECT json_schema_str FROM inferred_schema)).user_data
) exploded_user

注意事项 #

• 需确保选取的JSON样本包含所有可能的字段，否则schema_of_json推断的Schema会缺失字段，导致后续解析丢失数据。若数据存在多结构JSON，建议选取字段最完整的记录作为样本。
• 部分Spark版本中，binary_to_string需显式指定字符集，需根据JSON实际编码调整参数。
• 若表中所有response字段为空，该方法会失效，需确保至少存在一条有效的JSON记录。

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