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` です。 # ネストデータのユースケース このトピックでは、ネストデータのユースケースについて説明します。ネストデータは、ネストされたフィールドを含むデータです。ネストされたフィールドは、配列、構造体、オブジェクトなど、単一のエンティティとして結合されるフィールドです。 以前に説明した拡張は、通常の SQL 機能と組み合わせることができます。以下のユースケースでは、一般的な組み合わせを取り上げます。これらの例は、ネストデータの使用方法を示すのに役立ちます。これらはチュートリアルの範囲外です。 **Topics** + [ネストデータの取り込み](#ingesting-nested-data) + [サブクエリを使用したネストデータの集約](#aggregating-with-subquery) + [Amazon Redshift とネストデータの結合](#joining-redshift-data) ## ネストデータの取り込み 複合データ型を含む外部テーブルからデータを取り込むには、`CREATE TABLE AS` ステートメントを使用します。以下のクエリでは、`LEFT JOIN` を使用して、外部テーブルから顧客とその電話番号をすべて抽出し、Amazon Redshift テーブルの `CustomerPhones` にそのデータを保存します。 ``` CREATE TABLE CustomerPhones AS SELECT c.name.given, c.name.family, p AS phone FROM spectrum.customers c LEFT JOIN c.phones p ON true; ``` ## サブクエリを使用したネストデータの集約 ネストデータを集約するにはサブクエリを使用します。この方法の概要を次の例に示します。 ``` SELECT c.name.given, c.name.family, (SELECT COUNT(*) FROM c.orders o) AS ordercount FROM spectrum.customers c; ``` 以下のデータが返ります。 ``` given | family | ordercount --------|----------|-------------- Jenny | Doe | 0 John | Smith | 2 Andy | Jones | 1 (3 rows) ``` **注記** 親の行でグループ化してネストデータを集約するには、前の例で示した方法が最も簡単です。この例では、ネスト化された行 `c.orders` は、親の行 `c` によってグループ化されます。また、`id` は `customer` ごとに一意であり、`o.shipdate` が null になることはないという前提で、次の例に示すように集約することができます。ただし、この方法は通常、前の例ほど効率的ではありません。 ``` SELECT c.name.given, c.name.family, COUNT(o.shipdate) AS ordercount FROM spectrum.customers c LEFT JOIN c.orders o ON true GROUP BY c.id, c.name.given, c.name.family; ``` また、`FROM` 句で祖先クエリのエイリアス (`c`) を参照し、配列データを抽出するサブクエリを使用して、クエリを記述することもできます。次の例で、この方法を示します。 ``` SELECT c.name.given, c.name.family, s.count AS ordercount FROM spectrum.customers c, (SELECT count(*) AS count FROM c.orders o) s; ``` ## Amazon Redshift とネストデータの結合 また、Amazon Redshift データを外部テーブルのネストデータと結合することもできます。たとえば、Amazon S3 に次のネストデータがあるとします。 ``` CREATE EXTERNAL TABLE spectrum.customers2 ( id int, name struct, phones array, orders array> ); ``` さらに、Amazon Redshift に次のテーブルがあるとします。 ``` CREATE TABLE prices ( id int, price double precision ); ``` 次のクエリでは、前の値に基づいて各顧客の購入の合計数と金額を検索します。以下はあくまでも例です。データは、前に説明したテーブルを作成した場合にのみ返ります。 ``` SELECT c.name.given, c.name.family, COUNT(o.date) AS ordercount, SUM(p.price) AS ordersum FROM spectrum.customers2 c, c.orders o, prices p ON o.item = p.id GROUP BY c.id, c.name.given, c.name.family; ``` # ネストデータの制限 (プレビュー) このトピックでは、Redshift Spectrum でネストされたデータを読み込むための制限事項について説明します。ネストデータは、ネストされたフィールドを含むデータです。ネストされたフィールドは、配列、構造体、オブジェクトなど、単一のエンティティとして結合されるフィールドです。 **注記** 次のリストで「(プレビュー)」とマークされている制限は、以下のリージョンで作成されたプレビュークラスターにのみ適用されます。 米国東部 (オハイオ) (us-east-2) 米国東部 (バージニア北部) (us-east-1) 米国西部 (北カリフォルニア) (us-west-1) アジアパシフィック (東京) (ap-northeast-1) 欧州 (アイルランド) (eu-west-1) 欧州 (ストックホルム) (eu-north-1) プレビュークラスターの設定の詳細については、「Amazon Redshift 管理ガイド**」の「[プレビュークラスターの作成](https://docs.aws.amazon.com/redshift/latest/mgmt/managing-clusters-console.html#cluster-preview)」を参照してください。 ネストデータには以下の制限が適用されます。 + `array` 型または `map` 型には他の `array` 型または `map` 型を含めることができますが、ネスト化された `arrays` または `maps` のクエリで `scalar` 値が返されないことが前提です (プレビュー)。 + Amazon Redshift Spectrum では、外部テーブルとしてのみ複合データ型がサポートされています。 + サブクエリの結果列は、最上位である必要があります (プレビュー)。 + `OUTER JOIN` 式によって、ネスト化されたテーブルが参照される場合は、テーブルとそのネスト化された配列 (およびマップ) でのみ参照されます。`OUTER JOIN` 式でネスト化されたテーブルを参照しない場合は、任意の数のネスト化されていないテーブルを参照することができます。 + サブクエリの `FROM` 句でネスト化されたテーブルが参照されている場合は、その他のテーブルを参照することはできません。 + サブクエリが、親テーブルを参照するネスト化されたテーブルに依存する場合、親テーブルは `FROM` 句でのみ使用できます。`SELECT` 句や `WHERE` 句など、他の句で親を使用することはできません。例えば、次のクエリは、サブクエリの `SELECT` 句で親テーブル `c` を参照しているため、実行されません。 ``` SELECT c.name.given FROM spectrum.customers c WHERE (SELECT COUNT(c.id) FROM c.phones p WHERE p LIKE '858%') > 1; ``` 次のクエリは、親である `c` は、サブクエリの `FROM` 句でのみ使用されるため、問題なく動作します。 ``` SELECT c.name.given FROM spectrum.customers c WHERE (SELECT COUNT(*) FROM c.phones p WHERE p LIKE '858%') > 1; ``` + `FROM` 句以外の場所にあるネストされたデータにアクセスするサブクエリは、単一の値を返す必要があります。唯一の例外は、`(NOT) EXISTS` 句の `WHERE` 演算子です。 + `(NOT) IN` はサポートされていません。 + ネストされたすべてのタイプの最大ネスト深度は 100 です。この制限は、すべてのファイル形式 (Parquet、ORC、Ion、JSON) に適用されます。 + ネストデータにアクセスする集約サブクエリは、`arrays` 句の `maps` および `FROM` のみを参照できます。外部テーブルは参照できません。 + Redshift Spectrum テーブル内のネストされたデータの疑似列のクエリはサポートされていません。詳細については、「[疑似列](c-spectrum-external-tables.md#c-spectrum-external-tables-pseudocolumns)」を参照してください。 + 配列またはマップの列を `FROM` 句で指定してデータを抽出する場合、それらの列から、`scalar` である値のみを選択できます。例えば、以下のクエリは両方とも、配列内の要素を `SELECT` しようとしています。`arr.a` を選択するクエリは、`arr.a` が `scalar` 値であるため機能します。2 番目のクエリは、`array` が `FROM` 句で `s3.nested table` から抽出された配列であるため、機能しません (プレビュー)。 ``` SELECT array_column FROM s3.nested_table; array_column ----------------- [{"a":1},{"b":2}] SELECT arr.a FROM s3.nested_table t, t.array_column arr; arr.a ----- 1 --This query fails to run. SELECT array FROM s3.nested_table tab, tab.array_column array; ``` 別の配列やマップから抽出された配列またはマップは、`FROM` では使用できません。他の配列の中にネストされている配列やその他の複雑な構造体を選択するには、`SELECT` ステートメントでインデックスを使用することを検討してください。 # 複雑なネストされた JSON のシリアル化 このトピックでは、ネストデータを JSON 形式でシリアル化する方法について説明します。ネストデータは、ネストされたフィールドを含むデータです。ネストされたフィールドは、配列、構造体、オブジェクトなど、単一のエンティティとして結合されるフィールドです。 このチュートリアルで説明するメソッドの代わりに、最上位のネストされたコレクション列をシリアル化された JSON としてクエリする方法があります。シリアル化を使用して、Redshift Spectrum を使用して、ネストされたデータを JSON として検査、変換、取り込むことができます。このメソッドは、ORC、JSON、Ion、および Parquet 形式でサポートされています。セッション構成パラメータ `json_serialization_enable` を使用して、シリアル化動作を構成します。設定すると、複雑な JSON データ型が VARCHAR (65535) にシリアル化されます。ネストされた JSON には [JSON 関数](json-functions.md) でアクセスできます。詳細については、「[json\$1serialization\$1enable](r_json_serialization_enable.md)」を参照してください。 たとえば、`json_serialization_enable` を設定しないと、ネストされた列に直接アクセスする次のクエリが失敗します。 ``` SELECT * FROM spectrum.customers LIMIT 1; => ERROR: Nested tables do not support '*' in the SELECT clause. SELECT name FROM spectrum.customers LIMIT 1; => ERROR: column "name" does not exist in customers ``` `json_serialization_enable` を設定すると、最上位のコレクションに直接クエリを実行できます。 ``` SET json_serialization_enable TO true; SELECT * FROM spectrum.customers order by id LIMIT 1; id | name | phones | orders ---+--------------------------------------+----------------+---------------------------------------------------------------------------------------------------------------------- 1 | {"given": "John", "family": "Smith"} | ["123-457789"] | [{"shipdate": "2018-03-01T11:59:59.000Z", "price": 100.50}, {"shipdate": "2018-03-01T09:10:00.000Z", "price": 99.12}] SELECT name FROM spectrum.customers order by id LIMIT 1; name --------- {"given": "John", "family": "Smith"} ``` ネストされた JSON をシリアル化するときは、以下の項目について考慮してください。 + コレクション列が VARCHAR (65535) としてシリアル化されている場合、それらのネストされたサブフィールドは、クエリ構文の一部として直接アクセスできません (filter 句内など)。ただし、JSON 関数を使用して、ネストされた JSON にアクセスできます。 + 次の特殊な表現はサポートされていません。 + ORC 組合 + 複合型キーを持つ ORC マップ + Ion データグラム + Ion SEXP + タイムスタンプは ISO シリアル化された文字列として返されます。 + プリミティブマップキーは文字列に昇格されます (たとえば、`1`～`"1"`)。 + 最上位の null 値は NULL としてシリアル化されます。 + シリアル化によって VARCHAR の最大サイズである 65535 がオーバーフローすると、セルは NULL に設定されます。 ## JSON 文字列を含む複合型のシリアル化 デフォルトでは、ネストされたコレクションに含まれる文字列値は、エスケープされた JSON 文字列としてシリアル化されます。文字列が有効な JSON である場合、エスケープは望ましくない場合があります。代わりに、VARCHAR であるネストされたサブ要素またはフィールドを JSON として直接記述することができます。`json_serialization_parse_nested_strings` セッションレベルの設定でこの動作を有効にします。`json_serialization_enable` と `json_serialization_parse_nested_strings` の両方が設定されている場合、有効な JSON 値はエスケープ文字なしでインラインでシリアル化されます。値が有効な JSON でない場合、`json_serialization_parse_nested_strings` 設定値が設定されていないかのようにエスケープされます。詳細については、「[json\$1serialization\$1parse\$1nested\$1strings](r_json_serialization_parse_nested_strings.md)」を参照してください。 たとえば、前の例のデータの `name` VARCHAR(20) フィールドに `structs` 複合型として JSON が含まれているとします。 ``` name --------- {"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"} ``` `json_serialization_parse_nested_strings` が設定されている場合、`name` 列は次のようにシリアル化されます。 ``` SET json_serialization_enable TO true; SET json_serialization_parse_nested_strings TO true; SELECT name FROM spectrum.customers order by id LIMIT 1; name --------- {"given": {"first":"John","middle":"James"}, "family": "Smith"} ``` 次のようにエスケープされません。 ``` SET json_serialization_enable TO true; SELECT name FROM spectrum.customers order by id LIMIT 1; name --------- {"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"} ```