Amazon Redshiftは非常に魅力的なクラウド・データウェアハウスですが、データをロードするところに苦労する部分が多く、我々FlyDataはデータインテグレーションサービスとしてRedshiftに関するWebサービスを提供しています。その中でも特に、JSONデータフォーマットのサポートがRedshiftユーザに好評で、FlyDataでも積極的に開発しています。そのJSONサポートが、ついにAWSによってRedshiftに提供され始めたということで、詳細を調べてみました。

まずその前に、JSONの基本から見ていきましょう。JSONは急速に、アプリケーションとサーバ間のデータ交換を行うためのデファクトフォーマットとなりつつあります。つまり、簡単かつ柔軟に扱えるその特性により、JSONログを生成・分析することが一般的になりつつあるのです。

より多くのデータがJSONフォーマットで生成・保存・交換されるにつれ、JSONフォーマットをデータウェアハウスへ直接ロードすることがより求められるようになりました。なぜなら、それにより他のフォーマットへデータを変換するリソースを節約するだけでなく、より価値のあるもの、つまりあなたの貴重な時間をもっと有意義に使うことができるようになるからです。あなたのデータウェアハウスにより最新のデータをアップロードし、すぐに分析などで役立てることができる、ということです。

Uploading JSON to Amazon Redshift

今回、Amazon RedshiftはCOPYコマンドによってJSONフォーマットのロードをサポートしました。それは、まずRedshiftにテーブルを作成し、その後Amazon RedshiftのカラムとJSONのキーをマッピングする“⁠JSONPaths⁠”ファイルを作成することで可能となります。

これがどのように実行できるかを理解するためには、試してみるのが一番です。まず、RedshiftにアップロードしたいJSONデータを見ていきます。JSONファイルは以下のようになっています。

# users_test.json
{
  "userid": 1,
  "name": "George Washington"
  "address": [ "Westmoreland", "VA" ],
  "age": 282
}
{
  "userid": 2,
  "name": "John Adams",
  "address": [ "Braintree", "MA" ],
  "age": 279
}

このJSONデータをAmazon Redshiftにアップロードするために、まずはこのJSONデータをロードするRedshiftテーブルを作成する必要があります（※⁠）⁠。

※）

もしまだRedshiftクラスタを立ち上げていない場合、または費用をかけたくない場合は、こちらからFlyDataが提供する無料のテスト用Redshift環境にアクセスすることもできます。

Redshiftテーブルの作成

Redshiftクラスタへのアクセスができるようになったら、保存したいJSONデータに応じてusers_testテーブルを作成しましょう。

create table users_test (
        userid INTEGER not null,
        name VARCHAR(100),
        city VARCHAR(100),
        state CHAR(2),
        age INTEGER,
        primary key(userid)
);

ここでは、JSONデータの値を格納するための5つのカラムを持つusers_testテーブルを作成しています。

JSONPathsファイルの作成

これでRedshiftにデータをロードするためのテーブルが用意できました。

次に JSONPathsファイルを作成します。これはRedshiftのカラムとJSONデータをマッピングするものです。

今回は、以下のようなJSONPathsファイルを用意します。

# users_test_jsonpaths.json
{
  "jsonpaths":
  [
    "$['userid']", 
    "$['name']",
    "$['address'][0]",
    "$['address'][1]",
    "$['age']"
  ]
}

このJSONファイルには最上位に"jsonpaths"というキーが含まれています。このキーの値に対応するのが、Redshiftテーブルのカラムにマップするための文字列の行列です。

“$⁠”がついた要素がJSONオブジェクトを表します。このような角カッコによる表記で、Redshiftテーブルにあるカラムに割り当てるキーの順番を指定します。この行列は順番が大事で、Redshiftテーブルのカラム順である必要があります。これにより、それぞれの値がCOPYコマンドによってどのように割り当てられるかを指示します。

今回は、カラムの順番は以下のようになります（\d users_testをRedshift上で実行しています⁠）⁠。

 Column |          Type          | Modifiers 
--------+------------------------+-----------
 userid | integer                | not null
 name   | character varying(100) | 
 city   | character varying(100) | 
 state  | character(2)           | 
 age    | integer                | 
Indexes:
    "users_test_pkey" PRIMARY KEY, btree (userid)

対応するJSONPathsは次のようになります。

# users_test_jsonpaths.json
{
  "jsonpaths":
  [
    "$['userid']",
    "$['name']",
    "$['address'][0]",
    "$['address'][1]",
    "$['age']" 
  ]
}

では、このファイルとJSONデータファイルをS3バケット上に置きましょう。

COPYコマンドの実行

これでCOPYコマンドを実行する準備ができました。

copy users_test from 's3://<your_s3_bucket>/users_test.json'
credentials 'aws_access_key_id=<your_access_key>; aws_secret_access_key=<your_secret_access_key>'
JSON as 's3://<your_s3_bucket>/users_test_jsonpaths.json';

これでRedshiftに対して、このJSONPathsを使ってJSONデータを正しいRedshiftテーブルのカラムにマッピングし、ロードすることができました！

# (running `select * from users_test; `)
userid  |       name        |     city     | state | age 
--------+-------------------+--------------+-------+-----
      1 | George Washington | Westmoreland | VA    | 282
      2 | John Adams        | Braintree    | MA    | 279

まとめ

ここまで見てきたように、上記の方法でJSONデータをRedshiftにアップロードすることができます。問題は、JSONPathsファイル内のキーの順番をカラムと同じようにしなといけないということで、その点は要注意です。JSONPathsファイルに定義されていないJSONキーバリューペアは、COPYプロセスで無視されます。

データがRedshiftにロードされると、このデータを元にRedshiftの高速クエリの実行が可能になります。

個人的な感想としては、JSONデータロードの入り口としては必要十分な機能を準備してきたなという印象です。ただ、やはりこれでJSONデータロードのすべての問題が解決したわけではなく、特にJSON形式なのにフォーマットをJSONPathsという要素順番依存のファイルで事前定義しないといけない点が、JSONの持つ柔軟性を減らしているように感じました。

また、FlyDataが対応しているスキーマ生成・変更（自動テーブル・カラム作成等）には対応しておらず、データロード時のエラーハンドリングやスケーリングにも引き続き考慮が必要です。FlyDataとしては、このJSONPathsファイルを自動生成したり、その部分をサービス内部で処理したりという取り込みを行うことによって、引き続き柔軟にJSONをサポートするより高度な機能を提供していきたいと思っています。

この記事が参考になれば幸いです。ではまた！

［参考記事］

COPY from JSON format -Amazon Redshift Database Developer Guide