Consulta de datos JSON

Amazon Athena permite consultar datos codificados en JSON, extraer datos de JSON anidados, buscar valores y encontrar la longitud y el tamaño de matrices JSON. Para aprender los fundamentos de la consulta de datos JSON en Athena, considere el siguiente ejemplo de datos planetarios:

{name:"Mercury",distanceFromSun:0.39,orbitalPeriod:0.24,dayLength:58.65}
{name:"Venus",distanceFromSun:0.72,orbitalPeriod:0.62,dayLength:243.02}
{name:"Earth",distanceFromSun:1.00,orbitalPeriod:1.00,dayLength:1.00}
{name:"Mars",distanceFromSun:1.52,orbitalPeriod:1.88,dayLength:1.03}

Note cómo cada registro (esencialmente, cada fila de la tabla) está en una línea separada. Para consultar estos datos JSON, puede utilizar una instrucción CREATE TABLE como la siguiente:

CREATE EXTERNAL TABLE `planets_json`(
  `name` string,
  `distancefromsun` double,
  `orbitalperiod` double,
  `daylength` double)
ROW FORMAT SERDE
  'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION
  's3://amzn-s3-demo-bucket/json/'

Para consultar los datos, utilice una instrucción SELECT sencilla como la del ejemplo que aparece a continuación.

SELECT * FROM planets_json

Así se ven los resultados de la consulta.

Observe cómo la instrucción CREATE TABLE utiliza El SerDe JSON de OpenX, que requiere que cada registro JSON esté en una línea separada. Si el JSON tiene un formato de impresión bonita, o si todos los registros están en una sola línea, los datos no se leerán correctamente.

Para consultar datos JSON que están en formato de impresión bonita, puede utilizar SerDe de Amazon Ion Hive en lugar de OpenX JSON SerDe. Considere los datos anteriores almacenados en formato de impresión bonita:

{
  name:"Mercury",
  distanceFromSun:0.39,
  orbitalPeriod:0.24,
  dayLength:58.65
}
{
  name:"Venus",
  distanceFromSun:0.72,
  orbitalPeriod:0.62,
  dayLength:243.02
}
{
  name:"Earth",
  distanceFromSun:1.00,
  orbitalPeriod:1.00,
  dayLength:1.00
}
{
  name:"Mars",
  distanceFromSun:1.52,
  orbitalPeriod:1.88,
  dayLength:1.03
}

Para consultar estos datos sin volver a darles formato, puede utilizar una instrucción CREATE TABLE como la siguiente. Observe que, en lugar de especificar OpenX JSON SerDe, la instrucción especifica STORED AS ION.

CREATE EXTERNAL TABLE `planets_ion`(
  `name` string,
  `distancefromsun` DECIMAL(10, 2),
  `orbitalperiod` DECIMAL(10, 2),
  `daylength` DECIMAL(10, 2))
STORED AS ION
LOCATION
  's3://amzn-s3-demo-bucket/json-ion/'

La consulta SELECT * FROM planets_ion produce los mismos resultados que antes. Para obtener más información sobre cómo crear tablas de esta manera con Amazon Ion Hive SerDe, consulte Creación de tablas de Amazon Ion.

Los datos JSON del ejemplo anterior no contienen tipos de datos complejos, como matrices o estructuras anidadas. Para obtener más información sobre la consulta de datos JSON anidados, consulte Ejemplo: deserialización de JSON anidado.