本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
教學課程:使用 Amazon 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
。