こんにちは(￣^￣)ゞ
SAOの映画を周回している大久保です。
公式にお布施を払いつつ、全種類の特典を手に入れるのは大変です。

今回は最後の「~パート3~Athenaの設定方法」になります。

• ~パート1~データベースアクティビティストリームとは
• ~パート2~データベースアクティビティストリームの設定方法
• ~パート3~Athenaの設定方法

の3つにわけてあります。

今回やることの概要

今回は赤枠の部分を説明していきます。

処理フロー図

前回の内容で保存したDASのログをAthenaで検索していきます。

設定手順

それでは、Athenaの設定方法について説明していきます。
ここからの作業は、以下が前提になります。

• Administrator権限を持っているIAMユーザーでログインしている。
• ~パート2~データベースアクティビティストリームの設定方法までの作業が終わっている。

ワークグループの作成

まずはAthenaでワークグループの作成を行います。
ワークグループとは、Athena内で利用する論理的なグループで、作成したクエリやクエリの実行履歴などを分離することが出来ます。
デフォルトで作成されているprimaryグループを使ってもいいのですが、せっかくなので新しいものを作成して利用したいと思います。
詳しい内容については以下の公式ドキュメントに記載があります。

ワークグループを使用するメリット

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/workgroups-benefits.html

• Athenaのコンソール画面にアクセスし、左ペインより「ワークグループ」をクリックします。
• 「ワークグループを作成」をクリックします。

• 以下の設定で、ワークグループを作成します。
  • ワークグループ名
    • 任意の名前
  • クエリエンジンをアップグレード
    • 自動
  • クエリ結果の場所
    • 任意のS3バケット
  • バケット所有者にクエリ結果に対する完全なコントロールを割り当てる
    • チェックを外す
  • クエリ結果を暗号化
    • チェックを外す
  • AWS CloudWatchにクエリメトリクスを発行
    • チェックを外す

• ワークグループが作成されたことを確認し、右上の「ワークグループ」で作成したワークグループを選択します。

テーブル作成クエリの作成と実行

次にテーブル作成クエリを作成し、実行します。
テーブル作成のクエリは以下になります。

CREATE EXTERNAL TABLE `das_log_xxxx`(
  `logtime` string, 
  `statementid` int, 
  `substatementid` int, 
  `objecttype` string, 
  `command` string, 
  `objectname` string, 
  `databasename` string, 
  `dbusername` string, 
  `remotehost` string, 
  `remoteport` string, 
  `sessionid` string, 
  `rowcount` int, 
  `commandtext` string, 
  `paramlist` array<string>, 
  `pid` int, 
  `clientapplication` string, 
  `exitcode` string, 
  `class` string, 
  `serverversion` string, 
  `servertype` string, 
  `servicename` string, 
  `serverhost` string, 
  `netprotocol` string, 
  `dbprotocol` string, 
  `type` string, 
  `starttime` string, 
  `errormessage` string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
 WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true')
LOCATION
  '<ログが保存されているS3バケット>/<日付の階層>'

クエリは以下のAWSドキュメントを参考にしています。

JSON データ読み取りのベストプラクティス

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/parsing-JSON.html

カラムの順番については、事前にログファイルの中身を確認しておく必要があります。

• 「データベース」で「default」を選択します。
• 右の「+」をクリックし、「クエリ1」のタブが作成されたことを確認します。
• テーブル作成のクエリをコピペし、「実行」をクリックします。
  • 画像では「もう一度実行」になっています。
• 左の「テーブル」欄に、「das_log_xxxx」が作成されたことを確認します。

検索クエリの作成と実行

最後に検索クエリを作成し、実行します。
もう普通のSQLのselectのクエリを実行するだけです。
今回はシンプルに以下のクエリを実行します。

select * from das_log_xxxx;

• 右の「+」をクリックし、「クエリ2」のタブが作成されたことを確認します。
• 検索のクエリをコピペし、「実行」をクリックします。
  • 画像では「もう一度実行」になっています。
• 「結果」の欄に検索結果が表示されていることを確認する。

なにかしらのフィルタリングを行いたい場合は、where句をつけて検索することも可能です。

結果

無事に、DASのログをAthenaで検索することが出来ました！
検索結果については、「結果をダウンロード」からCSVファイルでDLすることも可能です。

まとめ

今回のシリーズでは、DASのログを取得してAthenaで検索するまでについて説明してみました。

最初の記事に書いたとおり、DASはPostgreSQLでもMySQLでも共通の設定、フォーマットで利用できるところが強みです。
ただ暗号化・復号化でKMSなどのコストが大きくなりやすいため、本番環境で利用する場合は気を付けてください。

気付いたら翌月倍の利用料を請求される、なんてこともあるかもしれません…orz

参考にした記事

• https://docs.aws.amazon.com/ja_jp/athena/latest/ug/workgroups-benefits.html
• https://docs.aws.amazon.com/ja_jp/athena/latest/ug/parsing-JSON.html