LoginSignup
2
4

More than 1 year has passed since last update.

Google Apps ScriptAdvent Calendar 2021Day 8
@miyamotok

GoogleAppsScript からSSHサーバー経由でオンプレMySQLにつないでみた：CData APIServer

Last updated at Posted at 2021-12-08

この記事はGoogle Apps Script Advent Calendar 2021の8日目の記事です

はじめに＆やってみること

Google Apps Scriptでオンプレ DB のデータを使いたいケースはやはり多いでしょうか？私自身もよく耳にすることがあったりします。
はい、ということで、今回は Google Apps Script からオンプレ上の DB に CRUD してみるような内容をやってみます。とは言え、実現するためにローカル側に CData API Server、GCE 上に SSH サーバーを用意したりします。

CData API Server とは？

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

セルフホスティング型のアプリケーションなので、ローカルマシンでも良いですが基本的にはサーバーやクラウド上の VM を用意して使います。
image.png

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

構成

ローカル DB(MySQL) と GAS の間に SSH サーバーと APIServer を挟んだ構成をとります。

image.png

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

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

SSH サーバー自体はどこに配置しても構いませんが、今回はGASで使うということで GCE の CentOS で作成しました。

色々書いていますが、結構簡単にできます。

ではさっそく見ていきましょう。

必要なもの

  • CData API Server ※30日間の無償トライアルあり
  • GCE（SSHサーバーとなるマシンであれば、AzureでもAWSでも）
  • Windows マシン（API Server を配置するオンプレミス環境）
  • Teraterm ※鍵生成でのみ利用。ツールは何でも良いです

手順

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

まずはここの GCE インスタンスの構築です。
image.png
GCPの管理コンソールを開き、Compute Engine → VM インスタンスと進み、CentOS7 で VM を作成していきます。SSH サーバーはIP アドレスが毎回変わると、ローカル側からのアクセスが出来なくなってしまうので、固定IP をできれば取得しておきましょう。
image.png

ブラウザウィンドウで開くで SSH で作成した GCE にログインします。
image.png

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

sudo vim /etc/ssh/sshd_config

image.png
これでGCEの設定は半分終了です。一旦ローカル側の設定を行ったらまた戻ってきて設定しなおします。

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

image.png
それでは API Server を構築していきます。まずはローカル環境内でのみ動く、ベーシックなAPIを作成してみましょう。

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

CData API Server にログインし「設定」→「接続」の画面に移動し、データソース一覧の中から「MySQL」を選択します。※MySQL じゃなくても同手順で設定可です
image.png

MySQL への接続情報を設定したら、「接続のテスト」→「変更を保存」をクリックして接続情報を保存します。
image.png
次にリソースを追加するために「設定」→「リソース」へ移動し、「リソースを追加」をクリックします。
image.png
sakila にある actor というテーブル情報をもとに RSET API を生成します。
image.png

あとはそのまま進んでいけば、REST API の生成完了です。

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

実際にブラウザから生成したエンドポイントにアクセスしてみると、GETリクエストが実行されて、データを取得できました。
image.png

API Server と SSH Server の SSH 接続設定

APIServer、GCE それぞれで設定していきます。
image.png

認証鍵を生成し、公開鍵をGCPに登録

ツールは何でも良いですが、私は Teraterm を利用します。設定 → SSH鍵生成 の順にクリックします。
image.png

生成ボタンをクリックしたら公開鍵と秘密鍵を保存します。
image.png

保存したら公開鍵を開き、中身を全部コピーしたら GCP の管理コンソールに移動します。
移動後、GCE画面に行き「メタデータ」 → SSH 認証鍵 と進み、先ほどコピーした値をセットします。
image.png

API Server で Cloud Gatewayの構成

ようやく API Server と SSH サーバーの SSH 接続の設定になります。タスクメニューのAPI Serverアイコンを右クリックして「Server Options」を選択します。
image.png

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

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

  • Host：作成した SSH サーバーの IP アドレスを入力
  • Port：デフォルトは22
  • Authentication Type：Public Key
  • User：GCPのアカウント
  • Certificate：先ほど Teraterm で生成した秘密鍵 image.png

入力後、秘密鍵を指定するため「...」をクリックし、PEM タブにて生成した秘密鍵を指定したら OK ボタンをクリックします。
image.png
設定後、接続テストを行って OK であれば接続設定を保存します。
image.png

これでローカルの API Server と クラウドの SSH サーバーが接続できるようなりました。

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

GCP に戻ってAPIServerで設定したポートを開放していきます。

image.png
APIServer の CloudGateway ではデフォルトで 8153 というポートで設定されています。
image.png

このポートで外部からアクセスできるようにするために、SSH サーバー（GCE）で 8153 ポートが使えるようにする必要があります。

GCP メニューから VPCネットワーク → ファイアウォール の順にクリックします。
image.png
ファイアウォール ルールを作成をクリックします。
image.png
このファイアウォールの名前とターゲットタグを任意の名前で設定します。
送信元のIP アドレスは一旦全て対象の 0.0.0.0/0 にします。次に、「指定したプロトコルとポート」とtcp を選択し、CloudGateway で指定したポート番号（8153）を入力したら、作成ボタンをクリックします。
image.png
ファイアウォールが出来上がったら、そのファイアウォールの作成で設定したタグ名を GCE に設定します。
image.png
GCEのファイアウォール停止とSELinux をdisableにします。

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

image.png

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

APIServerで生成したエンドポイントにアクセスできるユーザーを作成していきますので、APIServer を開いた後、設定→ユーザー→追加でクリックします。
image.png
今回は test というユーザーを作成し、参照から更新系まで全て行える権限を付与しました。
image.png

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

最後の設定です。デフォルトでは BaseURL が未設定のためデフォルトのlocalhostになっていますので、API Serverのサーバー設定タブ → BaseURL を参照し、この形式http://localhost:8153/api.rsc/の localhost となってる部分を GCE の外部 IP に変更して設定しましょう。
image.png

長くなりましたがこれで設定が完了です！
では試しにブラウザから APIServer が生成したエンドポイントを叩いてみましょう。

ブラウザから APIServer のエンドポイントにアクセス

APIServer の API タブからリファレンスを参照します。
image.png
とりあえず今回は条件指定なしで全件取得してみますので、赤枠をコピーしてそのままブラウザのURLに設定して実行します。その後、ユーザー名とパスワードを聞かれますが、先ほどAPIServerで作成したユーザー名と生成された認証トークンを入力します。
image.png
データが取得できたので外部からオンプレのMySQLのデータにアクセスできることが確認できました。
image.png

接続に失敗する場合

接続に失敗する場合は以下の対応で解消できるか確認してみてください。

GCE 上で対象ポート(8153)が開放しているかを確認

sudo netstat -ltunp4
[miyamotok@sqlgateway-sshsrv ~]$ sudo netstat -ltunp4
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      904/master
tcp        0      0 0.0.0.0:3311            0.0.0.0:*               LISTEN      1092/sshd: miyamoto
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      874/sshd
udp        0      0 0.0.0.0:68              0.0.0.0:*                           510/dhclient
udp        0      0 127.0.0.1:323           0.0.0.0:*                           426/chronyd
[miyamotok@sqlgateway-sshsrv ~]$

表示されてない場合は、APIServer で使用するポートが空いていないことになるので、再度GCP のファイアウォールか、SELinux の無効化などを行い確認してみる。

Google Apps Script からAPIServer 経由で MySQL にアクセスしてみる

MySQL のデータをGASから取得

ではやってみましょう。以下は先ほどブラウザから実行したリクエストを同じ内容ですが、スプレッドシートに取得結果を書き込むことをやってみます。

const sheet = SpreadsheetApp.getActiveSheet(); 
var url = 'http://xxxxxxx:8153/api.rsc/sakila_actor/';
var keys = {
  'actor_id' : "",
  'first_name' : "",
  'last_name' : "",
  'last_update' : "",
};    

function getOnpreData() {
  try{
    //var search = '1';
    var options = {
      'method': 'get',
      'contentType': 'application/json',
      'headers': {
        'x-cdata-authtoken': '5x5C2l0i5V3j1d3J5g0k'
      }
    };
//    var json = UrlFetchApp.fetch(url+"?$search="+search, options);
    var json = UrlFetchApp.fetch(url, 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);
  }  
}

このように取得結果が反映されました。
image.png

MySQL のデータをGASから登録

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

const sheet = SpreadsheetApp.getActiveSheet(); 
var url = 'http://xxxxxx:8153/api.rsc/sakila_actor/';

function postOnpreData() {
  try{
    var date = new Date();
    var data = {
      'actor_id': '201',
      'first_name': 'CData',
      'last_name': 'APIServer',
      'last_update': '2021-12-08T04:34:33.0000+09:00'
    };
    var options = {
      method : 'post',
      dataType: "json",
      payload : data,
      headers: {
        'x-cdata-authtoken': '5x5C2l0i5V3j1d3J5g0k'
      },
    };
    UrlFetchApp.fetch(url, options);
  }
  catch(e){
    Logger.log(e);
  }  
}

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

MySQL のデータをGASから更新

first_name を変更してみます。

const sheet = SpreadsheetApp.getActiveSheet(); 
var url = 'http://xxxxxx:8153/api.rsc/sakila_actor/';

function putOnpreData() {
  try{
    var data = {
      'actor_id': '201',
      'first_name': 'GAS User'
    };
    var options = {
      method : 'put',
      dataType: "json",
      payload : data,
      headers: {
        'x-cdata-authtoken': '5x5C2l0i5V3j1d3J5g0k'
      },
    };
    UrlFetchApp.fetch(url, options);
  }
  catch(e){
    Logger.log(e);
  }  
}

データ再取得後、名前が変わっているのが確認できました。
image.png

MySQL のデータをGASから削除

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

const sheet = SpreadsheetApp.getActiveSheet(); 
var url = 'http://xxxxxxx:8153/api.rsc/sakila_actor/';

function delOnpreData() {
  try{
    var actor_id = 201;
    var options = {
      method : 'delete',
      dataType: "json",
      headers: {
        'x-cdata-authtoken': '5x5C2l0i5V3j1d3J5g0k'
      },
    };
    UrlFetchApp.fetch(url+'('+ actor_id + ')', options);
  }
  catch(e){
    Logger.log(e);
  }  
}

対象レコードが削除されました。
image.png

おわりに

色々設定箇所はありましたが、一つこの構成を作成してしまえば、Oracle や SQLServer など CData API Server に追加するだけで利用できるようになります。

2
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
4