チュートリアル: Amazon Redshift Spectrum を使用したネストデータのクエリ

このチュートリアルでは、Redshift Spectrum を使用してネストデータをクエリする方法を示します。ネストデータは、ネストされたフィールドを含むデータです。ネストされたフィールドは、配列、構造体、オブジェクトなど、単一のエンティティとして結合されるフィールドです。

概要

Amazon Redshift Spectrum では、ファイル形式が Parquet、ORC、JSON、Ion のネストデータのクエリ実行をサポートしています。Redshift Spectrum は、外部テーブルを使用してデータにアクセスします。struct、array、map などの複合データ型を使用して外部テーブルを作成することもできます。

たとえば、customers という名前のフォルダ内のデータファイルに、Amazon S3 の以下のデータが含まれるとします。単一のルート要素はありませんが、このサンプルデータの各 JSON オブジェクトはテーブルの行を表します。

{"id": 1,
 "name": {"given": "John", "family": "Smith"},
 "phones": ["123-457789"],
 "orders": [{"shipdate": "2018-03-01T11:59:59.000Z", "price": 100.50},
            {"shipdate": "2018-03-01T09:10:00.000Z", "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-02T08:02:15.000Z", "price": 13.50}]
}  

Amazon Redshift Spectrum を使用して、ファイル内のネストデータにクエリを実行できます。以下のチュートリアルでは、Apache Parquet データでの実行方法を紹介します。

前提条件

Redshift Spectrum をまだ使用していない場合は、Amazon Redshift Spectrum の開始方法 のステップに従って行います。

外部スキーマを作成するには、次のコマンドの IAM ロール ARN を、「IAM ロールを作成する」で作成したロール ARN に置き換えます。次に、SQL クライアントでコマンドを実行します。

create external schema spectrum 
from data catalog 
database 'myspectrum_db' 
iam_role 'arn:aws:iam::123456789012:role/myspectrum_role'
create external database if not exists;

ステップ 1: ネストデータを含む外部テーブルを作成する

ソースデータは、Amazon S3 からダウンロードして表示できます。

このチュートリアル用に外部テーブルを作成するには、次のコマンドを実行します。

CREATE EXTERNAL TABLE spectrum.customers (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones array<varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';     

前述の例で、外部テーブル (spectrum.customers) では、データ型 struct および array を使用して、ネストデータを含む列を定義しています。Amazon Redshift Spectrum では、ファイル形式が Parquet、ORC、JSON、Ion のネストデータのクエリ実行をサポートしています。STORED AS パラメータは、Apache Parquet ファイルを表す PARQUET です。LOCATION パラメータは、ネストデータまたはファイルを含む Amazon S3 フォルダを参照する必要があります。詳細については、「CREATE EXTERNAL TABLE」を参照してください。

データ型 array および struct は、任意のレベルでネスト化することができます。たとえば、次の例で示すように、toparray という名前の列を定義できます。

toparray array<struct<nestedarray:
         array<struct<morenestedarray: 
         array<string>>>>>

また、次の例の struct で示すように、データ型 x をネスト化することもできます。

x struct<a: string,
         b: struct<c: integer,
                   d: struct<e: string>
                  >
        >

ステップ 2: SQL 拡張を使用して Amazon S3 のネストデータにクエリを実行する

Redshift Spectrum では、Amazon Redshift SQL 構文に拡張することで、複合型 (array、map、struct) のクエリをサポートしています。

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

struct 列からデータを抽出するには、フィールド名をパスに連結するドット表記を使用します。たとえば、次のクエリでは、指定された顧客の姓名が返ります。名にアクセスするには、長いパス c.name.given を使用します。姓にアクセスするには、長いパス c.name.family を使用します。

SELECT c.id, c.name.given, c.name.family
FROM   spectrum.customers c;

前述のクエリでは、次のデータが返ります。

id | given | family
---|-------|-------
1  | John  | Smith
2  | Jenny | Doe
3  | Andy  | Jones
(3 rows)

struct は、別の struct 列にすることができます。つまり、別の struct の列を任意のレベルで使用できます。このように深くネストされた struct 列にアクセスするパスは、任意的に長くすることができます。たとえば、次の例の x 列については、定義を参照してください。

x struct<a: string,
         b: struct<c: integer, 
                      d: struct<e: string>
                  >
        >

e のデータに x.b.d.e としてアクセスできます。

拡張 2: FROM 句の配列範囲

array 列 (および拡張の map 列) からデータを抽出するには、テーブル名ではなく、array 句の FROM 列を指定します。この拡張は、メインクエリの FROM 句だけでなく、サブクエリの FROM 句にも適用されます。

array 要素を位置 (例: c.orders[0]) で参照できます (プレビュー)。

次のユースケースで説明するように、arrays を joins と組み合わせることにより、さまざまな種類のネスト解除を行うことができます。

内部結合を使用したネスト解除

次のクエリでは、注文を含む顧客の顧客 ID と出荷日を選択します。FROM 句の SQL 拡張 c.orders o は、エイリアス c によって異なります。

SELECT c.id, o.shipdate
FROM   spectrum.customers c, c.orders o 

注文を含む顧客 c ごとに、FROM 句によって、顧客 o の注文 c 単位で返ります。その行は、顧客行 c と注文行 o を組み合わせたものです。次に、SELECT 句を使用して、c.id および o.shipdate を維持します。結果は次のとおりです。

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) 

エイリアス c では顧客フィールド、o では注文フィールドにアクセスすることができます。

セマンティクスは、標準的な SQL と似ています。FROM 句は、次のネステッドループの実行と考えることができます。続いて、SELECT 句を使用して、出力するフィールドを選択します。

for each customer c in spectrum.customers
  for each order o in c.orders
     output c.id and o.shipdate 

したがって、注文のない顧客は、結果に表示されません。

また、これは、FROM テーブルおよび JOIN 配列を使用して、customers を実行する orders 句と考えることができます。実際、次の例に示すように、クエリを記述することもできます。

SELECT c.id, o.shipdate
FROM   spectrum.customers c INNER JOIN c.orders o ON true 

左結合を使用したネスト解除

次のクエリでは、顧客名とその注文をすべて出力します。顧客が注文を行っていない場合でも、顧客名は返ります。ただし、この場合、次の Jenny Doe の例に示すように、注文列は NULL です。

SELECT c.id, c.name.given, c.name.family, o.shipdate, o.price
FROM   spectrum.customers 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 の値を p として参照します。たとえば、次のクエリでは、顧客名と電話番号のペアが生成されます。

SELECT c.name.given, c.name.family, p AS phone
FROM   spectrum.customers c LEFT JOIN c.phones p ON true  

前述のクエリでは、次のデータが返ります。

given  |  family  |  phone
-------|----------|-----------
John   | Smith    | 123-4577891
Jenny  | Doe      | 858-8675309
Jenny  | Doe      | 415-9876543
Andy   | Jones    | 
(4 rows) 

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

Redshift Spectrum は、map 列および array 列を持つ struct 型を含む key として value データ型を扱うことができます。key は、scalar である必要があります。値は任意のデータ型にすることができます。

たとえば、次のコードでは、電話番号を保存するために、map を使用して外部テーブルを作成します。

CREATE EXTERNAL TABLE spectrum.customers2 (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones map<varchar(20), varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';

map 型の動作は、array 列および key 列を持つ value 型と似ているため、前述のスキーマは次のように考えることができます。

CREATE EXTERNAL TABLE spectrum.customers3 (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones array<struct<key:varchar(20), value:varchar(20)>>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';   

次のクエリでは、顧客名と携帯電話番号が返ります。この番号は顧客名ごとに返ります。map クエリは、array 型のネスト化された struct のクエリと同じように処理されます。前述のように、以下のクエリでは、外部テーブルを作成した場合にのみデータが返ります。

SELECT c.name.given, c.name.family, p.value 
FROM   spectrum.customers c, c.phones p 
WHERE  p.key = 'mobile';