この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
昨年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 clauseFROM句以外の場所にあるネストされたデータにアクセスするサブクエリは、単一の値を返す必要があります。(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コンソールでテーブル定義（特にデータ型）を修正するのが簡単な方法だと思います。
10
