查詢半結構化資料 - Amazon Redshift

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

查詢半結構化資料

Amazon Redshift 使用 PartiQL 語言提供對關係數據、半結構化和嵌套數據的 SQL 兼容訪問。

PartiQL 使用動態類型進行操作。此方法支持對結構化、半結構化和嵌套數據集的組合進行直觀的篩選、連接和聚合。當訪問嵌套數據時,PartiQL 語法使用虛線符號和數組下標進行路徑導航。它還允許 FROM 子句項遍歷數組並用於取消操作。下面,您可以找到不同查詢模式的説明,這些模式將使用 SUPER 數據類型與路徑和數組導航、取消嵌套、無透視和連接結合起來。

Amazon Redshift 使用 PartiQL 分別使用 [...] 括號和點符號來啟用導航到數組和結構。此外,您可以使用點符號和數組使用括號表示法將導航混合到結構中。例如,下列範例假設c_ordersSUPER 數據列是一個具有結構的數組,一個屬性名為o_orderkey

若要在customer_orders_lineitem表中執行以下命令。將 IAM 角色替換為您自己的證書。

COPY customer_orders_lineitem FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/customer_orders_lineitem' REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3' FORMAT JSON 'auto'; SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem;

Amazon Redshift 還使用表別名作為表示法的前綴。下列範例與前一個範例相同。

SELECT cust.c_orders[0].o_orderkey FROM customer_orders_lineitem AS cust;

您可以在所有類型的查詢中使用點和括號符號,例如過濾、連接和聚合。您可以在通常存在列引用的查詢中使用這些符號。以下示例使用篩選結果的 SELECT 語句。

SELECT count(*) FROM customer_orders_lineitem WHERE c_orders[0]. o_orderkey IS NOT NULL;

以下示例在 GROUP BY 和 ORDER BY 子句中使用括號和點導航。

SELECT c_orders[0].o_orderdate, c_orders[0].o_orderstatus, count(*) FROM customer_orders_lineitem WHERE c_orders[0].o_orderkey IS NOT NULL GROUP BY c_orders[0].o_orderstatus, c_orders[0].o_orderdate ORDER BY c_orders[0].o_orderdate;

取消嵌套查詢

要取消查詢,Amazon Redshift 使用 PartiQL 語法迭代超級數組。它通過使用查詢的 FROM 子句導航數組來完成此操作。使用上一個示例,下面的示例遍歷c_orders

SELECT c.*, o FROM customer_orders_lineitem c, c.c_orders o;

取消嵌套語法是 FROM 子句的擴展。在標準 SQL 中,FROM 子句x (AS) y意味着y迭代每個元組x。在此案例中,x指的是一個關係,y是的別名x。同樣,使用 FROM 子句項取消嵌套的 PartiQL 語法x (AS) y意味着y迭代(SUPER)數組表達式 x 中的每個(SUPER)值。在此案例中,x是一個超級表達式,y是的別名。x

左操作數也可以使用點和括號表示法進行常規導航。在上述範例中,customer_orders_lineitem c是遍歷customer_order_lineitem基表和c.c_orders o是遍歷c.c_orders陣列。若要遍歷o_lineitems屬性,該屬性是數組中的一個數組,則可以添加多個子句。

SELECT c.*, o, l FROM customer_orders_lineitem c, c.c_orders o, o.o_lineitems l;

當使用 AT 關鍵字遍歷數組時,Amazon Redshift 還支持數組索引。子句x AS y AT z逐一查看陣列x並生成字段z,,它是資料陣列索引。下列範例顯示數組索引如何運作。

SELECT c_name, orders.o_orderkey AS orderkey, index AS orderkey_index FROM customer_orders_lineitem c, c.c_orders AS orders AT index ORDER BY orderkey_index; c_name | orderkey | orderkey_index -------------------+----------+---------------- Customer#000008251 | 3020007 | 0 Customer#000009452 | 4043971 | 0 (2 rows)

以下示例遍歷標量數組。

CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data; SELECT index, element FROM bar AS b, b.data.scalar_array AS element AT index; index | element -------+---------- 0 | 1 1 | 2.3 2 | 45000000 (3 rows)

以下示例遍歷多個級別的數組。該示例使用多個 unnest 子句迭代到最內層的數組。所以此f.multi_level_arrayAS 數組遍歷multi_level_array。數組 AS 元素是遍歷multi_level_array

CREATE TABLE foo AS SELECT json_parse('[[1.1, 1.2], [2.1, 2.2], [3.1, 3.2]]') AS multi_level_array; SELECT array, element FROM foo AS f, f.multi_level_array AS array, array AS element; array | element -----------+--------- [1.1,1.2] | 1.1 [1.1,1.2] | 1.2 [2.1,2.2] | 2.1 [2.1,2.2] | 2.2 [3.1,3.2] | 3.1 [3.1,3.2] | 3.2 (6 rows)

如需 FROM 子句的詳細資訊,請參FROM 子句

物件不透視

要執行對象不透視,Amazon Redshift 使用 PartiQL 語法遍歷超級對象。它使用具有 UNPIVOT 關鍵字的查詢的 FROM 子句來完成此操作。下面的查詢遍歷c.c_orders[0]物件。

SELECT attr as attribute_name, json_typeof(val) as value_type FROM customer_orders_lineitem c, UNPIVOT c.c_orders[0] AS val AT attr WHERE c_custkey = 9451; attribute_name | value_type -----------------+------------ o_orderstatus | string o_clerk | string o_lineitems | array o_orderdate | string o_shippriority | number o_totalprice | number o_orderkey | number o_comment | string o_orderpriority | string (9 rows)

與取消嵌套一樣,無旋轉語法也是 FROM 子句的擴展。不同之處在於,無旋轉語法使用 UNPIVOT 關鍵字來指示它正在迭代一個對象而不是數組。它使用伸縮value_alias來迭代對象內的所有值,並使用 ATattribute_alias來迭代所有屬性。

Amazon Redshift 還支持在單個 FROM 子句中使用對象無旋轉和數組取消嵌套,如下所示。

SELECT attr as attribute_name, val as object_value FROM customer_orders_lineitem c, c.c_orders AS o, UNPIVOT o AS val AT attr WHERE c_custkey = 9451;

當您使用對象不透視時,Amazon Redshift 不支持關聯無旋轉。具體而言,假設您有一種情況,其中在不同的查詢級別中有多個不透視的示例,並且內部無旋轉引用外部的示例。Amazon Redshift 不支援此類型的多重無透視。

如需 FROM 子句的詳細資訊,請參FROM 子句。如需如何使用 PIVOT 和 UNPIVOT 查詢結構化資料的範例,請參透視和不透視表示例

動態類型

動態類型不需要顯式轉換從點和括號路徑中提取的數據。Amazon Redshift 使用動態類型來處理模式 SUPER 數據,而無需在查詢中使用數據類型之前聲明這些數據類型。動態類型使用導航到 SUPER 數據列的結果,而無需明確地將它們轉換為 Amazon Redshift 類型。動態類型在連接和 GROUP BY 子句中最有用。以下示例使用 SELECT 語句,該語句不需要將點和括號表達式顯式轉換為通常的 Amazon Redshift 類型。有關類型兼容性和轉換的信息,請參閲類型相容性與轉換

SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem WHERE c_orders[0].o_orderstatus = 'P';

此查詢中的相等符號計算為true當 c_Order [0] .o_ 訂單狀態為字符串 'P' 時。在所有其他案例中,均等符號的計算結果為false,包括平等論點是不同類型的情況。

動態和靜態類型

如果不使用動態類型,則無法確定 c_訂單[ 0] .o_orderstatus 是字符串、整數還是結構。您只能確定 c_訂單[ 0] .o_order狀態是超級數據類型,可以是 Amazon Redshift 標量、數組或結構。c_Order [0] .o_Order 狀態的靜態類型是超級數據類型。通常,類型在 SQL 中是隱式的靜態類型。

Amazon Redshift 使用動態類型來處理架構數據。當查詢評估數據時,c_訂單[ 0] .o_Orderder狀態將會發現為特定的類型。例如,在客户訂單條目的第一條記錄上評估 c_Order [0] .o_ 訂單狀態可能會導致整數。對第二條記錄進行評估可能會導致字符串。這些是表達式的動態類型。

當使用具有動態類型的點和括號表達式的 SQL 運算符或函數時,Amazon Redshift 會產生類似於將標準 SQL 運算符或函數用於各自靜態類型的結果。在此示例中,當路徑表達式的動態類型是字符串時,與字符串 'P' 的比較是有意義的。只要 c_訂單[ 0] .o_orderstatus 的動態類型為除字符串以外的任何其他數據類型,相等性將返回 false。當使用錯誤輸入的參數時,其他函數返回 null。

以下示例使用靜態類型寫入上一個查詢:

SELECT c_custkey FROM customer_orders_lineitem WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string' THEN c_orders[0].o_orderstatus::VARCHAR = 'P' ELSE FALSE END;

請注意相等謂詞和比較謂詞之間的以下區別。在上一個示例中,如果用小於或等於的謂詞替換相等謂詞,則語義會產生 null 而不是 false。

SELECT c_orders[0]. o_orderkey FROM customer_orders_lineitem WHERE c_orders[0].o_orderstatus <= 'P';

在此示例中,如果 c_訂單[ 0] .o_order狀態是一個字符串,則 Amazon Redshift 如果字母順序等於或小於「P」,則返回 true。如果 Amazon Redshift 的字母順序大於「P」,則返回 false。但是,如果 c_訂單[ 0] .o_order狀態不是字符串,Amazon Redshift 將返回空值,因為 Amazon Redshift 無法比較不同類型的值,如以下查詢所示:

SELECT c_custkey FROM customer_orders_lineitem WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string' THEN c_orders[0].o_orderstatus::VARCHAR <= 'P' ELSE NULL END;

動態類型不排除在最小可比類型的比較之外。例如,您可以將 CHAR 和 VARCHAR Amazon Redshift 標量類型轉換為 SUPER。它們與字符串類似,包括忽略類似於 Amazon Redshift CHAR 和 VARCHAR 類型的尾隨空格字符。同樣,整數、小數和浮點值與 SUPER 值相當。特別是對於十進制列,每個值也可以具有不同的比例。Amazon Redshift 仍將它們視為動態類型。

Amazon Redshift 還支持在評估為深度相等的對象和數組上相等,例如深入評估對象或數組以及比較所有屬性。請謹慎使用深等於,因為執行深度相等的過程可能非常耗時。

對連接使用動態類型

對於連接,動態類型會自動匹配具有不同動態類型的值,而無需執行長 CASE WHEN 分析以找出可能顯示的數據類型。例如,假定您的組織隨着時間的推移更改了它用於部件鍵的格式。

發出的初始整數部分鍵被字符串部分鍵(如 'A55')替換,然後再次被數組部分鍵替換,如結合字符串和數字的 ['X', 10]。Amazon Redshift 不必對部件密鑰執行宂長的案例分析,並且可以使用連接,如以下示例所示。

SELECT c.c_name ,l.l_extendedprice ,l.l_discount FROM customer_orders_lineitem c ,c.c_orders o ,o.o_lineitems l ,supplier_partsupp s ,s.s_partsupps ps WHERE l.l_partkey = ps.ps_partkey AND c.c_nationkey = s.s_nationkey ORDER BY c.c_name;

以下示例説明瞭在不使用動態類型的情況下,同一查詢的複雜性和效率低下:

SELECT c.c_name ,l.l_extendedprice ,l.l_discount FROM customer_orders_lineitem c ,c.c_orders o ,o.o_lineitems l ,supplier_partsupp s ,s.s_partsupps ps WHERE CASE WHEN IS_INTEGER(l.l_partkey) AND IS_INTEGER(ps.ps_partkey) THEN l.l_partkey::integer = ps.ps_partkey::integer WHEN IS_VARCHAR(l.l_partkey) AND IS_VARCHAR(ps.ps_partkey) THEN l.l_partkey::varchar = ps.ps_partkey::varchar WHEN IS_ARRAY(l.l_partkey) AND IS_ARRAY(ps.ps_partkey) AND IS_VARCHAR(l.l_partkey[0]) AND IS_VARCHAR(ps.ps_partkey[0]) AND IS_INTEGER(l.l_partkey[1]) AND IS_INTEGER(ps.ps_partkey[1]) THEN l.l_partkey[0]::varchar = ps.ps_partkey[0]::varchar AND l.l_partkey[1]::integer = ps.ps_partkey[1]::integer ELSE FALSE END AND c.c_nationkey = s.s_nationkey ORDER BY c.c_name;

Lax 語義

默認情況下,對 SUPER 值的導航操作返回 null,而不是在導航無效時返回錯誤。如果 SUPER 值不是對象,或者 SUPER 值是一個對象,但不包含查詢中使用的屬性名稱,則對象導航無效。例如,以下查詢訪問超級數據列 cdata 中的無效屬性名稱:

SELECT c.c_orders.something FROM customer_orders_lineitem c;

如果 SUPER 值不是數組或數組索引超出界限,則數組導航返回 null。以下查詢返回 null,因為 c_Order [1] [1] 超出界限。

SELECT c.c_orders[1][1] FROM customer_orders_lineitem c;

當使用動態類型來轉換 SUPER 值時,Lax 語義特別有用。如果轉換無效,將 SUPER 值轉換為錯誤的類型返回 null 而不是錯誤。例如,以下查詢返回 null,因為它無法將對象屬性 o_orderstatus 的字符串值 '良好' 轉換為 INTEGER。Amazon Redshift 返回一個 VARCHAR 到整數轉換的錯誤,但不會為超級轉換返回錯誤。

SELECT c.c_orders.o_orderstatus::integer FROM customer_orders_lineitem c;

自我檢查的類型

SUPER 數據列支持返回有關 SUPER 值的動態類型和其他類型信息的檢查函數。最常見的例子是 JSON_TYPEOF 標量函數,該函數返回值為布爾值、數字、字符串、對象、數組或空值的 VARCHAR,具體取決於 SUPER 值的動態類型。Amazon Redshift 支持超級數據列的以下布爾函數:

  • 小數精度

  • 小尺度

  • 是陣列

  • 是比金

  • 伊斯字符

  • 以十進制

  • 是浮動

  • 是整數

  • 是對象

  • IS_標量

  • 是小

  • 伊斯瓦爾卡爾

  • JSON 類型

如果輸入值為空,則所有這些函數都返回 false。IS_標量、IS_OBJECT 和 IS_Array 是相互排斥的,並且覆蓋除了空值以外的所有可能值。

為了推斷與數據對應的類型,Amazon Redshift 使用 JSON_TYPEOF 函數,該函數返回超級值的類型(最高級別),如以下示例所示:

SELECT JSON_TYPEOF(r_nations) FROM region_nations; json_typeof ------------- array (1 row)
SELECT JSON_TYPEOF(r_nations[0].n_nationkey) FROM region_nations; json_typeof ------------- number

Amazon Redshift 將其視為單個長字符串,類似於將此值插入到 VARCHAR 列而不是 SUPER 中。由於該列是 SUPER,因此單個字符串仍然是一個有效的超級值,並且差異在 JSON_TYPEOF 中記錄:

SELECT IS_VARCHAR(r_nations[0].n_name) FROM region_nations; is_varchar ------------- true (1 row)
SELECT r_nations[4].n_name FROM region_nations WHERE CASE WHEN IS_INTEGER(r_nations[4].n_nationkey) THEN r_nations[4].n_nationkey::INTEGER = 15 ELSE false END;

排序依據

Amazon Redshift 不會在具有不同動態類型的值之間定義超級比較。作為字符串的 SUPER 值既不小,也不大於作為數字的 SUPER 值。要將 ORDER BY 子句與超級列結合使用,亞馬遜 Redshift 定義了不同類型之間的總排序,當 Amazon Redshift 使用 ORDER BY 子句對超級值進行排名時要遵循的總排序。動態類型之間的順序是布爾值,數字,字符串,數組,對象。下列範例顯示不同類型的訂單:

INSERT INTO region_nations VALUES (100,'name1','comment1','AWS'), (200,'name2','comment2',1), (300,'name3','comment3',ARRAY(1, 'abc', null)), (400,'name4','comment4',-2.5), (500,'name5','comment5','Amazon'); SELECT r_nations FROM region_nations order by r_nations; r_nations ---------------- -2.5 1 "Amazon" "AWS" [1,"abc",null] (5 rows)

如需 ORDER BY 子句的詳細資訊,請參ORDER BY 子句