Perché ricevo errori quando provo a leggere i dati JSON in Amazon Athena?

Risoluzione

Controlla i seguenti problemi comuni:

Uso del JSON SerDe corretto

Athena utilizza uno dei seguenti JSON SerDes per elaborare JSON:

• L'Hive JSON SerDe nativo
• L'OpenX JSON SerDe
• L'Amazon Ion Hive SerDe

Se devi eseguire la query INSERT INTO sulla tabella creata, usa Hive JSON SerDe.

Se utilizzi OpenX JSON SerDe, è possibile ignorare i record non validi per identificare i file che causano gli errori. Nell'esempio seguente, quando ignore.malformed.json è impostato su true, i record non validi vengono restituiti come NULL:

CREATE EXTERNAL TABLE json (    id int,
    name string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/';

Interroga la nuova tabella per identificare i file con record non validi eseguendo un comando simile al seguente:

SELECT "$PATH", * FROM your_table where your_column is NULL

Nota: Se esegui query Athena per file JSON ben stampati, puoi utilizzare Amazon Ion Hive SerDe. Amazon Ion Hive SerDe non prevede che ogni riga di dati sia su una singola riga. Usa questa funzione per interrogare i set di dati JSON che sono in un bel formato di stampa o suddividere i campi in una riga con caratteri di nuova riga.

Uso di una riga per record

I seguenti record JSON sono formattati correttamente e possono essere letti da tutti e tre i JSON SerDe:

{ "id" : 50, "name":"John" }
{ "id" : 51, "name":"Jane" }
{ "id" : 53, "name":"Jill" }

I seguenti record JSON possono essere letti solo da Amazon Ion Hive SerDe:

{  "id" : 50,
  "name":"John"
}
{
  "id" : 51,
  "name":"Jane"
}
{
  "id" : 53,
  "name":"Jill"
}

I seguenti dati JSON contengono più record all'interno di una singola riga e sono formattati in modo errato:

{ "id" : 50, "name":"John" } { "id" : 51, "name":"Jane" } { "id" : 53, "name":"Jill" }

Uso del tipo di dati corretto in ogni colonna

La seconda riga dell'esempio seguente contiene un tipo di dati errato per "età". Il valore della colonna deve essere "11" anziché "undici". Ciò causa il seguente messaggio di errore: HIVE_BAD_DATA: Error parsing field value 'eleven' for field 1: For input string: "eleven".

{"name":"Patrick","age":35,"address":"North Street"}
{"name":"Carlos","age":"eleven","address":"Flowers Street"}
{"name":"Fabiana","age":22,"address":"Main Street"}

Uso dell'estensione corretta per i file JSON compressi

Quando si utilizza un file JSON compresso, il file deve terminare nel formato ".gz". Ad esempio, "myfile.json.gz" o "myfile.gz" sono estensioni formattate correttamente.

Uso di colonne senza distinzione tra maiuscole e minuscole o impostazione della proprietà case.insensitive su false

Per impostazione predefinita, Athena non fa distinzione tra maiuscole e minuscole. Se i nomi delle colonne differiscono in base alle maiuscole e minuscole, ad esempio "Colonna" e "colonna", Athena genera il seguente errore:

"HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key"

Inoltre, i tuoi dati non sono visibili in Athena. Per evitare questo errore, genera i tuoi dati con colonne senza distinzione tra maiuscole e minuscole.

Se utilizzi OpenX SerDe, puoi utilizzare nomi di chiavi con distinzione tra maiuscole e minuscole. Per fare ciò, imposta la proprietà SerDe case.insensitive su false e aggiungi la mappatura per la chiave maiuscola. Ad esempio, per utilizzare colonne maiuscole e minuscole come questa:

{"Username": "bob1234", "username": "bob" }

Usa queste proprietà SerDe:

CREATE external TABLE casesensitive_json (user_name String,username String)
ROW FORMAT serde 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'mapping.user_name' = 'Username','case.insensitive'='false')
LOCATION 's3://awsexamplebucket/';

Se il file JSON ha nomi di colonna duplicati con distinzione tra maiuscole e minuscole, aggiorna il file per rimuovere le colonne duplicate:

{"username": "bob1234", "username": "bob" }

Assicurati che tutte le righe nella tabella JSON SerDe siano in formato JSON

Per scoprire se ci sono righe o nomi di file JSON non validi nella tabella Athena, procedi come segue:

1. Crea una tabella con un delimitatore che non è presente nei file di input. Esegui un comando simile al seguente:

   CREATE EXTERNAL TABLE IF NOT EXISTS json_validator (jsonrow string) ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '%'
   location 's3://awsexamplebucket/';

2. Esegui una query simile alla seguente per restituire il nome del file, i dettagli della riga e il percorso Amazon S3 per le righe JSON non valide:

   WITH testdataset AS (SELECT "$path" s3path,jsonrow,try(json_parse(jsonrow)) isjson FROM json_validator)
   SELECT * FROM testdataset WHERE ISJSON IS NULL;

Informazioni correlate

Best practices for reading JSON data

Errori relativi a JSON