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からモダンなデータレイクへマイグレートする のレポートを御覧ください。
ネストデータを含むIonデータのクエリ
正直、あまり馴染みのないIonデータですが、ザックリ解説するとJSONフォーマットの拡張で、データ型の指定やコメントの記入が可能です。キーはダブルクォーテーションで括らない点も異なります。JSONよりも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）カラムを定義する方法を御覧ください。
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 デフォルトの動作変更によりセキュリティを強化](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
