删除 Redshift 中的循环

我有一个表，其中包含有关数据库中不同表的信息。对于其中的每个表，我希望定期进行日志记录（每天）。问题是我使用 for 循环完成了此操作，并且在 Redshift 中执行需要花费大量时间。我给出了用于创建表、日志记录表和使用 for 循环的过程的查询。 请建议一种替代方法来实现这一点而不需要 for 循环。 包含不同表信息的表结构：

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;

这里，对象名称指的是表名称，object_type 包含表是否是视图或表，object_full_name 存储表与模式的串联名称，即如果模式名称是“test_schema”，则全名是“test_schema.table_name” 。需要存储日志记录的表结构：

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;

这里，refresh_date 存储记录日期，refresh_count 存储当天插入表中的记录数，total_count 包含截至记录日期为止表中的记录总数。

为了澄清起见，下面是 info_schema_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;

所有表的记录数仅逐日增加，因此在任何特定日期插入的记录始终 >=0。 问题是，这个过程有效，但仅适用于小记录info_schema_table，如果我运行大约 1000 条记录，该过程即使在一小时内也无法在 Redshift 上完成。

请建议一种不使用 for 循环来执行它的替代方法。谢谢。

好的，我们开始吧。

有工作insert它展示了如何在一个语句中实现这一点：

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;

您可以仅使用来验证它是如何工作的select没有insert，这很好，因为它会显示将插入的内容，而实际上却没有这样做。

这里发生了什么： 从...开始from:

• info_schema_table加入到svv_table_info获取实际行数。它允许避免动态创建select count每行的语句；
• 接下来，进行左连接redshift_logging_table- 对于某些对象尚不存在（新创建的等）的情况，它是左连接

In select:

• 有last_value使用的窗口函数（参见https://docs.aws.amazon.com/redshift/latest/dg/r_WF_first_value.html https://docs.aws.amazon.com/redshift/latest/dg/r_WF_first_value.html）它允许从由以下描述的某些行中的特定列中查找值partition by and order by）。该值是为每个对象名称单独计算的日志表中的最后刷新计数。

你可能需要以这样或那样的方式调整它，但我认为这是一个好的开始。另外，这可以直接移到您的存储过程中。

Enjoy!