步驟 2:使用 SQL 延伸模組查詢您在 Amazon S3 中的巢狀資料 - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

步驟 2:使用 SQL 延伸模組查詢您在 Amazon S3 中的巢狀資料

Redshift Spectrum 支援查詢arraymap,和struct複雜類型,通過 Amazon Redshift SQL 語法的延伸模組。

延伸模組 1:存取結構的欄位

您可以使用可將欄位名稱串連為路徑的點表示法,從 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 中的資料。

注意

您使用 struct 只是為了描述其包含欄位的路徑。您無法直接在查詢中存取它們或從查詢傳回它們。

延伸模組 2:在 FROM 子句中存取組的範圍

您可以從 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

針對具有訂單的每個客戶 cFROM 子句會為客戶 c 的每個訂單 o 傳回一個資料列。該資料列會結合客戶資料列 c 和訂單資料列 o。那麼,SELECT 子句只會保留 c.ido.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 2 | 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:訪問地圖的元素

Redshift Spectrum 會將 map 資料類型視為 array 類型,其中包含的 struct 類型具有 key 資料欄和 value 資料欄。key 必須是 scalar;值可以是任何資料類型。

例如,以下程式碼會建立外部資料表,其中具有 map 用於儲存電話號碼。

CREATE EXTERNAL TABLE spectrum.customers ( id int, name struct<given:varchar(20), family:varchar(20)>, phones map<varchar(20), varchar(20)>, orders array<struct<shipdate:timestamp, price:double precision>> )

因為 map 類型的行為類似具有資料欄 keyvaluearray 類型,您可以將前述結構描述想像成如下。

CREATE EXTERNAL TABLE spectrum.customers ( 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>> )

下列查詢會傳回具有行動電話號碼之客戶的名稱,並傳回每個名稱的號碼。映射查詢被視為與查詢 struct 類型的巢狀 array 的同等項目。下列查詢只會在您如先前所述建立了外部資料表時傳回資料。

SELECT c.name.given, c.name.family, p.value FROM spectrum.customers c, c.phones p WHERE p.key = 'mobile'
注意

mapkey 為用於 Ion 和 JSON 檔案類型的 string