Por que recebo erros quando tento ler dados JSON no Amazon Athena?

4 minuto de leitura
0

Quando tento ler dados JSON no Amazon Athena, recebo erros de dados NULL ou incorretos.

Resolução

Verifique os seguintes problemas comuns:

Use o SerDe JSON correto

O Athena processa dados JSON usando um dos dois SerDes JSON:

Se você não tiver certeza de qual SerDe usou, experimente as duas versões de SerDe. Se você usar o SerDe OpenX, poderá ignorar logs malformados para identificar as linhas que estão causando os erros, conforme mostrado no exemplo a seguir. Quando ignore.malformed.json está definido como true, logs malformados retornam como NULL.

CREATE EXTERNAL TABLE json (
    id int,
    name string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/';

Consulte a nova tabela para identificar os arquivos com logs malformados. Por exemplo:

SELECT "$PATH", * FROM your_table where your_column is NULL

Use uma linha por registro

Os seguintes logs JSON estão formatados corretamente:

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

Os seguintes logs JSON estão formatados incorretamente:

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

Estes logs também estão formatados incorretamente:

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

Usar o tipo de dados correto em cada coluna

A segunda linha no exemplo a seguir contém um tipo de dados incorreto para “age”. O valor da coluna deve ser “11” em vez de “eleven”. Isso causa a seguinte mensagem de erro: HIVE_BAD_DATA: Erro ao analisar o valor do campo ‘eleven’ para o campo 1: Para string de entrada: “eleven”.

{"name":"Patrick","age":35,"address":"North Street"}
{"name":"Carlos","age":"eleven","address":"Flowers Street"}
{"name":"Fabiana","age":22,"address":"Main Street"}

Usar a extensão correta para arquivos JSON compactados

Quando você usa um arquivo JSON compactado, ele deve terminar em “.json” seguido pela extensão do formato de compactação, como “.gz”. Por exemplo, esta é uma extensão formatada corretamente para um arquivo gzip: “myfile.json.gz”.

Usar colunas que não diferenciam maiúsculas de minúsculas ou definir a propriedade case.insensitive como false

Por padrão, o Athena não diferencia maiúsculas de minúsculas. Se você tiver nomes de coluna que diferem somente por maiúsculas e minúsculas (por exemplo, “Coluna” e “coluna”), o Athena gerará um erro (“HIVE_CURSOR_ERROR: A linha não é um objeto JSON válido - JSONException: Chave duplicada”) e seus dados não estão visíveis no Athena. A maneira mais fácil de evitar esse problema é gerar seus dados com colunas que não diferenciam maiúsculas de minúsculas.

Se você usar o SerDe OpenX, poderá usar nomes de chave com distinção entre maiúsculas e minúsculas. Para fazer isso, defina a propriedade de SerDe case.insensitive como false e adicione mapeamento para a chave maiúscula. Por exemplo, para usar colunas maiúsculas e minúsculas como esta:

{"Username": "bob1234", "username": "bob" }

Use estas propriedades de SerDe:

CREATE external TABLE casesensitive_json (user_name String,username String)
ROW FORMAT serde 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'mapping.user_name' = 'Username','case.insensitive'='false')
LOCATION 's3://awsexamplebucket/';

Certificar-se de que todas as linhas na tabela JSON SerDE estejam no formato JSON

Para descobrir se há linhas JSON ou nomes de arquivo inválidos na tabela Athena, faça o seguinte:

  1. Crie uma tabela com um delimitador que não esteja presente nos arquivos de entrada. Execute um comando semelhante ao seguinte:
CREATE EXTERNAL TABLE IF NOT EXISTS json_validator (jsonrow string) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '%'
location 's3://awsexamplebucket/';
  1. Execute uma consulta semelhante à seguinte para retornar o nome do arquivo, os detalhes da linha e o caminho do Amazon S3 para as linhas JSON inválidas.
WITH testdataset AS (SELECT "$path" s3path,jsonrow,try(json_parse(jsonrow)) isjson FROM json_validator)
SELECT * FROM testdataset WHERE ISJSON IS NULL;

Informações relacionadas

Práticas recomendadas de leitura de dados JSON

Solução de problemas relacionados a JSON

AWS OFICIAL
AWS OFICIALAtualizada há 2 anos