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?

Breve descrição

Há várias versões do erro HIVE_BAD_DATA. A mensagem de erro pode especificar uma string de entrada nula ou vazia, como "For input string: """. Para esse tipo de mensagem de erro, consulte Por que minha consulta do Amazon Athena falha com o erro "HIVE_BAD_DATA: Erro ao analisar o valor do campo X: Para string de entrada: "12312845691""?

Os erros que especificam uma string de entrada com um valor ocorrem sob uma das seguintes condições:

• 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

• Formato de origem: JSON
• Problema: no último registro, o valor da chave id é "0,54". Esse valor da chave é o tipo de dados DECIMAL. Para os outros registros, o valor da chave id está 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:

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

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

Declaração DML:

SELECT * FROM jsontest_error_hive_bad_data_correct_id_data_type

Também é possível converter para o tipo de dados desejado. 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 podem ser convertidos 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 a função 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

• Formato de origem: JSON
• Problema: A coluna id é definida como INT. O Athena não conseguiu 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:

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 esse problema, defina a coluna id como BIGINT, que 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

• Formato de origem: JSON
• Problema: 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 2 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:

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

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

Exemplo 4

• Formato de origem: JSON
• Problema: Os dados de entrada são STRING e a coluna está definida como INT na definição da tabela. No entanto, o Athena não consegue analisar o registro STRING como a chave id definida como INT.

Dados de origem

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

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

Declaração DML:

SELECT * FROM jsontest_error_hive_bad_data_sample_4

Erro:

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

Esse erro ocorre porque os dados de origem reais têm uma definição de tipo de dados incompatível.

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:

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

Informações relacionadas

Erros relacionados ao JSON

Solucionar problemas no Athena