ablog

不器用で落着きのない技術者のメモ

Oracle Database の PL/SQL を MySQL にどう移行するか

AWS

はじめに

Oracle Database の PL/SQL の MySQL への移行方法を説明します（MySQL 5.0 からストアドプロシージャに対応している）。
AWS Schema Conversion Tool (SCT) という GUI デスクトップアプリケーション（Windwos/Mac OS X/Linux版）をインストールし、移行元の Oracle Database に接続して自動的にPL/SQL を MySQL のルーチンに変換することができます。
SCT による変換方法は Oracle Database を Amazon Aurora に移行する方法 | Amazon Web Services ブログ をご覧ください。

移行先として適切なサービスを選ぶ

本題に入る前に Oracle Database からの移行先として MySQL、PostgreSQL、Redshift のどれが適切か説明します。
経験上、エンタープライズで Oracle Database を使っている場合は PostgreSQL が向いているケースが多いです。アクセスブロック数が少ないSQLのみで結合もネステッドループのみで問題ないシステムは MySQL が向いています。Exadata で OLTP（トランザクション） とOLAP（分析）を共存させている場合は PostgreSQL で OLTP、Redshift で OLAP（分析）という使い分けをオススメします。

MySQL が向いているケース
  • OLTP でシンプルでアクセスブロック数が少ないSQLが大半。
  • 結合はネステッドループのみで問題ない程度の軽い SQL のみ。
  • さらに結合も必要なく、キーのみにでアクセスするケースでは DynamoDB が向いています。
PostgreSQL が向いているケース
  • OLTP でも結合対象行数を絞れず HASH JOIN が必要な重いSQLが多い。
  • エンタープライズで Oracle Database を使っているシステムはこのケースが一番多い印象。
Redshift が向いているケース
  • Exadata で集計処理を投げていて Smart Scan を享受しているようなケースでは Redshift が向いている。
  • Exadata で OTLP（トランザクション処理）と分析の両方の用途で使っている場合は、OLTP は RDS で、分析は Redshift というように使い分けるアーキテクチャにする。

Oracle Database と MySQL のプロシージャ・ファンクションの構文の違い

SCT でプロシージャを変換した例

aws-database-migration-samples/generate_tickets.pls at master · aws-samples/aws-database-migration-samples · GitHub を変換した例です。

  • Oracle Database（変換前）
procedure generate_tickets(P_event_id IN NUMBER) as
  CURSOR event_cur(P_ID NUMBER) IS
  SELECT id,location_id
  FROM   sporting_event
  WHERE  ID = P_ID;

  standard_price NUMBER(6,2);

BEGIN
  standard_price := DBMS_RANDOM.VALUE(30,50);

  FOR event_rec IN event_cur(P_event_id) LOOP
    INSERT /*+ APPEND */ INTO sporting_event_ticket(id,sporting_event_id,sport_location_id,seat_level,seat_section,seat_row,seat,ticket_price)
    SELECT sporting_event_ticket_seq.nextval
      ,sporting_event.id
      ,seat.sport_location_id
      ,seat.seat_level
      ,seat.seat_section
      ,seat.seat_row
      ,seat.seat
      ,(CASE
         WHEN seat.seat_type = 'luxury' THEN 3*standard_price
         WHEN seat.seat_type = 'premium' THEN 2*standard_price
         WHEN seat.seat_type = 'standard' THEN standard_price
         WHEN seat.seat_type = 'sub-standard' THEN 0.8*standard_price
         WHEN seat.seat_type = 'obstructed' THEN 0.5*standard_price
         WHEN seat.seat_type = 'standing' THEN 0.5*standard_price
      END ) ticket_price
    FROM sporting_event
       ,seat
    WHERE sporting_event.location_id = seat.sport_location_id
    AND   sporting_event.id = event_rec.id;
  END LOOP;
END;
  • MySQL（変換後）
    • コメントが入っている箇所は Oracle Database の構文に相当するものが MySQL になかった箇所で、この部分は自分でワークアラウンドを記述する必要があります。
CREATE PROCEDURE DMS_SAMPLE.GENERATE_TICKETS(IN par_P_EVENT_ID DOUBLE)
BEGIN
    DECLARE var$ID DOUBLE;
    DECLARE var$LOCATION_ID DOUBLE;
    DECLARE par_P_ID DOUBLE;
    DECLARE var_standard_price DECIMAL (6, 2);
    DECLARE done INT DEFAULT FALSE;
    DECLARE event_cur CURSOR FOR SELECT
        ID,a
        FROM SPORTING_EVENT
        WHERE ID = par_P_ID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done := TRUE
    /*
    [340 - Severity CRITICAL - MySQL doesn't support the SYS.DBMS_RANDOM.VALUE(NUMBER,NUMBER) function. Create a user-defined function.]
    standard_price := DBMS_RANDOM.VALUE(30,50)
    */;
    SET par_P_ID := par_P_event_id;
    OPEN event_cur;

    read_label:
    LOOP
        FETCH event_cur INTO var$ID, var$LOCATION_ID;

        IF done THEN
            LEAVE read_label;
        END IF;
        INSERT INTO SPORTING_EVENT_TICKET
            (ID, SPORTING_EVENT_ID, SPORT_LOCATION_ID, SEAT_LEVEL, SEAT_SECTION, SEAT_ROW, SEAT, TICKET_PRICE)
            SELECT
                aws_oracle_ext.sequence$nextval('SPORTING_EVENT_TICKET_SEQ', 'DMS_SAMPLE'), SPORTING_EVENT.ID, SEAT.SPORT_LOCATION_ID, SEAT.SEAT_LEVEL, SEAT.SEAT_SECTION, SEAT.SEAT_ROW, SEAT.SEAT, (CASE WHEN SEAT.SEAT_TYPE = 'luxury' THEN 3 * var_standard_price WHEN SEAT.SEAT_TYPE = 'premium' THEN 2 * var_standard_price WHEN SEAT.SEAT_TYPE = 'standard' THEN var_standard_price WHEN SEAT.SEAT_TYPE = 'sub-standard' THEN 0.8 * var_standard_price WHEN SEAT.SEAT_TYPE = 'obstructed' THEN 0.5 * var_standard_price WHEN SEAT.SEAT_TYPE = 'standing' THEN 0.5 * var_standard_price END) AS ticket_price
                FROM SPORTING_EVENT, SEAT
                WHERE SPORTING_EVENT.LOCATION_ID = SEAT.SPORT_LOCATION_ID AND SPORTING_EVENT.ID = var$ID;
    END LOOP;
    CLOSE event_cur;
END;
比較

CREATE PROCEDURE Statement

# Oracle MySQL
1 CREATE OR REPLACE PROCEDURE DROP PROCEDURE IF EXISTS and CREATE PROCEDURE
2 param IN / OUT / IN OUT datatype Parameter definition IN / OUT / INOUT param datatype(length)
3 IS / AS Removed
4 Variable declaration is before BEGIN Variable declaration is after BEGIN
5 END sp_name END

CREATE FUNCTION Statement

  • Converting user-defined functions from Oracle to MySQL:
# Oracle MySQL
1 CREATE OR REPLACE FUNCTION DROP FUNCTION IF EXISTS and CREATE FUNCTION
2 param IN / OUT / IN OUT datatype Parameter definition param datatype(length)
3 RETURN datatype Return value RETURNS datatype(length)
4 IS / AS Removed
5 Variable declaration is before BEGIN Variable declaration is after BEGIN
6 END func_name END

PL/SQL Statements

# Oracle MySQL
1 variable datatype := value Variable declaration DECLARE variable datatype DEFAULT value
2 variable := value Assignment statement SET variable = value
3 CURSOR cur (params) IS SELECT Cursor declaration DECLARE cur CURSOR FOR SELECT
4 Variable and cursor declarations can be mixed in any order Variable declarations must be before cursor and handlers
5 FOR rec IN cursor LOOP Cursor loop OPEN cursor WHILE-FETCH-CLOSE
6 IF THEN ELSIF ELSE END IF IF statement IF THEN ELSEIF ELSE END IF
7 WHILE condition LOOP sql END LOOP A loop statement WHILE condition DO sql END WHILE
8 EXIT WHEN condition Exit from a loop IF condition THEN LEAVE label END IF
  • EXCEPTION block:
# Oracle MySQL
1 BEGIN stmts EXCEPTION … END Exception block structure BEGIN DECLARE HANDLER … stmts END
2 WHEN DUP_VAL_ON_INDEX Duplicate key DECLARE EXIT HANDLER FOR SQLSTATE '23000'
3 WHEN NO_DATA_FOUND No rows found DECLARE EXIT HANDLER FOR NOT FOUND
4 WHEN OTHERS All exceptions DECLARE EXIT HANDLER FOR SQLEXCEPTION
http://www.sqlines.com/oracle-to-mysql#plsql-statements
  • パッケージ変数は MySQL ではセション変数（@）で代用できる。
  • 結果セットの返却は MySQL では SELECT 文を書くだけ。
  • %TYPE および %ROWTYPE データ型定義は MySQL にはない。

SCTを使った変換手順例

$ git clone https://github.com/awslabs/aws-database-migration-samples.git

$ sudo rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
$ sudo rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
  • インストールされたファイルを確認する。
$ rpm -ql oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64
/usr/bin/sqlplus64
/usr/lib/oracle/12.2/client64/bin/sqlplus
/usr/lib/oracle/12.2/client64/lib/glogin.sql
/usr/lib/oracle/12.2/client64/lib/libsqlplus.so
/usr/lib/oracle/12.2/client64/lib/libsqlplusic.so
$ rpm -ql oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64
/usr/lib/oracle/12.2/client64/bin/adrci
/usr/lib/oracle/12.2/client64/bin/genezi
/usr/lib/oracle/12.2/client64/lib/libclntsh.so.12.1
/usr/lib/oracle/12.2/client64/lib/libclntshcore.so.12.1
/usr/lib/oracle/12.2/client64/lib/libipc1.so
/usr/lib/oracle/12.2/client64/lib/libmql1.so
/usr/lib/oracle/12.2/client64/lib/libnnz12.so
/usr/lib/oracle/12.2/client64/lib/libocci.so.12.1
/usr/lib/oracle/12.2/client64/lib/libociei.so
/usr/lib/oracle/12.2/client64/lib/libocijdbc12.so
/usr/lib/oracle/12.2/client64/lib/libons.so
/usr/lib/oracle/12.2/client64/lib/liboramysql12.so
/usr/lib/oracle/12.2/client64/lib/ojdbc8.jar
/usr/lib/oracle/12.2/client64/lib/xstreams.jar

$ vi ~.bashrc
export PATH=$PATH:/usr/lib/oracle/12.2/client64/bin
export NLS_LANG=American_America.UTF8
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/oracle/12.2/client64/lib
  • git をインストールする。
$ sudo yum -y install git

$ git clone https://github.com/awslabs/aws-database-migration-samples.git

$ cd aws-database-migration-samples/oracle/sampledb/v1
$ sqlplus awsuser/******@******.******.ap-northeast-1.rds.amazonaws.com:1521/ORCL
SQL> install-rds.sql
  • SCT でソースとターゲットを指定して、変換する。

補足

参考