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

Redshift Spectrum 支援查詢array、map，和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 

                    

針對具有訂單的每個客戶 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
 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 類型的行為類似具有資料欄 key 和 value 的 array 類型，您可以將前述結構描述想像成如下。

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'                    
                         
                     

注意

map 的 key 為用於 Ion 和 JSON 檔案類型的 string。