Setting the file. One moment.
Skill 123 · Creating Data Lake Table
Subchapter 123.2
references/athena-ddl-path.mdMarkdown3 KBView on GitHub
Alternative to the S3 Tables API. Use when the user specifically wants SQL DDL or needs schema evolution via ALTER TABLE after creation.
s3tablescatalog) MUST be registered (see Step 5 in SKILL.md)--result-configuration on each query.The catalog reference goes in --query-execution-context, NOT in the SQL statement. Use <database>.<table> format in SQL:
CREATE TABLE <namespace>.<table_name> (
<column_definitions>
)
PARTITIONED BY (<partition_columns>)
TBLPROPERTIES ('table_type' = 'ICEBERG')CRITICAL: Do NOT include a LOCATION clause. S3 Tables manages storage automatically. This differs from regular Athena external tables.
CRITICAL: Do NOT put the catalog name in the SQL. Athena cannot parse s3tablescatalog/<bucket> as a catalog identifier in DDL. It goes in the execution context only.
aws athena start-query-execution \
--query-string "<DDL>" \
--query-execution-context '{"Catalog": "s3tablescatalog/<BUCKET_NAME>", "Database": "<NAMESPACE>"}' \
--work-group "<WORKGROUP>" \
--result-configuration '{"OutputLocation": "s3://<RESULTS_BUCKET>/output/"}'Check status with aws athena get-query-execution --query-execution-id <ID>.
The results bucket MUST be in the same region as the table bucket.
Use the same execution context pattern for SELECT queries:
aws athena start-query-execution \
--query-string "SELECT * FROM <namespace>.<table_name> LIMIT 10" \
--query-execution-context '{"Catalog": "s3tablescatalog/<BUCKET_NAME>", "Database": "<NAMESPACE>"}' \
--work-group "<WORKGROUP>" \
--result-configuration '{"OutputLocation": "s3://<RESULTS_BUCKET>/output/"}'athena:StartQueryExecution, athena:GetQueryExecution, athena:GetQueryResults plus S3 access to the results bucket. Also requires S3 Tables and Glue permissions — see access-control.md.ALTER TABLE uses the same --query-execution-context pattern:
aws athena start-query-execution \
--query-string "ALTER TABLE <namespace>.<table_name> ADD COLUMNS (<col> <type>)" \
--query-execution-context '{"Catalog": "s3tablescatalog/<BUCKET_NAME>", "Database": "<NAMESPACE>"}' \
--work-group "<WORKGROUP>" \
--result-configuration '{"OutputLocation": "s3://<RESULTS_BUCKET>/output/"}'Supported operations: ALTER TABLE ADD COLUMNS, ALTER TABLE DROP COLUMN. WARNING: schema changes affect all future queries. You MUST confirm with the user before executing.
Alternative: Schema evolution is also supported via the S3 Tables Iceberg REST API or the S3 Tables Catalog for Apache Iceberg (open-source). Search AWS docs for "S3 Tables Catalog for Apache Iceberg" for setup.
For latest Athena DDL syntax, search AWS docs for "Creating Iceberg tables in Athena" and "Supported data types for Iceberg tables in Athena".