Query sui dati semistrutturati - Amazon Redshift
NavigazioneAnnullamento di queryNidificazione di oggettiDigitazione dinamicaSemantica permissivaOrder by (Ordina per)

Amazon Redshift non supporterà più la creazione di nuove UDF Python a partire dal 1º novembre 2025. Se desideri utilizzare le UDF Python, creale prima di tale data. Le UDF Python esistenti continueranno a funzionare normalmente. Per ulteriori informazioni, consulta il post del blog.

Query sui dati semistrutturati

Con Amazon Redshift puoi eseguire query sui dati semistrutturati, come JSON, Avro o Ion, e sui dati strutturati e analizzarli. I dati semistrutturati si riferiscono ai dati che hanno uno schema flessibile, che prevede strutture gerarchiche o annidate. Nelle sezioni seguenti viene illustrato come eseguire query sui dati semistrutturati utilizzando il supporto di Amazon Redshift per i formati di dati aperti, che consente di sbloccare informazioni preziose da strutture di dati complesse.

Amazon Redshift utilizza il linguaggio PartiQL per offrire un accesso compatibile con SQL ai dati relazionali, semistrutturati e annidati.

PartiQL funziona con tipi dinamici. Questo approccio consente di filtrare, effettuare il join e aggregare in modo intuitivo la combinazione di set di dati strutturati, semistrutturati e annidati. La sintassi PartiQL utilizza la notazione puntata e l'indice di array per la navigazione dei percorsi quando si accede ai dati nidificati. Consente inoltre agli elementi della clausola FROM di iterare su array e uso per le operazioni di annullamento nidificazione. Seguendo, è possibile trovare descrizioni di diversi modelli di query che combinano l'uso del tipo di dati SUPER con percorsi e array di navigazione, annullamento, nidificazione e join. Per ulteriori informazioni su PartiQL, consulta PartiQL: un linguaggio di query compatibile con SQL per Amazon Redshift.

Amazon Redshift utilizza PartiQL per abilitare la navigazione in array e strutture utilizzando rispettivamente la parentesi [...] e la notazione a punti. Inoltre, è possibile combinare la navigazione in strutture utilizzando la notazione a punti e gli array utilizzando la notazione con parentesi. Ad esempio, l’istruzione seguente seleziona solo il terzo elemento di un array annidato a un livello profondo in un oggetto SUPER:

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

Puoi utilizzare la notazione con punti e parentesi quando esegui operazioni sui dati come il filtraggio, il join e l’aggregazione. Puoi utilizzare queste notazioni ovunque in una query in cui ci sono normalmente riferimenti di colonna. Ad esempio, l’istruzione seguente seleziona il numero di eventi con il tipo UPDATED. 

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

Per esempi più dettagliati sull’utilizzo della navigazione PartiQL, consulta Esempi di utilizzo dei dati semistrutturati in Amazon Redshift.

Annullamento di query

Per annullare l’annidamento delle query, Amazon Redshift offre due modi per eseguire l’iterazione sugli array SUPER: la sintassi PartiQL e l’operazione UNNEST nella clausola FROM. Entrambi i metodi di annullamento dell’annidamento producono lo stesso output. Per ulteriori informazioni sull’operazione UNNEST, consulta Clausola FROM. Per esempi dell’utilizzo dell’operazione UNNEST, consulta Esempi di UNNEST.

Amazon Redshift può navigare negli array SUPER utilizzando la sintassi PartiQL nella clausola FROM di una query. Utilizzando l'esempio precedente, nell'esempio seguente vengono eseguite interazioni sui valori dell'attributo per c_orders.

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

La sintassi PartiQL di annullamento dell’annidamento con l’elemento della clausola FROM x (AS) y significa che in y vengono eseguite iterazioni su ciascun valore (SUPER) nell’espressione di array (SUPER) x. In questo caso, x è un'espressione SUPER e y è un alias per x.

Per la navigazione regolare, con l'operando sinistro si può anche utilizzare la notazione con punti e parentesi. Nell’esempio precedente, customer_orders_lineitem c è l’iterazione sulla tabella di base customer_order_lineitem e c.c_orders o è l’iterazione sull’array c.c_orders. Per eseguire iterazioni sull’attributo o_lineitems, che è un array all’interno di un array, puoi aggiungere più clausole, come segue:

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

Amazon Redshift supporta anche un indice dell'array quando si esegue l'iterazione sull'array utilizzando la parola chiave AT. Nella clausola x AS y AT z vengono eseguite iterazioni sull'array x e genera il campo z, che è l'indice dell'array. Nell'esempio seguente viene illustrato il funzionamento di un indice di array:

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)

Nell’esempio seguente sono eseguite iterazioni su un array scalare.

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)

Nell'esempio seguente viene eseguita un'iterazione su un array di più livelli. L'esempio utilizza più clausole unnest per eseguire l'iterazione negli array più interni. Nell'array AS f.multi_level_array viene eseguita un'iterazione su multi_level_array. L'elemento array AS è l'iterazione sugli array entro 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)

Per ulteriori informazioni sulla clausola FROM, consultare Clausola FROM. Per ulteriori esempi di annullamento dell’annidamento delle query SUPER, consulta Esempi di utilizzo dei dati semistrutturati in Amazon Redshift.

Nidificazione di oggetti

Per eseguire la nidificazionet degli oggetti, Amazon Redshift utilizza la sintassi PartiQL per eseguire l'iterazione sugli oggetti SUPER. A questo scopo utilizza la clausola FROM di una query con la parola chiave UNPIVOT. Nell’esempio seguente, l’espressione è l’oggetto c.c_orders[0]. La query di esempio esegue un’iterazione su ogni attributo restituito dall’oggetto.

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)

Come con l'annullamento, la sintassi di nidificazione è un'estensione della clausola FROM. La differenza è che la sintassi di nidificazione utilizza la parola chiave UNPIVOT per indicare che sta iterando su un oggetto anziché su un array. Utilizza AS value_alias per l’iterazione su tutti i valori all’interno di un oggetto e utilizza AT attribute_alias per l’iterazione su tutti gli attributi. Considera la sintassi seguente:

UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift supporta l’utilizzo dell’annullamento del pivot degli oggetti e l’annullamento dell’annidamento dell’array in una singola clausola FROM come indicato di seguito.

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;

Quando utilizzi la nidificazione degli oggetti, Amazon Redshift non supporta la nidificazione correlata. In particolare, supponiamo di avere un caso in cui ci sono più esempi di nidificazione in diversi livelli di query e che la nidificazione interna faccia riferimento a quella esterno. Amazon Redshift non supporta questo tipo di nidificazione multipla.

Per ulteriori informazioni sulla clausola FROM, consultare Clausola FROM. Per esempi di utilizzo del pivot con il tipo SUPER, consulta Esempi di utilizzo dei dati semistrutturati in Amazon Redshift.

Digitazione dinamica

La digitazione dinamica non richiede il casting esplicito dei dati estratti dai percorsi con punti e parentesi. Amazon Redshift utilizza la digitazione dinamica per elaborare dati SUPER senza schema senza la necessità di dichiarare i tipi di dati prima di utilizzarli nella query. La digitazione dinamica utilizza i risultati della navigazione nelle colonne di dati SUPER senza doverne eseguire esplicitamente il casting nei tipi Amazon Redshift. La digitazione dinamica è più utile nei join e nelle clausole GROUP BY. Nell'esempio seguente viene utilizzata un'istruzione SELECT che non richiede il casting esplicito delle espressioni con punti e parentesi ai normali tipi Amazon Redshift. Per informazioni sulla compatibilità e la conversione dei tipi, consultare Conversione e compatibilità dei tipi.

Considera l’esempio seguente, che cerca le righe in cui lo stato di un ordine è shipped:

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

Il segno di uguaglianza in questa query di esempio restituisce true quando il valore di c_orders[0].o_orderstatus è la stringa 'shipped'. In tutti gli altri casi, il segno di uguaglianza restituiscefalse, compresi i casi in cui gli argomenti dell'uguaglianza sono tipi diversi. Ad esempio, se lo stato dell’ordine è un numero intero, la relativa riga non viene selezionata.

Digitazione dinamica e statica

Senza utilizzare la digitazione dinamica, non è possibile determinare se c_orders[0].o_orderstatus è una stringa, un numero intero o una struttura. È possibile determinare solo che c_orders[0].o_orderstatus è un tipo di dati SUPER, che può essere uno scalare Amazon Redshift, un array o una struttura. Il tipo statico di c_orders[0].o_orderstatus è un tipo di dati SUPER. Convenzionalmente, in SQL un tipo è implicitamente un tipo statico.

Amazon Redshift utilizza la digitazione dinamica per l'elaborazione dei dati senza schema. Quando la query valuta i dati, c_orders[0].o_orderstatus diventa un tipo specifico. Ad esempio, la valutazione di c_orders[0].o_orderstatus sul primo record di customer_orders_lineitem può restituire un numero intero. La valutazione sul secondo record può restituire una stringa. Questi sono i tipi dinamici dell'espressione.

Se si utilizza un operatore o una funzione SQL con espressioni con punti e parentesi che hanno tipi dinamici, Amazon Redshift produce risultati simili all'utilizzo dell'operatore SQL standard o della funzione con i rispettivi tipi statici. In questo esempio, quando il tipo dinamico dell'espressione del percorso è una stringa, il confronto con la stringa 'P' è significativo. Ogni volta che il tipo dinamico di c_orders[0].o_orderstatus è qualsiasi altro tipo di dati che non sia stringa, l'uguaglianza restituisce false. Le altre funzioni restituiscono null quando vengono utilizzati argomenti errati.

L'esempio seguente scrive la query precedente con la digitazione statica:

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;

Si noti la seguente distinzione tra predicati di uguaglianza e predicati di confronto. Nell'esempio precedente, se si sostituisce il predicato di uguaglianza con un predicato minore o uguale, la semantica produce null anziché false.

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

In questo esempio, se c_orders[0].o_orderstatus è una stringa, Amazon Redshift restituisce true se è alfabeticamente uguale o inferiore a 'P'. Amazon Redshift restituisce false se è alfabeticamente più grande di 'P'. Tuttavia, se c_orders[0].o_orderstatus non è una stringa, Amazon Redshift restituisce null poiché non è in grado di confrontare valori di tipi diversi, come mostrato nella seguente query:

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;

La digitazione dinamica non esclude dai confronti di tipi minimamente comparabili. Ad esempio, è possibile convertire entrambi i tipi scalari CHAR e VARCHAR di Amazon Redshift in SUPER. Sono paragonabili come stringhe, ad esempio come nel caso di ignorare i caratteri di spazi finali simili ai tipi CHAR e VARCHAR di Amazon Redshift. Allo stesso modo, numeri interi, decimali e a virgola mobile sono comparabili come valori SUPER. Per le colonne decimali in particolare, ogni valore può anche avere una scala diversa. Amazon Redshift li considera ancora come tipi dinamici.

Amazon Redshift supporta anche l'uguaglianza su oggetti e array valutati come deep equal, ad esempio la valutazione approfondita di oggetti o array e il confronto di tutti gli attributi. Utilizzare deep equal con cautela, perché il processo di esecuzione di deep equal può richiedere molto tempo.

Utilizzo della digitazione dinamica per i join

Per i join, la digitazione dinamica corrisponde automaticamente ai valori con diversi tipi dinamici senza eseguire un'analisi CASE WHEN lunga per scoprire quali tipi di dati possono apparire. Ad esempio, si supponga che l'organizzazione abbia modificato nel tempo il formato utilizzato per le chiavi di parte.

Le chiavi di parte integer iniziali emesse vengono sostituite da chiavi di parte string, come 'A55', e successivamente sostituite di nuovo da chiavi di parte array, come ['X', 10] combinando una stringa e un numero. Amazon Redshift non deve eseguire un’analisi lunga dei casi sulle chiavi di parte e può utilizzare i join, come mostrato nell’esempio seguente.

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;

Nell'esempio seguente viene mostrato quanto sia complessa e inefficiente la stessa query se non viene utilizzata la digitazione dinamica:

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;

Semantica permissiva

Per impostazione predefinita, le operazioni di navigazione sui valori SUPER restituiscono null invece di restituire un errore quando la navigazione non è valida. La navigazione tra gli oggetti non è valida se il valore SUPER non è un oggetto o se il valore SUPER è un oggetto ma non contiene il nome dell'attributo utilizzato nella query. Ad esempio, la seguente query accede a un nome attributo non valido nella colonna di dati SUPER cdata:

SELECT c.c_orders.something FROM customer_orders_lineitem c;

La navigazione nell'array restituisce null se il valore SUPER non è un array o se l'indice dell'array è fuori dai limiti. La query seguente restituisce null perché c_orders[1][1] è fuori dai limiti. 

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

La sintassi permissiva è particolarmente utile quando si utilizza la digitazione dinamica per eseguire il casting di un valore SUPER. Il casting di un valore SUPER sul tipo non corretto restituisce null invece che un errore se il casting non è valido. Ad esempio, la seguente query restituisce null perché non è possibile eseguire il casting del valore stringa 'Good' dell'attributo oggetto o_orderstatus su INTEGER. Amazon Redshift restituisce un errore per un casting da VARCHAR a INTEGER ma non per un casting SUPER.

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

Order by (Ordina per)

Amazon Redshift non definisce i confronti SUPER tra valori con diversi tipi dinamici. Un valore SUPER che è una stringa non è né più piccolo né più grande di un valore SUPER che è un numero. Per utilizzare le clausole ORDER BY con colonne SUPER, Amazon Redshift definisce un ordine totale tra diversi tipi da osservare quando Amazon Redshift classifica i valori SUPER utilizzando le clausole ORDER BY. L'ordine tra i tipi dinamici è booleano, numero, stringa, array, oggetto.

Per un esempio di utilizzo di GROUP BY e ORDER BY in una query SUPER, consulta Filtraggio di dati semistrutturati.