¿Por qué aparece el error «HIVE_BAD_DATA: error al analizar el valor de campo X para la cadena de entrada» cuando consulto datos de JSON en Amazon Athena?

Descripción corta

Hay varias versiones del error HIVE_BAD_DATA. El mensaje de error puede especificar una cadena de entrada nula o vacía, como «Para la cadena de entrada: ""». Para este tipo de mensaje de error, consulte ¿Por qué mi consulta en Amazon Athena falla con el error «HIVE_BAD_DATA: error al analizar el valor de campo X para la cadena de entrada: "12312845691"»?

Los errores que especifican una cadena de entrada con un valor se producen en una de las siguientes condiciones:

• El tipo de datos que se define en la definición de la tabla no coincide con los datos de origen reales.
• Un solo campo contiene diferentes tipos de datos, como, por ejemplo, un valor entero para un registro y un valor decimal para otro registro.

Resolución

Se recomienda usar solo un tipo de datos en una columna. De lo contrario, se podría producir un error en la consulta. Para solucionar errores, asegúrese de que cada columna contenga valores del mismo tipo de datos y de que los valores estén dentro de los rangos permitidos.

Si sigue recibiendo errores, cambie el tipo de datos de la columna a un tipo de datos compatible que tenga un rango superior. Si el cambio de tipo de datos no resuelve el problema, pruebe las soluciones de los siguientes ejemplos.

Ejemplo 1

• Formato de origen: JSON
• Problema: En el último registro, el valor de la clave de ID es «0.54». Este valor de clave es el tipo de datos DECIMAL. Para los demás registros, el valor de la clave de ID se establece en INT.

Datos de origen:

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

Instrucción de lenguaje de definición de datos (DDL):

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

Instrucción de lenguaje de manipulación de datos (DML):

SELECT * FROM jsontest_error_hive_bad_data

Error:

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

Para resolver este problema, vuelva a definir la columna ID como STRING. El tipo de datos STRING puede representar correctamente todos los valores de este conjunto de datos.

Instrucción de DDL modificada:

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

Instrucción de DML:

SELECT * FROM jsontest_error_hive_bad_data_correct_id_data_type

También puede realizar la conversión al tipo de datos deseado. Por ejemplo, puede convertir una cadena a un entero. Sin embargo, para algunos tipos de datos, esto podría arrojar resultados nulos o inexactos desde y hacia los que se realiza la conversión. Los valores que no se pueden convertir se descartan. Por ejemplo, si convierte el valor de cadena «0.54» a ** INT**, se devuelven resultados nulos:

SELECT TRY_CAST(id AS INTEGER) FROM jsontest_error_hive_bad_data_correct_id_data_type

Resultado del ejemplo:

Results     _col0
1    50
2    51
3    53
4

La salida muestra que se ha descartado el valor «0.54». No se puede convertir ese valor directamente de una cadena a un entero. Para resolver este problema, use la función COALESCE para convertir los valores de tipo mixto en la misma columna que la salida. A continuación, permita que la función de agregación se ejecute en la columna. Ejemplo:

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

Salida:

Results     _col0
1    50.00
2    51.00
3    53.00
4    0.54

Ejecutar funciones agregadas:

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

Salida:

  _col01    154.54

Ejemplo 2

• Formato de origen: JSON
• Problema: La columna ID se define como INT. Athena no pudo analizar «49612833315» porque el rango de valores INT en Presto es de -2147483648 a 2147483647.

Datos de origen:

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

Instrucción de 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/';

Instrucción de DML:

SELECT * FROM jsontest_error_hive_bad_data_sample_2

Error:

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

Para resolver este problema, defina la columna id como BIGINT, que puede leer el valor «49612833315». Para obtener más información, consulte Tipos de enteros.

Instrucción de DDL modificada:

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

Ejemplo 3

• Formato de origen: JSON
• Problema: Los datos de entrada son DECIMAL, y la columna está definida como DECIMAL en la definición de la tabla. Sin embargo, la escala se define como 2, y 2 no coincide con el valor «0.000054». Para obtener más información, consulte Tipo DECIMAL o NUMERIC.

Datos de origen:

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

Instrucción de 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/';

Instrucción de DML:

SELECT * FROM jsontest_error_hive_bad_data_sample_3

Error:

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

Para resolver este problema, vuelva a definir la columna con una escala que capture todos los valores de entrada. Por ejemplo, en lugar de (10,2), use (10,7).

Instrucción de DDL modificada:

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

Ejemplo 4

• Formato de origen: JSON
• Problema: Los datos de entrada son STRING y la columna se define como INT en la definición de la tabla. Sin embargo, Athena no puede analizar el registro STRING como INT definido por la clave id.

Datos de origen:

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

Instrucción de 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/';

Instrucción de DML:

SELECT * FROM jsontest_error_hive_bad_data_sample_4

Error:

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

Este error se produce porque los datos de origen reales tienen una definición de tipo de datos incompatible.

Para resolver este problema, vuelva a definir la columna id como STRING. El tipo de datos STRING puede representar correctamente todos los valores de este conjunto de datos. Ejemplo:

Instrucción de DDL modificada:

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

Información relacionada

Errores relacionados con JSON

Solución de problemas en Athena