Amazon Athena
User Guide

Running CTAS Queries in the Console

In the Athena console, you can:

To create a CTAS query from another query

  1. Run the query, choose Create, and then choose Create table from query.

                  The screenshot for creating a table from query results.
  2. In the Create a new table on the results of a query form, complete the fields as follows:

    1. For Database, select the database in which your query ran.

    2. For Table name, specify the name for your new table. Use only lowercase and underscores, such as my_select_query_parquet.

    3. For Description, optionally add a comment to describe your query.

    4. For Output location, optionally specify the location in Amazon S3, such as s3://my_athena_results/mybucket/. If you don't specify a location and your workgroup does not Override Client-Side Settings, the following predefined location is used: s3://aws-athena-query-results-<account>-<region>/<query-name-or-unsaved>/year/month/date/<query-id>/.

    5. For Output data format, select from the list of supported formats. Parquet is used if you don't specify a format. See Columnar Storage Formats.

                        The screenshot for the CTAS wizard screen.
    6. Choose Next to review your query and revise it as needed. For query syntax, see CREATE TABLE AS. The preview window opens, as shown in the following example:

                     The screenshot for the CTAS wizard preview screen.
    7. Choose Create.

  3. Choose Run query.

To create a CTAS query from scratch

Use the CREATE TABLE AS SELECT template to create a CTAS query from scratch.

  1. In the Athena console, choose Create table, and then choose CREATE TABLE AS SELECT.

                  The screenshot of the CREATE TABLE AS SELECT for a new table.
  2. In the Query Editor, edit the query as needed, For query syntax, see CREATE TABLE AS.

  3. Choose Run query.

  4. Optionally, choose Save as to save the query.

See also Examples of CTAS Queries.