TABLE_REDIRECTION_ERROR in AWS Athena After Databricks Upgrade to 14.3 LTS

[Tam]

I have a Databricks pipeline set up to create Delta tables on AWS S3, using Glue Catalog as the Metastore. I was able to query the Delta table via Athena successfully. However, after upgrading Databricks Cluster from 13.3 LTS to 14.3 LTS, I began encountering TABLE_REDIRECTION_ERROR on AWS Athena.

TABLE_REDIRECTION_ERROR: Table 'awsdatacatalog.<database>.<table>' redirected to 'awsdatacatalog$delta-lake-aws.<database>.<table>', 
but the target table 'awsdatacatalog$delta-lake-aws.<database>.<table>' does not exist.!

[Kaniz]

Hi @Tam, 

It appears that you’ve encountered a TABLE_REDIRECTION_ERROR while working with your Databricks pipeline, AWS S3, Glue Catalog, and Athena. Let’s break down the issue and explore potential solutions:

1. AWS Glue as a Catalog for Databricks:

   • AWS Glue serves as an Apache-compatible Hive serverless metastore, allowing you to share table metadata across AWS services, applications, or accounts.
   • Databricks and Delta Lake are integrated with AWS Glue to discover data within your organization and register data in Delta Lake.
   • This integration simplifies manageability by using the same AWS Glue catalog across multiple Databri...¹.

2. TABLE_REDIRECTION_ERROR:

   • The error message indicates that a table redirection occurred, but the target table does not exist.
   • This could be due to changes in table metadata, catalog configuration, or compatibility issues during the Databricks Cluster upgrade.

3. Possible Solutions:

   • Check Table Metadata:
     • Verify that the table metadata in the Glue Catalog is accurate and consistent.
     • Ensure that the table names and paths match between Databricks and Glue Catalog.
   • Cluster Configuration:
     • Confirm that your Databricks cluster is correctly configured to access the AWS Glue Catalog.
     • Set up the necessary IAM role (e.g., Field_Glue_Role) and attach it to your cluster.
     • Adjust Spark Configuration properties as needed¹.
   • Direct Access to S3:
     • If querying directly from Athena, consider accessing the table in S3 without relying on Glue Catalog.
     • Keep in mind that this approach may bypass some metadata advantages.
   • Delta Lake Compatibility:
     • Ensure that your Delta Lake tables are compatible with the upgraded Databricks Cluster.
     • Check for any changes in Delta Lake behavior or features between the LTS versions.

4. Example Queries:

   • To query the table from Athena, use a query like:

     SELECT COUNT(*) FROM "AwsDataCatalog"."your_database"."your_table";
     

   • To append data to the Delta table, you can use:

     %sql INSERT INTO your_database.your_table SELECT * FROM your_source_view LIMIT 1000;
     

     Replace your_database, your_table, and your_source_view with the appropriate names².

Remember to validate your setup and ensure consistency across your components. 

[krystian]

Same problem with Athena and TABLE_REDIRECTION_ERROR

Works on runtime 13.3 but not on 14.3 LTS.

CREATE TABLE IF NOT EXISTS tablename (

      myid BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),

      name string,

      age int   

      )

      USING delta

      LOCATION 's3://location'

      TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'supported',delta.targetFileSize='128mb',delta.autoOptimize.autoCompact=true,delta.autoOptimize.optimizeWrite = true,spark.databricks.delta.schema.autoMerge.enabled = true)