1

Trying to automate a job and have a dynamic table name with the following format:

sales_yyyymm where yyyymm is last month's date.

Has anyone done it?

Improve this question
2
  • Are you saying that you want to send a CREATE TABLE command and have Redshift 'calculate' the table name? No, this is not possible. However, you could probably do it within a Stored Procedure. Commented Dec 14, 2020 at 20:58
  • I want to have a command that says create table sales_yyyymm where yyyymm is worked out using current_date Commented Dec 15, 2020 at 10:28

1 Answer 1

Reset to default
1

You cannot do this in SQL in Redshift AFAIK. When I've needed to have automation of this kind it is the job of the external to Redshift automation tool to provide the table names if they are variable. Redshift runs the SQL, the external tool generates the SQL.

A word of warning - Redshift is very good with big tables and does ok with small tables. The process you are laying out has the possibility to make many very small tables and want to treat them as if they are a big table. There are a number of issues that can crop up if you do this (assuming you are). Since the minimum storage size on Redshift (block) is 1MB and that these are multiplied out over columns and slices - a table of a few thousand rows can take up a lot of space. This affects disk storage, disk IO, and overall complexity of the query plan. If you plan on making 10,000 tables of 10,000 rows each I promise you are going to have a bad time.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.