Kinesis FirehoseでS3にアップロードしたファイルをAthenaで検索する

前回の投稿ではLambdaからエンキューされたメッセージをKinesis FirehoseでS3までアップロードしました。
今回はAthenaを使ってこのアップロードしたファイルをSQLで検索できるようにします。

Athena

S3バケットのファイルからSQLライクな構文で検索できるサーバレスなサービスで、スキャンされたデータ量に対してのみ課金されます。 現時点(2017/4/7)では米国リージョンのみの提供となっています。

Amazon Athena (サーバーレスのインタラクティブなクエリサービス) | AWS

今回は前回作成したバケットに対してAthenaで検索できるようにします。
また、あわせてパーティションも設定・作成します。 デフォルトだとバケット内の全ファイルをスキャンしますが、パーティションを使うことでスキャンするディレクトリを限定することができます。

DBとテーブルの作成

まずはAthenaでDBとテーブルを作成します。

Category Manager→Add tableでtestdbとtest_tableを作成します。

  • 前回作成したs3://delivery-s3/を選択します

f:id:ohke:20170406213621p:plain

データ形式JSONを指定します。

f:id:ohke:20170406213804p:plain

名前と型を指定して、3つのカラムを作成します。この時、JSONのキー名と同じカラム名にすることでマッピングします。

f:id:ohke:20170406213850p:plain

最後にパーティションの設定をして、完了です。
Kinesis Firehoseではバケット以下にyyyy/mm/dd/hhのフォルダを作成しますが、今回は日単位でパーティションを作成するため、year、month、dayをパーティションにします。

f:id:ohke:20170406214037p:plain

すると、SQLが実行され、テーブルが作成されます。

CREATE EXTERNAL TABLE IF NOT EXISTS testdb.test_table (
  `StringValue` string,
  `IntValue` int,
  `DoubleValue` double 
) PARTITIONED BY (
  year string,
  month string,
  day string 
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://delivery-s3/'
TBLPROPERTIES ('has_encrypted_data'='false')

パーティションの作成

これでもうSQLを実行できるようになりますが、パーティションを作成しておきます。

2017/03/30でパーティションを作る場合は、以下のようなSQLになります。 同様に2017/04/06も作成しておきます。

ALTER TABLE testdb.test_table ADD PARTITION (year='2017',month='03',day='30') location 's3://delivery-s3/2017/03/30/'

作成したパーティションは、show partitionsで一覧を見れます。

show partitions testdb.test_table;
year=2017/month=03/day=30
year=2017/month=04/day=06

SQLの実行

試しに全件を取得してみます。

  • パーティションで設定したカラム(year、month、day)も表示されていることを確認してください
select * from testdb.test_table;

f:id:ohke:20170406220547p:plain

パーティションを指定して検索する場合、以下のようにwhereでyear、month、dayを指定します。

select * from testdb.test_table where year='2017' and month = '03' and day = '30' and stringvalue = 'text2';

スキャンされたデータ量を見ると、0.19KBから0.13KBへ減っています。 パーティションによってスキャンするファイルが2017/03/30以下のみになったためです。

f:id:ohke:20170406220440p:plain