Amazon Redshift は、パッチ 198 以降、新しい Python UDF の作成をサポートしなくなります。既存の Python UDF は、2026 年 6 月 30 日まで引き続き機能します。詳細については、[ブログ記事](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)を参照してください。
# チュートリアル: Amazon Redshift Spectrum を使用したネストデータのクエリ
このチュートリアルでは、Redshift Spectrum を使用してネストデータをクエリする方法を示します。ネストデータは、ネストされたフィールドを含むデータです。ネストされたフィールドは、配列、構造体、オブジェクトなど、単一のエンティティとして結合されるフィールドです。
**Topics**
+ [概要](#tutorial-nested-data-overview)
+ [ステップ 1: ネストデータを含む外部テーブルを作成する](#tutorial-nested-data-create-table)
+ [ステップ 2: SQL 拡張を使用して Amazon S3 のネストデータにクエリを実行する](#tutorial-query-nested-data-sqlextensions)
+ [ネストデータのユースケース](nested-data-use-cases.md)
+ [ネストデータの制限 (プレビュー)](nested-data-restrictions.md)
+ [複雑なネストされた JSON のシリアル化](serializing-complex-JSON.md)
## 概要
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 の開始方法](c-getting-started-using-spectrum.md) のステップに従って行います。
外部スキーマを作成するには、次のコマンドの IAM ロール ARN を、「[IAM ロールを作成する](c-getting-started-using-spectrum.md#c-getting-started-using-spectrum-create-role)」で作成したロール 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: ネストデータを含む外部テーブルを作成する
[ソースデータ](https://s3.amazonaws.com/redshift-downloads/tickit/spectrum/customers/customer_file1)は、Amazon S3 からダウンロードして表示できます。
このチュートリアル用に外部テーブルを作成するには、次のコマンドを実行します。
```
CREATE EXTERNAL TABLE spectrum.customers (
id int,
name struct,
phones array,
orders array>
)
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](r_CREATE_EXTERNAL_TABLE.md)」を参照してください。
データ型 `array` および `struct` は、任意のレベルでネスト化することができます。たとえば、次の例で示すように、`toparray` という名前の列を定義できます。
```
toparray array>>>>
```
また、次の例の `struct` で示すように、データ型 `x` をネスト化することもできます。
```
x struct
>
>
```
## ステップ 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
>
>
```
`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,
phones map,
orders array>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';
```
`map` 型の動作は、`array` 列および `key` 列を持つ `value` 型と似ているため、前述のスキーマは次のように考えることができます。
```
CREATE EXTERNAL TABLE spectrum.customers3 (
id int,
name struct,
phones array>,
orders array>
)
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` です。