JSON データをクエリする - Amazon Athena

JSON データをクエリする

Amazon Athena では、JSON でエンコードされたデータのクエリ、ネストされた JSON からのデータの抽出、値の検索、および JSON 配列の長さとサイズの確認を行うことができます。Athena で JSON データをクエリする際の基本については、次のサンプル planet データを検討してください。

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

各レコード (基本的に、テーブル内の各行) がそれぞれの行にどのように配置されているかに注目してください。この JSON データをクエリするには、次のような CREATE TABLE ステートメントを使用します。

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/'

データをクエリするには、次のようなシンプルな SELECT ステートメントを使用します。

SELECT * FROM planets_json

クエリの結果は以下のようになります。

# name distancefromsun orbitalperiod daylength
1 Mercury 0.39 0.24 58.65
2 Venus 0.72 0.62 243.02
3 Earth 1.0 1.0 1.0
4 Mars 1.52 1.88 1.03

CREATE TABLE ステートメントが OpenX JSON SerDe をどのように使用しているかに注目してください。これにより、各 JSON レコードを別の行に配置する必要があります。JSON がプリティプリント形式の場合、またはすべてのレコードが 1 行にある場合、データは正しく読み取られません。

プリティプリント形式の JSON データをクエリするには、OpenX JSON SerDe の代わりに Amazon Ion Hive SerDe を使用できます。プリティプリント形式で保存された以前のデータについて考えてみましょう。

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

このデータをクエリするには、次のような CREATE TABLE ステートメントを使用します。OpenX JSON SerDe を指定する代わりに、このステートメントが 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/'

クエリ SELECT * FROM planets_ion は以前と同じ結果を生成します。Amazon Ion Hive SerDe を使用してこの方法でテーブルを作成する方法の詳細については、「Amazon Ion テーブルを作成する」を参照してください。

前述の JSON データの例には、ネストされた配列や構造体などの複雑なデータ型は含まれていません。ネストされた JSON データのクエリについては、「例: ネスト JSON の逆シリアル化」を参照してください。

トピック