DMSでリレーショナルデータベースからOSSにデータを定期的にバックアップする -

データ管理サービス (DMS) は、データ操作、データセキュリティ管理、およびデータ開発のためにAlibaba Cloudが提供するwebサービスプラットフォームです。 DMSはデータベースクライアントとして機能し、MySQL、PostgreSQL、SQL Server、Oracle、Redis、MongoDBなどの複数のデータベースエンジンをサポートします。 DMSは、登録データベースの統合権限管理と安定した変更をサポートします。また、データウェアハウスの開発やタスクオーケストレーションなどのデータ開発機能も統合されています。

背景情報

DMSベースのデータ開発のシナリオ

DMSが提供するデータ開発機能は、次のようなさまざまなシナリオで使用できます。

手順

オフラインデータ: T + 1ベースでオフラインデータのレポートを生成します。
リアルタイムデータ: 過去5分間のデータのレポートを生成します。
インテリジェントデータ: AIコンピューティングなどのコンピューティングモデルに接続します。
コールドデータ: コールドデータを定期的にObject Storage Service (OSS) にバックアップします。
トランザクションデータ: 大量のデータを定期的に削除または更新します。

結果

このトピックでは、DMSが提供するデータ開発機能を使用して、コールドデータと期限切れデータをOSSに定期的にバックアップする方法について説明します。

DMSでコールドデータと期限切れデータをバックアップする

ApsaraDB for RDSは、安定した運用、高いパフォーマンス、および高い費用対効果を確保するために、設定されたライフサイクルに基づいてコールドデータと期限切れデータを削除します。ただし、コールドデータと期限切れデータは、履歴レコードのクエリや長期データマイニングなどのシナリオで必要になる可能性があるため、直接削除することはできません。コストを削減するには、これらのデータをよりコスト効率の高いストレージサービスに移行する必要があります。 Alibaba Cloudが提供するData Lake Analytics (DLA) では、SQLのような構文を使用して、ApsaraDB for RDSから履歴データとコールドデータを読み取り、OSSにデータを書き込むことができます。また、DLAを介してOSSを管理することもできます。したがって、DMSコンソールのDLAを使用して、ApsaraDB for RDSからOSSにデータを簡単にバックアップできます。さらに、DMSが提供するタスクオーケストレーション機能により、DLAを介してコールドデータをOSSに定期的にバックアップできます。

ケースの背景

このトピックでは、ApsaraDB for RDSインスタンスのordersという名前のテーブルと、DLAインスタンスのdemo_schemaという名前のデータベースを含む特定のユースケースについて説明します。このユースケースでは、注文テーブルのデータは日単位でdemo_schemaデータベースにバックアップされます。注文テーブルの前日のデータは、毎日05:00にバックアップされます。次のSQL文を実行して、注文テーブルを作成できます。

create table orders(
  order_id bigint,
  product_name varchar(32),
  price double,
  total_amount double,
  created_date date
);

DLAでdemo_schemaデータベースを作成するには、次のSQL文を実行します。

CREATE DATABASE demo_schema
WITH DBPROPERTIES (
    catalog = 'oss',
    location = 'oss://xxxxxx/dla_demo/'
)

準備

SQLConsoleで次のSQL文を実行して、注文テーブルにデータを挿入します。

insert into orders values(1, 'product1', 1.0, 10.9, date_add(curdate(), interval -1 day));
insert into orders values(2, 'product1', 2.0, 20.9, date_add(curdate(), interval -1 day));
insert into orders values(3, 'product1', 3.0, 30.9, date_add(curdate(), interval -1 day));
insert into orders values(4, 'product1', 4.0, 40.9, date_add(curdate(), interval -1 day));
insert into orders values(5, 'product1', 5.0, 50.9, curdate());
insert into orders values(6, 'product1', 6.0, 60.9, curdate());
insert into orders values(7, 'product1', 7.0, 70.9, curdate());
insert into orders values(8, 'product1', 8.0, 80.9, curdate());

タスクフローの実装

このセクションでは、ApsaraDB for RDSからOSSに履歴データをバックアップする方法について説明します。

タスクフローの作成

DMSコンソールの上部のナビゲーションバーで、[Data Factory]> [Task orchestration] を選択します。 [タスクオーケストレーションページ] で、[無料オーケストレーションタスク] セクションの [新しいタスクフロー] をクリックします。表示されるダイアログボックスで、[タスクフロー名] フィールドにrds_data_to_ossと入力し、[OK] をクリックします。

DLA-SQLタスクノードの作成

表示されるrds_data_to_ossページで、次の3つのDLA-SQLタスクノードを順番に作成します。

ソースにマッピングするためのスキーマを作成する: このタスクノードは、ApsaraDB for RDSインスタンスにマッピングするためのスキーマをDLAに作成します。
OSSでバックアップテーブルを作成する: このタスクノードは、DLAを介してOSSにテーブルを作成し、ApsaraDB for RDSインスタンスの履歴データを保存します。
データのバックアップ: このタスクノードは、ApsaraDB for RDSインスタンスから前日のデータをバックアップテーブルにバックアップします。

ソースタスクノードにマッピングするためのスキーマの作成

DLA-SQLのタスクノードをキャンバスにドラッグし、タスクノードをクリックします。 [ノード名] タブで、[タスク名] を [ソースにマッピングするスキーマの作成] に設定します。 [コンテンツ] タブで、ApsaraDB for RDSインスタンスにマップするスキーマdla_mysql_rdsを作成するためのSQLスクリプトを記述します。このタスクノードのターゲットデータベースは、DLAインスタンスのdemo_schemaです。 dla_mysql_rdsスキーマを作成することで、ApsaraDB for RDSインスタンスから直接DLAでデータを読み取ることができます。

DBPROPERTIESを使用してdla_mysql_rdsが存在しない場合は

CREATE SCHEMA if not exists dla_mysql_rds WITH DBPROPERTIES (
   CATALOG = 'mysql', 
   LOCATION = 'jdbc:mysql://xxxxxx.rds.aliyuncs.com:3306/dmstest',
   USER = 'dmstest',
   PASSWORD = 'xxxxxxxxx',
   INSTANCE_ID = 'xxxxxxxxx',
   VPC_ID = 'xxxxxxxxx'
 );

 msck repair database dla_mysql_rds;

ApsaraDB for RDSインスタンスにマップするスキーマを作成する前に、CIDRブロック100.104.0.0/16をApsaraDB for RDSインスタンスにアクセスするためのホワイトリストに追加する必要があります。ApsaraDB for RDSインスタンスはVirtual Private Cloud (VPC) にあり、デフォルトではDLAはVPC内のリソースにアクセスできません。 DLAからApsaraDB for RDSインスタンスへのアクセスを有効にするには、リバースVPCテクノロジーを使用する必要があります。つまり、ApsaraDB for RDSインスタンスにアクセスするためのCIDRブロック100.104.0.0/16をホワイトリストに追加します。

OSSタスクノードでバックアップテーブルを作成する

DLA-SQLのタスクノードをキャンバスにドラッグし、タスクノードをクリックします。 [ノード名] タブで、[タスク名] を [OSSでバックアップテーブルを作成] に設定します。 [コンテンツ] タブで、DLAを介してOSSにoss_ordersという名前のバックアップテーブルを作成するためのSQLスクリプトを記述します。 oss_ordersテーブルは、注文テーブルから移行されたデータをApsaraDB for RDSインスタンスに格納するために使用されます。注文テーブルと同じスキーマを共有し、年、月、日ごとに分割されます。

CREATE EXTERNAL TABLE oss_orders (
    order_id bigint,
    product_name varchar(32),
    price double,
    total_amount double,
    created_date date)
PARTITIONED BY (y string, m string, d string)
STORED AS TEXTFILE
LOCATION 'oss://xxxxxx/dla_demo/';

データのバックアップタスクノードの作成

DLA-SQLのタスクノードをキャンバスにドラッグし、タスクノードをクリックします。 [ノード名] タブで、[タスク名] を [データのバックアップ] に設定します。 [変数設定] タブで、データのバックアップに使用する時間変数をSQL文で定義します。このタスクノードのターゲットデータベースは、DLAインスタンスのdemo_schemaです。

時間変数の定義

この例では、次の3つの時間変数を定義します。

年: yyyyの形式で、前日の年。
月: 前日の月、mmの形式。
day: 前日の日付 (dd形式) 。

データバックアップの実装

データバックアップを実装するには、次の手順に従います。

DLAを使用してOSSに一時テーブルを作成OSSのoss_ordersテーブルのディレクトリにoss_orders_tmpという名前の一時テーブルを作成します。ディレクトリの名前には、年、月、日を含める必要があります。一時テーブルは自動的にoss_ordersテーブルのパーティションになります。一時テーブルはoss_ordersテーブルと同じスキーマを共有します。一時テーブルの特定の場所は現在の日付によって異なります。
日単位のデータのバックアップINSERT INTO SELECTステートメントを実行して、dla_mysql_rdsのordersテーブルからデータを読み取り、OSSの一時テーブルにデータを書き込みます。
パーティション情報の更新と一時テーブルの削除oss_ordersテーブルのメタデータ情報の更新。一時テーブルのデータをoss_ordersテーブルにロードします。一時テーブルを削除します。

データをバックアップするためのSQLスクリプト

/* Create a temporary table */
CREATE EXTERNAL TABLE oss_orders_tmp (
    order_id bigint,
    product_name varchar(32),
    price double,
    total_amount double,
    created_date date) 
STORED AS TEXTFILE 
LOCATION 'oss://xxxxxx/dla_demo/y=${year}/m=${month}/d=${day}' 
TBLPROPERTIES('auto.create.location'= 'true');

/* Back up data by day */
insert into oss_orders_tmp
SELECT * FROM mysql_rds_to_oss.orders 
where DATE_FORMAT(created_date, '%Y') = '${year}' and 
      DATE_FORMAT(created_date, '%m') = '${month}' and 
      DATE_FORMAT(created_date, '%d') = '${day}';

/* Update the partition information of the backup table and delete the temporary table */
msck repair table oss_orders;
drop table oss_orders_tmp;

タスクフローの実行

上記のタスクノードを設定したら、左上隅の [Try Run] をクリックしてタスクフローを実行します。 SQLConsoleに移動し、DLAを介してOSSで作成されたoss_ordersテーブルを照会します。

スケジューリングポリシーの設定

[Try Run] をクリックすると、タスクフローが正しく設定されているかどうかが確認されます。タスクフローが正常に実行されたら、空白の領域をクリックします。表示される [スケジューリング] タブで、毎朝05:00にタスクフローを実行するように設定します。

考慮事項

タスクフローを作成する前に、ApsaraDB for RDSインスタンスにordersという名前のテーブルを作成し、DLAインスタンスにdemo_schemaという名前のデータベースを作成する必要があります。
注文テーブルの1つ以上のフィールドに、データの行が挿入された時刻が記録されていることを確認します。
ApsaraDB for RDSインスタンスがSecure Collaborationモードで管理されている場合、注文テーブルにデータを挿入するには、テーブルを変更し、SQL文をSQLConsoleで実行できるようにセキュリティルールを変更する権限を申請する必要があります。
ApsaraDB for RDSおよびDLAインスタンスが両方ともSecure Collaborationモードで管理されている場合、上記のタスクノード用に作成されたSQLスクリプトを実行するには、権限を申請し、スクリプトを実行できるようにセキュリティルールを変更する必要があります。

概要

このトピックでは、特定のユースケースで、DMSでDLAを使用して、ApsaraDB for RDSからOSSにコールドデータと期限切れデータを定期的にバックアップする方法について詳しく説明します。このユースケースでは、ApsaraDB for RDSの安定した運用と高いパフォーマンスが効果的に保証され、データストレージのコストが削減されます。このユースケースは、コールドデータと期限切れデータをバックアップするためにDMSが提供する強力な機能を示しています。

コールドデータと期限切れデータのバックアップを除き、DMSが提供するデータ開発機能は他のシナリオでも使用できます。詳細については、「DMSドキュメント」をご参照ください。