AWS CLIを使ってRedShiftの操作履歴をS3転送する

aws_logo

Amazon Resdshift のログ転送機能について

Amazon Resdshift は以下のログを取得できる。

  • Connection log – logs authentication attempts, and connections and disconnections.
  • User log – logs information about changes to database user definitions.
  • User activity log – logs each query before it is run on the database.

AWS Management Console からこれらログを取得し、S3 にログ転送する手順が下記 URL で解説されている。

http://docs.aws.amazon.com/redshift/latest/mgmt/db-auditing.html

この GUI ベースの手順をコマンドラインツールの AWS CLI から行う方法をメモ。

  • RedShift のクラスターID は dwh
  • ログを蓄積するバケット名は 14c6

とする。

ログ転送先のバケットを用意

まずはログ転送先のバケットを用意。

$ aws s3 mb s3://14c6
make_bucket: s3://14c6/

次に、このバケットにログファイルを転送するアクセスポリシーを設定する。

$ cat policy.json
{
    "Version": "2008-10-17",
    "Statement": [
        {
            "Sid": "Stmt1376526643068",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::404641285394:user/logs"
            },
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::14c6/*"
        },
        {
            "Sid": "Stmt137652664068",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::404641285394:user/logs"
            },
            "Action": "s3:GetBucketAcl",
            "Resource": "arn:aws:s3:::14c6"
        }
    ]
}
$ aws s3api put-bucket-policy --bucket 14c6 --policy file://policy.json

Principal には redshift のリージョンに対応する principal id(tokyo region では 404641285394) を記載
Resource には用意したバケット名(今回の例では 14c6)を指定

RedShift のロギング設定

  • Connection log – logs authentication attempts, and connections and disconnections.
  • User log – logs information about changes to database user definitions.
  • User activity log – logs each query before it is run on the database.

の S3 転送を有効にする。

Connection log と User log は RedShift が標準で STL システムテーブルに蓄積しているレコードを元にS3転送する。

User activity log は RedShift は標準ではログを取っていないので、パラメーターグループenable_user_activity_loggingtrue にする。

ログ出力用のパラメーターグループを $ aws redshift create-cluster-parameter-group で定義する。

<pre>$ aws redshift create-cluster-parameter-group --parameter-group-name myclusterparametergroup --parameter-group-family redshift-1.0 --description "My first cluster parameter group"
{
    "ClusterParameterGroup": {
        "ParameterGroupFamily": "redshift-1.0",
        "ParameterGroupName": "myclusterparametergroup",
        "Description": "My first cluster parameter group"
    }
}
$ aws redshift describe-cluster-parameters --parameter-group-name myclusterparametergroup
{
    "Parameters": [
        ...
        {
            "Description": "parameter for audit logging purpose",
            "DataType": "boolean",
            "IsModifiable": true,
            "AllowedValues": "true,false",
            "Source": "engine-default",
            "ParameterValue": "false",
            "ParameterName": "enable_user_activity_logging"
        },
        ...
    ]
}
$ aws redshift describe-cluster-parameters --parameter-group-name myclusterparametergroup --source user
{
    "Parameters": []
}

source はすべて engine-default なので、RedShift デフォルトのまま。
User activity log を取得するために enable_user_activity_logging を有効にする。

<pre>$ aws redshift modify-cluster-parameter-group --parameter-group-name myclusterparametergroup --parameters ParameterName=enable_user_activity_logging,ParameterValue=true
{
    "ParameterGroupStatus": "Your parameter group has been updated but changes won't get applied until you reboot the associated Clusters.",
    "ParameterGroupName": "myclusterparametergroup"
}

デフォルトと異なるパラメーターを確認。

$ aws redshift describe-cluster-parameters --parameter-group-name myclusterparametergroup --source  user
{
    "Parameters": [
        {
            "Description": "parameter for audit logging purpose",
            "DataType": "boolean",
            "IsModifiable": true,
            "AllowedValues": "true,false",
            "Source": "user",
            "ParameterValue": "true",
            "ParameterName": "enable_user_activity_logging"
        }
    ]
}

作成したパラメーターグループをクラスターに設定。

$ aws redshift modify-cluster --cluster-identifier dwh --cluster-parameter-group-name myclusterparametergroup
{
    "Cluster": {
        ...,
        "ModifyStatus": "Your Cluster has been associated with a new parameter group, but changes won't get applied until you reboot the Cluster.",
        ...,
        "ClusterParameterGroups": [
            {
                "ParameterGroupName": "myclusterparametergroup",
                "ParameterApplyStatus": "applying"
            }
        ],
        "ClusterIdentifier": "dwh",
        ...,
        "AvailabilityZone": "ap-northeast-1a",
        "NodeType": "dw2.large",
        "Encrypted": true,
        "ClusterStatus": "available"
    }
}

以降は SQL の実行などがロギングされるようになる。

S3 転送設定

最後に、ログを S3 転送させる。

$ aws redshift describe-logging-status --cluster-identifier dwh
{
    "LoggingEnabled": false
}

初期状態では無効化されている。
aws redshift enable-logging でログ転送先バケットとプリフィックスを指定。

$ aws redshift enable-logging --cluster-identifier dwh --bucket-name 14c6 --s3-key-prefix Auditing
{
    "LoggingEnabled": true,
    "S3KeyPrefix": "Auditing/",
    "BucketName": "14c6",
    "LastSuccessfulDeliveryTime": "2014-07-19T08:19:49.770Z"
}
$ aws redshift describe-logging-status --cluster-identifier dwh
{
    "LoggingEnabled": true,
    "S3KeyPrefix": "Auditing/",
    "BucketName": "14c6",
    "LastSuccessfulDeliveryTime": "2014-07-19T08:19:49.770Z"
}

ログ出力結果を確認

ログファイルは利用頻度にもよるが、少なくとも1時間に1回はログ転送される。

The number and size of Amazon Redshift log files in Amazon S3 will depend heavily on the activity in your cluster. At a minimum, Amazon Redshift uploads logging information on an hourly basis.
http://docs.aws.amazon.com/redshift/latest/mgmt/db-auditing.html#db-auditing-manage-log-files

S3 バケットのファイルをローカルファイルシステムに転送

$ aws s3 sync s3://14c6 .
$ tree .
.
`-- Auditing
    |-- AWSLogs
    |   `-- 423109000000
    |       `-- redshift
    |           `-- ap-northeast-1
    |               `-- 2014
    |                   `-- 07
    |                       `-- 19
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_connectionlog_2014-07-19T08:21.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_connectionlog_2014-07-19T09:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_connectionlog_2014-07-19T10:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_connectionlog_2014-07-19T11:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_connectionlog_2014-07-19T12:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_connectionlog_2014-07-19T13:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_connectionlog_2014-07-19T14:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_connectionlog_2014-07-19T15:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_connectionlog_2014-07-19T16:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_connectionlog_2014-07-19T17:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_useractivitylog_2014-07-19T08:21.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_useractivitylog_2014-07-19T09:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_useractivitylog_2014-07-19T10:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_useractivitylog_2014-07-19T11:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_useractivitylog_2014-07-19T12:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_useractivitylog_2014-07-19T13:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_useractivitylog_2014-07-19T14:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_useractivitylog_2014-07-19T15:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_useractivitylog_2014-07-19T16:43.gz
    |                           |-- 423109000000_redshift_ap-northeast-1_dwh_useractivitylog_2014-07-19T17:43.gz
    |                           `-- 423109000000_redshift_ap-northeast-1_dwh_userlog_2014-07-19T09:43.gz
    `-- RedshiftLoggingTestFile

8 directories, 22 files

ファイル階層は次のルールに従う

AWSLogs/AccountID/ServiceName/Region/Year/Month/Day/AccountID_ServiceName_Region_ClusterName_LogType_Timestamp.gz

実際のログファイルを確認

User Log

$ zcat Auditing/AWSLogs/423109000000/redshift/ap-northeast-1/2014/07/19/423109000000_redshift_ap-northeast-1_dwh_userlog_2014-07-19T09\:43.gz
102|guest2 | |create |0|0|0|9223372036854775807|17513|19501|Sat, 19 Jul 2014 03:50:50:513
...

User Activity Log

$ zcat Auditing/AWSLogs/423109000000/redshift/ap-northeast-1/2014/07/19/423109000000_redshift_ap-northeast-1_dwh_useractivitylog_2014-07-19T09\:43.gz
...
'2014-07-19T08:59:48Z UTC [ db=mydb user=awsuser pid=28386 userid=100 xid=23554 ]' LOG: select * from foo;
...

Connection Log

$ zcat Auditing/AWSLogs/423109000000/redshift/ap-northeast-1/2014/07/19/423109000000_redshift_ap-northeast-1_dwh_connectionlog_2014-07-19T08\:21.gz
disconnecting session |Sat, 19 Jul 2014 07:04:33:166|127.0.0.1 |46010 |23579|dev |rdsdb |password |1199985504
authenticated |Sat, 19 Jul 2014 07:44:33:190|127.0.0.1 |46022 |25621|dev |rdsdb |password |0
initiating session |Sat, 19 Jul 2014 07:44:33:190|127.0.0.1 |46022 |25621|dev |rdsdb |password |0
...

ファイルごとのカラムの意味は次のドキュメントを参照のこと

http://docs.aws.amazon.com/redshift/latest/mgmt/db-auditing.html

たとえば、User Activity Log であれば

Column name Description
recordtime Time the event occurred.
db Database name.
user User name.
pid Process ID associated with the statement.
userid User ID.
xid Transaction ID.
query A prefix of LOG: followed by the text of the query, including newlines.

というようになっていて、いつ、誰がどのような操作をやったのか、トレースできるようになる。

References

Advertisements
Posted in aws

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
%d bloggers like this: