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

# 概要

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

# ロード方法の検討

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

- https://docs.aws.amazon.com/ja_jp/AmazonS3/latest/dev/LogFormat.html

```
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]
```

- **COPYでタイムスタンプ型としてロードすることはできない**
- そのため、括弧を外して最終的にロードさせる必要がある
- ETL処理を噛ませる
  - https://ja.wikipedia.org/wiki/Extract/Transform/Load

#### ETL処理を噛ます?

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


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

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

# 一時テーブルへの挿入

## 一時テーブルの作成

```sql
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実施

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

## ゴールのテーブルを作成

```sql
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でロード

```sql
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
);
```

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

```sql
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

- 圧縮エンコーディングのタイプ
  - http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_Compression_encodings.html

- テンポラリテーブルからのINSERT INTO SELECTは自動圧縮の対象ではないので明示的に圧縮を指定する必要がある


## 手動圧縮の方法

- `ANALYZE COMPRESSION`で、挿入されているデータを解析する
- 得られた結果からスキーマに圧縮エンコーディングを定義
- テーブルを作り直してロードし直す
  - 既存のテーブルの圧縮エンコーディングの変更はできない

```sql
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の結果は変わります

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


```sql
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

```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

- [COPY - Amazon Redshift](http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_COPY.html)