Schritt 2: Abfragen Ihrer verschachtelten Daten in Amazon S3 mit SQL-Erweiterungen - Amazon Redshift

Schritt 2: Abfragen Ihrer verschachtelten Daten in Amazon S3 mit SQL-Erweiterungen

Redshift Spectrum unterstützt das Abfragen komplexer array-, map- und struct-Typen durch Erweiterungen der Amazon-Redshift-SQL-Syntax.

Erweiterung 1: Zugriff auf Structs-Spalten

Sie können Daten aus struct-Spalten extrahieren. Dazu verwenden Sie eine punktierte Schreibweise, die Feldnamen in Pfade verkettet. Die folgende Abfrage gibt z. B. Vor- und Nachnamen von Kunden zurück. Auf den Vornamen wird über den langen Pfad zugegriffen c.name.given. Auf den Nachnamen wird über den langen Pfad zugegriffen c.name.family.

SELECT c.id, c.name.given, c.name.family FROM spectrum.customers c;

Die vorhergehende Abfrage gibt die folgenden Daten zurück.

id | given | family ---|-------|------- 1 | John | Smith 2 | Jenny | Doe 3 | Andy | Jones (3 rows)

Ein struct-Element kann eine Spalte eines anderen struct-Elements sein, das wiederum auf einer beliebigen anderen Ebene eine Spalte eines anderen struct-Elements sein kann. Die Pfade, die auf Spalten in solchen tief verschachtelten struct-Elementen zugreifen können willkürlich lang sein. Betrachten Sie sich z. B. die Definition der Spalte x im folgenden Beispiel.

x struct<a: string, b: struct<c: integer, d: struct<e: string> > >

Sie können auf die Daten in e als x.b.d.e zugreifen.

Anmerkung

struct-Elemente dienen nur zur Beschreibung des Pfades zu den Feldern, die sie enthalten. Sie können in einer Abfrage nicht direkt auf sie zugreifen oder ihren Inhalt mit einer Abfrage zurückgeben.

Erweiterung 2: Übergreifende Arrays in einer FROM-Klausel

Sie können Daten aus array-Spalten (und damit auch aus map-Spalten) extrahieren, indem Sie die array-Spalten in einer FROM-Klausel anstelle von Tabellennamen angeben. Die Erweiterung gilt für die FROM-Klausel der Hauptabfrage sowie auch für die FROM-Klauseln von Unterabfragen. Sie können auf array-Elemente nicht nach Position, wie z. B. c.orders[0], verweisen.

Durch das Kombinieren von übergreifenden arrays mit Joins können Sie die Verschachtelung auf verschiedene Weise aufheben, wie in den folgenden Anwendungsfällen beschrieben.

Aufheben der Verschachtelung mit Inner Joins

Die folgende Abfrage wählt Kunden-IDs und Auftragslieferdaten für Kunden aus, für die Aufträgen vorhanden sind. Die SQL-Erweiterung in der FROM-Klausel c.orders o ist vom Alias c abhängig.

SELECT c.id, o.shipdate FROM spectrum.customers c, c.orders o

Für jeden Kunden c, für den Aufträge vorhanden sind, gibt die FROM-Klausel für jeden Auftrag o des Kunden c eine Zeile zurück. Diese Zeile kombiniert die Kundenzeile c und die Auftragszeile o. Die SELECT-Klausel behält dann nur c.id und o.shipdate bei. Dies führt zu folgendem Ergebnis.

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)

Der Alias c bietet Zugriff auf die Kundenfelder, und der Alias o bietet Zugriff auf die Auftragsfelder.

Die Semantik ist mit Standard-SQL vergleichbar. Sie können sich die FROM-Klausel so vorstellen, als würde die folgende verschachtelte Schleife ausgeführt werden, gefolgt von SELECT zur Auswahl der auszugebenden Felder.

for each customer c in spectrum.customers for each order o in c.orders output c.id and o.shipdate

Daher erscheinen Kunden, für die keine Aufträge vorhanden sind, nicht im Ergebnis.

Sie können sich das auch so vorstellen, als ob die FROM-Klausel einen JOIN mit der customers-Tabelle und dem orders-Array durchführen wurde. Sie können die Abfrage daher auch wie im folgenden Beispiel veranschaulicht schreiben.

SELECT c.id, o.shipdate FROM spectrum.customers c INNER JOIN c.orders o ON true
Anmerkung

Wenn ein Schema mit dem Namen c mit einer Tabelle mit dem Namen orders vorhanden ist, dann bezieht sich c.orders auf die Tabelle orders und nicht auf die Array-Spalte von customers.

Aufheben der Verschachtelung mit Left Joins

Die folgende Abfrage gibt alle Kundennamen und deren Aufträge aus. Für Kunden, die keinen Auftrag erteilt haben, wird dennoch der Kundenname zurückgegeben. In diesem Fall ist der Wert der Auftragsspalten jedoch NULL, wie im folgenden Beispiel für Jenny Doe veranschaulicht.

SELECT c.id, c.name.given, c.name.family, o.shipdate, o.price FROM spectrum.customers c LEFT JOIN c.orders o ON true

Die vorhergehende Abfrage gibt die folgenden Daten zurück.

id | given | family | shipdate | price ----|---------|---------|----------------------|-------- 1 | John | Smith | 2018-03-01 11:59:59 | 100.5 2 | 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)

Erweiterung 3: Direkter Zugriff auf ein Array von Skalaren über einen Alias

Wenn sich der Alias p in einer FROM-Klausel über ein Array von Skalaren erstreckt, bezeichnet die Abfrage die p-Werte einfach als p. Die folgende Abfrage ergibt z. B. Paare aus Kundennamen und Telefonnummern.

SELECT c.name.given, c.name.family, p AS phone FROM spectrum.customers c LEFT JOIN c.phones p ON true

Die vorhergehende Abfrage gibt die folgenden Daten zurück.

given | family | phone -------|----------|----------- John | Smith | 123-4577891 Jenny | Doe | 858-8675309 Jenny | Doe | 415-9876543 Andy | Jones | (4 rows)

Erweiterung 4: Zugriff auf Zuordnungselemente

Redshift Spectrum verarbeitet den map-Datentyp als einen array-Typ, der struct-Typen mit einer key-Spalte und einer value-Spalte enthält. key muss vom Typ scalar sein; der Wert kann ein beliebiger Datentyp sein.

Der folgende Code erstellt z. B. eine externe Tabelle mit einem map-Element zum Speichern von Telefonnummern.

CREATE EXTERNAL TABLE spectrum.customers ( id int, name struct<given:varchar(20), family:varchar(20)>, phones map<varchar(20), varchar(20)>, orders array<struct<shipdate:timestamp, price:double precision>> )

Da sich ein map-Typ wie ein array-Typ mit den Spalten key und value verhält, können Sie sich die vorhergehenden Schemen so vorstellen, als wären sie die folgenden Schemen.

CREATE EXTERNAL TABLE spectrum.customers ( 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>> )

Die folgende Abfrage gibt die Namen von Kunden mit Mobiltelefonnummer und die Nummer für jeden Namen zurück. Die Zuordnungsabfrage wird genauso wie die Abfrage eines verschachtelten array aus struct-Typen verarbeitet. Die folgende Abfrage gibt nur Daten zurück, wenn Sie die externe Tabelle wie zuvor beschrieben erstellt haben.

SELECT c.name.given, c.name.family, p.value FROM spectrum.customers c, c.phones p WHERE p.key = 'mobile'
Anmerkung

Der key-Wert für ein map-Element ist bei den Dateitypen Ion und JSON vom Typ string.