create a view from two tables in athena query-CodePudding

Basically my use case is that I am creating a view in athena but i want to select the data from two tables depending on the date (snapshot_date) which is given by a column in the tables. How to do it? I am not finding the syntax for that

SELECT
 baseline_year
, marketplace
, locale
, snapshot_time
, ...
FROM
  table1

I want to achieve something like if snapshot time is less than 2022-05-01, then use table 2 otherwise use table 1. Can we any kind of conditioning in FROM? I have explored that we can use CASE_WHEN for performing conditions on columns, but not sure that can be used in FROM?

CodePudding user response：

If I am not mistaken, you meant to use table2 if the snapshot_time is earlier than 2022-05-01 and use table1 if the snapshot_time is greater than or equal to 2022-05-01 REGARDLESS of what shapshot_time those two tables have.

SELECT ... FROM table1 where date(snapshot_time)>= '2022-05-01'
union
SELECT ... FROM table2 where date(snapshot_time) < '2022-05-01' ;

CodePudding user response：

You can proceed with a parametrized view, try to create a function:

CREATE FUNCTION func() RETURNS DATE
  RETURN @var;

Then create the view :

CREATE VIEW view1 AS
SELECT
 T1.baseline_year
, T1.marketplace
, T1.locale
, T1.snapshot_time
, ...
FROM  table1 AS T1
WHERE DATE(T1.snapshot_time) >= func()
UNION
SELECT
 T2...,
 ..
FROM
  table1 AS T2
WHERE DATE(T2.snapshot_time) <= func()