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.

Tutorial: Abfragen verschachtelter Daten mit Amazon Redshift Spectrum

Dieses Tutorial zeigt, wie Sie verschachtelte Daten mit Redshift Spectrum abfragen. Verschachtelte Daten sind Daten, die verschachtelte Felder enthalten. Verschachtelte Felder sind Felder, die zu einer Einheit zusammengefügt sind, z. B. Arrays, Strukturen oder Objekte.

Übersicht

Amazon Redshift Spectrum unterstützt das Abfragen verschachtelter Daten in den Dateiformaten ParquetORC,JSON, und Ion. Redshift Spectrum greift mittels externer Tabellen auf die Daten zu. Sie können externe Tabellen erstellen, die die komplexen Datentypen struct, array und map verwenden.

Angenommen, Ihre Datendatei enthält die folgenden Daten in Amazon S3 in einem Ordner mit dem Namen customers. Obwohl es kein einzelnes Stammelement gibt, stellt jedes JSON Objekt in diesen Beispieldaten eine Zeile in einer Tabelle dar.

{"id": 1,
 "name": {"given": "John", "family": "Smith"},
 "phones": ["123-457789"],
 "orders": [{"shipdate": "2018-03-01T11:59:59.000Z", "price": 100.50},
            {"shipdate": "2018-03-01T09:10:00.000Z", "price": 99.12}]
}
{"id": 2,
 "name": {"given": "Jenny", "family": "Doe"},
 "phones": ["858-8675309", "415-9876543"],
 "orders": []
}
{"id": 3,
 "name": {"given": "Andy", "family": "Jones"},
 "phones": [],
 "orders": [{"shipdate": "2018-03-02T08:02:15.000Z", "price": 13.50}]
}  

Sie können mithilfe von Amazon Redshift Spectrum verschachtelte Daten in Dateien abfragen. Das folgende Tutorial veranschaulicht die Vorgehensweise hierfür mit Apache-Parquet-Daten.

Voraussetzungen

Wenn Sie noch nicht mit Redshift Spectrum vertraut sind, befolgen Sie die Schritte unter Erste Schritte mit Amazon Redshift Spectrum, bevor Sie fortfahren.

Um ein externes Schema zu erstellen, ersetzen Sie die IAM Rolle ARN im folgenden Befehl durch die Rolle, die ARN Sie unter IAMRolle erstellen erstellt haben. Führen Sie dann den Befehl in Ihrem SQL Client aus.

create external schema spectrum 
from data catalog 
database 'myspectrum_db' 
iam_role 'arn:aws:iam::123456789012:role/myspectrum_role'
create external database if not exists;

Schritt 1: Erstellen einer externen Tabelle mit verschachtelten Daten

Sie können die Quelldaten anzeigen, indem Sie sie von Amazon S3 herunterladen.

Um die externe Tabelle für dieses Tutorial zu erstellen, führen Sie den folgenden Befehl aus.

CREATE EXTERNAL TABLE spectrum.customers (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones array<varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';     

Im voranstehenden Beispiel verwendet die externe Tabelle spectrum.customers die Datentypen struct und array, um Spalten mit verschachtelten Daten zu definieren. Amazon Redshift Spectrum unterstützt das Abfragen verschachtelter Daten in den Dateiformaten ParquetORC,JSON, und Ion. Der Parameter STORED AS für Apache-Parquet-Dateien lautet PARQUET. Der Parameter LOCATION muss auf den Amazon S3-Ordner verweisen, der die verschachtelten Daten oder Dateien enthält. Weitere Informationen finden Sie unter CREATE EXTERNAL TABLE.

Sie können die Typen array und struct auf jeder beliebigen Ebene verschachteln. So können Sie z. B. eine Spalte mit dem Namen toparray definieren; siehe folgendes Beispiel.

toparray array<struct<nestedarray:
         array<struct<morenestedarray: 
         array<string>>>>>

Außerdem können Sie wie für Spalte struct im folgenden Beispiel veranschaulicht auch x-Typen verschachteln.

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

Schritt 2: Fragen Sie Ihre verschachtelten Daten in Amazon S3 mit Erweiterungen ab SQL

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

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 c.name.given zugegriffen. Auf den Nachnamen wird über den langen Pfad c.name.family zugegriffen.

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.

Erweiterung 2: Zugriff auf Arrays in einer Klausel FROM

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 nach Position verweisen, also beispielsweise c.orders[0] angeben (Vorschau).

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

Mit der folgenden Abfrage werden Kunden IDs - und Auftragslieferdaten für Kunden ausgewählt, die Bestellungen haben. Die SQL Erweiterung in der FROM Klausel c.orders o hängt vom Alias abc.

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 ähnelt der SQL Standardsemantik. 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 

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 die Reihenfolge der Spalten jedoch soNULL, wie im folgenden Beispiel für Jenny Doe gezeigt.

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
 1  |  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 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.customers2 (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones map<varchar(20), varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';

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.customers3 (
  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>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';   

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';