Interrogazione di dati semistrutturati

Con Amazon Redshift, puoi interrogare e analizzare dati semistrutturati, come JSON, Avro o Ion, insieme ai dati strutturati. I dati semistrutturati si riferiscono a dati che hanno uno schema flessibile, che consente strutture gerarchiche o annidate. Le seguenti sezioni illustrano l'interrogazione di 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 a dati relazionali, semistrutturati e annidati.

PartiQL funziona con tipi dinamici. Questo approccio consente il filtraggio, l'unione e l'aggregazione intuitivi sulla 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, vedere. PartiQL: un linguaggio di query compatibile con SQL per Amazon Redshift

Navigazione

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

È possibile utilizzare la notazione con punti e parentesi quadre quando si eseguono operazioni sui dati come il filtraggio, l'unione e l'aggregazione. È possibile utilizzare queste notazioni in qualsiasi punto di una query in cui normalmente sono presenti 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ù approfonditi sull'utilizzo della navigazione PartiQl, vedere. Esempi di utilizzo di dati semistrutturati in Amazon Redshift

Annullamento di query

Per annullare le query, Amazon Redshift offre due modi per iterare sugli array SUPER: la sintassi PartiQL e l'operazione UNNEST nella clausola FROM. Entrambi i metodi di unnesting producono lo stesso output. Per informazioni sull'operazione UNNEST, vedere. Clausola FROM Per esempi di utilizzo dell'operazione UNNEST, vedere. Esempi UNNEST

Amazon Redshift può navigare tra 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 unnesting che utilizza l'elemento della clausola FROM x (AS) y significa che y itera su ogni valore (SUPER) nell'espressione dell'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 seguente, customer_orders_lineitem c è l'iterazione sulla tabella di customer_order_lineitem base e c.c_orders o l'iterazione sull'array. c.c_orders Per eseguire un'iterazione sull'o_lineitemsattributo, che è un array all'interno di un array, è possibile aggiungere più clausole, in questo modo:

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)

Di seguito è riportato un esempio di iterazione 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 altri esempi di annullamento delle query SUPER, vedi. Esempi di utilizzo di 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 tale scopo, utilizza la clausola FROM di una query insieme alla 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 l'AS value_alias per l'iterazione su tutti i valori all'interno di un oggetto e utilizza l'AT attribute_alias per l'iterazione su tutti gli attributi. Considerate la seguente sintassi:

UNPIVOT expression AS value_alias [ AT attribute_alias ]

Amazon Redshift supporta l'utilizzo del unpivoting degli oggetti e del unnesting degli array in un'unica clausola FROM come segue:

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 pivoting con il tipo SUPER, consulta. Esempi di utilizzo di 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.

Considerate 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 verrà 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 sostituite il predicato di uguaglianza con un less-than-or-equal predicato, 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 lunghe analisi di casi sulle chiavi delle parti e può utilizzare i join, come illustrato 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 SUPER query, consulta. Filtraggio di dati semistrutturati