在Hive中查询JSON数据集时读取外部资源失败问题求助

Let’s walk through the most common issues that could be triggering your Failed to read external... error, along with practical fixes for each scenario:

1. Legacy or Incorrect JSON SerDe Class #

The org.apache.hadoop.hive.contrib.serde2.JsonSerDe you’re using is a deprecated SerDe that often doesn’t play well with newer Hive versions. Here’s how to fix this:

• Switch to a supported SerDe: For modern Hive setups, use either:
  • OpenX’s maintained JSON SerDe: org.openx.data.jsonserde.JsonSerDe (requires the json-serde jar)
  • Hive’s built-in JSON SerDe (available in Hive 2.x+): org.apache.hadoop.hive.serde2.JsonSerDe
• Verify the jar is loaded: Run this command before creating your table (or add it to your Hive config permanently):

  ADD JAR /path/to/json-serde-1.3.8-jar-with-dependencies.jar; -- Update path to your jar file
  

• Recreate the table with the correct SerDe:

  create external table if not exists table-name(col1 string) 
  row format serde 'org.openx.data.jsonserde.JsonSerDe';
  

2. Invalid JSON File Format #

Hive’s JSON SerDe requires line-delimited JSON (one complete JSON object per line). If your file is a multi-line JSON array, it will fail to parse.

• Check your file structure: Run this HDFS command to inspect the content:

  hdfs dfs -cat /user/file1.json
  

  Valid format example:

  {"col1": "value1"}
  {"col1": "value2"}
  

  Invalid format (array) example:

  [{"col1": "value1"}, {"col1": "value2"}]
  

• Fix the JSON file: Convert the array to line-delimited format using a tool like jq:

  jq -c '.[]' /local/path/to/file1.json > /local/path/to/fixed-file.json
  # Upload the fixed file to HDFS:
  hdfs dfs -put /local/path/to/fixed-file.json /user/
  

3. HDFS Path or Permission Issues #

As an external table, Hive needs unobstructed access to your JSON file and its storage location:

• Confirm the file exists:

  hdfs dfs -ls /user/file1.json
  

• Check table location permissions: If you didn’t specify a LOCATION when creating the table, Hive uses the default path: /user/hive/warehouse/databasename.db/table-name. Verify the file was moved here (since LOAD DATA INPATH moves files for external tables too) and that the Hive user has read access:

  hdfs dfs -ls /user/hive/warehouse/databasename.db/table-name
  hdfs dfs -getfacl /user/hive/warehouse/databasename.db/table-name
  

• Use a custom location (if needed): Recreate the table to point directly to your file’s directory (not the file itself):

  create external table if not exists table-name(col1 string) 
  row format serde 'org.openx.data.jsonserde.JsonSerDe'
  location '/user/'; -- Points to the directory containing file1.json
  

4. Mismatched Column Names and JSON Keys #

Hive’s JSON SerDe is case-sensitive by default. If your JSON uses a different key name (e.g., Col1 instead of col1) or the key is missing, it will fail to read the data.

• Validate key consistency: Ensure the JSON keys exactly match your table’s column names (including case).
• Map mismatched keys: If you can’t modify the JSON, use SerDe properties to map the JSON key to your table column:

  create external table if not exists table-name(col1 string) 
  row format serde 'org.openx.data.jsonserde.JsonSerDe'
  with serdeproperties (
    "mapping.col1" = "ActualKeyNameInJSON" -- Replace with your JSON key name
  );
  

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