Amazon Athena
User Guide

Considerations and Limitations for CTAS Queries

The following table describes what you need to know about CTAS queries in Athena:

Item What You Need to Know
CTAS query syntax

The CTAS query syntax differs from the syntax of CREATE [EXTERNAL] TABLE used for creating tables. See CREATE TABLE AS.

CTAS queries vs views

CTAS queries write new data to a specified location in Amazon S3, whereas views do not write any data.

Location of CTAS query results

The location for storing CTAS query results in Amazon S3 must be empty. A CTAS query checks that the path location (prefix) in the bucket is empty and never overwrites the data if the location already has data in it. To use the same location again, delete the data in the key prefix location in the bucket, otherwise your CTAS query will fail.

You can specify the location for storing your CTAS query results. If omitted, Athena uses this location by default: s3://aws-athena-query-results-<account>-<region>/<query-name-or-unsaved>/<year>/<month/<date>/<query-id>/.

Formats for storing query results

The results of CTAS queries are stored in Parquet by default, if you don't specify a data storage format. You can store CTAS results in PARQUET, ORC, AVRO, JSON, and TEXTFILE. CTAS queries do not require specifying a SerDe to interpret format transformations. See Example 5: Storing Results of a CTAS Query in Another Format.

Compression formats

GZIP compression is used for CTAS query results by default. For Parquet and ORC, you can also specify SNAPPY. See Example 4: Specifying Data Storage and Compression Formats for CTAS Query Results.

Partitioning

You can partition the results data of a CTAS query by one or more columns. When creating a partitioned table, Athena automatically adds partitions to the AWS Glue Data Catalog.

Important

The results of a CTAS query in Athena can have a maximum of 100 partitions that Athena creates for you when writing CTAS query results to a specified location in Amazon S3.

List partition columns at the end of the SELECT statement in a CTAS query. For more information, see Example 7: CTAS Queries with Partitions and Bucketing vs Partitioning.

Bucketing

You can configure buckets for storing the results of a CTAS query and bucket data by one or more columns. There is no limit to the number of buckets you can specify. For more information, see Example 8: A CTAS Query with Bucketing and Bucketing vs Partitioning.

Encryption

You can encrypt CTAS query results in Amazon S3, similar to the way you encrypt other query results in Athena. For more information, see Configuring Encryption Options.

Data types

Column data types for a CTAS query are the same as specified for the original query.