Amazon Web Services ブログ

Amazon Aurora MySQL から Amazon S3 にデータをエクスポートおよびインポートするためのベストプラクティス

複雑なアプリケーションを小さな部分に分割することにより、多数の専用データベースを使用して高度に分散したアプリケーションを構築できます。これにより、ジョブに適したデータベースを選択できます。Amazon Aurora は、OLTP ワークロードの推奨される選択肢です。Aurora により、クラウド内でリレーショナルデータベースをセットアップ、操作、スケーリングするのが容易になります。

この記事では、Aurora MySQL から Amazon Simple Storage Service (Amazon S3) にデータをエクスポートおよびインポートする方法を示し、関連するベストプラクティスについて説明します。Amazon Aurora PostgreSQL では、データを Amazon S3 機能にエクスポートおよびインポートすることもできます。このブログでは、Amazon Aurora MySQL に焦点を当てます。

Aurora と Amazon S3 の概要

Aurora は、クラウド向けに構築された MySQL と PostgreSQL の互換性があるリレーショナルデータベースで、従来のエンタープライズデータベースのパフォーマンスと可用性、およびオープンソースデータベースのシンプルさと費用対効果を兼ね備えています。

Amazon S3 は、業界をリードするスケーラビリティ、データの可用性、セキュリティ、およびパフォーマンスを提供するオブジェクトストレージサービスです。つまり、どの規模と業界の顧客もこれを使用して、任意の量のデータを保存および保護できます。

前提条件

始める前に、以下を完了してください。

  1. Aurora MySQL DB クラスターを起動します。既存のクラスターを使用することもできます。
  2. MySQL クライアントをインストールした Amazon EC2 インスタンスを起動します。ここでは、MySQL Workbench を使用することもできます。
  3. 次の必須の Identity and Access Management (IAM) ポリシーとロールを作成します。
    • 次のコードで、リソースへの制限が最も少ない権限で IAM ポリシーを作成し、aurora-s3-access-pol という名前を付けます。ポリシーには、ファイルが保存されている S3 バケット (この記事では sample-loaddata01) へのアクセス権限が必要です。 
      {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                   "s3:AbortMultipartUpload",
                   "s3:DeleteObject",
                   "s3:ListMultipartUploadParts",
                   "s3:PutObject",
                   "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::sample-loaddata01/*",
                "arn:aws:s3:::sample-loaddata01"
            ]
        }
    ]
}
    • IAM ロールを作成し、次のコードに従って信頼関係を変更します。AssumeRole により、Aurora はユーザーに代わってその他の AWS のサービスにアクセスできます。 
      {
"Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "rds.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}
    • 前のステップのポリシー aurora-s3-access-pol をアタッチします。この記事では、ロールに s3-demo-access-role という名前を付けます。ロール ARN は arn:aws:iam::123456789012:role/aurora-s3-access-role です。
  1. クラスターのパラメータグループを作成して割り当てます。
    • AWS コマンドラインインターフェイス (AWS CLI) を使用して、カスタムパラメータグループを作成します。次のコードを参照してください。 
      aws rds create-db-cluster-parameter-group \
--db-cluster-parameter-group-name aurora-cluster-para-grp \
--db-parameter-group-family aurora5.6 \
--description "cluster parameter group for s3 copy"
    • パラメータグループを変更して、aurora_default_s3_role パラメータの値が IAM ロール s3-demo-access-role になるように編集します。次のコードを参照してください。 
      aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name aurora-cluster-para-grp \
  --parameters "ParameterName=aurora_default_s3_role,ParameterValue=arn:aws:iam::123456789012:role/aurora-s3-access-role,ApplyMethod=immediate"

      IAM ロールを aurora_select_into_s3_role パラメータにマップしてデータのエクスポートのみを許可するか、aurora_load_from_s3_role パラメータをマップして S3 バケットからのデータのインポートのみを許可できます。これら 2 つのパラメータに IAM ロールが指定されていない場合、Aurora はエクスポートとインポートの両方に aws_default_s3_role で指定された IAM ロールを使用します。

  1. Aurora クラスターを変更して、新しく作成したパラメータグループを関連付けます。次のコードを参照してください。 
    aws rds modify-db-cluster \
  --db-cluster-identifier database-1 \
  --db-cluster-parameter-group-name aurora-cluster-para-grp \
  --apply-immediately

    apply-immediately コードは変更をトリガーし、DB クラスターの PreferredMaintenanceWindow に関係なく、変更をできるだけ早く非同期に適用します。

  1. IAM ロールをクラスターに関連付けます。これにより、Aurora DB クラスターのデータベースユーザーは S3 バケットにアクセスできます。次のコードを参照してください。 
     aws rds add-role-to-db-cluster \
    --db-cluster-identifier database-1 \
    --role-arn arn:aws:iam::123456789012:role/aurora-s3-access-role
  2. クラスタのプライマリインスタンスを再起動して、変更を適用します。次のコードを参照してください。 
    aws rds reboot-db-instance \
      --db-instance-identifier database-1-instance-1

    これで、データをエクスポートおよびインポートする次のユースケースを探求する準備ができました。

Aurora MySQL から Amazon S3 へデータをエクスポートする

データをエクスポートするには、以下の手順を実行します。

  1. マスターユーザーとしてクラスターに接続します。デフォルトでは、マスターユーザーは Amazon S3 からデータをエクスポートおよびインポートする権限を持っています。この記事では、データを S3 バケットにエクスポートするために最低限必要な権限を持つテストユーザーを作成します。次のコードを参照してください。 
    $ mysql -h database-1.cluster-xxxxxxxxxxxx.us-west-2.rds.amazonaws.com -D sampledb01 -P 3306 -u admin01 -p
  1. ユーザー testuser を作成し、データベース上必要な権限をそのユーザーに付与します。この記事では sampledb01 を使用します。次のコードを参照してください。 
    MySQL> create user 'testuser'@'%' identified by 'Password';
MySQL> grant select on sampledb01.* to 'testuser'@'%';
  1. 次のコードを使用して、SELECT INTO S3 権限を testuser に付与します。 
    MySQL> grant SELECT INTO S3 on *.* to 'testuser'@'%';
  1. クラスターに testuser としてログインします。 
    $ mysql -h database-1.cluster-xxxxxxxxxxxx.us-west-2.rds.amazonaws.com -D sampledb01 -P 3306 -u testuser -p
  1. この記事では、lineitem テーブルを S3 バケットにエクスポートします。データベースの既存のテーブル (できれば小さいサイズ) を使用して、この機能をテストできます。次のコードを参照してください。 
    MySQL> describe lineitem;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| orderkey        | int(11)     | NO   |     | NULL    |       |
| linenumber      | int(11)     | NO   |     | NULL    |       |
| custkey         | int(11)     | NO   |     | NULL    |       |
| partkey         | int(11)     | NO   |     | NULL    |       |
| suppkey         | int(11)     | NO   |     | NULL    |       |
| orderdate       | int(11)     | NO   |     | NULL    |       |
| orderpriority   | varchar(15) | NO   |     | NULL    |       |
| shippriority    | varchar(1)  | NO   |     | NULL    |       |
| quantity        | int(11)     | NO   |     | NULL    |       |
| extendedprice   | int(11)     | NO   |     | NULL    |       |
| ordertotalprice | int(11)     | NO   |     | NULL    |       |
| discount        | int(11)     | NO   |     | NULL    |       |
| revenue         | int(11)     | NO   |     | NULL    |       |
| supplycost      | int(11)     | NO   |     | NULL    |       |
| tax             | int(11)     | NO   |     | NULL    |       |
| commitdate      | int(11)     | NO   |     | NULL    |       |
| lshipmode       | varchar(10) | NO   |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

    次のスクリーンショットは、lineitem テーブルのレコードを示しています。

  1. Aurora テーブルから S3 バケットにデータをエクスポートするには、SELECT INTO OUTFILE S3 を使用します。次のステートメントは、テーブル全体を S3 バケットにエクスポートします。この機能を初めて試す場合は、より大きなテーブルに LIMIT 句を使用することをご検討ください。次のコードを参照してください。 
    MySQL> SELECT * FROM lineitem INTO OUTFILE S3 
's3://sample-loaddata01/unload-data/file'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
Query OK, 50000000 rows affected (3 min 37.05 sec)

    次のコードは、S3 バケットにエクスポートされたファイルを示しています。デフォルトのファイルサイズのしきい値は 6 GB です。ステートメントによって選択されたデータはファイルサイズのしきい値よりも小さいため、単一のファイルが作成されます。

    $aws s3 ls s3://sample-loaddata01/unload-data --recursive --human-readable --summarize
2020-04-29 00:10:17    4.7 GiB unload-data/file.part_00000

    ファイルサイズを 6 GB 未満にする必要がある場合は、列を特定してテーブルデータを小さな部分に分割し、(WHERE 条件を使用して) 複数の SELECT INTO OUTFILE ステートメントを実行できます。選択したデータの量が 25 GB を超える場合には、これを行うのが最適です。

Amazon S3 から Aurora MySQL へデータをインポートする

このセクションでは、Amazon S3 ファイルから Aurora テーブルにデータをロードします。次の手順を実行します。

  1. testuser に LOAD FROM S3 権限を付与します。次のコードを参照してください。 
    MySQL> grant LOAD FROM S3 on *.* to 'testuser'@'%';
  1. LOAD DATA FROM S3 を使用して、Amazon S3 プレフィックスからデータファイルをインポートします。次のコードを参照してください。 
    MySQL > LOAD DATA FROM S3 PREFIX 's3://sample-loaddata01/unload-data/file'
INTO TABLE lineitem 
FIELDS TERMINATED BY ','   
LINES TERMINATED BY '\n'
(orderkey,linenumber,custkey,partkey,suppkey,orderdate,orderpriority,shippriority,quantity,extendedprice,ordertotalprice,discount,revenue,supplycost,tax,commitdate,shipmode);
Query OK, 50000000 rows affected ( 8 min 16 sec)
  1. ロードされたファイルを aurora_s3_load_history システムテーブルを使用して確認します。次のコードを参照してください。 
    MySQL> select * from mysql.aurora_s3_load_history order by load_timestamp desc limit 1\G;

*************************** 1. row ***********************
  load_prefix: s3://sample-loaddata01/unload-data/file
     file_name: unload-data/file.part_00000
version_number: 
  bytes_loaded: 5052058881
load_timestamp: 2020-05-05 18:09:53

ベストプラクティス

このセクションでは、Amazon S3 から Aurora MySQL データベースに大規模なデータセットを一括ロードするためのベストプラクティスについて説明します。この記事は、db.r4.4xlarge インスタンスの lineitem テーブルに 5,000 万レコードをロードする一連のテストの観察結果を基にしています (テーブル構造とサンプルレコードについては、前のセクションを参照してください)。

負荷テストは、クラスターで他のアクティブなトランザクションが実行されていない時に行われました。これは、クラスターの負荷とインスタンスタイプによって異なる場合があります。

ファイルサイズ

S3 バケット内のソースファイルが小さすぎないことを確認します。複数の小さなファイル (1～10 MB) を読み込むと、ファイル競合のオーバーヘッドが追加され、読み込みパフォーマンスに影響します。最適なパフォーマンスを得るには、100 MB～1 GB のファイルサイズをご検討ください。

次のグラフは、db.r4.4xlarge インスタンスの lineitem テーブルに 5,000 万レコードをロードする際のインポート時間とファイルサイズを示しています。小さいファイルではロードパフォーマンスが大幅に低下することは明らかですが、非常に大きいファイルでは有意な増加はありません。

パーティショニング

パーティション分割テーブルを使用している場合は、パーティションを同時にロードしてロードパフォーマンスを向上させることを検討してください。Amazon S3 からのロードは、明示的なパーティション選択をサポートし、データがロードされているパーティションのみをロックします。linenumber 列で lineitem テーブルをパーティション化し、すべてのパーティションを同時にロードすると、ロード時間が大幅に改善されます。次のコードを参照してください。

.
PARTITION BY List(linenumber) (
    PARTITION p1 VALUES IN (1),
    PARTITION p2 VALUES IN (2),
    PARTITION p3 VALUES IN (3),
    PARTITION p4 VALUES IN (4),
    PARTITION p5 VALUES IN (5),
    PARTITION p6 VALUES IN (6),
    PARTITION p7 VALUES IN (7)
);

パーティション戦略がない場合、ロード時間は約 8 分 20 秒です。list (linenumber) パーティション戦略と同時ロードを使用すると、ロード時間は約 2 分 30 秒です。

同時ロード

ロードパフォーマンスを向上させるために、複数のテーブルを同時にロードできます。ただし、同時性の程度は、クラスターで実行されている他のトランザクションに影響を与える可能性があります。ロード操作では、データの読み取り、テーブルへの挿入、変更のコミットといった CPU サイクルが必要になるため、インスタンスタイプに割り当てられた vCPU の数が重要な役割を果たします。詳細については、「Amazon RDS インスタンスタイプ」を参照してください。少ない vCPU で複数のテーブルを同時にロードすると、CPU 使用率が急上昇し、既存のワークロードに影響を与える可能性があります。詳細については、「Amazon RDS のモニタリングの概要」を参照してください。

次の Amazon CloudWatch メトリクスは、db.r4.8xlarge (32 vCPU) クラスターで 8 つの同時ロードが実行されている場合の最適な CPU 使用率を示しています。

次の Amazon RDS Performance Insights グラフは、8 つの同時ロード中に取得しました。グラフから、ほとんどの時間が CPU や I/O などの基本的なホストリソースの消費に費やされていることが分かります。DB 負荷の主な原因は待機イベント io/table/sql/handler です。これは、ストレージエンジンがテーブルに対して I/O リクエストを処理していることを示唆しています。

待機イベントの詳細については、「Aurora MySQL イベント」を参照してください。Performance Insights の使用の詳細については、「Performance Insights で Amazon RDS for MySQL をチューニングする」を参照してください。

他にも、ファイルを S3 バケットに分割し、パーティション分割されていない単一のテーブルで同時ロードを実行するオプションもあります。これにより、単一のロード操作を実行する場合と比べて、ロード時間が少し改善できます。1 つの同時セッションでは、1 億 5000 万行をインポートするためのロード時間は約 23 分 40 秒でした。3 つの同時セッションそれぞれが 5000 万レコードをインポートすると、ロード時間は約 22 分 10 秒でした。

リーダーインスタンスからエクスポートを実行する

Aurora から S3 への大量のデータのエクスポートは、集中的な操作です。ライターインスタンスからエクスポートを実行すると、クラスターで実行されている他のトランザクションに影響を与える可能性があります。これを解決するために、リーダーインスタンスを使用できます。Aurora リーダーは、読み取りトラフィックを処理し、SELECT INTO OUTFILE 操作をサポートするように設計されています。

制約

外部キーと一意キーのチェックが有効になっていると、データのインポートプロセスに時間がかかることがよくあります。これらのパラメータは、load ステートメントを実行する前に 0 に設定することにより、セッションレベルでオフにすることができます。Amazon S3 からロードされたデータの整合性が信頼でき、テーブルに他の書き込みワークロードがない場合にのみ、それを検討してください。キーチェックを無効にするには、次のコードを入力します。

SET foreign_key_checks=0;
SET unique_checks=0;

load ステートメントが完了したら、次のコードでキーチェックを有効にします。

SET foreign_key_checks=1;
SET unique_checks=1;

外部キーチェックと一意のチェックが無効になっている間は、(たとえば、ALTER TABLE を使用して) テーブル構造を変更しないでください。一般的なベストプラクティスとして、これらのパラメータを無効にする前に影響を評価しましょう。

まとめ

この記事では、Aurora MySQL と Amazon S3 の間でデータをインポートおよびエクスポートする方法を示しました。Aurora は運用分析機能を備えており、Amazon S3 との統合により、俊敏な分析環境を簡単に確立できます。データのインポートの詳細については、「Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターにデータをロードする」を参照してください。データのエクスポートの詳細については、「Amazon Aurora MySQL DB クラスターのデータを Amazon S3 バケットのテキストファイルに保存する」を参照してください。Aurora のベストプラクティスの詳細については、「Amazon Aurora MySQL のベストプラクティス」を参照してください。

 

 

著者について

 

Suresh Patnam は、AWS のシニアビッグデータアーキテクトです。彼は、ビッグデータ、データレイク、およびデータ戦略に焦点を当てて、お客様のクラウドへの移行をサポートしています。余暇には、テニスをし、家族と過ごす時間を楽しんでいます。

 

 

 

Mahesh Goyal は AWS のビッグデータのデータアーキテクトです。彼は、ビッグデータとデータウェアハウスに焦点を当てて、お客様のクラウドへの移行をサポートしています。余暇には、音楽を鑑賞し、家族と一緒に行ったことのないレストランを開拓するのを楽しんでいます。