はじめに
こんにちは。PE部の谷です。
S3 Selectを使ってSQLクエリを実行してみました。
前提知識
SQL
SQLはデータベース言語の一つで、データベースの定義や操作を行うことができます。 (データベースとやり取りするときに使う言語)
Structured Query Languageの略という記事もあれば、何かの略語ではないという記事もありました。
SQLの基本的な命令は4つです。
SELECT:データの検索
INSERT:データの追加
UPDATE:データの更新
DELETE:データの削除
SELECT カラム名 FROM テーブル名 WHERE hoge = 'fuga';
上記は、テーブルからhogeカラムが'fuga'と一致するレコードを検索し取得するという命令分です。
S3 Select
S3 Select を使用すると、データを簡単に S3 に保存し、SQL ステートメントを使用して S3 オブジェクトのコンテンツをフィルタリングし、必要なデータのみを取得するクエリを実行できます。 AWS公式より引用
S3にファイルをアップロードするだけでそのファイルに対してSQLクエリを実行することができます。
対応しているファイル形式はCSV、JSON、またはParquet形式で、
エンコーディングタイプはUTF-8のみです。
また、SELECTコマンドのみをサポートしています。
やってみよう
今回使うデータは、最近1年間(2021/2/23~2022/2/23)の名古屋の天気情報(平均気温、平均風速、昼と夜の天気)です。 気象庁の各種データ・資料からCSVファイルを作成しました。
名古屋の天気.csv
このファイルをS3にアップロードし、[オブジェクトアクション]から[S3 Select を使用したクエリ]をクリックします。
入力設定、出力設定を行いSQLクエリを実行してみます。
上から5件のレコードを取得する
SELECT * FROM s3object s LIMIT 5
2021/2/23,9.4,5.9,晴時々薄曇,晴時々薄曇 2021/2/24,5.5,5.8,晴,晴 2021/2/25,7.0,2.3,薄曇時々晴,曇 2021/2/26,8.7,3.8,雨後曇,曇 2021/2/27,7.8,4.5,薄曇後一時晴,曇時々晴
SELECTの後の * は、全てを表します。
FROMの後のs3object sは、これによってテーブル名がsになるので、カラムを指定する時は「s.カラム名」と書くことができます。
LIMITは、取得するデータの行数の上限を設定することができます。
平均気温が10℃以下の日付と平均気温を取得する
SELECT s.NagoyaDate,s.AverageTemperature FROM s3object s WHERE s.AverageTemperature <= '10.0'
2021/3/14,10.0 2021/12/26,1.5 2021/12/27,0.9 2021/12/31,1.5 2022/1/1,1.4 2022/2/5,1.6 2022/2/6,0.9 2022/2/17,1.0 2022/2/21,1.7
該当するレコードの日付と平均気温カラムのみ取得したいので、SELECTの後をs.NagoyaDate,s.AverageTemperatureにします。
平均気温が10℃以下など検索条件を追加したい時にWHEREを使います。
天気が一瞬でも晴だった日を取得する
SELECT * FROM s3object s WHERE s.DaytimeWeather LIKE '%晴%' OR s.NightWeather LIKE '%晴%'
2021/2/23,9.4,5.9,晴時々薄曇,晴時々薄曇 2021/2/24,5.5,5.8,晴,晴 2021/2/25,7.0,2.3,薄曇時々晴,曇 2021/2/27,7.8,4.5,薄曇後一時晴,曇時々晴 2021/2/28,8.8,1.8,晴後曇,曇一時晴 2021/3/1,12.9,2.7,曇時々晴,曇時々雨 2021/3/2,10.8,6.2,雨後曇,快晴 ・ ・ ・
お昼の天気と夜の天気のカラムどちらかに'晴'という文字列を含むレコードを取得します。 LIKEを使って特定の文字列を含む含まない検索を行います。
平均気温が高い順にレコードを取得する
SELECT * FROM s3object s ORDER BY s.AverageTemperature DESC
Unexpected token found KEYWORD
エラーになってしまいました。
調べてみると、ソートして並び替えるORDER BYはS3 Selectではできないみたいです。
データをグループ化させるGROUP BYもできないみたいです。
まとめ
S3 Selectを使ってみた感想は、簡単!です。
ファイルのアップロードからS3 Selectの設定、クエリの実行までパッとできて驚きました。
S3 Selectではできることが限られているため、もう少し複雑なクエリの実行をAmazon Athenaでやってみたいと思います。