Pourquoi est-ce que ma requête Amazon Athena échoue 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 null ou vide (par exemple, « For input string: "" »), consultez Pourquoi est-ce que je reçois l'erreur « HIVE_BAD_DATA: Error parsing field value for field X: For input string: "" » lorsque j'interroge des données CSV dans Amazon Athena ?

Les erreurs qui spécifient une chaîne d'entrée avec une valeur sous l'une des conditions suivantes :

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

Solution

Il est recommandé d'utiliser un seul type de données dans une colonne. Sinon, la requête peut é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 trouvent dans les plages autorisées.

Si vous recevez toujours des erreurs, remplacez le type de données de la colonne par un type de données compatible ayant une plage plus grande. Si vous ne parvenez pas à résoudre le problème en modifiant le type de données, essayez les solutions décrites dans les exemples suivants.

Exemple 1

• Format source : JSON
• Problème : dans le dernier enregistrement, la valeur de clé « id » est « 0.54 », qui est le type de données DECIMAL. Toutefois, pour les autres enregistrements, la valeur de clé « id » 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

Erreur :

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 cet ensemble 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 commande CAST sur le type de données souhaité. Par exemple, vous pouvez convertir une chaîne en nombre entier. Toutefois, en fonction des types de données depuis et vers lesquels vous effectuez une conversion, les résultats peuvent être null ou inexacts. Les valeurs qui ne peuvent pas faire l'objet d'une conversion sont ignorées. Par exemple, convertir la valeur de chaîne « 0.54 » en INT renvoie des résultats null :

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é ignorée. Vous ne pouvez pas convertir cette valeur directement d'une chaîne en un nombre entier. Pour résoudre ce problème, utilisez COALESCE pour effectuer une commande CAST sur les valeurs de type mixte dans la même colonne que la sortie. Autorisez ensuite la fonction d'agrégation à s'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 les 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 :

_col0
1    154.54

Exemple 2

• Format source : JSON
• Problème : la colonne « id » est définie comme INT. Athena n'a pas pu analyser « 49612833315 », car la plage des valeurs INT dans Presto s'étend de -2147483648 à 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

Erreur :

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, qui peut lire la valeur « 49612833315 ». Consultez les Types de valeurs entières pour plus d'informations.

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

• Format source : JSON
• Problème : les données en entrée sont DECIMAL et la colonne est définie comme DECIMAL dans la définition de table. Toutefois, l'échelle est définie sur 2, ce qui ne correspond pas à la valeur « 0.000054 ». Consultez Type DECIMAL ou NUMERIC pour plus d'informations.

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

Erreur :

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

---