JSONデータをRedshiftに取り込みたい

荒木智仁

2025.03.31

データ事業本部の荒木です。

JSONデータをRedshiftに取り込む際に、取り込み方や取り込み後のデータの展開方法などに色々な方法があったのでまとめたいと思います。

準備

JSONデータとして以下のようなJSONL形式のサンプルデータを用意しました。

{"key1":"value1","key2":"value2"} -- 階層構造がないデータ
{"key1":"value1","key3":{"key4":"value3","key5":"value4"}} -- 階層構造があるデータ
{"key1":"value1","key6":["element1","element2","element3"]} -- 配列を含むデータ

データを取り込むテーブルとして以下の2つのテーブルを準備しました。

table1はSUPER型で定義した1つのカラムのみのテーブルです。
RedshiftのSUPER型カラムは、JSONなどの半構造化データをオブジェクトとして取り込むことができます。

create table sample.table1(
json super
);

table2はJSONデータ内の各Keyをそれぞれのカラムにしたテーブルです。
このときJSONデータ内の各Key名とテーブルのカラム名は同一にしています。
これは詳しくは後述しますが、JSONデータ内の各Key名とテーブルのカラム名が同じ場合、COPY処理のオプションによって、各Keyの値をKeyと同じカラム名にデータを取り込むことができます。

create table cm_araki.json_key(
key1 varchar(100),
key2 varchar(100),
key3 varchar(100),
key4 varchar(100),
key5 varchar(100),
key6 varchar(100),
key6_1 varchar(100),
key6_2 varchar(100),
key6_3 varchar(100),
);

COPYコマンドについて

JSONファイルの取り込みで使用するコマンドは以下です。

COPY cm_araki.json_key
FROM '{JSONファイルを配置したS3のパス}'
IAM_ROLE '{S3へのアクセス権限を持ったRedshift用IAMロール}'
json '{オプション}'
;

上記のjsonパラメータにはいくつかのオプションがあり、どれを選ぶかによってデータの取り込まれ方が違ってきます。

auto

オプションでautoを指定すると自動でマッピングされるため、JSONデータ内のKey名と同じカラム名にデータを展開して取り込むことができます。
JSONデータに存在してもテーブルに同名のカラムが存在しない場合はNULLとして取り込まれます。

このオプションは階層構造のないJSONデータであれば、Key名とカラム名が同じである限り自動マッピングで取り込むことができますが、階層構造や配列を含むデータなどは値を文字列として取り込むことになります。

jsonpaths

オプションjsonpathsは、JSONのどのデータをどのカラムに取り込むかをマッピングした設定ファイルを作成することでデータを展開して取り込むことができます。

今回は以下のファイルを準備しました。
階層構造になっているデータは、最下層のKeyの値を取得し配列のデータは全ての要素をそれぞれ別のカラムに展開します。

このオプションは設定ファイルの一番上データが、取り込むテーブルの一番左のカラムに該当するためどのJSONデータをどのカラムに取り込むかを指定することができます。

{
    "jsonpaths": [
       "$['key2']",
       "$['key1']",
       "$['key1']",
       "$['key3']['key4']",
       "$['key3']['key5']",
       "$['key1']",
       "$['key6'][0]",
       "$['key6'][1]",
       "$['key6'][2]",
    ]
}

autoで取り込んだ場合と取り込んだデータが異なることがわかるように上記のようなファイルを作成しました。
作成したファイルをS3の任意の場所に格納し、ファイルのフルパスをCOPYコマンドで指定します。

noshred

オプションnoshredは、JSONデータを取り込む際にカラム毎に分解せずにオブジェクトのまま取り込みたい場合に指定することで、JSONデータのまま取り込むことができます。

このオプションはSUPER型でカラムを作成したテーブルにオブジェクトのままJSONを取り込む際に使用します。

取り込み結果

auto

COPY cm_araki.json_key
FROM '{JSONファイルを配置したS3のパス}'
IAM_ROLE '{S3へのアクセス権限を持ったRedshift用IAMロール}'
json 'auto'
;

key1	key2	key3	key6
1_value1	value2
2_value1		{"key4":"value3","key5":"value4"}
3_value1			["element1","element2","element3"]

jsonpaths

COPY cm_araki.json_key
FROM '{JSONファイルを配置したS3のパス}'
IAM_ROLE '{S3へのアクセス権限を持ったRedshift用IAMロール}'
json '{jsonpathsファイルを配置したS3のパス}'
;

key1	key2	key3	key4	key5	key6	key6_1	key6_2	key6_3
value2	1_value1	1_value1			1_value1
	2_value1	2_value1	value3	value4	2_value1
	3_value1	3_value1			3_value1	element1	element2	element3

noshred

オブジェクトのまま取り込みたいのでnoshredオプションでjson_dataにCOPYします。

COPY cm_araki.json_data
FROM '{JSONファイルを配置したS3のパス}'
IAM_ROLE '{S3へのアクセス権限を持ったRedshift用IAMロール}'
json 'noshred'
;

json
{"key1":"1_value1","key2":"value2"}
{"key1":"2_value1","key3":{"key4":"value3","key5":"value4"}}
{"key1":"3_value1","key6":["element1","element2","element3"]}

ちなみに取り込んだオブジェクトのデータは以下のようなSQLでデータを展開することもできます。

SELECT
JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(json), 'key1') as key1,
JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(json), 'key2') as key2,
JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(json), 'key3', 'key4') as key4,
JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(json), 'key3', 'key5') as key5,
JSON_EXTRACT_ARRAY_ELEMENT_TEXT(JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(json), 'key6'),0) as key6_1,
JSON_EXTRACT_ARRAY_ELEMENT_TEXT(JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(json), 'key6'),1) as key6_2,
JSON_EXTRACT_ARRAY_ELEMENT_TEXT(JSON_EXTRACT_PATH_TEXT(JSON_SERIALIZE(json), 'key6'),2) as key6_3
FROM cm_araki.json_dat;