Amazon Redshift 如何获取表插入数据的最后日期 [英] Amazon Redshift how to get the last date a table inserted data

问题描述

我正在尝试获取在表中执行插入的最后日期(在 Amazon Redshift 上)，有没有办法使用元数据来执行此操作?这些表不存储任何时间戳列，即使有时间戳列，我们也需要找出 3k 表，因此这是不切实际的，因此元数据方法是我们的​​策略.有什么提示吗?

I am trying to get the last date an insert was performed in a table (on Amazon Redshift), is there any way to do this using the metadata? The tables do not store any timestamp column, and even if they had it, we need to find out for 3k tables so it would be impractical so a metadata approach is our strategy. Any tips?

推荐答案

查询的所有插入执行步骤都记录在 STL_INSERT.这个查询应该给你你正在寻找的信息:

All insert execution steps for queries are logged in STL_INSERT. This query should give you the information you're looking for:

SELECT sti.schema, sti.table, sq.endtime, sq.querytxt
FROM 
    (SELECT MAX(query) as query, tbl, MAX(i.endtime) as last_insert
    FROM stl_insert i
    GROUP BY tbl
    ORDER BY tbl) inserts
JOIN stl_query sq ON sq.query = inserts.query
JOIN svv_table_info sti ON sti.table_id = inserts.tbl
ORDER BY inserts.last_insert DESC;

注意:STL 表仅保留大约两到五天的日志历史记录.

Note: The STL tables only retain approximately two to five days of log history.

这篇关于Amazon Redshift 如何获取表插入数据的最后日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！