¿Por qué aparecen errores cuando intento leer datos JSON en Amazon Athena?

Resolución

Compruebe los siguientes problemas comunes:

Utilice el JSON SerDe correcto

Athena procesa los datos JSON mediante uno de los dos JSON SerDe:

• El Apache Hive/HCatalog JsonSerDe nativo (org.apache.hive.hcatalog.data.JsonSerDe)
• El SerDe de OpenX (org.openx.data.jsonserde.JsonSerDe)

Si no está seguro de qué SerDe utilizó, pruebe las dos versiones de SerDe. Si utiliza el SerDe de OpenX, puede ignorar los registros con formato incorrecto para identificar las líneas que causan los errores, como se muestra en el siguiente ejemplo. Cuando ignore.malformed.json se establece en true, los registros con formato incorrecto devuelven 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 la nueva tabla para identificar los archivos con registros con formato incorrecto. Por ejemplo:

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

Utilice una línea por registro

Los siguientes registros JSON tienen el formato correcto:

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

Los siguientes registros JSON tienen un formato incorrecto:

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

Estos registros también tienen un formato incorrecto:

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

Utilice el tipo de datos correcto en cada columna

La segunda línea del siguiente ejemplo contiene un tipo de datos incorrecto para «edad». El valor de la columna debe ser «11» en lugar de «once». Esto provoca el siguiente mensaje de error: HIVE_BAD_DATA: Error al analizar el valor del campo «once» para el campo 1: Para la cadena de entrada: «once».

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

Use la extensión correcta para los archivos JSON comprimidos

Cuando utilice un archivo JSON comprimido, el archivo debe terminar en «.json» seguido de la extensión del formato de compresión, como «.gz». Por ejemplo, esta es una extensión con el formato correcto para un archivo gzip: «myfile.json.gz».

Utilice columnas que no distingan mayúsculas de minúsculas o establezca la propiedad de no distinción entre mayúsculas y minúsculas en «false»

Athena no distingue mayúsculas de minúsculas por defecto. Si los nombres de las columnas solo difieren entre mayúsculas y minúsculas (por ejemplo, «Columna» y «columna»), Athena genera un error («HIVE_CURSOR_ERROR: La fila no es un objeto JSON válido - JSONException: Clave duplicada») y sus datos no serán visibles en Athena. La forma más sencilla de evitar este problema es generar los datos con columnas que no distingan mayúsculas de minúsculas.

Si usa OpenX SerDe, puede usar nombres de clave que distingan mayúsculas de minúsculas. Para ello, defina la propiedad SerDe case.insensitive en false y añada un mapeo para la tecla mayúscula. Por ejemplo, para usar columnas en mayúsculas y minúsculas como esta:

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

Utilice estas propiedades 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/';

Asegúrese de que todas las filas de la tabla JSON SerDe estén en formato JSON

Para averiguar si hay filas JSON o nombres de archivo no válidos en la tabla de Athena, haga lo siguiente:

1.    Cree una tabla con un delimitador que no esté presente en los archivos de entrada. Ejecute un comando similar al siguiente:

CREATE EXTERNAL TABLE IF NOT EXISTS json_validator (jsonrow string) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '%'
location 's3://awsexamplebucket/';

2.    Ejecute una consulta similar a la siguiente para devolver el nombre del archivo, los detalles de la fila y la ruta de Amazon S3 de las filas JSON no válidas.

WITH testdataset AS (SELECT "$path" s3path,jsonrow,try(json_parse(jsonrow)) isjson FROM json_validator)
SELECT * FROM testdataset WHERE ISJSON IS NULL;

Información relacionada

Prácticas recomendadas para leer datos JSON

Solución de problemas de errores relacionados con JSON