¿Por qué mi consulta de Athena devuelve el error «HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691"»?

Descripción corta

Hay varias versiones del error HIVE_BAD_DATA. Si el mensaje de error especifica una cadena de entrada nula o vacía (por ejemplo, «For input string: ""»), consulta ¿Por qué aparece el error «HIVE_BAD_DATA: Error parsing field value for field X for input string:» cuando consulto datos de JSON en Amazon Athena?

Los errores que especifican una cadena de entrada con un valor se producen en uno de los siguientes escenarios:

• 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úrate de que cada columna contenga valores del mismo tipo de datos y de que los valores estén dentro de los rangos permitidos.

Si sigues recibiendo errores, cambia 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, prueba las soluciones de los siguientes ejemplos. 

Ejemplo 1

En el siguiente ejemplo, el formato de origen es JSON. El problema está en el último registro porque el valor de la clave id es del tipo de datos DECIMAL (0,54). Sin embargo, para el resto de registros, el valor de clave 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, vuelve a definir la columna ID como STRING. El tipo de datos CADENA 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 puedes realizar la conversión al tipo de datos que prefieras. Por ejemplo, puedes 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 puedes emitir se descartan. Por ejemplo, si conviertes 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 de ejemplo:

Results     _col0
1    50
2    51
3    53
4

El resultado muestra que se descartó el valor 0,54. No se puede convertir ese valor directamente de una cadena a un entero. Para resolver este problema, usa la función COALESCE para CONVERTIR los valores de tipo mixto en la misma columna que la salida. A continuación, permite 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

En el siguiente ejemplo, el formato de origen es JSON. 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, define la columna id como bigint, ya que el tipo de datos BIGINT puede leer el valor «49612833315». Para obtener más información, consulta 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

En el siguiente ejemplo, el formato de origen es JSON. 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 no coincide con el valor «0,000054». Para obtener más información, consulta 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, vuelve a definir la columna con una escala que capture todos los valores de entrada. Por ejemplo, en lugar de (10,2), usa (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/';

Ejemplo 4

En el siguiente ejemplo, el formato de origen es CSV. En el esquema, attribute2 contiene STRING y el formato es date. El esquema de la tabla lo define como el tipo DATE.

Datos de origen:

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

Instrucción de 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».

Para resolver este problema, cambia el tipo attribute2 de DATE a STRING.

Datos de origen modificados:

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

Instrucción de DDL modificada:

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

Información relacionada

Tipos de datos en Amazon Athena

Solución de problemas en Athena