Amazon AthenaでSQLクエリを実行してみた

はじめに

こんにちは。PE部の谷です。
前回、S3 Selectを使ってSQLクエリを実行しました。
今回は、Amazon AthenaでSQLクエリを実行してみました。

前提知識

Amazon Athena

Amazon Athena は、標準的な SQL を使用して Amazon Simple Storage Service (Amazon S3) 内のデータを直接分析することを容易にするインタラクティブなクエリサービスです。AWS Management Console でいくつかのアクションを実行するだけで、Athena にデータの保存先の Amazon S3 を設定し、標準 SQL を使用してアドホッククエリの実行を開始できます。AWS公式より引用

S3にファイルをアップロードするとそれをデータベースのように扱うことができます。

S3 Selectとの違いは、

• クエリの機能が多い

• 対応ファイルが多い

• 複数のファイルを対象にできる

テーブル

基盤となるソースデータのスキーマを定義するメタデータ定義のコンテナです。 S3 のどこにデータがあるかを Athena に伝え、列名、データ型、およびテーブルの名前などのデータの構造を指定します。 AWS公式より引用

S3にアップロードした分析対象のデータ情報をテーブルに指定します。

データベース

テーブルの名前などのデータの構造を指定します。データベースは、テーブルを論理的なグループにまとめたものであり、データセットのメタデータおよびスキーマ情報のみを保持します。AWS公式より引用

やってみよう

今回使うのは、野菜の栄養素データです。
左から廃棄率(%)、エネルギー(kcal)、水分(g)、タンパク質(g)、脂質(g)、炭水化物(g)、灰分(g)、カリウム(mg)、カルシウム(mg)、マグネシウム(mg)になります。

S3バケットにアップロードしておきます。

事前準備

データベースを作成する

Amazon Athenaコンソールを開き、 [クエリエディタを詳しく確認する]をクリックします。
クエリエディタの[設定]タブから[管理]をクリックし、クエリの結果の場所（クエリ結果が保存されるS3バケット）を選択します。

次に[エディタ]タブに移動してデータベースを作成します。
以下のクエリを実行します。

CREATE DATABASE mydatabase

データベースにmydatabaseを選択します。

テーブルを作成する

テーブルとビューの[作成]から、[S3バケットデータ]をクリックします。

データベース設定で[既存のデータベースを選択]で先ほど作成したmydatabaseを選択します。

データセットに分析したいファイルがアップロードしてあるS3バケットを選択します。
データ形式にCSVを選択します。
列の詳細でカラムと属性を設定します。

[テーブルを作成]をクリックします。
これで分析の準備が整いました。

分析

上から10件のレコードを取得する

SELECT * FROM "default"."kensho_table" limit 10;

カロリーが高い順にレコードを取得する

SELECT * FROM kensho_table ORDER BY energy DESC

タンパク質（g）が２以上の野菜を多い順に取得する

SELECT vegetable,protein FROM kensho_table
WHERE 2 <= protein
ORDER BY protein DESC;

30kcal以上の野菜の数を取得する

SELECT vegetable, Count(vegetable)FROM "default"."kensho_table"
WHERE energy >= 30
GROUP BY vegetable;

まとめ

今回は、データが数値ばかりだったので、複雑な分析ができなかったですが、ログの分析などに役立つと思いました。
S3 Selectのような手軽さはありませんが、実行結果を残せる点などしっかり分析したい時におすすめです。