こんにちは。インサイトテクノロジーの松尾です！
11月になりました。先日、札幌市内では積もりはしませんでしたが初雪が降りましたね。
さて、今回のブログでは、Oracle から Amazon Redshift への移行へ Insight Database Testing を活用する例をご紹介します。
移行については、AWS社のページ Oracle から Amazon Redshift に移行する に記載があります。
ご使用の Oracle データウェアハウスを Amazon Redshift へ移行することで、クエリーとデータ読み込みパフォーマンスの大幅な改善、スケーラビリティの向上、コストの削減を実現できます。Amazon Redshift は高速で完全マネージド型、ペタバイト規模のデータウェアハウスです。お客様は、シンプルかつ費用対効果の高い方法で、すべてのデータを既存のビジネスインテリジェンスツールで分析できます。AWS Database Migration Service と AWS Schema Conversion Tool により、オンプレミスと AWS の Oracle データウェアハウスから、スキーマとデータを Amazon Redshift へ、データソースとして信頼しているアプリケーションへの中断なく、移行するのを容易にします。
移行の手順としては、Migrating an Amazon RDS for Oracle Database to Amazon Redshift の Step-by-Step Migration に従います。
Insight Database Testingを使うとできること
Insight Database Testingを使うことで、元々Oracleで使用していたSQLが、Amazon Redshiftでも、
- そのまま実行できるか？
- 返す結果は同じか？
- 性能劣化はないか？
の確認に利用することができます。
さて、それでは手順に従って試してみましょう。ブログで記述が足りてない部分は必要に応じてAWSのページを参照してください。
Oracle からAmazon Redshiftへの移行
Step 1: CloudFormationテンプレートを使ってRDSを起動 (Step 1: Launch the RDS Instances in a VPC by Using the AWS CloudFormation Template)
リンク先のAWS社のページから、ファイル一式(dms-sbs-RDSOracle2Redshift.zip)をダウンロードし、解凍します。以下の2ファイルが解凍されます。
Oraclesalesstarschema.sqlOracle_Redshift_For_DMSDemo.template
スタックを作成するにあたって、RDS for Oracleのバージョンとインスタンスタイプを変更します。
- 12.1.0.2.v4 → 19.0.0.0.ru-2022-10.rur-2022-10.r1 (※有効なバージョンへ)
- db.m4.large → db.m5.large
- db.t2.medium → db.t3.medium
作成する前にパラメータを入力しますが、後に Insight Database Testing での評価を行うために、OracleとRedshiftのデータベースユーザー名とパスワードは合わせてください。
- dbadmin / Insight123 など
作成が終了すると以下のように「出力」タブに必要な情報が表示されます。
Step 2: SQLツールとSCTをクライアントにインストール (Step 2: Install the SQL Tools and AWS Schema Conversion Tool on Your Local Computer)
クエリーを実行するのに、何かしらのSQLクライアントが必要です。AWSのサイトの手順では「SQL Workbench/J」を入れてますが、私はすでにdbeaverが入ってるのでそれを使います。お気に入りのSQL Clientを使用すればよいでしょう。
dbeaverの場合は、ドライバーは必要に応じてダウンロードしてくれるので、この手順に記載されているような、手動でのドライバー設定は不要です。
AWS SCTについてはAWS SCT のインストール、検証、更新からインストールします。
jdbcドライバーについてもOracleとAmazon Redshiftについて、ダウンロードして設定してください。
Step 3: Oracleに接続してサンプルスキーマを用意 (Step 3: Test Connectivity to the Oracle DB Instance and Create the Sample Schema)
Oracleに接続してSQLを実行し、サンプルスキーマを用意しましょう。ここではAWS社の手順ではSQL Workbench/Jを使用していますがdbeaver(22.2.5)を使用しています。
SQL Clientで接続します。dbeaverの場合はOracleを選択して、Host/Database/ユーザー名/パスワードあたりを設定します。
SQLエディタを開き、Oraclesalesstarschema.sql の内容を貼り付けます。
このSQLファイルは以下の修正が必要です。
REM INSERTING into ～のように記載されているコメント行をコメントアウトしますSET DEFINE OFF;の行をコメントアウトします
これらは、組になっていて、5箇所あります。
「スクリプトを実行」からクエリーを実行します。
日本語環境だと日付関連のエラーが出る場合があります。その場合はdbeaverのOracleの接続設定の、Oracle PropertysのLanguageをAMERICANにすると、私の環境では実行できました。
サンプルクエリーを実行してみると、同じ結果が返ってきました。
Select OBJECT_TYPE, COUNT(*) from dba_OBJECTS where owner='SH'
GROUP BY OBJECT_TYPE;
同様に、サンプルクエリーを実行します。後者はAWSの説明の半分のサイズになってますが、気にしないでおきます。
Select table_name, num_rows from dba_tables where owner='SH' order by 1;
Select b.channel_desc,count(*) from SH.SALES a,SH.CHANNELS b where a.channel_id=b.channel_id
group by b.channel_desc
order by 1;
Step 4: Redshiftへの接続を確認 (Step 4: Test the Connectivity to the Amazon Redshift Database)
次はRedshiftへの接続を確認します。
Oracleの場合と同様、Redshiftを選択して、Host/Database/ユーザー名/パスワードあたりを設定します。
テスト接続を実行すると接続を確認でき、簡単なクエリーの実行を確認します。
Step 5: SCTでスキーマを移行 (Step 5: Use AWS SCT to Convert the Oracle Schema to Amazon Redshift)
今度はSCTを使って、OracleのスキーマをRedshiftへ移行してみましょう。
プロジェクトを作成したあと「Add source」からOracleを追加します。
次に「Add target」からRedshiftを追加します。
shスキーマに対して「Create mapping」からMappingルールを作成します。
Main viewでshスキーマを選択し、「Convert schema」を実行します。
Main viewでRedshift側にできたshスキーマを選択し、「Apply to database」を実行します。ここでAWSプロファイルが登録されてない場合は作成するようメッセージが出ます。おそらく設定しないと先に進まなそうです。
処理が終了したら、「Refresh from database」を実行して問題がないことを確認しておきます。
Step 6: スキーマを移行の検証 (Step 6: Validate the Schema Conversion)
いくつかのクエリーを実行して、スキーマが想定通りに移行されていることを確認します。
SELECT 'TABLE' AS OBJECT_TYPE,
TABLE_NAME AS OBJECT_NAME,
TABLE_SCHEMA AS OBJECT_SCHEMA
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECT_SCHEMA = 'sh';
set search_path to '$user', 'public', 'sh';
SELECT tablename,
"column",
TYPE,
encoding,
distkey,
sortkey,
"notnull"
FROM pg_table_def
WHERE (distkey = TRUE OR sortkey <> 0);
Step 7: DMSレプリケーションインスタンスの作成 (Step 7: Create an AWS DMS Replication Instance)
DMSレプリケーションインスタンスを作成します。ドキュメント通りに簡単に作成できますので、手順は割愛します。
Step 8: DMSエンドポイントの作成 (Step 8: Create AWS DMS Source and Target Endpoints)
DMSエンドポイント(ソース、ターゲット)を作成します。ドキュメント通りに簡単に作成できますので、手順は割愛します。
Step 9: DMSマイグレーションタスクの作成と実行 (Step 9: Create and Run Your AWS DMS Migration Task)
続いてマイグレーションタスクを作成して実行します。ドキュメント通りに簡単に作成できますので、手順は割愛します。
マイグレーションタスクを実行すると以下のように表示され、Redshiftへデータがコピーされます。
Step 10: データマイグレーションが実行されたことの確認 (Step 10: Verify That Your Data Migration Completed Successfully)
Oracleでの確認と同様に、サンプルクエリーを実行します。
select "table", tbl_rows
from svv_table_info
where
SCHEMA = 'sh'
order by 1;
ここまでで、Redshiftにスキーマ(テーブル)とデータを用意することができました。
Insight Database Testingでのテスト
さて、やっとここからが、本題のInsight Database Testingによるテストになりますよ♪
Insight Database TestingはDB移行時の移行アセスメントやSQL改修のテストに活用可能なソリューションです。現行DBで実行されているSQLやバインド変数の収集と、収集されたSQLのテスト機能を提供しており、現在使っているSQLの修正の必要性について簡単に見積もることが可能です。詳細については、製品ページやブログ、Insight Database Testing マニュアルなどを参照ください。
OracleからRedshiftへの移行へあたっても、気になるのはSQLの互換性やパフォーマンスなど、同じクエリーに対する挙動の違いではないでしょうか？BIツールなどから接続している場合は、ツールがRedshiftに対応していれば問題はないと思います。一方で、自社の内製のシステムなどからOracleに接続していた場合、アプリケーション側で修正が必要になる場合があります。では、さっそく実施していきましょう。
とても大まかな構成とせいては以下のようなイメージです。左側のRDS for Oracleは現行環境のイメージ。また、アセスメントの際にはテスト用に現行環境相当のものを用意します(右下のターゲットDBのみでのアセスメントも可能)。
Step 11: ターゲットDBの用意
ターゲットDBとは、テスト用のDBのことです。
まず、テスト対象のDBを登録します。テスト環境であれば、移行元のRDS for Oracleをテストに使用することも可能です。ｈｙ設定によってはテストの実行によりデータが更新されます。
RDS for Oracleを、ターゲットDBとして登録します。ホスト名はRDSのエンドポイントを指定します。詳細は6.1.1.1. Oracle Database – Insight Database Testing マニュアルを参照ください。
Redshiftを、ターゲットDBとして登録します。RedshiftはPostgreSQLとして登録します。ホスト名はRedshiftのエンドポイントのポートとDB名を外したものを指定します。
Step 12: 評価SQLセットの用意
評価SQLセットは、テスト対象のSQLの集合です。通常は、テスト対象のSQLは大量となりますので、Insight Database Testingの仕組みでSQLを収集します。一方、事前にSQLが手元にある場合などは、CSVファイルを用意することでテスト対象として取り込むことができます。
今回はSQL4本についてのCSVファイルを用意し(CSVファイルのフォーマットの説明はこちら)、以下にSQLの例を掲載します。その他のSQLについては、CSVファイルを直接確認するか、実際にこのブログの手順を実行すると確認できます。
以下はSQLの例です。
SELECT p.prod_name, c.cust_gender, TO_CHAR(s.time_id, 'D') AS DAY_OF_WEEK,
SUM(s.QUANTITY_SOLD) AS QUANTITY_SOLD, SUM(s.amount_sold) AS TOTAL_SOLD
FROM sh.sales s, sh.products p, sh.customers c
WHERE p.prod_id = s.prod_id
AND c.cust_id = s.cust_id
-- AND p.prod_name = 'Deluxe Mouse'
-- AND c.cust_gender = 'F'
AND TO_CHAR(s.time_id, 'D') = '1'
GROUP BY p.prod_name, c.cust_gender, TO_CHAR(s.time_id, 'D');
評価SQLセットの新規作成で、CSVファイルをアップロードして作成します。
作成すると、以下のようにSQLを確認することができます。
Step 13: アセスメントの実行
それではアセスメントを実行しましょう。
アセスメントの新規作成、から必要な情報を入力します。
アセスメントオプションの詳細については6.7.1. アセスメントの新規作成 – Insight Database Testing マニュアルをご確認ください。
「テスト用ソースDBにもSQLを実行します」を有効にし、テスト用ソースDBにRDS for Oracleを設定し、ターゲットDBにはRedshiftを指定します。
OracleとRedshiftを比較するので、カラム名の比較をしないようなオプションを設定します。
オプションを設定したら、アセスメントを実行します！(SQLが少ないのですぐ終了します)
実行後、アセスメント結果を確認することができます。
アセスメントサマリー画面では、処理したSQLうち、どれくらいのSQLがエラーになったのか、などを一目で確認できるよう構成されています。さらにドリルダウンすることで、詳細を確認することが可能です。
例えば、エラーとなったSQLを確認してみます。実際にSQLを実行した際のエラーの内容が表示されているので、その内容をもとにエラーを修正することが可能です。アセスメント詳細画面では、実際にSQLの修正を行い、再実行することも可能です。
このエラーになったSQLについては、カラムの別名 year が予約語なので、as year と変更してあげると、正常に実行可能です。
また、パフォーマンスが劣化したSQLについては、実行計画を確認することも可能です。
Redshiftへの移行という観点だと、パフォーマンスが気になるところかもしれません。
今回はサンプルデータでデータ量もたいして多くありませんでしたが、想定しているデータ量、想定しているインスタンスサイズなどにして、同じクエリーを実行した時に返ってくる結果は同じか？実行時間がどのくらい改善するか？Insight Database Testingを用いることで簡単に確認することができます。
補足
なお、今回作成した環境の削除については (Step 11: Delete Walkthrough Resources) をご確認ください。基本的には、DMS関連のリソースと、クラウドフォーメーションスタックの削除になります。
現行のInsight Database Testingでは、ターゲットDBを1つのみ指定するアセスメントでは、Redshiftへのクエリーの実行を行うことができません。”テスト用ソースDB”を用意できない場合は、テスト用ソースDBの指定もRedshiftにすることで、クエリーの実行可否確認は行うことが可能です。詳細はお問い合わせください。
おわりに
いかがでしたか？
本ブログでは、OracleからRedsfhitへの移行を想定した時に、Insight Database Testingを使ってクエリの互換性や性能変化を確認する方法をご紹介しました。
Insight Database Testing をまだご利用いただいていない方で実際に試されたい場合は、製品の説明やデモ、トライアルなどについて
Insight Database Testingに関するお問い合わせ よりお問い合わせいただければと思います。
次回もどうぞお楽しみに！
