Por que recebo o erro "HIVE_BAD_DATA: Erro ao analisar o valor do campo X para a string de entrada:” 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 "Para string de entrada: """. 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 poderá 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 da 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 (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 da linguagem de manipulação de dados (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

Você também pode lançar para o tipo de dados desejado. Por exemplo, você pode lançar uma string como um número inteiro. No entanto, para alguns tipos de dados, isso pode retornar resultados nulos ou imprecisos que você converte de e para. Valores que não podem ser lançados são descartados. Por exemplo, se você converter o valor da string “0.54" em INT, os resultados nulos serã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. Você não pode lançar esse valor diretamente de uma string para um número inteiro. Para resolver esse problema, use a função COALESCE para lançar 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 é definida como DECIMAL na definição da tabela. No entanto, a escala é definida como 2 e 2 não corresponde ao valor "0,000054". Para obter mais informações, consulte Tipo DECIMAL ou NUMÉRICO.

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 é definida como INT na definição da tabela. No entanto, o Athena não pode interpretar o registro STRING como a chave de 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

Solução de problemas no Athena