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 Table, database, or column names for CTAS queries should not contain quotes or backticks. To ensure this, check that your table, database, or column names do not represent reserved words, and do not contain special characters (which require enclosing them in quotes or backticks). For more information, see Names for Tables, Databases, and Columns. |
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 |
If your workgroup overrides the client-side setting for query results
location, Athena creates your table in the location
If your workgroup does not override the query results location,
you can use the syntax The If you omit the |
Locating Orphaned Files |
If a |
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 |
Compression formats |
GZIP compression is used for CTAS query results by default. For Parquet and ORC, you can also specify SNAPPY. See Example: Specifying Data Storage and Compression Formats. |
Partition and Bucket Limits |
You can partition and bucket the results data of a CTAS query. For more information, see Bucketing vs Partitioning. Athena supports writing to 100 unique partition and bucket combinations. For example, if no buckets are defined in the destination table, you can specify a maximum of 100 partitions. If you specify five buckets, 20 partitions (each with five buckets) are allowed. If you exceed this count, an error occurs. Include partitioning and bucketing predicates at the end of the
For information about working around the 100-partition limitation, see Using CTAS and INSERT INTO to Create a Table with More Than 100 Partitions. |
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 Encrypting Query Results Stored in Amazon S3. |
Data types |
Column data types for a CTAS query are the same as specified for the original query. |