別途Excelのpdf形式で添付いたします。Extract(抽出)、Transform(変換・加工)、Load(ロード)の一連の処理を指す用語です。ETL処理の作成/実施を支援する為に、ITベンダ各社より様々なツールが展開されています。ETLツールが必要とされるケースは以下のような場合があります。連携元DBから抽出したデータが、そのままPostgreSQLに取り込めない形式の場合データの抽出にあたりAPの作成が必要となる場合データを反映する前にデータの加工/変換など中間処理が必要となる場合1.のケースとしては、通常のRDBMSであればCSVに出力すればそのまま取り込める場合が多いです。PostgreSQLに合せてデータの加工が必要となります。連携元のデータベースに対するプログラミングの知識も必要となります。実施したい場合には何らかの変換ツールの活用であったり、APの作成が必要となります。ETLツールはこれらの課題に対し大きな効果を期待することができます。連携先のデータベースに対するプログラミングの知識がなくとも連携用のAPを構築できます。連携における中間処理も部品とし豊富に用意されており、複雑な業務も容易かつ柔軟に組むことができます。本章では無償版が提供されているTalendをピックアップし、紹介いたします。Talendとは、Talend社が提供するデータ統合・連携製品です。それぞれTalend社やベンダー各社によるサポートが提供されています。GUIを利用したジョブの作成と、JARファイルによる実行ファイルの出力まで実施できます。Talendの特徴としてOSS版でも多くのETL機能、データファイルやDBへの接続コンポーネントを利用することができます。無償版でもほぼ差はありません。Studioを利用して異種DBと連携する手順を紹介いたします。主キーはuser_idとpayment_dateの複合キーとします。実行には事前にJavaのインストールが必要となります。V6.3.1の推奨Verは1.7です。その他、実行環境の要件はTalend社のHPから確認できます。ここでは新規にTest_Projectを作成します。起動して最初の画面です。サンプルのジョブが開かれているので閉じておきます。『DB接続の作成』を選択します。接続するDBMSに合せてDBタイプを選択し、必要な情報を入力します。チェックボタンで接続の確認が可能です。この時、必要なJDBCドライバをTalendが自動でダウンロードします。別途予め必要なJDBCを用意し、手動で配置する必要があります。ログイン欄に指定するユーザー名/ロールには予め適切な権限を設定しておいて下さい。反映元DBへ接続するユーザーには参照権限、反映先DBへ接続するユーザーには各種更新権限が必要となります。スキーマ情報の取得¶DBMSとの接続が設定できたら、データ連携の対象となるスキーマを選択し、読み込みます。して取得したテーブルやビューがTalendの操作対象となります。作成したDBの接続を右クリックし、『スキーマ情報の取得』を選択します。データ連携の対象とするテーブルを選択します。読み込んだスキーマは各DB接続のプルダウンメニューに表示されるようになります。GUI上でコンポーネントを配置しながら連携の処理内容を作成していきます。『ジョブの作成』を選択します。コンポーネントとして配置することができます。最初に反映元であるOracleのテーブルをコンポーネント配置します。Oracleがデータの抽出元である場合は、『tOracleInput』を選択します。次に反映先であるPostgreSQLのテーブルも同様にドラッグ&ドロップで配置します。PostgreSQLが出力先である為、『tPostgresqlOutput』を選択します。Oracleからの出力とPostgreSQLへの反映がそれぞれ配置されました。データの入/出力についてはコンポーネントのアイコンからも判別することができます。『ロウ(Main)』を選択し、tPostgresqlOutputまでrowを繋げます。Oracleからの抽出と、PostgreSQLへの反映が繋がりました。これで単純な全件抽出、反映のジョブが完成となります。データ型について暗黙的に変換が可能なものは、Talendが自動で対応します。事前にテーブルをtruncateする処理を設定しておきます。選択したコンポーネントの詳細な設定が可能となります。下部ウィンドウの『実行』タブを選択し、ジョブの実行をテストします※実際に接続するDBMSへの操作が行われます。ジョブの実行が正常に終了できれば、ジョブの完成となります。ジョブ作成エリアには実行時間なども表示されます。作成したジョブはJavaのAPとして実行ファイルに出力することが可能です。『ジョブをエクスポート』を選択します。ジョブがビルドされ、jarファイルと関連ファイルをまとめたzipファイルが出力されます。jarを実行するbatファイルとスクリプトに加え、Javaのソースコードも含まれています。データ連携APを実施、OracleのデータをPostgreSQLに反映することが可能となります。hh24:mi:ss'));1行が作成されました。commit;コミットが完了しました。複雑な処理もGUIで直感的に作成することが可能です。フィルタのコンポーネントを間に挟み込むことで設定できます。様々なコンポーネントは右側のパレットから選択できます。ネットワークの設定などが異なる場合はDBへの接続情報を個別に変更することも可能です。接続の際に指定しているホスト名などを変更可能です。本ジョブに関してのみ設定が変更されます。対象DBでのシステム開発の経験が少ない開発期間が短い、開発工数が少ないAPの継続的な改修があり、処理の可視化などのメンテナンス性が必要などが想定されます。コンポーネントが標準で用意されていることです。DB(mongoDBなど)やクラウド(AWSなど)にも対応しています。留意点としては、OSS版ではCDCの機能が使えないため、差分反映を設定することができません。時系列によってテーブルを分ける、反映先は都度truncateするなど、運用による工夫が必要です。運用を実施したい場合には有償版の採用も検討に値します。関わる高度な機能が利用可能です。ついて¶SymmetricDSは、異種DB間で一方向レプリケーション、双方向マスターレプリケーションをサポートするツールで、レプリケーション時にフィルタリングや変換処理を行うことができます。レプリケーションは非同期でデータを複製します。SymmetricDSは、Java上で動作するため、Javaをサポートする様々なデータベースに対応し、様々なプラットフォーム間で動作するように設計されています。Pro)の2種類があります。3.0となっています。ついて検証した結果をまとめます。商用製品の紹介していますので、併せてご参照ください。SymmetricDSを利用した異種DB連携環境の構築手順¶SymmetricDSを利用して異種DB間の連携する手順は、以下の通りとなります。デモのシナリオを使って説明します。構築結果イメージは、下図になります。ノードは、それぞれ親となるノードをRootNode、クライアントとなるノードをClientNodeと明記します。PostgreSQLとMySQLの連携による環境構築を行っていますが、説明はPostgreSQLのみとします。Jobの2種類が存在します。node)で監視します。RootNode、ClientNodeの両ノードに対して行います。構築手順の検証で行ったSELinuxとファイアウォールの設定は、以下の通りです。7.0以降がシステム要件となっています。インストールしていない場合には、インストールが必要です。必要に応じて、PL/pgSQLのインストールを行います。9.0以降はデフォルトでインストールされるため、改めてのインストールは不要です。ユーザ作成と権限付与¶SymmetricDSユーザ"corp"を作成します。corp;必要に応じて、publicスキーマに対してSymmetricDSユーザ"corp"に権限(usage、create)を付与します。データベース作成¶SymmetricDSでDB連携するデータベースを作成します。SymmetricDSの展開¶SymmetricDSのダウンロードサイトからダウンロードした圧縮ファイルを展開します。環境構築の検証では、SymmetricDSのホームディレクトリを/usr/local/symmetric-serverとして展開した例で説明します。プロパティファイルを作成、配置¶/usr/local/symmetric-server/engines下にプロパティファイル(.properties)を配置します。いずれもPostgreSQLの場合の設定を記載しています。SymmetricDS用のテーブルを作成¶SymmetricDS用のテーブルを作成します。RootNode側で作成しておけば、ClientNode側にも反映されます(通常のテーブルの場合は、反映されない点に注意)。create-sym-tables作成される主なテーブルは、以下の通りです。役割sym_channelチャネルを管理するテーブルチャネルは、任意のテーブル更新を1つのトランザクションでまとめる単位を定義します。sym_nodeノードを管理するテーブル一意識別子となるノードIDを付与したノード情報や、どのノードグループに属するかを定義します。sym_node_groupノードグループを管理するテーブルノードグループは、データを反映するノードのグルーピングを定義します。pull)を管理するテーブルsym_triggerトリガを管理するテーブルトリガは、監視対象のテーブルとチャネルの結びつけを定義します。条件に従った変更のみ)を定義します。sym_trigger_routerトリガとルータの結び付けを管理するテーブル監視対象のテーブルに変更があった場合の反映の範囲を定義します。sym_node_securityノードのセキュリティを管理するテーブルノードの認証に必要なパスワードを定義します。※SymmetricDS起動時に必須。Link、Trigger、Routerとの関係をイメージにした図を下図に示します。図では、左のテーブルが更新された場合の連携を示しています。RootNodeの起動¶RootNode側のSymmetricDSを起動し、ClientNodeから待ち受けるモードにします。SymmetricDSによるデータ連携(動作確認)¶SymmetricDSで準備されているサンプルデータを用いて、データ連携の動作確認を行います。連携するテーブルを作成¶RootNode、ClientNodeの両ノードで、create_sample.xmlを用いて、itemテーブルなどのサンプルテーブルを作成します。データロード¶RootNodeで、insert_sample.sqlを用いて、サンプルデータと連携の設定情報をインサートします。insert_sample.sql実行後、ClientNode側にインサートしたデータが連携していることを確認してください。反対方向の連携(ClientNodeからRootNode)は転送方式が異なるため、ClientNode側に別のデータをインサートしてみて、連携することを確認してください。挙動の概要¶データ連携におけるデータの流れは、下記の通り元テーブルから宛先テーブルに伝搬します。Jobの場合をそれぞれ図に示します。データの連携機能¶データの伝搬を行うにあたって、4つの主要な機能により連携を実現しています。トリガ元テーブルが更新されると、トリガがSYM_DATAテーブルに更新内容を記録します。更新内容は、CSV形式で格納します。Jobが、SYM_DATAテーブルの更新を検知し、バッチを作成し、SYM_OUTGOING_BATCHテーブルに格納します。その際、同一チャネル(バッチの宛先、連携方式)であれば処理をまとめられるため、その処理をSYM_DATA_EVENTテーブルで決定し、どこまで終わったかの連携管理をSYM_DATA_GAPテーブルで管理しています。当該ファイルからSYM_INCOMING_BATCHテーブルにデータをロードします。ロードしたデータをもとに宛先テーブルを更新します。Areaで受け取ります。当該ファイルからSYM_INCOMING_BATCHテーブルにデータをロードします。ロードしたデータをもとに宛先テーブルを更新します。Jobはバッチがあるときのみネットワークコネクションを貼ります。連携におけるデータの整合性は、送信元はSYM_OUTGOING_BATCHテーブル、送信先はSYM_INCOMING_BATCHテーブルのステータスを用いて保つようになっており、各テーブルで確認が可能です。ステータスは以下の通りです。同時更新時の注意点¶データ連携の挙動を確認したところ、送信元ノードと送信先ノードで同時更新した場合には更新をそれぞれ反映されてしまったので、データの一貫性が担保できませんでした。下図が検証結果になります。このように、同時更新が発生すると更新が上書きされてしまうため、SymmetricDSをマルチマスタ用途として利用することは避けるべきであると考えられます。本節では、SymmetricDSの障害検知方法と障害回復方法を紹介します。障害の検知対象は下表の8種類で、検知対象の閾値設定やノードグループ、ノードIDとの対応付けや有効化などの設定を行います。した場合の通知方法としては下記の2種類があります。こちらも、通知方法とノードグループやノードIDとの対応付けや有効化などの設定を行います。ClientNode障害時の手順¶ClientNode障害時の回復手順とイメージを下記に示します。RootNode障害時の手順¶RootNode障害時の回復手順とイメージを下記に示します。動作検証の概要¶SymmetricDSは、非同期のデータ連携であるため、どの程度の遅延が発生するか気になります。実際に動作検証を行いました。ハードウェアおよびソフトウェアの主なスペックと構成は以下の通りです。検証を行うに当たって、その他の負荷はほとんど無い状態で実施しています。検証方法¶SymmetricDSによるデータ連携環境を構築し、1クエリ(insert文)を実行した際の遅延度合を計測しました。計測方法は、SymmetricDSの実行ログと全クエリのレスポンス時間をログに出力・集計することで、SymmetricDSの内部処理にかかったタイムラグが確認できます。この検証に当たって、以下の設定を行いました。全クエリのレスポンス時間をログに出力する設定しました。0●SymmetricDSのプロパティファイルの設定JOBの処理間隔を最小値に設定する設定にしました。前に、処理の流れをシーケンス図でまとめました。insert文のトランザクション結果をt1、SymmetricDSの処理結果がd1~d4で表しており、トランザクション時間(t1)と連携先のテーブルに書き込まれるまでの時間(d1+d2+d3)のギャップが、遅延度合ということになります。5回検証を行った結果を下図にまとめました。142.837上記の検証環境下においては、遅延度合は1秒以内にデータ連携が完了していることがわかります。連携後の処理(d4)に時間がかかっているため、バッチが多くなる場合には遅延度合が増える可能性があります。Job方式を主方式として検討する。Job方式を検討することでリソースを無駄なく使うようにする。・連携できるのはDML(insert、update、delete)のみで、truncateやDDLはtriggerの対象外なので連携できません。・NullPointerExceptionや強制終了など発生することが多く、品質にかなり難があると感じています。・ユーザガイド通り設定してもできない箇所や、ユーザガイドのリンク先が誤っている箇所を10個ほど見つけました。ドキュメント品質を上げる必要があると感じています。・コミュニティはあるがあまり積極的ではなく、重要な問題もなかなか解決しないので、使用するにあたり注意が必要です。ロジカルデコーディングについて¶ロジカルデコーディングは、PostgreSQL9.4から提供されている仕組みです。実行された更新処理を他のリソースへと伝播することができます。反映することで実現します。自由度が高いことが特徴です。データの列名や値によるフィルタリングもプラグインの実装次第で可能です。ロジカルデコーディングを利用した異種DB連携環境の構築手順¶ロジカルデコーディングを用いる環境を構築するために必要な手順の流れを以下に示します。(1)元となるPostgreSQLを用意するロジカルデコーディングの入力元となるのはWALの情報です。何はともあれ、PostgreSQLを用意する必要があります。(2)プラグインを作成するDB連携する異種DBにあわせて、プラグインを作成します。(プラグイン作成に必要なことを後述します)(3)PostgreSQLの設定を変更するデフォルトの設定のままでは、ロジカルデコーディングを利用することはできません。利用するための設定を施します。(4)ロジカルデコーディングを実行するSQL関数もしくはレプリケーション接続を介して、デコーディングを行います。contribに含まれるtest_decodingモジュールを例に、プラグインの実装を見ていきます。ロジカルデコーディングの仕組みを利用するためは、いくつかのコールバック関数を実装する必要があります。初期化処理でこれらのコールバック関数を登録します。(1)初期化関数_PG_output_plugin_initという名前で実装し、構造体に関数ポインタを登録します。構造体および初期化関数の定義は以下の通りです。*cb);なお、test_decodingでの実装は、以下のようになっています。commit_cbです。初期化関数の中で登録されるコールバック関数の一覧を以下に示します。開始コールバックスロットの作成時及びデータ取得の要求の各処理があった際に呼び出されます。OutputPluginOptions.output_typeの変更(OUTPUT_PLUGIN_TEXTUAL_OUTPUTかOUTPUT_PLUGIN_BINARY_OUTPUT)など各種オプションの設定を行います。終了コールバック各処理の最後に呼び出されます。*ctx)開始コールバック関数で割り当てた領域の削除などのクリーンアップ処理を実施します。トランザクション開始コールバックトランザクションの開始(BEGIN)を検出した際に呼び出されます。場合には、トランザクション開始コールバック関数は呼び出されません。トランザクション終了コールバックトランザクションの終了(COMMIT)を検出した際に呼び出されます。DELETE)を検出した際に呼び出されます。*change)更新内容は、change->actionを見ることで判別できます。新旧データの内容はchange->data.tpを見ることで取得できます。双方向のレプリケーション時などにデータの伝播が循環し続けてしまうことを抑制できます。オリジンの設定は、後述のSQL関数で設定できます。汎用メッセージコールバック汎用メッセージを検出した際に呼び出されます。*message)汎用メッセージは、後述のSQL関数で発行することができます。(1)wal_levelwal_levelパラメータでWALに含まれる情報の量を変更します。デフォルトの"minimal"から"logical"にします。"minimal"の順でWALに含まれる情報の量は多くなります。PostgreSQL9.5以前の"archive"/"hot_standby"は"replica"として扱われます。(2)max_replication_slotmax_replication_slotパラメータでデータベースクラスタで作成できるスロットの最大数を変更します。デフォルトの0から1以上の値にします。上記に加えて、以下の設定を変更する必要があります。senderプロセスの最大数を変更します。デフォルトの0から1以上の値にします。1(4)レプリケーション接続の許可レプリケーション接続を許可するため、pg_hba.confにreplicationのレコードを追加します。認証方式の選択は適宜行ってください。実行例¶ロジカルデコーディングを実行する方法としては、以下の2通りがあります。(1)SQL関数を介して実行SQL関数を実行することで、ロジカルデコーディングを実行します。レプリケーション接続を介した実装、設定、実行を行う必要があります。処理を実装する必要があります。http://www.postgresql.jp/document/9.6/html/protocol-replication.html説明を簡単にするため、ここではSQL関数を用いた実行例を見ていきます。設定後に実施する大まかな手順の流れは以下の通りです。(1)スロットの作成まずpg_create_logical_replication_slot関数を実行して、スロットを作成します。name)関数の引数として、スロット名(slot_name)とプラグイン名(plugin)を指定します。戻り値は、スロット名(slot_name)とトランザクション位置(xlog_position)です。row)なお、作成したスロットはpg_replication_slotsビューで確認できます。削除はpg_drop_replication_slot関数で行えます。row)(2)デコーディングの実行スロットの作成が完了したら、そのスロットからWALを読み込みデコーディングしていきます。デコーディングを実行できます。プラグインで利用されるオプション(options)を指定します。upto_nchangeでトランザクション位置及びトランザクション数によるデコード対象の絞り込みが行えます。両方ともNULLが指定されている場合は全ての結果が返されます。pg_logical_slot_get_changes関数は一度デコードされたデータは再度デコードされません。pg_logical_slot_peek_changes関数は一度デコードされたデータも再度デコードの対象となります。戻り値は、トランザクション位置(location)、トランザクションID(xid)、デコードしたデータ(data)です。基本的な利用方法は上記のみです。以降はオリジンフィルター処理とメッセージ処理の実行方法について見ていきます。(3)オリジンフィルター処理オリジンの設定を行っておくと、WALの中に含まれるオリジンに関する情報を利用できるようになります。判別できるようになります。オリジンの作成はpg_replication_origin_create関数で行います。text)関数の引数は、オリジン名(node_name)です。戻り値は、そのオリジンを一意に特定するID(oid)です。オリジンの設定はpg_replication_origin_session_setup関数で行います。text)関数の引数は、オリジン名(node_name)です。戻り値はありません(void)。オリジンの削除はpg_replication_origin_drop関数で行います。text)関数の引数は、オリジン名(node_name)です。戻り値はありません(void)。row)test_decodingでは、デコーディングのオプションとしてonly-localオプションを指定できます。デコードの対象から外すようになります。汎用メッセージにより、WALに独自の文字列を含めることができます。汎用メッセージの発行はpg_logical_emit_message関数で行います。メッセージ本体(content:textもしくはbytea)です。戻り値はトランザクション位置(pg_lsn)です。ロジカルデコーディングの今後¶ロジカルデコーディングを用いた正式なソリューション、まだPostgreSQL本体には含まれていません。プラグインを適用する必要があります。導入に際しては、十分に検証を行った上で判断してください。ベースにした「ロジカルレプリケーション」機能が本体に追加される見込みです。この機能の追加により、様々な異種DB間の連携ソリューションが加速することが期待されます。本章では、PostgreSQLの外部データラッパを用いた異種データベース連携の方式に関して説明します。SQL文を利用してアクセスすることが可能になります。外部データには、外部データラッパ(以降、FDW)を通じてアクセスします。外部データラッパは、外部データと通信できるライブラリであり、接続の確立やデータ取得の機能を抽象化します。「外部データ」の対象にし、異種データベースとの連携方法としてFDWを評価することを目的としています。PostgreSQLの特長的な機能として、外部データラッパの調査結果が整理されていますので、併せてご参照ください。FDWを利用した異種DB連携環境の構築手順¶FDWを利用して異種DBと連携する手順は、大きく以下の通りです。11gと、外部データラッパとしてoracle_fdwを利用します。Oracleクライアントのセットアップ手順、oracle_fdwのセットアップ手順が記載されています。本節では、外部データラッパの利用に共通で必要な、PostgreSQL内での構築手順(3.,4.,5.)を中心に記載します。以下のパラメータを利用して外部サーバの定義を実施します。XE以下のSQL文を実行し、外部サーバを定義します。必要に応じて、外部サーバに接続するユーザに権限を付与します。MAPPING)¶PostgreSQLユーザと外部サーバのユーザの関連付けます。TABLE)¶ここでは、Oracle上のoracle_fdw_testという名前のテーブルに対し、PostgreSQL側ではoracle_testという名前で外部テーブルを作成することで対応付けます。定義した外部テーブルに対し、ローカルのテーブルアクセスと同様にSQL文を記述します。SCHEMAは、外部サーバ上に存在するテーブルを指す外部テーブルを作成するための構文です。以下は、Oracleのスキーマ「SYSTEM」配下のテーブルを外部テーブル定義としてインポートする例です。SCHEMAシステムカタログ「pg_foreign_table」を利用して、外部テーブル定義が実施されていることを確認します。外部テーブルへのアクセスの確認方法として、実行計画を参照する方法があります。以下は、通常のテーブルへのアクセス時の実行計画です。rows)以下は、外部テーブルへのアクセス時の実行計画です。Scan」が選択されています。連携先データベース上で処理が実施されます(「プッシュダウン」と呼びます)。これにより、連携先のデータベースのリソースを有効に利用できます。rows)同じ定義のテーブルをローカルに作成し、同様のSQL文を実行した場合の実行計画は、以下の通りです。連携先のリソースを利用できます。連携先、連携元を流通するデータ量が削減できています。異種DB上に配置されているストアドプロシージャやユーザファンクションは、FDWを通じて直接実行することができません。そのため、FDWを経由せず、連携先の異種DBに対して直接SQL文を実行します。Consortium。