How to store Amazon Athena Query Results into new Table

Question

I need to store Amazon Athena query results into New Amazon Athena Table. Can someone help me with this?

Answer 1

This is not supported by Athena as Amazon Athena does not support INSERT or CTAS (Create Table As Select) queries.

Amazon athena stores query result in S3. Here is a documentation on how Athena works. https://docs.aws.amazon.com/athena/latest/ug/querying.html

As Athena only points to data in S3. So once results are in S3, you can use athena to point to this as a new table.

Answer 2

You can use CTAS - as Amazon Athena support it :

for example :

CREATE TABLE new_table
WITH (
      format = 'Parquet',
      parquet_compression = 'SNAPPY')
AS SELECT *
FROM old_table

Amazon Athena DO support CTAS:

https://docs.aws.amazon.com/athena/latest/ug/create-table-as.html