AWS Documentation » Amazon Athena » User Guide » Querying Data in Amazon Athena Tables » Creating a Table from Query Results (CTAS) » Considerations and Limitations for CTAS Queries

The AWS Documentation website is getting a new look!
Try it now and let us know what you think. Switch to the new look >>

You can return to the original look by selecting English in the language selector above.

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. Note 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	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 and if your workgroup does not override client-side settings, Athena uses this location by default: s3://aws-athena-query-results-<account>-<region>/<query-name-or-unsaved>/<year>/<month/<date>/<query-id>/. If your workgroup overrides client-side settings, this means that the workgroup's query result location is used for your CTAS queries. If you specify a different results location, your query will fail. To obtain the results location specified for the workgroup, view workgroup's details. If the workgroup in which a query will run is configured with an enforced query results location, do not specify an external_location for the CTAS query. Athena issues an error and fails a query that specifies an external_location in this case. For example, this query fails, if you override client-side settings for query results location, enforcing the workgroup to use its own location: CREATE TABLE <DB>.<TABLE1> WITH (format='Parquet', external_location='s3://my_test/test/') AS SELECT * FROM <DB>.<TABLE2> LIMIT 10;
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. If the number of partitions to which the results data is written in parallel exceeds 100, Athena issues an error. 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.