Hatena::ブログ(Diary)

ablog このページをアンテナに追加 RSSフィード Twitter

2017-09-10

Big Data Architecture Pattern (Polyglot/Lambda/Kappa)

仕事の都合で残念ながら聞けなかったが、db tech showcase 2017 での諸橋さんのセッション「polyglot data acces」の内容を polyglot data access - JPOUG in 15 minutes at db tech showcase Tokyo 2017 - wmo6hash::blog で拝見した。とても勉強になる内容だったのでメモ。

f:id:yohei-a:20170911012430p:image

An Enterprise Architect’s Guide to Big Data Reference Architecture Overview より


Big Data Architecture Patterns

Polyglot
  • 透過的にデータベースから外部のデータソースにアクセスする構成
Lambda
Kappa
  • LinkedIn(当時) の Jay 氏が挙げたアーキテクチャ*2
  • Lambda Architectureの複雑性に対して問題を提起
  • ストリーム処理システムでバッチと同様の 精度を保証する対処をして構成をシンプル化 した構成

今回の dbts で佐藤さんが紹介していた Apache Kudu*3 や Big Query の Stream Insert は Lambda アーキテクチャのような複雑が不要でシンプルだと思う。

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

はじめに

Oracle Database の PL/SQLMySQL への移行方法を説明します(MySQL 5.0 からストアドプロシージャに対応している)。

AWS Schema Conversion Tool (SCT) という GUI デスクトップアプリケーション(Windwos/Mac OS X/Linux版)をインストールし、移行元の Oracle Database に接続して自動的にPL/SQLMySQL のルーチンに変換することができます。

SCT による変換方法は Oracle Database を Amazon Aurora に移行する方法 | Amazon Web Services ブログ をご覧ください。


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

本題に入る前に Oracle Database からの移行先として MySQLPostgreSQL、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 ? awslabs/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

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

CREATE FUNCTION Statement

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

PL/SQL Statements

#OracleMySQL
1variable datatype := value Variable declaration DECLARE variable datatype DEFAULT value
2variable := value Assignment statement SET variable = value
3CURSOR cur (params) IS SELECT Cursor declaration DECLARE cur CURSOR FOR SELECT
4Variable and cursor declarations can be mixed in any order Variable declarations must be before cursor and handlers
5FOR rec IN cursor LOOP Cursor loop OPEN cursor WHILE-FETCH-CLOSE
6IF THEN ELSIF ELSE END IF IF statement IF THEN ELSEIF ELSE END IF
7WHILE condition LOOP sql END LOOP A loop statement WHILE condition DO sql END WHILE
8EXIT WHEN condition Exit from a loop IF condition THEN LEAVE label END IF
  • EXCEPTION block:
#Oracle MySQL
1BEGIN stmts EXCEPTION … END Exception block structure BEGIN DECLARE HANDLER … stmts END
2WHEN DUP_VAL_ON_INDEX Duplicate key DECLARE EXIT HANDLER FOR SQLSTATE '23000'
3WHEN NO_DATA_FOUND No rows found DECLARE EXIT HANDLER FOR NOT FOUND
4WHEN 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
$ 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 でソースとターゲットを指定して、変換する。

補足


参考

Amazon RDS for Oracle で起動しているプロセスを確認する

Amazon RDS for Oracle で起動しているプロセスは V$PROCESS で確認できる。*4

$ export _JAVA_OPTIONS="-Duser.language=en -Duser.country=US"
$ sql awsuser@orcl.******.ap-northeast-1.rds.amazonaws.com:1521/orcl
Picked up _JAVA_OPTIONS: -Duser.language=en -Duser.country=US

SQLcl: Release 4.2.0 Production on Sun Sep 10 12:27:40 2017

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


	New version: 4.1.0 available to download

Password? (**********?) *************
Last Successful login time: Sun Sep 10 2017 12:27:49 +09:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> set sqlformat ansiconsole
SQL> select * from v$process;
ADDR              PID  SOSID  SPID   STID   EXECUTION_TYPE  PNAME  USERNAME  SERIAL#  TERMINAL  PROGRAM                     TRACEID  TRACEFILE                                                        BACKGROUND  LATCHWAIT  LATCHSPIN  PGA_USED_MEM  PGA_ALLOC_MEM  PGA_FREEABLE_MEM  PGA_MAX_MEM  CON_ID
0000000344B0E280  1                         NONE                             0                  PSEUDO                               /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_ora_0.trc                                         0             0              0                 0            0
0000000344B0F028  2    12011  12011  12011  PROCESS         PMON   rdsdb     1        UNKNOWN   oracle@ip-10-7-2-38 (PMON)           /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_pmon_12011.trc  1                                 797036        974508         0                 974508       0
0000000344B0FDD0  3    12013  12013  12013  PROCESS         PSP0   rdsdb     1        UNKNOWN   oracle@ip-10-7-2-38 (PSP0)           /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_psp0_12013.trc  1                                 772748        949716         0                 949716       0
0000000344B10B78  4    12015  12015  12015  PROCESS         VKTM   rdsdb     1        UNKNOWN   oracle@ip-10-7-2-38 (VKTM)           /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_vktm_12015.trc  1                                 769508        949716         0                 949716       0
0000000344B11920  5    12019  12019  12019  PROCESS         GEN0   rdsdb     1        UNKNOWN   oracle@ip-10-7-2-38 (GEN0)           /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace/ORCL_gen0_12019.trc  1                                 778644        958084         0                 958084       0
(以下略)

参考