Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.
Tutorial: Esecuzione di query su dati nidificati con Amazon Redshift Spectrum
Panoramica
Amazon Redshift Spectrum supporta l'esecuzione di query di dati nidificati nei formati di file Parquet, ORC, JSON e Ion. Redshift Spectrum accede ai dati mediante tabelle esterne. Puoi creare tabelle esterne che utilizzano i tipi di dati complessi struct, array e map.
Ad esempio, si supponga che il file di dati contenga i seguenti dati in Amazon S3 all'interno di una cartella denominata customers. Anche se non è presente un singolo elemento root, ciascun oggetto JSON in questi dati di esempio rappresenta una riga in una tabella.
{"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}] }
Puoi utilizzare Amazon Redshift Spectrum per eseguire query sui dati nidificati nei file. Il seguente tutorial illustra la procedura da seguire con i dati Apache Parquet.
Per i prerequisiti, le fasi e i casi d'uso relativi ai dati nidificati del tutorial, consultare i seguenti argomenti:
Prerequisiti
Se ancora non utilizzi Redshift Spectrum, segui la procedura in Nozioni di base su Amazon Redshift Spectrum prima di continuare.
Per creare uno schema esterno, sostituire l'ARN del ruolo IAM nel comando seguente con l'ARN del ruolo creato in Creazione di un ruolo IAM. Quindi eseguire il comando nel proprio client SQL.
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;
Fase 1: creazione di una tabella esterna contenente dati nidificati
È possibile visualizzare i dati di origine
Per creare la tabella esterna per questo tutorial, utilizza il comando seguente.
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/';
Nell'esempio precedente, la tabella esterna spectrum.customers utilizza i tipi di dati struct e array per definire colonne con dati nidificati. Amazon Redshift Spectrum supporta l'esecuzione di query di dati nidificati nei formati di file Parquet, ORC, JSON e Ion. Il parametro STORED AS è PARQUET per i file Apache Parquet. Il parametro LOCATION deve fare riferimento alla cartella Amazon S3 che contiene i file o i dati nidificati. Per ulteriori informazioni, consulta CREATE EXTERNAL TABLE.
Puoi nidificare tipi array e struct a qualsiasi livello. Ad esempio, puoi definire una colonna denominata toparray come mostrato nell'esempio seguente.
toparray array<struct<nestedarray: array<struct<morenestedarray: array<string>>>>>
Puoi inoltre nidificare tipi struct come mostrato per la colonna x nell'esempio seguente.
x struct<a: string, b: struct<c: integer, d: struct<e: string> > >
Fase 2: Esecuzione di query sui dati nidificati in Amazon S3 con estensioni SQL
Redshift Spectrum supporta l'esecuzione di query su tipi complessi array, map e struct mediante estensioni alla sintassi SQL di Amazon Redshift.
Estensione 1: accesso a colonne di struct
Puoi estrarre dati da colonne struct utilizzando una notazione punto che concatena i nomi dei campi in percorsi. Ad esempio, la query seguente restituisce il nome e il cognome dei clienti. L'accesso al nome viene eseguito dal percorso lungo c.name.given. L'accesso al cognome viene eseguito dal percorso lungo c.name.family.
SELECT c.id, c.name.given, c.name.family FROM spectrum.customers c;
La query precedente restituisce i seguenti dati.
id | given | family ---|-------|------- 1 | John | Smith 2 | Jenny | Doe 3 | Andy | Jones (3 rows)
Uno struct può essere una colonna di un altro struct, che a sua volta può essere una colonna di un altro struct, a qualsiasi livello. I percorsi di accesso alle colonne in struct nidificati in modo così profondo possono essere lunghi. Ad esempio, consultare la definizione relativa alla colonna x nell'esempio seguente.
x struct<a: string, b: struct<c: integer, d: struct<e: string> > >
Puoi accedere ai dati in e come x.b.d.e.
Estensione 2: matrici estese a una clausola FROM
Puoi estrarre dati da colonne array (e, per estensione, da colonne map) specificando le colonne array in una clausola FROM al posto dei nomi delle tabelle. L'estensione si applica alla clausola FROM della query principale e, inoltre, alle clausole FROM delle query secondarie.
Puoi fare riferimento a elementi array in base alla posizione, ad esempio c.orders[0] (anteprima).
Combinando arrays estese e join, puoi annullare annidamenti in vari modi, come illustrato nei seguenti casi d'uso.
Annullamento di annidamenti mediante inner join
La seguente query seleziona gli ID dei clienti e le date di spedizione degli ordini per i clienti associati a degli ordini. L'estensione SQL nella clausola FROM c.orders
o dipende dall'alias c.
SELECT c.id, o.shipdate FROM spectrum.customers c, c.orders o
Per ciascun cliente c associato a degli ordini, la clausola FROM restituisce una riga per ogni ordine o del cliente c. La riga in questione combina la riga del cliente c e quella dell'ordine o. In seguito, la clausola SELECT mantiene solo c.id e o.shipdate. Il risultato è il seguente.
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)
L'alias c fornisce l'accesso ai campi dei clienti, mentre l'alias o consente l'accesso ai campi degli ordini.
La semantica è simile a quella di SQL standard. È possibile pensare a un processo in cui la clausola FROM esegue il loop nidificato mostrato sotto, mentre SELECT sceglie i campi da restituire.
for each customer c in spectrum.customers for each order o in c.orders output c.id and o.shipdate
Pertanto, se un cliente non è associato ad alcun ordine, non verrà mostrato tra i risultati.
Puoi pensare anche a un processo in cui la clausola FROM esegue un JOIN con la tabella customers e la matrice orders. In effetti, puoi anche scrivere la query, come mostrato nell'esempio seguente.
SELECT c.id, o.shipdate FROM spectrum.customers c INNER JOIN c.orders o ON true
Nota
Se è presente uno schema denominato c con una tabella chiamata orders, allora c.orders fa riferimento alla tabella orders e non alla colonna della matrice di customers.
Annullamento di annidamenti mediante left join
La query seguente restituisce tutti i nomi dei clienti e i loro ordini. Se un cliente non ha effettuato alcun ordine, il suo nome viene comunque restituito. Tuttavia, in questo caso, l'ordine presenta colonne NULL, come mostrato nell'esempio seguente per 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
La query precedente restituisce i seguenti dati.
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)
Estensione 3: accesso diretto a una matrice di scalari tramite un alias
Quando un alias p in una clausola FROM si estende a una matrice di scalari, la query fa riferimento ai valori di p come p. Ad esempio, la query seguente genera coppie di nomi e numeri di telefono dei clienti.
SELECT c.name.given, c.name.family, p AS phone FROM spectrum.customers c LEFT JOIN c.phones p ON true
La query precedente restituisce i seguenti dati.
given | family | phone -------|----------|----------- John | Smith | 123-4577891 Jenny | Doe | 858-8675309 Jenny | Doe | 415-9876543 Andy | Jones | (4 rows)
Estensione 4: accesso agli elementi del tipo di dati map
Redshift Spectrum tratta map come un tipo di dati array che contiene tipi struct con una colonna key e una colonna value. key deve essere uno scalar; il valore può essere qualunque tipo di dati.
Ad esempio, il codice seguente crea una tabella esterna con un tipo map per l'archiviazione dei numeri di telefono.
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/';
Dal momento che un tipo map si comporta come un tipo array con colonne key e value, puoi pensare agli schemi precedenti come a quelli riportati di seguito.
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/';
La query seguente restituisce i nomi dei clienti con un numero di cellulare e il numero per ciascun nome. La query sul tipo map viene considerata equivalente a una query su un array nidificato di tipi struct. La query seguente restituisce dati solo se hai creato la tabella esterna come descritto in precedenza.
SELECT c.name.given, c.name.family, p.value
FROM spectrum.customers c, c.phones p
WHERE p.key = 'mobile'; Nota
La key per un tipo map è una string per i tipi di file Ion e JSON.
