トップ > Amazon S3 > S3 Select で CSV ファイルをクエリしてみた

S3 Select で CSV ファイルをクエリしてみた

Amazon S3 SQL
記事タイトルとURLをコピーする

アプリケーションサービス部の宮本です。
今回は S3 Select の検証をしたのでご紹介します。

はじめに

S3 Select とは

Amazon S3 Select では、シンプルな構造化クエリ言語 (SQL) ステートメントを使用して Amazon S3 オブジェクトのコンテンツをフィルタリングすることで、必要なデータのサブセットのみ取得することができます。

https://docs.aws.amazon.com/ja_jp/AmazonS3/latest/userguide/selecting-content-from-objects.html

はい、つまり S3 に配置したファイルに対して SQL を発行できるということですね。 S3 に SQL、というと Amazon Athena を思い浮かべる方が多いと思いますが、事前にデータカタログを作成する必要があります。対して S3 Select は S3 にファイルを配置するだけで実行できるのでよりお手軽に使用出来ます。

対応しているファイル形式は以下の通りで、GZIP や BZIP2 で圧縮されたオブジェクトにもクエリ可能です。

  • CSV
  • JSON
  • Apache Parquet

料金

東京リージョン、かつ標準ストレージクラスの場合です。これに加えてストレージ保管容量など、通常の S3 料金が課金されます。

  • スキャンされたデータ: 0.00225USD/GB
  • クライアントに返されたデータ: 0.0008USD/GB

スキャンされたデータに対して課金される為、可能であれば圧縮したデータを使用することで、料金を抑えられます。

参考) 料金 - Amazon S3 ｜ AWS

検証

事前準備

AWS CLI を使って検証します。手元の環境は以下の通りです。

$ aws --version
aws-cli/2.2.5 Python/3.9.5 Darwin/19.6.0 source/x86_64 prompt/off

任意の名前でバケットを作成しておきます。

$ aws s3 mb s3://s3-select-sample-20210615

aws-samples からサンプルデータを拝借し、S3 にアップロードしておきます。

$ curl https://raw.githubusercontent.com/aws-samples/s3-select-phonebook-search/master/src/test/resources/sample_data.csv -o sample_data.csv
$ aws s3 cp sample_data.csv s3://s3-select-sample-20210615

中身を確認しておきましょう。見出し行あり、カンマ区切り、文字コードは UTF-8、改行コードは CRLF です。

$ cat sample_data.csv
Name,PhoneNumber,City,Occupation
Sam,(949) 123-45567,Irvine,Solutions Architect
Vinod,(949) 123-4556,Los Angeles,Solutions Architect
Jeff,(949) 123-45567,Seattle,AWS Evangelist
Jane,(949) 123-45567,Chicago,Developer
Sean,(949) 123-45567,Chicago,Developer
Mary,(949) 123-45567,Chicago,Developer
Kate,(949) 123-45567,Chicago,Developer

これで準備完了です。

クエリ実行

今回はCLI で実行します。リファレンスはこちら。

$ aws s3api select-object-content --bucket s3-select-sample-20210615 --key sample_data.csv \
--expression "SELECT * FROM S3Object s WHERE s.City = 'Chicago'" \
--expression-type SQL \
--input-serialization '{"CSV": {"FileHeaderInfo": "USE", "RecordDelimiter": "\r\n", "FieldDelimiter": ",", "QuoteCharacter": ""}}' \
--output-serialization '{"CSV": {}}' \
sample_data_out.csv

オプションを解説します。

  • bucket: ファイルを配置したバケット名
  • key: 配置したファイル名
  • expression: SELECT 文
  • expression-type: SQL 固定
  • input-serialization: クエリ対象ファイルの設定。CSV の部分は JSON、Parquet も指定出来ます。
    • FileHeaderInfo: 見出し行の設定
      • USE: 見出し行あり、かつ SQL のカラム名の指定で使用する。
      • IGNORE: 見出し行あり、SQL のカラム名の指定で使用しない。この場合、カラムは _1, _2 のように指定します。
      • NONE: 見出し行なし
    • RecordDelimiter: 改行コード
    • FieldDelimiter: 区切り文字
    • QuoteCharacter: 項目の引用符
  • output-serialization: クエリ結果ファイル(上記例では sample_data_out.csv) の設定。全てデフォルトとしています。
$ cat sample_data_out.csv
Jane,(949) 123-45567,Chicago,Developer
Sean,(949) 123-45567,Chicago,Developer
Mary,(949) 123-45567,Chicago,Developer
Kate,(949) 123-45567,Chicago,Developer

結果はコマンドの引数で指定したファイルに出力されます。

日本語対応は？

続いて日本語対応も確認しておきます。 見出し行、データに日本語を含めてアップロードします。

$ cp sample_data.csv sample_data_ja.csv
$ vim sample_data_ja.csv

Name,PhoneNumber,都市,Occupation
Sam,(949) 123-45567,Irvine,Solutions Architect
Vinod,(949) 123-4556,Los Angeles,Solutions Architect
Jeff,(949) 123-45567,Seattle,AWS Evangelist
Jane,(949) 123-45567,シカゴ,Developer
Sean,(949) 123-45567,シカゴ,Developer
Mary,(949) 123-45567,シカゴ,Developer
Kate,(949) 123-45567,シカゴ,Developer
$ aws s3 cp sample_data_ja.csv s3://s3-select-sample-20210615

日本語のカラム指定で実行します。

$ aws s3api select-object-content --bucket s3-select-sample-20210615 --key sample_data_ja.csv \
--expression "SELECT * FROM S3Object s WHERE s.都市 = 'シカゴ'" \
--expression-type SQL \
--input-serialization '{"CSV": {"FileHeaderInfo": "USE", "RecordDelimiter": "\r\n", "FieldDelimiter": ",", "QuoteCharacter": ""}}' \
--output-serialization '{"CSV": {}}' \
sample_data_ja_out.csv  

An error occurred (LexerInvalidChar) when calling the SelectObjectContent operation: Invalid character at line 1, column 34.

おや、条件として日本語は使用できないようです。都市 を二重引用符で囲むなどしても上手くいきませんでした。 こんな時は FileHeaderInfo を IGNORE とし、条件を _3 のようにカラムの番号指定として回避します。

$ aws s3api select-object-content --bucket s3-select-sample-20210615 --key sample_data_ja.csv \
--expression "SELECT * FROM S3Object s WHERE s._3 = 'シカゴ'" \
--expression-type SQL \
--input-serialization '{"CSV": {"FileHeaderInfo": "IGNORE", "RecordDelimiter": "\r\n", "FieldDelimiter": ",", "QuoteCharacter": ""}}' \
--output-serialization '{"CSV": {}}' \
sample_data_ja_out.csv  

$ cat sample_data_ja_out.csv
Jane,(949) 123-45567,シカゴ,Developer
Sean,(949) 123-45567,シカゴ,Developer
Mary,(949) 123-45567,シカゴ,Developer
Kate,(949) 123-45567,シカゴ,Developer

上手くいきましたね。

関数

関数も豊富ではないですが使えます。

$ aws s3api select-object-content --bucket s3-select-sample-20210615 --key sample_data.csv \
--expression "SELECT UPPER(s.Name) FROM S3Object s WHERE s.Occupation = 'Developer'" \
--expression-type SQL \
--input-serialization '{"CSV": {"FileHeaderInfo": "USE", "RecordDelimiter": "\r\n", "FieldDelimiter": ",", "QuoteCharacter": ""}}' \
--output-serialization '{"CSV": {}}' \
sample_data_upper_out.csv  

$ cat sample_data_upper_out.csv
JANE
SEAN
MARY
KATE

注意点

クエリ出来るファイルは UTF-8 のみです。UTF-8 以外だと以下のようなエラーになりました。

An error occurred (InvalidTextEncoding) when calling the SelectObjectContent operation: UTF-8 encoding is required. The text encoding error was found near byte 304.

まとめ

S3 Select の検証でした。以下ポイントを押さえておけば良いでしょう。

  • 見出し行の有無、改行コード、区切り文字はクエリ時に指定できる。
  • 見出し行の日本語は、クエリ時の条件や SELECT 句で使用できない。
  • オブジェクトのサイズが大きい場合は、圧縮しておくとコスト効率が良い。
  • オブジェクトの文字エンコードは UTF-8 (BOM 付きでもなしでもOK) にする必要がある。
    • Excel on Windows から CSV を作成するときは、「名前を付けて保存」で「CSV UTF-8」を指定すると S3 Select でクエリ可能な形式で出力できます。

ちょっとしたアプリケーションで SQL を使いたい、でも RDS 等のデータベースを構築する程の規模でも無い... といった場合に有効ですね。

参考