詳しくはこちら
 
Blog
 

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

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 でアクセスする方法についての記事もありますので、よろしければこちらもご参照ください。

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

トライアル・お問い合わせ

無償トライアルへ
お問い合わせ

関連コンテンツ

ブログ
API Server でRDB のデータをAPI として公開、Web 上で公開するまでを一挙に解説
詳しくはこちら
紹介動画
API Server の特徴と使い方を1分で紹介
詳しくはこちら
導入事例
三菱UFJ 様は、投資信託の関連情報の公開にCData API Server を開発運用基盤として採用
詳しくはこちら
製品資料
API Server 製品資料：機能やライセンス種類などを掲載
詳しくはこちら

製品をチェック

CData API Server

無償トライアルへ

業容拡大につき
一緒に働けるメンバーを募集中

募集ページへ

目次

×