Semistrukturierte Daten abfragen

Mit Amazon Redshift können Sie neben Ihren strukturierten Daten auch halbstrukturierte Daten wie JSON, Avro oder Ion abfragen und analysieren. Semistrukturierte Daten beziehen sich auf Daten, die über ein flexibles Schema verfügen, das hierarchische oder verschachtelte Strukturen ermöglicht. In den folgenden Abschnitten wird das Abfragen halbstrukturierter Daten mithilfe der Unterstützung offener Datenformate durch Amazon Redshift veranschaulicht, sodass Sie wertvolle Informationen aus komplexen Datenstrukturen gewinnen können.

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

PartiQL arbeitet mit dynamischen Typen. Dieser Ansatz ermöglicht ein intuitives Filtern, Zusammenführen und Aggregieren von strukturierten, halbstrukturierten und verschachtelten Datensätzen. 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 Informationen zu PartiQL finden Sie unterPartiQL — eine SQL-kompatible Abfragesprache für Amazon Redshift.

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. Mit der folgenden Anweisung wird beispielsweise nur das dritte Element in einem Array ausgewählt, das eine Ebene tief in einem SUPER-Objekt verschachtelt ist:

SELECT super_object.array[2];
         
 array
---------------
 third_element

Sie können die Notation mit Punkt und Klammern verwenden, wenn Sie Datenoperationen wie Filtern, Zusammenfügen und Aggregieren ausführen. Sie können diese Notationen überall in einer Abfrage verwenden, wo es normalerweise Spaltenverweise gibt. Die folgende Anweisung wählt beispielsweise die Anzahl der Ereignisse mit dem Typ UPDATED aus.

SELECT COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0]."eventType" = 'UPDATED';
         
 eventType | count
-----------+-------
 "UPDATED" | 1    

Ausführlichere Beispiele für die Verwendung der PartiQL-Navigation finden Sie unterBeispiele für die Verwendung halbstrukturierter Daten in Amazon Redshift.

Aufheben der Verschachtelung von Abfragen

Um Abfragen zu entfernen, bietet Amazon Redshift zwei Möglichkeiten, über SUPER-Arrays zu iterieren: PartiQL-Syntax und die UNNEST-Operation in der FROM-Klausel. Beide Methoden zum Entfernen von Verschachtelungen führen zu derselben Ausgabe. Hinweise zur Operation UNNEST finden Sie unter. FROM-Klausel Beispiele für die Verwendung des UNNEST-Vorgangs finden Sie unter. UNNEST-Beispiele

Amazon Redshift kann in SUPER-Arrays mithilfe der PartiQL-Syntax in der FROM-Klausel einer Abfrage navigieren. Das folgende Beispiel nutzt das vorherige Beispiel und iteriert über die Attributwerte für c_orders.

SELECT orders.*, o FROM customer_orders orders, orders.c_orders o;

Die PartiQL-Syntax für das Aufheben von Verschachtelungen mithilfe des FROM-Klauselelements x (AS) y bedeutet, dass über jeden (SUPER-) Wert im (SUPER-) Array-Ausdruck x y iteriert wird. 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 folgenden Beispiel customer_orders_lineitem c ist die Iteration über die customer_order_lineitem Basistabelle und die Iteration über c.c_orders o das Array. c.c_orders Um über das o_lineitems Attribut zu iterieren, bei dem es sich um ein Array innerhalb eines Arrays handelt, können Sie mehrere Klauseln wie folgt hinzufügen:

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 ist ein Beispiel für die Iteration über ein skalares Array.

CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data;

SELECT element, index 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;

 element | array
---------+---------
 1.1     | [1.1,1.2]
 1.2     | [1.1,1.2]
 2.1     | [2.1,2.2] 
 2.2     | [2.1,2.2]
 3.1     | [3.1,3.2] 
 3.2     | [3.1,3.2] 
(6 rows)

Weitere Informationen über die FROM-Klausel finden Sie unter FROM-Klausel. Weitere Beispiele für das Aufheben von Verschachtelungen bei SUPER-Abfragen finden Sie unter. Beispiele für die Verwendung halbstrukturierter Daten in Amazon Redshift

Entpivotieren von Objekten

Um Objekte zu entpivotieren, verwendet Amazon Redshift die PartiQL-Syntax, um über SUPER-Objekte zu iterieren. Dazu wird die FROM-Klausel einer Abfrage zusammen mit dem Schlüsselwort UNPIVOT verwendet. Im folgenden Beispiel ist der Ausdruck das c.c_orders[0] Objekt. In der Beispielabfrage wird jedes vom Objekt zurückgegebene Attribut wiederholt.

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. Sie verwendet den AS value_alias für die Iteration über alle Werte innerhalb eines Objekts und den AT attribute_alias für die Iteration über alle Attribute. Beachten Sie die folgende Syntax:

UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift unterstützt die Verwendung von Object Unpivoting und Array-Unnesting in einer einzigen FROM-Klausel wie folgt:

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 für die Verwendung von Pivoting mit dem Typ SUPER finden Sie unter. Beispiele für die Verwendung halbstrukturierter Daten in Amazon Redshift

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.

Sehen Sie sich das folgende Beispiel an, das nach Zeilen sucht, in denen der Status einer Bestellung lautetshipped:

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

Das Gleichheitszeichen in dieser Beispielabfrage ergibt, true wenn der Wert von c_orders [0] .o_orderstatus die Zeichenfolge 'versendet' ist. 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. Wenn der Bestellstatus beispielsweise eine Ganzzahl ist, wird die zugehörige Zeile nicht ausgewählt.

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 Prädikat ersetzen, less-than-or-equal ergibt die Semantik Null statt Falsch.

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 für Teilschlüssel 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;

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.

Ein Beispiel für die Verwendung von GROUP BY und ORDER BY in einer SUPER-Abfrage finden Sie unterFiltern von halbstrukturierten Daten.