Redshift Spectrum で S3 に格納された JSON ファイルを検索する #S3

はじめに

弊社ではウェブサーバーのアクセスログやアプリケーションログはDatadogのログ管理機能を使ってログの集約から可視化までを行っています。

これらは運用上、サービスの正常性を確認したり、トラブルシューティングに主に使われていますが、Log Analytics 機能を使えば簡単なグラフを作成することはできるので、簡単なデータ解析を行うことも可能です。
ただ、ログの保存期限は最大で60日なので（プランによる）、長期間のデータを使った分析をDatadog上で行うことができません。

Datadogでは、収集したログデータをS3などのクラウドストレージに転送する機能があるため、クラウドストレージにデータを貯め込んでこちらのデータを解析に使うのがよさそうです。
また、この機能を使えば、Datadogで行ったパース済のデータがJSON形式で転送されるため、保存されたデータは他のツールから解析しやすい形になっているのも良い点です。

弊社ではログデータ以外にも本番環境から参照されないスレーブをデータ転送用として設置して、そこからEmbulkを使ってデータの整形や個人情報を含むデータを削除したりしながら Redshift にデータを転送して蓄積しています。BIツールは Metabase を採用しています。

ログ管理 | Datadog
ログアーカイブ

最初は、Embulkを使ってS3のデータもRedshiftに転送する方法で検証していましたが、データ変換処理などでCPU使用率が高くて転送速度が想定以上に出なかったため断念しました。現在は、下記のように Redshift Spectrum を使って直接 S3 のデータを参照する方法にしています。

Redshift Spectrum とは？

Redshift を使って S3 に格納されたデータをSQLで検索できます
既に Redshift を使っているなら、簡単に使い始めることができます
外部データベース扱いなので、内部のテーブルとの結合など高度な解析が可能です
スキャンされたバイト数による課金のため、普通の Redshift の料金体系とは異なります Redshift Spectrum の料金

Redshift Specturm と同様に S3 のデータに対して SQL を実行するものとして Athena がありますが、使っているBIツールの Metabase が Athena に対応していなかったため未検証です。

使ってみる

基本的な使い方は公式のドキュメント通りです
Amazon Redshift Spectrum の開始方法

IAM ロールの作成とクラスターを関連づけ

Redshift から S3 へアクセスするための許可と、AWS Glue もしくは AWS Athena のデータカタログへのアクセス許可をするIAM Roleが必要です。今回は AWS Glue を使ってみました。

弊社ではTerraformを使っているためIAM Roleを作成するのは以下のようなコードになります（実際にはモジュールを使ったりと抽象化されています）

data "aws_iam_policy_document" "this" {
  statement {
    actions = ["sts:AssumeRole"]

    principals {
        type = "Service"
        identifiers = ["redshift.amazonaws.com"]
    }
  }
}

resource "aws_iam_role" "this" {
  name                  = "redshift-assume-role"
  assume_role_policy    = data.aws_iam_policy_document.this.json
}

resource "aws_iam_role_policy_attachment" "s3" {
  role       = aws_iam_role.this.name
  policy_arn = "arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess"
}

resource "aws_iam_role_policy_attachment" "glue" {
  role       = aws_iam_role.this.name
  policy_arn = "arn:aws:iam::aws:policy/AWSGlueConsoleFullAccess"
}

resource "aws_redshift_cluster" "this" {
  ..(省略)..
  iam_roles = [aws_iam_role.this.arn]
  ..(省略)..
}

外部スキーマの作成

Redshift のコンソールにログインして外部スキーマを以下のようなコマンドを実行します。

CREATE EXTERNAL SCHEMA local_schema_name
FROM data catalog
DATABASE 'external_database_name'
iam_role '<作成したIAM Role の ARN>'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

local_schema_name は Redshift で参照するときのスキーマ名です。
external_database_name は、ドキュメントを見ても特に明示されていませんが、他の RDS PostgreSQL や Aurora PostgreSQL のデータベースを接続する場合は対象のデータベース名になります。S3の場合は特にデータベース名も無いため自由につけてよさそうです。

Amazon Redshift Spectrum 用の外部スキーマの作成

JSON形式のネストした構造を扱う外部テーブルの作成

Datadog からログ転送されたS3のデータはJSON形式に変換されたファイルが保存されています。
Redshift Spectrum では Parquet、ORC、JSON、Ion のネストしたデータもテーブル定義でネストデータを含む列を定義することでSQLを実行することができます。

ネストしたカラムの定義の例

CREATE EXTERNAL TABLE schema.test_table (
  "attributes" struct<"network":struct<"client":struct<"ip":varchar>>
)

テキストファイルで格納されたJSONフォーマットとして指定

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS textfile

S3のロケーション

LOCATION 's3://<ファイルが保存されたURL>'

テーブル定義の例

CREATE EXTERNAL TABLE local_schema_name.access_logs (
    "datetime" timestamp,
    "service" varchar,
    "host" varchar,
    "status" varchar,
    "source" varchar,
    "attributes" struct<
        "network":struct<
            "client":struct<
                "ip":varchar>,
            "bytes_written":integer>,
        "http": struct<
            "method":varchar,
            "url":varchar,
            "version":varchar,
            "status_code":varchar,
            "response_time":integer,
            "protocol":varchar,
            "useragent":varchar,
            "referer":varchar,
            "useragent_details":struct<
                "browser":struct<
                    "patch":varchar,
                    "major":varchar,
                    "minor":varchar,
                    "family":varchar>,
                "os":struct<
                    "major":varchar,
                    "minor":varchar,
                    "family":varchar>,
                "device":struct<
                    "family":varchar,
                    "category":varchar>>,
            "url_details":struct<
                "path":varchar>,
            "status_category":varchar>,
        "duration":integer>
)
PARTITIONED BY ("dt" date)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS textfile
LOCATION 's3://example/access_logs/';

チュートリアル: Amazon Redshift Spectrum を使用したネストデータのクエリ
 CREATE EXTERNAL TABLE

パーティショニングを追加する

Redshift Spectrum はスキャンするデータ量に応じて課金されるため、パーティショニングを追加することで、スキャンするデータ量を減らすことができます。

Datadog では S3 に転送する際、日付と時間で以下のようなパスが切られるので日付部分を使うことにしました。

s3://<bucket>/<path>/dt=20200201/hour=10/xxxxxxx.json.gz

上記のテーブル定義の例ではパーティション名を既に定義済み。

PARTITIONED BY ("dt" date)

パーティションを追加していく。

ALTER TABLE local_schema_name.access_logs ADD
PARTITION(“dt”=‘2020–02-01’) LOCATION ’s3://example/access_logs/dt=20200101’
PARTITION(“dt”=‘2020-02-02’) LOCATION ’s3://example/access_logs/dt=20200102’;

検索時にパーティションカラムを追加することでスキャンデータを減らせます

SELECT attributes.network.client.ip
  FROM local_schema_name.access_logs
 WHERE dt = ’2020-02-01’
 LIMIT 10;

まとめ

Redshift Spectrum を使うことで、S3のデータを参照することができるようになりました。
ただ、残念ながらMetabaseでは外部データベースをデータソースとして登録できないため、以下のようなテーブル一覧には表示されません。そのため、GUIでポチポチしながらグラフを作ることができず、SQLを直書きする必要があります。

弊社では、この辺りのデータ解析は開発エンジニアが行っているので問題ありませんが、SQLを書かない非エンジニアが触るような環境だとちょっと敷居が高いかもしれません。