MTU lab

強化版！MySQLへのデータ移行術（１／２）

概要

• このトピックでは、前回に引き続きデータ移行の高速化をテーマとして扱います。MTUとMySQL間にフリーのデータ変換ツールを介在させることで、前回の方法では移行できなかったデータ型への対応を補います。
• 次のデータ型を含むテーブルのデータ・コピーが可能です。
  VARCHAR2, CHAR, NVARCHAR2, NCHAR, DATE, NUMBER, LONG, LONG RAW, CLOB, NCLOB, BLOB。
  強調文字で示した型は前回のトピックでは非対応でした。
• このツールは MTU adaptor for MySQL と言い、Oracle Database 上の様々なデータ型のバリエーションを含む100万件のデータ（MTUの通常のテキスト出力で2.0GBのボリューム）を370秒前後で移行できる実用的な性能を備えています（評価版／有償版の使用、およびMySQLストレージエンジンにInnoDB使用にて）。
• MTU adaptor for MySQLは、1200行前後のc++で書き上げられた C API と呼ばれる MySQL のライブラリを利用する、コマンドライン・ユーティリティーです。MySQL が提供する次のような機能を利用して最適化を図る特長があります。
  1. MYSQL_OPT_COMPRESS: 圧縮されたクライアント/サーバプロトコルを使用。特に MySQL サーバーとの間にWAN接続がある場合でも、長期化するレイテンシは転送効率ＵＰでカバーされます。
  2. 準備されたC APIステートメント機能: mysql_stmt_prepare() 関数を使って SQL INSERT 文の列値をパラメータ化し、一度解析された文を繰り返し利用することにより、ネットワーク通信量とMySQL インスタンスが消費するCPU負荷を減らします。
  3. multiple lists of column values: INSERT 文の VALUES に続く構文要素として使用すると複数の行の値を一つの挿入文へまとめて（バルク）バインドできるので、転送効率が向上します。
• 読者はGPLの下での利用に同意することを条件に、この記事の後半にあるリンクからMTU adaptor for MySQLのソース・コードを無料でダウンロードすることができます。
• 下図にこの方法の概要を示します。
  

  図１．概要図

  MTU および MTU adaptor for MySQL の実行環境は Vista または Server 2008 以降の Windows をご用意下さい。Oracle Database と MySQL Server のOSは特に制限がありませんが、Oracle Database は 10g Release 2 以降、MySQL Server は Release 5.1 以降に対応します。

動機

• 前回のトピック「手軽で速い！MySQLへのデータ移行術」では、LONG, LONG RAW, CLOB, NCLOB, BLOB といったデータ型を扱えない制約がありました。
• またNULLを含むデータを扱う時には、かなり不自然なSQLの記述を余儀なくされました。
• このような欠点は MySQL の LOAD DATA INFILE コマンドを採用したことに原因がありました。手順をシンプルに保つことには役立ちましたが、その反面、前述のデータ型やNULL値表現にはMTUの出力仕様と合わない部分がありました。
• MTU adaptor for MySQL は、MTU-MySQL間のデータ表現の差異を柔軟に調整することが可能で、主要なOracle データ型の殆どに対応出来ます。また復帰・改行コードやエスケープ文字を含むレコードにも対応します。
• LOAD DATA INFILE からINSERTへ変更することで懸念される性能劣化は、「multiple lists of column values」等を含む様々な最適化アプローチの効果で相殺されます。

MTUのプロトコル

• MTU用の環境変数が下表通りに設定されたときの通信規約を示します。空欄はすでにある値を取り除くという意味です。この設定値はこの記事の後半にあるデモンストレーションでも使用します。

  環境変数名	出荷時初期値	書き換える値
  USERID	SYSTEM/MANAGER	SYSTEM/パスワード@DB別名
  SRC_USER	SCOTT	移行元テーブルが含まれるスキーマ名
  STDOUT	0×0000	0×0007
  BULK_SIZE	1000	20
  STREAM_LOCATOR		named_pipe://\\.\pipe\{C}.{X}
  QUERYFIX	qryfix
  QUERYVAR	qryvar	“select * into 移行元テーブル名 from 移行元テーブル名;”
  SKIPSCR		true
  DATE_MASK	YYYYMMDDHH24MISS	YYYY-MM-DD HH24:MI:SS
  MERGE_LOBS_INTO_SDF		true

  表１．環境変数の設定値

  
  下記の環境変数は MTU の出荷時初期値で使用するものとします。前回のトピックでこれらの値を変更して使っていた場合はご注意ください。

  環境変数名	出荷時初期値（参考）
  EMBED_COLUMN_NAME
  OUTPUT	%MTU_USER_DIR%output
  ENCLOSER	~~
  ENCLOSER2
  RECLENGTH	true
  LONGTRANSIT	0
  DATAEXT
  CHARSETID	0
  NOBYTEORDERMARK

  表２．出荷時初期値のまま使用する環境変数

• 受け渡されるデータの種類には次の３つがあります。1と2は必ず生成されますが、3は問い合わせのデータ型にLOBが含まれている場合だけ生成されるオプションです。「ファイル」と言う表現は紛らわしいですが、実際のところファイルは作られず、データは名前付きパイプはへ送られます。
  1. SQL*Loader用制御ファイル：拡張子 ctl
  2. データファイル：拡張子 dat
  3. LOB格納SDFファイル：拡張子 clo, または blo
• つまり 1、2、および3のカーディナリティーは1:1:0～n ということが言えます。
• MTU は名前付きパイプを 1->3->2 の順序で生成します。3が複数ある時はすべてを生成し終えてから2を生成します。非スレッドプロセスでこの順番を守らずに、パイプを開こうとするとエラーやハングとなりますのでご注意ください。MTUが名前付きパイプを閉じる順序は、生成時と反対です。
• MTUは環境変数 STREAM_LOCATOR=named_pipe://\\.\pipe\{C}.{X}
  の設定により、命名ルールは次の通りとなります。イタリック体で示したinto_field_value_nameの部分には、環境変数 QUERYVAR へ記述したSQL内上の into 識別子に続くラベルが使用されます。nnnn には 0001 から 9999 までの数字文字列として識別される、カラム位置の序数です。イタリック体以外の部分は字句のまま出力されます。
  
  1. \\.\pipe\into_field_value_name.ctl
  2. \\.\pipe\into_field_value_name.dat
  3. \\.\pipe\into_field_value_name_Cnnnn.clo<(またはblo)/li>
• 1の文字エンコーディングはCP932です。2は環境変数 CHARSETID によって変化します。3はCLOB/NCLOBの時UTF-16　Big-Endian です。
• CHARSETIDに873や871といったUTF-8系のキャラクタセットが選択された場合、2の冒頭には3バイトのBOM（Byte Order Mark）が出力されます。これを抑圧するには環境変数 NOBYTEORDERMARK へ TRUEを設定して下さい。
• 2および3の名前は1の中で規定されています。
  1. 2の名前は1に含まれるキーワードINFLEの後に続くシンボルで規定されます。拡張子が無い場合は”dat”が指定されたものとみなします。
  2. 3の名前は1に含まれるキーワードLOBFILE (CONSTANT後に続くシンボルで規定されます。
• 2に出力される列の順序は1に含まれるキーワードFIELDSの後に続くカンマ区切りリストで示されたフィールド名の順序と同じです。
• 可変長形式で使用される行のデリミタは、通常復帰（CR）＋改行（LF）が使われますが、RECLENGTH が true の時は行デリミタを使いません。その代り、行の終末位置は行の冒頭10桁に置かれた10進数の数値文字列によって示されたバイト数で識別します。10桁の数値文字列のすぐ後にデータ本体が送信されます。データ本体のデータ長は数値文字列で示されたバイト数と同じです。
• 可変長形式で使用される列のデリミタは、通常通り半角カンマを使います。行末にも列のデリミタが置かれるので注意して下さい。列のデリミタは環境変数TERMINATOR で設定されます。
• 各Oracle データ型の表現は次の通りです。
  1. NUMBER型データは10進数のテキストで送信されます。数値がマイナスである場合、数値テキストの先頭に半角のマイナス記号が追加されます。数値が浮動小数点数の場合は指数部に続いて仮数部が表記される一般的な表記法と同じです。
  2. VARCHAR2,CHAR,NVARCHAR2,NCHAR型、DATE型はデータ本体を囲むようにして囲み文字列が送信されます。MTUの環境変数 ENCLOSER と ENCLOSER2 の設定により設定されます。ENCLOSER2が空文字列のばあいはENCLOSERと同じ文字列が設定されたとみなされます。囲み文字列として”~~”(半角チルダ記号２つ)を使っているので、文字列データに復帰＋改行など特殊文字が含まれていても、文字列の一部として扱うことが可能となります。
  3. DATE型は環境変数 DATE_MASK に指定した日付書式モデルが適用されます。
  4. LONG型はinto_field_value_name.dat中にインラインで出力されます。囲み文字は有りません。データ本体の冒頭部に10桁10進数の長さフィールドが置かれ、データ本体の長さがバイト数で示されています。
  5. LONG RAW型もinto_field_value_name.dat中にインラインで出力されます。データ本体の冒頭部に４バイト整数（Little-Endian）の長さフィールドが置かれ、データ本体の長さがバイト数で示されています。データ本体はバイナリのイメージのまま格納されています。
  6. CLOB,NCLOB,BLOB型はinto_field_value_name.dat中にNULLかNOT NULLの区別だけ識別する文字列が出力されます。前者の場合は “NONE”、後者の場合は “have”という文字列となります。
  7. CLOB,NCLOB型は文字数単位データ長を示すの10桁10進数の長さフィールドとそれに続くデータ本体がinto_field_value_name_Cnnnn.cloへ出力されます。LOBデータが複数ある場合は長さフィールドとデータ本体のペアが繰り返し連続出力されます。この時の出力順序は2のファイルにデータを出力した順序と同じです。
  8. BLOB型はCLOB,NCLOB型の時と同じように出力されますが、長さフィールドが４バイト整数（Little-Endian）でバイト単位であることと、出力先がinto_field_value_name_Cnnnn.bloであるという点が異なります。
• CLOB,NCLOB,BLOB型は、BULK_SIZEで指定した数だけ一括出力されます。LOBが出力されるタイミングは、into_field_value_name.datへのバルク出力の後です。表に複数のLOB型がある場合は、列の並び順に各列からBULK_SIZE個のLOBが出力されます。

MTU adaptor for MySQLの外部仕様

• MTU adaptor for MySQLは、表１と表２で示した条件下でのプロトコルに準拠して動作するよう設計されています。
• シングル・スレッド・プロセスとして実装されており、MTUと同期をとりながら動作します。
• MTUとMTU adaptor for MySQLの何れか片方が先に起動されると、もう一方が起動され通信可能になるのを待ちます。
• MTU adaptor for MySQL　は次の起動時引数を序数順に取ります。省略不可です。引数が不足している場合は、簡単なコマンドの使い方が表示されます。
  1. into_field_value_name
  2. multiple lists of column values: INSERT 文の VALUES に続く構文要素を繰り返す回数
  3. libmysql.dll 自身を含むパス。フォルダ名がPHTHに含まれていれば libmysql.dll のみでOK。
• 1についてはMTUのプロトコルで述べた、「環境変数 QUERYVAR へ記述したSQL内上の into ラベルに続く識別子」と同じものです。
• 2は。LOB型が含まれる時だけ厳格にMTU環境変数のBULK_SIZEと同じして下さい。これを間違えるとハングします。LOBが含まれなければ、一致していなくても動作します。
• 起動するユーザやカレントディレクトリについては特に制限はありません。
• 正常終了した時の戻り値は０で、異常終了時は非０を戻します。

MTU adaptor for MySQLのコード解説

• main 関数
  次のような流れに沿って全体の手続きを実行します。
  1. バナー出力
  2. プログラム引数のチェック
  3. 経過時間計測の開始
  4. MySQL データベースへ接続
  5. 制御ファイルのオープン
  6. 制御ファイルの内容キャッシュ
  7. 制御ファイルの構文解析と変数初期化
  8. データファイルの数だけ繰り返し
     1. データファイルのオープン
     2. LOBがあればLOB用SDFのオープン（繰り返しの初回だけ）
     3. データファイルの読み取り
  9. 正常終了のメッセージ
• 名前空間 nsMysql
  MySQL の C API を c++ から利用し易くする為のラッパー・クラスを定義します。名前空間 nsMysql には、libmysql.dll からインポートした関数のエントリ・アドレスを指し示すポインタが置かれます。
  1. class Db
     C API 関数を指し示すポインタの初期化と、MySQL への接続、トランザクションの制御を担当します。このクラスのインスタンスは、次に説明する、Stmt オブジェクトの構築に使われます。スタティック関数 StartSession の呼び出しを通じてのみ、インスタンスの生成が可能な為 Singleton であることが保証されます。つまり、何回呼び出されても唯一のインスタンスに対するアドレスを戻します。
     MySQL への接続を開始する前にmysql_options 関数を呼び出します。引数に MYSQL_OPT_COMPRESS を与えて、クライアント／サーバープロトコルの圧縮を図ります。
  2. class Stmt
     MySQL で SQL 文を取り扱う為の操作を担当します。
     GenerateInsStmt 関数はとてもユニークで、引数にテーブル名とフィールド名の配列、そして繰り返しの数（バルクの要素数）を受けて、multiple lists of column values を含むINSERT … VALUES 構文を生成します。判り易く表現すると、次のような挿入文を得るという事です。

     INSERT INTO tbl_name (a,b,c) VALUES (?,?,?),(?,?,?),(?,?,?)

     丸かっこで囲まれた疑問符のカンマ区切りリストは、バルクの要素数だけ繰り返されます。
     Prepare 関数は mysql_stmt_prepare 関数を呼び出します。この関数は Stmt オブジェクとの存続期間を通じて1回乃至2回までしか呼び出されないので、MqSQLデータベースへ解析を原因とする負荷をほとんど与えません。
     MapParam 関数は、MTU から読み取ったデータのバッファをMySQL VALUES 句へ割り当てます。列の数と行の数の積の回数、呼び出されます。
     Execute 関数はmysql_stmt_bind_param 関数と、mysql_stmt_execute 関数をそれぞれ、（全体の行数÷バルクの要素数）回呼び出します。（全体の行数÷バルクの要素数）の計算結果、剰余が生じる場合、剰余を要素数とするバルクを１回だけ実行します。

• class Chrono
  時間計測を行います。get_elapsed() 関数を呼び出すと、オブジェクト生成からの経過時間をミリ秒単位で戻すことが出来ます。
• struct Consoleおよびstatic Console con スタティック変数
  標準出力・標準エラーを包み込んだクラスとそのオブジェクトです。メッセージ出力対象の文字列を指定して printStdXXX 関数を呼び出して使います。この関数はスレッドセーフです。
• class cInStream
  cCtlFile クラスと cDatFile クラスのオブジェクトがファイル（か、それに該当する名前付きパイプ）を扱う時の実装を担当します。
• struct Param
  Stmt::MapParam 関数へ渡すための引数を保持するオブジェクトです。
• struct tFormatConverter
  データ変換をする関数オブジェクトのインターフェースのみを定義する抽象クラスです。各Oracle データ型に固有な変換手順は、このクラスのインタフェースを継承して実装されます。
  makeInstance 関数は、引数の内容に応じた各派生クラスのインスタンスを生産する仮想コンストラクタです。
  1. class fDecimal
     MNUMBER 型を変換するオブジェクトを生成するクラスです。
  2. class fChar
     CHAR, NCHAR, VARCHAR2, NVARCHAR2, DATE 型を変換するオブジェクトを生成するクラスです。
  3. class cLobImpl
     fCLob クラスと fBLob クラスのオブジェクトに共通な実装を担当します。
  4. class fCLob
     CLOB 型、NCLOB 型を変換するオブジェクトを生成するクラスです。
  5. class fBLob
     BCLOB 型を変換するオブジェクトを生成するクラスです。
  6. class fLongRaw
     LONG RAW 型を変換するオブジェクトを生成するクラスです。
  7. class fLongStr
     LONG 型を変換するオブジェクトを生成するクラスです。

  MTU adaptor for MySQL を改良して未対応のデータ型へ対応させようとする場合は、そのデータ型固有の変換手順を実装する派生クラスを新設し、仮想コンストラクタの実装へ新しい型のインスタンスを生成する分岐を追加して下さい。
  必要に応じて、cCtlFile::Parse にある正規表現を改修して、新しいデータ型の SQL*Loader のフィールド式にマッチさせて下さい。

• class cCtlFile
  SQL*Loader の制御ファイル（か、それに相当する名前付きパイプ）をオープンし、テーブル名やフィールド名のリストを読み取り、データ変換に使用する、tFormatConverter を決定します。
• class cDatFile
  可変長データファイル（か、それに相当する名前付きパイプ）をオープンし、レコードを繰り返し読み取ります。読み取ったレコードのデータは、文字列型のstd::vector へ一時的にバッファリングされます。読み取ったレコード数がバルクの要素数に達したら一括して MySQL へ送信するよう Stmt::Execute 関数を呼び出します。

強化版！MySQLへのデータ移行術（２／２）へ進む