---
title: "How to emulate temporary tables in Athena"
description: "Use CTAS to create a temporary table in Athena"
author: "Bartosz Mikulski"
author_bio: "Principal AI Engineer & MLOps Architect. I bridge the gap between \"it works in a notebook\" and \"it works for 200 million users.\""
author_url: https://mikulskibartosz.name
author_linkedin: https://www.linkedin.com/in/mikulskibartosz/
author_github: https://github.com/mikulskibartosz
canonical_url: https://mikulskibartosz.name/temporary-tables-in-athena
---

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:

```sql
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.

```sql
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:

```sql
CREATE TABLE some_temp_table
    WITH (
        format = 'PARQUET',
        partitioned_by = ARRAY['column_A'])
    )
    AS SELECT column_A, column_B, column_C
    FROM source_table;
```