Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Tutorial: Menanyakan data bersarang dengan Amazon Redshift Spectrum

Tutorial ini menunjukkan bagaimana untuk query data bersarang dengan Redshift Spectrum. Data bersarang adalah data yang berisi bidang bersarang. Bidang bersarang adalah bidang yang disatukan sebagai entitas tunggal, seperti array, struct, atau objek.

Gambaran Umum

Amazon Redshift Spectrum mendukung kueri data bersarang dalam format file ParketORC,JSON, dan Ion. Redshift Spectrum mengakses data menggunakan tabel eksternal. Anda dapat membuat tabel eksternal yang menggunakan tipe data yang kompleksstruct,array, danmap.

Misalnya, misalkan file data Anda berisi data berikut di Amazon S3 dalam folder bernama. customers Meskipun tidak ada elemen root tunggal, setiap JSON objek dalam data sampel ini mewakili baris dalam tabel.

{"id": 1,
 "name": {"given": "John", "family": "Smith"},
 "phones": ["123-457789"],
 "orders": [{"shipdate": "2018-03-01T11:59:59.000Z", "price": 100.50},
            {"shipdate": "2018-03-01T09:10:00.000Z", "price": 99.12}]
}
{"id": 2,
 "name": {"given": "Jenny", "family": "Doe"},
 "phones": ["858-8675309", "415-9876543"],
 "orders": []
}
{"id": 3,
 "name": {"given": "Andy", "family": "Jones"},
 "phones": [],
 "orders": [{"shipdate": "2018-03-02T08:02:15.000Z", "price": 13.50}]
}  

Anda dapat menggunakan Amazon Redshift Spectrum untuk menanyakan data bersarang dalam file. Tutorial berikut menunjukkan cara melakukannya dengan data Apache Parquet.

Prasyarat

Jika Anda belum menggunakan Redshift Spectrum, ikuti langkah-langkah di Memulai dengan Amazon Redshift Spectrum sebelum melanjutkan.

Untuk membuat skema eksternal, ganti IAM peran ARN dalam perintah berikut dengan peran yang ARN Anda buat di Buat IAM peran. Kemudian jalankan perintah di SQL klien Anda.

create external schema spectrum 
from data catalog 
database 'myspectrum_db' 
iam_role 'arn:aws:iam::123456789012:role/myspectrum_role'
create external database if not exists;

Langkah 1: Buat tabel eksternal yang berisi data bersarang

Anda dapat melihat data sumber dengan mengunduhnya dari Amazon S3.

Untuk membuat tabel eksternal untuk tutorial ini, jalankan perintah berikut.

CREATE EXTERNAL TABLE spectrum.customers (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones array<varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';     

Pada contoh sebelumnya, tabel eksternal spectrum.customers menggunakan tipe array data struct dan untuk menentukan kolom dengan data bersarang. Amazon Redshift Spectrum mendukung kueri data bersarang dalam format file ParketORC,JSON, dan Ion. STORED ASParameternya adalah PARQUET untuk file Apache Parquet. LOCATIONParameter harus merujuk ke folder Amazon S3 yang berisi data atau file bersarang. Untuk informasi selengkapnya, lihat CREATE EXTERNAL TABLE.

Anda dapat bersarang array dan struct mengetik di tingkat mana pun. Misalnya, Anda dapat menentukan kolom bernama toparray seperti yang ditunjukkan pada contoh berikut.

toparray array<struct<nestedarray:
         array<struct<morenestedarray: 
         array<string>>>>>

Anda juga dapat struct jenis sarang seperti yang ditunjukkan untuk kolom x dalam contoh berikut.

x struct<a: string,
         b: struct<c: integer,
                   d: struct<e: string>
                  >
        >

Langkah 2: Kueri data bersarang Anda di Amazon SQL S3 dengan ekstensi

Redshift Spectrum mendukung kueri arraymap, dan jenis struct kompleks melalui ekstensi ke sintaks Amazon Redshift. SQL

Ekstensi 1: Akses ke kolom struct

Anda dapat mengekstrak data dari struct kolom menggunakan notasi titik yang menggabungkan nama bidang menjadi jalur. Misalnya, permintaan berikut mengembalikan nama yang diberikan dan keluarga untuk pelanggan. Nama yang diberikan diakses oleh jalur panjangc.name.given. Nama keluarga diakses oleh jalan panjangc.name.family.

SELECT c.id, c.name.given, c.name.family
FROM   spectrum.customers c;

Query sebelumnya mengembalikan data berikut.

id | given | family
---|-------|-------
1  | John  | Smith
2  | Jenny | Doe
3  | Andy  | Jones
(3 rows)

A struct bisa berupa kolom lainstruct, yang bisa berupa kolom lainstruct, di tingkat mana pun. Jalur yang mengakses kolom dalam struct s bersarang sedemikian dalam bisa sangat panjang. Misalnya, lihat definisi untuk kolom x dalam contoh berikut.

x struct<a: string,
         b: struct<c: integer, 
                      d: struct<e: string>
                  >
        >

Anda dapat mengakses data di e asx.b.d.e.

Ekstensi 2: Mulai dari array dalam klausa FROM

Anda dapat mengekstrak data dari array kolom (dan, dengan ekstensi, map kolom) dengan menentukan array kolom dalam FROM klausa sebagai pengganti nama tabel. Ekstensi berlaku untuk FROM klausa kueri utama, dan juga FROM klausa subquery.

Anda dapat mereferensikan array elemen berdasarkan posisi, sepertic.orders[0]. (pratinjau)

Dengan menggabungkan mulai arrays dengan gabungan, Anda dapat mencapai berbagai jenis unnesting, seperti yang dijelaskan dalam kasus penggunaan berikut.

Menghapus sarang menggunakan sambungan batin

Kueri berikut memilih tanggal pengiriman pelanggan IDs dan pesanan untuk pelanggan yang memiliki pesanan. SQLEkstensi dalam FROM klausa c.orders o tergantung pada c alias.

SELECT c.id, o.shipdate
FROM   spectrum.customers c, c.orders o 

Untuk setiap pelanggan c yang memiliki pesanan, FROM klausa mengembalikan satu baris untuk setiap pesanan o pelangganc. Baris itu menggabungkan baris pelanggan c dan baris pesanano. Kemudian SELECT klausa hanya menyimpan c.id dano.shipdate. Hasilnya adalah sebagai berikut.

id|      shipdate
--|----------------------
1 |2018-03-01  11:59:59
1 |2018-03-01  09:10:00
3 |2018-03-02  08:02:15
(3 rows) 

Alias c menyediakan akses ke bidang pelanggan, dan alias o menyediakan akses ke bidang pesanan.

Semantiknya mirip dengan standar. SQL Anda dapat menganggap FROM klausa sebagai menjalankan loop bersarang berikut, yang diikuti dengan SELECT memilih bidang yang akan dikeluarkan.

for each customer c in spectrum.customers
  for each order o in c.orders
     output c.id and o.shipdate 

Oleh karena itu, jika pelanggan tidak memiliki pesanan, pelanggan tidak muncul di hasilnya.

Anda juga dapat menganggap ini sebagai FROM klausa yang melakukan a JOIN dengan customers tabel dan orders array. Bahkan, Anda juga dapat menulis query seperti yang ditunjukkan pada contoh berikut.

SELECT c.id, o.shipdate
FROM   spectrum.customers c INNER JOIN c.orders o ON true 

Menghapus sarang menggunakan gabungan kiri

Kueri berikut menghasilkan semua nama pelanggan dan pesanan mereka. Jika pelanggan belum melakukan pemesanan, nama pelanggan masih dikembalikan. Namun, dalam hal ini, kolom urutannyaNULL, seperti yang ditunjukkan pada contoh berikut untuk Jenny Doe.

SELECT c.id, c.name.given, c.name.family, o.shipdate, o.price
FROM   spectrum.customers c LEFT JOIN c.orders o ON true

Query sebelumnya mengembalikan data berikut.

id  |  given  | family  |    shipdate          | price
----|---------|---------|----------------------|--------
 1  |  John   | Smith   | 2018-03-01  11:59:59 | 100.5
 1  |  John   | Smith   | 2018-03-01  09:10:00 |  99.12
 2  |  Jenny  | Doe     |                      |
 3  |  Andy   | Jones   | 2018-03-02  08:02:15 |  13.5
 (4 rows)

Ekstensi 3: Mengakses array skalar secara langsung menggunakan alias

Ketika alias p dalam FROM klausa berkisar pada array skalar, kueri mengacu pada nilai as. p p Misalnya, kueri berikut menghasilkan pasangan nama pelanggan dan nomor telepon.

SELECT c.name.given, c.name.family, p AS phone
FROM   spectrum.customers c LEFT JOIN c.phones p ON true  

Query sebelumnya mengembalikan data berikut.

given  |  family  |  phone
-------|----------|-----------
John   | Smith    | 123-4577891
Jenny  | Doe      | 858-8675309
Jenny  | Doe      | 415-9876543
Andy   | Jones    | 
(4 rows) 

Ekstensi 4: Mengakses elemen peta

Redshift Spectrum memperlakukan tipe map data sebagai array tipe yang berisi struct tipe dengan key kolom dan kolom. value keyHarus ascalar; nilainya bisa berupa tipe data apa pun.

Misalnya, kode berikut membuat tabel eksternal dengan map untuk menyimpan nomor telepon.

CREATE EXTERNAL TABLE spectrum.customers2 (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones map<varchar(20), varchar(20)>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';

Karena map tipe berperilaku seperti array tipe dengan kolom key danvalue, Anda dapat memikirkan skema sebelumnya seolah-olah mereka adalah sebagai berikut.

CREATE EXTERNAL TABLE spectrum.customers3 (
  id     int,
  name   struct<given:varchar(20), family:varchar(20)>,
  phones array<struct<key:varchar(20), value:varchar(20)>>,
  orders array<struct<shipdate:timestamp, price:double precision>>
)
STORED AS PARQUET
LOCATION 's3://redshift-downloads/tickit/spectrum/customers/';   

Kueri berikut mengembalikan nama pelanggan dengan nomor ponsel dan mengembalikan nomor untuk setiap nama. Kueri peta diperlakukan sebagai setara dengan menanyakan tipe bersarangarray. struct Query berikut hanya mengembalikan data jika Anda telah membuat tabel eksternal seperti yang dijelaskan sebelumnya.

SELECT c.name.given, c.name.family, p.value 
FROM   spectrum.customers c, c.phones p 
WHERE  p.key = 'mobile';