Table doesn't update.

Question

I'm creating a table with this query:

```
CREATE TABLE test_1 AS
SELECT id, created_at, transaction_type, customer_id, amount, description FROM "db_1"."transactions"
WHERE (customer_id != xxxx AND customer_id != yyyy) AND transaction_type = 'type_4' AND description = 'Some Description'
UNION ALL
SELECT id, created_at, transaction_type, customer_id, amount, description FROM "db_1"."transactions"
WHERE customer_id = xxxx AND (transaction_type = 'type_1'
OR transaction_type = 'type_2' OR transaction_type = 'type_3')
```

The query gets me what I need correctly, except that it doesn't update itself. The issue could be because I'm building a **secondary table** from the **main table**, but since the main table comes directly from an S3 bucket, when the bucket is updated, the **main table** updates as well. But the **secondary table** doesn't.

So, what should I do differently? Make a reference to the S3 bucket instead of the tables? How would that look like?

Thanks in advance for any suggestions.

Answer

CREATE TABLE AS does not update the underlying data. https://docs.aws.amazon.com/athena/latest/ug/create-table-as.html

Instead try CREATE VIEW and the VIEW would be updated as the underlying data in the TABLE is updated. https://docs.aws.amazon.com/athena/latest/ug/create-view.html

Table doesn't update.

Relevant content