【AWS】S3 Select 使い方まとめ

# はじめに

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

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

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

https://qiita.com/c60evaporator/items/f6c3a2aa1ba04618fe5e

# 前準備

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

## 使用するデータ

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

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

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

コンソールでの表示例

![スクリーンショット 2023-03-19 2.09.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/610167/2fcb4117-730d-7568-60bd-432f2e4fe886.png)

CSVファイルの中身は以下のようになっています ([参考](https://qiita.com/c60evaporator/items/92d0ebde001f568f2707#スキーマの作成))

```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画面に入り、
左側のタブから「ポリシー」→「ポリシーの作成」をクリックします

<img width=600 src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/610167/0990bac4-640f-4e77-1c78-ba2fdeec3de7.png">

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

<img width=400 src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/610167/c60cbcd6-dbf2-2a9d-1fac-57944eec2eaf.png">

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

```json:作成するS3SelectPolicyポリシー
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": "*"
        }
    ]
}
```
名前と説明を記載し、「ポリシーの作成」をクリックすれば作成完了です

ここからは

1. [**コンソールを使用する場合** or **ローカルPCからスクリプトを使用する場合**](https://qiita.com/c60evaporator/items/a926097d7275f07fef03#1コンソールを使用する場合-or-ローカルpcからスクリプトを使用する場合)
2. [**EC2等のAWSリソースからスクリプトを使用する場合**](https://qiita.com/c60evaporator/items/a926097d7275f07fef03#2-ec2等のawsリソースからスクリプトを使用する場合)

で**操作が変わります**。

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

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

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

#### IAMユーザーの作成

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

![スクリーンショット 2021-11-27 18.29.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/610167/6718429c-9cea-7361-bdb0-277fd1564c31.png)

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

![スクリーンショット 2023-03-21 23.14.28 2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/610167/0e16fbca-66be-a8ff-1acf-0650ff416908.png)

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

![スクリーンショット 2023-03-22 0.28.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/610167/994e0adc-5d29-0ed8-5954-dd224ec0f5f6.png)

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

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

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

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

<img width=480 src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/610167/69f65e8b-4ea6-b873-22d4-09a0c2e01935.png>

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

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

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

https://docs.aws.amazon.com/ja_jp/cli/latest/userguide/install-cliv2.html

インストールが完了したら、ターミナル（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を使用する場合、[先ほど作成したポリシー](https://qiita.com/c60evaporator/items/a926097d7275f07fef03#iamポリシーの作成)を付与したロールを、対象のリソースにアタッチする必要があります。以下の「AWS内サービスへの権限付与」を参照ください。

https://qiita.com/c60evaporator/items/0121399880625cc1de51#ロールの作成


# S3 Selectの使用法

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

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

## コンソールから実行

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

<img width=400 src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/610167/de9feff3-7866-ccd7-1aa5-900d4457f154.png>

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

<img width=400 src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/610167/43afe667-2450-f6d2-1935-43b7e672389c.png>

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

![スクリーンショット 2022-10-02 23.53.17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/610167/16e76b9b-c825-c5da-e315-139d7aac2a48.png)

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

```sql
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文を実行します

```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のような集計はできない

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

https://docs.aws.amazon.com/ja_jp/AmazonS3/latest/userguide/s3-glacier-select-sql-reference.html

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


## Python SDKから実行

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

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

### スクリプト例
下の例では、[コンソールでの実行例と同じクエリ](https://qiita.com/c60evaporator/items/a926097d7275f07fef03#コンソールから実行)を、`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の料金は、以下の公式サイトに記載されています。

https://aws.amazon.com/jp/s3/pricing/

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

![スクリーンショット 2023-03-18 23.26.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/610167/5d9093a0-fdfd-fd11-86b1-d96461e3d02f.png)

[こちら](https://blog.clairvoyantsoft.com/accelerate-s3-data-querying-performance-with-s3-select-ff84dde1b8da)によると「スキャンされたデータ」はクエリ対象となったファイル全体のサイズ（今回使用したサンプルデータでは、CSVファイル全体のサイズ）を、「戻りデータ」はクエリで返されるデータのサイズ（クエリのWHEREやSELECTでフィルタリングされた後のサイズ）を表します。

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

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