Navigasi Kueri tanpa sarang Objek tidak berputar Pengetikan dinamis Case-insensitive menanyakan Semantik yang lemah Urutkan berdasarkan

Mengkueri data semi-terstruktur

Dengan Amazon Redshift, Anda dapat menanyakan dan menganalisis data semi-terstruktur, seperti JSON, Avro, atau Ion, di samping data terstruktur Anda. Semi-structured Data mengacu pada data yang memiliki skema fleksibel, memungkinkan struktur hierarkis atau bersarang. Bagian berikut menunjukkan kueri data semi-terstruktur menggunakan dukungan Amazon Redshift untuk format data terbuka, sehingga Anda dapat membuka informasi berharga dari struktur data yang kompleks.

Amazon Redshift menggunakan bahasa PartiQL untuk menawarkan SQL-compatible akses ke data relasional, semi-terstruktur, dan bersarang.

PartiQL beroperasi dengan tipe dinamis. Pendekatan ini memungkinkan pemfilteran intuitif, penggabungan, dan agregasi pada kombinasi kumpulan data terstruktur, semi-terstruktur, dan bersarang. Sintaks PartiQL menggunakan notasi bertitik dan subskrip array untuk navigasi jalur saat mengakses data bersarang. Ini juga memungkinkan item klausa FROM untuk mengulangi array dan digunakan untuk operasi unnest. Berikut ini, Anda dapat menemukan deskripsi pola kueri yang berbeda yang menggabungkan penggunaan tipe data SUPER dengan navigasi jalur dan array, un, unpivoting, dan join. Untuk informasi lebih lanjut tentang PartiQL, lihatPartiQL — bahasa SQL-compatible query untuk Amazon Redshift.

Amazon Redshift menggunakan PartiQL untuk mengaktifkan navigasi ke array dan struktur menggunakan tanda kurung [...] dan notasi titik masing-masing. Selanjutnya, Anda dapat mencampur navigasi ke dalam struktur menggunakan notasi titik dan array menggunakan notasi braket. Misalnya, pernyataan berikut hanya memilih elemen ketiga dalam array yang bersarang satu tingkat dalam objek SUPER:


SELECT super_object.array[2];
         
 array
---------------
 third_element

Anda dapat menggunakan notasi titik dan tanda kurung saat melakukan operasi data seperti pemfilteran, penggabungan, dan agregasi. Anda dapat menggunakan notasi ini di mana saja dalam kueri di mana biasanya ada referensi kolom. Misalnya, pernyataan berikut memilih jumlah peristiwa dengan tipeUPDATED.


SELECT COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0]."eventType" = 'UPDATED';
         
 eventType | count
-----------+-------
 "UPDATED" | 1

Untuk contoh yang lebih mendalam tentang menggunakan navigasi PartiQL, lihatContoh penggunaan data semi-terstruktur di Amazon Redshift.

Kueri tanpa sarang

Untuk mengatasi kueri, Amazon Redshift menyediakan dua cara untuk mengulangi array SUPER: sintaks PartiQL dan operasi UNNEST dalam klausa FROM. Kedua metode unmenghasilkan output yang sama. Untuk informasi tentang operasi UNNEST, lihatKlausa FROM. Untuk contoh penggunaan operasi UNNEST, lihatContoh UNNEST.

Amazon Redshift dapat menavigasi array SUPER menggunakan sintaks PartiQL dalam klausa FROM kueri. Menggunakan contoh sebelumnya, contoh berikut mengulangi nilai atribut untukc_orders.


SELECT orders.*, o FROM customer_orders orders, orders.c_orders o;

Sintaks PartiQL dari unmenggunakan item klausa FROM x (AS) y berarti bahwa y mengulangi setiap nilai (SUPER) dalam ekspresi array (SUPER) x. Dalam hal ini, x adalah ekspresi SUPER dan y merupakan alias untukx.

Operan kiri juga dapat menggunakan notasi titik dan tanda kurung untuk navigasi reguler. Dalam contoh berikut, customer_orders_lineitem c adalah iterasi di atas tabel customer_order_lineitem dasar dan c.c_orders o merupakan iterasi di atas c.c_orders array. Untuk mengulangi o_lineitems atribut, yang merupakan array dalam array, Anda dapat menambahkan beberapa klausa, seperti:


SELECT c.*, o, l FROM customer_orders_lineitem c, c.c_orders o, o.o_lineitems l;

Amazon Redshift juga mendukung indeks array saat mengulangi array menggunakan kata kunci AT. Klausa x AS y AT z mengulangi array x dan menghasilkan bidang z, yang merupakan indeks array. Contoh berikut menunjukkan bagaimana indeks array bekerja.


SELECT c_name,
       orders.o_orderkey AS orderkey,
       index AS orderkey_index
FROM customer_orders_lineitem c, c.c_orders AS orders AT index 
ORDER BY orderkey_index;

c_name             | orderkey | orderkey_index
-------------------+----------+----------------
Customer#000008251 | 3020007  |        0
Customer#000009452 | 4043971  |        0
  (2 rows)

Berikut ini adalah contoh iterasi di atas array skalar.


CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data;

SELECT element, index FROM bar AS b, b.data.scalar_array AS element AT index;

 index | element
-------+----------
     0 | 1
     1 | 2.3
     2 | 45000000
(3 rows)

Contoh berikut berulang-ulang di atas array beberapa level. Contoh menggunakan beberapa klausa unnest untuk mengiterasi ke array terdalam. Array f.multi_level_array AS berulangmulti_level_array. Elemen array AS adalah iterasi atas array di dalamnya. multi_level_array


CREATE TABLE foo AS SELECT json_parse('[[1.1, 1.2], [2.1, 2.2], [3.1, 3.2]]') AS multi_level_array;

SELECT array, element FROM foo AS f, f.multi_level_array AS array, array AS element;

 element | array
---------+---------
 1.1     | [1.1,1.2]
 1.2     | [1.1,1.2]
 2.1     | [2.1,2.2] 
 2.2     | [2.1,2.2]
 3.1     | [3.1,3.2] 
 3.2     | [3.1,3.2] 
(6 rows)

Untuk informasi lebih lanjut tentang klausa FROM, lihatKlausa FROM. Untuk contoh lebih lanjut tentang kueri SUPER yang tidak bersarang, lihatContoh penggunaan data semi-terstruktur di Amazon Redshift.

Objek tidak berputar

Untuk melakukan pembongkaran objek, Amazon Redshift menggunakan sintaks PartiQL untuk mengulangi objek SUPER. Ia melakukan ini menggunakan klausa FROM dari kueri bersama dengan kata kunci UNPIVOT. Dalam contoh berikut, ekspresi adalah c.c_orders[0] objek. Contoh kueri berulang di atas setiap atribut yang dikembalikan oleh objek.


SELECT attr as attribute_name, json_typeof(val) as value_type 
FROM customer_orders_lineitem c, UNPIVOT c.c_orders[0] AS val AT attr 
WHERE c_custkey = 9451;

 attribute_name  | value_type
-----------------+------------
 o_orderstatus   | string
 o_clerk         | string
 o_lineitems     | array
 o_orderdate     | string
 o_shippriority  | number
 o_totalprice    | number
 o_orderkey      | number
 o_comment       | string
 o_orderpriority | string
(9 rows)

Seperti halnya un, sintaks unpivoting juga merupakan perpanjangan dari klausa FROM. Perbedaannya adalah bahwa sintaks unpivoting menggunakan kata kunci UNPIVOT untuk menunjukkan bahwa itu mengulangi objek alih-alih array. Ini menggunakan AS value_alias untuk iterasi pada semua nilai di dalam objek, dan menggunakan AT attribute_alias untuk mengiterasi semua atribut. Pertimbangkan sintaks berikut:


UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift mendukung penggunaan objek unpivoting dan unarray dalam satu klausa FROM sebagai berikut:


SELECT attr as attribute_name, val as object_value
FROM customer_orders_lineitem c, c.c_orders AS o, UNPIVOT o AS val AT attr 
WHERE c_custkey = 9451;

Saat Anda menggunakan objek unpivoting, Amazon Redshift tidak mendukung unpioting berkorelasi. Secara khusus, anggaplah Anda memiliki kasus di mana ada beberapa contoh unpivoting di tingkat kueri yang berbeda dan bagian dalam yang tidak berputar mereferensikan yang luar. Amazon Redshift tidak mendukung jenis multiple unpivoting ini.

Untuk informasi lebih lanjut tentang klausa FROM, lihatKlausa FROM. Untuk contoh penggunaan berputar dengan tipe SUPER, lihatContoh penggunaan data semi-terstruktur di Amazon Redshift.

Pengetikan dinamis

Pengetikan dinamis tidak memerlukan pengecoran eksplisit data yang diekstraksi dari jalur titik dan tanda kurung. Amazon Redshift menggunakan pengetikan dinamis untuk memproses data SUPER tanpa skema tanpa perlu mendeklarasikan tipe data sebelum Anda menggunakannya dalam kueri Anda. Pengetikan dinamis menggunakan hasil navigasi ke kolom data SUPER tanpa harus secara eksplisit menempatkannya ke jenis Amazon Redshift. Pengetikan dinamis paling berguna dalam klausa join dan GROUP BY. Contoh berikut menggunakan pernyataan SELECT yang tidak memerlukan pengecoran eksplisit ekspresi titik dan tanda kurung ke jenis Amazon Redshift biasa. Untuk informasi tentang kompatibilitas jenis dan konversi, lihatKompatibilitas dan konversi tipe.

Pertimbangkan contoh berikut, yang mencari baris di mana status pesanan adalahshipped:


SELECT c_orders[0].o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus = 'shipped';

Tanda kesetaraan dalam kueri sampel ini mengevaluasi true kapan nilai c_orders [0] .o_orderstatus adalah string 'shipped'. Dalam semua kasus lain, tanda kesetaraan mengevaluasifalse, termasuk kasus-kasus di mana argumen kesetaraan adalah jenis yang berbeda. Misalnya, jika status pesanan adalah bilangan bulat, barisnya tidak akan dipilih.

Pengetikan dinamis dan statis

Tanpa menggunakan pengetikan dinamis, Anda tidak dapat menentukan apakah c_orders [0] .o_orderstatus adalah string, integer, atau struktur. Anda hanya dapat menentukan bahwa c_orders [0] .o_orderstatus adalah tipe data SUPER, yang dapat berupa skalar Amazon Redshift, array, atau struktur. Tipe statis c_orders [0] .o_orderstatus adalah tipe data SUPER. Secara konvensional, tipe secara implisit adalah tipe statis dalam SQL.

Amazon Redshift menggunakan pengetikan dinamis untuk pemrosesan data tanpa skema. Ketika kueri mengevaluasi data, c_orders [0] .o_orderstatus ternyata tipe tertentu. Misalnya, mengevaluasi c_orders [0] .o_orderstatus pada catatan pertama customer_orders_lineitem dapat menghasilkan integer. Mengevaluasi pada catatan kedua dapat menghasilkan string. Ini adalah jenis ekspresi dinamis.

Saat menggunakan operator SQL atau fungsi dengan ekspresi titik dan tanda kurung yang memiliki tipe dinamis, Amazon Redshift menghasilkan hasil yang mirip dengan menggunakan operator atau fungsi SQL standar dengan tipe statis masing-masing. Dalam contoh ini, ketika tipe dinamis dari ekspresi jalur adalah string, perbandingan dengan string 'P' bermakna. Setiap kali tipe dinamis c_orders [0] .o_orderstatus adalah tipe data lain kecuali string, kesetaraan mengembalikan false. Fungsi lain mengembalikan null ketika argumen salah ketik digunakan.

Contoh berikut menulis kueri sebelumnya dengan pengetikan statis:


SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR = 'P'
           ELSE FALSE END;

Perhatikan perbedaan berikut antara predikat kesetaraan dan predikat perbandingan. Dalam contoh sebelumnya, jika Anda mengganti predikat kesetaraan dengan predikat kurang dari atau sama, semantik menghasilkan nol, bukan false.


SELECT c_orders[0]. o_orderkey
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderstatus <= 'P';

Dalam contoh ini, jika c_orders [0] .o_orderstatus adalah string, Amazon Redshift mengembalikan true jika menurut abjad sama atau lebih kecil dari 'P'. Amazon Redshift mengembalikan false jika menurut abjad lebih besar dari 'P'. Namun, jika c_orders [0] .o_orderstatus bukan string, Amazon Redshift mengembalikan null karena Amazon Redshift tidak dapat membandingkan nilai dari jenis yang berbeda, seperti yang ditunjukkan dalam kueri berikut:


SELECT c_custkey
FROM customer_orders_lineitem
WHERE CASE WHEN JSON_TYPEOF(c_orders[0].o_orderstatus) = 'string'
           THEN c_orders[0].o_orderstatus::VARCHAR <= 'P'
           ELSE NULL END;

Pengetikan dinamis tidak mengecualikan dari perbandingan tipe yang minimal sebanding. Misalnya, Anda dapat mengonversi tipe skalar CHAR dan VARCHAR Amazon Redshift menjadi SUPER. Mereka sebanding dengan string, termasuk mengabaikan karakter spasi putih di belakang yang mirip dengan jenis Amazon Redshift CHAR dan VARCHAR. Demikian pula, bilangan bulat, desimal, dan nilai floating-point sebanding dengan nilai SUPER. Khusus untuk kolom desimal, setiap nilai juga dapat memiliki skala yang berbeda. Amazon Redshift masih menganggapnya sebagai tipe dinamis.

Amazon Redshift juga mendukung kesetaraan pada objek dan array yang dievaluasi sebagai sama dalam, seperti mengevaluasi jauh ke dalam objek atau array dan membandingkan semua atribut. Gunakan deep equal dengan hati-hati, karena proses melakukan deep equence bisa memakan waktu.

Menggunakan pengetikan dinamis untuk bergabung

Untuk gabungan, pengetikan dinamis secara otomatis mencocokkan nilai dengan tipe dinamis yang berbeda tanpa melakukan analisis CASE WHEN yang panjang untuk mengetahui jenis data apa yang mungkin muncul. Misalnya, asumsikan bahwa organisasi Anda mengubah format yang digunakan untuk kunci bagian dari waktu ke waktu.

Kunci bagian integer awal yang dikeluarkan diganti dengan kunci bagian string, seperti 'A55', dan kemudian diganti lagi dengan kunci bagian array, seperti ['X', 10] yang menggabungkan string dan angka. Amazon Redshift tidak harus melakukan analisis kasus yang panjang tentang kunci bagian dan dapat menggunakan join, seperti yang ditunjukkan pada contoh berikut.


SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE l.l_partkey = ps.ps_partkey
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

Contoh berikut menunjukkan betapa kompleks dan tidak efisiennya kueri yang sama tanpa menggunakan pengetikan dinamis:


SELECT c.c_name
    ,l.l_extendedprice
    ,l.l_discount
FROM customer_orders_lineitem c
    ,c.c_orders o
    ,o.o_lineitems l
    ,supplier_partsupp s
    ,s.s_partsupps ps
WHERE CASE WHEN IS_INTEGER(l.l_partkey) AND IS_INTEGER(ps.ps_partkey)
           THEN l.l_partkey::integer = ps.ps_partkey::integer
           WHEN IS_VARCHAR(l.l_partkey) AND IS_VARCHAR(ps.ps_partkey)
           THEN l.l_partkey::varchar = ps.ps_partkey::varchar
           WHEN IS_ARRAY(l.l_partkey) AND IS_ARRAY(ps.ps_partkey)
                AND IS_VARCHAR(l.l_partkey[0]) AND IS_VARCHAR(ps.ps_partkey[0])
                AND IS_INTEGER(l.l_partkey[1]) AND IS_INTEGER(ps.ps_partkey[1])
           THEN l.l_partkey[0]::varchar = ps.ps_partkey[0]::varchar
                AND l.l_partkey[1]::integer = ps.ps_partkey[1]::integer
           ELSE FALSE END
AND c.c_nationkey = s.s_nationkey
ORDER BY c.c_name;

Case-insensitive menanyakan

Anda dapat melakukan perbandingan string yang tidak peka huruf besar/kecil pada data SUPER menggunakan fungsi COLLATE atau dengan mengatur kolasi pada tingkat kolom atau database. Untuk informasi selengkapnya tentang pengaturan kolasi pada pembuatan tabel, lihatCREATE TABLE. Untuk informasi tentang perilaku kolasi dengan operator dan fungsi data SUPER, lihatPerilaku pengumpulan.

Contoh berikut menggunakan fungsi COLLATE pada nilai string yang diekstraksi dari data SUPER.


CREATE TABLE events (data SUPER);
INSERT INTO events VALUES (JSON_PARSE('{"status": "Active", "name": "Event1"}'));
INSERT INTO events VALUES (JSON_PARSE('{"status": "ACTIVE", "name": "Event2"}'));
INSERT INTO events VALUES (JSON_PARSE('{"status": "active", "name": "Event3"}'));

SELECT data.name FROM events 
WHERE COLLATE(data.status::VARCHAR, 'case_insensitive') = 'active';

 name
----------
 "Event1"
 "Event2"
 "Event3"
(3 rows)

Anda juga dapat menentukan kolom SUPER dengan kolasi case-insensitive pada pembuatan tabel. Dalam hal ini, semua perbandingan string pada kolom tidak peka huruf besar/kecil.


CREATE TABLE events_ci (data SUPER COLLATE CASE_INSENSITIVE);
INSERT INTO events_ci VALUES (JSON_PARSE('{"status": "Active"}'));
INSERT INTO events_ci VALUES (JSON_PARSE('{"status": "ACTIVE"}'));

SELECT * FROM events_ci WHERE data.status::VARCHAR = 'active';

 data
-----------------------
 {"status":"Active"}
 {"status":"ACTIVE"}
(2 rows)

Semantik yang lemah

Secara default, operasi navigasi pada nilai SUPER mengembalikan null alih-alih mengembalikan kesalahan saat navigasi tidak valid. Navigasi objek tidak valid jika nilai SUPER bukan objek atau jika nilai SUPER adalah objek tetapi tidak berisi nama atribut yang digunakan dalam kueri. Misalnya, kueri berikut mengakses nama atribut yang tidak valid di kolom data SUPER cdata:


SELECT c.c_orders.something FROM customer_orders_lineitem c;

Navigasi array mengembalikan null jika nilai SUPER bukan array atau indeks array berada di luar batas. Kueri berikut mengembalikan null karena c_orders [1] [1] berada di luar batas.


SELECT c.c_orders[1][1] FROM customer_orders_lineitem c;

Semantik lax sangat berguna saat menggunakan pengetikan dinamis untuk memberikan nilai SUPER. Mendapatkan nilai SUPER ke tipe yang salah mengembalikan null alih-alih kesalahan jika cast tidak valid. Misalnya, kueri berikut mengembalikan null karena tidak dapat memberikan nilai string 'Baik' dari atribut objek o_orderstatus ke INTEGER. Amazon Redshift mengembalikan kesalahan untuk pemeran VARCHAR ke INTEGER tetapi tidak untuk pemeran SUPER.


SELECT c.c_orders.o_orderstatus::integer FROM customer_orders_lineitem c;

Urutkan berdasarkan

Amazon Redshift tidak mendefinisikan perbandingan SUPER di antara nilai-nilai dengan tipe dinamis yang berbeda. Nilai SUPER yang merupakan string tidak lebih kecil atau lebih besar dari nilai SUPER yang merupakan angka. Untuk menggunakan klausa ORDER BY dengan kolom SUPER, Amazon Redshift mendefinisikan urutan total di antara berbagai jenis yang akan diamati saat Amazon Redshift memberi peringkat nilai SUPER menggunakan klausa ORDER BY. Urutan di antara tipe dinamis adalah boolean, angka, string, array, objek.

Untuk contoh penggunaan GROUP BY dan ORDER BY dalam kueri SUPER, lihatMemfilter data semi-terstruktur.

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

Membongkar data semi-terstruktur

Operator dan fungsi