Abfragen von halbstrukturierten Daten

Amazon Redshift verwendet die PartiQL-Sprache, um SQL-kompatiblen Zugriff auf relationale, halbstrukturierte und verschachtelte Daten zu bieten.

PartiQL arbeitet mit dynamischen Typen. Dies ermöglicht eine intuitive Filterung, Verknüpfung und Aggregation für die Kombination strukturierter, semistrukturierter und verschachtelter Datensätze. Die PartiQL-Syntax verwendet Punktschreibweise und Array-Subscript für die Pfadnavigation beim Zugriff auf verschachtelte Daten. Es ermöglicht auch die FROM-Klauselelemente über Arrays zu iterieren und für Unnest-Operationen zu verwenden. Nachfolgend werden die verschiedenen Abfragemuster beschrieben, die die Verwendung des SUPER-Datentyps mit Pfad- und Array-Navigation, Aufheben der Verschachtelung, Entpivotieren und Joins kombinieren.

Weitere Hinweise zu den Tabellen, die in den folgenden Beispielen verwendet werden, finden Sie unter SUPER-Beispieldatensatz.

Navigation

Amazon Redshift verwendet PartiQL, um die Navigation in Arrays und Strukturen mithilfe der [...]-Klammer bzw. Punktschreibweise zu ermöglichen. Darüber hinaus können Sie die Navigation mithilfe von Punktschreibweise und Arrays mithilfe der Klammernotation in Strukturen mischen. Im folgenden Beispiel wird angenommen, dass die SUPER-Datenspalte c_orders ein Array mit einer Struktur ist und ein Attribut o_orderkey lautet.

Um Daten in der Spalte customer_orders_lineitem zu erfassen, führen Sie den folgenden Befehl aus. Ersetzen Sie die IAM-Rolle durch Ihre eigenen Anmeldeinformationen.

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 verwendet auch einen Tabellenalias als Präfix für die Notation. Das folgende Beispiel zeigt dieselbe Abfrage wie im vorherigen Beispiel.

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

Sie können die Punkt- und Klammernotationen in allen Arten von Abfragen verwenden, z. B. Filtern, Verknüpfen und Aggregation. Sie können diese Notationen in einer Abfrage verwenden, in der normalerweise Spaltenverweise vorhanden sind. Im folgenden Beispiel wird eine SELECT-Anweisung verwendet, die Ergebnisse filtert.

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

Im folgenden Beispiel wird die Klammer- und Punktnavigation in den Klauseln GROUP BY und ORDER BY verwendet.

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;

Aufheben der Verschachtelung von Abfragen

Zur Aufhebung der Verschachtelung von Abfragen verwendet Amazon Redshift die PartiQL-Syntax, um über SUPER-Arrays zu iterieren. Dazu navigiert es im Array mithilfe der FROM-Klausel einer Abfrage. Das folgende Beispiel nutzt das vorherige Beispiel und iteriert über die Attributwerte für c_orders.

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

Die Unnesting-Syntax ist eine Erweiterung der FROM-Klausel. In Standard-SQL bedeutet die FROM-Klausel x (AS) y, dass y über jedes Tupel in Beziehung x iteriert. In diesem Fall bezieht sich x auf eine Beziehung und y bezieht sich auf einen Alias für Beziehung x. Entsprechend bedeutet die PartiQL-Syntax zum Aufheben der Verschachtelung mithilfe des FROM-Klauselelements x (AS) y, dass y über jeden (SUPER)-Wert in (SUPER)-Array-Ausdruck x iteriert. In diesem Fall ist x ein SUPER-Ausdruck und y ist ein Alias für x.

Der linke Operand kann auch die Punkt- und Klammernotation für die reguläre Navigation verwenden. Im vorherigen Beispiel ist customer_orders_lineitem c die Iteration über die Basistabelle customer_order_lineitem und c.c_orders o ist die Iteration über das Array c.c_orders. Um über das Attribut o_lineitems zu iterieren, also ein Array innerhalb eines Arrays, fügen Sie mehrere Klauseln hinzu.

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

Amazon Redshift unterstützt auch einen Array-Index, wenn mit dem AT-Schlüsselwort über das Array iteriert wird. Die Klausel x AS y AT z iteriert über Array x und generiert das Feld z,, das der Array-Index ist. Das folgende Beispiel zeigt die Funktionsweise eines Array-Index.

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)

Das folgende Beispiel iteriert über ein skalares Array.

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)

Im folgenden Beispiel wird über ein Array mit mehreren Ebenen iteriert. Das Beispiel nutzt mehrere Klauseln zum Aufheben der Verschachtelung, um in die innersten Arrays zu iterieren. Das AS-Array f.multi_level_array iteriert über multi_level_array. Das Array-AS-Element ist die Iteration über die Arrays innerhalb von 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)

Weitere Informationen über die FROM-Klausel finden Sie unter FROM-Klausel.

Entpivotieren von Objekten

Um Objekte zu entpivotieren, verwendet Amazon Redshift die PartiQL-Syntax, um über SUPER-Objekte zu iterieren. Dazu verwendet es die FROM-Klausel einer Abfrage mit dem Schlüsselwort UNPIVOT. Die folgende Abfrage iteriert über das Objekt 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)

Wie beim Aufheben der Verschachtelung ist die Syntax zum Entpivotieren eine Erweiterung der FROM-Klausel. Der Unterschied ist, dass die Syntax zum Entpivotieren das Schlüsselwort UNPIVOT verwendet, um anzuzeigen, dass es über ein Objekt anstelle eines Arrays iteriert. Es verwendet das AS value_alias zur Iteration über alle Werte innerhalb eines Objekts und das AT attribute_alias zum Iterieren über alle Attribute.

Amazon Redshift unterstützt auch das Entpivotieren von Objekten und das Aufheben der Verschachtelung von Arrays in einer einzigen FROM-Klausel:

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;

Wenn Sie das Entpivotieren von Objekten verwenden, unterstützt Amazon Redshift kein korreliertes Entpivotieren. Angenommen den Fall, es gibt mehrere Beispiele für Entpivotieren in verschiedenen Abfrageebenen und das innere Entpivotieren verweist auf das äußere. Amazon Redshift unterstützt diese Art von mehrfachem Entpivotieren nicht.

Weitere Informationen über die FROM-Klausel finden Sie unter FROM-Klausel. Beispiele, die Abfragen für strukturierte Daten mit PIVOT und UNPIVOT veranschaulichen, finden Sie unter Beispiele für PIVOT und UNPIVOT.

Dynamische Typisierung

Die dynamische Eingabe erfordert keine explizite Umwandlung von Daten, die aus den Punkt- und Klammerpfaden extrahiert werden. Amazon Redshift verwendet die dynamische Typisierung, um schemalose SUPER-Daten zu verarbeiten, ohne dass die Datentypen deklariert werden müssen, bevor Sie sie in Ihrer Abfrage verwenden. Bei der dynamischen Typisierung werden die Ergebnisse der Navigation in SUPER-Datenspalten verwendet, ohne sie explizit in Amazon-Redshift-Typen umwandeln zu müssen. Dynamische Typisierung ist am nützlichsten in Joins und GROUP-BY-Klauseln. Im folgenden Beispiel wird eine SELECT-Anweisung verwendet, die keine explizite Umwandlung der Punkt- und Klammerausdrücke in die üblichen Amazon-Redshift-Typen erfordert. Informationen zur Typkompatibilität und Konvertierung finden Sie unter Kompatibilität von Typen und Umwandlung zwischen Typen.

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

Das Gleichheitszeichen in dieser Abfrage wird als true evaluiert, wenn c_orders[0].o_orderstatus die Zeichenfolge ‘P’. In allen anderen Fällen wird das Gleichheitszeichen als false evaluiert, einschließlich der Fälle, in denen die Argumente der Gleichheit unterschiedliche Typen sind.

Dynamische und statische Typisierung

Ohne dynamische Typisierung können Sie nicht bestimmen, ob c_orders[0].o_orderstatus eine Zeichenfolge, eine Ganzzahl oder eine Struktur ist. Sie können nur feststellen, dass c_orders[0].o_orderstatus ein SUPER-Datentyp ist, bei dem es sich um einen Amazon-Redshift-Skalar, ein Array oder eine Struktur handeln kann. Der statische Typ von c_orders[0].o_orderstatus ist ein SUPER-Datentyp. Üblicherweise ist ein Typ implizit ein statischer Typ in SQL.

Amazon Redshift verwendet dynamische Typisierung für die Verarbeitung von schemalosen Daten. Wenn die Abfrage die Daten auswertet, erweist sich c_orders[0].o_orderstatus als ein bestimmter Typ. Beispielsweise kann die Auswertung von c_orders[0].o_orderstatus auf dem ersten Datensatz von customer_orders_lineitem zu einer Ganzzahl führen. Die Auswertung des zweiten Datensatzes kann zu einer Zeichenfolge führen. Dies sind die dynamischen Typen des Ausdrucks.

Wenn Sie einen SQL-Operator oder eine SQL-Funktion mit Punkt- und Klammerausdrücken verwenden, die dynamische Typen haben, erzeugt Amazon Redshift ähnliche Ergebnisse wie bei der Verwendung des Standard-SQL-Operators bzw. der Standard-SQL-Funktion mit den jeweiligen statischen Typen. Wenn in diesem Beispiel der dynamische Typ des Pfadausdrucks eine Zeichenfolge ist, ist der Vergleich mit der Zeichenfolge „P“ sinnvoll. Immer wenn der dynamische Typ von c_orders[0].o_orderstatus ein anderer Datentyp außer eine Zeichenfolge ist, gibt die Gleichheit false zurück. Andere Funktionen geben null zurück, wenn falsch eingegebene Argumente verwendet werden.

Im folgenden Beispiel wird die vorherige Abfrage mit statischer Eingabe geschrieben:

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;

Beachten Sie die folgende Unterscheidung zwischen Gleichheitsprädikaten und Vergleichsprädikaten. Wenn Sie im vorherigen Beispiel das Gleichheitsprädikat durch ein less-than-or-equal Prädikat ersetzen, erzeugt die Semantik null statt false.

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

Wenn in diesem Beispiel c_orders[0].o_orderstatus eine Zeichenfolge ist, gibt Amazon Redshift true zurück, wenn sie alphabetisch gleich oder kleiner als „P“ ist. Amazon Redshift gibt false zurück, wenn sie alphabetisch größer als „P“ ist. Wenn c_orders[0].o_orderstatus jedoch keine Zeichenfolge ist, gibt Amazon Redshift null zurück, da Amazon Redshift Werte verschiedener Typen nicht vergleichen kann, wie in der folgenden Abfrage dargestellt:

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;

Dynamische Typisierung schließt keine Vergleiche von Typen aus, die minimal vergleichbar sind. Beispielsweise können Sie Amazon-Redshift-Skalartypen von sowohl CHAR als auch VARCHAR in SUPER konvertieren. Sie sind als Zeichenfolgen vergleichbar, einschließlich des Ignorierens nachstehender Leerzeichen, ähnlich wie bei CHAR- und VARCHAR-Typen von Amazon Redshift. In ähnlicher Weise sind Ganzzahlen, Dezimalzahlen und Gleitkommawerte als SUPER-Werte vergleichbar. Speziell für Dezimalspalten kann jeder Wert auch einen anderen Maßstab haben. In Amazon Redshift gelten sie weiterhin als dynamische Typen.

Amazon Redshift unterstützt auch die Gleichheit von Objekten und Arrays, die als deep equal ausgewertet werden, z. B. die ausführliche Auswertung von Objekten oder Arrays und der Vergleich aller Attribute. Verwenden Sie deep equal mit Vorsicht, da die Durchführung zeitaufwendig sein kann.

Verwenden der dynamischen Typisierung für Joins

Bei Joins passt die dynamische Typisierung automatisch Werte mit unterschiedlichen dynamischen Typen an, ohne eine lange CASE-WHEN-Analyse durchzuführen, um herauszufinden, welche Datentypen möglicherweise angezeigt werden. Nehmen wir beispielsweise an, dass Ihre Organisation das Format geändert hat, das sie für Teileschlüssel verwendet hat.

Die ursprünglichen ganzzahligen Teilschlüssel werden durch Zeichenfolgen-Teilschlüssel ersetzt, wie „A55“, und später wieder durch Array-Teilschlüssel ersetzt, wie ['X', 10], die eine Zeichenfolge und eine Zahl kombinieren. Amazon Redshift muss keine langwierige Fallanalyse zu Teileschlüsseln durchführen und kann Joins verwenden, wie im folgenden Beispiel gezeigt.

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;

Das folgende Beispiel zeigt, wie komplex und ineffizient dieselbe Abfrage ohne dynamische Typisierung sein kann:

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-Semantik

Standardmäßig geben Navigationsvorgänge für SUPER-Werte null zurück, anstatt einen Fehler zurückzugeben, wenn die Navigation ungültig ist. Die Objektnavigation ist ungültig, wenn der SUPER-Wert kein Objekt ist oder wenn der SUPER-Wert ein Objekt ist, aber nicht den Attributnamen enthält, der in der Abfrage verwendet wird. Die folgende Abfrage greift beispielsweise auf einen ungültigen Attributnamen in der SUPER-Datenspalte cdata zu:

SELECT c.c_orders.something FROM customer_orders_lineitem c;

Die Array-Navigation gibt null zurück, wenn der SUPER-Wert kein Array ist oder der Array-Index außerhalb der Grenzen liegt. Die folgende Abfrage gibt null zurück, da c_orders[1][1] außerhalb der Grenzen liegt.

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

Lax-Semantik ist besonders nützlich, wenn dynamische Typisierung verwendet wird, um einen SUPER Wert zu konvertieren. Wenn ein SUPER Wert in den falschen Typ umgewandelt wird, wird null anstelle eines Fehlers zurückgegeben, wenn die Umwandlung ungültig ist. Die folgende Abfrage gibt beispielsweise null zurück, da sie den Zeichenfolgenwert 'Good' des Objektattributs o_orderstatus nicht in INTEGER umwandeln kann. Amazon Redshift gibt einen Fehler für eine Umwandlung von VARCHAR zu INTEGER zurück, aber nicht für eine SUPER-Umwandlung.

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

Arten der Introspektion

SUPER-Datenspalten unterstützen Inspektionsfunktionen, die den dynamischen Typ und andere Typinformationen über den SUPER Wert zurückgeben. Das gängigste Beispiel ist die Skalarfunktion JSON_TYPEOF, die einen VARCHAR mit den Werten „boolean“, „number“, „string“, „object“, „array“ oder „null“ zurückgibt, abhängig vom dynamischen Typ des SUPER-Wertes. Amazon Redshift unterstützt die folgenden booleschen Funktionen für SUPER-Datenspalten:

• DECIMAL_PRECISION

• DECIMAL_SCALE

• IS_ARRAY

• IS_BIGINT

• IS_CHAR

• IS_DECIMAL

• IS_FLOAT

• IS_INTEGER

• IS_OBJECT

• IS_SCALAR

• IS_SMALLINT

• IS_VARCHAR

• JSON_TYPEOF

Alle diese Funktionen geben false zurück, wenn der Eingabewert null ist. IS_SCALAR, IS_OBJECT und IS_ARRAY schließen sich gegenseitig aus und decken alle möglichen Werte mit Ausnahme von null ab.

Um die den Daten entsprechenden Typen abzuleiten, verwendet Amazon Redshift die Funktion JSON_TYPEOF, die den Typ (die oberste Ebene) des SUPER-Werts zurückgibt, wie im folgenden Beispiel gezeigt:

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 sieht dies als eine einzelne lange Zeichenfolge, ähnlich dem Einfügen dieses Werts in eine VARCHAR-Spalte anstelle eines SUPER. Da die Spalte SUPER ist, ist die einzelne Zeichenfolge immer noch ein gültiger SUPER-Wert und der Unterschied wird in JSON_TYPEOF notiert:

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;

Order by (Sortieren nach)

Amazon Redshift definiert keine SUPER-Vergleiche zwischen Werten mit verschiedenen dynamischen Typen. Ein SUPER-Wert, der eine Zeichenfolge ist, ist weder kleiner noch größer als ein SUPER-Wert, der eine Zahl ist. Um ORDER-BY-Klauseln mit SUPER-Spalten zu verwenden, definiert Amazon Redshift eine Gesamtsortierung zwischen verschiedenen Typen, die beachtet werden müssen, wenn Amazon Redshift SUPER-Werte mithilfe von ORDER-BY-Klauseln anordnet. Die Reihenfolge zwischen dynamischen Typen ist boolesch, Zahl, Zeichenfolge, Array, Objekt. Das folgende Beispiel zeigt die Reihenfolge der verschiedenen Typen:

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)

Weitere Informationen über die ORDER-BY-Klausel finden Sie unter ORDER BY-Klausel.