amazon-redshift - 有没有办法在 Redshift 中找到表创建日期？

Question

我在 Amazon Redshift 中查找表创建日期时遇到问题。
我知道 svv_table_info 会提供关于表的所有信息，但创建日期。有人可以帮忙吗？

Answer 1

在 Redshift 中，您可以通过搜索 svl_qlog 中运行的任何创建表 sql 的开始和停止时间来获取表的创建时间。您可以查看其他表来获取类似数据，但这种方式的问题在于它只能保留几天(3 - 5 天)。尽管每个人都希望与表本身一起存储的元数据进行查询。 Amazon 建议保留此数据，以便将数据从要保留到 S3 的日志导出到 S3。然后在我看来，您可以将这些 s3 文件重新导入到您想要的名为 aws_table_history 或其他名称的永久表中，以便您永远保留这些特殊数据。

select * from svl_qlog where substring ilike 'create table%' order by starttime desc limit 100;

select * from stl_query a, stl_querytext b where a.query = b.query and b.text ilike 'create table%' order by a.starttime desc limit 100; 

或者只获取表名称和日期，如下所示:

select split_part(split_part(b.text,'table ', 2), ' ', 1) as tablename, 
starttime as createdate 
from stl_query a, stl_querytext b 
where a.query = b.query and b.text ilike 'create table%' order by a.starttime desc;

使用您的 key 将您想要的创建表数据历史记录导出到您创建的 S3 存储桶。下面的 select 语句将输出创建的表名和它创建的日期时间。

使用要导出到 S3 的数据创建一个临时表。

create table temp_history as 
(select split_part(split_part(b.text,'table ', 2), ' ', 1) as tablename, starttime as createdate 
from stl_query a, stl_querytext b 
where a.query = b.query 
and b.text ilike 'create table%' order by a.starttime desc);

然后将此表上传到 S3。

unload ('select * from temp_history') 
to 's3://tablehistory' credentials 'aws_access_key_id=myaccesskey;aws_secret_access_key=mysecretkey' 
DELIMITER '|' NULL AS '' ESCAPE ALLOWOVERWRITE;

在 AWS Redshift 中创建一个新表。

CREATE TABLE aws_table_history
(
tablename VARCHAR(150),
createdate DATETIME
);

然后将其重新导入到您的自定义表中。

copy aws_table_history from 's3://tablehistory' credentials 'aws_access_key_id=MYKEY;aws_secret_access_key=MYID'
emptyasnull
blanksasnull
removequotes
escape
dateformat 'YYYY-MM-DD'
timeformat 'YYYY-MM-DD HH:MI:SS'
maxerror 20;
delimiter '|';

我测试了所有这些，它对我们有用。我希望这可以帮助一些人。
最后一种更简单的方法是使用 Talend Big Data Open Studio 并创建一个新作业，获取组件 tRedshiftRow 并将以下 SQL 粘贴到其中。然后构建作业，您可以安排在您想要的任何环境中运行 .bat (windows) 或 .sh (unix)。

INSERT INTO temp_history 
(select split_part(split_part(b.text,'table ', 2), ' ', 1) as tablename, starttime as createdate 
from stl_query a, stl_querytext b 
where a.query = b.query 
and b.text ilike 'create table%' order by a.starttime desc);
COMMIT;
insert into historytable
select distinct s.* 
from temp_history s;
COMMIT;
--remove  duplicates 
DELETE FROM historytable USING historytable a2 
WHERE historytable.tablename = a2.tablename AND
historytable.createdate < a2.createdate;
COMMIT;
---clear everything from prestage
TRUNCATE temp_history;
COMMIT;