Retrieve snapshot IDs and timestamps for Iceberg tables in Athena

Question

How to retrieve all snapshot IDs and timestamps for Iceberg tables in Athena.
I tried running a query SELECT * FROM "iceberg_table_name$iceberg_history" but it does not work with Athena engine version 3. it fails with an error
NOT_SUPPORTED: Invalid Iceberg table name (unknown type 'iceberg_history'): iceberg_table_name$iceberg_history.

Accepted Answer

The table seems to have been renamed in Athena version 3:

You can perform 
```
SELECT * FROM "iceberg_table_name$history";
```
 instead.

For information, additional tables are availables using Athena v3, likes:
```
-- list snapshots
SELECT * FROM "iceberg_table_name$snapshots";
-- list used data files
SELECT * FROM "iceberg_table_name$files";
-- list partitions
SELECT * FROM "iceberg_table_name$partitions";
```

Sadly I didn't find theses system tables in AWS documentations, but by "trying" using the few information available on the internet.

Answer

Athena engine version 3 does not support the consults to Iceberg System Tables. In order you can perform the query successfully, you need to change workgroup group version to Athena Version 2 (Athena V2). To change the Athena version: 
- In the menu at the left side, click on Workgroups. 
- - Select the Workgroup you are using, in this case “Primary”, and then in the click on Actions>Edit. 
- - In the Edit Page, in the section “Query engine version” in the option “Upgrade query engine” click on Manual and finally in the Query engine version choose “Athena engine version 2” 
- - Save the changes and try to run again the query again.

References:
[1] Amazon Athena Workshop :: Hands on Labs
https://catalog.us-east-1.prod.workshops.aws/workshops/9981f1a1-abdc-49b5-8387-cb01d238bb78/en-US

[2] ACID Transactions with Iceberg
https://catalog.us-east-1.prod.workshops.aws/workshops/9981f1a1-abdc-49b5-8387-cb01d238bb78/en-US/90-athena-acid

[3] Timetravel on Iceberg table
https://catalog.us-east-1.prod.workshops.aws/workshops/9981f1a1-abdc-49b5-8387-cb01d238bb78/en-US/90-athena-acid/9013-timetravel-queries

Retrieve snapshot IDs and timestamps for Iceberg tables in Athena

相关内容