Pourquoi ma requête Athena échoue-t-elle avec l’erreur « HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691" » ?

Brève description

Il existe plusieurs versions de l'erreur HIVE_BAD_DATA. Si le message d'erreur spécifie une chaîne d'entrée nulle ou vide, telle que « For input string: "" », consultez la section Pourquoi l'erreur « HIVE_BAD_DATA: Error parsing field value for field X for input string: » s’affiche-t-elle lorsque j’interroge des données JSON dans Amazon Athena ?

Les erreurs qui spécifient une chaîne d'entrée avec une valeur se produisent dans l'un des scénarios suivants :

• Le type de données défini dans la définition de la table ne correspond pas aux données source réelles.
• Un seul champ contient différents types de données, tels qu'une valeur entière pour un enregistrement et une valeur décimale pour un autre enregistrement.

Résolution

Il est recommandé de n'utiliser qu'un seul type de données dans une colonne. Dans le cas contraire, la requête risque d'échouer. Pour résoudre les erreurs, assurez-vous que chaque colonne contient des valeurs du même type de données et que les valeurs se situent dans les plages autorisées.

Si les erreurs persistent, remplacez le type de données de la colonne par un type de données compatible dont la plage est plus élevée. Si la modification du type de données ne résout pas le problème, essayez les solutions décrites dans les exemples suivants.

Exemple 1

Dans l'exemple suivant, le format source est JSON. Le problème concerne le dernier enregistrement car la valeur de la clé d’identification est le type de données DECIMAL (0,54). Cependant, la valeur de la clé d’identification pour les autres enregistrements est définie sur INT.

Données sources :

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

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

Instruction DML (Data Manipulation Language) :

SELECT * FROM jsontest_error_hive_bad_data

Error:

« 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. »

Pour résoudre ce problème, redéfinissez la colonne id comme STRING. Le type de données STRING peut représenter correctement toutes les valeurs de jeu de données.

Exemple :

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

Instruction DML :

SELECT * FROM jsontest_error_hive_bad_data_correct_id_data_type

Vous pouvez également effectuer une conversion selon le type de données de votre choix. Vous pouvez, par exemple, convertir une chaîne en entier. Toutefois, pour certains types de données, cette méthode peut renvoyer des résultats nuls ou inexacts à partir desquels et vers lesquels vous effectuez une conversion. Les valeurs que vous ne pouvez pas convertir sont ignorées. Par exemple, si vous convertissez la valeur de chaîne 0.54 en INT, les résultats nuls sont renvoyés :

SELECT TRY_CAST(id AS INTEGER) FROM jsontest_error_hive_bad_data_correct_id_data_type

Exemple de sortie :

Results     _col0
1    50
2    51
3    53
4

La sortie indique que la valeur 0.54 a été supprimée. Vous ne pouvez pas convertir cette valeur directement d’une chaîne en un entier. Pour résoudre ce problème, utilisez COALESCE pour convertir les valeurs de type mixte dans la même colonne que la sortie. Puis, autorisez la fonction d’agrégation à exécuter sur la colonne.

Exemple :

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

Sortie :

Results     _col0
1    50.00
2    51.00
3    53.00
4    0.54

Exécutez des fonctions d'agrégation :

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

Sortie :

_col01    154.54

Exemple 2

Dans l'exemple suivant, le format source est JSON. La colonne id est définie comme INT. Athena ne peut pas analyser « 49612833315 » car la plage de valeurs INT dans Presto est -2147483648 et 2147483647.

Données sources :

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

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

Instruction DML :

SELECT * FROM jsontest_error_hive_bad_data_sample_2

Error:

« 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. »

Pour résoudre ce problème, définissez la colonne id comme bigint, car le type de données BIGINT peut lire la valeur « 49612833315 ». Pour plus d’informations, consultez la section Types d’entiers.

Instruction DDL modifiée :

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

Exemple 3

Dans l'exemple suivant, le format source est JSON. Les données d'entrée sont de type DECIMAL et la colonne est définie comme decimal dans la définition de la table. Toutefois, l'échelle est définie comme 2 et ne correspond pas à la valeur « 0,000054 ». Pour plus d'informations, consultez la section Type DECIMAL ou NUMERIC.

Données sources :

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

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

Instruction DML :

SELECT * FROM jsontest_error_hive_bad_data_sample_3

Error:

« 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. »

Pour résoudre ce problème, redéfinissez la colonne avec une échelle qui capture toutes les valeurs d'entrée. Par exemple, au lieu de (10,2), utilisez (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/';

Exemple 4

Dans l'exemple suivant, le format source est CSV. Dans le schéma, attribute2 contient STRING et le format est date. Le schéma de la table le définit comme étant le type DATE.

Données sources :

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

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

Error:

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

Pour résoudre ce problème, modifiez le type attribute2 de DATE à STRING.

Données sources modifiées :

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

Instruction DDL modifiée :

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

Informations connexes

Types de données dans Amazon Athena

Résolution des problèmes dans Athena