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

Solución

Compruebe los siguientes problemas habituales:

Utilización del SerDe para datos JSON correcto

Athena usa uno de los siguientes SerDe de JSON para procesar los datos JSON:

• El SerDe JSON de Hive nativo
• El SerDe JSON de OpenX
• El SerDe de Amazon Ion Hive

Si necesita ejecutar la consulta INSERT INTO en la tabla creada, utilice el SerDe JSON de Hive.

Si utiliza el SerDe JSON de OpenX, puede ignorar los registros con formato incorrecto para identificar los archivos que causan los errores. En el siguiente ejemplo, cuando ignore.malformed.json se establece en true, los registros con formato incorrecto se muestran 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/';

Realice una consulta en la nueva tabla para identificar los archivos con registros con formato incorrecto que sea similar a la siguiente:

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

Nota: Si ejecuta consultas de Athena para archivos JSON de tipo «pretty-print», puede utilizar el SerDe de Amazon Ion Hive. El SerDe de Amazon Ion Hive no espera que cada fila de datos esté en una sola línea. Utilice esta característica para consultar conjuntos de datos JSON que estén en formato «pretty-print» o para dividir los campos en una fila con caracteres de nueva línea.

Utilización de una línea por registro

Los siguientes registros JSON tienen el formato correcto y los tres SerDe de JSON pueden leerlos:

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

Los siguientes registros JSON solo los puede leer el SerDe de Amazon Ion Hive:

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

Los siguientes datos JSON tienen varios registros en una sola línea y su formato es incorrecto:

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

Utilización del tipo de datos correcto en cada columna

La segunda línea del siguiente ejemplo contiene un tipo de datos incorrecto para «age». El valor de la columna debería ser «11» en lugar de «eleven». Esto provoca el siguiente mensaje de error: HIVE_BAD_DATA: Error parsing field value 'eleven' for field 1: For input string: "eleven".

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

Utilización de la extensión correcta para los archivos JSON comprimidos

Cuando se utiliza un archivo JSON comprimido, el archivo debe terminar en el formato «.gz». Por ejemplo, "myfile.json.gz" o "myfile.gz" son extensiones con el formato correcto.

Utilización de columnas que no distingan entre mayúsculas y minúsculas o definición de la propiedad case.insensitive como false

De forma predeterminada, Athena no distingue entre mayúsculas y minúsculas. Si los nombres de las columnas difieren en las mayúsculas y minúsculas, por ejemplo, «Columna» y «columna», Athena generará el siguiente error:

«HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key»

Además, sus datos no están visibles en Athena. Para evitar este error, genere los datos con columnas que no distingan entre mayúsculas y minúsculas.

Si usa el SerDe de OpenX, puede utilizar nombres de clave que distingan entre mayúsculas y minúsculas. Para ello, defina la propiedad de SerDe case.insensitive como false y añada una asignación para la tecla de las mayúsculas. 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/';

Si el archivo JSON tiene nombres de columna duplicados que distinguen entre mayúsculas y minúsculas, actualice el archivo para eliminar las columnas duplicadas:

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

Comprobación de que todas las filas de la tabla de SerDe para datos JSON estén en formato JSON

Para averiguar si en la tabla de Athena hay nombres de archivo o filas de datos JSON sin validez, siga estos pasos:

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 de datos JSON sin validez:

   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 la lectura de datos JSON

Errores relacionados con JSON