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à.

Query sui dati semistrutturati

Con Amazon Redshift, puoi interrogare e analizzare dati semistrutturati, come Avro o IonJSON, 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 SQL compatibile a dati relazionali, semistrutturati e annidati.

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 FROM clausola di iterare su array e di utilizzarli per operazioni non aggregate. Di seguito, è possibile trovare le descrizioni dei diversi modelli di query che combinano l'uso del tipo di SUPER dati con la navigazione tra percorsi e array, il unnesting, l'unpivoting e i join.

Per informazioni sulle tabelle utilizzate nell'esempio seguente, consulta SUPERset di dati di esempio.

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

Per acquisire dati nella tabella customer_orders_lineitem, eseguire il seguente comando. Sostituisci il IAM ruolo con le tue 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. L'esempio seguente utilizza un'SELECTistruzione che filtra i risultati.

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

L'esempio seguente utilizza la navigazione tra 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 iterare sugli array. SUPER A tale scopo, naviga nell'array utilizzando la clausola di una query. FROM 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 unnesting è un'estensione della clausola. FROM In standardSQL, la FROM clausola x (AS) y significa che y itera 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. Allo stesso modo, la sintassi PartiQL di unnesting che utilizza l'elemento FROM clausola x (AS) y significa che y itera su ogni valore () nell'espressione dell'array (SUPER) x. SUPER In questo caso, x è un'SUPERespressione ed è un alias per. y 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 FROM clausola, vedere. Clausola FROM

Nidificazione di oggetti

Per eseguire l'unpivoting degli oggetti, Amazon Redshift utilizza la sintassi PartiQL per iterare sugli oggetti. SUPER A tale scopo, utilizza la FROM clausola di una query con la parola chiave. UNPIVOT In questo caso, l'espressione è l'c.c_orders[0]oggetto. 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 per l'unnesting, anche la sintassi unpivoting è un'estensione della clausola. FROM La differenza è che la sintassi unpivoting utilizza la UNPIVOT parola chiave 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 come segue: FROM

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, consulta. FROM Clausola FROM Per esempi che mostrano come eseguire query su dati strutturati, con PIVOT eUNPIVOT, vediEsempi 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 tipizzazione dinamica per elaborare SUPER dati 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 SUPER dati senza doverli inserire esplicitamente nei tipi di Amazon Redshift. La digitazione dinamica è particolarmente utile nei join e nelle clausole BY. GROUP L'esempio seguente utilizza un'SELECTistruzione che non richiede il trasferimento esplicito delle espressioni con punti e parentesi ai tipi usuali di 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. Puoi solo determinare che c_orders [0] .o_orderstatus è un tipo di SUPER dati, che può essere uno scalare, un array o una struttura di Amazon Redshift. Il tipo statico di c_orders [0] .o_orderstatus è un tipo di dati. SUPER Convenzionalmente, un tipo è implicitamente un tipo statico in. SQL

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.

Quando si utilizza un SQL operatore o una funzione con espressioni di punti e parentesi con tipi dinamici, Amazon Redshift produce risultati simili all'utilizzo di operatori o funzioni SQL standard 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, puoi convertire entrambi i tipi CHAR scalari di VARCHAR Amazon Redshift in. SUPER Sono simili alle stringhe, incluso l'ignoramento dei caratteri di spazio bianco finali simili a quelli di Amazon Redshift e ai tipi. CHAR VARCHAR Allo stesso modo, numeri interi, decimali e valori 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 abbina automaticamente i valori con diversi tipi dinamici senza eseguire un'CASEWHENanalisi 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 SUPER valori restituiscono null anziché restituire un errore quando la navigazione non è valida. La navigazione tra oggetti non è valida se il SUPER valore non è un oggetto o se il SUPER valore è un oggetto ma non contiene il nome dell'attributo utilizzato nella query. Ad esempio, la seguente query accede a un nome di attributo non valido nella colonna di SUPER dati cdata:

SELECT c.c_orders.something FROM customer_orders_lineitem c;

La navigazione tra matrici restituisce null se il SUPER valore non è un array o se l'indice dell'array non è compreso nei 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 semantica permissiva è particolarmente utile quando si utilizza la digitazione dinamica per trasmettere un valore. SUPER L'assegnazione di un SUPER valore al tipo sbagliato restituisce null anziché un errore se il cast non è valido. Ad esempio, la seguente query restituisce null perché non può trasmettere il valore di stringa 'Good' dell'attributo dell'oggetto o_orderstatus su. INTEGER Amazon Redshift restituisce un errore per un VARCHAR INTEGER cast ma non per un SUPER cast.

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

Tipi di introspezione

SUPERle colonne di dati supportano funzioni di ispezione che restituiscono il tipo dinamico e altre informazioni relative al SUPER valore. L'esempio più comune è la funzione TYPEOF scalare JSON _ che restituisce un valore VARCHAR con valori boolean, 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

Tutte queste funzioni restituiscono false se il valore di input è null. IS_SCALAR, IS_ e IS_ OBJECT ARRAY si escludono a vicenda e coprono tutti i valori possibili tranne null.

Per dedurre i tipi corrispondenti ai dati, Amazon Redshift utilizza JSON la funzione TYPEOF _ che restituisce il tipo (il livello più alto di) SUPER il valore, 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 vede questa stringa come una singola stringa lunga, simile all'inserimento di questo valore in una VARCHAR colonna anziché in una. SUPER Poiché la colonna èSUPER, la singola stringa è ancora un SUPER valore valido e la differenza è indicata 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 SUPER confronti tra valori con diversi tipi dinamici. Un SUPER valore che è una stringa non è né più piccolo né più grande di un SUPER valore che è un numero. Per utilizzare le clausole ORDER BY con SUPER colonne, Amazon Redshift definisce un ordinamento totale tra diversi tipi da osservare quando Amazon Redshift classifica i valori utilizzando le clausole BY. SUPER ORDER 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 BY, consulta. ORDER Clausola ORDER BY