How do I use the results of an Amazon Athena query in another query?

Short description

To use the results of an Athena query in another query, choose one of the following methods:

• Create a new table from the results with a CREATE TABLE AS SELECT (CTAS) query.
• Create a view.
• Use a WITH clause to run multiple SELECT statements in the same query.

Resolution

Create a new table from the Athena query results with a CTAS query

A CTAS query creates a new table from the results of a SELECT statement in another query. CTAS queries are useful when you want to transform data that you regularly query. For examples of CTAS queries, see Examples of CTAS queries.

Note: CTAS queries do have some limitations. For example, you can specify a maximum of only 100 new partitions. For more information, see Considerations and limitations for CTAS queries.

Create a view

Views are useful when you want to query the results of small- to medium-size queries that are specific and not expected to change. For more information, see Work with views.

Use a WITH clause to run multiple SELECT statements in the same query

Use a WITH clause to define one or more subqueries. Each subquery defines a temporary table, similar to a view definition. WITH clause subqueries are useful to efficiently define tables that you can use when the query runs. For more information, see Parameters.

Related information

How do I access and download the results of an Athena query?

Reuse query results in Athena

Top 10 performance tuning tips for Amazon Athena