Come posso utilizzare il tipo di dati SUPER in Amazon Redshift per gestire ed eseguire query su dati JSON?

Breve descrizione

Utilizza il tipo di dati SUPER per archiviare dati semistrutturati nei data warehouse Amazon Redshift. Puoi eseguire query sul tipo di dati SUPER con il linguaggio PartiQL.

Per supportare i dati semistrutturati, puoi anche utilizzare Amazon Redshift Spectrum.

Risoluzione

Per caricare dati JSON in dati SUPER, puoi utilizzare il comando COPY o INSERT con la funzione JSON_PARSE. Quando carichi dati di grandi dimensioni, è consigliabile utilizzare il comando COPY.

Utilizza il comando COPY

Per caricare dati JSON da file di dati in Amazon Simple Storage Service (Amazon S3), utilizza il comando COPY. Per ulteriori informazioni, consulta Caricamento di dati semistrutturati in Amazon Redshift.

Per caricare dati JSON in una singola colonna di dati SUPER, utilizza l'opzione noshred.

Per caricare dati JSON in più colonne, utilizza l'opzione auto o specifica il file jsonpaths. Quando utilizzi l'opzione auto, COPY abbina gli attributi JSON di primo livello ai nomi delle colonne e consente di caricare i valori nidificati come valori SUPER. Gli esempi seguenti mostrano come utilizzare l'opzione auto.

Esempio di file di dati JSON:

{"r_regionkey":0,"r_name":"AFRICA","r_nations":[{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]}
 {"r_regionkey":1,"r_name":"AMERICA","r_nations":[{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]}
 {"r_regionkey":2,"r_name":"ASIA","r_nations":[{"n_nationkey":8,"n_name":"INDIA"}]}

Esempio di comando COPY:

# CREATE TABLE region_nations
 (
  r_regionkey smallint
  ,r_name varchar
  ,r_nations super
  );

# COPY region_nations
  FROM 's3://<S3 path to the JSON data file>'
  IAM_ROLE '<IAM role>'
  FORMAT JSON 'auto';

# SELECT * FROM region_nations;
 r_regionkey | r_name  |                                                     r_nations                                                     
-------------+---------+-------------------------------------------------------------------------------------------------------------------
           0 | AFRICA  | [{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]
           1 | AMERICA | [{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]
           2 | ASIA    | [{"n_nationkey":8,"n_name":"INDIA"}]
(3 rows)

Carica i dati JSON in SUPER utilizzando un file di testo o .csv. Assicurati che sia in un formato JSON valido. Amazon Redshift utilizza regole di escape standard per i file .csv.

Esempio di file .csv:

r_regionkey,r_name,r_nations
0,AFRICA,"[{""n_nationkey"":11,""n_name"":""ALGERIA""},{""n_nationkey"":5,""n_name"":""ETHIOPIA""},{""n_nationkey"":14,""n_name"":""KENYA""}]"
1,AMERICA,"[{""n_nationkey"":1,""n_name"":""ARGENTINA""},{""n_nationkey"":2,""n_name"":""BRAZIL""}]"
2,ASIA,"[{""n_nationkey"":8,""n_name"":""INDIA""}]"

Esempio di comando COPY:

# CREATE TABLE region_nations
 (
  r_regionkey smallint
  ,r_name varchar
  ,r_nations super
  );

# COPY region_nations
   FROM 's3://<S3 path to the CSV file>'
   IAM_ROLE '<IAM role>'
   FORMAT CSV
   IGNOREHEADER 1;

# SELECT * FROM region_nations;
 r_regionkey | r_name  |                                                     r_nations                                                     
-------------+---------+-------------------------------------------------------------------------------------------------------------------
           0 | AFRICA  | [{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]
           1 | AMERICA | [{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]
           2 | ASIA    | [{"n_nationkey":8,"n_name":"INDIA"}]
(3 rows)

Utilizza il comando INSERT e la funzione JSON_PARSE

La funzione JSON_PARSE analizza i dati in formato JSON e li converte nel tipo di dati SUPER che puoi utilizzare con il comando INSERT.

Esempio di comando:

# CREATE TABLE region_nations
 (
  r_regionkey smallint
  ,r_name varchar
  ,r_nations super
  );

# INSERT INTO region_nations VALUES(0,'AFRICA',json_parse('[{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]'));
# INSERT INTO region_nations VALUES(1,'AMERICA',json_parse('[{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]'));
# INSERT INTO region_nations VALUES(2,'ASIA',json_parse('[{"n_nationkey":8,"n_name":"INDIA"}]'));

# SELECT * FROM region_nations;
 r_regionkey | r_name  |                                                     r_nations                                                     
-------------+---------+-------------------------------------------------------------------------------------------------------------------
           0 | AFRICA  | [{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]
           1 | AMERICA | [{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]
           2 | ASIA    | [{"n_nationkey":8,"n_name":"INDIA"}]
(3 rows)

Esegui query sui dati SUPER con PartiQL

Per l'accesso compatibile con SQL a dati relazionali, semistrutturati e nidificati, Amazon Redshift utilizza il linguaggio PartiQL. Per ulteriori informazioni, consulta Query sui dati semistrutturati.

Esempio di query:

# SELECT * FROM region_nations;
 r_regionkey | r_name  |                                                     r_nations                                                     
-------------+---------+-------------------------------------------------------------------------------------------------------------------
           0 | AFRICA  | [{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]
           1 | AMERICA | [{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]
           2 | ASIA    | [{"n_nationkey":8,"n_name":"INDIA"}]
(3 rows)

# SELECT region_nations.r_nations[1].n_name FROM region_nations;
   n_name   
------------
 "ETHIOPIA"
 "BRAZIL"

(3 rows)

# SELECT rn.r_regionkey, rn.r_name, n FROM region_nations rn, rn.r_nations n;
 r_regionkey | r_name  |                   n                    
-------------+---------+----------------------------------------
           0 | AFRICA  | {"n_nationkey":11,"n_name":"ALGERIA"}
           0 | AFRICA  | {"n_nationkey":5,"n_name":"ETHIOPIA"}
           0 | AFRICA  | {"n_nationkey":14,"n_name":"KENYA"}
           1 | AMERICA | {"n_nationkey":1,"n_name":"ARGENTINA"}
           1 | AMERICA | {"n_nationkey":2,"n_name":"BRAZIL"}
           2 | ASIA    | {"n_nationkey":8,"n_name":"INDIA"}
(6 rows)

Scarica i dati SUPER in un file JSON

Per estrarre dati dal tipo di dati SUPER e archiviarli in S3 come documento JSON, puoi utilizzare il comando UNLOAD.

Esempio di comando:

# SELECT * FROM region_nations;
 r_regionkey | r_name  |                                                     r_nations                                                     
-------------+---------+-------------------------------------------------------------------------------------------------------------------
           0 | AFRICA  | [{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]
           1 | AMERICA | [{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]
           2 | ASIA    | [{"n_nationkey":8,"n_name":"INDIA"}]
(3 rows)

# UNLOAD ('SELECT * FROM region_nations')
  TO 's3://<S3 path>'
  IAM_ROLE '<IAM role>'
  FORMAT JSON;

Esempio di documento JSON:

{"r_regionkey":0,"r_name":"AFRICA","r_nations":[{"n_nationkey":11,"n_name":"ALGERIA"},{"n_nationkey":5,"n_name":"ETHIOPIA"},{"n_nationkey":14,"n_name":"KENYA"}]}
{"r_regionkey":1,"r_name":"AMERICA","r_nations":[{"n_nationkey":1,"n_name":"ARGENTINA"},{"n_nationkey":2,"n_name":"BRAZIL"}]}
{"r_regionkey":2,"r_name":"ASIA","r_nations":[{"n_nationkey":8,"n_name":"INDIA"}]}