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

インサイトウォッチ

はじめに

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

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フォーマットファイルをご用意しました。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
{ 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の定義方法と同様です。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 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データ型をサポートしたので試してみました が参考になるはずです。

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

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

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

1
2
3
4
5
6
7
8
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形式（１レコード１行形式）で作成しています。

1
2
3
{"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フォーマットのテーブル定義と変わりません。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 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フォーマットのクエリと変わりません。

1
2
3
4
5
6
7
8
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の配列をテーブルとして定義しています。

1
2
3
4
5
6
7
8
# 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 句と置き換え、次のようにクエリを書き換えることもできます。

1
2
3
4
5
6
7
8
9
# 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のすべてのレコードが出力できます。

1
2
3
4
5
6
7
8
9
# 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 として参照します。

1
2
3
4
5
6
7
8
9
# 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のデータがあったとします。

1
2
3
{"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として定義します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 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で定義したカラムがエラーになっています。

1
2
3
4
5
6
7
8
9
10
11
12
13
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)に変更したテーブルを再作成しました。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 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が文字列型になりましたが、クエリが正常に実行できました。

1
2
3
4
5
6
7
8
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)の指定を忘れないでください。

1
2
3
4
5
6
7
8
9
10
11
12
13
# 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 にそのデータを保存します。

1
2
3
4
5
6
7
8
9
10
11
12
13
# 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)

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

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

1
2
3
4
5
6
7
8
# 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 句など、他の句でクエリを使用することはできません。たとえば、次のクエリは実行されません。
1
2
3
4
# 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コンソールでテーブル定義（特にデータ型）を修正するのが簡単な方法だと思います。

参考

新メンバーズ

コメントは受け付けていません。