基于含JSON字符串的分区Parquet文件创建Hive外部表非分区字段返回NULL如何解决

问题核心原因 #

你定义的建表语句和Parquet文件的实际存储结构不匹配：你存储的Parquet文件中仅包含单个JSON字符串类型的列，并非与你声明的a、b、c对应的结构化列。Hive按照你定义的三个字段去Parquet文件中匹配同名列，找不到对应值就会返回NULL，只有从路径解析的分区字段可以正常返回。

解决方案 #

方案1：直接解析JSON字符串（无需修改原始文件，适合快速验证） #

1. 先确认Parquet文件中存储JSON的字段名，可通过parquet-tools工具查看：

parquet-tools head /user/output/dates=2021-09-27/hour=09/something.parquet

通常默认写入的JSON字段名为json_str、value或data，下文中假设字段名为json_str。
2. 重新创建匹配Parquet实际结构的外部表：

CREATE EXTERNAL TABLE test_table_raw (
  json_str string
) 
PARTITIONED BY (dates string, hour string) 
STORED AS PARQUET 
LOCATION '/user/output/';

3. 执行分区修复：

MSCK REPAIR TABLE test_table_raw;

4. 解析JSON得到目标字段，可以直接查询或者创建视图简化后续使用：

• 直接查询示例：

SELECT 
  cast(get_json_object(json_str, '$.a') as int) as a,
  cast(get_json_object(json_str, '$.b') as int) as b,
  cast(get_json_object(json_str, '$.c') as int) as c,
  dates,
  hour
FROM test_table_raw;

• 创建视图示例：

CREATE VIEW test_table AS
SELECT 
  cast(get_json_object(json_str, '$.a') as int) as a,
  cast(get_json_object(json_str, '$.b') as int) as b,
  cast(get_json_object(json_str, '$.c') as int) as c,
  dates,
  hour
FROM test_table_raw;

视图创建完成后，直接查询test_table即可得到完整数据。

方案2：转换为结构化Parquet存储（适合高频查询场景，性能更优） #

如果后续需要经常查询该表，建议提前解析JSON并存储为结构化Parquet文件，避免每次查询都重复解析JSON：

CREATE TABLE test_table_structured
PARTITIONED BY (dates string, hour string)
STORED AS PARQUET
AS
SELECT 
  cast(get_json_object(json_str, '$.a') as int) as a,
  cast(get_json_object(json_str, '$.b') as int) as b,
  cast(get_json_object(json_str, '$.c') as int) as c,
  dates,
  hour
FROM test_table_raw;

注意事项 #

• 确保get_json_object中指定的JSON路径与实际JSON内容的key完全一致，大小写、特殊字符都需要匹配，否则会返回NULL。
• 如果需要解析的JSON字段较多，可以使用json_tuple函数同时解析多个字段，简化语法。

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