📩

s3にあるアクセスログをredshift使って分析する話

2024/04/29に公開

AWS

ECS

Redshift

tech

前書き

目標は、S3に保存されているデータをRedshiftに移動することです。

S3に保管されるログは下記のディレクトリ構成に.gzタイプで圧縮されてます。

データの構造はJson、深い階層も存在します。

{
　　"level":"info",
　　"message":"HTTP GET /",
 "meta":{"req":{"headers":{"accept-charset":"utf-8","accept-encoding":"gzip","host":"xx.xxx.xxx.xx","user-agent":"ua","x-amzn-trace-id":"Root=x-xxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxx","x-forwarded-for":"xx.xxx.xxx.xxx","x-forwarded-port":"443","x-forwarded-proto":"https"},"httpVersion":"1.1","method":"GET","originalUrl":"/","query":{},"url":"/"},"res":{"statusCode":200},"responseTime":0},
 "container_name":"app",
 "ecs_task_definition":"app-task-definition:32"
}

やり方

Amazon Redshiftに移動するにはCOPYコマンド(現在プレビュークラスター版のみ)を使用できます。

COPYコマンドサンプル

COPY table-name 
[ column-list ]
FROM data_source
authorization
[ [ FORMAT ] [ AS ] data_format ] 
[ parameter [ argument ] [, ... ] ]

先にRedshiftに該当データ格納できるテーブルを作る必要がありますが、
現時点(2024/04/28)のRedshiftがベースにしてるpostgresqlのバージョンが下記のものになります。

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.66584

Json扱えるデータタイプJSONBは Version9.2以降しか存在しません。

代わりにRedshiftのSUPERタイプを使用します。

create-table.sql

CREATE TABLE http_logs (
    level VARCHAR(50),
    message TEXT,
    meta SUPER,
    container_name VARCHAR(50),
    ecs_task_definition VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COPYコマンド

COPY public.http_logs FROM 's3://<your-bucket-name>/2024/'
CREDENTIALS 'aws_iam_role=arn:aws:iam::111111111111:role/redshift-role'
JSON 'auto'
REGION 'ap-northeast-1'
GZIP IGNOREHEADER 1;

階層のあるテータにアクセスする場合、下記のようなクエリで実現可能です。

select
    meta.req.headers
FROM
    public.http_logs

該当S3にデータ追加されるたびにRedshiftに同期をしたい場合、COPYコマンドを下記のように修正すれば、実現できます。

copy.sql

COPY public.http_logs FROM 's3://<your-bucket-name>/2024/'
IAM_ROLE 'arn:aws:iam::111111111111:role/redshift-role'
format as json 'auto ignorecase' gzip
REGION 'ap-northeast-1'
GZIP IGNOREHEADER 1;
JOB CREATE "copy_logs"
AUTO ON;

参考ドキュメント

前書き

やり方

Discussion