Currently (I wrote this article in October 2020), Athena does not support temporary tables, but we can easily emulate them using the CREATE TABLE AS SELECT statements:
CREATE TABLE some_temp_table
WITH (format = 'PARQUET')
AS SELECT column_A, column_B, column_C
FROM source_table;
Unfortunately, we have to remember about removing the table when we no longer need it.
DROP TABLE some_temp_table
If we don’t specify the S3 location, Athena will use the default results bucket as the storage location. I think it is good enough in the case of a temporary table.
Note that I used Parquet as the storage file type. In general, you should pick a file format that is best for the operations you want to perform later. Parquet is a columnar storage file that stores metadata about the content to scan and find the relevant data quickly.
Because the table we create is just a regular table, we can also use partitioning:
CREATE TABLE some_temp_table
WITH (
format = 'PARQUET',
partitioned_by = ARRAY['column_A'])
)
AS SELECT column_A, column_B, column_C
FROM source_table;