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
