Warum schlägt meine Amazon Athena-Abfrage mit dem Fehler „HIVE_BAD_DATA: Error parsing field value for field X: For input string: „12312845691““ fehl?

Kurzbeschreibung

Es gibt mehrere Versionen des HIVE_BAD_DATA-Fehlers. Wenn die Fehlermeldung eine Null- oder leere Eingabezeichenfolge angibt, z. B. „For input string: ""“, findest du weitere Informationen unter Warum erhalte ich den Fehler „HIVE_BAD_DATA: Error parsing field value for field X for input string:“, wenn ich JSON-Daten in Amazon Athena abfrage?

Fehler, die eine Eingabezeichenfolge mit einem Wert angeben, treten in einem der folgenden Szenarien auf:

• Der in der Tabellendefinition definierte Datentyp stimmt nicht mit den tatsächlichen Quelldaten überein.
• Ein einzelnes Feld enthält verschiedene Datentypen, z. B. einen Ganzzahlwert für einen Datensatz und einen Dezimalwert für einen anderen Datensatz.

Lösung

Es hat sich bewährt, nur einen Datentyp in einer Spalte zu verwenden. Andernfalls schlägt die Abfrage möglicherweise fehl. Um Fehler zu beheben, stelle sicher, dass jede Spalte Werte desselben Datentyps enthält und dass die Werte in den zulässigen Bereichen liegen.

Wenn du immer noch Fehlermeldungen erhältst, ändere den Datentyp der Spalte in einen kompatiblen Datentyp mit einem höheren Bereich. Wenn die Änderung des Datentyps das Problem nicht löst, probiere die Lösungen in den folgenden Beispielen aus. 

Beispiel 1

Im folgenden Beispiel ist das Quellformat JSON. Das Problem liegt beim letzten Datensatz, da der id-Schlüsselwert vom Datentyp DECIMAL ist (0,54). Der id-Schlüsselwert für die anderen Datensätze ist jedoch auf INT gesetzt.

Quelldaten:

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

Datenbeschreibungssprach (DDL)-Anweisung:

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/';

Datenbearbeitungssprach (DML)-Anweisung:

SELECT * FROM jsontest_error_hive_bad_data

Fehler:

„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.“

Um dieses Problem zu beheben, definiere die id-Spalte als string neu. Der STRING-Datentyp kann alle Werte in diesem Datensatz korrekt darstellen.

Beispiel:

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/';

DML-Anweisung:

SELECT * FROM jsontest_error_hive_bad_data_correct_id_data_type

Du kannst den Datentyp auch in deinen bevorzugten Datentyp umwandeln. Zum Beispiel kannst du eine Zeichenfolge in eine Ganzzahl umwandeln. Bei einigen Datentypen gibt dies jedoch möglicherweise Null- oder ungenaue Ergebnisse zurück, wenn du von und in Datentypen umwandelst. Werte, die du nicht umwandeln kannst, werden verworfen. Wenn du beispielsweise den Zeichenfolgenwert 0,54 in INT umwandelst, werden Nullergebnisse zurückgegeben:

SELECT TRY_CAST(id AS INTEGER) FROM jsontest_error_hive_bad_data_correct_id_data_type

Beispielausgabe:

Results     _col0
1    50
2    51
3    53
4

Die Ausgabe zeigt, dass der Wert 0,54 verworfen wurde. Du kannst diesen Wert nicht direkt aus einer Zeichenfolge in eine Ganzzahl umwandeln. Um dieses Problem zu lösen, verwende COALESCE, um die Werte kombinierter Typen in derselben Spalte als Ausgabe umzuwandeln. Lasse anschließend die Aggregatfunktion auf der Spalte ausführen.

Beispiel:

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

Ausgabe:

Results     _col0
1    50.00
2    51.00
3    53.00
4    0.54

Führe Aggregatfunktionen aus:

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

Ausgabe:

_col01    154.54

Beispiel 2

Im folgenden Beispiel ist das Quellformat JSON. Die id-Spalte ist als INT definiert. Athena kann „49612833315“ nicht parsen, da der Bereich für INT-Werte in Presto zwischen -2147483648 und 2147483647 liegt.

Quelldaten:

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

DDL-Anweisung:

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/';

DML-Anweisung:

SELECT * FROM jsontest_error_hive_bad_data_sample_2

Fehler:

„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.“

Um dieses Problem zu beheben, definiere die id-Spalte als bigint, da der BIGINT-Datentyp den Wert „49612833315“ lesen kann. Weitere Informationen findest du unter Ganzzahltypen.

Modifizierte DDL-Anweisung:

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/';

Beispiel 3

Im folgenden Beispiel ist das Quellformat JSON. Die Eingabedaten sind DECIMAL und die Spalte ist in der Tabellendefinition als decimal definiert. Die Skala ist jedoch als 2 definiert und entspricht nicht dem Wert „0,000054“. Weitere Informationen findest du unter Typ DECIMAL oder NUMERIC.

Quelldaten:

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

DDL-Anweisung:

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/';

DML-Anweisung:

SELECT * FROM jsontest_error_hive_bad_data_sample_3

Fehler:

„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.“

Um dieses Problem zu beheben, definiere die Spalte mit einer Skala neu, die alle Eingabewerte erfasst. Verwende beispielsweise (10,7) anstelle von (10,2).

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/';

Beispiel 4

Im folgenden Beispiel ist das Quellformat CSV. Im Schema enthält attribute2 STRING, und das Format ist date. Das Tabellenschema definiert ihn als den DATE-Typ.

Quelldaten:

"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"

DDL-Anweisung:

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");

Fehler:

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

Um dieses Problem zu beheben, ändere den Typ attribute2 von DATE in STRING.

Geänderte Quelldaten:

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

Modifizierte DDL-Anweisung:

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");

Ähnliche Informationen

Datentypen in Amazon Athena

Problembehandlung in Athena