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

Last updated: 2020-06-12

I want to use the results of an Amazon Athena query to perform a second query. How do I do that?

Resolution

Use one of the following methods to use the results of an Athena query in another query:

  • CREATE TABLE AS SELECT (CTAS): A CTAS query creates a new table from the results of a SELECT statement in another query. CTAS is useful for transforming data that you want to query regularly. CTAS has some limitations. For example, you can specify a maximum of 100 new partitions. For more information, see Considerations and Limitations for CTAS Queries. For examples, see Examples of CTAS Queries.
  • Create a view: Views are useful for querying the results of small-to-medium size queries that are specific and not expected to change. For more information, see Working with Views.
  • Use the WITH clause to execute multiple select statements at the same time: Use the WITH clause to define one or more subqueries. Each subquery defines a temporary table, similar to a view definition. Use WITH clause subqueries to efficiently define tables that you can use throughout the execution of a single query. For more information, see Parameters. Example:
WITH temp AS (SELECT * FROM tbl1 WHERE col1 = 1 ) SELECT * FROM tbl2, temp;

Did this article help you?

Anything we could improve?


Need more help?