Query sui dati semistrutturati

Amazon Redshift utilizza il linguaggio PartiQL per offrire accesso compatibile con SQL a dati relazionali, semistrutturati e nidificati.

PartiQL funziona con tipi dinamici. Questo approccio consente di filtrare, unire e aggregare intuitivi sulla combinazione di set di dati strutturati, semistrutturati e nidificati. 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 informazioni sulle tabelle utilizzate nell'esempio seguente, consulta Set di dati di esempio SUPER.

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, nell'esempio seguente si assume che la colonna di dati SUPER c_orders sia un array con una struttura e che un attributo sia denominato o_orderkey.

Per acquisire dati nella tabella customer_orders_lineitem, eseguire il seguente comando. Sostituire il ruolo IAM con le proprie credenziali.

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 utilizza anche un alias della tabella come prefisso alla notazione. L'esempio seguente è la stessa query dell'esempio precedente.

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

È possibile utilizzare le notazioni con punti e parentesi in tutti i tipi di query, ad esempio filtraggio, join e aggregazione. È possibile utilizzare queste notazioni in una query in cui ci sono normalmente riferimenti di colonna. Nell'esempio seguente viene utilizzata un'istruzione SELECT che filtra i risultati.

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

Nell'esempio seguente viene utilizzata la navigazione con parentesi e punti nelle clausole GROUP BY e 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;

Annullamento di query

Per annullare le query, Amazon Redshift utilizza la sintassi PartiQL per eseguire l'iterazione su array SUPER. Lo fa navigando nell'array utilizzando la clausola FROM di una query. Utilizzando l'esempio precedente, nell'esempio seguente vengono eseguite interazioni sui valori dell'attributo per c_orders.

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

La sintassi di annullamento nidificazione è un'estensione della clausola FROM. In SQL standard, la clausola FROM x (AS) y significa che in y vengono eseguite iterazioni su ogni tupla in relazione x. In questo caso, x si riferisce a una relazione e y si riferisce a un alias per relazione x. Analogamente, la sintassi PartiQL di unnesting utilizzando 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, si aggiungono più clausole.

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 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)

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;

   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)

Per ulteriori informazioni sulla clausola FROM, consultare Clausola FROM.

Nidificazione di oggetti

Per eseguire la nidificazionet degli oggetti, Amazon Redshift utilizza la sintassi PartiQL per eseguire l'iterazione sugli oggetti SUPER. Lo fa utilizzando la clausola FROM di una query con la parola chiave UNPIVOT. In questo caso, 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 il seguente frammento di 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 gli esempi di query su dati strutturati con PIVOT e UNPIVOT consulta Esempi PIVOT e UNPIVOT.

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.

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

Il segno di uguaglianza in questa query restituisce truequando c_orders[0].o_orderstatus è la stringa 'P'. In tutti gli altri casi, il segno di uguaglianza restituiscefalse, compresi i casi in cui gli argomenti dell'uguaglianza sono tipi diversi.

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 sostituisci 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 una 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;

Tipi di introspezione

Le colonne di dati SUPER supportano funzioni di ispezione che restituiscono il tipo dinamico e altre informazioni di tipo sul valore SUPER. L'esempio più comune è la funzione scalare JSON_TYPEOF che restituisce un VARCHAR con valori booleani, number, string, object, array o null, a seconda del tipo dinamico del valore SUPER. Amazon Redshift supporta le seguenti funzioni booleane per le colonne di dati SUPER:

• DECIMAL_PRECISION

• DECIMAL_SCALE

• IS_ARRAY

• IS_BIGINT

• IS_CHAR

• IS_DECIMAL

• IS_FLOAT

• IS_INTEGER

• IS_OBJECT

• IS_SCALARE

• IS_SMALLINT

• IS_VARCHAR

• JSON_TYPEOF

Tutte queste funzioni restituiscono false se il valore di input è null. IS_SCALAR, IS_OBJECT e IS_ARRAY si escludono a vicenda e coprono tutti i valori possibili ad eccezione di null.

Per dedurre i tipi corrispondenti ai dati, Amazon Redshift utilizza la funzione JSON_TYPEOF che restituisce il tipo (il livello superiore) del valore SUPER, come mostrato nell'esempio seguente:

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 lo vede come una singola stringa lunga, simile all'inserimento di questo valore in una colonna VARCHAR invece che in una SUPER. Poiché la colonna è SUPER, la singola stringa è ancora un valore SUPER valido e la differenza è annotata in 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;

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. Nell'esempio seguente vengono illustrati gli ordini di tipi diversi:

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)

Per ulteriori informazioni sulla clausola ORDER BY, consultare Clausola ORDER BY.