Amazon S3 および Athena を使用して外部データを調査と統合

使用できる外部データ

Athena を使用すると、JSON、CSV、Parquet などの一般的な形式を含む、さまざまな形式の外部データを、調査クエリに追加して統合することができます。これを行うための基本的なプロセスは、形式に関係なく、ほぼ共通しています。

Determine the schema for your data
Load the data into S3
Create the table
Join the external data with internal data

外部データの例

この例は、ユーザーが実際の認証情報を使用してサーバーにログインできるようにする ssh プロキシからのサンプルログです。

ヘッダ:

session_id, login_date, real_user, host, local_user

ファイル:

10203,"2020-09-21 03:04:05.324",laneb,ip-172-31-76-158,ec2-user
10204,"2020-09-21 15:14:22.324",mikep,ip-172-31-76-158,ec2-user

S3 にデータを読み込む

Athena がデータにアクセスできるようにするには、データを S3 にアップロードしておく必要があります。多くのツールはログを S3 に出力できますが、この例では、ファイルサイズが小さいため、アップロードするだけで十分です。データは、1つまたは複数のファイルとして S3 にアップロードできます。このため、次の手順で作成するテーブルの名前と一致するフォルダにファイルを保存することを推奨します。

スキーマを決定する

前述の例のログインデータに基づいて、このファイルのデータ型は次のようになります。

session_id int
login_date string
real_user  string
host       string
local_user string

Athena スキーマは sql スキーマであり、CREATE EXTERNAL TABLE ステートメントを使用して作成されます。このテーブルの場合、CREATE ステートメントは次のようになります。

CREATE EXTERNAL TABLE proxy_logs (
    session_id INT,
login_date STRING,
real_user STRING,
host STRING,
local_user STRING
)
ROW FORMAT SERDE `org.apache.hadoop.hive.serde2.OpenCSVSerde`
WITH SERDEPROPERTIES (
    "separatorChar" = ",", "quoteChar" = "\" )
LOCATION `s3://[BUCKET_NAME]/proxy_logs`;

Athena でこれを実行すると、proxy_logs テーブルが作成されます。さらに、データを含むファイルが [BUCKET_NAME]/proxy logs/ にあることを示します。なお、[BUCKET_NAME] は、前の手順のバケットで置き換えられます。

外部データを内部データと結合する

テーブルの作成が完了すると、データは Athena で公開されます。これは、SELECT ステートメントを使用して表示できます。

次に例を示します。

SELECT * FROM proxy_logs ORDER BY session_id DESC LIMIT 100

これによって、前回 100件のログインセッションが取得されます。またこれは、新しいテーブルを調査データと結合できることも意味します。たとえば、次のクエリは、警告発生の 1時間以内にホストにログインしたユーザーすべてを取得します。

SELECT *
FROM proxy_logs pl
LEFT JOIN sensors s
ON s.hostname = pl.host
LEFT JOIN alerts a
ON s.sensor_id = a.sensor_id
WHERE a.incident_id = `adde60c8-4c72-4662-8266-5d968cca20e4` AND date_diff(`day`, date_parse(login_date, '%Y-%m-%d %H:%i:%s'), from_unixtime(a.unix_nano_timestamp / 1e9)) BETWEEN -1 AND 0