Creating a table from query results (CTAS) - Amazon Athena
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Creating a table from query results (CTAS)

A CREATE TABLE AS SELECT (CTAS) query creates a new table in Athena from the results of a SELECT statement from another query. Athena stores data files created by the CTAS statement in a specified location in Amazon S3. For syntax, see CREATE TABLE AS.

CREATE TABLE AS combines a CREATE TABLE DDL statement with a SELECT DML statement and therefore technically contains both DDL and DML. However, note that for Service Quotas purposes, CTAS queries in Athena are treated as DML. For information about Service Quotas in Athena, see Service Quotas.

Use CTAS queries to:

  • Create tables from query results in one step, without repeatedly querying raw data sets. This makes it easier to work with raw data sets.

  • Transform query results and migrate tables into other table formats such as Apache Iceberg. This improves query performance and reduces query costs in Athena. For information, see Creating Iceberg tables.

  • Transform query results into storage formats such as Parquet and ORC. This improves query performance and reduces query costs in Athena. For information, see Columnar storage formats.

  • Create copies of existing tables that contain only the data you need.

Topics