CODEWITHSUNDEEP
amazon-web-servicesamazon-redshift
Mobix
Mobix

Reputation: 103

Creating Table with variable name in Amazon Redshift

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?

Upvotes: 1

Views: 1040

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11082

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.

Upvotes: 1

Related Questions