从stl_load_commits获取红移复制命令的表名

Question

尝试获取在特定日期运行的复制命令的列表以及为每个复制命令更新的表。

使用此查询：

select
        slc.query as query_id, 
        trim(slc.filename) as file, 
        slc.curtime as updated, 
        slc.lines_scanned as rows, 
        sq.querytxt as querytxt
from stl_load_commits slc
    join stl_query sq on sq.query = slc.query
where trunc(slc.curtime) = '2020-05-07';

我们如何获取为每个复制命令更新的表？也许在querytxt上使用红移RegEx函数？或者连接到另一个系统表以查找表id或名称？

Answer 1

此正则表达式将从stl_query.querytxt中选择table或schema.table

select
        slc.query as query_id,
        trim(slc.filename) as file,
        slc.curtime as updated,
        slc.lines_scanned as rows,
        sq.querytxt as querytxt,
        REGEXP_REPLACE(LOWER(sq.querytxt), '^copy (analyze )?(\\S+).*$', '$2') AS t
from stl_load_commits slc
    join stl_query sq on sq.query = slc.query
where trunc(updated) = '2020-05-07';