Perché ricevo l'errore "HIVE_BAD_DATA: Error parsing field value for field X for input string:" quando interrogo i dati JSON in Amazon Athena?

Breve descrizione

Esistono diverse versioni dell'errore HIVE_BAD_DATA. Il messaggio di errore potrebbe specificare una stringa di input nulla o vuota, come ad esempio "For input string: """. Per questo tipo di messaggio di errore, consulta Perché la mia query su Amazon Athena fallisce con l'errore "HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691""?

Gli errori che specificano una stringa di input con un valore si verificano in una delle seguenti condizioni:

• Il tipo di dati indicato nella definizione della tabella non corrisponde ai dati di origine effettivi.
• Un singolo campo contiene diversi tipi di dati (ad esempio, un valore intero per un record e un valore decimale per un altro record).

Risoluzione

È consigliabile utilizzare un solo tipo di dati nella stessa colonna. Altrimenti la query potrebbe avere esito negativo. Per risolvere gli errori, assicurati che ogni colonna contenga valori dello stesso tipo di dati e che i valori rientrino negli intervalli consentiti.

Se continui a ricevere errori, modifica il tipo di dati della colonna scegliendo un tipo di dati compatibile con un intervallo più elevato. Se la modifica del tipo di dati non risolve il problema, prova le soluzioni illustrate negli esempi seguenti.

Esempio 1

• Formato di origine: JSON
• Problema: nell'ultimo record, il valore della chiave id è "0.54". Questo valore è un tipo di dato DECIMAL. Per gli altri record, il valore della chiave id è impostato su INT.

Dati di origine:

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

Istruzione DDL (Data Definition Language):

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

Istruzione DML (Data Manipulation Language):

SELECT * FROM jsontest_error_hive_bad_data

Errore:

HIVE_BAD_DATA: Error reading field value: For input string: "0.54"
This query ran against the 'default' database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query ID: add6f434-3397-4c80-a55f-1672a0102e32

Per risolvere il problema, ridefinisci la colonna id come STRING. Il tipo di dati STRING può rappresentare correttamente tutti i valori di questo set di dati.

Istruzione DDL modificata:

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

Istruzione DML:

SELECT * FROM jsontest_error_hive_bad_data_correct_id_data_type

Puoi anche effettuare la conversione nel tipo di dati desiderato. Ad esempio, puoi convertire una stringa in numero intero. Tuttavia, a seconda dei tipi di dati da e verso cui effettui la conversione, potrebbero essere restituiti risultati nulli o imprecisi. I valori che non possono essere convertiti vengono scartati. Ad esempio, se converti il valore della stringa "0.54" in INT, vengono restituiti risultati nulli:

SELECT TRY_CAST(id AS INTEGER) FROM jsontest_error_hive_bad_data_correct_id_data_type

Esempio di output:

Results     _col0
1    50
2    51
3    53
4

L'output mostra che il valore "0.54" è stato scartato. Non puoi convertire tale valore direttamente da una stringa in un numero intero. Per risolvere il problema, utilizza la funzione COALESCE per effettuare una conversione di valori di tipo misto nella stessa colonna dell'output. Quindi consenti l'esecuzione della funzione di aggregazione sulla colonna. Esempio:

SELECT COALESCE(TRY_CAST(id AS INTEGER), TRY_CAST(id AS DECIMAL(10,2))) FROM jsontest_error_hive_bad_data_correct_id_data_type

Output:

Results     _col0
1    50.00
2    51.00
3    53.00
4    0.54

Esegui funzioni aggregate:

SELECT SUM(COALESCE(TRY_CAST(id AS INTEGER), TRY_CAST(id AS DECIMAL(10,2)))) FROM jsontest_error_hive_bad_data_correct_id_data_type

Output:

  _col01    154.54

Esempio 2

• Formato di origine: JSON
• Problema: la colonna id è definita come INT. Athena non ha potuto analizzare "49612833315" perché l'intervallo per i valori INT in Presto è compreso tra -2147483648 e 2147483647.

Dati di origine:

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

Istruzione DDL:

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

Istruzione DML:

SELECT * FROM jsontest_error_hive_bad_data_sample_2

Errore:

HIVE_BAD_DATA: Error reading field value: For input string: "49612833315"
This query ran against the 'default' database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query ID: 25be5ca1-0799-4c21-855d-b9171aadff47

Per risolvere il problema, definisci la colonna id come BIGINT, che può leggere il valore "49612833315". Per ulteriori informazioni, consulta Tipi Integer.

Istruzione DDL modificata:

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

Esempio 3

• Formato di origine: JSON
• Problema: i dati di input sono DECIMAL e la colonna è definita come DECIMAL nella definizione della tabella. Tuttavia, la scala è definita come 2, e 2 non corrisponde al valore "0.000054". Per ulteriori informazioni, consulta Tipo DECIMAL o NUMERIC.

Dati di origine:

{ "id" : 0.50, "name":"John" }
{ "id" : 0.51, "name":"Jane" }
{ "id" : 0.53, "name":"Jill" }
{ "id" : 0.000054, "name":"Jill" }

Istruzione DDL:

CREATE EXTERNAL TABLE jsontest_error_hive_bad_data_sample_3(    id DECIMAL(10,2),
    name STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/jsontest_error_hive_bad_data_3/';

Istruzione DML:

SELECT * FROM jsontest_error_hive_bad_data_sample_3

Errore:

HIVE_BAD_DATA: Error Parsing a column in the table: target scale must be larger than source scale
This query ran against the 'default' database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query ID: 97789f62-d2a5-4749-a506-26480a91a1db

Per risolvere il problema, ridefinisci la colonna con una scala che acquisisca tutti i valori di input. Ad esempio, anziché (10,2) utilizza (10,7).

Istruzione DDL modificata:

CREATE EXTERNAL TABLE jsontest_error_hive_bad_data_sample_3_corrected(    id DECIMAL(10,7),
    name STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/jsontest_error_hive_bad_data_3/';

Esempio 4

• Formato di origine: JSON
• Problema: i dati di input sono STRING e la colonna è definita come INT nella definizione della tabella. Tuttavia, Athena non può analizzare il record STRING come chiave id definita INT.

Dati di origine

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

Istruzione DDL:

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

Istruzione DML:

SELECT * FROM jsontest_error_hive_bad_data_sample_4

Errore:

HIVE_BAD_DATA: Error reading field value: For input string: "one"
This query ran against the 'default' database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query ID: 291b69f6-48f9-4381-af72-6786b3a90826

Questo errore si verifica perché i dati di origine effettivi hanno una definizione del tipo di dati incompatibile.

Per risolvere il problema, ridefinisci la colonna id come STRING. Il tipo di dati STRING può rappresentare correttamente tutti i valori di questo set di dati. Esempio:

Istruzione DDL modificata:

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

Informazioni correlate

Errori correlati a JSON

Risoluzione dei problemi in Athena