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

4 minutos de lectura
0

Cuando intento leer datos JSON en Amazon Athena, se muestran errores por datos incorrectos o NULL.

Solución

Compruebe los siguientes problemas habituales:

Utilización del SerDe para datos JSON correcto

Athena procesa los datos JSON mediante uno de los dos SerDe disponibles para ello:

Si no sabe a ciencia cierta cuál es el SerDe que ha utilizado, pruebe ambas 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. Si ignore.malformed.json se ha definido como true, los registros con formato incorrecto se 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

Utilización de 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" }

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

Si utiliza un archivo JSON comprimido, 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».

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 solo difieren en las mayúsculas y las minúsculas (por ejemplo, «Columna» y «columna»), Athena generará un error («HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key») y los datos no se mostrarán en Athena. La forma más sencilla de evitar este problema es generar 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/';

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

Best practices for reading JSON data

Troubleshooting JSON-related errors

OFICIAL DE AWS
OFICIAL DE AWSActualizada hace 2 años