Assistance Needed – Error Creating Athena Table from Delta Lake

Hello,

I understand you are trying to create an Athena table based on a Delta Lake table stored in S3, however you are encountering an unexpected error, even though you have successfully created similar tables in nearby buckets.

The query you are trying to run is as along with its error is as follows:

CREATE EXTERNAL TABLE IF NOT EXISTS dicom.dicom_daily_traffic_images LOCATION 's3://medigate-data-lake/dicom_daily/traffic/images/' TBLPROPERTIES ('table_type' = 'DELTA');

Delta Table DDL failed with an unexpected error. This query ran against the "dicom" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: b6d3a92d-f513-4172-b8a7-b9018abb597e

Analyzing the error from the backend reveals that your delta table data may be the cause of this error. This error is triggered when trying to parse your delta lake schema however the format is not as expected thus the error, please make sure your delta lake data is in correct format.

To make sure that your delta lake table/s is in the required format, I suggest you create a crawler that will crawl the delta lake table/s and register them in one of your databases of choice in AWSdatacatalog.

to create a crawler for testing out you delta lake format is expected please follow the steps outlined in this documentation[1].

In this instruction, create the crawler through the console. Complete the following steps to create a Delta Lake crawler:

1. Open the AWS Glue console.
2. Choose Crawlers.
3. Choose Create crawler.
4. For Name, enter delta-lake-native-crawler, and choose Next.
5. Under Data sources, choose Add a data source.
6. For Data source, select Delta Lake.
7. For Include delta lake table path(s), enter s3://your_s3_bucket/data/sample_delta_table/.
8. For Create tables for querying, choose Create Native tables,
9. Choose Add a Delta Lake data source.
10. Choose Next.
11. For Existing IAM role, choose your IAM role, then choose Next.
12. For Target database, choose Add database, then Add database dialog appears. For Database name, enter delta_lake_native, then choose Create. Choose Next.
13. Choose Create crawler.
14. The Delta Lake crawler can be triggered to run through the console or through the SDK or AWS CLI using the StartCrawl API. It could also be scheduled through the console to trigger the crawlers at specific times. In this instruction, run the crawler through the console.
15. Select delta-lake-native-crawler, and choose Run.
16. Wait for the crawler to complete.

Once you have completed the steps above:

• If the crawler runs successfully and you can see your table in Glue that means your delta table data is in correct format. You can then query your data in Athena.
• If the crawler fails that means your delta table structure or format is not correct.

Additionally, you may alter your query to:

CREATE EXTERNAL TABLE db_name.table_name(col array<string>) LOCATION 's3://amzn-s3-demo-bucket/your-folder/' TBLPROPERTIES ('spark.sql.sources.provider' = 'delta')

Use the following DDL to create your Delta Lake table. Note that when using this method, the value for TBLPROPERTIES must be 'spark.sql.sources.provider' = 'delta' and not 'table_type' = 'delta'.

Note that this same schema (with a single of column named col of type array<string>) is inserted when you use Apache Spark (Athena for Apache Spark) or most other engines to create your table [2].

The error you're encountering when creating an Athena table from a Delta Lake table appears to be related to how Athena interacts with Delta Lake tables. There are a few potential causes and solutions to consider:

1. Delta Lake Version Compatibility: Athena has specific compatibility requirements for Delta Lake tables. It supports reader versions up to 3 for DQL queries and up to 1 for DDL queries. If your Delta Lake table uses a higher reader version, you might encounter issues.

2. Table Registration: Delta Lake tables must be registered with AWS Glue to work properly with Athena. Make sure your Delta Lake table is properly registered.

3. DDL Support Limitations: Athena has limited DDL support for Delta Lake tables. While CREATE EXTERNAL TABLE is supported, there might be specific requirements or limitations for this operation.

4. Empty Location: One common cause of errors when creating tables is if the database has an empty LOCATION property. Verify that the S3 location you're specifying contains valid Delta Lake table data.

5. Permissions: Ensure you have the necessary permissions to access both the Delta Lake data in S3 and to create tables in Athena.

For your specific case, I recommend:

1. Verify that the S3 path 's3://medigate-data-lake/dicom_daily/traffic/images/' contains valid Delta Lake table data.
2. Check if you need to configure Spark properties for Delta Lake in Athena:

"spark.sql.catalog.spark_catalog" : "org.apache.spark.sql.delta.catalog.DeltaCatalog", 
"spark.sql.extensions" : "io.delta.sql.DeltaSparkSessionExtension"

3. Try using the "Synchronize Delta Lake metadata" process if your Delta Lake table has a reader version greater than 1.
4. If the issue persists, you might need to contact AWS Support with the specific query ID you provided for more detailed troubleshooting.

If you still face any difficulties, don't hesitate to reach back. I am here to assist you.

Please also follow along this re:post thread as a similar issue was also being discussed and resolved. https://repost.aws/questions/QU8cguL7_zSjCdNfGPTsIpQg/athena-query-to-a-native-delta-table-fails-with-internal-error-after-15-minutes-of-execution-what-s-wrong

References: [1]: https://aws.amazon.com/blogs/big-data/introducing-native-delta-lake-table-support-with-aws-glue-crawlers/ [2]: https://docs.aws.amazon.com/athena/latest/ug/delta-lake-tables-syncing-metadata.html Troubleshoot table errors - Amazon Athena
Use Linux Foundation Delta Lake tables in Athena for Spark - Amazon Athena
Query Linux Foundation Delta Lake tables - Amazon Athena