kakakakakku blog

Weekly Tech Blog : Keep on Learning 👍

Aurora v1.13 から使える SELECT INTO OUTFILE S3 を試した

Aurora v1.14 の強制アップデートがあったりして,改めて Aurora のリリースノートを読み直していたら,Aurora v1.13 から SELECT INTO OUTFILE S3 が使えるようになっていることに気付いた(今さら!).今まで試したことが無かったので,実際に試してみた.

大きめのデータ抽出が必要なときなど,MySQL だと SELECT INTO OUTFILE でファイルに書き出すシチュエーションがあるため,Aurora でも使えるようになるのは非常に良いことだと思う.むしろ v1.13 まで無かったんだ...?という感じ.

docs.aws.amazon.com

事前準備

S3 と IAM ロールを作成する

事前に RDS 用の IAM ロールと IAM ポリシーを用意しておく.必要最小限のアクションは以下のドキュメントにまとまっている.今回は SELECT INTO OUTFILE S3 に必要なアクションだけを許可した.あと S3 も作成しておく.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Stmt1506948517000",
            "Effect": "Allow",
            "Action": [
                "s3:AbortMultipartUpload",
                "s3:DeleteObject",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:ListMultipartUploadParts",
                "s3:PutObject"
            ],
            "Resource": [
                "arn:aws:s3:::xxx",
                "arn:aws:s3:::xxx/*"
            ]
        }
    ]
}

docs.aws.amazon.com

Aurora クラスターに IAM ロールをアタッチする

次に Aurora クラスターに IAM ロールをアタッチする必要があり,今回は管理コンソールで実施した.これだけではダメで,さらにパラメータグループ(クラスター)も修正する必要がある.今回は aurora_select_into_s3_role パラメータに作成した IAM ロールの ARN を登録した.詳しくはドキュメントに載っている.

docs.aws.amazon.com

ちなみに aurora_select_into_s3_role パラメータを修正し忘れると,以下のエラーが出る.

ERROR 1871 (HY000): S3 API returned error: Both aurora_select_into_s3_role and aws_default_s3_role are not specified, please see documentation for more details

GRANT

基本的に RDS のマスタユーザーを使うことはないと思うので,SELECT INTO OUTFILE S3 を実行するユーザーに SELECT INTO S3 権限を付与しておく必要がある.ここは公式ドキュメントの通りで良い.

mysql> GRANT SELECT INTO S3 ON *.* TO user@domain-or-ip-address

サンプルデータ

MySQL が公開している world database を使う.

world.sql を流し込んで,3テーブル存在していれば,問題なし.

mysql> SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.01 sec)

SELECT INTO OUTFILE S3

事前準備が多くなってしまったが,やっと本題に入る.既に用意している city テーブルのデータを S3 に出力してみた.

mysql> SELECT * FROM city INTO OUTFILE S3 's3://xxx/city';

S3 にファイルを出力できた.

$ aws s3 ls xxx
2017-10-02 22:34:29     143565 city.part_00000

ファイルも問題なく確認できた.

$ head -n 10 ~/city.part_00000
1  Kabul   AFG Kabol   1780000
2  Qandahar    AFG Qandahar    237500
3  Herat   AFG Herat   186800
4  Mazar-e-Sharif  AFG Balkh   127800
5  Amsterdam   NLD Noord-Holland   731200
6  Rotterdam   NLD Zuid-Holland    593321
7  Haag    NLD Zuid-Holland    440900
8  Utrecht NLD Utrecht 234323
9  Eindhoven   NLD Noord-Brabant   201843
10 Tilburg NLD Noord-Brabant   193238

ポイントなど

詳しくは公式ドキュメントに記載されているが,カスタマイズ可能なパラメータが一部ある.

docs.aws.amazon.com

S3 バケットのリージョン指定

出力する S3 のバケットが Aurora と別のリージョンにある場合,以下のようにリージョンを指定する構文がサポートされている.

s3-region://bucket-name/file-prefix

具体的には以下のようになる.

  • s3-us-west-1://
  • s3-ap-northeast-1://

ただし,デフォルトでは Aurora と同じリージョンになるため,特殊な要件がない限りはそのままで良いと思う.

region 値を指定しないと、Aurora は DB クラスターと同じリージョンの Amazon S3 にファイルを保存します。

FIELDS TERMINATED BYLINES TERMINATED BY

デフォルトではタブ区切りで出力されるが,必要に応じてカンマ区切りなどに変更することもできる.

mysql> SELECT * FROM city INTO OUTFILE S3 's3://xxx/city2'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n';

S3 にファイルを出力できた.

$ aws s3 ls xxx
2017-10-02 22:34:29     143565 city.part_00000
2017-10-02 22:38:34     143565 city2.part_00000

ファイルもカンマ区切りになっていた.

$ head -n 10 ~/city2.part_00000
1,Kabul,AFG,Kabol,1780000
2,Qandahar,AFG,Qandahar,237500
3,Herat,AFG,Herat,186800
4,Mazar-e-Sharif,AFG,Balkh,127800
5,Amsterdam,NLD,Noord-Holland,731200
6,Rotterdam,NLD,Zuid-Holland,593321
7,Haag,NLD,Zuid-Holland,440900
8,Utrecht,NLD,Utrecht,234323
9,Eindhoven,NLD,Noord-Brabant,201843
10,Tilburg,NLD,Noord-Brabant,193238

マニフェストファイル

MANIFEST ON を指定すると .manifest というファイルも合わせて生成される.LOAD DATA FROM S3 コマンドを使って S3 から Aurora にデータをロードする場合に必要になる.なお,出力されるファイルの閾値は 6GB なので,巨大なテーブルを出力する以外は,基本的には1ファイルになると思う.

mysql> SELECT * FROM city INTO OUTFILE S3 's3://xxx/city3'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    MANIFEST ON;

S3 にファイルを出力できた.

$ aws s3 ls xxx
2017-10-02 22:34:29     143565 city.part_00000
2017-10-02 22:38:34     143565 city2.part_00000
2017-10-02 22:39:46        114 city3.manifest
2017-10-02 22:39:46     143565 city3.part_00000

マニフェストファイルは以下のような構造になっていた.

$ cat ~/city3.manifest
{
   "entries" : [
      {
         "url" : "s3://xxx/city3.part_00000"
      }
   ]
}

参考情報

SELECT INTO OUTFILE S3 を使うときに気になる secure_file_priv の値を Aurora で確認した.

mysql> SELECT @@aurora_version;
+------------------+
| @@aurora_version |
+------------------+
| 1.14.1           |
+------------------+
1 row in set (0.01 sec)

mysql> SELECT @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /tmp/              |
+--------------------+
1 row in set (0.00 sec)

まとめ

  • Aurora v1.13 から SELECT INTO OUTFILE S3 が使えるようになった
  • Aurora クラスターに IAM ロールをアタッチして,aurora_select_into_s3_role パラメータを修正する必要がある
  • データ抽出が必要なときに「Aurora → S3 に出力できる」と知っておくと良さそう