Icerberg Optimizing
0
The OPTIMIZE and VACUUM sql statements described in the docs here https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-data-optimization.html does not seems to be supported in iceberg tables.
HY000][100071] [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. line 1:1: mismatched input 'OPTIMIZE'. Expecting: 'ALTER', 'ANALYZE', 'CALL', 'COMMENT', 'COMMIT', 'CREATE', 'DEALLOCATE', 'DELETE', 'DENY', 'DESC', 'DESCRIBE', 'DROP', 'EXECUTE', 'EXPLAIN', 'GRANT', 'INSERT', 'MERGE', 'PREPARE', 'REFRESH', 'RESET', 'REVOKE', 'ROLLBACK', 'SET', 'SHOW', 'START', 'TRUNCATE', 'UNLOAD', 'UPDATE', 'USE', <query> [Execution ID not available] line 1:1: mismatched input 'OPTIMIZE'. Expecting: 'ALTER', 'ANALYZE', 'CALL', 'COMMENT', 'COMMIT', 'CREATE', 'DEALLOCATE', 'DELETE', 'DENY', 'DESC', 'DESCRIBE', 'DROP', 'EXECUTE', 'EXPLAIN', 'GRANT', 'INSERT', 'MERGE', 'PREPARE', 'REFRESH', 'RESET', 'REVOKE', 'ROLLBACK', 'SET', 'SHOW', 'START', 'TRUNCATE', 'UNLOAD', 'UPDATE', 'USE', <query> (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: b903dd29-d6b2-404c-9a94-e41bdf0d27cb; Proxy: null)
If this is true the docs should be updated.
asked 7 months ago168 views
1 Answer
- Newest
- Most votes
- Most comments
0
It sounds the issue is with the table definition, it is treating it as a generic JDBC table instead of an Iceberg table.
Make sure the table was added to the catalog by Iceberg itself or following the Athena instructions https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-creating-tables.html
Relevant content
AWS OFFICIALUpdated 8 months ago- AWS OFFICIALUpdated 9 months ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago
The table is added by iceberg. The table format is Apache Iceberg. What sounds weird is that each table added directly by iceberg does not show any info about Input/Output Format and Serde lib.
Iceberg tables are not standard, the main thing is that in the parameters specified "table_type": "ICEBERG" and the metadata_location
Got it. That said my tables have table_type ICEBERG and the metadata_location set. Still OPTIMIZE and VACUUM commands does not work from Athena. Any suggestion?