Parquet

検証結果

• Athena

準備手順

$ aws s3 mb s3://amazon-reviews-pds-az
$ aws s3 cp --recursive s3://amazon-reviews-pds/ s3://amazon-reviews-pds-az

CREATE EXTERNAL TABLE amazon_reviews_parquet(
  marketplace string, 
  customer_id string, 
  review_id string, 
  product_id string, 
  product_parent string, 
  product_title string, 
  star_rating int, 
  helpful_votes int, 
  total_votes int, 
  vine string, 
  verified_purchase string, 
  review_headline string, 
  review_body string, 
  review_date bigint, 
  year int)
PARTITIONED BY (product_category string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://amazon-reviews-pds-az/parquet/'

MSCK REPAIR TABLE  amazon_reviews_parquet

情報採取

• select count(*) from amazon_reviews_parquet

select eventname, count(eventname) as apicall_count from default.cloudtrail_logs_cloudtrail_123456789012_do_not_delete 
where eventsource = 's3.amazonaws.com' 
and useragent = 'athena.amazonaws.com' 
and awsregion = 'us-east-1'
and eventtime > '2018-09-23T07:12:00Z'
and eventtime < '2018-09-23T07:35:00Z'
group by eventname
order by apicall_count desc

• select count(year) from amazon_reviews_parquet

select eventname, count(eventname) as apicall_count from default.cloudtrail_logs_cloudtrail_123456789012_do_not_delete 
where eventsource = 's3.amazonaws.com' 
and useragent = 'athena.amazonaws.com' 
and awsregion = 'us-east-1'
and eventtime > '2018-09-23T07:35:00Z'
and eventtime < '2018-09-23T07:55:00Z'
group by eventname
order by apicall_count desc

• select count(review_body) from amazon_reviews_parquet

select eventname, count(eventname) as apicall_count from default.cloudtrail_logs_cloudtrail_123456789012_do_not_delete 
where eventsource = 's3.amazonaws.com' 
and useragent = 'athena.amazonaws.com' 
and awsregion = 'us-east-1'
and eventtime > '2018-09-23T07:50:00Z'
and eventtime < '2018-09-23T08:00:00Z'
group by eventname
order by apicall_count desc

• select * from amazon_reviews_parquet limit 10000

select eventname, count(eventname) as apicall_count from default.cloudtrail_logs_cloudtrail_123456789012_do_not_delete 
where eventsource = 's3.amazonaws.com' 
and useragent = 'athena.amazonaws.com' 
and awsregion = 'us-east-1'
and eventtime > '2018-09-23T08:10:00Z'
and eventtime < '2018-09-23T08:20:00Z'
group by eventname
order by apicall_count desc

• select year from amazon_reviews_parquet limit 10000

select eventname, count(eventname) as apicall_count from default.cloudtrail_logs_cloudtrail_123456789012_do_not_delete 
where eventsource = 's3.amazonaws.com' 
and useragent = 'athena.amazonaws.com' 
and awsregion = 'us-east-1'
and eventtime > '2018-09-23T08:20:00Z'
and eventtime < '2018-09-23T08:30:00Z'
group by eventname
order by apicall_count desc

• select review_body from amazon_reviews_parquet limit 10000

select eventname, count(eventname) as apicall_count from default.cloudtrail_logs_cloudtrail_123456789012_do_not_delete 
where eventsource = 's3.amazonaws.com' 
and useragent = 'athena.amazonaws.com' 
and awsregion = 'us-east-1'
and eventtime > '2018-09-23T08:40:00Z'
and eventtime < '2018-09-23T08:59:00Z'
group by eventname
order by apicall_count desc

検証パターン

• Athena/PySpark
• S3/HDFS/ファイルシステム
• クエリ

参考

• https://registry.opendata.aws/amazon-reviews/
• https://s3.amazonaws.com/amazon-reviews-pds/readme.html