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

by 宮本航太 | 2020年06月15日

こんにちは、CData の宮本です。

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

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

• CData API Server とは？
• Cloud Gateway機能について
• 必要なもの
• SSHサーバーとなるGCE インスタンスを構成する
• オンプレミス側 Oracle Client のインストール
• オンプレミス側 API Server のセットアップ
• Cloud Gatewayの構成
• SSHサーバー（GCE）で CloudGateway のポートを開放
• ユーザーごとのAPI エンドポイント権限設定
• API Serverにアクセスしてみる
• Google Apps Script からオンプレDBにアクセスしてみる
  • データ取得
  • データ登録
  • データ更新
  • データ削除
• おわりに

CData API Server とは？

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

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

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

なので、インターネット上に公開した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

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 の以下のインスタンスを使用しました。

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

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

sudo vi /etc/ssh/sshd_config

以上で 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」を選択します。

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

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

接続先の一覧から先程登録したOracle-local 接続を選択すると

対象のテーブル一覧が出てきます。ここから任意のテーブルを選択して

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

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

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

Cloud Gatewayの構成

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

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

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

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

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

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

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

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

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

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

APIServer の CloudGateway では8153 というポートを設定しました。

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

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

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

ファイアウォール ルールを作成をクリックします。

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

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

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

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

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

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

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

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


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

API Serverにアクセスしてみる

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

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

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

データ取得

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

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

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);
  }  
}

取得結果がスプレッドシートにセットされます。

データ登録

固定値ですが以下の値で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);
  }  
}

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

データ更新

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

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);
  }  
}

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

データ削除

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

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);
  }  
}

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

おわりに

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

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

https://www.cdata.com/jp/blog/2020-06-15-094755

関連コンテンツ