チュートリアル: Amazon 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: ネストデータを含む外部テーブルを作成する
ソースデータ
このチュートリアル用に外部テーブルを作成するには、次のコマンドを実行します。
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
注記
c
という名前のテーブルを持つスキーマ orders
が存在する場合、c.orders
は、テーブル orders
を参照します。customers
の配列の列は参照されません。
左結合を使用したネスト解除
次のクエリでは、顧客名とその注文をすべて出力します。顧客が注文を行っていない場合でも、顧客名は返ります。ただし、この場合、次の 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';
注記
key
の map
は、ファイル形式が Ion や JSON の場合の string
です。