Go to Qiita Advent Calendar 2024 Top
LoginSignup
9
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@iogi(Ippei Ogiwara)

Amazon S3のアクセスログをRedshiftにロードする

Posted at

概要

S3のアクセスログは、直接Redshiftにはロードできないので、一旦テンポラリテーブルに入れてロードしよう

ロード方法の検討

S3のアクセスログのフォーマット

79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:00:38 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be 3E57427F3EXAMPLE REST.GET.VERSIONING - "GET /mybucket?versioning HTTP/1.1" 200 - 113 - 7 - "-" "S3Console/0.4" -
79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:00:38 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be 891CE47D2EXAMPLE REST.GET.LOGGING_STATUS - "GET /mybucket?logging HTTP/1.1" 200 - 242 - 11 - "-" "S3Console/0.4" -
79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:00:38 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be A1206F460EXAMPLE REST.GET.BUCKETPOLICY - "GET /mybucket?policy HTTP/1.1" 404 NoSuchBucketPolicy 297 - 38 - "-" "S3Console/0.4" -
79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:01:00 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be 7B4A0FABBEXAMPLE REST.GET.VERSIONING - "GET /mybucket?versioning HTTP/1.1" 200 - 113 - 33 - "-" "S3Console/0.4" -
79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:01:57 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be DD6CC733AEXAMPLE REST.PUT.OBJECT s3-dg.pdf "PUT /mybucket/s3-dg.pdf HTTP/1.1" 200 - - 4406583 41754 28 "-" "S3Console/0.4" -
79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be mybucket [06/Feb/2014:00:03:21 +0000] 192.0.2.3 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be BC3C074D0EXAMPLE REST.GET.VERSIONING - "GET /mybucket?versioning HTTP/1.1" 200 - 113 - 28 - "-" "S3Console/0.4" -

フィールドの内容とサンプル

Field Name Example
Bucket Owner 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be
Bucket mybucket
Time [06/Feb/2014:00:00:38 +0000]
Remote IP 192.0.2.3
Requester 79a59df900b949e55d96a1e698fbacedfd6e09d98eacf8f8d5218e7cd47ef2be
Request ID 3E57427F33A59F07
Operation REST.PUT.OBJECT
Key "/photos/2014/08/puppy.jpg"
Request-URI "GET /mybucket/photos/2014/08/puppy.jpg?x-foo=bar"
HTTP status 200
Error Code NoSuchBucket
Bytes Sent 2662992
Object Size 3462992
Total Time 70
Turn-Around Time 10
Referrer "http://www.amazon.com/webservices"
User-Agent "curl/7.15.1"
Version Id 3HL4kqtJvjVBH40Nrjfkd

課題

カラムの検討

  • Request-URI等、最大長が不明なカラムがある
  • varchar (可変長)で適当なサイズで入れておけばOK
  • Redshiftでは圧縮があるので、同じパターンが多いなら圧縮効率は向上する
  • charとvarcharどっちがよい?
    • マルチバイトはvarchar必須
    • 固定長ならchar、可変長ならvarchar
    • char(3) は3バイト消費するが、varchar(3)は4バイト消費 (長さを格納)
    • ただRedshiftは圧縮があるので(ry
  • エラーを出力するのでなく切り落としてよい場合は TRUNCATECOLUMNS をCOPYで指定

N/Aの場合に "-" と記録される

  • 文字列型ならそのまま入りはするが、Object SizeなどInt (整数)で入れたい
    • 適切なデータ型に入れることで、パフォーマンスやデータ格納効率の向上、想定外の値の混入などを避けられる
  • COPYオプションの NULL ASを利用する

タイムスタンプが [ ] で囲まれる

[06/Feb/2014:00:00:38 +0000]

ETL処理を噛ます?

  • 目的のデータ形式に変換する
    • [06/Feb/2014:00:00:38 +0000] をtimestamp型に
    • 要らないカラムを削除
    • その他データの整形
  • 方法?
    • Data PipelineやEMRでCSVを変形してRedshiftに入れる
    • 一度Redshiftにロードして文字列処理して最終的なテーブルにする

分散キー、ソートキーの検討

  • 分散キーは要件に応じて変わってくるが、データの偏りも考慮すると、EVEN か、分散キーにrecord_timeを指定するくらいと思われる
  • ソートキーは record_time くらいでは (Interleaved Sortで http_status 等も含めるとかあり)

一時テーブルへの挿入

一時テーブルの作成

DROP TABLE IF EXISTS s3_log_temp;
CREATE /* TEMPORARY */ TABLE s3_log_temp (
  bucket_owner CHAR(64),
  bucket CHAR(64),
  time1 CHAR(21),
  time2 CHAR(6),
  remote_ip CHAR(15),
  requester VARCHAR(200),
  request_id CHAR(16),
  operation VARCHAR(100),
  object_key VARCHAR(1024),
  request_uri VARCHAR(2000),
  http_status CHAR(3),
  error_code CHAR(20),
  bytes_sent         INT,
  object_size        INT,
  total_time         INT,
  turn_around_time   INT,
  referrer VARCHAR(2000),
  user_agent VARCHAR(2000),
  version_id CHAR(21)
);
  • CREATE TEMPORARY TABLE は、そのセッションの存在中にのみ有効なテーブルを作成します。
    • 実運用では、このテーブルは一時的な処理なので、TEMPORARYキーワードを付けることとなりますが、とりあえず試行錯誤するので外す
  • request_uri等の長さは適当。はみ出る場合はカラムのサイズを大きくするか、COPY時に TRUNCATECOLUMNS オプションを付与して切り捨て

COPY実施

COPY s3_log_temp
FROM 's3://...' 
delimiter ' '            -- 空白区切り
REMOVEQUOTES             -- 引用符を除去
NULL AS '-'              -- ハイフンをNULLで格納
TRUNCATECOLUMNS          -- 長い文字列をカラムに収める
CREDENTIALS '...'
REGION 'ap-northeast-1'; -- 別リージョンからの取得の場合は region 指定

ゴールのテーブルを作成

CREATE TABLE s3_log 
(
  bucket_owner       CHAR(64)  NOT NULL,
  bucket             CHAR(64)  NOT NULL,
  record_time        TIMESTAMP NOT NULL,
  remote_ip          CHAR(15),
  requester          VARCHAR(200),  -- 長さは適当
  request_id         CHAR(16) NOT NULL,
  operation          VARCHAR(100) NOT NULL,
  object_key         VARCHAR(1024), -- 長さはS3側のキー上限値 (1024 bytes)
  request_uri        VARCHAR(2000), -- 長さは適当
  http_status        CHAR(3),
  error_code         CHAR(20),
  bytes_sent         INT,
  object_size        INT,
  total_time         INT,
  turn_around_time   INT,
  referrer           VARCHAR(2000), -- 長さは適当
  user_agent         VARCHAR(2000), -- 長さは適当
  version_id         CHAR(21)
);

目的のテーブルにINSERT INTO SELECTでロード

INSERT INTO s3_log
(
  SELECT bucket_owner,
  bucket,
  LTRIM(time1,'[')::TIMESTAMP AS record_time,
  remote_ip,
  requester,
  request_id,
  operation,
  object_key,
  request_uri,
  http_status,
  error_code,
  bytes_sent,
  object_size,
  total_time,
  turn_around_time,
  referrer,
  user_agent,
  version_id
  FROM s3_log_temp
);

とりあえずロードできた!!

dev=# SELECT COUNT(*) FROM s3_log;
  count
---------
 1234567
(1 行)

圧縮の検討

実は、上記手順でロードしたテーブルのレコードは圧縮が行われません。
空のテーブルにCOPYした場合、自動圧縮が適用されるが、INSERT INTO SELECTでは行われないためです。
ということでスキーマ作成時に圧縮エンコーディングを明示的に指定する必要があります。

自動圧縮

  • 空の圧縮エンコーディング未指定のテーブルに、COMPUPDATE OFF を指定せずにCOPYすれば自動圧縮が働く

COPY コマンドは、デフォルトでは、空のターゲットテーブルで COPY コマンドを実行し、すべてのテーブル列で RAW エンコーディングかエンコーディングなしが設定されている場合に、自動圧縮を適用します。

現在の圧縮エンコーディングに関係なく、空のテーブルに自動圧縮を適用するには、COMPUPDATE オプションを ON に設定して COPY コマンドを実行します。自動圧縮を無効にするには、COMPUPDATE オプションを OFF に設定して COPY コマンドを実行します。
http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_Loading_tables_auto_compress.html

手動圧縮の方法

  • ANALYZE COMPRESSIONで、挿入されているデータを解析する
  • 得られた結果からスキーマに圧縮エンコーディングを定義
  • テーブルを作り直してロードし直す
    • 既存のテーブルの圧縮エンコーディングの変更はできない
dev=# analyze compression s3_log
dev-# ;
 Table  |      Column      | Encoding
--------+------------------+-----------
 s3_log | bucket_owner     | runlength
 s3_log | bucket           | runlength
 s3_log | record_time      | lzo
 s3_log | remote_ip        | lzo
 s3_log | requester        | lzo
 s3_log | request_id       | lzo
 s3_log | operation        | lzo
 s3_log | object_key       | lzo
 s3_log | request_uri      | lzo
 s3_log | http_status      | lzo
 s3_log | error_code       | lzo
 s3_log | bytes_sent       | lzo
 s3_log | object_size      | lzo
 s3_log | total_time       | bytedict
 s3_log | turn_around_time | bytedict
 s3_log | referrer         | lzo
 s3_log | user_agent       | lzo
 s3_log | version_id       | raw
(18 行)

注: アクセスログのデータの傾向によってANALYZE COMPRESSIONの結果は変わります

圧縮エンコーディングを踏まえて再作成

DROP TABLE s3_log;
CREATE TABLE s3_log 
(
  bucket_owner       CHAR(64) encode runlength,
  bucket             CHAR(64) encode lzo,
  record_time        TIMESTAMP encode lzo distkey, -- 分散キー
  remote_ip          CHAR(15) encode lzo,
  requester          VARCHAR(200) encode lzo,
  request_id         CHAR(16) encode lzo,
  operation          VARCHAR(100) encode lzo,
  object_key         VARCHAR(2000) encode lzo,
  request_uri        VARCHAR(2000) encode lzo,
  http_status        CHAR(3) encode lzo,
  error_code         CHAR(20) encode lzo,
  bytes_sent         INT encode lzo,
  object_size        INT encode lzo,
  total_time         INT encode bytedict,
  turn_around_time   INT encode bytedict,
  referrer           VARCHAR(2000) encode lzo,
  user_agent         VARCHAR(2000) encode lzo,
  version_id         CHAR(21) encode raw
)
SORTKEY (record_time);

結論のSQL

CREATE TEMPORARY TABLE s3_log_temp (
  bucket_owner       CHAR(64),
  bucket             CHAR(64),
  time1              CHAR(21),
  time2              CHAR(6),
  remote_ip          CHAR(15),
  requester          VARCHAR(200),
  request_id         CHAR(16),
  operation          VARCHAR(100),
  object_key         VARCHAR(1024), -- S3のキー上限
  request_uri        VARCHAR(2000),
  http_status        CHAR(3),
  error_code         CHAR(20),
  bytes_sent         INT,
  object_size        INT,
  total_time         INT,
  turn_around_time   INT,
  referrer           VARCHAR(2000),
  user_agent         VARCHAR(2000),
  version_id         CHAR(21)
);

COPY s3_log_temp
FROM 's3://...' 
delimiter ' '            -- 空白区切り
REMOVEQUOTES             -- 引用符を除去
NULL AS '-'              -- ハイフンをNULLで格納
TRUNCATECOLUMNS          -- 長い文字列をカラムに収める
CREDENTIALS '...'
REGION 'ap-northeast-1'; -- 別リージョンからの取得の場合は region 指定


-- データ傾向によってより適切な圧縮エンコーディングが考えられるため
-- 必要に応じて、ANALYZE COMPRESSIONを実施して適切な圧縮エンコーディングを判断
CREATE TABLE IF NOT EXISTS s3_log 
(
  bucket_owner       CHAR(64) encode runlength,
  bucket             CHAR(64) encode lzo,
  record_time        TIMESTAMP encode lzo distkey,
  remote_ip          CHAR(15) encode lzo,
  requester          VARCHAR(200) encode lzo,
  request_id         CHAR(16) encode lzo,
  operation          VARCHAR(100) encode lzo,
  object_key         VARCHAR(2000) encode lzo,
  request_uri        VARCHAR(2000) encode lzo,
  http_status        CHAR(3) encode lzo,
  error_code         CHAR(20) encode lzo,
  bytes_sent         INT encode lzo,
  object_size        INT encode lzo,
  total_time         INT encode bytedict,
  turn_around_time   INT encode bytedict,
  referrer           VARCHAR(2000) encode lzo,
  user_agent         VARCHAR(2000) encode lzo,
  version_id         CHAR(21) encode raw
)
SORTKEY (record_time);
 
-- タイムスタンプを組み立てる (タイムゾーンは固定なので捨てる)
INSERT INTO s3_log
(
  SELECT bucket_owner,
  bucket,
  LTRIM(time1,'[')::TIMESTAMP AS record_time,
  remote_ip,
  requester,- 
  request_id,
  operation,
  object_key,
  request_uri,
  http_status,
  error_code,
  bytes_sent,
  object_size,
  total_time,
  turn_around_time,
  referrer,
  user_agent,
  version_id
  FROM s3_log_temp
);

See Also

9
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?