Athenaで集計クエリー

久々のAthena

データの集計をする必要が出てきたので、かなり久しぶりにAthenaを使ってみました。 エンジンもVersion 2でPresto 0.217になり、Glueの機能拡張も相まって、以前に利用した際と比べても明らかに使い易くなりました。
ヒストグラムグラフを作る必要があり count() を使った際に思わぬハマりがあったので、記事にしておきます。

レコードを数える

言わずもがな、SELECT count(*) FROM my_table とすることで、テーブルのレコード数は数えられます。 今回、ヒストグラムのグラフを作成したデータとは違いますが、機械学習のデータとしても有名なIrisのサンプルデータを使ってAthenaのクエリーを実行してみたいと思います。

サンプルデータをDatatableにする

ダウンロードしたデータのうち、iris.csv をテーブル化したいと思います。 まずは、データを同一のS3のパスに配置します。

スキーマがすでにわかっているので、直接テーブルを作成してもよいのですが、今回はGlueのCrawlerを利用することにします。 一時的にテーブルスキーマを作成することが目的なので、細かい指定は省きますがGlueのコンソールからAdd crawler ボタンを クリックして、ウィザードにしたがって順に作成してゆきます。 スキーマの設定後にクローラーを実行するとDatatableが作成されます。
テーブルを作成するのが劇的に簡単になったのは、本当に良いですね。

作成したテーブルを分析

早速、Athenaのコンソールで、データセットのレコード数を確認してみます。 150レコードで件数もあっているようです。

SELECT count(*) AS _total
FROM iris

次にお決まりのガクの幅でヒストグラムを書くためにサンプルの数を数えたいと思います。
5つのカテゴリに分類するために最小値、最大値とその差を抽出します。
クエリの結果を見る限りでは、2.0cmから0.48cm刻みでカテゴリを作ることで5つにカテゴライズできそうです。

SELECT min(sepalwidthcm) AS _min,
         max(sepalwidthcm) AS _max,
         max(sepalwidthcm) - min(sepalwidthcm) AS _diff,
         (max(sepalwidthcm) - min(sepalwidthcm))/5 AS _step
FROM iris

集計クエリーの実行

下記の5つのカテゴリのサンプル数を数える集計クエリーを実行してみます。

普通に考えるとこんなクエリーを書くのではないでしょうか。
これでうまくいくのであれば、わざわざ記事にならないというのはお察しのとおりですが、 結果を見ると全てのカテゴリで150サンプルという残念な結果になります。

SELECT 
    count(sepalwidthcm<2.48) as _c1,
    count(2.48<=sepalwidthcm and sepalwidthcm<2.96) as _c2,
    count(2.96<=sepalwidthcm and sepalwidthcm<3.44) as _c3,
    count(3.44<=sepalwidthcm and sepalwidthcm<3.92) as _c4,
    count(3.92<=sepalwidthcm) as _c5
FROM iris

下記だとうまくいきます。 ポイントは、or nullのようです。

SELECT 
    count((sepalwidthcm<2.48) or null) as _c1,
    count((2.48<=sepalwidthcm and sepalwidthcm<2.96) or null) as _c2,
    count((2.96<=sepalwidthcm and sepalwidthcm<3.44) or null) as _c3,
    count((3.44<=sepalwidthcm and sepalwidthcm<3.92) or null) as _c4,
    count((3.92<=sepalwidthcm) or null) as _c5
FROM iris

Countの仕様によるところが大きく詳細を解説されているサイトがありましたので、参考にしてみてください。
SQLも奥が深いですね。では。

参照

• Iris Species
• 条件付きのCOUNTクエリ

• ← Previous Post
• Next Post →