Amazon Redshift wird UDFs ab dem 1. November 2025 die Erstellung von neuem Python nicht mehr unterstützen. Wenn Sie Python verwenden möchten UDFs, erstellen Sie das UDFs vor diesem Datum liegende. Bestehendes Python UDFs wird weiterhin wie gewohnt funktionieren. Weitere Informationen finden Sie im Blogbeitrag
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.
Beispiele für die Verwendung halbstrukturierter Daten in Amazon Redshift
Die folgenden Beispiele zeigen, wie Sie mit halbstrukturierten Daten in Amazon Redshift mithilfe der PartiQL-Syntax arbeiten. Sie erstellen eine Beispieltabelle, um einen Beispielsatz halbstrukturierter Daten zu laden, und fragen dann halbstrukturierte Datenobjekte in einer Vielzahl von Anwendungsfällen ab.
Anmerkung
Wir empfehlen, dass Sie die enable_case_sensitive_identifier und enable_case_sensitive_super_attribute Konfigurationsoptionen festlegen, bevor Sie mit dem SUPER-Datentyp arbeiten. Weitere Informationen erhalten Sie unter enable_case_sensitive_identifier und enable_case_sensitive_super_attribute.
Semistrukturierte Daten werden geladen
Die folgenden Anweisungen erstellen eine Beispieltabelle und laden ein JSON-Beispielobjekt in die all_data SUPER-Spalte.
DROP TABLE IF EXISTS test_json; SET enable_case_sensitive_super_attribute TO true; SET enable_case_sensitive_identifier TO true; CREATE TABLE test_json (all_data SUPER); INSERT INTO test_json VALUES (JSON_PARSE(' { "data":{ "pnr":{ "type":"pnr", "pnrid":"123PQRS-2024-09-20", "bookingIdentifier":"123PQRS", "version":"5", "triggerType":"", "events":[ { "eventType":"UPDATED", "type":"PART", "id":"123PQRS-2024-09-20-HO-1" }, { "eventType":"CREATED", "type":"ABC", "id":"123PQRS-2024-09-20-OT-38" } ], "create":{ "pnrCreateDate":"2024-09-20T16:56:00Z", "officeID":"OFFCID1234", "officeIDCategory":"Email" }, "lastModification":{ "dateTime":"2024-09-20T17:09:00Z" }, "PARTDetails":[ { "path":"1", "TrainPARTs":[ { "PARTID":"123PQRS-2024-09-20-HO-1", "departure":{ "departureStation":"XYZ", "departureTimeLocal":"2024-10-03T06:30:00", "departureTimeGMT":"2024-10-03T10:30:00Z" }, "arrival":{ "arrivalStation":"ABC", "arrivalTimeLocal":"2024-10-03T08:20:00", "arrivalTimeGMT":"2024-10-03T15:20:00Z" }, "marketing":{ "carrierCode":"XX", "TrainNumber":"100" }, "operating":{ "carrierCode":"YY", "TrainNumber":"100-A" }, "status":"ON", "aircraft":{ "code":"222" }, "class":"WC", "first":"Y", "seating":[ ] } ] } ], "commuterInformation":[ { "commuterID":"2", "commuterPNR":"123PQRS-2024-09-20-RO-2", "commuterTypeCode":"DOM", "firstName":"JOHN", "lastName":"MILLER" } ], "contactDetail":[ { "emailContacts":[ { "id":"123PQRS-2024-09-20-OT-4", "contact":"JOHNMILLER@EXAMPLE.COM", "purpose":[ "BUSINESS" ], "commuter":[ "123PQRS-2024-09-20-RO-2" ], "language":"EN" }, { "id":"123PQRS-2024-09-20-OT-5", "contact":"HARVEYCORMIER@EXAMPLE.COM", "purpose":[ "NOTIFICATION" ], "commuter":[ "123PQRS-2024-09-20-RO-2" ], "language":"EN" } ] }, { "phoneContacts":[ { "id":"123PQRS-2024-09-20-OT-3", "contact":"1234567890", "purpose":[ "NOTIFICATION" ], "commuter":[ "123PQRS-2024-09-20-RO-2" ], "language":"" } ] }, { "addressInfo":[ { "id":"123PQRS-2024-09-20-OT-6", "addressline":[ "112 PORT STREET" ], "provinceState":"CA", "cityName":"SAN JOSE", "postalCode":"12345", "countryCode":"USA", "purpose":[ "MAILING" ], "commuter":[ "123PQRS-2024-09-20-RO-2" ] } ] } ], "PendingService":[ { "id":"123PQRS-2024-09-20-OT-26", "code":"MONO", "status":"", "text":"Broken Seat at Coach-No XYZ123 Seat-No 567", "trainCode":"WC-1", "TrainsArray":[ "123PQRS-2024-09-20-HO-1" ], "commuter":[ "123PQRS-2024-09-20-RO-2" ] }, { "id":"123PQRS-2024-09-20-OT-27", "code":"OTHS", "status":"", "text":"Broken Seat at Coach-No XYZ567 Seat-No 111", "trainCode":"WC-1", "TrainsArray":[ "123PQRS-2024-09-20-HO-1" ], "commuter":[ "123PQRS-2024-09-20-RO-2" ] }, { "id":"123PQRS-2024-09-20-OT-28", "code":"OTHS", "status":"", "text":"Broken Seat at Coach-No XYZ890 Seat-No 123", "trainCode":"WC-1", "TrainsArray":[ "123PQRS-2024-09-20-HO-1" ], "commuter":[ "123PQRS-2024-09-20-RO-2" ] }, { "id":"123PQRS-2024-09-20-OT-29", "code":"OTHS", "status":"", "text":"Broken Seat at Coach-No XYZ111 Seat-No 333", "trainCode":"WC-1", "TrainsArray":[ "123PQRS-2024-09-20-HO-1" ], "commuter":[ "123PQRS-2024-09-20-RO-2" ] } ], "parts": [ { "partname": "prop", "manufacturer": "local parts co", "quality": 2, "price": 10.00 }, { "partname": "prop", "manufacturer": "big parts co", "quality": null, "price": 9.00 }, { "partname": "prop", "manufacturer": "small parts co", "quality": 1, "price": 12.00 }, { "partname": "rudder", "manufacturer": "local parts co", "quality": 1, "price": 2.50 }, { "partname": "rudder", "manufacturer": "big parts co", "quality": 2, "price": 3.75 }, { "partname": "rudder", "manufacturer": "small parts co", "quality": null, "price": 1.90 }, { "partname": "wing", "manufacturer": "local parts co", "quality": null, "price": 7.50 }, { "partname": "wing", "manufacturer": "big parts co", "quality": 1, "price": 15.20 }, { "partname": "wing", "manufacturer": "small parts co", "quality": null, "price": 11.80 } ], "count_by_color": [ { "quality": "high", "red": 15, "green": 20, "blue": 7 }, { "quality": "normal", "red": 35, "green": null, "blue": 40 }, { "quality": "low", "red": 10, "green": 23, "blue": null } ] } }, "id":"abcdefgh-ijklmnop-qrstuvwxyz123", "mainIds":[ { "ID":"pqrstuvwxyz-aabbcc123", "Source":"NYC" } ] } '));
Abfragen verschachtelter halbstrukturierter Daten
Die folgende Anweisung verwendet die Punktnotation von PartiQL, um das pnrid Feld zu extrahieren, das drei Ebenen tief im Objekt der obersten Ebene verschachtelt ist. all_data
select all_data.data.pnr.pnrid::varchar from test_json;pnrid -------------------- 123PQRS-2024-09-20
Die folgende Anweisung verwendet die Klammernotation von PartiQL, um nur das erste Element aus dem events Array zu spezifizieren und zu extrahieren, das innerhalb des Objekts der obersten Ebene verschachtelt ist.
SELECT all_data.data.pnr.events[0] FROM test_json;events --------------------------------- { "eventType":"UPDATED", "type":"PART", "id":"123PQRS-2024-09-20-HO-1" }
Die folgende Anweisung extrahiert nur die eventType Eigenschaft des angegebenen Elements aus dem Array. events
SELECT all_data.data.pnr.events[0].eventType FROM test_json;eventtype ----------- "UPDATED"
Die folgenden Aussagen
Verwendung von enable_case_sensitive_identifier und enable_case_sensitive_super_attribute mit halbstrukturierten Daten
Die folgenden Beispiele zeigen, wie sich die Konfigurationsoptionen enable_case_sensitive_identifier enable_case_sensitive_super_attribute unterscheiden, wenn sie für die Abfrage halbstrukturierter Daten verwendet werden. Weitere Informationen zu diesen Konfigurationsoptionen finden Sie unter. Zugreifen auf JSON-Felder mit Feldnamen oder Attributen in Groß- und Kleinschreibung
In der folgenden Anweisung gibt die Abfrage NULL zurück, wenn beide Konfigurationsoptionen auf ihren Standardwert False zurückgesetzt werden.
RESET enable_case_sensitive_identifier; RESET enable_case_sensitive_super_attribute; SELECT all_data.data.pnr.events[0].eventType FROM test_json;eventtype ----------- NULL
Im folgenden Beispiel gibt die Beispielabfrage das gewünschte Ergebnis zurück, nachdem Sie die Attribute, bei denen Groß- und Kleinschreibung beachtet wird, in doppelte Anführungszeichen gesetzt und enable_case_sensitive_identifier auf true gesetzt haben.
RESET enable_case_sensitive_identifier; RESET enable_case_sensitive_super_attribute; SELECT all_data.data.pnr.events[0]."eventType" FROM test_json;eventtype ----------- NULLSET enable_case_sensitive_identifier TO true; SELECT all_data.data.pnr.events[0]."eventType" FROM test_json;eventtype ----------- "UPDATED"
Im folgenden Beispiel gibt die Beispielabfrage das gewünschte Ergebnis zurück, nachdem Sie den Wert auf true gesetzt haben, ohne die Attributeenable_case_sensitive_super_attribute, bei denen Groß- und Kleinschreibung beachtet wird, in doppelte Anführungszeichen zu setzen.
RESET enable_case_sensitive_identifier; RESET enable_case_sensitive_super_attribute; SELECT all_data.data.pnr.events[0].eventType FROM test_json;eventtype ----------- NULLSET enable_case_sensitive_super_attribute TO true; SELECT all_data.data.pnr.events[0].eventType FROM test_json;eventtype ----------- "UPDATED"
Filtern von halbstrukturierten Daten
Die folgende Anweisung verwendet die PartiQL-Syntax in der WHERE-Klausel einer Anweisung, die Ereignisse des Typs zähltUPDATED, um Daten eines bestimmten Attributs aus einem Array abzurufen. Sie können diese Syntax in jedem Teil der Abfrage verwenden, in dem Sie normalerweise auf Spalten verweisen würden.
SELECT COUNT(*) FROM test_json WHERE all_data.data.pnr.events[0].eventType = 'UPDATED';count ------ 1
Das folgende Beispiel verwendet die Klammern- und Punktsyntax von PartiQL sowohl in GROUP BY- als auch in ORDER BY-Klauseln.
SELECT all_data.data.pnr.events[0].eventType::varchar, COUNT(*) FROM test_json WHERE all_data.data.pnr.events[0].eventType IS NOT NULL GROUP BY all_data.data.pnr.events[0].eventType ORDER BY all_data.data.pnr.events[0].eventType;eventtype | count -----------+------- "UPDATED" | 1
Unverschachtelung halbstrukturierter Daten
Die folgende Anweisung verwendet PartiQL-Joins, um das Array zu entverschachteln. events Beachten Sie, dass diese Verknüpfung auch dann funktioniert, wenn die Anzahl der Indizes für das Array nicht statisch ist.
Beispiele für das Aufheben von Verschachtelungen halbstrukturierter Daten mithilfe von UNNEST in der FROM-Klausel finden Sie unter. UNNEST-Beispiele
SELECT a.all_data.data.pnr.type::varchar type_info, a.all_data.data.pnr.pnrid::varchar pnr_id , a.all_data.data.pnr.bookingIdentifier::varchar booking_id, a.all_data.data.pnr.version::varchar version_info, b.eventType::varchar event_type, b.id::varchar event_id FROM test_json a, a.all_data.data.pnr.events b;type_info | pnr_id | booking_id | version_info | event_type | event_id -----------+---------------------+------------+--------------+------------+------------------------- pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | UPDATED | 123PQRS-2024-09-20-HO-1 pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | CREATED | 123PQRS-2024-09-20-OT-38
Verschachtelung verschachtelter Arrays aufheben
Die folgende Anweisung verwendet PartiQL-Joins, um ein Array zu entverschachteln, das in einem anderen Array verschachtelt ist.
Beispiele für das Aufheben von verschachtelten halbstrukturierten Daten mithilfe von UNNEST in der FROM-Klausel finden Sie unter. UNNEST-Beispiele
SELECT a.all_data.data.pnr.type::varchar type_info, a.all_data.data.pnr.pnrid::varchar pnr_id , a.all_data.data.pnr.bookingIdentifier::varchar booking_id, a.all_data.data.pnr.version::varchar version_info, d.id::varchar email_record_id, d.contact::varchar email_contact, e::varchar email_purpose, f::varchar email_commuter FROM test_json a, a.all_data.data.pnr.contactDetail c, c."emailContacts" d, d.purpose e, d.commuter f;type_info | pnr_id | booking_id | version_info | email_record_id | email_contact | email_purpose | email_commuter -----------+---------------------+------------+--------------+-------------------------+---------------------------+---------------+------------------------- pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 123PQRS-2024-09-20-OT-4 | JOHNMILLER@EXAMPLE.COM | BUSINESS | 123PQRS-2024-09-20-RO-2 pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 123PQRS-2024-09-20-OT-5 | HARVEYCORMIER@EXAMPLE.COM | NOTIFICATION | 123PQRS-2024-09-20-RO-2
Verwendung halbstrukturierter Daten in Unterabfragen
Die folgende Anweisung verwendet eine Unterabfrage in der WHERE-Klausel, um nur einen Unterabschnitt der Ergebnisse aus dem vorherigen Beispiel zurückzugeben.
SELECT a.all_data.data.pnr.type::varchar type_info, a.all_data.data.pnr.pnrid::varchar pnr_id , a.all_data.data.pnr.bookingIdentifier::varchar booking_id, a.all_data.data.pnr.version::varchar version_info, d.id::varchar email_record_id, d.contact::varchar email_contact FROM test_json a, a.all_data.data.pnr.contactDetail c, c."emailContacts" d WHERE (SELECT COUNT(*) FROM d.purpose e WHERE e = 'BUSINESS') > 0;type_info | pnr_id | booking_id | version_info | email_record_id | email_contact | email_purpose | email_commuter -----------+---------------------+------------+--------------+-------------------------+---------------------------+---------------+------------------------- pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 123PQRS-2024-09-20-OT-4 | JOHNMILLER@EXAMPLE.COM | BUSINESS | 123PQRS-2024-09-20-RO-2
Aggregieren von Abfragen mithilfe halbstrukturierter Daten
Die folgende Anweisung verwendet die COUNT-Funktion, um die Anzahl der Elemente im Array zu aggregieren. PendingService
SELECT a.all_data.data.pnr.type::varchar type_info, a.all_data.data.pnr.pnrid::varchar pnr_id , a.all_data.data.pnr.bookingIdentifier::varchar booking_id, a.all_data.data.pnr.version::varchar version_info, COUNT(*) AS total_pending_service FROM test_json a, a.all_data.data.pnr.PendingService c GROUP BY 1,2,3,4;type_info | pnr_id | booking_id | version_info | total_pending_service -----------+--------------------+------------+--------------+----------------------- pnr | 123PQRS-2024-09-20 | 123PQRS | 5 | 4
Verwendung halbstrukturierter Daten in materialisierten Ansichten
Die folgende Anweisung verwendet die Anweisung aus dem vorherigen Beispiel, um eine materialisierte Ansicht zu erstellen. Die materialisierte Ansicht aktualisiert automatisch die Anzahl der ausstehenden Dienste, wenn die Basistabelle neue Daten erhält.
CREATE MATERIALIZED VIEW mv_total_pending_service AUTO REFRESH YES AS SELECT a.all_data.data.pnr.type::varchar type_info, a.all_data.data.pnr.pnrid::varchar pnr_id , a.all_data.data.pnr.bookingIdentifier::varchar booking_id, a.all_data.data.pnr.version::varchar version_info, COUNT(*) AS total_pending_service FROM test_json a, a.all_data.data.pnr.PendingService c GROUP BY 1,2,3,4;
Verwendung von PIVOT und UNPIVOT mit halbstrukturierten Daten
In der folgenden Anweisung wird PIVOT für die partname Spalte verwendet, um den Durchschnittspreis jedes Teils zurückzugeben.
SELECT * FROM ( SELECT c.partname::varchar, c.price FROM test_json a, a.all_data.data.pnr.parts c) PIVOT (AVG(price) for partname IN ('prop', 'rudder', 'wing'));prop | rudder | wing ------------+--------------------+-------- 10.33 | 2.71 | 11.50
Im vorherigen Beispiel werden die Ergebnisse in Spalten umgewandelt. Das folgende Beispiel zeigt eine GROUP BY-Abfrage, die die Durchschnittspreise in Zeilen und nicht in Spalten zurückgibt.
SELECT partname, avg(price) FROM ( SELECT c.partname::varchar, c.price FROM test_json a, a.all_data.data.pnr.parts c) WHERE partname IN ('prop', 'rudder', 'wing') GROUP BY partname;partname | avg ----------+------- prop | 10.33 rudder | 2.71 wing | 11.50
Es folgt ein manufacturer PIVOT-Beispiel mit einer impliziten Spalte.
SELECT * FROM ( SELECT c.quality, c.manufacturer::varchar FROM test_json a, a.all_data.data.pnr.parts c) PIVOT ( count(*) FOR quality IN (1, 2, NULL) );manufacturer | 1 | 2 | null -------------------+----+----+------ local parts co | 1 | 1 | 1 big parts co | 1 | 1 | 1 small parts co | 1 | 0 | 2
Es folgt ein UNPIVOT-Beispiel für die quality Spalte.
SELECT * FROM ( SELECT c.quality as quality FROM test_json a, a.all_data.data.pnr.parts c) UNPIVOT (cnt FOR column_header IN (quality));column_header | cnt -----------------+---- quality | 2 quality | 1 quality | 1 quality | 2 quality | 1
