Consultar dados JSON

O Amazon Athena permite analisar dados codificados em JSON, extrair dados JSON aninhado, pesquisar valores e saber o comprimento e o tamanho dos arrays JSON. Para aprender os fundamentos da consulta de dados JSON no Athena, considere os seguintes exemplos de dados do planeta:

{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}

Observe como cada registro (basicamente, cada linha na tabela) está em uma linha diferente. Para consultar os dados JSON, você pode usar uma declaração CREATE TABLE como a seguinte:

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 os dados, use uma declaração SELECT como a seguinte.

SELECT * FROM planets_json

Os resultados da consulta são semelhantes aos seguintes:

Observe como a declaração CREATE TABLE usa OpenX JSON SerDe, o que exige que cada registro JSON esteja em uma linha diferente. Se o JSON estiver em um formato pretty print ou se todos os registros estiverem em uma única linha, os dados não serão lidos corretamente.

Para consultar dados JSON que estejam em um formato pretty print, você pode usar Amazon Ion Hive SerDe em vez do OpenX JSON SerDe. Considere os dados anteriores armazenados em um formato pretty print:

{
  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 esses dados sem reformatar, você pode usar uma declaração CREATE TABLE como a seguinte. Observe que, em vez de especificar o OpenX JSON SerDe, a instrução 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/'

A consulta SELECT * FROM planets_ion produz os mesmos resultados de antes. Para obter mais informações sobre a criação de tabelas dessa forma usando o Amazon Ion Hive SerDe, consulte Criar tabelas do Amazon Ion.

O exemplo anterior de dados JSON não contém tipos de dados complexos, como arrays ou estruturas aninhadas. Para obter mais informações sobre como consultar dados JSON aninhado, consulte Exemplo: desserializar JSON aninhado.