Amazon Redshift tidak akan lagi mendukung penggunaan Python UDF setelah 30 Juni 2026. Kami akan mulai menegakkannya secara bertahap. Untuk informasi lebih lanjut tentang detail opsi akhir masa pakai dan migrasi Python, lihat posting blog
Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.
Contoh penggunaan data semi-terstruktur di Amazon Redshift
Contoh berikut menunjukkan cara bekerja dengan data semi-terstruktur di Amazon Redshift menggunakan sintaks PartiQL. Anda akan membuat tabel sampel untuk memuat kumpulan sampel data semi-terstruktur, kemudian menanyakan objek data semi-terstruktur dalam berbagai kasus penggunaan.
catatan
Kami menyarankan Anda mengatur opsi enable_case_sensitive_super_attribute konfigurasi ke true sebelum bekerja dengan tipe data SUPER. Untuk informasi selengkapnya, lihat enable_case_sensitive_super_atribut.
Memuat data semi-terstruktur
Pernyataan berikut membuat tabel sampel dan memuat objek JSON sampel ke kolom all_data SUPER.
DROP TABLE IF EXISTS test_json; SET enable_case_sensitive_super_attribute 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" } ] } '));
Mengkueri data semi-terstruktur bersarang
Pernyataan berikut menggunakan notasi titik PartiQL untuk mengekstrak pnrid bidang, yang bersarang tiga tingkat jauh di dalam objek tingkat atasall_data.
select all_data.data.pnr.pnrid::varchar from test_json;pnrid -------------------- 123PQRS-2024-09-20
Pernyataan berikut menggunakan notasi braket PartiQL untuk menentukan dan mengekstrak hanya elemen pertama dari events array yang bersarang di dalam objek tingkat atas.
SELECT all_data.data.pnr.events[0] FROM test_json;events --------------------------------- { "eventType":"UPDATED", "type":"PART", "id":"123PQRS-2024-09-20-HO-1" }
Pernyataan berikut mengekstrak eventType properti hanya elemen yang ditentukan dari events array.
SELECT all_data.data.pnr.events[0].eventType FROM test_json;eventtype ----------- "UPDATED"
Pernyataan berikut
Menggunakan enable_case_sensitive_super_atribut dengan data semi-terstruktur
Contoh berikut menunjukkan bagaimana opsi enable_case_sensitive_super_atribut konfigurasi mempengaruhi kueri data semi-terstruktur. Untuk informasi selengkapnya, lihat Mengakses bidang JSON dengan nama atau atribut bidang huruf besar dan huruf campuran.
Dalam pernyataan berikut, mengatur ulang opsi konfigurasi ke default false membuat kueri mengembalikan NULL untuk nama atribut kasus campuran.
RESET enable_case_sensitive_super_attribute; SELECT all_data.data.pnr.events[0].eventType FROM test_json;eventtype ----------- NULL
Dalam contoh berikut, kueri sampel mengembalikan hasil yang diinginkan setelah Anda menyetel enable_case_sensitive_super_attribute ke true. Tidak diperlukan tanda kutip ganda di sekitar nama atribut.
SET enable_case_sensitive_super_attribute TO true; SELECT all_data.data.pnr.events[0].eventType FROM test_json;eventtype ----------- "UPDATED"
Memfilter data semi-terstruktur
Pernyataan berikut menggunakan sintaks PartiQL dalam klausa WHERE dari pernyataan yang menghitung peristiwa jenis UPDATED untuk mengambil data atribut tertentu dari dalam array. Anda dapat menggunakan sintaks ini di bagian mana pun dari kueri di mana Anda biasanya akan merujuk kolom.
SELECT COUNT(*) FROM test_json WHERE all_data.data.pnr.events[0].eventType = 'UPDATED';count ------ 1
Contoh berikut menggunakan sintaks tanda kurung dan titik PartiQL di klausa GROUP BY dan ORDER BY.
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
Data semi-terstruktur yang tidak bersarang
Pernyataan berikut menggunakan gabungan PartiQL untuk menguji array. events Perhatikan bahwa gabungan ini berfungsi bahkan ketika jumlah indeks untuk array tidak statis.
Untuk contoh data semi-terstruktur yang tidak bersarang menggunakan UNNEST dalam klausa FROM, lihat. Contoh UNNEST
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
Array bersarang yang tidak bersarang
Pernyataan berikut menggunakan gabungan PartiQL untuk menguji array yang bersarang di dalam array lain.
Untuk contoh data semi-terstruktur yang tidak bersarang menggunakan UNNEST dalam klausa FROM, lihat. Contoh UNNEST
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
Menggunakan data semi-terstruktur dalam subquery
Pernyataan berikut menggunakan subquery dalam klausa WHERE untuk mengembalikan hanya subbagian dari hasil dari contoh sebelumnya.
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
Menggabungkan kueri menggunakan data semi-terstruktur
Pernyataan berikut menggunakan fungsi COUNT untuk menggabungkan jumlah elemen dalam PendingService array.
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
Menggunakan data semi-terstruktur dalam tampilan terwujud
Pernyataan berikut menggunakan pernyataan dari contoh sebelumnya untuk membuat tampilan yang terwujud. Tampilan terwujud secara otomatis menyegarkan jumlah layanan yang tertunda ketika tabel dasar mendapatkan data baru.
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;
Menggunakan PIVOT dan UNPIVOT dengan data semi-terstruktur
Pernyataan berikut menggunakan PIVOT pada partname kolom untuk mengembalikan harga rata-rata setiap bagian.
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
Pada contoh sebelumnya, hasilnya diubah menjadi kolom. Contoh berikut menunjukkan kueri GROUP BY yang mengembalikan harga rata-rata dalam baris, bukan dalam kolom.
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
Berikut ini adalah contoh PIVOT manufacturer dengan kolom implisit.
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
Berikut ini adalah contoh UNPIVOT pada quality kolom.
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
