🦔

インフラエンジニアがAthenaと格闘してみた

2024/05/15に公開

AWS

JAWS-UG

jawsugtokyo

tech

はじめに

@mujyun_furu こと古林信吾(ふるばやししんご)です。
今回は、2024/5/15 の「JAWS-UG東京ランチタイムLT会 #11」に登壇した際お話した、「インフラエンジニアがAthenaと格闘してみた」の詳細内容です。

Amazon Athena とは

簡単に言うと、Amazon S3 に保存されたデータを SQL でデータ解析できるサービスです。

・非構造化データ、半構造化データ、および構造化データの分析が可能
・CSV 形式、JSON 形式、列データ形式 (Apache Parquet や Apache ORC など) に対応
といった特徴があります。

詳細はドキュメントをご覧ください。
https://aws.amazon.com/jp/athena/

Athena を使うには、まず解析したいデータに対してテーブルを定義し、そのテーブルに対してクエリを発行する必要があります。
・どこにデータがあるのか
・どのような列名なのか
・どのようなデータ型なのか
といった情報をテーブル定義します。
その際に、SerDe(シリアライザー/デシリアライザー)を活用しますが、データ形式や正規表現を理解していないと適切なクエリを発行できません。

Athena による SQL クエリ単位の課金は、実行したクエリでスキャンされたデータに対して発生します。
データの圧縮や、パーティション化、列指向形式に変換することで料金を節約できます。

パーティション化をすることで、スキャンされるデータの量を制限することが出来ます。
パーティション化は、データがどのように格納されているかに合わせて設定します。
更に、パーティション射影をすることで、パーティション管理を自動化することが出来ます。

背景

もともとオンプレのインフラエンジニアで、プログラミングとかコーディングとか全然わからない筆者に Athena でのデータ解析依頼がありました。
SQL について最低限の使い方は知っており、簡単な内容なら読み書きできますが、細かい関数とかは把握していません。
また、正規表現もマニュアルを読みながらなら辛うじて読めますが、出力形式に合わせてカスタマイズするとかは全然できません。
そのため、生成AI を活用して、Athena と格闘してみました。

使用する生成AI

今回は無料枠で利用可能な生成AIを複数活用しています。

Copilot in Bing (Microsoft)
Gemini (Google)
Claude 3 Sonnet (Anthropic)

今回解析するデータ

Linux の audit (監査)ログで、以下の形式になっています。
type=監査ログタイプ msg=audit(UNIXタイム:ID): ログ詳細

サンプルログ

type=SYSCALL msg=audit(1712886969.746:14364): arch=c000003e syscall=2 success=yes exit=4 a0=7fefbd8a0c21 a1=80000 a2=1b6 a3=80000 items=1 ppid=3107 pid=3489 auid=4294967295 uid=0 gid=0 euid=0 suid=0 fsuid=0 egid=0 sgid=0 fsgid=0 tty=(none) ses=4294967295 comm="sshd" exe="/usr/sbin/sshd" key="audit_log"
type=CWD msg=audit(1712886969.746:14364): cwd="/"
type=PATH msg=audit(1712886969.746:14364): item=0 name="/etc/shadow" inode=9240400 dev=ca:01 mode=0100000 ouid=0 ogid=0 rdev=00:00 nametype=NORMAL cap_fp=0 cap_fi=0 cap_fe=0 cap_fver=0 cap_frootid=0
type=PROCTITLE msg=audit(1712886969.746:14364): proctitle=2F7573722F7362696E2F73736864002D44002D52
type=USER_ACCT msg=audit(1712886969.746:14365): pid=3489 uid=0 auid=4294967295 ses=4294967295 msg='op=PAM:accounting grantors=pam_unix acct="ec2-user" exe="/usr/sbin/sshd" hostname=xxx.xxx.xxx.xxx addr= xxx.xxx.xxx.xxx terminal=ssh res=success'
type=CRYPTO_KEY_USER msg=audit(1712886969.750:14366): pid=3489 uid=0 auid=4294967295 ses=4294967295 msg='op=destroy kind=session fp=? direction=both spid=3490 suid=74 rport=38264 laddr=xxx.xxx.xxx.xxx lport=22 exe="/usr/sbin/sshd" hostname=? addr= xxx.xxx.xxx.xxx terminal=? res=success'
type=USER_AUTH msg=audit(1712886969.750:14367): pid=3489 uid=0 auid=4294967295 ses=4294967295 msg='op=success acct="ec2-user" exe="/usr/sbin/sshd" hostname=? addr=xxx.xxx.xxx.xxx terminal=ssh res=success'

audit ログから、監査のためにログイン記録を抽出します。

いつログインしたか
どのユーザがログインしたか
どのIPアドレスからログインしたか

ログは、Amazon Data Firehose を経由して Amazon S3 に配信しているため、以下のフォルダ階層で格納されています。
s3://backetname/prefix/yyyy/MM/dd/HH/ファイル
何年/何月/何日/何時となっています。

テーブルの作成

各生成AIにテーブルを作成する方法を以下のように質問しました。

AWSを使ったデータアナリストとして教えてください。

Amazon Athena を使い、Amazon S3 配下のデータを解析したい。

格納されているのは Linux の audit ログのデータであり、gzip 形式で圧縮されており、以下の形式になっている。
  type=監査ログタイプ msg=audit(UNIXタイム:ID): ログ詳細
  type=監査ログタイプ msg=audit(UNIXタイム:ID): ログ詳細
    :

正規表現の SerDe を使い、監査ログタイプと、UNIXタイム、ログ詳細を各列とするテーブルを1つのSQL文で作成したい。

テーブル作成するための SQL を教えてください。

対象のS3は以下です。
  s3://backetname/prefix/

Copilot in Bing

回答された SQL でテーブル作成には成功しました。
しかしながら、データをプレビューしようとするとエラーが発生します。

列数が正しくないとなっています。
Gemini

回答された SQL で、そもそもテーブル作成に失敗しました。

指定内容が正しくないようです。
Claude 3 Sonnet

回答された SQL でテーブル作成に成功しました。

データをプレビューすると、結果は返ってきますが、unix_time 列が空になってしました。

エラーの発生しない Claude 3 Sonnet の SQL について、実際のデータと unix_time 列のデータ型を確認すると、実際のデータには少数が含まれるにも関わらず、整数型である BIGINT を使われており、これが原因とわかります。
少数も扱える DECIMAL にデータ型を変更することで、データ取得に成功しました。
更に、ヘッダー行を不要と判断してスキップ処理が追加されておりましたが、ヘッダーのないデータのため不要記述を削除しました。
入出力フォーマットの指定もされていましたが、今回のデータでは不要なので、合わせて記述を削除しました。

結果、以下のような SQL となりました。

CREATE EXTERNAL TABLE audit_logs (
  log_type STRING,
  unix_time DECIMAL,
  log_details STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex' = 'type=(.+?) msg=audit\\((.+?):.+?\\): (.+)'
)
LOCATION 's3://backetname/prefix/';

SQL による必要データの抽出

今回目的とするログイン記録を抽出する方法を確認します。
Linux の audit ログでは、以下を抽出すれば実現できます。

type=USER_START の行
msg=audit(UNIXタイムをタイムスタンプ型(JST)に変換
sshd と session_open が含まれる(SSHでログインしてきた)行
acct="ユーザ名"
addr=接続元のIPアドレス
※type=USER_LOGIN の行の場合、どのIPアドレスからログインしたか記録されないため上記を条件にしています。

該当行のサンプルログ

type=USER_START msg=audit(1712886969.754:14385): pid=3489 uid=0 auid=1000 ses=7 msg='op=PAM:session_open grantors=pam_selinux,pam_loginuid,pam_selinux,pam_namespace,pam_keyinit,pam_keyinit,pam_limits,pam_systemd,pam_unix,pam_lastlog acct="ec2-user" exe="/usr/sbin/sshd" hostname=XXX.XXX.XXX.XXX addr=XXX.XXX.XXX.XXX terminal=ssh res=success'

各生成AIにデータを抽出する SQL を以下のように質問しました。

Amazon S3 に格納されているデータを Amazon Athena で抽出したい。
テーブル(audit_logs)は作成済であり、以下の内容を1つのSQL文で実現する方法を教えてください。

log_type 列の内容が USER_START であり、log_details 列に sshd と session_open が両方含まれる行を抽出したい。
抽出結果では、unix_time 列がUNIXタイムスタンプのため、タイムスタンプ(JST)に変換し login_time 列として抽出したい。
ユーザ名は log_details 内に acct="ユーザ名" の形式で格納されているので、user 列として抽出したい。
接続元IPアドレスは log_details 内に addr=IPアドレス の形式で格納されているので、ipaddress 列として抽出したい。

Copilot in Bing

回答された SQL ではエラーになりました。

from_unixtime 関数の使い方に問題があると言われます。
数回問い合わせれば以下のような正解にたどり着きます。
　　from_unixtime(unix_time, 'yyyy-MM-dd HH:mm:ss', 'JST') AS login_time,
　　　↓
　　from_unixtime(unix_time, 'Asia/Tokyo') AS login_time
Gemini

回答された SQL ではエラーになりました。

substring_index という関数は使えないと言われます。
エラー内容を伝えることで、SUBSTRING_INDEX 関数が Amazon Athena でサポートされていないと気付き、REGEXP_EXTRACT 関数を案内され、数回の試行錯誤で正常動作を確認できます。
Claude 3 Sonnet

回答された SQL でデータの抽出に成功しました。

しかしながら、JST で出力して欲しいにも関わらず、UTC のままになっていました。

Claude 3 Sonnet に、追加質問することで、目的達成できました。

パーティション/パーティション射影の追加

今回、Amazon Data Firehose を経由して Amazon S3 に配信しています。
Athena ユーザーガイドに Amazon Data Firehose の場合のパーティション射影の例があるので、これを活用します。

最終形

これまでの生成AI を活用した Athena との格闘結果をまとめると以下のようになります。

CREATE EXTERNAL TABLE audit_log (
  log_type STRING,
  unix_time DECIMAL,
  log_details STRING
)
PARTITIONED BY (
  datehour string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex' = 'type=(.+?) msg=audit\\((.+?):.+?\\): (.+)'
)
LOCATION 's3://backetname/prefix/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.datehour.type' = 'date',
    'projection.datehour.range' = 'NOW-3YEARS,NOW+9HOURS',
    'projection.datehour.format' = 'yyyy/MM/dd/HH',
    'projection.datehour.interval' = '1',
    'projection.datehour.interval.unit' = 'HOURS',
    'storage.location.template' = 's3://backetname/prefix/${datehour}/'
);

SELECT
  date_add('hour', 9, from_unixtime(unix_time)) AS login_time,
  SPLIT_PART(SPLIT_PART(log_details, 'acct="', 2), '"', 1) AS user,
  SPLIT_PART(SPLIT_PART(log_details, 'addr=', 2), ' ', 1) AS ipaddress
FROM
  audit_logs
WHERE
  datehour >= 'yyyy/MM/dd/HH' AND datehour < 'yyyy/MM/dd/HH'
  AND log_type = 'USER_START'
  AND log_details LIKE '%sshd%'
  AND log_details LIKE '%session_open%';

まとめ

プログラミングやコーディングが出来ず、SerDe(シリアライザー/デシリアライザー)や正規表現、SQL の詳しい使い方を理解していないインフラエンジニアでも、生成AI を活用することで、Athena から求めるデータを抽出することが可能です。
生成AI によって動きは異なり、正解も1つではありませんが、食わず嫌いするのではなく格闘してみることをお勧めします。

本記事により、誰かが一歩を踏み出す助けになれば幸いです。