社内で様々なアプリケーションのデータ記録場所として、Googleスプレッドシートを使っているわけなのですが、スプレッドシートには最大500万セルという制限があり(最近、200万セルから拡張されました)、またLockServiceで排他制御があるとは言え簡易的なものでしかありません。また、Google SpreadsheetとCloud Platformではサポートが異なるので、より安定してるCloud SQLは魅力的。Google Apps Scriptでのアプリ作成の幅がグンっと広がります。Google App Engineというものもあるのですが、GASではなく主にJavaで作成するものなので、あくまでGASでこれまで通り出来るという点が重要です。

今回は、用意したDBサーバや最近流行りのDBaaSサービスで数分で用意できるクラウドデータベースなどを保存先として、クライアントはこれまでのGoogle Apps Scriptを使うというやり方です。主にAmazon AWSGoogle Cloud SQLなどがそれらに該当します。GASからなので、素直にGoogle Cloud SQLのMySQL Serverを選択しました。

今回使用するクラス等やサービス

事前準備

データベースを使う利点

データベースはスプレッドシートと違って、課金されるサービスなのですが近年は単価も下がってきており、また何よりも大きなメリットがあるため、ビジネスでは普遍的に利用されています。主な利点は

  1. スプレッドシートと異なりSQLによる大容量・高速なデータの読み書きが可能です。
  2. リレーショナルデータベースなので、リレーションシップを利用したデータの整合性を常に取る事が可能です。
  3. 連鎖更新・連鎖削除・ロックなどDBならではの機能を利用して管理を軽減する事が可能です
  4. MySQLは様々なウェブサービスやDBaaSでサービス展開されてるので引っ越しが楽です。
  5. 接続元制限や接続メンバーの制限など細かな設定が可能です(Azure ClearDBでは出来ません)
  6. 他のスクリプトからでも容易にデータベースへアクセスが可能です。
  7. スプレッドシートのようなセル数での制限はなく、データベース容量での制限があります(後者のほうが遥かに広いです)。
  8. ベンダーロックインじゃないですが、Googleサービスに完全依存しない上でも利点があります。
  9. スマートフォンアプリ等への横展開がし易いです。スマフォ用の場合は、Firebaseを使うのも手ですね。
  10. もちろん、Accessから直接ODBC接続でつなげて運用が出来るので、AccessをクライアントにするのもGoodですね。
  11. G SuitesのFusion Tableが廃止されるので、移行先として使うのも良いかと思います。

利用料金と確認法

気になるGoogle Cloud SQLの利用料金ですが、わかりにくいです。今回の設定は

  • MySQL第二世代
  • 東京リージョン(asia-northeast1)
  • マシンタイプはdb-g1-small

この場合の料金になります。3つの課金がなされるのですが、起動におよそ15分は消費するので注意。

  • インスタンス 1ヶ月時間分の料金:1時間あたり「$0.0455(108円レートで4.914円)」* 24時間 * 31日 = 3,656円
  • ストレージ 1ヶ月分の料金:SSDの場合「$0.22/GB/月」なので10GBで「$2.2(108円レートで237.6円)」
  • ネットワーク :上りは無料。下りは「$0.19/GB」なので 1GBあたり「0.19円(108円レートで20.52円)」ここが可変なので注意

ネットワーク部分が要するにクエリをぶん投げてデータを拾ってくる場合の料金ですが、10GBも拾っても200円くらいなので、よほどオカシナ使い方しない限りは気にする必要無し。一番ボリュームが大きいのはインスタンスの部分。ここはおいそれと止められないので、トータルで最低でも「3,914円/月以上」は掛かることになります。MySQL第一世代のほうがパッケージプランがあって安そうだ。

インスタンスが本番使用での標準の「db-n1-standard-1」ならば、$0.0878なので換算値で7,054円は標準的に掛かるという事。安くなったとはいえ、まだまだ高いですねぇ。SakuraでVPSでMySQL借りて運用したほうが安いなぁ。まぁ、セキュリティやバックアップ、MySQL構築の手間とか考えたらってのはあるんですけれどね。Nifty Cloudもいいかもしれない。

※とは言え、G Suite上で作ってユーザに入力だけしてもらうならば、こちらは固定費で済むので、利用者数が多いなら、G Suiteアカウントではなく、専用アプリ用意して使わせたほうが、トータルコストは安く済む計算(G Suite Basic 12名分の料金で済むので、ソレ以上の利用者がいるならば)

※Azureに以前あったClearDBというMySQLサービスは削除されてしまい、現在はcleardb.netというサイトに分離独立してしまっています。今まで使っていた人は、支払い情報やアカウントについては、こちらのサイトのように自分で手続しなければならないのです。また、Azureには2018年10月にMariaDBプレビューながら追加されているので、これに置き換えたようですね。

料金の確認は以下の手順で確認可能です。

  1. 「≡」というアイコンが左上にあるので、これをクリックして「お支払い」をクリック
  2. 請求アカウントへ移動をクリック
  3. 概要には紐付けられている課金プロジェクトが出てきます。初期ボーナスの30,000円分の残りクレジットなどはここで確認できます。
  4. 料金の履歴をクリックするとこれまで課金された金額がわかります。
  5. 予算とアラートを作成しておくと、予算到達時にアラートが飛んできます。

Google Cloud SQLを使う場合

プロジェクトを移動

今回の発表直前の2019年4月8日より、Google Apps ScriptからCloud Platform Projectへ直接アクセスが出来なくなりました。これまでにデプロイしてるものについては、これまで通り「リソース」⇒「Google Cloud Platform API ダッシュボード」からアクセスが可能です。

今回の変更はスプレッドシート上で動かすスクリプトやGoogleの拡張サービスを利用しないタイプのスクリプトであれば特に問題はありませんが、「Apps Script API」や「Google Picker API」、「Cloud SQL接続」などGCP上のAPIを利用する場合には以下の手順を踏んで、Google Apps Scriptにプロジェクトを連結する必要があります。これまでは、自動的にGCP上にGoogle Apps Script用のプロジェクトが生成されていたのですが、今後は自分の組織(もしくはGCPプロジェクト)上で作成されたプロジェクトでなければならないということです。詳細はこちらのページを見てください。

連結する手順は以下の通り

  1. Google Cloud Consoleを開く
  2. 左上にある▼をクリックする
  3. ダイアログが出てくるので、新規プロジェクトを作るか?既存のプロジェクトを選択する。この時、G Suiteであれば選択元は「自分のドメイン」を選択する必要があります。
  4. プロジェクト情報パネルから「プロジェクト番号」をコピーする
  5. 対象のGoogle Apps Scriptのスクリプトエディタを開く
  6. 「リソース」⇒「Cloud Platform プロジェクト」を開く
  7. 4.で入手した番号をプロジェクトを変更のテキストボックスに入れて、プロジェクトを設定ボタンをクリックする
  8. 無事に移動が完了すればメッセージが表示されます。
  9. この時、元の自動作成されたプロジェクトはシャットダウンされて消えます。これで設定完了です。

今回のこの変更だと1つ作ったプロジェクトに集約する必要があるので、クォータについてプロジェクト毎のカウントだったので問題なかったものが、集約されることで、クォータに引っ掛かる可能性があります。

図:プロジェクト番号をコピーしておきます

図:プロジェクトを他のプロジェクトに紐付けしました。

図:GCPの拡張サービスを使うには手順が必要になった

APIの有効化

Google Apps Scriptから使うためには、Google Cloud Platformでいろいろ作業を行わなければなりません。以下の手順でAPIを有効にしましょう。

  1. スクリプトエディタに入って、メニューより「リソース」⇒「Googleの拡張サービス」を開きます。
  2. ダイアログ下にある「Google Cloud Platform API ダッシュボード」をクリックします。
  3. APIとサービスの有効化をクリックします。
  4. 検索窓より「Cloud SQL」を検索しクリックします。
  5. 有効化のボタンをクリックします。
  6. これで、API自体は有効になりましたが、まだ使えません。

図:APIをまずは使えるようにしておく

 

図:実行承認時にCloud SQLのスコープが出てくる

インスタンスの作成

APIを有効化したら、しばらく待つとデータベースのインスタンスというものが作れるようになります。「≡」というアイコンが左上にあるので、これをクリックして、左サイドバーを開きます。

  1. 下のほうにある「SQL」という項目を開きます。
  2. インスタンスを作成をクリックします。
  3. MySQLとPostgreSQLのどちらかを選ぶことができます。ここはMySQLを選んでおきます。
  4. インスタンスIDには適当なインスタンス名を付けます。変更は不可。ここでは、「rurikosan」と名付けました。
  5. rootパスワードはこのインスタンスに接続するマスターパスワードです。忘れないものを入力しましょう。
  6. リージョンですが、現在は東京リージョンが存在します。「asia-northeast1」を選択します。遠くのリージョンを選ぶと通信が遅くなります(一方でネットワーク単価は下がりますが・・・)
  7. ゾーンは東京リージョンのどのサーバを使うかを選ぶものですが、適当に選んでおきます。asia-northeast1-cを選んでおきました。
  8.  まだ、作成はクリックせずに、設定オプションを表示をクリックします。
  9. マシンタイプとストレージの設定」を開き、変更をクリックします。
  10. ここでは、マシンタイプを選ぶのですが、選ぶマシンタイプで料金が全然変わってきます。ハイスペックほど高い料金が掛かります。通常は、「db-n1-standard-1」の1CPU、RAM:3.75GBを選べばとりあえずは良いのですが、今回はテスト開発なので、「共用のdb-g1-small」を選びました。CPU共用でRAM:1.75GBです。
  11. ストレージの種類は、HDDのほうが安いのですが、それほど大きな値差があるわけでもないようなので、高速なSSDを選びました。
  12. ストレージ容量は最低が10GBからなので、それで行きます。増えればそれだけ料金は掛かります。
  13. ストレージの自動増量を有効化をしておくと、ストレージがいっぱいになっても自動でスケールするようになります。今回はオフにしてあります。
  14. 次に、「自動バックアップの有効化」をクリックします。今回はテストなので不要。ですので、チェックを外してあります。通常は付けたままで良いでしょう。
  15. これで、保存をクリックすると、インスタンスが作成されます。結構時間が掛かるので待ちます。

図:インスタンスを作成開始

図:今回はMySQLを選びます

図:インスタンス情報を設定します。リージョンはasia-northeast1を

図:調子に乗ってハイスペック選ぶとエライことに

図:ストレージはあまり気にしなくて良い

図:本番環境ではバックアップは有効にしておきましょう。

作成後の設定を施す

さて、これでインスタンスも立ち上がり、MySQLは稼働しているのですが、まだやることがあります。データベースの作成と、ローカルからMySQL WorkBenchで接続出来るようにIPの許可を追加します。

  1. 接続タブをクリックする。
  2. パブリックIPにある「承認済みネットワーク」に追加するIPアドレスを、確認くんで調べておく
  3. ネットワークを追加をクリックして、2.のIPアドレスと名前をつけて保存する。企業などからの場合には、プロキシーのアドレスなどを入れると内部 or VPN経由からじゃないと接続させないように出来ますね。
  4. SSL接続のみを許可にするとGASからアクセスができなくなるので注意。
  5. ユーザタブをクリックする。
  6. rootが登録されていますが、それとは別にユーザアカウントを作っておきましょう。ユーザアカウントを作成をクリックして登録しておきます。
  7. データベースタブをクリックする
  8. 適当なDBを作る。今回は、gas_dbという名前でデータベースを作りました。文字コードはutf-8でオッケー
  9. これですべての準備が整いました。
  10. 概要タブに戻り、「このインスタンスに接続」に記述されている「IPアドレス」と「インスタンス接続名」を控えておきます。IPアドレスはMySQL Workbenchから接続する際に使用し、インスタンス接続名はGoogle Apps Scriptから接続する際に使用します。

図:DBは予め作っておきましょう。

図:接続情報は非常に重要です。

図:IP接続許可しないとローカルから接続できません

Cloud Shellで作業をする

Google Cloud Consoleには、Cloud Shellという標準でターミナルが用意されています。画面右上にある左端にあるアイコンがそれ。クリックすると、画面下部にターミナルが起動します。

今回は、rurikosanというインスタンスに接続して、rootアカウントで入っています。Enterで実行するとしばらく接続まで待ちます。Passwordを聞いてきたら、入力。すると、MySQLへログインが完了し、以降は、コマンドラインでMySQLを操作可能になります。quitコマンドで終了です。

※あらかじめ、新しいネットワークで自分のIPアドレスを登録しておかないと接続できませんので注意。

図:Cloud Shellで操作がその場で出来ます。

MySQL Workbenchで接続・作業

取得した情報を元にMySQL Workbenchにて作業をします。コンソールが使える人はそちらで作業をしても良いでしょう。MySQL WorkbenchはGUIでテーブルの作成や設計、データの入力等が可能な非常に良いツールで、Windows/Mac/Linuxで使える優れものです。ポートフォワーディングなどを利用すれば、レンタルサーバのMySQLも操作が可能です。

※MySQL Workbenchは無償のツールですが、OracleのIDが必要です。

  1. 新しい接続をつくり、取得したIPアドレス、ユーザID、パスワードでログインします。
  2. DB名も入れておいたほうが良いでしょう。今回は、gas_dbという名前で作っています。default_schemeに入れます。
  3. そして接続してみましょう。
  4. 下記の図のような感じの画面が出たら成功。右下のSCHEMESで作業をします。
  5. Tablesで右クリックして、Create New Tableでテーブルを作ります。
  6. AccessのようにGUIでテーブル設計が可能ですがフル英語ですので気合をいれましょう。今回は5個のカラムでIDのみAuto Incrementを入れています。テーブル名はjinjiとしました。
  7. Applyボタンでテーブルが作成されます。再度テーブル設計をする場合はAlter Tableで入れます。
  8. 出来たテーブルを右クリックして、今度はselect rowsをクリック実行
  9. この画面は直接テーブルに値を入れることが出来ます。入れたらApplyボタンを忘れずに。IDはAuto Incrementが指定されてるので、空でも自動で数値が入ります。
  10. これでテーブルの準備も出来ました。

図:無事にローカルからCloud SQLへ接続できた

図:Cloud SQLへテーブルを作ってる様子

Google Apps Scriptから接続

JDBCを使うに当たって

JDBCサービスの制限はダッシュボードから確認できますが、以下のような感じになっています。以下の制限に引っかからないように注意しながら、運用する必要性があります。それほど大規模な人数でなければ、制限に引っかかることはないとは思いますが。

操作一般ユーザーGoogle Apps 無償版G Suite
JDBC 接続10000 個/日10000 個/日50000 個/日
JDBC 接続の失敗100 個/日100 個/日500 個/日

データベース接続用URL

Google Apps Scriptでは、JDBCでコネクションを張るときに接続文字列、ユーザID、パスワード、接続先DB名が必要になります。自分が立てたMySQLサーバや他のクラウドデータベースの場合と違い、Google Cloud SQLのMySQL接続時には、概要タブで取得した「インスタンス接続名」を利用します。

Jdbc.getCloudSqlConnection(“jdbc:google:mysql://インスタンス接続名/接続DB名“, ユーザID, パスワード);

getCloudSqlConnectionメソッドを利用し、接続文字列の後に、上記の通り、インスタンス名、接続DB名といった形がつなげると接続が可能になります。接続インスタンス名は、主に以下のような感じの文字列です。

プロジェクト名:リージョン:インスタンスID

ここが一番苦労しました・・・ちなみに、Google Cloud SQL以外のサイトのMySQLへ接続する場合には、以下のような形になります。

Jdbc.getConnection(‘jdbc:mysql://yoursqlserver.example.com:3306/database_name‘, {user: ‘username‘, password: ‘password‘});

getConnectionメソッドを利用し、サーバーアドレス、DB名、ユーザID、パスワードと繋げて接続します。メソッドと接続用文字列が異なるので、注意が必要です。

ソースコード

今回はCloud SQL側に1枚だけテーブルを作ってありますので、これに対して1行新規データを追加、またテーブルの中身を取得してAlertで表示するといったものを作ってみたいと思います。まずは現在テーブルに入ってる情報を抜き出して見たいと思います。

セッティング関係のコード

GAS側コード

  • インスタンス名や接続DB名などを格納したり、取り出したりするルーチンです。
  • サイドバーから初期設定をするようになっているので、利用する場合にはメニューの「作業用」⇒「DBの設定」を開くと実行されるので、セットしてから利用しましょう。
HTML側コード

テーブルを作成する

  • Cloud SQLのMySQLへjinjiというテーブルを作成します。
  • 各種設定値はスクリプトプロパティより取得しています。
  • instanceUrlは以降のスクリプト共通のグローバル変数です。

テーブルデータを取得しスプレッドシートに追記

  • 料金が怖いので、現在このコードは、stmt.setMaxRows(20);にて20件の取得に留めています。
  • データは洗い替えで取得しますので、現在のスプレッドシートの値はクリアされます。
  • 取得に掛かる時間を計測してアラート表示します。
  • 実際には、「SELECT * FROM jinji」なんて大雑把なやり方ではなく、様々な抽出条件を加えて必要なテーブルの必要なカラムだけ絞って取得を行いますが、今回は大したデータ量でもないためこのような書き方をしています。
  • また、サービスによってはDBではなくキャッシュからであれば安価にデータを取得できるといったようなものもあるため、自分で使用するDBaaSサービスの内容をよく把握してから、クエリは投げましょう。
  • 接続する部分で変数connにデータベース接続用URLを構築し、stmt.executeQueryにてクエリ発行をしています。これでデータの塊が取得できたので、配列に1つずつ収めて、スプレッドシートに追記するといった形を取っています。
  • 最後にかならずデータベースはクローズしましょう。時間課金の場合余計な時間消費の原因になったりします。使ったらさっさと閉じるというのがお約束です。(いちおうGASでは関数が実行終了と共に自動クローズされるようになってはいるみたいですが)。

データベースにレコードを追加する

次にHTMLサービスで作成したフォームに情報を入れて、このDBへデータを1行追加するというのをやってみたいと思います。スプレッドシート上でHTMLサービスで作成したダイアログに入力後、送信を押すとCloud SQLのMySQLへデータがインサートされるという単純な仕組みです。

GAS側コード

  • 別に用意した入力フォームから呼び出して利用しています。
  • Insert Intoにてレコードを一個追加しています。スプレッドシートに記述しても追加はされたりしません。
  • HTMLサービスのダイアログの値を取得して、query文のInsert intoに続けて値をはめ込むコードです。ハマりどころは必ずDB名を指定しなければならないので、テーブル名だけでInsert Intoを実行するとエラーになります。
  • IDを入れていませんが、DB側でAuto Increment指定をしていないと、やはりエラーになるので、主キーになるIDはAuto Incrementするか?IDは独自に重複しないように生成する必要があります。
  • setStringで1個目〜4個目のパラメータにはめ込んでゆきます。そして、executeでインサート実行です。insert intoはprepareStatementというメソッドを使用する点も注意して下さい。
HTML側コード

  • 入力用のダイアログのHTMLです。
  • 登録実行をすると、mysql_insertに値なげて、実行します。

図:UIフォームはじっくり作り込みましょう。

データベースのレコードを更新する

次にレコードを更新をする事例です。。今回はIDが1の人間役職名を変更するというシーンを想定してコードを記述してあります。よって、UPDATE文だけでなくWHERE句も出てきます。

  • INSERT INTOとほぼおなじスタイルですが、SQL文が異なります。UPDATE文を使用します。
  • SET JOBにて役職フィールドに値をセットしています。WHRER句でIDを限定しています。
  • それぞれ、をsetStringsetIntで値を入れると、IDが1の人間の役職が平社員から職員へと変わりました。WHEREで制限をしなければ全員変わることになります。
  • stmt.setString(1,”職員”);、stmt.setInt(2,1)が肝です。SQL文の1個目の「?」に「職員」をString型でセットするという意味。これで、ID=7がSQL文にセットされるわけです。つづけて、SQL文の2個目の「?」に1をInt型でセットし、これで、IDが1のものに対して、JOBを職員にするという意味になります。

データベースのレコードを削除する

データベースのレコードを同じくWHRER句で条件をつけて削除します。今回はIDが7の専務を消し去りたいと思います。POSITIONで専務としても良いのですが、それだと無関係の専務まで消えてしまうので注意してください。あくまでIDが7の魔王専務です。WHRER句はANDをつけて2つ条件をつけても良いでしょう。

  • 実行すると、IDが7の専務が消えてくれます。このようにSELECT, INSERT INTO, UPDATE, DELETEの4つが主なデータベースの操作になります
  • stmt.setInt(1,7);が肝です。SQL文の1個目の「?」に7をInt型でセットするという意味。これで、ID=7がSQL文にセットされるわけです。複数ある場合には、複数セットする。7を変数に置き換えれば動的に変更が出来ますね。
  • 他にもテーブルの作成や削除などといったものもGoogle Apps ScriptのJDBC Serviceには用意されているので、使いやすいようにラッピングした関数などを用意しておくと良いでしょう。

Access to Google Cloud SQL

手元にあるAccessアプリケーションからも接続出来るようにし、Google Apps ScriptではなくAccessをクライアントにして運用ができたら、手持ちのアプリを簡単にクラウド化する事が出来るのではないか?と思い、チャレンジしてみました。

MySQL Migration Toolkitで変換

MySQL Migration Toolkitはすでに開発終了済みで古いツールなのですが、AccessデータベースをMySQLのテーブルに変換し送り込むツールです。使用する為には、旧Java SE 5.0が必要なので、そちらも別途インストールが必要。また、インポート時に型や文字コードを自動判定するのですが、失敗するので、適当に進めず文字コードなどはとくにしっかり指定しておきましょう。。

  1. あらかじめaccdbファイルをmdbファイルへ変換して作っておく
  2. MySQL Migration Toolkitにてmdbを指定
  3. 接続先にはGoogle Cloud SQLのIPアドレスとID、パスワードを指定。
  4. 主キーのないテーブルは変換に失敗します。
  5. そのままポチポチ適当に進めていくと、無事に接続完了。インポートが始まる。
  6. 途中のObject Mappingに於いて、Migration of Type schemeは「Multilanguage」、Migration Type Tableは「Data Consistency/Multilanguge」にしないと、テーブルの文字コードが一部Latin1になってしまうので注意。(10.がそれです)
  7. インポートが完了したら、データの転送が開始されるのだが、ここはなぜか失敗していた。
  8. 完了
  9. ただし、一部の列の型がおかしな型になっていて、JANコードなどの大きな数値が入らなかったので、手動で型変換をしてあげました。こればかりは、避けられないので、データ転送が失敗するのはこれが原因です。
  10. また、Incorrect string valueというエラーが入力時に出るので、見てみたらdatabaseの文字コードがlatin1になってた・・・データ転送が失敗したのはこういうのが原因ですね・・・schema editorからUTF8に変更しました。
  11. テーブルのVarchar型のフィールドも「スウェーデン語」の文字コードになっているので、こちらもUTF8に変換してあげましょう。。。。
  12. もう一度同じ作業をすると上書きでインポートされます。

MySQL WorkbenchでGoogle Cloud SQLの中身を見てみたところ、データは空っぽでしたが、無事にテーブルは出来ていて、きちんとテーブル定義も変換して取り込めていました。テーブル定義が非常に面倒なので、これだけでも非常に助かるツールですね。Windows版しかありませんが。

図:mdbファイルのテーブル構造をインポート出来る

図:データの転送は失敗しました。

図:テーブル定義はばっちりインポート完了

図:文字コードがスウェーデン語になってた・・・

図:データーベース自体、UTF8に変更してあげました。

図:テーブルおよびvarchar型の全フィールドの文字コードも変更が必要

図:本当はこの画面できちんと文字コード指定してあげないと駄目

AccessからODBC接続してみた

手持ちのAccess2013よりODBC接続でテーブルをリンクできれば、データはGoogle Cloud SQL、開発はコレまで通りAccessで可能になるのではと、MySQL Connector ODBCドライバをインストールし、設定をしてリンクテーブルでつなげてみました。なお、5.3 DriverはVisual Studio 2013 の Visual C++ 再頒布可能パッケージに依存しているので、それもインストール済みでなければインストールできません。

※「作成後の設定を施する」にて、接続元IP許可に自分のアクセス元のIPアドレスを登録しておく必要があります。

  1. MySQL Migration Tool kitにドライバが入ってるのでドライバインストールはスキップ
  2. Access 2013を起動する
  3. 外部データタブを開く
  4. インポートとリンクに「ODBCデータベース」があるのでクリックする
  5. リンクテーブルを作成しソースデータにリンクする」を選んで次へ
  6. データソースの選択画面では、コンピュータデータソースを開く。
  7. まだ作ってないので、新規作成ボタンをクリック
  8. データソースの新規作成では、ユーザデータソースを選ぶ
  9. MySQL ODBC 5.3 Unicode Driverを選択
  10. 次へ進んで完了する
  11. 接続画面が出るので、Google Cloud SQLのIPアドレスとIDおよびパスワードを入力。
  12. 11の画面はコントロールパネルの管理ツールにあるODBCデータソースアドミニストレーターにあるので再編集が可能です。
  13. データソースの選択画面に戻ったら、作ったソースを選んでOKを押す。
  14. 接続に成功すると、テーブル一覧が出てくるので、選べばリンクテーブルとしてAccessに登録されます。

図:MySQL Driverが登録されている

図:ODBCドライバからの接続画面

図:ユーザDSNとしてMySQLでの接続設定が出来た

図:無事にAccessから接続完了。

図:きちんとデータを入力できました。

Cloud SQL Proxyにてポートフォワーディングさせる

AccessでODBC接続させてデータの取得には成功しました。これを実運用する場合、企業などのように「プロキシーを利用した固定IP」が接続元ならば、それらのIPを登録すれば問題ありません。必ずユーザはプロキシーを経由するからです。

しかし、そうではない環境の場合、接続元のIPアドレスは固定ではなく、場合によっては特定のアドレスレンジまるごと登録といった雑な許可をする必要があります。そこで用意されているのが、Google Cloud SQL Proxyを利用した接続です。この方式の場合、DBへはグローバルIPアドレスではなく、ポートフォワーディングされたlocalhostで接続が可能になります。クライアント側のIPアドレスが変動しても問題なく、継続して接続が可能になります。接続手順は以下の通り(今回はWindows版で説明します)

※Linux, OSX, Windowsのそれぞれ32bitと64bit用にプロキシープログラムが用意されています。

※Cloud SDKを利用した方法もあるのですが、PCにPythonがインストールされている必要があるため、手間が多いので今回はやめておきました。専用のサーバでも用意する場合には良い選択肢かもしれません。

サービスアカウントの作成

  1. Google Cloud Consoleにて該当のプロジェクトの「IAMと管理」に入ります。
  2. サービスアカウントをクリックします。
  3. 上部にある「サービスアカウントを作成」をクリックします。
  4. 適当なアカウント名を入れて、作成ボタンを押します。
  5. 役割は通常は「編集者」でオッケーです。閲覧のみも可能。続行ボタンを押して続けます。
  6. 次に、キーの作成ボタンをクリック
  7. キータイプを問われるので「JSON」を選択します。
  8. 秘密鍵がダウンロードされます。これを適当なフォルダに保存します。大切なものなので、流出しないように!!

プロキシープログラムの設定と実行

サービスアカウントの作成とキーを手に入れました。続けて、cloud_proxyのプログラムをダウンロードし設定・実行します。キーはこのプログラムの中で利用します。

  1. Cloud SQL Proxyのページにおいて「プロキシーをインストールする」にて、Windows用のexeをダウンロードします。ファイル名はcloud_sql_proxy.exeと変更しておきます。これを適当な場所に保存する。今回はマイドキュメント直下にしました。
  2. コマンドプロンプトを立ち上げる
  3. マイドキュメント直下にcmdにて移動する
  4. あらかじめ、前項8.の秘密鍵のファイルのパスを調べておきましょう。
  5. Cloud SQLのインスタンスのページにて、該当のインスタンスに入り、インスタンス接続名を控えておきます。
  6. 以下のコマンドを実行します。プロキシーが起動し常駐します。
  7. 127.0.0.7:3306にODBCで接続が可能になります。もちろん、MySQL Workbenchもこのlocalhostへ接続が可能になります。
  8. Accessなどからこの処理を自動化する場合には、accdbのカレントディレクトリにcloud_sql_proxy.exeおよび秘密鍵のJSONを配置し、shellを実行するコードを実行すれば良いと思います。ファイルのパスも簡単にCurrentProject.pathで取得できますし。

図:接続ログも表示されます。

Accessで起動からODBC接続までやらせてみた

cloud_sql_proxy.exeおよび秘密鍵のJSONファイルを元にMySQL ODBC 5.3 Unicode Driverにて、DSNレスで接続させてみました。Autoexecマクロで、Accessファイル起動時に実行させれば、自動的にプロキシー起動⇒ODBC接続でリンクテーブル作成が可能になります。ただしこのコードには以下の課題があります。

VBAからのコマンドライン実行は

  1. 起動時にとりあえずリンクテーブル全削除コードを入れる必要がある。
  2. データベース接続用のIDおよびPASSワードを入力させるフォームが必要(現在はVBAに直書きであるため)
  3. 2.を自動でやるならば、VBAからPowershell経由で資格情報マネージャにPasswordを読み書きさせるコードを記述する必要があります(VBAから直接、資格情報マネージャは触れない為)
  4. できれば、インスタンス接続名も資格情報に格納できたら最高だ。
  5. 終了時にコマンドプロンプトどうやって終わらせようか・・・

とりあえず、簡単なコードは以下の通り。MySQL ODBC 5.3 Unicode Driverを利用していますが、DSN設定を利用せずにリンクテーブルを作成しています。

他のデータベースに接続する

今回はGoogle Cloud SQLをテーマに外部のデータベース接続についてこれまで記述しました。しかし、Google Apps ScriptのJDBCサービスでは他にもMicrosoft SQL Server、Oracleに対応しています。これらは前項で紹介したgetConnectionにて接続用URLを渡せば、操作する事が可能です。

JDBCサービスからのアクセスは以下のIP範囲からのものを、相手方DBサーバで許可(ホワイトリスト)してあげる必要があります。

また、MySQLからフォークして作成されているMariaDBやCloud SQLにあるPostgreSQLについては、JDBCサービスではサポートされていません。stackoverflowでは、Google Apps ScriptからPostgreSQLに関しては、REST APIアクセス化する為のPostgRESTというものがGithubにて公開されています。GASからはUrlfetchAppでアクセスする方法を使うようです。しかし、UrlfetchAppは結構使用制限の厳しいものなので、素直にMySQLのレンタルを選んだほうが幸せになれると思います。

其の場合、GASからのアクセスはねこの足跡Rさんのページにて、IP範囲についてのエントリーが紹介されています。

MariaDBについては、MySQLからフォークされているものの、お互い最新版では互換性が薄れています。其のため、Google Apps ScriptからMySQLとして「MariaDB」を使う場合には、MariaDBのバージョンは5.5である必要があります。これは、MySQL WorkbenchでMariaDBに接続する場合も同じです。

ポイント

  • DBaaSサービスは時間や転送量などに応じての従量制課金サービスなので、SQLなどを発行する場合には、その時必要なレコードだけに絞って取得するようにしないと、どんどん課金されるので注意。
  • その為、Googleスプレッドシートなどを土台にしたアプリケーションを作ってる感覚で作ってはいけません。先にDBに投げるに当って必要な情報をすべて用意し、必要な情報に絞った状態でデータの取得をするように心がけましょう。
  • データベースシステムですので、データの要求はSQL言語を扱える必要があります。
  • GROUP BY句やHAVING句、JOIN句, ORDER BY句、DISTINCT句、サブクエリ等覚えることはたくさんありますが、AccessやスプレッドシートのQUERY関数などで予行演習をしておくと良いでしょう。
  • 今回のプログラムは、DB接続パスワードやユーザIDがそのままスクリプト内にあるので、ここはログインフォームを作って対応が必要です。この辺のうまい仕組みはまた今後考えてみたいと思います。
  • また、ユーザIDに対しては、操作できる範囲を絞るよう設定をしましょう。

図:ユーザの権限、テーブル範囲を絞っておく

関連リンク

共有してみる: