Meminta data semi-terstruktur

Dengan Amazon Redshift, Anda dapat melakukan kueri dan menganalisis data semi-terstruktur, seperti JSON, Avro, atau Ion, di samping data terstruktur Anda. Data semi-terstruktur 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, memungkinkan Anda untuk membuka informasi berharga dari struktur data yang kompleks.

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

PartiQL beroperasi dengan tipe dinamis. Pendekatan ini memungkinkan penyaringan, penggabungan, dan agregasi intuitif pada kombinasi kumpulan data terstruktur, semi-terstruktur, dan bersarang. Sintaks PartiQL menggunakan notasi putus-putus 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 berbeda yang menggabungkan penggunaan tipe data SUPER dengan navigasi jalur dan array, unnesting, unpivoting, dan join. Untuk informasi lebih lanjut tentang PartiQL, lihat. PartiQL — bahasa kueri yang kompatibel dengan SQL untuk Amazon Redshift

Navigasi

Amazon Redshift menggunakan PartiQL untuk mengaktifkan navigasi ke dalam array dan struktur menggunakan braket [...] 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 bersarang satu tingkat dalam objek SUPER:

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

Anda dapat menggunakan notasi titik dan braket saat melakukan operasi data seperti penyaringan, 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 lebih mendalam tentang penggunaan navigasi PartiQL, lihat. Contoh penggunaan data semi-terstruktur di Amazon Redshift

Kueri yang tidak bersarang

Untuk kueri unnest, Amazon Redshift menyediakan dua cara untuk mengulangi array SUPER: sintaks PartiQL dan operasi UNNEST di klausa FROM. Kedua metode unnesting menghasilkan output yang sama. Untuk informasi tentang operasi UNNEST, lihatKlausa FROM. Untuk contoh menggunakan operasi UNNEST, lihatContoh UNNEST.

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

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

Sintaks PartiQL dari unnesting menggunakan x (AS) y item klausa FROM berarti y bahwa iterasi atas 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 braket untuk navigasi reguler. Dalam contoh berikut, customer_orders_lineitem c adalah iterasi atas tabel customer_order_lineitem dasar dan c.c_orders o merupakan iterasi atas array. c.c_orders 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 melakukan iterasi melalui array menggunakan kata kunci AT. Klausa x AS y AT z iterasi atas array x dan menghasilkan bidang yang z, 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 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 iterasi atas array dari beberapa level. Contoh menggunakan beberapa klausa unnest untuk beralih ke array terdalam. Array f.multi_level_array AS iterasi. multi_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 selengkapnya tentang klausa FROM, lihatKlausa FROM. Untuk lebih banyak contoh kueri SUPER yang tidak bersarang, lihat. Contoh penggunaan data semi-terstruktur di Amazon Redshift

Objek tidak berputar

Untuk melakukan unpivoting objek, Amazon Redshift menggunakan sintaks PartiQL untuk mengulangi objek SUPER. Ini dilakukan dengan menggunakan klausa FROM dari kueri bersama dengan kata kunci UNPIVOT. Dalam contoh berikut, ekspresi adalah c.c_orders[0] objek. Contoh query iterasi atas setiap atribut 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 unnesting, sintaks unpivoting juga merupakan perpanjangan dari klausa FROM. Perbedaannya adalah bahwa sintaks unpivoting menggunakan kata kunci UNPIVOT untuk menunjukkan bahwa itu iterasi di atas objek, bukan array. Ini menggunakan AS value_alias untuk iterasi atas semua nilai di dalam objek, dan menggunakan AT attribute_alias untuk iterasi atas semua atribut. Pertimbangkan sintaks berikut:

UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift mendukung penggunaan objek unpivoting dan array unnesting 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 unpivoting objek, Amazon Redshift tidak mendukung unpivoting yang berkorelasi. Secara khusus, anggaplah Anda memiliki kasus di mana ada beberapa contoh unpivoting di tingkat kueri yang berbeda dan unpivoting bagian dalam mereferensikan yang luar. Amazon Redshift tidak mendukung jenis multiple unpivoting ini.

Untuk informasi selengkapnya tentang klausa FROM, lihatKlausa FROM. Untuk contoh menggunakan pivoting dengan tipe SUPER, lihat. Contoh penggunaan data semi-terstruktur di Amazon Redshift

Pengetikan dinamis

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

Perhatikan 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 'dikirim'. 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, bilangan bulat, 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 merupakan tipe statis di SQL.

Amazon Redshift menggunakan pengetikan dinamis untuk memproses data tanpa skema. Saat 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 bilangan bulat. Mengevaluasi pada catatan kedua dapat menghasilkan string. Ini adalah tipe ekspresi yang dinamis.

Saat menggunakan operator SQL atau fungsi dengan ekspresi titik dan braket yang memiliki tipe dinamis, Amazon Redshift menghasilkan hasil yang mirip dengan menggunakan operator SQL standar atau fungsi 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 yang salah ketik digunakan.

Contoh berikut menulis query 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. Pada contoh sebelumnya, jika Anda mengganti predikat kesetaraan dengan predikat, semantik menghasilkan null, bukan false. less-than-or-equal

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 dengan 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 pada 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 dikecualikan dari perbandingan tipe yang sebanding minimal. Misalnya, Anda dapat mengonversi jenis skalar CHAR dan VARCHAR Amazon Redshift menjadi SUPER. Mereka sebanding dengan string, termasuk mengabaikan karakter spasi putih 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 deep equal, seperti mengevaluasi jauh ke dalam objek atau array dan membandingkan semua atribut. Gunakan deep equal dengan hati-hati, karena proses melakukan deep equal bisa memakan waktu.

Menggunakan pengetikan dinamis untuk bergabung

Untuk bergabung, pengetikan dinamis secara otomatis mencocokkan nilai dengan tipe dinamis yang berbeda tanpa melakukan analisis CASE WHEN yang panjang untuk mengetahui tipe 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] menggabungkan string dan angka. Amazon Redshift tidak harus melakukan analisis kasus panjang tentang kunci bagian dan dapat menggunakan gabungan, 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;

Semantik longgar

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 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. Mengirimkan nilai SUPER ke tipe yang salah mengembalikan null alih-alih kesalahan jika pemeran tidak valid. Misalnya, query berikut mengembalikan null karena tidak dapat melemparkan 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;

Memesan oleh

Amazon Redshift tidak mendefinisikan perbandingan SUPER di antara 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, number, string, array, object.

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