¿Por qué aparece el error «HIVE_BAD_DATA: error al analizar el valor de campo '' para el campo X para la cadena de entrada ""» cuando consulto datos 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:

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.

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:

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, en función de los tipos de datos desde los que realice la conversión y hacia los que realice la conversión, esto podría arrojar resultados nulos o inexactos. Los valores que no se pueden convertir se descartan. Por ejemplo, al convertir el valor de cadena «0.54» a INT se obtienen resultados nulos:

SELECT TRY_CAST(id AS INTEGER)
FROM jsontest_error_hive_bad_data_correct_id_data_type

Ejemplo de salida:

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 (del sitio web de Presto) 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:

     _col0
1    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:

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.

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:

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.

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

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