Amazon Redshiftの監査ログをパーティション化してAmazon Athenaでクエリする

AthenaのPartition Projectionを使ってRedshiftの監査ログをクエリしましょう

#AWS

#Amazon Redshift

#Amazon Athena

niino

2023.06.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部インテグレーション部コンサルティングチーム・新納（にいの）です。

Redshiftへのアクセスやクエリ、ユーザーに対する変更などをログとして管理したいときこそ監査ログの出番です。特定条件でクエリできるようにするにはAthenaを使うと便利です。

監査ログについての詳細や有効化については以下をご参照ください。

Redshiftの監査ログをAthenaでテーブル化するとうれしいところ

Redshiftの監査ログはCloudWatch Logsへ出力できます。出力先に設定するだけでいいですし、ログへのアクセスもマネジメントコンソールから容易にできてありがたい機能です。

一方Amazon Athenaではパーティション化したテーブルの作成が必要となりますが、例えば実行ユーザーに絞ってクエリするなど、知りたい項目に絞ってログを確認できるのがメリットです。

また、Amazon Athenaにはパーティション管理を自動化するPartition Projectionの機能があります。この機能を使うことで、新しい日付で監査ログが作成された際でもMSCK REPAIR TABLEを実行し、パーティション追加作業を行う必要はありません。自動でパーティションが追加されます。Partition Projectionについての詳細は以下をご参照ください。

Amazon Athenaで監査ログをクエリする

以下のテーブル・ビューをそれぞれ作成します。

Redshiftの監査ログをパーティション化したテーブル
接続ログビュー
ユーザーログビュー
ユーザーアクティビティログビュー

テーブルを作成する

まずはRedshiftの監査ログをパーティション化したテーブルを作成します。Redshiftの監査ログは以下の構造でS3バケットに格納されています。（バケット名は一例です）

s3://redshift-audit-logging/audit-logs/AWSLogs/AccountID/ServiceName/Region/Year/Month/Day/AccountID_ServiceName_Region_ClusterName_LogType_Timestamp.gz

Year/Month/Dayのパーティションをdateというカラムでパーティション化したテーブルを作成します。

以下がCREATE TABLE文です。Partition ProjectionはTBLPROPERTIES句のprojectionで設定しています。この設定内容を元にしてAthenaがパーティションを自動計算してくれます。

以下のクエリではprojection.date.rangeで現在日付から1年前の間のログをクエリできるように設定しています。

CREATE EXTERNAL TABLE redshiftuseractivitylog
(
    logrecord STRING
) 
PARTITIONED BY (date string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '',
'field.delim' = '\n',
'collection.delim' = '|',
'escape.delim' = '\\'
)
LOCATION 's3://redshift-audit-logging/audit-logs/AWSLogs/＜AWSアカウントID＞/redshift/＜リージョン＞/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.date.type' = 'date',
    'projection.date.range' = 'NOW-1YEARS,NOW',
    'projection.date.format' = 'yyyy/MM/dd',
    'projection.date.interval' = '1',
    'projection.date.interval.unit' = 'DAYS',
    'projection.region.type' = 'enum',
    'storage.location.template' = 's3://redshift-audit-logging/audit-logs/AWSLogs/＜AWSアカウントID＞/redshift/＜リージョン＞/'${date}',
    'classification'='csv',
    'compressionType'='gzip',
    'typeOfData'='file'
);

こんなテーブルができあがります。このままではクエリが難しいので、このテーブルにビューを被せるような形でクエリしやすい形式に加工します。

接続ログビューを作成する

認証の試行、接続と切断を記録した接続ログです。

CREATE or REPLACE VIEW redshiftconnectionlog_view AS
select regexp_extract(logrecord, '(.*?)\|(.*)', 1) AS event,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*)', 2) AS recordtime,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*)', 3) AS remotehost,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(.*)', 4) AS remoteport,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*)', 5) AS pid,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*)', 6) AS dbname,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(.*)', 7) AS username,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(.*?)\|(.*)', 8) AS authmethod,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*)', 9) AS duration,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*)', 10) AS
sslversion,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(.*)',
11) AS sslcipher,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(\d+)\|
(.*)', 12) AS mtu,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(\d+)\|
(.*?)\|(.*)', 13) AS sslcompression,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(\d+)\|
(.*?)\|(.*?)\|(.*)', 14) AS sslexpansion,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(\d+)\|
(.*?)\|(.*?)\|(.*?)\|(.*)', 15) AS iamauthguid,
regexp_extract(logrecord, '(.*?)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*?)\|(.*?)\|(\d+)\|
(.*?)\|(.*?)\|(.*?)\|(.*)', 16) AS application_name
FROM redshiftuseractivitylog
WHERE regexp_like("$path", '[0-9]+_redshift_＜リージョン＞_＜Redshiftのクラスタ名＞_connectionlog_.*');

ユーザーログビュー

データベースユーザーに対して以下の変更を加えたユーザーログです。

ユーザーの作成
ユーザーの削除
ユーザーの変更 (名前の変更)
ユーザーの変更 (プロパティの変更)

CREATE or REPLACE VIEW redshiftuserlog_view AS
select regexp_extract(logrecord, '(\d+)\|(.*?)\|(.*)', 1) AS userid,
regexp_extract(logrecord, '(\d+)\|(.*?)\|(.*)', 2) AS username,
regexp_extract(logrecord, '(\d+)\|(.*?)\|(.*?)\|(.*)', 3) AS oldusername,
regexp_extract(logrecord, '(\d+)\|(.*?)\|(.*?)\|(.*?)\|(.*)', 4) AS action,
regexp_extract(logrecord, '(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(.*)', 5) AS usecreatedb,
regexp_extract(logrecord, '(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(\d+)\|(.*)', 6) AS usesuper,
regexp_extract(logrecord, '(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(\d+)\|(\d+)\|(.*)', 7) AS usecatupd,
regexp_extract(logrecord, '(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(\d+)\|(\d+)\|(.*?)\|(.*)', 8) AS valuntil,
regexp_extract(logrecord, '(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(\d+)\|(\d+)\|(.*?)\|(\d+)\|(.*)', 9) AS pid,
regexp_extract(logrecord, '(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(\d+)\|(\d+)\|(.*?)\|(\d+)\|(\d+)\|(.*)', 10) AS
xid,
regexp_extract(logrecord, '(\d+)\|(.*?)\|(.*?)\|(.*?)\|(\d+)\|(\d+)\|(\d+)\|(.*?)\|(\d+)\|(\d+)\|(.*)', 11) AS
recordtime
FROM redshiftuseractivitylog
WHERE regexp_like("$path", '[0-9]+_redshift_＜リージョン＞_＜Redshiftのクラスタ名＞_userlog_.*');

ユーザーアクティビティログビュー

データベースで実行される前に記録した各クエリのユーザーアクティビティログです。

CREATE or REPLACE VIEW redshiftuseractivitylog_view AS
SELECT date_parse(regexp_extract(logrecord, '\d+-\d+-\d+T\d+:\d+:\d+Z UTC'), '%Y-%m-%dT%TZ UTC') AS
recordtimestamp,
regexp_extract(logrecord, '\[ db=(.*?) user=(.*?) pid=(\d+) userid=(\d+) xid=(\d+) \]', 1) AS db,
regexp_extract(logrecord, '\[ db=(.*?) user=(.*?) pid=(\d+) userid=(\d+) xid=(\d+) \]', 2) AS user,
regexp_extract(logrecord, '\[ db=(.*?) user=(.*?) pid=(\d+) userid=(\d+) xid=(\d+) \]', 3) AS pid,
regexp_extract(logrecord, '\[ db=(.*?) user=(.*?) pid=(\d+) userid=(\d+) xid=(\d+) \]', 4) AS userid,
regexp_extract(logrecord, '\[ db=(.*?) user=(.*?) pid=(\d+) userid=(\d+) xid=(\d+) \]', 5) AS xid,
regexp_extract(logrecord, 'LOG: (.*)', 1) AS query
FROM redshiftuseractivitylog
WHERE regexp_like("$path", ''[0-9]+_redshift_＜リージョン＞_＜Redshiftのクラスタ名＞_useractivitylog_.*');

最後に

Amazon AthenaでRedshiftの監査ログにクエリしてみました。ログが増えれば増えるほどパーティションによるスキャン効率の向上がパフォーマンスにもコスト面でも効果的になります。簡単にログの中身を見たい場合はCloudWatch Logsに流したり、Redshiftで一元的に管理したい場合はRedshift Spectrumを使うのも選択肢としてありますが、安価に高速なクエリが可能なのもAthenaの魅力です。この記事が誰かのお役に立てば幸いです。