Amazon Athena Memo

HIVE形式

下記のように key=value形式でパス（S3のキー）を表現する記載方法のこと。

year=YYYY/month=MM/day=DD/

CREATE EXTERNAL TABLE IF NOT EXISTS your_table (
  json_data string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://your-s3-location/';

SELECT 
  json_extract_scalar(json_data, '$.data.categories.Action') AS Action,
  json_extract_scalar(json_data, '$.data.categories.Condition.Key') AS ConditionKey,
  json_extract_scalar(json_data, '$.data.categories.Version') AS Version
FROM your_table;