経緯とやりたいこと

社内でRedshiftが広く使われるようになり、色々なシステムや人から色々なクエリが実行されるようになって久しい。
時間の掛かったクエリが自動的に通知されるようにしたい。

やったこと

LambdaのS3に作成されたファイルの内容をメール送信する仕組みを作り、そこへ目掛けてRedshiftでunloadを実行。

1.実行するクエリ

STL_QUERYを利用
参考：http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STL_QUERY.html

こんな感じで。querytxtは通知先のツールで見やすいくらいのものが良さそう。

select_slowlogquery.sql

select userid, starttime, datediff(second,starttime ,endtime) as query_time , substring(querytxt,0,250) from stl_query where datediff(second,starttime ,endtime) >=1 and starttime >='2016-11-07 10:00:00' and starttime <'2016-11-08 10:00:00' order by query_time desc limit 100;

利用時間帯で集中してたらそっちが原因（かも）と一目で分かるので、開始時刻＋所要時間が良いと思う。
ドキュメントによるとSTL_QUERYは2～5日分しか残っていない点に注意だが、逆に言えば昨日分は残っている（きっと）ので、こうしたことに向いてるはず。

2. 本日のゲスト：lambdaさん

言語はNode.js 4.3で、トリガーはスタンダードにs3-get-objectを利用。
メモとしてPrefix、Suffixは以下みたいな感じで。
- Prefix :　path1/lambda/send_mail/
- Suffix :　なし

var topicArn = '（★通知したいArnを書く）';
var errorThreshold = 524288; // 500KBを超えたらエラー。

var aws = require('aws-sdk');
var readline = require('readline');

var s3 = new aws.S3({ apiVersion: '2006-03-01' });
var sns = new aws.SNS();

exports.handler = function(event, context) {

    var bucket = event.Records[0].s3.bucket.name;
    var key = event.Records[0].s3.object.key;
    var mailBody ='message from lambda.\n\n';
    var mailSubject ="[information from lambda]" + key.substring(key.lastIndexOf('/')+1);

    s3.getObject({
        Bucket:bucket,
        Key:key
    },
    function(err,data) {
        if (err) {
            console.log(err);
            context.succeed('error','FileGetError:'+err);
        }
        else {

            if(data.ContentLength >errorThreshold){
                mailSubject += " [SIZE ERROR!]";
                mailBody += "data size error! file size("+data.ContentLength+") is over errorThreshold("+errorThreshold+")." ;
            }else{
                mailBody += data.Body;
            }
            sns.publish({
                Message: mailBody,
                Subject: mailSubject,
                TopicArn: topicArn
            }, function(err, data){
                if(err) throw err;
                else context.succeed('success.');
            });
        }
    });    

};

ちょっと工夫
- ファイル名をメールのSubjectに入れる
- アップされたファイルサイズが一定サイズ以上だったらエラー

3. 動作テスト

以下のような形で、lambdaのトリガの箇所に目掛けてunloadするとメールが届く。

unload.sql

unload ('
select userid, starttime, datediff(second,starttime ,endtime) as query_time , substring(querytxt,0,250) from stl_query where datediff(second,starttime ,endtime) >=1 and starttime >=\'2016-11-07 10:00:00\' and starttime <\'2016-11-08 10:00:00\' order by query_time desc 
') 
to 's3://(Bucket名)/path1/lambda/send_mail/rs_slowquerylog_20161106_.txt' CREDENTIALS 
'aws_access_key_id=XXXXXXXX;aws_secret_access_key=XXXXXXXX' delimiter '\t' PARALLEL OFF  allowoverwrite;

ちなみにunloadの場合はlimitを直接書けない（これをやってて初めて知った）ので、limitを掛けたい場合は

limit_unload.sql

unload ('
select * from (
select userid, starttime, datediff(second,starttime ,endtime) as query_time , substring(querytxt,0,250) from stl_query where datediff(second,starttime ,endtime) >=1 and starttime >=\'2016-11-07 10:00:00\' and starttime <\'2016-11-08 10:00:00\' order by query_time desc limit 100
);
') 
to 's3://(Bucket名)/path1/lambda/send_mail/rs_slowquerylog_20161106_.txt' CREDENTIALS 
'aws_access_key_id=XXXXXXXX;aws_secret_access_key=XXXXXXXX' delimiter '\t' PARALLEL OFF  allowoverwrite;

とサブクエリにすると動く（公式ドキュメントにそう書いてある）。

4.定期実行

その、cronとかで...

あと、いまどきメール通知なの？というのは一旦置いておきます。

Redshiftのスロークエリログっぽいものを通知 feat.Lambda