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

記事タイトルとURLをコピーする

はじめに

こんにちは。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)になります。

f:id:swx-tani:20220226165358p:plain

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

事前準備

データベースを作成する

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

f:id:swx-tani:20220228191829p:plain

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

CREATE DATABASE mydatabase

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

f:id:swx-tani:20220225165150p:plain

テーブルを作成する

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

f:id:swx-tani:20220225165608p:plain

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

f:id:swx-tani:20220225165936p:plain

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

f:id:swx-tani:20220228191843p:plain

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

分析

上から10件のレコードを取得する
SELECT * FROM "default"."kensho_table" limit 10;

f:id:swx-tani:20220226164054p:plain

カロリーが高い順にレコードを取得する
SELECT * FROM kensho_table ORDER BY energy DESC

f:id:swx-tani:20220226164624p:plain

タンパク質(g)が2以上の野菜を多い順に取得する
SELECT vegetable,protein FROM kensho_table
WHERE 2 <= protein
ORDER BY protein DESC;

f:id:swx-tani:20220226165057p:plain

30kcal以上の野菜の数を取得する
SELECT vegetable, Count(vegetable)FROM "default"."kensho_table"
WHERE energy >= 30
GROUP BY vegetable;

f:id:swx-tani:20220227141519p:plain

まとめ

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