RedshiftでJSONファイルをCOPYする時にはキー名の大文字小文字に注意！

はじめに

こんにちは。DI部の大高です。

先日、JSONファイルのCOPY時にうまくCOPYできないケースに遭遇しました。私はこのケースを初めて知ったのでご紹介したいと思います。

主旨としてはタイトルの通り「JSONのキー名の大文字小文字の違いによってCOPYできないことがある」という話になります。

やりたいこと

S3に配置してあるJSON（JSONL）ファイルをRedshiftのテーブルのCOPY文でロードしたいと思います。

なお、やり方に関しては以下のヘルプが参考になります。

COPY の例 - Amazon Redshift

では、早速やっていきます。

ロード先のテーブル

Redshift上に以下のように事前にテーブルを作成します。（スキーマ ootaka_sandbox は事前に作成したものです）

CREATE TABLE ootaka_sandbox.users(
    Id INTEGER,
    Username NVARCHAR(512),
    PRIMARY KEY(Id)
)
DISTSTYLE EVEN
;

JSONファイル

JSONファイルは以下の２ファイルを用意します。１つ目の users_camel.json はキー名の先頭が大文字のもの（テーブル作成時と同じ）、２つ目の users_lower.json はキー名がすべて小文字のものです。

{"Id": 1, "Username": "Sougo"}
{"Id": 2, "Username": "Geiz"}
{"Id": 3, "Username": "Woz"}

{"id": 1, "username": "Sougo"}
{"id": 2, "username": "Geiz"}
{"id": 3, "username": "Woz"}

用意したら、Resdhitから参照できるように設定済みのS3バケットにアップロードしておきます。

COPYしてみる

用意したJSONを利用してCOPYしてみます。

users_camel.json

COPY文は以下のようになります。

COPY ootaka_sandbox.users
FROM 's3://ootaka_sandbox/json/users_camel.json'
IAM_ROLE 'arn:aws:iam::999999999999:role/redshift-role'
FORMAT AS JSON 'auto';

実行するとエラーが発生しました。

XX000: Load into table 'users' failed.  Check 'stl_load_errors' system table for details.

stl_load_errors テーブルを確認してみます。

SELECT
    filename
  , line_number
  , colname
  , position
  , raw_line
  , err_reason
FROM
  stl_load_errors
ORDER BY
  starttime DESC
LIMIT 1
;

すると、 err_reason カラムには、 Missing data for not-null field が入っていました。日本語だと NOT NULL として指定されたフィールドにデータが含まれていませんでした。 です。

すなわち、プライマリキーとしてNOT NULLにしていた Id カラムに値が無いと言っています。

原因は、JSONのキー名の大文字小文字です。Reshiftでテーブル作成をすると、Redshift上のカラム名はすべて小文字となるのでCREATE TABLE文でカラム名を Id と指定しても、実際には id となります。一方で、JSONのキー名は Id なので、認識してくれなかったという理由です。

users_camel.json

では、キー名が小文字のもので再チャレンジしてみます。COPY文は以下です。

COPY ootaka_sandbox.users
FROM 's3://ootaka_sandbox/json/users_lower.json'
IAM_ROLE 'arn:aws:iam::999999999999:role/redshift-role'
FORMAT AS JSON 'auto';

こちらはエラーが起きずに成功しました。

回避策

回避策の1つとしてJSONPathsファイルの利用があります。以下のようなファイルを用意して、カラムのマッピングを行います。

{
    "jsonpaths": [
        "$['Id']",
        "$['Username']"
    ]
}

このファイルをS3に配置して以下のようにロードします。

COPY ootaka_sandbox.users
FROM 's3://ootaka_sandbox/json/users_camel.json'
IAM_ROLE 'arn:aws:iam::999999999999:role/redshift-role'
JSON 's3://ootaka_sandbox/json/users_jsonpath.json';

こうすることで、先頭が大文字のキー名を各カラムにマッピングすることができ、問題なくCOPYすることができました。

まとめ

以上、JSONファイルのCOPYについてでした。JSONファイルのキー名に大文字小文字が利用されているケースは割りとあるかと思いますので、うまくCOPYできない場合には思い出して頂ければ幸いです。それでは！