はじめに

S3 Selectは、S3内のCSVやテキストファイルから簡単にクエリでデータ取得できるサービスです。

ログ解析等を簡単に実行できるサービスですが、一方で日本語の記事は充実しているとは言えない状況です。そこで今回、その操作法をざっくりまとめたいと思います。

なお類似サービスであるAthenaやRedshift Spectrumとの比較は、以下の記事を参照ください

前準備

S3 Selectを使用するために必要な環境を構築します。

使用するデータ

本記事では、こちらの記事で取得したIoTデータを、年ごとにフォルダ分けし、月ごとにCSVファイルにまとめて以下のフォルダ構成 (プレフィックス)でS3バケットに格納して使用します。

実際の使用時は、好きなCSVファイルやテキストファイルをバケット内に格納してください

バケット名
  └──sensors
      └──per_month
          ├─2020
          │   ├─202008.csv
          │   :
          │   └─202012.csv
          ├─2021
          │   ├─202101.csv
          │   :
          │   └─202012.csv
          └─2022
              ├─202101.csv
              :
              └─202008.csv

コンソールでの表示例

CSVファイルの中身は以下のようになっています (参考)

使用するCSVファイル

_id,Date_Master,Date_ScanStart,no01_DeviceName,no01_Date,no01_Temperature,no01_Humidity,no01_Light,no01_Human_last,no01_HumanMotion,no01_TempSetting,no01_AirconMode,no01_AirVolume,no01_AirDirection,no01_AirconPower,no01_CumulativeEnergy,no01_Watt,no02_DeviceName,no02_Date,no02_Temperature,no02_Humidity,no02_Light,no02_Pressure,no02_Noise,no02_eTVOC,no02_eCO2,no03_DeviceName,no03_Date,no03_Temperature,no03_Humidity,no04_DeviceName,no04_Date,no04_Temperature,no04_Humidity,no05_DeviceName,no05_Date,no05_Temperature,no05_Humidity,no05_Light,no05_UV,no05_Pressure,no05_Noise,no05_BatteryVoltage,no06_DeviceName,no06_Date,no06_Temperature,no06_Humidity,no07_DeviceName,no07_Date,no07_Temperature,no07_Humidity,no07_BatteryVoltage,no08_DeviceName,no08_Date,no08_HumanLast,no08_HumanMotion,_partition
5f359f60de55bc0802637080,2020-08-14 05:15:00.000,2020-08-14 05:15:03.891,Nature_Remo_1,2020-08-14 05:15:06.167,28.2,64.0,143.0,2020-08-13T20:08:42Z,0.0,28.0,cool,1,auto,power-on,2957.17,548.0,Omron_USB_1,2020-08-14 05:15:07.093,27.48,71.54,111,1005.229,58.29,289,1650,Inkbird_IBSTH1_1,2020-08-14 05:15:10.068,27.91,68.21,Inkbird_IBSTH1_2,2020-08-14 05:15:12.706,31.49,63.15,Omron_BAG_1,2020-08-14 05:15:17.748,32.18,62.48,6.0,0.01,1006.3,39.8,2.96,Inkbird_IBSTH1mini_1,2020-08-14 05:15:21.384,26.53,75.28,SwitchBot_Thermo_1,2020-08-14 05:15:26.413,31.7,65.0,100.0,Sony_MeshHuman_1,2020-08-14 05:15:28.835,2020-08-13T20:14:14.000Z,1,Project HomeIoT
5f35a09092376c3d5d76612c,2020-08-14 05:20:00.000,2020-08-14 05:20:04.054,Nature_Remo_1,2020-08-14 05:20:06.294,28.2,62.0,143.0,2020-08-13T20:08:42Z,0.0,28.0,cool,1,auto,power-on,2957.22,360.0,Omron_USB_1,2020-08-14 05:20:07.249,27.45,68.86,108,1005.284,46.18,280,1638,Inkbird_IBSTH1_1,2020-08-14 05:20:12.872,27.93,66.46,Inkbird_IBSTH1_2,2020-08-14 05:20:17.417,31.4,63.06,Omron_BAG_1,2020-08-14 05:20:22.446,32.24,62.63,13.0,0.02,1006.3,38.61,2.96,Inkbird_IBSTH1mini_1,2020-08-14 05:20:25.224,26.51,74.69,SwitchBot_Thermo_1,2020-08-14 05:20:30.252,31.9,64.0,100.0,Sony_MeshHuman_1,2020-08-14 05:20:32.635,2020-08-13T20:20:14.000Z,1,Project HomeIoT
5f35a1ba291e40c5ea258ee3,2020-08-14 05:25:00.000,2020-08-14 05:25:04.460,Nature_Remo_1,2020-08-14 05:25:06.701,28.2,64.0,143.0,2020-08-13T20:08:42Z,0.0,28.0,cool,1,auto,power-on,2957.25,580.0,Omron_USB_1,2020-08-14 05:25:07.458,27.32,70.51,106,1005.352,44.38,259,1615,Inkbird_IBSTH1_1,2020-08-14 05:25:09.629,27.95,68.14,Inkbird_IBSTH1_2,2020-08-14 05:25:11.219,31.4,62.94,Omron_BAG_1,2020-08-14 05:25:21.290,32.15,62.87,25.0,0.03,1006.3,39.02,2.96,Inkbird_IBSTH1mini_1,2020-08-14 05:25:23.063,26.53,73.39,SwitchBot_Thermo_1,2020-08-14 05:25:28.090,31.8,65.0,100.0,Sony_MeshHuman_1,2020-08-14 05:25:30.343,2020-08-13T20:24:14.000Z,1,Project HomeIoT
5f35a2e24098c6e9f618daa4,2020-08-14 05:30:00.000,2020-08-14 05:30:03.594,Nature_Remo_1,2020-08-14 05:30:05.794,28.2,64.0,143.0,2020-08-13T20:08:42Z,0.0,28.0,cool,1,auto,power-on,2957.29,428.0,Omron_USB_1,2020-08-14 05:30:06.555,27.25,73.02,106,1005.377,43.66,265,1623,Inkbird_IBSTH1_1,2020-08-14 05:30:09.387,27.92,68.94,Inkbird_IBSTH1_2,2020-08-14 05:30:12.162,31.49,62.93,Omron_BAG_1,2020-08-14 05:30:17.203,32.14,62.77,32.0,0.02,1006.5,39.22,2.96,Inkbird_IBSTH1mini_1,2020-08-14 05:30:18.825,26.53,75.26,SwitchBot_Thermo_1,2020-08-14 05:30:23.865,31.7,65.0,100.0,Sony_MeshHuman_1,2020-08-14 05:30:26.573,2020-08-13T20:30:15.000Z,1,Project HomeIoT
5f35a41a90393b85364a6c71,2020-08-14 05:35:00.000,2020-08-14 05:35:04.370,Nature_Remo_1,2020-08-14 05:35:06.617,28.2,64.0,143.0,2020-08-13T20:34:01Z,1.0,28.0,cool,1,auto,power-on,2957.32,456.0,Omron_USB_1,2020-08-14 05:35:07.443,27.45,74.21,104,1005.468,57.8,244,1598,Inkbird_IBSTH1_1,2020-08-14 05:35:10.129,28.07,69.8,Inkbird_IBSTH1_2,2020-08-14 05:35:12.960,31.47,62.75,Omron_BAG_1,2020-08-14 05:35:18.000,32.06,62.93,48.0,0.02,1006.6,39.22,2.96,Inkbird_IBSTH1mini_1,2020-08-14 05:35:25.717,26.62,76.67,SwitchBot_Thermo_1,2020-08-14 05:35:35.787,31.7,65.0,100.0,Sony_MeshHuman_1,2020-08-14 05:35:38.204,2020-08-13T20:34:15.000Z,1,Project HomeIoT
:

アクセス権限の付与

S3 Selectを使用するためには、適切なアクセス権限をIAMで作成して付与する必要があります。

IAMポリシーの作成

管理者ユーザーでログインして、コンソールからIAM画面に入り、
左側のタブから「ポリシー」→「ポリシーの作成」をクリックします

「JSON」タブをクリックし、JSONでの編集モードに入ります

以下のようなポリシーを記述します。
（例えば、S3SelectPolicyというポリシー名を付けます。"Resource"の部分を"*"から対象のバケットのみに絞るとセキュリティ強度がさらに強まりますが、利便性とのバランスで判断してください）

作成するS3SelectPolicyポリシー

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": "*"
        }
    ]
}

名前と説明を記載し、「ポリシーの作成」をクリックすれば作成完了です

ここからは

コンソールを使用する場合 or ローカルPCからスクリプトを使用する場合
EC2等のAWSリソースからスクリプトを使用する場合

で操作が変わります。

1.コンソールを使用する場合 or ローカルPCからスクリプトを使用する場合

コンソールを使用する場合、ユーザーに対してポリシーに基づくアクセス権限を与えます

ローカルPCからスクリプトを使用する場合、上記のユーザーから作成したアクセスキーをPCに登録します。

IAMユーザーの作成

上記ポリシーを付与するためのIAMユーザを作成します。
管理者ユーザーでログインして、コンソールからIAM → ユーザーと進み、「ユーザを追加」をクリックします

好きなユーザ名とパスワードを入力し、以下のようにユーザーを作成します

先ほど作成したポリシーをアタッチします

コンソールを使用する場合は、このユーザーでログインすることでS3 Selectの操作が可能となります。

ローカルPCからスクリプトを使用する場合、以下の処理を進めてアクセスキーを登録する必要があります。

アクセスキーの作成（ローカルPCからスクリプトを使用する場合）

作成したIAMユーザでコンソールにログインし、IAM → ユーザ → 先ほど作成したユーザ → 「セキュリティ認証情報」タブに移動し、「アクセスキーの作成」をクリックします。

アクセスキーを作成して出てきたアクセスキーIDとシークレットアクセスキーをメモします。

アクセスキーのCLIへの登録（ローカルPCからスクリプトを使用する場合）

AWSをコマンドラインから操作するためのAWS CLIを、クライアントPCに以下を参考にインストールしてください

インストールが完了したら、ターミナル（Windowsの場合Powershell）から以下のコマンドを打ち

aws configure

以下のように入力します

AWS Access Key ID [None]: [アクセスキーID]
AWS Secret Access Key [None]: [先ほどメモしたシークレットアクセスキー]
Default region name [None]: ap-northeast-1
Default output format [None]: [空欄でOK]

「~/.aws/credentials」と「~/.aws/config」に入力したクレデンシャル情報（アクセスキーやリージョン）が生成していれば成功です。

2. EC2等のAWSリソースからスクリプトを使用する場合

EC2やFargate、Lambda等のAWSリソースからPython SDKを通じてS3 Selectを使用する場合、先ほど作成したポリシーを付与したロールを、対象のリソースにアタッチする必要があります。以下の「AWS内サービスへの権限付与」を参照ください。

S3 Selectの使用法

S3 Selectは、コンソール、CLI、REST API、SDKを利用してS3内のデータをクエリする事ができます。

本記事ではは、このうち使用頻度が高いと思われるコンソール、SDKによる操作方法を紹介します。

コンソールから実行

コンソールからの使用時は、以下のようにオブジェクトを開き

「オブジェクトアクション」→「S3 Selectを使用したクエリ」でS3 Selectのコンソールを開けます

S3 Selectのコンソールは以下のようになっています

全ての列を取得したい場合は、以下のようにアスタリスク(*)を使用します

SELECT * FROM s3object s

実行結果

_id,Date_Master,Date_ScanStart,no01_DeviceName,no01_Date,no01_Temperature,no01_Humidity,no01_Light,no01_Human_last,no01_HumanMotion,no01_TempSetting,no01_AirconMode,no01_AirVolume,no01_AirDirection,no01_AirconPower,no01_CumulativeEnergy,no01_Watt,no02_DeviceName,no02_Date,no02_Temperature,no02_Humidity,no02_Light,no02_Pressure,no02_Noise,no02_eTVOC,no02_eCO2,no03_DeviceName,no03_Date,no03_Temperature,no03_Humidity,no04_DeviceName,no04_Date,no04_Temperature,no04_Humidity,no05_DeviceName,no05_Date,no05_Temperature,no05_Humidity,no05_Light,no05_UV,no05_Pressure,no05_Noise,no05_BatteryVoltage,no06_DeviceName,no06_Date,no06_Temperature,no06_Humidity,no07_DeviceName,no07_Date,no07_Temperature,no07_Humidity,no07_BatteryVoltage,no08_DeviceName,no08_Date,no08_HumanLast,no08_HumanMotion,_partition
5f359f60de55bc0802637080,2020-08-14 05:15:00.000,2020-08-14 05:15:03.891,Nature_Remo_1,2020-08-14 05:15:06.167,28.2,64.0,143.0,2020-08-13T20:08:42Z,0.0,28.0,cool,1,auto,power-on,2957.17,548.0,Omron_USB_1,2020-08-14 05:15:07.093,27.48,71.54,111,1005.229,58.29,289,1650,Inkbird_IBSTH1_1,2020-08-14 05:15:10.068,27.91,68.21,Inkbird_IBSTH1_2,2020-08-14 05:15:12.706,31.49,63.15,Omron_BAG_1,2020-08-14 05:15:17.748,32.18,62.48,6.0,0.01,1006.3,39.8,2.96,Inkbird_IBSTH1mini_1,2020-08-14 05:15:21.384,26.53,75.28,SwitchBot_Thermo_1,2020-08-14 05:15:26.413,31.7,65.0,100.0,Sony_MeshHuman_1,2020-08-14 05:15:28.835,2020-08-13T20:14:14.000Z,1,Project HomeIoT
5f35a09092376c3d5d76612c,2020-08-14 05:20:00.000,2020-08-14 05:20:04.054,Nature_Remo_1,2020-08-14 05:20:06.294,28.2,62.0,143.0,2020-08-13T20:08:42Z,0.0,28.0,cool,1,auto,power-on,2957.22,360.0,Omron_USB_1,2020-08-14 05:20:07.249,27.45,68.86,108,1005.284,46.18,280,1638,Inkbird_IBSTH1_1,2020-08-14 05:20:12.872,27.93,66.46,Inkbird_IBSTH1_2,2020-08-14 05:20:17.417,31.4,63.06,Omron_BAG_1,2020-08-14 05:20:22.446,32.24,62.63,13.0,0.02,1006.3,38.61,2.96,Inkbird_IBSTH1mini_1,2020-08-14 05:20:25.224,26.51,74.69,SwitchBot_Thermo_1,2020-08-14 05:20:30.252,31.9,64.0,100.0,Sony_MeshHuman_1,2020-08-14 05:20:32.635,2020-08-13T20:20:14.000Z,1,Project HomeIoT
:

一部の列を列名で取得したい場合や、列に対してフィルタ(Where句)や並び替えを指定したい場合は、上図の「CSVデータの最初の行を除外する」をチェックし、以下のように列名を記述したSQL文を実行します

SELECT s.Date_Master, s.no01_Temperature, s.no01_AirconPower FROM s3object s WHERE s.no01_Temperature > '28.5' AND no01_AirconPower = 'power-on'

なお、以下のように通常のSQLと比べてかなり制約が多いので、ご注意ください

数値に対して条件 (等号、不等号)を指定する場合も、'28.5'のようにクオーテーションで囲む必要あり
ORDER BYは使用できない
GROUP BYのような集計はできない

詳細は以下の公式リファレンスが参考になります

S3 Selectはあくまでシンプルなクエリにしか対応していないので、複雑なクエリを使用したい場合はAthenaやRedshift Spectrumを使用してください。

Python SDKから実行

S3 Selectを実活用するのであれば、コンソールよりもこちらの方法を使用する事が多いかと思います (バッチ化することで処理の自動化が可能)

注意点として、前述のようにS3 Selectはクエリ文の制約が厳しいので、まずコンソールで目的のクエリがエラーなく実行できるか確認する事をお薦めします。

スクリプト例

下の例では、コンソールでの実行例と同じクエリを、sensors/per_monthフォルダ (プレフィックス)内のCSVファイル (オブジェクト)全てに対してクエリを実行して合体し、Pandasのデータフレームに変換しています。

s3select_example.py

import boto3
import pandas as pd

BUCKET_NAME = ここにバケット名を記載  # バケット名
COLNAMES = ['Date_Master', 'no01_Temperature', 'no01_AirconPower']  # クエリ取得対象の列名

# SQLクエリを記述
colnames = [f's.{colname}' for colname in COLNAMES]  # 列名の冒頭に's.'を追加
query = f"SELECT {','.join(colnames)} FROM s3object s WHERE s.no01_Temperature > '28.5' AND no01_AirconPower = 'power-on'"

# S3のバケットにアクセス (リソースAPIを使用)
s3_resource = boto3.resource('s3')
bucket = s3_resource.Bucket(BUCKET_NAME)
# 指定したプレフィックス内のCSVファイルのみ検索 (endswithを正規表現に変えて検索する事も可能)
objects = [obj for obj in bucket.objects.filter(Prefix='sensors/per_month/') if obj.key.endswith('.csv')]

# CSVファイルごとのDataFrameを格納するリスト
df_all = []

# 該当するCSVファイルそれぞれにS3 Selectのクエリ実行
for obj in objects:
    print(f'object key: {obj.key}')
    # S3 Selectのクエリ実行
    resp = s3_resource.meta.client.select_object_content(
        Bucket=BUCKET_NAME,  # バケット名
        Key=obj.key,  # キー(プレフィックスも含んだオブジェクト名)
        ExpressionType='SQL',
        Expression=query,  # クエリ文をここに記述
        InputSerialization = {'CSV': {"FileHeaderInfo": "Use"}, 'CompressionType': 'NONE'},  # 入力設定(ヘッダや圧縮の有無等を記述)
        OutputSerialization = {'CSV': {}},  # 出力設定
    )

    # クエリ結果をデコードしてPandasのデータフレームに格納
    # (参考https://aws.amazon.com/jp/blogs/storage/querying-data-without-servers-or-databases-using-amazon-s3-select/)
    for event in resp['Payload']:
        if 'Records' in event:
            # デコード
            records = event['Records']['Payload'].decode('utf-8')
            # 改行で行分割
            records = records.splitlines()
            # カンマ区切りで列分割
            records = list(map(lambda record: record.split(','), records))
            # PandasのDataFrameに変換してリストに格納
            df_records = pd.DataFrame(records, columns=COLNAMES)
            df_all.append(df_records)
        elif 'Stats' in event:
            statsDetails = event['Stats']['Details']
            print(f'Stats details bytesScanned: {statsDetails["BytesScanned"]}')
            print(f'Stats details bytesProcessed: {statsDetails["BytesProcessed"]}')

# 全てのDataFrameを合体
df_all = pd.concat(df_all, axis=0)
print(df_all.head())

実行結果

               Date_Master no01_Temperature no01_AirconPower
0  2020-08-14 13:35:00.000             30.0         power-on
1  2020-08-14 13:40:00.000             30.0         power-on
2  2020-08-14 13:45:00.000             30.0         power-on
3  2020-08-14 13:50:00.000             30.0         power-on
4  2020-08-14 13:55:00.000        29.400002         power-on

注意点として、S3 Selectには複数のオブジェクト(CSVファイル)に同時にクエリを実行する機能は存在しないため、Boto3とPythonを使って予め対象オブジェクトをリスト化し、都度クエリを実行する必要があります。

また、クエリ結果もデコードや行列の分割(DataFrame化)等の処理が必要となるので、実装にはそれなりに手間が掛かります。

S3 Selectの料金

S3 Selectの料金は、以下の公式サイトに記載されています。

2023年4月現在、東京リージョンでは以下のような料金となっています。

こちらによると「スキャンされたデータ」はクエリ対象となったファイル全体のサイズ（今回使用したサンプルデータでは、CSVファイル全体のサイズ）を、「戻りデータ」はクエリで返されるデータのサイズ（クエリのWHEREやSELECTでフィルタリングされた後のサイズ）を表します。

WHEREやSELECT等のクエリの工夫によりサイズが縮小できるのは「戻りデータ」の方ですが、S3 Selectでは「スキャンされたデータ」の方が料金が高く、クエリの工夫によりコスト削減できる余地は小さいと言えそうです。

一方で、「スキャンされたデータ」のコストは、データを圧縮（GZIPまたはBZIP2）する事で低減できます。