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

Resolução

Verifique os seguintes problemas comuns:

Usar o SerDe JSON correto

O Athena usa um dos seguintes JSON SerDes para processar JSON:

• O Hive JSON SerDe nativo
• O OpenX JSON SerDe
• O Amazon Ion Hive SerDe

Se você precisar executar a consulta INSERT INTO na tabela criada, use o Hive JSON SerDe.

Se você usar o OpenX JSON SerDe, poderá ignorar registros malformados para identificar os arquivos que causam os erros. No exemplo a seguir, quando ignore.malformed.json está definido como true, os 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 a fm de identificar os arquivos com registros malformados, semelhante ao seguinte:

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

Observação: se você executar consultas do Athena para arquivos JSON com impressão bonita, poderá usar o Amazon Ion Hive SerDe. O Amazon Ion Hive SerDe não espera que cada linha de dados esteja em uma única linha. Use esse recurso para consultar conjuntos de dados JSON que estão em um formato pretty-print ou dividir os campos em uma linha com caracteres de nova linha.

Usar uma linha por registro

Os seguintes registros JSON estão formatados corretamente e podem ser lidos por todos os três JSON SerDe:

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

Os seguintes registros JSON só podem ser lidos pelo Amazon Ion Hive SerDe:

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

Os dados JSON a seguir têm vários registros em uma única linha e 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, o arquivo deve terminar no formato “.gz”. Por exemplo, “myfile.json.gz” ou “myfile.gz” são extensões formatadas corretamente.

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 por maiúsculas e minúsculas, por exemplo, “Coluna” e “coluna”, o Athena gerará o erro a seguir:

“HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key”

Além disso, seus dados não estão visíveis no Athena. Para evitar esse erro, gere seus dados com colunas que não diferenciam maiúsculas de minúsculas.

Se você usar o SerDe OpenX, então use 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/';

Se o arquivo JSON tiver nomes de coluna duplicados que diferenciam maiúsculas de minúsculas, atualize o arquivo para remover as colunas duplicadas:

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

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 que não são vá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/';

2. 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 que não são vá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

Erros relacionados ao JSON