Caricamento di dati semistrutturati in Amazon Redshift

Utilizzare il tipo di dati SUPER per mantenere ed eseguire query sui dati gerarchici e generici in Amazon Redshift. Amazon Redshift presenta la funzione json_parse per analizzare i dati in formato JSON e convertirli nella rappresentazione SUPER. Amazon Redshift supporta anche il caricamento delle colonne SUPER tramite il comando COPY. I formati file supportati sono JSON, Avro, text, formato CSV (comma-separated value, valori delimitati da virgole), Parquet e ORC.

Per informazioni sulle tabelle utilizzate negli esempi seguenti, consulta Set di dati di esempio SUPER.

Per ulteriori informazioni sulla funzione json_parse, consultare Funzione JSON_PARSE.

La codifica predefinita per il tipo di dati SUPER è ZSTD.

Analisi dei documenti JSON nelle colonne SUPER

È possibile inserire o aggiornare i dati JSON in una colonna SUPER utilizzando la funzione json_parse. La funzione analizza i dati in formato JSON e li converte nel tipo di dati SUPER, che è possibile utilizzare nelle istruzioni INSERT o UPDATE.

Nell'esempio seguente i dati JSON vengono inseriti in una colonna SUPER. Se la funzione json_parse non è presente nella query, Amazon Redshift considera il valore come una singola stringa anziché una stringa in formato JSON che deve essere analizzata.

Se si aggiorna una colonna di dati SUPER, Amazon Redshift richiede che il documento completo venga passato ai valori delle colonne. Amazon Redshift non supporta l'aggiornamento parziale.

INSERT INTO region_nations VALUES(0,
   'lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to',
   'AFRICA',
   JSON_PARSE('{"r_nations":[
      {"n_comment":" haggle. carefully final deposits detect slyly agai",
         "n_nationkey":0,
         "n_name":"ALGERIA"
      },
      {"n_comment":"ven packages wake quickly. regu",
         "n_nationkey":5,
         "n_name":"ETHIOPIA"
      },
      {"n_comment":" pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t",
         "n_nationkey":14,
         "n_name":"KENYA"
      },
      {"n_comment":"rns. blithely bold courts among the closely regular packages use furiously bold platelets?",
         "n_nationkey":15,
         "n_name":"MOROCCO"
      },
      {"n_comment":"s. ironic, unusual asymptotes wake blithely r",
         "n_nationkey":16,
         "n_name":"MOZAMBIQUE"
      }
   ]
}'));

Utilizzo di COPY per caricare le colonne SUPER in Amazon Redshift

Nelle sezioni seguenti, è possibile ottenere informazioni sui diversi modi di utilizzare il comando COPY per caricare i dati JSON in Amazon Redshift.

Copia di dati da JSON e Avro

Utilizzando il supporto dei dati semistrutturati in Amazon Redshift, è possibile caricare un documento JSON senza ridurre gli attributi delle sue strutture JSON in più colonne.

Amazon Redshift fornisce due metodi per importare il documento JSON utilizzando COPY, anche con una struttura JSON completamente o parzialmente sconosciuta:

1. Archiviare i dati derivanti da un documento JSON in una singola colonna di dati SUPER utilizzando l'opzione noshred. Questo metodo è utile quando lo schema non è noto o si prevede che cambi. Pertanto, questo metodo rende più facile archiviare l'intera tupla in una singola colonna SUPER.

2. Ridurre il documento JSON in più colonne Amazon Redshift utilizzando l'opzione auto o jsonpaths. Gli attributi possono essere valori scalari di Amazon Redshift o valori SUPER.

È possibile utilizzare queste opzioni con i formati JSON o Avro.

La dimensione massima per un oggetto JSON prima della riduzione è 4 MB.

Copia di un documento JSON in una singola colonna di dati SUPER

Per copiare un documento JSON in una singola colonna di dati SUPER, creare una tabella con una singola colonna di dati SUPER.

CREATE TABLE region_nations_noshred (rdata SUPER);

Copiare i dati da Amazon S3 nella singola colonna di dati SUPER. Per importare i dati di origine JSON in una singola colonna di dati SUPER, specificare l'opzione noshred nella clausola FORMAT JSON.

COPY region_nations_noshred FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'noshred';

Dopo che COPY ha importato correttamente il JSON, la tabella conterrà una colonna rdata di dati SUPER con i dati dell'intero oggetto JSON. I dati importati mantengono tutte le proprietà della gerarchia JSON. Tuttavia, per un'elaborazione efficiente delle query, gli elementi secondari della struttura vengono convertiti in tipi scalari Amazon Redshift.

Utilizzare la seguente query per recuperare la stringa JSON originale.

SELECT rdata FROM region_nations_noshred;

Quando Amazon Redshift genera una colonna di dati SUPER, diventa accessibile utilizzando JDBC come stringa tramite la serializzazione JSON. Per ulteriori informazioni, consultare Serializzazione di JSON nidificato complesso.

Copia di un documento JSON in più colonne di dati SUPER

È possibile suddividere un documento JSON in più colonne che possono essere colonne di dati SUPER o tipi scalari Amazon Redshift. Amazon Redshift diffonde diverse parti dell'oggetto JSON su colonne diverse.

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

Per copiare i dati dell'esempio precedente nella tabella, specificare l'opzione AUTO nella clausola FORMAT JSON per dividere il valore JSON su più colonne. COPY corrisponde agli attributi JSON di primo livello con i nomi delle colonne e consente l'inserimento dei valori nidificati come valori SUPER, ad esempio array e oggetti JSON.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 'auto';

Quando i nomi degli attributi JSON sono in lettere maiuscole e minuscole miste, specificare l'opzione auto ignorecase nella clausola FORMAT JSON. Per ulteriori informazioni sul comando COPY, consultare Caricamento da dati JSON utilizzando l'opzione 'auto ignorecase'.

In alcuni casi, c'è una mancata corrispondenza tra i nomi delle colonne e gli attributi JSON o l'attributo da caricare è nidificato con una profondità di più di un livello. In tal caso, utilizzare un file jsonpaths per mappare manualmente gli attributi JSON alle colonne Amazon Redshift.

CREATE TABLE nations
(
 regionkey smallint
 ,name varchar
 ,comment super
 ,nations super
 ); 

Si supponga di voler caricare i dati in una tabella in cui i nomi delle colonne non corrispondono agli attributi JSON. Nell'esempio seguente, la tabella nations è una tabella del genere. È possibile creare un file jsonpaths che mappa i percorsi degli attributi alle colonne della tabella in base alla loro posizione nell'array jsonpaths.

{"jsonpaths": [
       "$.r_regionkey",
       "$.r_name",
       "$.r_comment",
       "$.r_nations
    ]
}

La posizione del file jsonpaths viene utilizzata come argomento per FORMAT JSON.

COPY nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 's3://redshift-downloads/semistructured/tpch-nested/data/jsonpaths/nations_jsonpaths.json';

Utilizzare la query seguente per accedere alla tabella che mostra la distribuzione dei dati su più colonne. Le colonne di dati SUPER vengono stampate utilizzando il formato JSON.

SELECT r_regionkey,r_name,r_comment,r_nations[0].n_nationkey FROM region_nations ORDER BY 1,2,3 LIMIT 1;

I file Jsonpath mappano i campi del documento JSON alle colonne della tabella. È possibile estrarre colonne aggiuntive, come le chiavi di distribuzione e ordinamento, mentre continuano a caricare il documento completo come colonna SUPER. La seguente query carica il documento completo nella colonna nazioni. La colonna name è la chiave di ordinamento e la colonna regionkey è la chiave di distribuzione.

CREATE TABLE nations_sorted (
    regionkey smallint,
    name varchar,
    nations super
) DISTKEY(regionkey) SORTKEY(name);

Il root jsonpath «$» mappa alla radice del documento come segue:

{"jsonpaths": [
       "$.r_regionkey",
       "$.r_name",
       "$"
    ]
}

La posizione del file jsonpath viene utilizzata come argomento per FORMAT JSON.

COPY nations_sorted FROM 's3://redshift-downloads/semistructured/tpch-nested/data/json/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT JSON 's3://redshift-downloads/semistructured/tpch-nested/data/jsonpaths/nations_sorted_jsonpaths.json';

Copia di dati da testo e CSV

Amazon Redshift rappresenta colonne SUPER nei formati testo e CSV come JSON serializzato. È necessaria una formattazione JSON valida per il caricamento delle colonne SUPER con le informazioni sul tipo corrette. Oggetti, array, numeri, booleani e valori nulli non quotati. Avvolgi i valori di stringa tra virgolette. Le colonne SUPER utilizzano regole di escape standard per i formati testo e CSV. Per CSV, i delimitatori sono sfuggiti secondo lo standard CSV. Per il testo, se il delimitatore scelto appare anche in un campo SUPER, utilizzare l'opzione ESCAPE durante COPY e UNLOAD

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/csv/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT CSV;

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/text/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
DELIMITER ','
ESCAPE;

Copia dei dati da Parquet e ORC in formato colonna

Se i dati semistrutturati o nidificati sono già disponibili in formato Apache Parquet o Apache ORC, è possibile usare il comando COPY per inserire i dati in Amazon Redshift.

La struttura della tabella Amazon Redshift deve corrispondere al numero di colonne e ai tipi di dati delle colonne dei file Parquet o ORC. Specificando SERIALIZETOJSON nel comando COPY, è possibile caricare qualsiasi tipo di colonna nel file che si allinea con una colonna SUPER nella tabella come SUPER. Ciò include la struttura e i tipi di array.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/parquet/region_nation'
REGION 'us-east-1' IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT PARQUET SERIALIZETOJSON;

Nell'esempio seguente viene utilizzato un formato ORC.

COPY region_nations FROM 's3://redshift-downloads/semistructured/tpch-nested/data/orc/region_nation'
IAM_ROLE 'arn:aws:iam::xxxxxxxxxxxx:role/Redshift-S3'
FORMAT ORC SERIALIZETOJSON;

Quando gli attributi dei tipi di dati di data o ora sono in ORC, Amazon Redshift li converte in varchar dopo averli codificati in SUPER.