より速く、より深くしたい人のためのMS SQL ServerとPostgreSQLの統合

最近、PostgreSQLとMSSQLの統合の説明がハブで既に公開されています しかし、詳細は明確に欠けていました。 したがって、この出版物の目的は次のとおりです。

• tds_fdwと呼ばれるMSSQLのFDWに関する一般に公開されている情報を展開および深化：メジャーバージョンの違いについて話し、基本的な互換性の問題を説明します。
• 外部テーブルを使用してクエリを最適化する可能性について話します。
• 実体化された表現での外部データのキャッシュに触れる。
• PostgreSQLとMSSQLの統合へのエキゾチックなアプローチについて少し話してください。

TDS FDWのインストールと構成

PostgresProのスタッフはすでにこのプロセスについて十分に述べています。繰り返しはしません。 PostgreSQLの公式ドキュメントとtds_fdwからの例へのリンクをいくつか残します。

• PGでの拡張（拡張）の作成： off.documentation ;
• サーバーの作成： off.documentation 、 tds_fdwの 例 ;
• ユーザーのマッピングの作成（ユーザーマッピング）： ドキュメント外 、 tds_fdwの例 ;
• 外部テーブルの作成：外部ドキュメント 、 tds_fdwの例

そしてもう1つ： tds_fdwのインストール手順に示されているようにしないでください

sudo make USE_PGXS=1 install
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

猫を保存し 、debパッケージを収集して生活を楽しみましょう ：

 sudo USE_PGXS=1 checkinstall
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

TDS FDWのメジャーバージョン間の違い

現時点では、FDWには2つの現在のバージョンがあります。安定版1.0.7と2.0.0-alphaは、本質的にマスターブランチであり、最も興味深いものがすべて発生します。 違いの簡単なリストを次に示します。

• 2.0.0では、外部テーブルに直接関連するWHERE句の条件のプッシュダウンサポートがついに導入されました。 ただし、foreign_table宣言内でクエリオプションを使用する場合は、依然としてうまく機能しません。
• tds 7.4のサポートが登場しました（これがなぜ必要で重要なのかを以下に説明します）。
• 外部テーブルでのDISTINCTの作業にはいくつかの問題があります（これはGitHubの問題です ）が、私の手がそこから成長しているかどうか、またはバグが非常にトリッキーであるかどうかは不明で、特定の状況下でのみ表示されます。

互換性の落とし穴

最近まで、tds_fdwは7.3より上のtdsバージョンでは動作しませんでした。 しかし、この記事の執筆中に、バージョン7.4のサポートを見つける必要がありました 。 そのため、 3ds803cコミット以降、tds_fdwはtdsの現在のすべてのバージョンをサポートしています。



このバージョンのサポートがなぜそれほど重要なのですか？ 個人的には、これはMSSQL 2012で作業する必要があるため重要です。Rubyon Railsでは、 activerecord-sqlserver-adapterライブラリを使用してMSSQLに接続し、MSSQLはtiny_tdsを使用します。 MSSQL。 問題は、RoR 3および対応するライブラリのメジャーバージョンでは、tds 7.1バージョンの使用が禁止されており、4 +バージョンでのみ構成を介して変更できることです。 同時に、バージョン7.1はMSSQL 2008でも問題なく機能しますが、MSSQL 2012と通信する場合、次のエラーが表示されます。

 DB-Library error: DB #: 20017, DB Msg: Unexpected EOF from the server
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 ActiveRecord::LostConnection: TinyTds::Error: closed connection: ...
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 TinyTds::Error: Adaptive Server connection failed
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

など。



RoRの更新はカテゴリー的に長く高価ですので、FDWの使用に切り替えることでそれらを回避したかったのです。 しかし、tds_fdwは必要なバージョンをサポートしておらず、それについて何かをしなければなりませんでした。



間違いについては、それらはすべてランダムに表示され、同じ場所のために成長します。 アプリケーションで「バラエティ」を作成し、ランダムな場所でランダムな時間に落下させます。 この不名誉はすべて、正しいバージョンのtdsを使用してのみ処理されます。 MSSQL 2012の場合、これはtds 7.4です。



最初の待ち伏せは次のとおりです。tds7.4のサポートは、バージョン0.95以降のFreeTDSで実装されています。 しかし、Ubuntu 14.04および16.04 では、それぞれバージョン0.91-5および0.91-6.1build1がそのまま使用できます。 また、次の2つの方法でFreeTDSの新しいバージョンを入手できます。

1. ソースから FreeTDS をビルドする
2. FreeTDS 1.00で代替PPAを使用します。

2番目のケースでは、1つの警告があります。指定されたリポジトリには、Ubuntu 14.04（信頼できる）専用のパッケージがあります。 16.04（これはゼニアルです）では、何もありません。 しかし、一般的に、致命的なことはありません。16.04の/etc/apt/sources.list.d/jamiewillis-freetds-trusty.listを次のように修正すると、

 deb http://ppa.launchpad.net/jamiewillis/freetds/ubuntu trusty main
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

パッケージを最新のUbuntuにインストールできます（そして、はい、問題なく動作します）。



CentOSをお持ちの場合は、その下でバージョン0.95までのFreeTDSを簡単に見つけることができます 。 古いものはすべてソースから収集する必要があります。

互換性の問題の回避策

エラー番号20017とその派生物が非常に迷惑であり、必要なバージョンのtdsを使用する可能性がない場合、PostgreSQLによってスローされた例外を処理し、FDW経由でMSSQLにアクセスするブロック/メソッド/などを再起動できます。 私の場合、RoRアプリケーションの場合、次のようになりました。

 def retry_mssql_operation(tries = 5) begin yield rescue ActiveRecord::StatementInvalid => e if e.message =~ /^PG::FdwUnableToCreateExecution/ && tries > 0 tries -= 1 retry else raise end end end
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最初は節約できますが、長期的なソリューションには適切ではありません。

プッシュダウンとFDWが「指で」動作する方法について少し

外部データベースへのクエリ最適化の質問に移る前に、プッシュダウンについて少し説明したいと思います。 何らかの理由で、このメカニズムの説明はロシア語のリソースでは必要とされていません（または、その正しい翻訳に精通しておらず、ブロック上の三頭筋ベンチプレスは明らかにそのオペラのものではありません）。 したがって、私はそれについて簡単に話したいです。



最も単純なケースでは、PGで次の形式のクエリを実行します。

 SELECT column_name FROM foreign_table WHERE column_id = 42;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

実際、データベースでは次のことが発生します。

1. サードパーティのサーバーにあるforeign_table（またはテーブルではない）に関連付けられたテーブルから、postgresのすべてのコンテンツが抽出されます。
2. 次に、受信したデータはWHEREからの条件に基づいてフィルタリングされます。

特に数百万行のテーブルから1つだけを取得する場合は、あまり効率的なスキームではありません。 そして、ここにプッシュダウンがあります。 このメカニズムにより、リモートサーバーから受信する行数を減らすことができます。 これは、PG側で必要なものを考慮して、つまりWHERE、JOIN、ORDERなどに示されているものを考慮して、外部データベースへのクエリを構築することによって行われます。つまり、FDWはPotsgreSQLで元のクエリを解析し、それから選択します。これらの条件に応じて、リモートデータウェアハウスが新しいリクエストを理解して収集できること。 当然の結果として、プッシュダウンはすべてのFDWに適用できるわけではありません（たとえば、プッシュダウンはfile_fdwではほとんど役に立ちませんが、 postgres_fdwまたはtds_fdwではまったく逆です）。



ボトムライン：プッシュダウンはクールで、外部データストレージメカニズムを使用でき、PGと外部ストレージ間で循環するデータ量を削減できるため、クエリの実行が高速化されますが、同時に別のメカニズムであるため、実装して維持する必要がありますかなり重要なタスク。

リクエストの高速化

インストール、構成、およびハードウェアを把握しました。 次に、MSSQLからデータをすばやく抽出する方法について説明します。

プッシュダウン

このアプローチは、さまざまなJOINやその他のSQLトリックの負担にならない単純なクエリの場合に役立ちます。 tds_fdwの最新バージョン（現在2.0.0-alpha）では、WHEREの単純なプッシュダウンのサポートが導入されました。



たとえば、MSSQLデータベースのsimple_tableテーブルを考えます。 このテーブルには、idとdataの2つのフィールドがあります。 外部テーブルの定義は次のとおりです。

 CREATE FOREIGN TABLE mssql_table ( id integer, custom_data varchar OPTIONS (column_name 'data')) SERVER mssql_svr OPTIONS (schema_name 'dbo', table_name 'simple_table', row_estimate_method 'showplan_all', match_column_names '1');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この場合、PostgreSQLとMSSQLの最初の列の名前はidです。 2番目の列の名前はPGとMSSQLで異なるため、ここではcolumn_nameオプションが必要です。 このパラメーターは、PostgreSQLからMSSQLの列への列のマッピングを明示的に設定します。 また、最後にmatch_column_nameパラメーターが指定されます 。これは、名前による列名の暗黙的なマッピングを担当します。つまり、id列がマッピングされます。



リクエストを実行する場合、これで終わりです。

 SELECT custom_data FROM mssql_table WHERE id = 42;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

FDWはWHEREで指定された条件を処理し、MSSQLで正しいクエリを収集する必要があります。 たとえば、これ：

 SELECT data FROM simple_table WHERE id = 42;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

tds_fdwバージョン1.0.7以下の場合、MSSQLのクエリは異なります。

 SELECT id, data FROM simple_table;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

繰り返しますが、現時点ではプッシュダウンはWHEREに対してのみ機能します。 JOIN、ORDER、およびMAX、LOWERなどのその他の関数では、起動しません。



そしてもう1つ、MSSQL側で実際に実行されたクエリをどのように知るのでしょうか。 たとえば、MySQLにFDWを使用すると、explainに次の行が表示されます。

 Remote query: SELECT `id`, `customer_id`, `order_date` FROM `data`.`orders`
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして便利です。 tds_fdwでは、これはまだではなく、FreeTDSログをより長く調べる必要があります。 デフォルトでは、FreeTDSのログは無効になっていますが、これは/etc/freetds/freetds.confを調べることで簡単に修正できます。 ここでこれらの行を見つけることができます：

 ; dump file = /tmp/freetds.log ; debug flags = 0xffff
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

最初にセミコロンを削除する必要があります。



これで、PGからのMSSQLでのクエリに対して、FreeTDSは可能な限りすべてのログを記録します。 これにより、すべての外部リクエストの実行が遅くなり、大量のログが生成される可能性があります（私の場合、通常のSELECTのログは約300MBで、JOINはわずかに約1.5GBに絞り込まれています）。 ただし、ログでは、MSSQLで実際に実行されたことを確認できます。 さらに、「デバッグフラグ」で遊ぶことでログの量を減らすことができます。 FreeTDS でのロギングの詳細についてはこちらを 、「デバッグフラグ」の詳細についてはこちらをご覧ください 。

マテリアライズドビュー

マテリアライズドビュー （以降MV）は、通常のビュー+データテーブルです。 このアプローチは、外部テーブルと内部テーブルの結合、関数、設定、遊女などの複雑なクエリの場合に役立ちます。



MVの利益は次のとおりです。PGの「ネイティブ」オブジェクトです。つまり、MVはPostgreSQLの残りの部分と素晴らしく相互作用し、データソースに関係なくインデックスを作成して分析できます。 短所：MVを更新する必要があります。 内部トリガー、外部イベント、完全に再作成などによって更新できます。 しかし、いずれにせよ、MVはPGを元のデータソースよりも遅れさせます。



上記の外部テーブルMVの場合、次のように作成できます。

 CREATE MATERIALIZED VIEW materialized_mssql_table AS SELECT id, custom_data FROM mssql_table;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これで、MSSQLからのすべてのデータはPostgreSQLに格納されます。つまり、 B-tree 、 GIN、GiSTなど、好きなようにインデックスを作成でき、 統計が利用可能になります。



標準のINCERT / UPDATE / DELETEコマンドを使用してMVを更新するか、単に次を使用してすべてのコンテンツを再作成できます。

 REFRESH MATERIALIZED VIEW CONCURRENTLY materialized_mssql_table;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

CONCURRENTLYオプションを使用すると、競合する読み取り要求をブロックせずにMVを更新できますが、より多くの時間とリソースが必要です。 また、CONCURRENTLYを使用するには、ターゲットMVがいくつかの要件を満たしている必要があります。 それらは、 対応するドキュメントのページで見つけることができます 。

エキゾチックなアプローチ

正直なところ、悪魔はこのアプローチがうまくいくかどうかを知っています。おそらく、尊敬されている大衆はこの主題について何か面白いことを言うでしょう。 いずれにせよ、2つのデータベースの統合に関する質問のほとんどがコアリソースで「FDWを使用する」と回答されており、奇妙なものが必要な場合でも、多様性は期待されないため、それについて言う必要があると思います。



そのため、必要な場合：上記のすべてのオプションがさまざまな制限のために役に立たなかった場合。 例：

• 大量の外部データベースとPGでのクローニングの不可能性。
• 厳格なパフォーマンス要件と外部データベースへの最適なクエリの可用性。
• パラメータ化されたクエリ、つまりFDWのクエリオプションに類似した動的パラメータのみを実行したい場合。たとえば、 CONTAINS関数を介してMSSQL側でフルテキスト検索を使用したい場合。
• 何か他の珍しい。

使用するもの： dbi-linkまたはdblink-tds これらはdblinkに類似していますが、dbi-linkの場合はPostgreSQL、MySQL、MSSQL Server、Oracle、dblink-tdsの場合はTDSのみのいくつかのDBMSをサポートしています。



仕事の仕組みが見られます：渡された引数に基づいて必要なクエリを内部で収集し、上記のツールを介して外部データベースで実行し、データを受信し、処理してパイプライン関数としてPGに返すPG関数の形式のFDWの一種の非常に特殊なアナログとして。 つまり、仮説的には、希望する要求を正確に満たし、その結果をPGでの後続処理のために消化可能な形式で提示することが可能です。



上記はすべて、純粋な理論的考慮事項です。 これらのツールまたは類似のツールを実際に使用した経験がある場合は、知識を世界中の人と共有してください。

おわりに

現時点では、PostgreSQLとMSSQLを結合するための唯一のシンプルで実用的なソリューションがあります。 これはtds_fdwです。 多くの欠点がありますが、プロジェクトは開発中であり、バグは修正され、機能はロールアウトされており、素晴らしいです。 したがって、tds_fdwは、PGを介してMSSQLからデータを取得することに関連する問題のほとんどを解決できます。 迅速かつ最適に、遊女と一緒にやりたい人は、PostgreSQLとその豊富な最適化ツールを手伝うでしょう。 そして、非常に奇妙な何かを望み、最小限の外部サービスでデータベース内のすべてを実行したい人はきつい必要があります。 このツールキットは古く、ドキュメントもサポートもありません。ツールキットはロボットによって実装されており、ソースを読むこと以外は何も役に立ちません。