CODEWITHSUNDEEP
sqlamazon-redshift
hhh_
hhh_

Reputation: 328

Create a dynamic view based on partitioned tables

We have a large database with monthly partitioned tables. I need to aggregate a selection of these tables every month but I don't want to update the union all every month to add the new monthly table. 

CREATE VIEW dynamic_view AS
SELECT timestamp, 
       traffic
FROM traffic_table_m_2017_01
UNION ALL 
SELECT timestamp, 
       traffic
FROM traffic_table_m_2017_02

Is this where I would use a stored procedure? I am not really familiar with them.

I think it would also work as: 

SELECT timestamp, 
       traffic
FROM REPLACE(REPLACE('traffic_table_m_yyyy_mm', 
                     yyyy, FORMAT(GETDATE(),'yyyy', 'en-us')),
                     mm, FORMAT(GETDATE(),'mm', 'en-us'));

This might work for the current month but I would need to save the data from the past months which would also be an issue.

Upvotes: 0

Views: 952

Answers (1)

Jon Scott
Jon Scott

Reputation: 4354

you should append each table as it arrives to 1 larger table then run your queries against that. there are many ways to do this but probable the fastest and most elegant is to use. ALTER TABLE APPEND Instructions here https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE_APPEND.html

Upvotes: 1

Related Questions