Problem with reading _rt table in AWS Athena: GENERIC_INTERNAL_ERROR: class org.apache.hadoop.io.Text cannot be cast to class org.apache.hadoop.io.BooleanWritable

Hi Pavlo, thanks for sharing your question to re:Post.

Issue

In looking at the error message you shared, when querying a Hudi Merge-on-Read (MoR) real-time table (_rt) in Athena, you encountered type casting errors:

GENERIC_INTERNAL_ERROR: class org.apache.hadoop.io.Text cannot be cast to class org.apache.hadoop.io.BooleanWritable
GENERIC_INTERNAL_ERROR: Cannot inspect org.apache.hadoop.hive.serde2.io.DateWritableV2
GENERIC_INTERNAL_ERROR: class org.apache.hadoop.io.Text cannot be cast to class org.apache.hadoop.io.IntWritable

The _ro (read-optimized) table queries successfully, but the _rt (real-time) table fails.

---

Specific Diagnosis from Your Errors

Based on your error messages:

Your data has columns stored as TEXT/STRING in the Avro delta files, but the Glue Catalog schema defines them as:

• Boolean - Error: Text cannot be cast to BooleanWritable
• Integer - Error: Text cannot be cast to IntWritable
• Date - Error: Cannot inspect DateWritableV2

Why _ro works but _rt fails:

• _ro table: Reads only Parquet base files (which may have correct types)
• _rt table: Merges Parquet base files + Avro delta files (where types are strings)

Root cause: When your Deltastreamer reads CSV files, string values are being written to Avro delta files as TEXT instead of being converted to the proper data types (boolean, int, date) defined in your Avro schema.

This typically happens when:

1. Your Avro schema defines fields as string instead of proper types
2. CSV values aren't being cast to the correct types during ingestion
3. Schema validation isn't catching the mismatch

---

Root Cause

This error occurs due to schema mismatch or data type inconsistencies between:

1. The table schema registered in AWS Glue Data Catalog
2. The actual data types in the Parquet (base) and Avro (delta) files
3. How Athena interprets these types when querying MoR real-time tables

Hudi MoR tables store data in two formats:

• Base files: Parquet (columnar)
• Delta/log files: Avro (row-based)

When querying _rt tables, Athena must merge both formats. Type mismatches between the Glue schema and actual file data cause these casting errors.

Reference: Query Apache Hudi datasets

---

Common Causes

1. Schema Evolution Issues

Your Avro schema files may not match the actual data types in your CSV source or the Glue Catalog schema.

2. Hive Sync Configuration

The AwsGlueCatalogSyncTool may have registered incorrect data types in the Glue Catalog.

3. Spark Configuration Mismatches

Spark configurations for type handling may conflict with how Athena reads the data:

• spark.sql.parquet.int96AsTimestamp
• spark.sql.legacy.parquet.nanosAsLong
• spark.sql.parquet.binaryAsString

4. CSV to Avro Type Inference

When reading CSV files, Spark may infer types differently than what's defined in your Avro schema.

---

Solutions

Solution 1: Fix Your Avro Schema Files (Most Likely Fix)

This is likely your main issue. Check your Avro schema files at:

• /opt/spark/schemas/source/<sourceFolder>-schema.avsc
• /opt/spark/schemas/target/<sourceFolder>-schema.avsc

Your schemas might look like this:

{
  "type": "record",
  "name": "YourTable",
  "fields": [
    {"name": "boolean_field", "type": ["null", "string"]},  // ❌ Should be boolean
    {"name": "int_field", "type": ["null", "string"]},      // ❌ Should be int
    {"name": "date_field", "type": ["null", "string"]},     // ❌ Should be date
    {"name": "string_field", "type": ["null", "string"]}    // ✓ Correct
  ]
}

Change them to this, for example:

{
  "type": "record",
  "name": "YourTable",
  "fields": [
    {"name": "boolean_field", "type": ["null", "boolean"]},
    {"name": "int_field", "type": ["null", "int"]},
    {"name": "date_field", "type": ["null", {"type": "int", "logicalType": "date"}]},
    {"name": "string_field", "type": ["null", "string"]}
  ]
}

Check that:

• Boolean fields are defined as boolean, not string
• Integer fields are defined as int or long, not string
• Date fields use proper logical types

Reference: Apache Avro Specification

---

Solution 2: Update Glue Catalog Schema

The Glue Catalog schema may be incorrect. Update it manually or re-sync:

1. Check current schema in Glue Console:

   • Navigate to AWS Glue > Tables
   • Find your table_rt table
   • Review column data types

2. Update schema if needed:

   aws glue update-table --database-name <database> \
     --table-input '{
       "Name": "table_rt",
       "StorageDescriptor": {
         "Columns": [
           {"Name": "boolean_field", "Type": "boolean"},
           {"Name": "int_field", "Type": "int"},
           {"Name": "date_field", "Type": "date"}
         ]
       }
     }'

3. Or drop and re-sync the table:

   • Delete the _rt table from Glue Catalog
   • Re-run your Deltastreamer job to re-sync

Reference: AWS Glue Data Catalog

---

Solution 3: Add Schema Validation in Deltastreamer

Update your Deltastreamer configuration to enforce strict schema validation:

- --hoodie-conf
- "hoodie.avro.schema.validate=true"
- --hoodie-conf
- "hoodie.avro.schema.validate.enable=true"
- --hoodie-conf
- "hoodie.datasource.write.schema.allow.auto.evolution.column.drop=false"

You already have validation enabled, but ensure your source and target schemas are identical.

---

Solution 4: Use Explicit Type Casting in CSV Reading

Modify your Spark configuration to explicitly define CSV column types instead of relying on inference:

sparkConf:
  spark.sql.csv.inferSchema: "false"

Then define the schema explicitly in your transformer or use a schema file that matches your Avro schema exactly.

---

Solution 5: Query _ro Table Instead

As a temporary workaround, query the read-optimized (_ro) table instead of the real-time (_rt) table:

SELECT * FROM "AwsDataCatalog"."database"."table_ro" LIMIT 10;

Trade-off: The _ro table shows only compacted data and may not include the most recent updates until compaction runs.

Reference: Hudi Query Types

---

Solution 6: Check for Null Handling

Ensure your Avro schema properly handles nullable fields using union types:

{"name": "field_name", "type": ["null", "int"]}

Not:

{"name": "field_name", "type": "int"}

---

Debugging Steps

1. Compare schemas:

   # Check Glue Catalog schema
   aws glue get-table --database-name <db> --name table_rt
   
   # Check actual Parquet schema
   aws s3 cp s3://bucket/path/to/file.parquet - | parquet-tools schema -

2. Examine Avro schema files:

   • Review /opt/spark/schemas/source/<folder>-schema.avsc
   • Review /opt/spark/schemas/target/<folder>-schema.avsc
   • Ensure they match your CSV data types

3. Check Hudi metadata:

   SELECT * FROM "database"."table_rt$timeline" LIMIT 10;

4. Test with specific columns:

   -- Instead of SELECT *, test individual columns
   SELECT string_column FROM "database"."table_rt" LIMIT 10;
   SELECT int_column FROM "database"."table_rt" LIMIT 10;

   This helps identify which specific column has the type mismatch.

---

Additional Configuration to Try

Add these Hudi configurations to your Deltastreamer job:

- --hoodie-conf
- "hoodie.datasource.hive_sync.support_timestamp=true"
- --hoodie-conf
- "hoodie.datasource.hive_sync.skip_ro_suffix=false"
- --hoodie-conf
- "hoodie.datasource.hive_sync.skip_rt_sync=false"

---

References

• Query Apache Hudi datasets in Athena
• Using Hudi framework in AWS Glue
• Apache Hudi Documentation
• AWS Glue Data Catalog