Perché la mia query Athena ha esito negativo con l'errore "HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691""?

Breve descrizione

Esistono diverse versioni dell'errore HIVE_BAD_DATA. Se il messaggio di errore specifica una stringa di input nulla o vuota (ad esempio, “"For input string: """), consulta 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?

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

• 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

Nel seguente esempio, il formato di origine è JSON. Il problema riguarda l'ultimo record perché il valore della chiave id è il tipo di dati DECIMAL (0.54). 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:

"Your query has the following error(s):HIVE_BAD_DATA: Error parsing field value '0.54' for field 0: 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: bd50793b-94fc-42a7-b131-b7c81da273b2."

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:

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

Nel seguente esempio, il formato di origine è JSON. La colonna id è definita come INT. Athena non può 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:

"Your query has the following error(s):HIVE_BAD_DATA: Error parsing field value '49612833315' for field 0: 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: 05b55fb3-481a-4012-8c0d-c27ef1ee746f."

Per risolvere il problema, definisci la colonna id come bigint perché il tipo di dati BIGINT 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

Nel seguente esempio, il formato di origine è JSON. I dati di input sono DECIMAL e la colonna è definita come decimal nella definizione della tabella. Tuttavia, la scala è definita come 2 e 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:

"Your query has the following error(s):HIVE_BAD_DATA: Error parsing column '0': 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: 1c3c7278-7012-48bb-8642-983852aff999."

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

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

Nel seguente esempio, il formato di origine è CSV. Nello schema, attribute2 contiene STRING e il formato è date. Lo schema della tabella lo definisce come tipo DATE.

Dati di origine:

"attribute1","attribute2","attribute3","attribute4"
"1","2018-01-01","10.01","Hello!"
"2","2018-01-02","20.02","Hi!"
"3","2018-01-03","30.03","How are you"

Istruzione DDL:

CREATE EXTERNAL TABLE test (attribute1 int, attribute2 date, attribute3  float, attribute4 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
LOCATION 's3://awsexamplebucket/csvtest_error_hive_bad_data/'
TBLPROPERTIES ("skip.header.line.count"="1");

Errore:

"HIVE_BAD_DATA: Error reading field value: Cannot convert value 2018-01-01 of type String to a LONG value"

Per risolvere il problema, modifica il tipo attributo2 da DATE a STRING.

Dati di origine modificati:

"attribute1","attribute2","attribute3","attribute4"
"1","17532","10.01","Hello!"
"2","17533","20.02","Hi!"
"3","17534","30.03","How are you"

Istruzione DDL modificata:

CREATE EXTERNAL TABLE test (attribute1 int, attribute2 date, attribute3  float, attribute4 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
LOCATION 's3://awsexamplebucket/csvtest_error_hive_bad_data2/'
TBLPROPERTIES ("skip.header.line.count"="1");

Informazioni correlate

Tipi di dati in Amazon Athena

Risoluzione dei problemi in Athena