Amazon RedshiftにJSONフォーマットのデータをインポートする

#Amazon Redshift

#AWS

しんや

2014.08.15

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Amazon Redshiftに於けるデータのインポートでは、代表的なところだとCSV(カンマ区切り)やTSV(タブ区切り)形式のものを取り込む事が多いかと思いますが、JSON形式のファイルインポートにも実は対応しています。

JSON 形式からの COPY - Amazon Redshift / COPY from JSON format - Amazon Redshift

そこで当エントリでは、Amazon RedshiftへのJSONデータ取り込みについて紹介・実践してみたいと思います。(※上記リストにあるように日本語ドキュメントも整備されてますので、ポイントだけ掻い摘む形で)

JSONとは
Amazon RedshiftへのJSONファイルの取り込み方法
手順1.jsonpathsオプションを使い、取り込むファイルの内容・順番をテーブル項目名と一致した名前の指定で取り込む場合
手順2.jsonpathsオプションを使い、取り込むファイルの内容・順番を配列のインデックス値で指定して取り込む(JSON 配列からのロードの場合)
手順3.autoオプションを使って取り込む(jsonpathsオプションは使わず、ファイル内の項目名とテーブル項目名を一致させた状態とする事で対応)
まとめ

JSONとは

そもそもJSONとは何ぞや？という点については、Wikipediaを御参照ください。

JavaScript Object Notation - Wikipedia

JSON（ジェイソン、JavaScript Object Notation）は軽量なデータ記述言語の1つである。
構文はJavaScriptにおけるオブジェクトの表記法をベースとしているが、
JSONはJavaScript専用のデータ形式では決してなく、様々なソフトウェアやプログラミング言語間における
データの受け渡しに使えるよう設計されている。

また、Amazon Redshiftに於けるJSONファイルの仕様についても公式ドキュメントに展開されていますのでそちらを御参照ください。

JSON 形式からの COPY - Amazon Redshift / COPY from JSON format - Amazon Redshift

JSON のデータ構造は、一連のオブジェクトまたは配列により構成されています。
オブジェクトの先頭と末尾には中括弧が付き、順序が設定されていない一連の名前と値のペアが含まれます。
各名前と値はコロンで区切られ、ペアはカンマで区切られます。名前は二重引用符で囲まれた文字列です。
JSON 配列の先頭と末尾には角括弧が付き、順序が設定された一連のカンマ区切りの値が含まれます。
値には、二重引用符で囲まれた文字列、数値、ブール値の true または false、Null、JSON オブジェクト、
配列を指定できます。
JSON のオブジェクトと配列を入れ子にして、階層データ構造を実現できます。

Amazon RedshiftへのJSONファイルの取り込み方法

ドキュメントを参照する限りですと、以下の手法を取ることが出来るようです。

手順1.jsonpathsオプションを使い、取り込むファイルの内容・順番をテーブル項目名と一致した名前の指定で取り込む
手順2.jsonpathsオプションを使い、取り込むファイルの内容・順番を配列のインデックス値で指定して取り込む
手順3.autoオプションを使って取り込む(jsonpathsオプションは使わず、ファイル内の項目名とテーブル項目名を一致させた状態とする事で対応)

当エントリでは、CloudTrailで出力されるログファイルをサンプルとして利用してみたいと思います。

現状CloudTrailの実行ログは構造上の都合で以下のような形になってますので、

{"Records":[
{"eventVersion":"1.01","userIdentity":....},{"eventVersion":"1.01","userIdentity":....},{"eventVersion":"1.01","userIdentity":....},...
]}

以下の形式にファイルを加工しておきます。先頭のRecordsと対になる括弧、またeventVersion毎のレコード間に含まれるカンマを除去し、改行することで1行1レコードとしました。

{"eventVersion":"1.01","userIdentity":....}
{"eventVersion":"1.01","userIdentity":....}
{"eventVersion":"1.01","userIdentity":....}
:

また、3つ目の手法『autoオプションを使う』場合、テーブル項目名とファイル内の項目名は一致させる必要があるようです。JSONファイルの項目名はキャメルケース(複合語をひと綴りとして、要素語の最初を大文字で書き表す)形となっているので、スネークケース(アンダースコア（_）を区切記号として単語をつなげる)形に一律変更するルールで進めてみたいと思います。

キャメルケース - Wikipedia

{"event_version":"1.01","user_identity":....}
{"event_version":"1.01","user_identity":....}
{"event_version":"1.01","user_identity":....}
:

作成するテーブルも、上記ルールに基づき以下の形で進めてみます。

DROP TABLE cloudtrail_log;
CREATE TABLE cloudtrail_log (
  event_version VARCHAR(4),
  :
);

手順1.`jsonpaths`オプションを使い、取り込むファイルの内容・順番をテーブル項目名と一致した名前の指定で取り込む場合

上記ルールで再構成(主にリネーム)したデータ1行のフォーマットはこのような形となります。

{
"event_version":"1.01",
"user_identity":{
    "type":"IAMUser",
    "principal_id":"XXXXXXXXXXXXXXXXXXXX",
    "arn":"arn:aws:iam::XXXXXXXXXXXX:user/xxxxxxxxxxxxxxx",
    "account_id":"XXXXXXXXXXXX",
    "access_key_id":"XXXXXXXXXXXXXXXXXXX",
    "user_name":"xxxxxxxxxxxxx",
    "session_context":{
        "attributes":{
           "mfa_authenticated":"false",
           "creation_date":"2014-07-31T02:24:57Z"
       }
    },
   "invoked_by":"signin.amazonaws.com"
},
"event_time":"2014-07-31T02:25:10Z",
:
}

これに対し、jsonpathsファイルを以下の様に定義しました。

{
    "jsonpaths": [
        "$['event_version']",
        "$['user_identity']['type']",
        "$['user_identity']['principal_id']",
        "$['user_identity']['arn']",
        "$['user_identity']['account_id']",
        "$['user_identity']['access_key_id']",
        "$['user_identity']['user_name']",
        "$['user_identity']['session_context']['attributes']['mfa_authenticated']",
        "$['user_identity']['session_context']['attributes']['creation_date']",
        "$['user_identity']['invoked_by']",
        "$['event_time']"
    ]
}

テーブルはこの様に定義しました。

DROP TABLE cloudtrail_log;
CREATE TABLE cloudtrail_log (
  event_version VARCHAR(4),
  type VARCHAR(20),
  principal_id VARCHAR(25),
  arn VARCHAR(50),
  account_id VARCHAR(12),
  access_key_id VARCHAR(20),
  user_name VARCHAR(30),
  mfa_authenticated BOOLEAN,
  creation_date TIMESTAMP,
  invoked_by VARCHAR(50),
  event_time TIMESTAMP
);

COPY文はこの様になります。公式ドキュメントではファイルの圧縮(gz圧縮など)については特に言及されていませんでしたが、従来同様、JSON ファイルのCOPY処理についても圧縮済ファイルでの取り込みは可能のようです。

TRUNCATE cloudtrail_log;
COPY cloudtrail_log
FROM 's3://xxxxxxx/xxxxxxxx/cloudtrail_log.json.gz'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYYYYYYYYYYYYYYYYYYYYYY'
json 's3://xxxxxxx/xxxxxxxx/cm-jsonpaths.json'
GZIP
TIMEFORMAT 'auto';

手順2.`jsonpaths`オプションを使い、取り込むファイルの内容・順番を配列のインデックス値で指定して取り込む(JSON 配列からのロードの場合)

基本的な取り込み方法は上記で紹介した手順1と同様です。jsonpathsでの指定内容が名称を指定するものでは無く、インデックス値で項目の順番を指定するものとなるため、項目名が増えてくると結構手間が掛かりそうな手法になるような気がします。混乱を避け、手間を減らす為にもファイル生成・編集の段階で上記手順1の形式に変換した方が良いのかなという気もしますがいかがでしょうか。

[1,"Sports","MLB","Major League Baseball"]
[2,"Sports","NHL","National Hockey League"]
[3,"Sports","NFL","National Football League"]
[4,"Sports","NBA","National Basketball Association"]
[5,"Concerts","Classical","All symphony, concerto, and choir concerts"]

{
    "jsonpaths": [
        "$[0]",
        "$[1]",
        "$[2]",
        "$[3]"
    ]
}

手順3.`auto`オプションを使って取り込む(`jsonpaths`オプションは使わず、ファイル内の項目名とテーブル項目名を一致させた状態とする事で対応)

こちらの手順は上記2つとは手法が異なります。

項目名を一致させておけば、順序を気にする事無く取り込んでくれるという便利な手法ですが、この制約によって、逆に上記2つの様にJSONファイルが階層構造となっていた場合、利用出来ないというデメリットも出て来ます。

仮に、今回サンプルとして挙げたCloudTrailログをこの形で取り込むのであれば、以下の様に1行に於けるデータ構造をフラットなものにする必要があります。これなら1行に於ける項目名がそのままテーブル構造の項目名と一致する形となります。

{
"event_version":"1.01",
"type":"IAMUser",
"principal_id":"XXXXXXXXXXXXXXXXXXXX",
"arn":"arn:aws:iam::XXXXXXXXXXXX:user/xxxxxxxxxxxxxxx",
"account_id":"XXXXXXXXXXXX",
"access_key_id":"XXXXXXXXXXXXXXXXXXX",
"user_name":"xxxxxxxxxxxxx",
"mfa_authenticated":"false",
"creation_date":"2014-07-31T02:24:57Z",
"invoked_by":"signin.amazonaws.com",
"event_time":"2014-07-31T02:25:10Z",
:
}

COPY文実行の際は、JSONオプションに併せて'auto'オプションも付与する事でデータを取り込む事が出来ます。

TRUNCATE cloudtrail_log;
COPY cloudtrail_log
FROM 's3://xxxxxxx/xxxxxxxx/cloudtrail_log.json.gz'
CREDENTIALS 'aws_access_key_id=XXXXXXXXXX;aws_secret_access_key=YYYYYYYYYYYYYYYYYYYYYYYYYYYYYY'
TIMEFORMAT 'auto'
json 'auto';