CData Software Blog

クラウド連携のCData Software の技術ブログです。

オンプレOracle DB にCloudGateway 経由でGoogleAppsScript からWEB APIでアクセスしてみた：CData APIServer

API Server Cloud Gateway REST オンプレミス Oracle Google Apps Script GCP

f:id:sennanvolar44:20200612233435p:plain こんにちは、CData の宮本です。

先日、弊社の技術ブログにて「オンプレミスの RDB から REST API を自動生成・API Server Cloud Gateway 経由でインターネットに公開」という記事が投稿されました。
www.cdatablog.jp

特に難しい設定もせず、ローカルのWindows マシンと Amazon EC2 だけで完結する構成でしたので、今回はそのGCP 版を実際に構築しながらご紹介していきたいと思います。
またタイトルにもあるように、最後は公開したAPI に Google Apps Script からアクセスしてみます。

CData API Server とは？

CData API Server は3Stepでお好みのRDBやNoSQL、クラウドサービスからREST ful APIを生成して、公開することができるソフトウェアです。

https://www.cdata.com/jp/apiserver/

f:id:sugimomoto:20200521180327p:plain

配置型のソフトウェア製品なので、基本的にはサーバーないしWebアプリをホスティングできる環境を用意して、そこにAPI Serverを構成し、アクセスできるようにする製品です。

f:id:sugimomoto:20200521180335p:plain

なので、インターネット上に公開したAPIを作成したい場合は、クラウドホスティング、オンプレミスの場合はDMZなどに配置する必要が出てきます。

そこで今回紹介する Cloud Gatewayの機能を活用することができます。

Cloud Gateway機能について

Cloud Gateway はクラウド上のSSHサーバーのリバースSSHポートフォワーディングを利用し、CData API Serverをインターネット上に公開する機能です。

http://cdn.cdata.com/help/BWE/jp/odata/Windows-Edition.html#cloud-gateway

f:id:sennanvolar44:20200612233435p:plain API Server がSSH Server と接続を確立した状態で、SSH Server がHTTP/Sリクエストをポートフォワーディングすることにより、API Serverをインターネットアクセスを可能にします。

これにより、ファイアウォールの変更、DMZへの配置、固定IPアドレス・ドメインといった要素を GCP などのクラウドサービス側に寄せることができ、セキュアにオンプレミスに存在するRDBや基幹システムをクラウド上で扱えます。

今回はこの機能をGCEの CentOS インスタンスで構成する方法を紹介したいと思います。なお、SSHサーバーはお好みのマシンで構いません。

必要なもの

  • CData API Server
  • Oracle（API Server の APIデータソースとして使います）
  • GCE（SSHサーバーとなるマシンであれば、AzureでもAWSでも）
  • Windows マシン（API Server を配置するオンプレミス環境です）

SSHサーバーとなるGCE インスタンスを構成する

まずはゲートウェイの役割を担うSSH Serverを GCE で構成したいと思います。 今回はCentOS7 の以下のインスタンスを使用しました。

f:id:sennanvolar44:20200612234911p:plain

環境が構成できたら、GCE の設定を調整します。右側の下矢印からブラウザウィンドウで開くをクリックしてGCE にログインします。
f:id:sennanvolar44:20200613001433p:plain

「sshd_config」に「GatewayPorts yes」を追加してください。デフォルトではコメントアウト状態で「#GatewayPorts no」になっています。

sudo vi /etc/ssh/sshd_config

f:id:sennanvolar44:20200613002339p:plain

以上で GCE 側の設定が完了となります。

オンプレミス側 Oracle Client のインストール

Oracle をデータソースにする場合、Oracle Client が必要となりますのでインストールを行います。
以下は CData Sync のOracle コネクタについてのヘルプになりますが、APIServer でも同内容を行います。
http://cdn.cdata.com/help/ASE/jp/sync/Oracle-Destination.html

オンプレミス側 API Server のセットアップ

続いて、オンプレミス側にAPI Server をダウンロード・インストールします。とりあえずローカルでのみ動く、ベーシックなAPIを構成してみましょう。Oracle は予めインストールされていることを想定しています。

以下URLからWindows版のAPI Server をダウンロード、インストールしてください。
https://www.cdata.com/jp/apiserver/download/

CData API Server にログインし「設定」→「接続」の画面に移動し、データソース一覧の中から「Oracle」を選択します。
f:id:sennanvolar44:20200613004911p:plain

Oracle への接続情報を入力後、接続のテストを行います。正常に接続できることを確認後、変更を保存をクリックします。
f:id:sennanvolar44:20200613005220p:plain

次にリソースを追加するために「設定」→「リソース」へ移動し、「リソースを追加」をクリックします。

f:id:sennanvolar44:20200613005545p:plain

接続先の一覧から先程登録したOracle-local 接続を選択すると
f:id:sennanvolar44:20200613005818p:plain
対象のテーブル一覧が出てきます。ここから任意のテーブルを選択して

f:id:sennanvolar44:20200613011434p:plain

あとは、保存をクリックすれば対象のテーブルがそのままREST APIとして生成されて公開されます。
f:id:sennanvolar44:20200613011520p:plain

APIページに行くと、テーブルリソースが追加され、API Reference を確認できます。
f:id:sennanvolar44:20200613011712p:plain

実際にエンドポイントにアクセスしてみると、GETリクエストが実行されて、データを取得できました。
f:id:sennanvolar44:20200613012254p:plain

Cloud Gatewayの構成

最後にCloud Gatewayの構成を行います。

タスクメニューのAPI Serverアイコンを右クリックして「Server Options」を選択します。
f:id:sennanvolar44:20200613012451p:plain

Server Options画面の「Cloud Gateway」タブから設定を行うことができます。

「Enable Cloud Gateway」にチェックを入れて、GCEに接続するために必要な情報を入力します。

Host：構成したGoogle Compute Engineの IP アドレスを入力します。
Port：デフォルトは22です。
Authentication Type：Public Key
User：GCPのアカウント
Certificate：GCE 接続用のpemファイルを選択します。

f:id:sennanvolar44:20200613013640p:plain

pem ファイルの生成から指定までは、以下記事の「SSHサーバーとなるGCE インスタンスを構成する」の章にある pem 形式の秘密鍵を生成する箇所を参考にしてください。

オンプレOracle DB にSSHサーバ経由でGoogleAppsScript からSQLでアクセスしてみた：SQLGateway - CData Software Blog


pem ファイルの指定ができたら、「Test Connection」をクリックし、SSH サーバーとの接続を確立します。
f:id:sennanvolar44:20200613015454p:plain

最後に「Service」タブに戻り「Run as a Windows Service」のチェックボックスをOFFにして、「Save Changes」をクリックします。クリック後、API Serverの再起動が始まるので、正常に起動すればOKです。（始まらない場合はStartボタンをクリックしてください）

f:id:sugimomoto:20200521180524p:plain

SSHサーバー（GCE）で CloudGateway のポートを開放

APIServer の CloudGateway では8153 というポートを設定しました。
f:id:sennanvolar44:20200614190119p:plain

これは外部からアクセスした場合に、SSH サーバーのGCE を通ってアクセスするようになるため、GCE で3311 ポートが使えるようにする必要があります。

ではさっそくポートの開放をしていきます。

GCP にメニューから VPCネットワーク → ファイアウォール の順にクリックします。 f:id:sennanvolar44:20200611174443p:plain

ファイアウォール ルールを作成をクリックします。 f:id:sennanvolar44:20200611174609p:plain

このファイアウォールの名前とターゲットタグを任意の名前で設定し、送信元のIP アドレスは今回は全て対象にするために 0.0.0.0/0 を設定しています。 次に、「指定したプロトコルとポート」とtcp を選択し、 CloudGateway で指定したポート番号をここに入力したら、作成ボタンをクリックします。

f:id:sennanvolar44:20200614190402p:plain

ファイアウォールが出来上がったら、そのファイアウォールの作成で設定したタグ名を GCE に設定します。
f:id:sennanvolar44:20200614190552p:plain

sudo systemctl disable firewalld
sudo vi /etc/selinux/config

f:id:sennanvolar44:20200611192232p:plain

これでSSH サーバ側の設定が完了しましたので、このタイミングでGCEを再起動してください。

ユーザーごとのAPI エンドポイント権限設定

APIServerで生成したエンドポイントにアクセスできるユーザーを作成していきますので、APIServer を開いた後、設定→ユーザー→追加でクリックします。

f:id:sennanvolar44:20200614193959p:plain

今回は test というユーザーを作成し、参照から更新系まで全て行える権限を付与しました。
f:id:sennanvolar44:20200614194442p:plain

ユーザーを作成すると、自動的に認証トークンが生成されます。後にGoogle Apps Scriptでアクセスする際に使用します。
f:id:sennanvolar44:20200614194611p:plain

長くなりましたがこれで設定が完了いたしました。

API Serverにアクセスしてみる

あとはGCEのホストアドレスにポート番号：8783を付与すれば、CloudGatewayがポートフォワーディング処理を実施して、API Serverへのアクセスを提供してくれます。

f:id:sugimomoto:20200521181129p:plain

また、デフォルトではBaseURLがローカルホストになっているので、API Serverのサーバー設定タブからGCE の BaseURLを指定しておきましょう。

f:id:sennanvolar44:20200614194833p:plain

Google Apps Script からオンプレDBにアクセスしてみる

データ取得

APIServer で生成したエンドポイント（以下の赤枠の順でクリックすると表示）を確認してGoogle Apps Script からアクセスします。
f:id:sennanvolar44:20200614214124p:plain

まずは案件データのうち、既に受注してあるデータのみを取得してみます。

const sheet = SpreadsheetApp.getActiveSheet(); 
var url = 'http://xx.xx.xxx.xxx:8153/api.rsc/SQLGATEWAY_DEAL_202006/';
var keys = {
  'RECORDID' : "",
  'ご担当者名' : "",
  '案件名' : "",
  '顧客名' : "",
  '受注確度' : "",
  '受注金額' : "",
};    

function getOnpreData() {
  try{
    var search = '受注';
    var options = {
      'method': 'get',
      'contentType': 'application/json',
      'headers': {
        'x-cdata-authtoken': 'xxxxxxxxxxxxxxx'
      }
    };
    var json = UrlFetchApp.fetch(url+"?$search="+search, options);
    var jsonData = JSON.parse(json);

    var value = jsonData.value

    var i = 1;    
    for (var idx in value) {
      var j=1;
      for(var key in keys) {
        var type = typeof value[idx][key];
        sheet.getRange(i, j).setValue(value[idx][key]);  
        j++;
      }
      i++;
    }
  }
  catch(e){
      Logger.log(e);
  }  
}

取得結果がスプレッドシートにセットされます。
f:id:sennanvolar44:20200614214751p:plain

データ登録

固定値ですが以下の値でOracleに登録します。

function postOnpreData() {
  try{
    var data = {
      'RECORDID': '100',
      'tantousya': 'CDataユーザ',
      'annkenmei': 'APIServer案件',
      'kokyakumei': "CData Software",
      'jyuchukakudo': '受注',
      'jyuchukingaku': '200000',
    };
    var options = {
      method : 'post',
      dataType: "json",
      payload : data,
      headers: {
        'x-cdata-authtoken': 'xxxxxxxxxxxxxx'
      },
    };
    UrlFetchApp.fetch(url, options);
  }
  catch(e){
    Logger.log(e);
  }  
}

上記コード実行後、データを再取得して追加されてることを確認してみます。
f:id:sennanvolar44:20200614225403p:plain

データ更新

担当者名を変更してみます。

function putOnpreData() {
  try{
    var data = {
      'RECORDID': '100',
      'tantousya': '佐藤 太郎'
    };
    var options = {
      method : 'put',
      dataType: "json",
      payload : data,
      headers: {
        'x-cdata-authtoken': '2i0S1m1b9X2j7o5B8o9t'
      },
    };
    UrlFetchApp.fetch(url, options);
  }
  catch(e){
    Logger.log(e);
  }  
}

再度データを取得してみると、担当者名が変更されていることがわかります。
f:id:sennanvolar44:20200614230035p:plain

データ削除

では最後に追加したデータを削除します。

function putOnpreData() {
  try{
    var RECORDID = 100;
    var options = {
      method : 'delete',
      dataType: "json",
      headers: {
        'x-cdata-authtoken': 'xxxxxxxx'
      },
    };
    UrlFetchApp.fetch(url+'('+ RECORDID + ')', options);
  }
  catch(e){
    Logger.log(e);
  }  
}

またデータを取得してみます。そうしますと、先ほど作成したレコードが表示されなくなりました。
f:id:sennanvolar44:20200614231708p:plain

おわりに

いかがでしたでしょうか。APIServer を用いることで、オンプレミスにあるデータベースを外部からセキュアにアクセスすることができます。
今回使用したOracle 以外にもMySQLやSQLServer など他の接続先についても併せて API を生成し管理することができます。

ちなみに、オンプレミスにあるOracle を Google Apps Script で SQL でアクセスする方法についての記事もありますので、よろしければこちらもご参照ください。

www.cdatablog.jp