Amazon Redshift 將不再支援從修補程式 198 開始建立新的 Python UDFs。現有 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：在 Amazon S3 中使用 SQL 延伸模組查詢您的巢狀資料](#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` 的外部資料表。
例如，假設您的資料檔案在 Amazon S3 名為 `customers` 的資料夾中包含下列資料。雖然沒有單一根元素，此取樣資料中的每個 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：建立包含巢狀資料的外部資料表
您可以從 Amazon S3 下載[來源資料](https://s3.amazonaws.com/redshift-downloads/tickit/spectrum/customers/customer_file1)以檢視來源資料。
若要建立此教學課程的外部資料表，請執行以下命令。
```
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 檔案格式的巢狀資料。Apache Parquet 檔案的 `STORED AS` 參數是 `PARQUET`。`LOCATION` 參數必須參考包含巢狀資料或檔案的 Amazon S3 資料夾。如需詳細資訊，請參閱[CREATE EXTERNAL TABLE](r_CREATE_EXTERNAL_TABLE.md)。
您可以在任何層級將 `array` 和 `struct` 類型形成巢狀。例如，您可以如以下範例所示定義名為 `toparray` 的資料欄。
```
toparray array>>>>
```
您也可以如以下範例中的資料欄 `x` 所示，將 `struct` 類型形成巢狀。
```
x struct
>
>
```
## 步驟 2：在 Amazon S3 中使用 SQL 延伸模組查詢您的巢狀資料
Redshift Spectrum 支援透過 Amazon Redshift SQL 語法的延組模組查詢 `array`、`map` 和 `struct` 複雜類型。
### 延組模組 1：存取 structs 的欄位
您可以使用可將欄位名稱串連為路徑的點表示法，從 `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
>
>
```
您可以以 `x.b.d.e` 的形式存取 `e` 中的資料。
### 延伸模組 2：在 FROM 子句中存取 array 的範圍
您可以從 `array` 資料欄擷取資料 (並且透過延伸模組 `map` 資料欄)，方法是在 `FROM` 子句中指定 `array` 資料欄以取代資料表名稱。延伸模組會套用到主查詢的 `FROM` 子句，以及子查詢的 `FROM` 子句。
您可以依位置參考 `array` 元素，例如 `c.orders[0]`。(預覽)
藉由將範圍與 `arrays` 聯結結合，您可以達成各種解巢狀，如下列使用案例所述。
#### 使用內部聯結解巢狀
下列查詢會選取具有訂單之客戶的客戶 ID 和訂單出貨日期。FROM 子句 `c.orders o` 中的 SQL 延伸模組取決於別名 `c`。
```
SELECT c.id, o.shipdate
FROM spectrum.customers c, c.orders o
```
針對具有訂單的每個客戶 `c`，`FROM` 子句會為客戶 `c` 的每個訂單 `o` 傳回一個資料列。該資料列會結合客戶資料列 `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
```
因此，如果客戶沒有訂單，客戶便不會出現在結果中。
您也可以將這想成執行 `JOIN` 搭配 `customers`資料表和 `orders` 陣列的 `FROM` 子句。實際上，您也可以如下列範例所示寫入查詢。
```
SELECT c.id, o.shipdate
FROM spectrum.customers c INNER JOIN c.orders o ON true
```
**注意**
如果名為 `c` 的結構描述存在於名為 `orders` 的資料表中，則 `c.orders` 會參考資料表 `orders`，而非 `customers` 的陣列資料欄。
#### 使用左聯結解巢狀
下列查詢會輸出所有客戶名稱和其訂單。如果客戶尚未下訂單，仍會傳回客戶的名稱。不過，在此情況下，訂單資料欄會是 NULL，如以下 Jenny Doe 的範例所示。
```
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：直接使用別名存取純量的陣列
當 `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` 類型的行為類似具有資料欄 `key` 和 `value` 的 `array` 類型，您可以將前述結構描述想像成如下。
```
CREATE EXTERNAL TABLE spectrum.customers3 (
id int,
name struct,
phones array>,
orders array>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';
```
下列查詢會傳回具有行動電話號碼之客戶的名稱，並傳回每個名稱的號碼。映射查詢被視為與查詢 `struct` 類型的巢狀 `array` 的同等項目。下列查詢只會在您如先前所述建立了外部資料表時傳回資料。
```
SELECT c.name.given, c.name.family, p.value
FROM spectrum.customers c, c.phones p
WHERE p.key = 'mobile';
```
**注意**
`map` 的 `key` 為用於 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` 群組。或者，如果您知道每個 `customer` 的 `id` 是唯一的，並且 `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;
```
您也可以在參照上階查詢別名 (`c`) 的 `FROM` 子句中使用子查詢來編寫查詢，並擷取陣列資料。下列範例示範此方法。
```
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` 子句以外位置巢狀資料的子查詢必須傳回單一值。唯一的例外是 `WHERE` 子句中的 `(NOT) EXISTS` 運算子。
+ 不支援 `(NOT) IN`。
+ 所有巢狀類型的最大深度為 100。此限制適用所有檔案格式 (Parquet、ORC、Ion 和 JSON)。
+ 存取巢狀資料的彙總子查詢只能參考其 `FROM` 子句中 (而非外部資料表) 的 `arrays` 和 `maps`。
+ 不支援查詢 Redshift Spectrum 資料表中巢狀資料的虛擬資料欄。如需詳細資訊，請參閱[虛擬資料欄](c-spectrum-external-tables.md#c-spectrum-external-tables-pseudocolumns)。
+ 當透過在 `FROM` 子句中指定陣列或映射資料欄來擷取資料時，您只能從這些資料欄中選擇值 (如果值為 `scalar`)。例如，下列查詢會嘗試從陣列內部的 `SELECT` 元素。選取 `arr.a` 的查詢是有效的，因為 `arr.a` 是一個 `scalar` 值。第二個查詢不起作用，因為 `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-functions.md) 存取巢狀 JSON。如需詳細資訊，請參閱[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) 時，無法直接存取其巢狀子欄位做為查詢語法的一部分 (例如，在篩選條件子句中)。但是，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) 欄位中包含 JSON 作為 `structs` 複雜類型：
```
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"}
```