Meminta data semi-terstruktur

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 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 berbeda yang menggabungkan penggunaan tipe data SUPER dengan navigasi jalur dan array, unnesting, unpivoting, dan gabungan.

Untuk informasi tentang tabel yang digunakan dalam contoh berikut, lihatDataset sampel SUPER.

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, contoh berikut mengasumsikan bahwa kolom data c_orders SUPER adalah array dengan struktur dan atribut diberi namao_orderkey.

Untuk menelan data dalam customer_orders_lineitem tabel, jalankan perintah berikut. Ganti peran IAM dengan kredensyal Anda sendiri.

COPY customer_orders_lineitem FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/customer_orders_lineitem'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'auto';

SELECT c_orders[0].o_orderkey FROM customer_orders_lineitem;

Amazon Redshift juga menggunakan alias tabel sebagai awalan notasi. Contoh berikut adalah query yang sama dengan contoh sebelumnya.

SELECT cust.c_orders[0].o_orderkey FROM customer_orders_lineitem AS cust;

Anda dapat menggunakan notasi titik dan tanda kurung di semua jenis kueri, seperti pemfilteran, gabungan, dan agregasi. Anda dapat menggunakan notasi ini dalam kueri di mana biasanya ada referensi kolom. Contoh berikut menggunakan pernyataan SELECT yang memfilter hasil.

SELECT count(*) FROM customer_orders_lineitem WHERE c_orders[0]. o_orderkey IS NOT NULL;

Contoh berikut menggunakan braket dan navigasi titik di kedua klausa GROUP BY dan ORDER BY.

SELECT c_orders[0].o_orderdate,
       c_orders[0].o_orderstatus,
       count(*)
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderkey IS NOT NULL
GROUP BY c_orders[0].o_orderstatus,
         c_orders[0].o_orderdate
ORDER BY c_orders[0].o_orderdate;

Kueri yang tidak bersarang

Untuk kueri unnest, Amazon Redshift menggunakan sintaks PartiQL untuk mengulangi array SUPER. Hal ini dilakukan dengan menavigasi array menggunakan klausa FROM dari query. Menggunakan contoh sebelumnya, contoh berikut iterasi atas nilai atribut untukc_orders.

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

Sintaks unnesting adalah perpanjangan dari klausa FROM. Dalam SQL standar, klausa FROM x (AS) y berarti bahwa y iterasi atas setiap tupel dalam hubungannya. x Dalam hal ini, x mengacu pada relasi dan y mengacu pada alias untuk relasix. Demikian pula, 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 sebelumnya, 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 menambahkan beberapa klausa.

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)

Contoh berikut iterasi atas array skalar.

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

SELECT index, element 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;

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

Untuk informasi selengkapnya tentang klausa FROM, lihatKlausa FROM.

Objek tidak berputar

Untuk melakukan unpivoting objek, Amazon Redshift menggunakan sintaks PartiQL untuk mengulangi objek SUPER. Ini dilakukan dengan menggunakan klausa FROM dari kueri dengan kata kunci UNPIVOT. Dalam hal ini, 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 fragmen 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 yang menunjukkan cara menanyakan data terstruktur, dengan PIVOT dan UNPIVOT, lihat. Contoh PIVOT dan UNPIVOT

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.

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

Tanda kesetaraan dalam kueri ini mengevaluasi true kapan c_orders [0] .o_orderstatus adalah string 'P'. Dalam semua kasus lain, tanda kesetaraan mengevaluasifalse, termasuk kasus-kasus di mana argumen kesetaraan adalah jenis yang berbeda.

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;

Jenis introspeksi

Kolom data SUPER mendukung fungsi inspeksi yang mengembalikan tipe dinamis dan informasi tipe lainnya tentang nilai SUPER. Contoh paling umum adalah fungsi skalar JSON_TYPEOF yang mengembalikan VARCHAR dengan nilai boolean, number, string, object, array, atau null, tergantung pada tipe dinamis dari nilai SUPER. Amazon Redshift mendukung fungsi boolean berikut untuk kolom data SUPER:

• DESIMAL_PRESISI

• SKALA DESIMAL

• IS_ARRAY

• IS_BIGINT

• IS_CHAR

• ADALAH_DESIMAL

• IS_MENGAPUNG

• IS_INTEGER

• IS_OBJEK

• IS_SKALAR

• IS_SMALLINT

• IS_VARCHAR

• JSON_TYPEOF

Semua fungsi ini mengembalikan false jika nilai input adalah null. IS_SCALAR, IS_OBJECT, dan IS_ARRAY saling eksklusif dan mencakup semua nilai yang mungkin kecuali untuk null.

Untuk menyimpulkan tipe yang sesuai dengan data, Amazon Redshift menggunakan fungsi JSON_TYPEOF yang mengembalikan tipe (tingkat atas) nilai SUPER seperti yang ditunjukkan pada contoh berikut:

SELECT JSON_TYPEOF(r_nations) FROM region_nations;
 json_typeof
 -------------
 array
(1 row)

SELECT JSON_TYPEOF(r_nations[0].n_nationkey) FROM region_nations;
 json_typeof
 -------------
 number

Amazon Redshift melihat ini sebagai string panjang tunggal, mirip dengan memasukkan nilai ini ke dalam kolom VARCHAR alih-alih SUPER. Karena kolomnya SUPER, string tunggal masih merupakan nilai SUPER yang valid dan perbedaannya dicatat di JSON_TYPEOF:

SELECT IS_VARCHAR(r_nations[0].n_name) FROM region_nations;
 is_varchar
-------------
 true
(1 row)

SELECT r_nations[4].n_name FROM region_nations
WHERE CASE WHEN IS_INTEGER(r_nations[4].n_nationkey) 
           THEN r_nations[4].n_nationkey::INTEGER = 15 
           ELSE false END;

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. Contoh berikut menunjukkan urutan dari berbagai jenis:

INSERT INTO region_nations VALUES 
(100,'name1','comment1','AWS'), 
(200,'name2','comment2',1),
(300,'name3','comment3',ARRAY(1, 'abc', null)),
(400,'name4','comment4',-2.5),
(500,'name5','comment5','Amazon');

SELECT r_nations FROM region_nations order by r_nations;

r_nations
----------------
 -2.5
 1
 "Amazon"
 "AWS"
 [1,"abc",null]
(5 rows)

Untuk informasi selengkapnya tentang klausa ORDER BY, lihatKlausa ORDER BY.