前回の投稿では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/を選択します
名前と型を指定して、3つのカラムを作成します。この時、JSONのキー名と同じカラム名にすることでマッピングします。
最後にパーティションの設定をして、完了です。
Kinesis Firehoseではバケット以下にyyyy/mm/dd/hhのフォルダを作成しますが、今回は日単位でパーティションを作成するため、year、month、dayをパーティションにします。
すると、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;
パーティションを指定して検索する場合、以下のように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以下のみになったためです。