How to create 10000 tables on redshift using stored procedure?

Question

I need to create 10000 tables on Redshift database using stored procedure. Can you please provide a sample stored procedure?

Here's the stored procedure to create 100000 tables for Snowflake

create or replace procedure tablecheck()
returns string
language javascript
strict
execute as owner
as
$$
var i = 1;
while (i < 1001) {  
  var sql_command = 
   'create table performance.al55260.tab'+i+'(col1 int,col2 int,col3 int,col4 int,col5 int,col6 int,col7 int,col8 int,col9 int,col10 int,col11 int,col12 int,col13 int,col14 int,col15 int,col16 int,col17 int,col18 int,col19 int,col20 int,col21 int,col22 int,col23 int,col24 int,col25 int,col26 int,col27 int,col28 int,col29 int,col30 int,col31 int,col32 int,col33 int,col34 int,col35 int,col36 int,col37 int,col38 int,col39 int,col40 int,col41 int,col42 int,col43 int,col44 int,col45 int,col46 int,col47 int,col48 int,col49 int,col50 int);'
  try {
      snowflake.execute (
          {sqlText: sql_command}
          );
         // Return a success/error indicator.


      }
  catch (err)  {
      return "Failed: " + err;   // Return a success/error indicator.
      }
  i++;
}
return 'yes';
$$;

I'm looking for stored proc or function on Redshift to achieve the same.

It would be great if you have any idea about creating millions of tables in any other programmatic way.

Answer 1

Please note that the current maximum number of tables per Redshift cluster is either 9,900 (for smaller instance types) or 20,000 (for larger instance types). This is documented in "Quotas and limits in Amazon Redshift"

Here is my translation of your stored procedure for Redshift:

CREATE OR REPLACE PROCEDURE tablecheck( 
      table_count IN  INTEGER
    , return_val  OUT VARCHAR
) 
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
    schema_check BOOLEAN;
    loop_count   INTEGER;
    sql_command  VARCHAR(MAX);
BEGIN
    SELECT NVL(TRUE,FALSE) INTO schema_check 
    FROM pg_namespace 
    WHERE nspowner > 1 
    AND nspname = 'tablecheck';
    IF schema_check IS NULL THEN
        CREATE SCHEMA tablecheck;
    ELSE
        DROP SCHEMA tablecheck CASCADE;
        CREATE SCHEMA tablecheck;
    END IF;
    loop_count := 0;
    WHILE (loop_count < table_count) LOOP
      loop_count := loop_count + 1;
      sql_command := 'CREATE TABLE tablecheck.tbl_' || loop_count
            ||'(col1  int, col2  int, col3  int, col4  int, col5  int,'
            ||' col6  int, col7  int, col8  int, col9  int, col10 int,'
            ||' col11 int, col12 int, col13 int, col14 int, col15 int,'
            ||' col16 int, col17 int, col18 int, col19 int, col20 int,'
            ||' col21 int, col22 int, col23 int, col24 int, col25 int,'
            ||' col26 int, col27 int, col28 int, col29 int, col30 int,'
            ||' col31 int, col32 int, col33 int, col34 int, col35 int,'
            ||' col36 int, col37 int, col38 int, col39 int, col40 int,'
            ||' col41 int, col42 int, col43 int, col44 int, col45 int,'
            ||' col46 int, col47 int, col48 int, col49 int, col50 int);';
      EXECUTE sql_command;
      RAISE INFO 'Create table: %', loop_count;
    END LOOP;
    SELECT 'Complete' INTO return_val;
    DROP SCHEMA tablecheck CASCADE;
END
$$;

You call this stored procedure in Redshift as follows:

BEGIN; CALL tablecheck(100); END;
-- …
-- INFO:  Create table: 99
-- INFO:  Create table: 100
--  return_val
-- ------------
--  Complete
--
-- Time: 720.729 ms

For more information see "Overview of stored procedures in Amazon Redshift"