Issue: HIVE_BAD_DATA - Malformed Parquet File

Question

I'm encountering the following error when querying a Parquet file in Hive:

`HIVE_BAD_DATA: Malformed Parquet file. Field *sales* type INT32 in parquet file s3://s3-bbb-p-ue1-eeee/datasets/parquet//year=2021/month=01/part-00001-a82c381d-9a2a-4511-80b7-454330e8e0bb.c000.snappy.parquet is incompatible with type varchar defined in table schema [s3://s3-bbb-p-ue1-eeee/datasets/parquet/ext_ss_trk/year=2021/month=01/part-00001-a82c381d-9a2a-4511-80b7-454330e8e0bb.c000.snappy.parquet]
`

**What I've tried so far:**
* Altered the sales column in the Hive table from string to int.
* Ran MSCK REPAIR TABLE ; to refresh the metadata.
* Downloaded the Parquet file from S3 and verified the metadata using a Parquet Viewer Tool.

**The metadata shows:**
The field appears to be correctly defined as INT32, and the content looks fine.

```
{
  "Field_id": 0,
  "Name": "RTD_SALES",
  "Type": "INT32",
  "Type_length": 0,
  "LogicalType": null,
  "Scale": 0,
  "Precision": 0,
  "Repetition_type": "OPTIONAL",
  "Converted_type": "UTF8"
}
```

**Request:**
Despite these checks and updates, the error persists. Has anyone faced a similar issue or can suggest what might be going wrong here?

re:Post Agent · Answer

The error you're encountering indicates a data type mismatch between your Parquet file and the table schema definition. The specific issue is that the field "sales" is stored as INT32 in the Parquet file, but it's defined as VARCHAR in your table schema.

There appears to be a contradiction in your Parquet metadata - while the Type is listed as "INT32", the Converted_type is listed as "UTF8" (which corresponds to a string/varchar type). This mixed typing is likely causing the confusion.

Here are some approaches to resolve this issue:

1. Update the table schema: Ensure your table definition matches the actual data type in the Parquet file. Since the field is stored as INT32, modify your table definition to use INTEGER instead of VARCHAR for the sales column.

2. Check schema consistency: Verify that all your Parquet files have consistent schemas. It's possible that different files in your dataset have different schemas, causing intermittent errors.

3. Use CAST in your query: As a temporary workaround, you can use a CAST function in your SELECT statement to convert between types.

4. Recreate the table: You could use a CREATE TABLE AS SELECT (CTAS) query to create a new table with the correct schema while converting the data types as needed.

5. Investigate your ETL process: There might be an issue in how the data is being processed or converted before being written to Parquet format. The "Converted_type" being set to "UTF8" while the main type is "INT32" suggests there might be a conversion issue in your data pipeline.

Note that Hive has had issues with Parquet type compatibility in the past. For example, Amazon EMR 6.9.0 included a backport fix for "HIVE-21987: Hive is unable to read Parquet int32 annotated with decimal," which addressed a similar type of incompatibility issue.  
**Sources**  
[Incompatible Issue with timestamp | AWS re:Post](https://repost.aws/questions/QU3zCLltcBRMiE48bE867Q7w/incompatible-issue-with-timestamp)  
[Amazon EMR 6.9.0 - Hive release notes - Amazon EMR](https://docs.aws.amazon.com/emr/latest/ReleaseGuide/Hive-release-history-690.html)  
[field data type INT32 is incompatible with type varchar defined in table schema, when selecting random samples | AWS re:Post](https://repost.aws/questions/QU7wuqMElpTIS7u6aTtAzafA/field-data-type-int32-is-incompatible-with-type-varchar-defined-in-table-schema-when-selecting-random-samples)

Issue: HIVE_BAD_DATA - Malformed Parquet File

Add your answer

Relevant content