如何保存超过5天的查询日志? [英] How do I keep more than 5 day's worth of query logs?

问题描述

在Redshift中，有一个 STL_QUERY 表存储了过去5天运行的查询.我正在尝试找到一种方法来保存超过5天的记录.这是我考虑过的一些事情:

In Redshift, there's an STL_QUERY table that stores queries that were run over the last 5 days. I'm trying to find a way to keep more than 5 days worth of records. Here are some things that I've considered:

1. 是否有Redshift设置?看起来不会.
2. 我可以使用触发器吗?触发器在Redshift中不可用，因此这是不行的.
3. 我可以创建一个Amazon Data Pipeline作业来定期擦除" STL_QUERY 表吗?我可以，所以这是一个选择.不幸的是，我将不得不给管道一些EC2实例来运行这项工作.每天有一个实例围着桌子刮一次，这似乎是一种浪费.
4. 我可以使用Amazon Simple Work Flow作业来刮擦桌子吗?我可以，但是它遇到了与3相同的问题.

1. Is there a Redshift setting for this? It would appear not.
2. Could I use a trigger? Triggers are not available in Redshift, so this is a no-go.
3. Could I create an Amazon Data Pipeline job to periodically "scrape" the STL_QUERY table? I could, so this is an option. Unfortunately, I would have to give the pipeline some EC2 instance to use to run this work. It seems like a waste to have an instance sitting around to scrape this table once a day.
4. Could I use an Amazon Simple Work Flow job to scrape the table? I could, but it suffers from the same issues as 3.

我还有其他选择/想法吗?我宁愿选择其他不涉及我专用于EC2实例的选项，即使这意味着要支付额外的服务费用(前提是它比我本来会使用的EC2实例便宜).

Are there any other options/ideas that I'm missing? I would prefer some other option that does not involve me dedicating an EC2 instance, even if it means paying for an additional service (provided that it's cheaper than the EC2 instance I would have used in it's stead).

推荐答案

保持简单，在Redshift中完成所有操作.

Keep it simple, do it all in Redshift.

首先，使用"CREATE TABLE…AS"将所有当前历史记录保存到永久表中.

First, use "CREATE TABLE … AS" to save all current history into a permanent table.

CREATE TABLE admin.query_history AS SELECT * FROM stl_query;

其次，使用 psql 运行它，将作业安排在您控制的机器上每天运行一次.

Second, using psql to run it, schedule a job on a machine you control to run this every day.

INSERT INTO admin.query_history SELECT * FROM stl_query WHERE query > (SELECT MAX(query) FROM admin.query_history);

完成.:)

注意:

• 如果尚未设置 psql 的8.x版本.
• 即使您的工作几天没有运行，stl_query也会保留足够的历史记录，可以为您服务.
• 根据您的评论，使用开始时间而不是查询作为条件可能更安全.

这篇关于如何保存超过5天的查询日志?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！