S3 Selectをやってみた

# S3 SelectとAthenaの違い

- Athena
  - クエリの機能も対応フォーマットも多い
  - 複数のファイルを対象にできる
- S3 Select
  - 単一ファイルを対象
  - CSVとJSON（LD JSONもOKの模様）に対応
  - 少しだけお手軽
  - クエリの機能が少ない。`ORDER BY`すらできない
- AthenaでS3をクエリするときは、全データをAthenaに送ってからクエリする。SelectはS3の中だけでクエリしているので、パフォーマンスがいいはず
- AthenaからSelectを使うこともできるらしい

AWSコンソールでS3を見ていて「あ、このファイルの中をさくっと検索したいな」と思ったときはS3 Select、腰を据えた集計をするときはAthenaという使いわけかなと思った。


# テスト用ファイル

テスト用ファイルはこちら。JSONと言っているけどカンマが抜けているのでvalidなJSONじゃないです。

```s3select.json
{
  "_id": "5ad023505514e15120684996",
  "age": 34,
  "eyeColor": "green",
  "name": {
    "first": "Dalton",
    "last": "Alvarado"
  },
  "tags": [
    "laboris",
    "pariatur",
    "sunt",
    "exercitation",
    "esse"
  ],
  "friends": [
    {
      "id": 0,
      "name": "Kristen Jenkins"
    },
    {
      "id": 1,
      "name": "Burch Daugherty"
    },
    {
      "id": 2,
      "name": "Wynn Brown"
    }
  ],
  "favoriteFruit": "banana"
}
{
  "_id": "5ad02350becf165e64c27ad9",
  "age": 23,
  "eyeColor": "blue",
  "name": {
    "first": "Martha",
    "last": "Graham"
  },
  "tags": [
    "ad",
    "aliqua",
    "enim",
    "eu",
    "ipsum"
  ],
  "friends": [
    {
      "id": 0,
      "name": "Adams Mccullough"
    },
    {
      "id": 1,
      "name": "Minerva Kent"
    },
    {
      "id": 2,
      "name": "Bishop Bradley"
    }
  ],
  "favoriteFruit": "banana"
}
{
  "_id": "5ad023507622d8404af14399",
  "age": 34,
  "eyeColor": "green",
  "name": {
    "first": "Juliette",
    "last": "Holmes"
  },
  "tags": [
    "sunt",
    "anim",
    "nisi",
    "est",
    "ipsum"
  ],
  "friends": [
    {
      "id": 0,
      "name": "Massey Nash"
    },
    {
      "id": 1,
      "name": "Skinner Blanchard"
    },
    {
      "id": 2,
      "name": "Barber Dalton"
    }
  ],
  "favoriteFruit": "strawberry"
}
{
  "_id": "5ad02350c8fff17c3fb1dcb9",
  "age": 22,
  "eyeColor": "brown",
  "name": {
    "first": "Patrice",
    "last": "Doyle"
  },
  "tags": [
    "esse",
    "ex",
    "magna",
    "minim",
    "velit"
  ],
  "friends": [
    {
      "id": 0,
      "name": "Bush Santiago"
    },
    {
      "id": 1,
      "name": "Jasmine Howe"
    },
    {
      "id": 2,
      "name": "Rebecca Sellers"
    }
  ],
  "favoriteFruit": "apple"
}
{
  "_id": "5ad02350259ad16576bf2335",
  "age": 38,
  "eyeColor": "brown",
  "name": {
    "first": "Bolton",
    "last": "Clark"
  },
  "tags": [
    "elit",
    "ad",
    "nulla",
    "commodo",
    "laboris"
  ],
  "friends": [
    {
      "id": 0,
      "name": "Deidre Hayes"
    },
    {
      "id": 1,
      "name": "James Casey"
    },
    {
      "id": 2,
      "name": "Duran Spencer"
    }
  ],
  "favoriteFruit": "strawberry"
}
{
  "_id": "5ad02350f126c16eb7e76a48",
  "age": 28,
  "eyeColor": "green",
  "name": {
    "first": "Guadalupe",
    "last": "Young"
  },
  "tags": [
    "et",
    "duis",
    "enim",
    "aliqua",
    "aute"
  ],
  "friends": [
    {
      "id": 0,
      "name": "Owen Ford"
    },
    {
      "id": 1,
      "name": "Carrillo Harrison"
    },
    {
      "id": 2,
      "name": "Tricia Castillo"
    }
  ],
  "favoriteFruit": "strawberry"
}
{
  "_id": "5ad023500cf7eeb255c32926",
  "age": 30,
  "eyeColor": "green",
  "name": {
    "first": "Anthony",
    "last": "Brooks"
  },
  "tags": [
    "aliquip",
    "minim",
    "fugiat",
    "ut",
    "cupidatat"
  ],
  "friends": [
    {
      "id": 0,
      "name": "Flores Mcpherson"
    },
    {
      "id": 1,
      "name": "Kimberley England"
    },
    {
      "id": 2,
      "name": "Selma Padilla"
    }
  ],
  "favoriteFruit": "strawberry"
}
```

# AWSコンソールでの使い方

ここからポチポチでわりと手軽にできる。

![S3 Select.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/79394/fe661af6-d855-e398-d7ff-5910654f6e5d.png)


# CLIでの使い方

```bash
aws s3api select-object-content \
  --bucket my-bucket \
  --key s3select.json \
  --input-serialization '{"JSON": {"Type": "LINES"}}' \
  --output-serialization '{"JSON":{"RecordDelimiter":"\n"}}' \
  --expression "select * from s3object s where s.eyeColor = 'green'" \
  --expression-type SQL out.json
```

# 参考

- [S3 Select の機能を awscli から使ってみた](https://tkaaad97.hatenablog.com/entry/2018/06/05/013009)
- [S3 SelectでどんなSQLが書けるか検証](https://xp-cloud.jp/blog/2018/05/16/3287/?fbclid=IwAR1FRB_3pv3DtcYjRROTIGvxfTFJNaXLi5v8C04r1qdOv-_7Ex84X3Uuzz0)
- https://www.reddit.com/r/aws/comments/8d3qb3/athena_vs_s3_select/