Amazon RedshiftにJSONフォーマットのデータをインポートする
Amazon Redshiftに於けるデータのインポートでは、代表的なところだとCSV(カンマ区切り)やTSV(タブ区切り)形式のものを取り込む事が多いかと思いますが、JSON形式のファイルインポートにも実は対応しています。
そこで当エントリでは、Amazon RedshiftへのJSONデータ取り込みについて紹介・実践してみたいと思います。(※上記リストにあるように日本語ドキュメントも整備されてますので、ポイントだけ掻い摘む形で)
目次
- JSONとは
- Amazon RedshiftへのJSONファイルの取り込み方法
- 手順1.jsonpathsオプションを使い、取り込むファイルの内容・順番を テーブル項目名と一致した名前の指定で取り込む場合
- 手順2.jsonpathsオプションを使い、取り込むファイルの内容・順番を 配列のインデックス値で指定して取り込む(JSON 配列からのロードの場合)
- 手順3.autoオプションを使って取り込む(jsonpathsオプションは使わず、 ファイル内の項目名とテーブル項目名を一致させた状態とする事で対応)
- まとめ
JSONとは
そもそもJSONとは何ぞや？という点については、Wikipediaを御参照ください。
JSON（ジェイソン、JavaScript Object Notation）は軽量なデータ記述言語の1つである。 構文はJavaScriptにおけるオブジェクトの表記法をベースとしているが、 JSONはJavaScript専用のデータ形式では決してなく、様々なソフトウェアやプログラミング言語間における データの受け渡しに使えるよう設計されている。
また、Amazon Redshiftに於けるJSONファイルの仕様についても公式ドキュメントに展開されていますのでそちらを御参照ください。
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ファイルの項目名はキャメルケース(複合語をひと綴りとして、要素語の最初を大文字で書き表す)形となっているので、スネークケース(アンダースコア（_）を区切記号として単語をつなげる)形に一律変更するルールで進めてみたいと思います。
{"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';
まとめ
以上、Amazon Redshiftに於けるJSONファイルの取り込み方法についてのご紹介でした。局面によってはCSV/TSVだけで無く、今回ご紹介したようなJSON形式のデータも取り込む必要が出て来るでしょう。その場合、オプション指定をする事で柔軟に対応出来る事が分かりました。
また、Amazon Redshiftには個別にJSON系の内容を扱う関数も用意されています。今回の取り込みと併せてこのJSON関数も使う事で、JSONデータの取り扱いがより便利に・効率的になるのではないでしょうか。こちらからは以上です。
![[アップデート] Amazon Redshift デフォルトの動作変更によりセキュリティを強化](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
