Remove loop in Redshift

Question

I have a table that contains information about the different tables in my database. For each table in it, I want regular logging (daily) to be done. The problem is that I have done this using for loop, and it is taking a lot of time to execute in Redshift. I am giving the queries I used to create my table, logging table and the Procedure that uses the for loop. Please suggest an alternate method to implement this without for loop. Structure of table that contains info of different tables:

CREATE TABLE public.info_schema_table
(
    info_schema_name character varying(200) ENCODE lzo,
    info_object_name character varying(200) ENCODE lzo,
    info_object_type character varying(200) ENCODE lzo,
    info_object_full_name character varying(400) ENCODE lzo
)
DISTSTYLE EVEN;

Here, object name refers to the table name, object_type contains if the table is view or table, and the object_full_name stores the concatenated name of the table with the schema i.e. if schema name is "test_schema" then full name is "test_schema.table_name". Structure of table in which logging needs to be stored:

CREATE TABLE public.redshift_logging_table
(
    log_schema_name character varying(30) ENCODE lzo,
    log_object_name character varying(30) ENCODE lzo,
    log_object_type character varying(30) ENCODE lzo,
    log_refresh_date date ENCODE az64,
    log_refresh_count bigint ENCODE az64,
    log_total_count bigint ENCODE az64
)
DISTSTYLE EVEN;

Here, refresh_date stores the date of logging, refresh_count stores the number of records inserted in the table on that particular day and total_count contains the total number of records in the table till the logging date.

Just for clarification, here's one sample record of the info_schema_table:

Here's my procedure that I am using to fill the logging table:

CREATE OR REPLACE PROCEDURE public.REDSHIFT_LOGGING_PROCEDURE()
AS $$
DECLARE
var_total_count bigint;
var_records_today bigint;
my_row record;
my_cursor CURSOR  
FOR select  info_schema_name, info_object_name, info_object_type , info_object_full_name  from INFO_SCHEMA_TABLE ;
BEGIN
  open my_cursor;
  LOOP
        FETCH my_cursor INTO my_row;
        EXIT WHEN NOT FOUND;
            BEGIN

             EXECUTE ' select count(1)::bigint  from '||my_row.INFO_OBJECT_FULL_NAME INTO var_total_count;
             var_records_today=var_total_count-(select log_total_count  from REDSHIFT_LOGGING_TABLE where REFRESH_DATE=current_date-1 and LOG_OBJECT_NAME=my_row.INFO_OBJECT_NAME);
             
             insert into REDSHIFT_LOGGING_TABLE
              (LOG_SCHEMA_NAME,LOG_OBJECT_NAME,LOG_OBJECT_TYPE,LOG_REFRESH_DATE, LOG_REFRESH_COUNT, LOG_TOTAL_COUNT )
              values 
              (my_row.info_schema_name, my_row.info_object_name, my_row.info_object_type , current_date, var_records_today, var_total_count);
            END;

  END LOOP;
END;
$$ LANGUAGE plpgsql
SECURITY INVOKER;

All the table only increase in number of records day by day, so records inserted on any particular day is always >=0. Issue is that, this procedure works but only for small records in info_schema_table, if I run it for around 1000 records, the procedure doesn't completes on Redshift even in one hour.

PLEASE suggest an alternate method to execute it without using for loop. Thank you.

user_5172513 · Answer

Ok, here we go.

There is working insert which shows how this could work in one single statement:

insert into redshift_logging_table(log_schema_name, log_object_name, log_object_type, log_refresh_date, log_refresh_count, log_total_count)
select distinct 
    ist.info_schema_name, 
    ist.info_object_name, 
    ist.info_object_type, 
    current_date
    , sti.tbl_rows - last_value(log_total_count ignore nulls) over (
        partition by log_object_name order by log_refresh_date asc
        rows between unbounded preceding and unbounded following
    )
    , sti.tbl_rows
from svv_table_info sti
inner join info_schema_table ist on ist.info_schema_name = sti.schema and ist.info_object_name = sti.table
left join redshift_logging_table rlt on rlt.log_schema_name = ist.info_schema_name and rlt.log_object_name = ist.info_object_name;

You could verify how it works using only select without insert, which is nice as it will show what would be inserted without doing it in fact.

What happens here: Starting from from:

info_schema_table is joined to svv_table_info to get actual number of rows. It allows to avoid to create dynamically select count statement for each row;
next, left join is made to redshift_logging_table - it's left join for cases when some objects weren't there yet (newly created, etc.)

In select:

there is last_value window function used (see https://docs.aws.amazon.com/redshift/latest/dg/r_WF_first_value.html) which allows to find value from specific column from some rows described by partition by and order by). This value is the last refresh count from logs table calculated for every object name separately.

You may need to adjust it in one way or another, but I think it is a good start. Also, this can be just moved into your stored procedure.

Enjoy!

Remove loop in Redshift

1 Answers