Amazon Redshift unterstützt UDFs ab Patch 198 nicht mehr die Erstellung von neuem Python. Das bestehende Python UDFs wird bis zum 30. Juni 2026 weiterhin funktionieren. Weitere Informationen finden Sie im [Blog-Posting](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). 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 einzelnen Einheit zusammengefügt sind, z. B. Arrays, Strukturen oder Objekte. **Topics** + [-Übersicht](#tutorial-nested-data-overview) + [Schritt 1: Erstellen einer externen Tabelle mit verschachtelten Daten](#tutorial-nested-data-create-table) + [Schritt 2: Abfragen Ihrer verschachtelten Daten in Amazon S3 mit SQL-Erweiterungen](#tutorial-query-nested-data-sqlextensions) + [Anwendungsfälle für verschachtelte Daten](nested-data-use-cases.md) + [Einschränkungen bei verschachtelten Daten (Vorschau)](nested-data-restrictions.md) + [Serialisieren komplexer verschachtelter JSON-Datentypen](serializing-complex-JSON.md) ## -Übersicht Amazon Redshift Spectrum unterstützt das Abfragen verschachtelter Daten in den Dateiformaten ORC, 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 kein einziges Stammelement vorhanden ist, stellt jedes JSON-Objekt in diesen Stichprobendaten 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](c-getting-started-using-spectrum.md), bevor Sie fortfahren. Um ein externes Schema zu erstellen, ersetzen Sie den ARN der IAM-Rolle im folgenden Befehl durch den Rollen-ARN, den Sie im Abschnitt [Erstellen einer IAM-Rolle](c-getting-started-using-spectrum.md#c-getting-started-using-spectrum-create-role) festgelegt 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](https://s3.amazonaws.com/redshift-downloads/tickit/spectrum/customers/customer_file1) 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, phones array, orders array> ) 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 ORC, 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](r_CREATE_EXTERNAL_TABLE.md). 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>>>> ``` Außerdem können Sie wie für Spalte `struct` im folgenden Beispiel veranschaulicht auch `x`-Typen verschachteln. ``` x struct > > ``` ## 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 `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 > > ``` Sie können auf die Daten in `e` als `x.b.d.e` zugreifen. ### 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 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 Die folgende Abfrage wählt Kunden IDs - und Auftragsversanddaten für Kunden aus, die Bestellungen haben. 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 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, phones map, orders array> ) 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, phones array>, orders array> ) 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'; ``` **Anmerkung** Der `key`-Wert für ein `map`-Element ist bei den Dateitypen Ion und JSON vom Typ `string`.