本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
教學課程：使用 Amazon Redshift Spectrum 查詢巢狀資料
本教學課程示範如何使用 Redshift Spectrum 查詢巢狀資料。巢狀資料是包含巢狀欄位的資料。巢狀欄位是做為單一實體聯結在一起的欄位，例如陣列、結構或物件。
概觀
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中的步驟，再繼續進行。
如要建立外部結構描述，請將以下命令中的 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：建立包含巢狀資料的外部資料表
您可以從 Amazon S3 下載來源資料
若要建立此教學課程的外部資料表，請執行以下命令。
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 檔案格式的巢狀資料。Apache Parquet 檔案的 STORED AS 參數是 PARQUET。LOCATION 參數必須參考包含巢狀資料或檔案的 Amazon S3 資料夾。如需詳細資訊，請參閱CREATE EXTERNAL TABLE。
您可以在任何層級將 array 和 struct 類型形成巢狀。例如，您可以如以下範例所示定義名為 toparray 的資料欄。
toparray array<struct<nestedarray: array<struct<morenestedarray: array<string>>>>>
您也可以如以下範例中的資料欄 x 所示，將 struct 類型形成巢狀。
x struct<a: string,
b: struct<c: integer,
d: struct<e: string>
>
>步驟 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<a: string,
b: struct<c: integer,
d: struct<e: string>
>
>您可以以 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<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 類型的行為類似具有資料欄 key 和 value 的 array 類型，您可以將前述結構描述想像成如下。
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/'; 下列查詢會傳回具有行動電話號碼之客戶的名稱，並傳回每個名稱的號碼。映射查詢被視為與查詢 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。
