Por que minha consulta do Athena falha com o erro "HIVE_BAD_DATA: Error parsing field value for field X: For input string: "12312845691""?

Breve descrição

Há várias versões do erro HIVE_BAD_DATA. Se a mensagem de erro especificar uma string de entrada nula ou vazia (por exemplo, "For input string: """), consulte Por que recebo o erro "HIVE_BAD_DATA: Error parsing field value for field X for input string:" quando eu consulto dados JSON no Amazon Athena?

Os erros que especificam uma string de entrada com um valor ocorrem em um dos seguintes cenários:

• O tipo de dado definido na definição da tabela não corresponde aos dados de origem reais.
• Um único campo contém diferentes tipos de dados, como um valor inteiro para um registro e um valor decimal para outro registro.

Resolução

É uma prática recomendada usar somente um tipo de dados em uma coluna. Caso contrário, a consulta pode falhar. Para resolver erros, certifique-se de que cada coluna contenha valores do mesmo tipo de dados e que os valores estejam nos intervalos permitidos.

Se ainda receber erros, altere o tipo de dados da coluna para um tipo de dados compatível com um intervalo maior. Se a alteração no tipo de dados não resolver o problema, tente as soluções nos exemplos a seguir.

Exemplo 1

No exemplo a seguir, o formato de origem é JSON. O problema está no último registro porque o valor da chave id é o tipo de dados DECIMAL (0,54). No entanto, em outros registros, o valor da chave id é definido como INT.

Dados de origem:

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

Declaração de Linguagem de definição de dados (Data Definition Language, 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/';

Declaração de Linguagem de manipulação de dados (Data Manipulation Language, DML):

SELECT * FROM jsontest_error_hive_bad_data

Erro:

"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 esse problema, redefina a coluna id como string. O tipo de dado STRING pode representar corretamente todos os valores nesse conjunto de dados.

Exemplo:

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

Declaração DML:

SELECT * FROM jsontest_error_hive_bad_data_correct_id_data_type

Também é possível converter para seu tipo de dados preferido. Por exemplo, é possível converter uma string como um número inteiro. No entanto, para alguns tipos de dados, isso pode retornar resultados nulos ou imprecisos ao converter de e para esses tipos. Valores que não é possível converter são descartados. Por exemplo, se você converter o valor da string 0.54 em INT, resultados nulos são retornados:

SELECT TRY_CAST(id AS INTEGER) FROM jsontest_error_hive_bad_data_correct_id_data_type

Exemplo de saída:

Results     _col0
1    50
2    51
3    53
4

A saída mostra que o valor 0,54 foi descartado. Não é possível converter esse valor diretamente de uma string para um número inteiro. Para resolver esse problema, use COALESCE para converter os valores de tipo misto na mesma coluna da saída. Em seguida, permita que a função agregada seja executada na coluna.

Exemplo:

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

Saída:

Results     _col0
1    50.00
2    51.00
3    53.00
4    0.54

Execute funções 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

Saída:

_col01    154.54

Exemplo 2

No exemplo a seguir, o formato de origem é JSON. A coluna id está definida como INT. O Athena não consegue analisar "49612833315" porque o intervalo dos valores INT no Presto é de -2147483648 a 2147483647.

Dados de origem:

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

Declaração 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/';

Declaração DML:

SELECT * FROM jsontest_error_hive_bad_data_sample_2

Erro:

"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 esse problema, defina a coluna id como bigint porque o tipo de dados BIGINT pode ler o valor "49612833315". Para obter mais informações, consulte Tipos de inteiros.

Declaração 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/';

Exemplo 3

No exemplo a seguir, o formato de origem é JSON. Os dados de entrada são DECIMAL, e a coluna está definida como decimal na definição da tabela. No entanto, a escala está definida como 2 e não corresponde ao valor "0,000054". Para obter mais informações, consulte Tipo DECIMAL ou NUMERIC.

Dados de origem:

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

Declaração 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/';

Declaração DML:

SELECT * FROM jsontest_error_hive_bad_data_sample_3

Erro:

"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 esse problema, redefina a coluna com uma escala que capture todos os valores de entrada. Por exemplo, em vez 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/';

Exemplo 4

No exemplo a seguir, o formato de origem é CSV. No esquema, attribute2 contém STRING e o formato é date. O esquema da tabela o define como o tipo DATE.

Dados de origem:

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

Declaração 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");

Erro:

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

Para resolver esse problema, altere o tipo attribute2 de DATE para STRING.

Dados de origem modificados:

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

Declaração 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");

Informações relacionadas

Tipos de dados no Amazon Athena

Solucionar problemas no Athena