Redshift Maximum tables limit exceeded problem, how to prevent this behavior
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.
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
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.
Thank you !
Amazon mTurk - Problem to pay "You have exceeded your monthly credit limit"asked 5 months ago
Redshift data warehouse and Glue ETL design recommendationsAccepted Answerasked 2 years ago
Prevent Rewrite of Queries in Redshiftasked a month ago
How is data returned from Spectrum to Redshift cluster?Accepted Answerasked 2 years ago
Redshift questions from AWS CustomerAccepted Answerasked 3 years ago
Lambda@Edge - Maximum number of distributions with Lambda function exceededasked 5 years ago
What is the best practice to load data to redshift with aws glue ?asked 2 years ago
What is the best ETL tool for ongoing loads of data into Redshift?Accepted Answerasked 5 years ago
Maximum number of connections from user+IP exceededasked 2 years ago
Redshift Maximum tables limit exceeded problem, how to prevent this behaviorasked a month ago