Amazon Redshift Spectrumが新たにサポートした『ネスト化されたデータに対するクエリ』を実際に試してみました

はじめに

昨年2017年11月の re:Invent2017 にて紹介された Redshift Spectrum の Nested Data のクエリ実行がついにサポートされました。Nested Data サポートは、ファイル形式が Parquet、ORC、JSON、Ion のネストデータを struct、array、map の複合データ型を使用して外部テーブルを作成できます。Ionフォーマットのクエリと、要望の多いjsonのクエリについて、クエリを実際に試してみたいと思います。

JSONやIonフォーマットの対応については以前のブログを御覧ください。

Amazon Redshift Spectrum がスカラーJSONおよびIonデータ型をサポートしたので試してみました

Nested Data サポート

Nested Data サポートは、複合データ型（map、struct、map）といった複雑なデータタイプに対して、データを変換またはロードすることなくクエリを行えます。このため既存のデータ取り込みプロセスが簡素化され、クエリの結果を得るまでの時間が短縮されます。

• S3上のネストされた半構造化データをRedshift Spectrumを用いて分析する
• Amazon Redshift Spectrumを用いてネストされた半構造化データ変換した結果を、Amazon Redshift の CTASと組み合わせて簡単なETLを可能にする
• オープンな仕様のファイルフォーマットのサポート：Parquet、ORC、JSON、Ion
• ドット表記を使用して既存のSQLを拡張する

下記の例では、クエリのパフォーマンスを向上させるため、新しいOrdersテーブルには、OrderWithItemsがネストされた列として含まれ、結合処理が排除されます

Nested Data サポートについて 【レポート】古いDWHからモダンなデータレイクへマイグレートする のレポートを御覧ください。

【レポート】古いDWHからモダンなデータレイクへマイグレートする #reinvent #ABD327

ネストデータを含むIonデータのクエリ

正直、あまり馴染みのないIonデータですが、ザックリ解説するとJSONフォーマットの拡張で、データ型の指定やコメントの記入が可能です。キーはダブルクォーテーションで括らない点も異なります。JSONよりもIonの方がヒューマンリーダブルで柔軟なフォーマットといえるでしょう。

• Amazon Ion

サンプルデータ（customers.ion）

売上データのIonフォーマットファイルをご用意しました。

{ Id: 1,
Name: {Given:"John", Family:"Smith"},
Phones: ["123-457789"],
Orders: [ {Shipdate: "2018-03-01 11:59:59", Price: 100.50}
{Shipdate: "2018-03-01 09:10:00", Price: 99.12} ]
}
{ Id: 2,
Name: {Given:"Jenny", Family:"Doe"},
Phones: ["858-8675309", "415-9876543"],
Orders: [ ]
}
{ Id: 3,
Name: {Given:"Andy", Family:"Jones"},
Phones: [ ],
Orders: [ {Shipdate: "2018-03-02 08:02:15", Price: 13.50} ]
}

外部テーブルの作成

Ionデータは、JSONフォーマットの拡張であるため、SERDEはJsonSerDeを用います。struct、array、mapの定義もJSONの定義方法と同様です。

-- DROP TABLE cm_schema.customers_ion;
CREATE EXTERNAL TABLE cm_schema.customers_ion (
id int,
name struct<given:varchar(20), family:varchar(20)>,
phones array<varchar(20)>,
orders array<struct<shipdate:varchar(20), price:double precision>>
)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://cm-data/customers_ion/'
;

IonやJSONのテーブル定義については、Amazon Redshift Spectrum がスカラーJSONおよびIonデータ型をサポートしたので試してみました が参考になるはずです。

Amazon Redshift Spectrum がスカラーJSONおよびIonデータ型をサポートしたので試してみました

struct、array、mapなど、複合型（Complex Type）についてや、定義の方法については、Amazon Athena Nested-JSONのSESログファイルを検索するの複合型（Complex Type）カラムを定義する方法を御覧ください。

Amazon Athena Nested-JSONのSESログファイルを検索する

SQL 拡張を使用してクエリを実行する

Strunct型の中のデータを取得するには、列名をパスに連結するドット表記を使用します。C言語のStrunct型のメンバ変数の値を取得するのと同じです。

cmdb=# SELECT c.id, c.name.given, c.name.family
FROM cm_schema.customers_ion c;
id | given | family
----+-------+--------
1 | John | Smith
2 | Jenny | Doe
3 | Andy | Jones
(3 rows)

ネストデータを含むJSONデータのクエリ

以降は問い合わせや要望が特に多いJSONデータを試してみたいと思います。

サンプルデータ（customers_minify.json）

先程のIonのデータをjsonフォーマットで用意しました。jsonデータはminify形式（１レコード１行形式）で作成しています。

{"Id":1,"Name":{"Given":"John","Family":"Smith"},"Phones":["123-457789"],"Orders":[{"Shipdate":"2018-03-01 11:59:59","Price":100.50},{"Shipdate":"2018-03-01 09:10:00","Price":99.12}]}
{"Id":2,"Name":{"Given":"Jenny","Family":"Doe"},"Phones":["858-8675309","415-9876543"],"Orders":[]}
{"Id":3,"Name":{"Given":"Andy","Family":"Jones"},"Phones":[],"Orders":[{"Shipdate":"2018-03-02 08:02:15","Price":13.50}]}

外部テーブル定義

jsonデータのテーブル定義は、Ionフォーマットのテーブル定義と変わりません。

-- DROP TABLE cm_schema.customers_minify;
CREATE EXTERNAL TABLE cm_schema.customers_minify (
id int,
name struct<given:varchar(20), family:varchar(20)>,
phones array<varchar(20)>,
orders array<struct<shipdate:varchar(20), price:double precision>>
)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://cm-data/customers_minify/'
;

拡張 1: Structs 列へのアクセス

JSONデータのクエリの実行結果は、Ionフォーマットのクエリと変わりません。

cmdb=# SELECT c.id, c.name.given, c.name.family
FROM cm_schema.customers_minify c;
id | given | family
----+-------+--------
1 | John | Smith
2 | Jenny | Doe
3 | Andy | Jones
(3 rows)

拡張 2: FROM 句の配列範囲

array 列 (および拡張の map 列) からデータを取り出すには、テーブル名ではなく、FROM 句に array 列を指定します。この拡張は、メインクエリの FROM 句だけでなく、サブクエリの FROM 句にも適用されます。array 要素を位置 (例: c.orders[0]) で参照することはできません。

次のユースケースで説明するように、arrays を joins と組み合わせることにより、さまざまな種類のネスト解除を行うことができます。array 列は直接指定できないので、メインクエリの FROM 句に指定したテーブルのエイリアスにドット表記で指定ことでネスト解除します。

INNER JOIN を使用したネスト解除

FROM 句の SQL 拡張 c.orders o は、cm_schema.customers_minifyのエイリアス c を利用して、ordersの配列をテーブルとして定義しています。

# SELECT c.id, o.shipdate
# FROM cm_schema.customers_minify c, c.orders o ;
id | shipdate
----+---------------------
1 | 2018-03-01 11:59:59
1 | 2018-03-01 09:10:00
3 | 2018-03-02 08:02:15
(3 rows)

JOIN を実行する FROM 句と置き換え、次のようにクエリを書き換えることもできます。

# SELECT c.id, o.shipdate
# FROM cm_schema.customers_minify c INNER JOIN c.orders o ON true
cmawsteamdb-# ;
id | shipdate
----+---------------------
1 | 2018-03-01 11:59:59
1 | 2018-03-01 09:10:00
3 | 2018-03-02 08:02:15
(3 rows)

LEFT JOIN を使用したネスト解除

customersのすべてのレコードが出力できます。

# SELECT c.id, c.name.given, c.name.family, o.shipdate, o.price
# FROM cm_schema.customers_minify c LEFT JOIN c.orders o ON true;
id | given | family | shipdate | price
----+-------+--------+---------------------+-------
1 | John | Smith | 2018-03-01 11:59:59 | 100.5
1 | John | Smith | 2018-03-01 09:10:00 | 99.12
2 | Jenny | Doe | |
3 | Andy | Jones | 2018-03-02 08:02:15 | 13.5
(4 rows)

拡張 3: エイリアスを使用して Scalars の配列に直接アクセスする

FROM 句のエイリアス p がスカラーの配列の値を単に p として参照します。

# SELECT c.name.given, c.name.family, p AS phone
# FROM cm_schema.customers_minify c LEFT JOIN c.phones p ON true;
given | family | phone
-------+--------+-------------
John | Smith | 123-457789
Jenny | Doe | 858-8675309
Jenny | Doe | 415-9876543
Andy | Jones |
(4 rows)

拡張 4: Maps 要素へのアクセス

key 列および value 列を持つ struct 型を含む array として map データ型を扱う例です。下記のようにphoneがmapのデータがあったとします。

{"Id":1,"Name":{"Given":"John","Family":"Smith"},"Phones":{"mobile":"123-457789"},"Orders":[{"Shipdate":"2018-03-01 11:59:59","Price":100.50},{"Shipdate":"2018-03-01 09:10:00","Price":99.12}]}
{"Id":2,"Name":{"Given":"Jenny","Family":"Doe"},"Phones":{"fax":"858-8675309","mobile":"415-9876543"},"Orders":[]}
{"Id":3,"Name":{"Given":"Andy","Family":"Jones"},"Phones":{},"Orders":[{"Shipdate":"2018-03-02 08:02:15","Price":13.50}]}

phonesをarrayではなく、mapとして定義します。

-- DROP TABLE cm_schema.customers_minify_map;
CREATE EXTERNAL TABLE cm_schema.customers_minify_map (
id int,
name struct<given:varchar(20), family:varchar(20)>,
phones map<varchar(20), varchar(20)>,
orders array<struct<shipdate:varchar(20), price:double precision>>
)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://cm-data/customers_minify_map/'
;

実験

prettify形式のjsonデータだとどうなるか？

同じJSONデータをインデントして人が読みやすくしたprettify形式（customers_minify.json）を作成してクエリするとどうなるかを確認しました。

クエリの実行

内部エラーが発生して、クエリが失敗しました。INTで定義したカラムがエラーになっています。

cmdb=# SELECT c.id, c.name.given, c.name.family
FROM cm_schema.customers_prettify c;
ERROR: S3 Query Exception (Fetch)
DETAIL:
-----------------------------------------------
error: S3 Query Exception (Fetch)
code: 15001
context: Task failed due to an internal error. In file https://s3.amazonaws.com/cm-data/customers_prettify/customers_prettify.json declared column type INT for column STRING is incompatible

query: 949030
location: dory_util.cpp:724
process: query0_123_949030 [pid=21901]
-----------------------------------------------

外部テーブル定義（修正）

id列をintからvarchar(20)に変更したテーブルを再作成しました。

-- DROP TABLE cm_schema.customers_prettify;
CREATE EXTERNAL TABLE cm_schema.customers_prettify (
id varchar(20),
name struct<given:varchar(20), family:varchar(20)>,
phones array<varchar(20)>,
orders array<struct<shipdate:timestamp, price:double precision>>
)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://cm-data/customers_prettify/'
;

クエリの実行（修正後の再実行）

idが文字列型になりましたが、クエリが正常に実行できました。

cmdb=# SELECT c.id, c.name.given, c.name.family
FROM cm_schema.customers_prettify c;
id | given | family
----+-------+--------
1 | John | Smith
2 | Jenny | Doe
3 | Andy | Jones
(3 rows)

一般的なjsonデータはminify形式の場合がほとんどなので、あまり問題にならないと思います。jsonデータはminify形式とするのが望ましいでしょう。

ネストしたデータのクエリをビューに登録できるか？

複合型のデータタイプや深くネストしたクエリを毎回書くのは大変なので、これをビューにしたいと誰もが思うはずです。

下記の通り、ビューとして登録し、ビューを実行できることを確認できました。外部テーブルに対するビューになるため、Late-Binding ビュー(WITH NO SCHEMA BINDING)の指定を忘れないでください。

# CREATE VIEW shipdate_list AS (
# SELECT c.id, o.shipdate
# FROM cm_schema.customers_minify c, c.orders o
# ) WITH NO SCHEMA BINDING;
CREATE VIEW

# SELECT * FROM shipdate_list;
id | shipdate
----+---------------------
1 | 2018-03-01 11:59:59
1 | 2018-03-01 09:10:00
3 | 2018-03-02 08:02:15
(3 rows)

登録した外部テーブルは、Amazon Athena からどう見えるのか？

外部テーブルはGlueデータカタログに登録されますので、Amazon Athenaからテーブル名が登録されていることが確認できます。

• Ionのテーブル： AthenaはIonフォーマットをサポートしていませんのでエラーになります。
• minify形式のJSONファイルは、Athenaからもクエリ可能です。
• 非minify形式（prettify形式）のJSONファイルは、エラーになり参照できません。
• varchar(20)で定義したカラムが、Glueデータカタログで確認したところ、varchar(65535)と定義されていました

ネスト化されたデータに対するクエリのユースケース

ネストデータ/非構造化データのETL

従来だとAWS GlueなどでJSONをフラット化したファイルを事前に変換する必要がありました。複合データ型（map、struct、map）を含む外部テーブルからデータを取り込むには、CREATE TABLE AS ステートメントを使用します。以下のクエリの結果をAmazon Redshift テーブルの CustomerPhones にそのデータを保存します。

# CREATE TABLE customer_phones AS
# SELECT c.name.given, c.name.family, p AS phone
# FROM cm_schema.customers_minify c LEFT JOIN c.phones p ON true;
SELECT

# SELECT * FROM customer_phones;
given | family | phone
-------+--------+-------------
Andy | Jones |
John | Smith | 123-457789
Jenny | Doe | 858-8675309
Jenny | Doe | 415-9876543
(4 rows)

サブクエリを使用したネストデータの集約

ネストデータを集約するにはサブクエリを使用します。

# SELECT c.name.given, c.name.family, (SELECT COUNT(*) FROM c.orders o) AS ordercount
# FROM cm_schema.customers_minify c;
given | family | ordercount
-------+--------+------------
John | Smith | 2
Jenny | Doe | 0
Andy | Jones | 1
(3 rows)

ネストデータの制限事項

ネストデータには以下の制限が適用されます。

• 配列に含めることができるのは、スカラー型または struct 型のみです。Array 型に、array 型または map 型を含めることはできません。
• Redshift Spectrum の外部データのみ複合データがサポートされています。（Redshiftのテーブルは複合データをサポートしない）
• クエリおよびサブクエリの結果列は、スカラー型である必要があります。
• OUTER JOIN 式によって、ネスト化されたテーブルが参照される場合は、テーブルとそのネスト化されたstruct (およびmap) でのみ参照されます。OUTER JOIN 式でネスト化されたテーブルを参照しない場合は、任意の数のネスト化されていないテーブルを参照することができます。
• サブクエリの FROM 句でネスト化されたテーブルが参照されている場合は、その他のテーブルを参照することはできません。
• サブクエリが、親を参照するネスト化されたテーブルに依存する場合、親は FROM 句でのみ使用できます。SELECT 句や WHERE 句など、他の句でクエリを使用することはできません。たとえば、次のクエリは実行されません。

# SELECT c.name.given
# FROM cm_schema.customers_minify c
# WHERE (SELECT COUNT(c.id) FROM c.phones p WHERE p LIKE '858%') > 1;
ERROR: aggregates not allowed in WHERE clause

• FROM 句以外の場所にあるネストされたデータにアクセスするサブクエリは、単一の値を返す必要があります。
• (NOT) IN と (NOT) EXISTS はサポートされていません。
• ネストされたすべてのタイプの最大ネスト数は 100 です。この制限は、すべてのファイル形式 (Parquet、ORC、Ion、JSON) に適用されます。

最後に

timestamp型については、色々な形式やテーブル定義を試してみましたが、timestamp型として認識できなかったため、今回は文字列型（varchar(20)）として検証しています。

今回は、IonとJSONフォーマットについて実際に試してみました。特に問い合わせの多かったネストしたJSONについては、マニュアルにもあまり記載がないので一通り試しています。実際に動くサンプルとして活用できるはずです。

Amazon Redshift Spectrumは、prettify形式のJSONファイルもデータ型を文字列型に変更することでクエリできましたが、Amazon AthenaやGlueとのデータを共有することを考えるとminify形式にすることを推奨します。minify形式のJSONファイルであれば、AWS Glueクローラを利用してテーブル定義できるということなので、テーブル定義をいきなり手で書くのではなく、Glueクローラで雛形を作り、Glueコンソールでテーブル定義（特にデータ型）を修正するのが簡単な方法だと思います。

参考

• 公式マニュアル：チュートリアル: Amazon Redshift Spectrum を使用したネストデータのクエリ