Redshift query a daily-generated table

Question

I am looking for a way to create a Redshift query that will retrieve data from a table that is generated daily. Tables in our cluster are of the form:

event_table_2016_06_14
event_table_2016_06_13 

.. and so on.

I have tried writing a query that appends the current date to the table name, but this does not seem to work correctly (invalid operation):

SELECT * FROM concat('event_table_', to_char(getdate(),'YYYY_MM_DD'))

Any suggestions on how this can be performed are greatly appreciated!

Answer 1

I am assuming that you are creating a new table everyday.

What you can do is:

1. Create a view on top of event_table_* tables. Query your data using this view.
2. Whenever you create or drop a table, update the view.

If you want, you can avoid #2: Instead of creating a new table everyday, create empty tables for next 1-2 years. So, no need to update the view every day. However, do remember that there is an upper limit of 9,900 tables in Redshift.

Edit: If you always need to query today's table (instead of all tables, as I assumed originally), I don't think you can do that without updating your view.

However, you can modify your design to have just one table, with date as sort-key. So, whenever your table is queried with some date, all disk blocks that don't have that date will be skipped. That'll be as efficient as having time-series tables.