By using AWS re:Post, you agree to the Terms of Use
/Redshift Maximum tables limit exceeded problem, how to prevent this behavior/

Redshift Maximum tables limit exceeded problem, how to prevent this behavior

0

Hi, we have recently deployed our data warehouse into Production. There is lot of ETL done using stored procedures within Redshift.

my ETL workflow is as follows,

Data from DB2 system is staged within Redshift. and then the data is moved into Data warehouse using Redshift procedures. I have a 2 node RA3 cluster. (with node Type: ra3.xlplus)

every now & then my ETL is failing with following error

SQL Error [XX000]: ERROR: Failed to incorporate external table "xxxxxx.covera" into local catalog. Maximum tables limit exceeded. The maximum number of tables per cluster is 20000 for this instance type. The limit includes permanent and temporary tables.

In my ETL redshift procedures, I use cross database queries in the procedures.
I cannot disable Temporary Tables as we are using CTE, and local temp tables to perform ETL using Redshift procedures.

when I queried STV_TBL_PERM, I see there are lot of Temporary Tables created within Redshift, I have seen ~20000 tables. I can understand why I am getting above error.

I am not able to understand the need for this temporary tables by redshift, and why are they persisted forever.

Table name looks exactly like this localhost@232987@465587@ .

how can I delete these temporary tables automatically?

Can you please give some insight.

Regards Shiv

1 Answers
0

Hello Shiv,

The limit on maximum number of tables includes permanent tables, temporary tables, datashare tables, and materialized views. External tables are counted as temporary tables. Temporary tables include user-defined temporary tables and temporary tables created by Amazon Redshift during query processing or system maintenance. Views and system tables aren't included in this limit. https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-limits.html

Work-around :-

I would suggest you to delete unnecessary local tables, this includes permanent and temporary tables or consider resizing to 4xlarge/8xlarge/16xlarge node type which can have a maximum of 100,000 tables.

https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_usage.html

Thank you !

answered a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions