How do I use the results of an Amazon Athena query in another query?
Last updated: 2022-01-05
I want to use the results of an Amazon Athena query to perform a second query.
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 run 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 when the query runs. For more information, see Parameters. Example:
WITH temp AS (SELECT * FROM tbl1 WHERE col1 = 1) SELECT * FROM tbl2, temp;