presto athena tables as variables

user_6646710 0
Viewed 191

I have an SQL in aws-athena which looks more or less like this

SELECT * FROM "db1"."2021_08_31" WHERE condition
Union
SELECT * FROM "db2"."2021_08_31" WHERE condition

...

Is there an intelligent way of rewriting this, so in case I want to switch from 2021_08_31 to 2021_09_30 I would only need to change it in one place. For example as follows (which does not work)

tbl = "2021_09_30"

SELECT * FROM "db1".tbl WHERE condition
Union
SELECT * FROM "db2".tbl WHERE condition
presto amazon-athena
2 Answers

You can use a WITH clause to factor out the access to the underlying table:

WITH data AS (TABLE "db1"."2021_08_31")
SELECT * FROM data WHERE condition1
UNION
SELECT * FROM data WHERE condition2
...
user_29587520

SQL does not support this feature. You can use the Athena API and the StartQueryExecution call and have a template for this query that you render before you submit the job.

user_1275080
Related