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`。