Create Athena Tables using Playground

The Playground allows you to create Athena tables using two methods:

• CREATE TABLE AS SELECT (CTAS)
• CREATE EXTERNAL TABLE

Successfully created tables are automatically synchronized with Amorphic datasets through the resource sync functionality.

CREATE TABLE AS SELECT (CTAS)

CTAS queries enable you to create new Athena tables from the results of a SELECT query. These tables can source data from existing Athena or Lake Formation datasets.

Important: Table names are automatically converted to lowercase, regardless of the case used in your query. We recommend using lowercase names in your queries to maintain consistency.

How It Works

1. When you submit a CTAS query, a new dataset appears in your datasets list with basic information
2. Upon successful query execution, resource sync updates the dataset with complete details
3. Query results become available under the dataset's Files tab
4. If the query fails, resource sync removes the dataset automatically

   Note: Wait for the deletion to complete before retrying the query to avoid "dataset already exists" errors

Query Syntax

CREATE TABLE [IF NOT EXISTS] <target_domain>.<new_table_name>
[ WITH (property_name = expression [, ...]) ]
AS SELECT <column1>, <column2>, ..., upload_date
FROM <source_domain>.<existing_table_name> [ WHERE <condition> ]
[ WITH NO DATA ]

Required: Include upload_date in your SELECT query as it serves as a default partition.

Supported Properties

Property Name	Description	Notes
table_type	Table type specification	Currently only 'HIVE' supported
external_location	S3 location for query results	Format: s3://<DLZ_BUCKET_NAME>/<target_domain>/<new_table_name>/ Added automatically if not specified
format	Storage format for results	Currently only 'PARQUET' supported
partitioned_by	Array of partition columns	- Must be String type - Columns must appear in SELECT query - upload_date automatically added as final partition

For additional property options, see the AWS CTAS documentation.

Common Use Cases

• Data Optimization: Convert large datasets into smaller, more efficient ones
• Data Filtering: Create focused datasets with specific columns and rows
• Performance Enhancement: Add partitioning to unoptimized datasets to improve query performance and reduce costs

CREATE EXTERNAL TABLE

Use this query type to create new Athena tables with custom-defined properties and structure.

Query Syntax

CREATE EXTERNAL TABLE [IF NOT EXISTS]
 <domain_name>.<new_table_name> [(col_name data_type [COMMENT col_comment] [, ...] )]
 [COMMENT table_comment]
 [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
 [ROW FORMAT row_format]
 [STORED AS file_format]
 [WITH SERDEPROPERTIES (...)]
 [LOCATION 's3://<DLZ_BUCKET_NAME>/<target_domain>/<new_table_name>/']
 [TBLPROPERTIES ( property_name=property_value [, ...] ) ]

Important Considerations

• Maintain the exact keyword order as shown in the syntax
• Specify LOCATION using the format: s3://<DLZ_BUCKET_NAME>/<target_domain>/<new_table_name>/
• upload_date is automatically added as a default partition
• Use only Amorphic-supported properties to ensure successful table synchronization

For detailed property specifications, refer to the AWS External Table documentation.