Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

Abfragen von halbstrukturierten Daten

Mit Amazon Redshift können Sie neben Ihren strukturierten Daten auch semistrukturierte 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. 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. Außerdem können die FROM Klauselelemente über Arrays iteriert und für Operationen verwendet werden, die nicht verschachtelt sind. Im Folgenden finden Sie Beschreibungen der verschiedenen Abfragemuster, die die Verwendung des SUPER Datentyps mit Pfad- und Arraynavigation, Entverschachtelung, Entpivotierung und Verknüpfungen kombinieren.

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

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 beispielsweise davon ausgegangen, dass es sich bei der c_orders SUPER Datenspalte um ein Array mit einer Struktur und einem benannten Attribut handelt. o_orderkey

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 werden die Klammern- und Punktnavigation sowohl in der GROUP ORDER BY- als auch in der BY-Klausel 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

Um Abfragen zu entfernen, verwendet Amazon Redshift die PartiQL-Syntax, um über Arrays zu iterieren. SUPER Dazu navigiert es mithilfe der Klausel einer Abfrage durch das Array. FROM 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 Syntax ohne Verschachtelung ist eine Erweiterung der Klausel. FROM Standardmäßig x (AS) y bedeutet die FROM KlauselSQL, dass über jedes Tupel in y Relation iteriert wird. x In diesem Fall bezieht sich x auf eine Beziehung und y bezieht sich auf einen Alias für Beziehung x. In ähnlicher Weise x (AS) y bedeutet die PartiQL-Syntax für das Aufheben von Verschachtelungen mithilfe des FROM Klauselelements, dass über jeden (SUPER) -Wert im (SUPER) -Array-Ausdruck x y iteriert wird. In diesem Fall x ist es ein SUPER Ausdruck und ein Alias y 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 Hinweise zu der FROM Klausel finden Sie unterFROM-Klausel.

Entpivotieren von Objekten

Um das Entpivotieren von Objekten durchzuführen, verwendet Amazon Redshift die PartiQL-Syntax, um über Objekte zu iterieren. SUPER Dazu wird die FROM Klausel einer Abfrage mit dem Schlüsselwort verwendet. UNPIVOT In diesem Fall ist der Ausdruck das c.c_orders[0] Objekt. Die Beispielabfrage iteriert über jedes vom Objekt zurückgegebene Attribut.

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 Entfernen von Verschachtelungen ist auch die Syntax zum Entkippen eine Erweiterung der Klausel. FROM Der Unterschied besteht darin, dass bei der Syntax für das Entpivotieren das UNPIVOT Schlüsselwort verwendet wird, um anzuzeigen, dass über ein Objekt statt über ein Array iteriert wird. Es verwendet das AS value_alias zur Iteration über alle Werte innerhalb eines Objekts und das AT attribute_alias zum Iterieren über alle Attribute. Betrachten Sie das folgende Syntaxfragment:

UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift unterstützt die Verwendung von Object Unpivoting und Array-Unnesting in einer einzigen Klausel wie folgt: 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;

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 zu dieser Klausel finden Sie unter. FROM FROM-Klausel Beispiele, die zeigen, wie strukturierte Daten mit PIVOT und abgefragt werdenUNPIVOT, finden Sie unterBeispiele 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 dynamische Typisierung, um SUPER Daten ohne Schema zu verarbeiten, ohne dass die Datentypen deklariert werden müssen, bevor Sie sie in Ihrer Abfrage verwenden. Die dynamische Typisierung verwendet die Ergebnisse der Navigation in SUPER Datenspalten, ohne sie explizit in Amazon Redshift Redshift-Typen umwandeln zu müssen. Dynamische Typisierung ist in Joins- und GROUP BY-Klauseln am nützlichsten. Das folgende Beispiel verwendet eine SELECT Anweisung, die keine explizite Umwandlung der Ausdrücke mit Punkt und Klammern in die üblichen Amazon Redshift 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, der ein Amazon Redshift Redshift-Skalar, ein Array oder eine Struktur sein kann. Der statische Typ von c_orders [0] .o_orderstatus ist ein Datentyp. SUPER Herkömmlicherweise 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 Funktion mit Punkt- und Klammerausdrücken verwenden, die dynamische Typen haben, erzeugt Amazon Redshift ähnliche Ergebnisse wie die Verwendung eines SQL Standardoperators oder einer Standardfunktion 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. Sie können beispielsweise sowohl als auch CHAR VARCHAR Amazon Redshift Redshift-Skalartypen in konvertieren. SUPER Sie sind vergleichbar mit Zeichenketten, einschließlich der Ignorierung nachfolgender Leerzeichen, die Amazon CHAR Redshift und Typen ähneln. VARCHAR In ähnlicher Weise sind Ganzzahlen, Dezimalzahlen und Gleitkommawerte als Werte vergleichbar. SUPER 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 Verknüpfungen werden bei der dynamischen Eingabe automatisch Werte mit unterschiedlichen dynamischen Typen abgeglichen, ohne dass eine lange CASE WHEN Analyse durchgeführt werden muss, um herauszufinden, welche Datentypen auftreten können. 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 Navigationsoperationen mit SUPER Werten 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 in der Abfrage verwendeten Attributnamen enthält. 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;

Die laxe Semantik ist besonders nützlich, wenn dynamische Typisierung zur Umwandlung eines Werts verwendet wird. SUPER Das Umwandeln eines SUPER Werts in den falschen Typ gibt Null statt eines Fehlers zurück, wenn die Umwandlung ungültig ist. Die folgende Abfrage gibt beispielsweise Null zurück, weil sie den Zeichenfolgenwert 'Good' des Objektattributs o_orderstatus nicht in umwandeln kann. INTEGER Amazon Redshift gibt einen Fehler für eine Umwandlung VARCHAR zurück, INTEGER aber nicht für eine SUPER Besetzung.

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

Arten der Introspektion

SUPERDatenspalten unterstützen Inspektionsfunktionen, die den dynamischen Typ und andere Typinformationen über den SUPER Wert zurückgeben. Das gängigste Beispiel ist die TYPEOF Skalarfunktion JSON _, die je nach dynamischem Typ des Werts a VARCHAR mit den Werten Boolean, Zahl, Zeichenfolge, Objekt, Array oder Null zurückgibt. SUPER Amazon Redshift unterstützt die folgenden booleschen Funktionen für SUPER Datenspalten:

Alle diese Funktionen geben false zurück, wenn der Eingabewert null ist. IS_SCALAR, IS_ OBJECT und IS_ schließen ARRAY sich gegenseitig aus und decken alle möglichen Werte außer Null ab.

Um die Typen abzuleiten, die den Daten entsprechen, verwendet Amazon Redshift die TYPEOF Funktion JSON _, 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 betrachtet dies als eine einzelne lange Zeichenfolge, ähnlich dem Einfügen dieses Werts in eine VARCHAR Spalte statt in eine. SUPER Da es sich bei der Spalte um einen Wert handeltSUPER, ist die einzelne Zeichenfolge immer noch ein gültiger SUPER Wert, und der Unterschied wird in JSON _ vermerkt: 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;

Order by (Sortieren nach)

Amazon Redshift definiert keine SUPER Vergleiche zwischen Werten mit unterschiedlichen 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 Gesamtreihenfolge zwischen verschiedenen Typen, die eingehalten werden muss, wenn Amazon Redshift SUPER Werte mithilfe ORDER von BY-Klauseln einstuft. 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 zur BY-Klausel finden Sie ORDER unter. ORDER BY-Klausel