share facebook facebook twitter menu hatena pocket slack

2017.02.14 TUE

Redshiftへ ELB/ALB/S3のアクセスログをそのままインポート(したかった)

WRITTEN BY 大住 孝之

S3に出力された ELB/ALB/S3 アクセスログを特に加工せずにRedshiftへインポート。。。したかったのですができませんでした。 (極力簡単にインポートし、整形・集計はDB側に任せようと思いました。)
スペース区切りで且つクォートが必要最低限しか付いていないという、Apacheアクセスログに似た形式のため加工が必要でした。 それぞれ書式が微妙に異なりますが、tsv等ログ内で使用されていない区切り文字に変換してしまえばインポート可能です。

アクセスログ形式

ELB

スペース区切り、未圧縮ファイル、ヘッダ無しです。 Request、UserAgent等はダブルクォートで囲まれた状態になります。

ALB

スペース区切り、圧縮(gzip)、ヘッダ無しです。 Request、UserAgent等はダブルクォートで囲まれた状態になります。

S3

スペース区切り、未圧縮ファイル、ヘッダ無しです。 Request、UserAgent等はダブルクォートで囲まれた状態になります。 また、タイムスタンプ部分は角括弧([])で囲まれています。

テーブル作成

各カラムは記号と予約語回避のため _ を使用しています。

ELB

CREATE TABLE elb_log (
 timestamp_    VARCHAR(4096)
,elb_    VARCHAR(4096)
,client_port_    VARCHAR(4096)
,backend_port_    VARCHAR(4096)
,request_processing_time_    VARCHAR(4096)
,backend_processing_time_    VARCHAR(4096)
,response_processing_time_    VARCHAR(4096)
,elb_status_code_    VARCHAR(4096)
,backend_status_code_    VARCHAR(4096)
,received_bytes_    VARCHAR(4096)
,sent_bytes_    VARCHAR(4096)
,request_    VARCHAR(4096)
,user_agent_    VARCHAR(4096)
,ssl_cipher_    VARCHAR(4096)
,ssl_protocol_    VARCHAR(4096)
);

ALB

CREATE TABLE alb_log (
 type_    VARCHAR(4096)
,timestamp_    VARCHAR(4096)
,elb_    VARCHAR(4096)
,client_port_    VARCHAR(4096)
,target_port_    VARCHAR(4096)
,request_processing_time_    VARCHAR(4096)
,target_processing_time_    VARCHAR(4096)
,response_processing_time_    VARCHAR(4096)
,elb_status_code_    VARCHAR(4096)
,target_status_code_    VARCHAR(4096)
,received_bytes_    VARCHAR(4096)
,sent_bytes_    VARCHAR(4096)
,request_    VARCHAR(4096)
,user_agent_    VARCHAR(4096)
,ssl_cipher_    VARCHAR(4096)
,ssl_protocol_    VARCHAR(4096)
,target_group_arn_    VARCHAR(4096)
,trace_id_    VARCHAR(4096)
);

S3

CREATE TABLE s3_log (
 bucket_owner_    VARCHAR(4096)
,bucket_    VARCHAR(4096)
,time_    VARCHAR(4096)
,remote_ip_    VARCHAR(4096)
,requester_    VARCHAR(4096)
,request_id_    VARCHAR(4096)
,operation_    VARCHAR(4096)
,key_    VARCHAR(4096)
,request_uri_    VARCHAR(4096)
,http_status_    VARCHAR(4096)
,error_code_    VARCHAR(4096)
,bytes_sent_    VARCHAR(4096)
,object_size_    VARCHAR(4096)
,total_time_    VARCHAR(4096)
,turn_around_time_    VARCHAR(4096)
,referrer_    VARCHAR(4096)
,user_agent_    VARCHAR(4096)
,version_id_    VARCHAR(4096)
);

インポート

スペース区切りをTAB区切りへ変換したファイルをgzip圧縮し、S3へ格納後にCOPYコマンドで取り込みます。

COPY { elb_log | alb_log | s3_log }
FROM 's3://bucket/key/〜.tsv.gz'
CREDENTIALS 'aws-auth-args'
GZIP
FORMAT CSV
DELIMITER '\t'

変換は様々な言語でツール化されていたりしますが、 今回は gawk で行いました。

元記事はこちら

Redshiftへ ELB/ALB/S3のアクセスログをそのままインポート(したかった)
 

大住 孝之

構築運用担当。 個性的な面々の中で無個性という個性を打ち出していこうと画策中。

cloudpack

cloudpackは、Amazon EC2やAmazon S3をはじめとするAWSの各種プロダクトを利用する際の、導入・設計から運用保守を含んだフルマネージドのサービスを提供し、バックアップや24時間365日の監視/障害対応、技術的な問い合わせに対するサポートなどを行っております。
AWS上のインフラ構築およびAWSを活用したシステム開発など、案件のご相談はcloudpack.jpよりご連絡ください。