データアナリティクス事業本部インテグレーション部コンサルティングチーム・新納（にいの）です。
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の魅力です。この記事が誰かのお役に立てば幸いです。
参考資料
【イベント開催のお知らせ】7/19(水)「DevelopersIO大阪」4年ぶりのオフライン開催決定！
AWSはもちろん、ChatGPTやデータ分析など幅広い分野の技術を取り扱った12のライブセッションをお送りします。 また、クラスメソッドのエンジニアと直接技術の話ができる相談ブースも開設いたします。 大阪オフィス所属のAWS Japan Top Engineersや元Ambassadorも大集合！
さらになんと、数量限定でクラスメソッド社員謹製の本格スパイスカレーを提供いたします！ 入場無料、途中の入退室は自由です。ぜひご参加ください！
タイムスケジュール・お申込みはこちらから！
0
