求助：AWS Athena创建表时JSON数组解析错误问题排查

Root Cause #

The error you're seeing happens because AWS Glue inferred an incorrect schema for your data. Your sample data is either a single struct ({"lts":150}) or an array of structs ([{"lts":150}]), but Glue generated a schema for an array of arrays of structs (array<array<struct<lts:int>>>. When Athena tries to parse the data using this mismatched schema, it expects a JSONArray but encounters a JSONObject—hence the cast error.

Step-by-Step Solution #

1. Confirm Your Actual Data Structure #

First, double-check a few sample records in your S3 bucket to clarify the structure:

• Case 1: Each line is a single struct: {"lts":150}
• Case 2: Each line is an array of structs: [{"lts":150}, {"lts":200}]
• Case 3: Entire file is one big array of structs (all records in one line): [{"lts":150}, {"lts":200}]

2. Create the Table with the Correct Schema #

Instead of relying on Glue's auto-inference, define the table manually in Athena with a schema that matches your data.

Case 1: Single Struct per Line

Use this DDL to create a table with a direct lts column:

CREATE EXTERNAL TABLE IF NOT EXISTS your_table_name (
  lts INT
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://your-bucket-path/your-data-folder/'
TBLPROPERTIES (
  'has_encrypted_data' = 'false',
  'serialization.format' = '1'
);

Case 2: Array of Structs per Line

Define a table with an array column, then use UNNEST to query individual records:

CREATE EXTERNAL TABLE IF NOT EXISTS your_table_name (
  records ARRAY<STRUCT<lts: INT>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://your-bucket-path/your-data-folder/'
TBLPROPERTIES (
  'has_encrypted_data' = 'false',
  'serialization.format' = '1'
);

To extract individual lts values:

SELECT r.lts
FROM your_table_name, UNNEST(records) AS t(r);

Case 3: Single Array per File

If your data is stored as one large array (all records in a single line), use Athena's OPENROWSET to parse it correctly:

SELECT *
FROM OPENROWSET(
  FORMAT = 'JSON',
  LOCATION = 's3://your-bucket-path/your-data-file.json'
) WITH (
  lts INT PATH '$[*].lts'
) AS t;

Alternatively, preprocess the data with AWS Lambda to split each struct into its own line for easier querying.

3. Verify the Fix #

After creating the table with the correct schema, run a test query like:

SELECT * FROM your_table_name LIMIT 10;

This should resolve the HIVE_BAD_DATA error if the schema matches your data structure.

Why Glue Inferred the Wrong Schema #

Glue's schema inference only samples a subset of your data. If some of the sampled records had a nested array structure (e.g., [[{"lts":150}]]), Glue would infer the nested array schema even if most records don't follow that pattern. Manually defining the schema ensures alignment with your actual data.

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