Google Apps ScriptからCloud SQLへの接続を再検証【GAS】
2019年頃にちょこっとだけ、Google Apps ScriptからGoogle CloudのCloud SQL(MySQL)に接続するエントリーを書きましたが、大分たってしまい、リライトするのも大変なので、改めて現時点に於いて、GASからCloudSQLのMySQLに接続する上での手順やパフォーマンス、活用方法などで新規にエントリーを書くことにしました。
だいぶ変わってる点があるため、注意点等含めて、複数名で活用するアプリを作ってみたいと思います。
今回利用するサービス等
2023年時点でGoogle Apps ScriptはそれまでのMySQL5.7のみ接続可能な状態から、MySQL8.0への接続にJDBCサービスが対応したようで（Release Noteには記載が無い）、今回はMySQL 8.0でインスタンスを立ち上げてそこに対して読み書きをしようと思います。
なお、過去のMySQL 5.7時代のエントリーについては以下の内容になりますが、現在とは大分変わっているため、あまり参考にならないかもです。
事前準備
Cloud SQL側の準備
2019年頃と大分変わってる点があります。それらを中心に抑えると共に、データベースの作成までを一気に進めます。また、この時このCloudSQLをセットしたGCP側プロジェクトのプロジェクト番号を控えておきます（GAS側でプロジェクト移動で利用します）
現在は、MySQLだけでなくPostgreSQLやSQL Serverも選択出来るようになっています。CloudSQLではないですが、Compute Engine上ではOracleが動くようになったみたいです。
APIの有効化
現在のCloudSQLは、土台としてCompute Engineを利用しているらしくいくつかのAPIを有効化する必要性があります。構築途中でも訊かれるので、指示にしたがってGCP側のAPIを有効化します。ただし、Compute Engineを利用してるとは言え、Compute Engineで仮想環境を構築してるわけではないので、そちらを見てもリストには出てきません。
- Google Cloudにログインする
- 選択中のプロジェクトのリンクをクリックして利用するプロジェクトを選択します
- APIとサービスを開く
- APIとサービスを有効にするをクリックする
- 「Compute Engine」および「Cloud SQL」を検索し、それぞれのAPIを有効化します。
図：APIとしてCompute Engineを使うようだ
インスタンスの作成
インスタンス作成画面の手順がいくつか変わっています。以前はGASからはMySQL 5.7しかアクセス出来なかったのですが、現在はMySQL 8.0でもアクセスが可能になっているため、今回は8.0にてインスタンスを作成します。
※ちなみにこの画面はAdblockやuBlock Originが有効だと保存のボタンがクリック出来ないので要注意。
- GCPのプロダクト一覧画面にて、データベース=>SQLを見つけてクリック
- インスタンスを作成ボタンをクリックします。
- MySQLを選択をクリックします。
- インスタンスIDには適当な文字を入力します（今回はgastankとでも名付けました）
- rootのパスワードをセットします。
- データベースのバージョンはMySQL 8.0のままで行きます。
- CloudSQLのエディションの選択ですが、ここはEnterpriseで十分なのでそちらを選択します。
- エディションのプリセットは今回はテスト用なので「サンドボックス」を選びます
- リージョンでは特に今回はテストなのでデフォルトのus-central1 アイオワを選択します。
- ゾーンの可用性もシングルゾーンで十分です
- この状態でとりあえず「インスタンスの作成」をクリックします。まだこのあとカスタマイズが控えています（作成完了まで結構な時間が掛かります。自分の場合14分くらい掛かった）
- この状態でも良いのですが、スペックをさらにカスタマイズするため、上部にある「編集」をクリックします。
- 下の方にあるインスタンスのカスタマイズに移動して、マシンシェイプをひらき、共有コアを選択して1 vCPUを選択します。
- ストレージを開いて、SSDのままでオッケーだけれども自動増量のチェックを外します。
- データの保護を開いて、今回はテストなので自動バックアップはすべてオフにします。削除からの保護の有効化も無効にします。
- メンテナンスを開いて、メンテナンスのタイミングは任意に変更します。
- 保存をクリックして保存して再起動をクリックする
データベースが再起動したら完了となります。また今回はIPアドレスに関してはパブリックIPのままでセットしていますので、外からもアクセス出来てしまうので要注意。また、あくまでテスト環境であるため、色々とオフにしたりダウングレードしていますが、実際の本番環境では色々スペックは考慮する必要があります。
図：エディションを選択する必要がある
図：設定の概要
作成後の設定を施す
作成直後のデータベースは自分自身以外誰もアクセスが出来ません。また、パブリックIPでセットしているため、どこからでも接続が可能な状況になっています。企業でプロキシーサーバなどを採用してる場合、出口のIPアドレスを固定化できるため、IPアドレス制限を掛けておいたほうがセキュリティ的には推奨になります。そのための作業を行います。
- Cloud SQLの概要画面の左サイドバーから「接続」をクリックします。
- ネットワーキングタブを開きます。
- パブリックIPにある「承認済みネットワーク」に追加するIPアドレスを、確認くんで調べておく
- ネットワークを追加をクリックして、3.のIPアドレスと名前をつけて保存する（これはローカルや他のウェブサイトから接続時用）
- それ以外として、IPアドレスで接続が必要な場合、JDBCサービスからアクセスされる可能性のあるURLリストがあるので、これらもネットワークの追加で登録しておくと尚良いでしょう（Jdbc.getConnectionを使う場合は全部登録が必要。Jdbc.getCloudSqlConnectionの場合は不要）。
- セキュリティのタブにてSSL接続にしてしまうと、SSLのキーがなければGASからは接続できないので注意。
- 保存をクリックする
- 左サイドバーから「ユーザー」をクリックします。
- rootしかいない状態なので、ユーザアカウントを追加をクリックする
- 通常のID/PWの他にIAMからも選択もできますが、今回はベーシックに前者で行くので登録します。IAMの場合にはSQL関連の権限を持っている必要があるので注意。
- 次に左サイドバーから「データベース」をクリックします。
- データベースの作成をクリックする
- 適当なデータベース名を入力して、作成をクリックする。ただし中身はフィールドもない空っぽの状態です（今回はgastableという名前にしておきました。）
作成したデータベースにテーブルを作りデータを入れていく必要があります。
図：IPアドレスに縛りを設ける
図：データベースの作成中
図：URLリストを全部登録してみた
接続用の文字列を取得する
Google Apps Scriptで利用する接続用の文字列を取得する必要があります。概要タブに戻って以下の項目をメモっておきます。
- パブリックIPアドレス
- インスタンス接続名（厳密にいうとこれだけでもオッケー）
この2つの値から接続用文字列を作ってGAS側から接続して読み書きをすることになります。
図：接続に必要な情報を取得する
注意点
今回使ってみてわかったことですが、以前と違ってCloud SQLのインスタンスは停止していても課金が継続して発生します。1日あたり100円未満程度ではありますが月額にするとまぁまぁの金額になります。課金から確認をしてみるとその様子を伺うことが可能です。必ずアラートを設定して、自分が想定している以上に課金に至らないように注意を払う必要があります。
インスタンスを削除すると課金は停止します。
図：停止していても課金はなされる
GAS側の準備
プロジェクトを移動する
GCP上のAPIの利用となるので、Google Apps ScriptのプロジェクトをGCPと連結する必要があります。
図：プロジェクト変更画面
データベース接続用文字列
今回は、GCP上のMySQLへの接続とするので、利用するメソッドはJdbc.getCloudSQLConnection()となります。そしてこのメソッドのために接続用の文字列を構築する必要があります。
Jdbc.getCloudSqlConnection("jdbc:google:mysql://インスタンス接続名/接続DB名", ユーザID, パスワード);
getCloudSqlConnectionメソッドを利用し、接続文字列の後に、上記の通り、インスタンス接続名、接続DB名といった形がつなげると接続が可能になります。接続インスタンス名は前述の接続文字列の取得で控えておくものであり、接続DB名は作成した空っぽのDB（今回ならばgastable）がそれになります。ユーザIDとパスワードは作っておいたものをユーザに応じてはめ込むようにしましょう。
今回の例でいくと以下のような形になります。
Jdbc.getCloudSqlConnection("jdbc:google:mysql://gaspro-xxxx:us-central1:gastank/gastable", hogeuser, hogepw);
GASから接続して読み書き
読み書きをするにあたって、ユーザのID/PWについては、スクリプトプロパティではなくユーザプロパティに格納するようにしましょう。今回の記事では便宜上スクリプトプロパティを利用しています。また、プロパティに格納せずに毎回初回起動時に変数に格納してアクセスさせるような仕組みを構築した方が望ましいです。
以下は今回利用するスクリプトプロパティとその値のリストです。
- ipaddress : 取得したIPアドレスを格納
- userid : 作成したユーザのID
- passwd : 作成したユーザのPW
- instance : インスタンス接続名
- dbname : データベース名
図：プロパティに値を格納する
テーブルの作成
前回の記事ではMySQL Workbenchを使ってローカルからテーブルの作成などをしていましたが、今回はGAS側からテーブルも作成してしまおうかと思います。
//テーブルを作成する
function createTable() {
//プロパティ値を取得
var Prop = PropertiesService.getScriptProperties();
var instance = Prop.getProperty("instance");
var dbname = Prop.getProperty("dbname");
var userid = Prop.getProperty("userid");
var userPwd = Prop.getProperty("passwd");
//テーブル作成を実行
try{
//コネクションを確立
var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);
//Create Tableを実行
conn.createStatement().execute('CREATE TABLE kinoko '
+ '(ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), '
+ 'name VARCHAR(255), '
+ 'type VARCHAR(255), '
+ 'edible boolean DEFAULT false, '
+ 'price INT(255));');
}catch(e){
console.log('エラーが発生したようです。 %s', e.message);
return;
}
//作ったテーブルを参照
let ui = SpreadsheetApp.getUi();
let test = conn.createStatement().execute('SHOW TABLES FROM ' + dbname + ';');
//終了処理
if(test == true){
ui.alert("作成完了");
}else{
ui.alert("なんか失敗したみたいですよ。");
}
}
- スプレッドシートと同じような形式のテーブルをデータベースに対して作成します。
- すでに作成済みの場合にはconsoleにエラーが表示されます。
現在のCloud SQLにはCloud SQL Studioというものが用意されており、GUIにてテーブルに対してクエリの実行などやテーブルの状態を確認する手段が用意されているため、以前とは異なり大分扱いやすくなっています。テーブルの作成などもSQL文ではありますが、可能になっています。
図：GUIでデータベースを操作できる
データを追加する
通常のデータは1行ずつInsert Intoでいれるわけなのですが、今回テーブルが空っぽなのでスプレッドシートのデータをごっそりバッチインポートしてみたいと思います。2700行程度のデータを貼り付けてみて実行してみました。時間計測をしていますが、1分未満で終わることもあれば、4分44秒掛かることもあり、結構ムラがある感じです（後述のパフォーマンス問題に該当する事象だと思います）。
//スプシのデータをバッチでインサート
function insertBatchData(){
//プロパティ値を取得
var Prop = PropertiesService.getScriptProperties();
var instance = Prop.getProperty("instance");
var dbname = Prop.getProperty("dbname");
var userid = Prop.getProperty("userid");
var userPwd = Prop.getProperty("passwd");
//開始時間
let starttime = new Date();
let batch;
try{
//コネクションを確立
var conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname, userid, userPwd);
//自動コミットをオフ
conn.setAutoCommit(false);
//statementを作成
let query = "INSERT INTO `" + dbname + "`.`kinoko` (`name`, `type`, `edible`, `price`) VALUES (?,?,?,?)";
let stmt = conn.prepareStatement(query);
//スプシデータからinsert文を構築(ID列は除外)
let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("database").getRange("B2:E").getValues()
let num;
for(let i = 0;i<ss.length;i++){
//レコードを一個取り出す
let rec = ss[i];
//値が空の場合はスルーする
if(rec[0] == "" || rec[0] == undefined){
continue;
}
//numを初期化
num = 1;
for(let j = 0;j<rec.length;j++){
//edibleの時だけ判定を加える
if(j == 2){
//falseやtrueは数値に変換
if(rec[2]==false){
stmt.setString(num, -1);
}else{
stmt.setString(num, 0);
}
}else{
//データを追加
stmt.setString(num, rec[j]);
}
//numを加算
num = num + 1;
}
//バッチに追加
stmt.addBatch();
}
//バッチインサートを実行
batch = stmt.executeBatch();
//コミットとコネクションをクローズ
conn.commit();
conn.close();
}catch(e){
console.log('エラーが発生したようです。 %s', e.message);
return;
}
//終了時間
let endtime = new Date();
//時間を計測
let difftime = (endtime.getTime()-starttime.getTime())/(1000*60);
console.log('処理時間:' + difftime + "分でした");
}
- ベースになるInsert Into文を構築し、１レコード単位でaddBatchで追加していきます。
- Batchが出来上がったら、executeBatchにて一括でインサートを実行します。
- コミットを今回は自動ではなくコードによる手動で実行にしています。
SSL/TLS証明書で接続するようにする
概要
前述までの方法はパブリックIPに対して接続をする方法で、ローカルからの場合はIPアドレスを追加することで、所定のIPから以外の通信を遮断出来ます。しかし、この通信そのものはデフォルトでは保護されてるわけではないので非推奨となっています。
ということで、証明書を用いてリクエスト時に証明書をセットし、それをもってして通信を保護する方式をCloud SQLは備えています。今回このSSL/TLS証明書を持って通信する方法をGASで実装してみようと思います
※メソッドとしてはgetConnectionを利用するため、承認済みネットワークにて前述のようにGoogleのURLリスト80個近くを全て登録が必要です。登録しないと接続時にエラーが出て拒否される可能性があります。
設定を変更する
まずはSSLモードでの接続にするよう設定を変更します。
- Cloud SQLの概要画面を開きます。
- 左サイドバーより接続をクリックする
- セキュリティタブを開き、SSL モードの管理の管理の中の「SSLモード」 にて、「信頼できるクライアント証明書を必須にするにチェックを入れる
- 保存するダイアログが出てくるので指示に従う（更新が終了するまでちょっと時間が掛かる）
図：まずはSSLモードに設定変更する
証明書等を用意する
証明書を用意するといっても、すでにインスタンスを立てた時点でじつはGCP上ですでにファイルが用意されています。以下の手順で3つのファイルを入手します。これらファイルは絶対に流出しないように厳重に管理をする必要があります。
- 前述の続きで、「サーバー CA 証明書の管理」の下にある「証明書をダウンロード」をクリックして、server-ca.pemをダウンロードする
- クライアント証明書の管理にある「クライアント証明書を作成」をクリックする
- 右サイドバーが出現するので、適当な名前を入れて作成をクリックする
- 新しい SSL クライアント証明書を作成しましたというダイアログが出てくるので、client-key.pemおよびclient-cert.pemをダウンロードする。このシーンでしか入手が出来ないので要注意。
- closeをクリックする
ダウンロードした3種類のファイルは厳格な管理の出来るGoogle Driveの所定の場所にでもアップロードしておきます。
図：クライアント証明作成の画面
図：クライアント証明の入手
接続するコード
取得した証明書ファイルをGoogle Driveにアップロードし、アクセス権を絞っておきます。この3つのファイルを持ってして、コネクションを接続するようにします。
//証明書を持ってして接続する
function connbyCertFile() {
//各種証明書ファイルの中身を取得する
let client_key_file = DriveApp.getFileById(clientKey).getBlob().getDataAsString("utf-8");
let client_cert_file = DriveApp.getFileById(clientCert).getBlob().getDataAsString("utf-8");
let server_ca_file = DriveApp.getFileById(serverCa).getBlob().getDataAsString("utf-8");
//プロパティ値を取得
var Prop = PropertiesService.getScriptProperties();
var dbname = Prop.getProperty("dbname");
var userid = Prop.getProperty("userid");
var userPwd = Prop.getProperty("passwd");
var ipaddress = Prop.getProperty("ipaddress");
//コネクションを確立
try{
//接続情報を構築
let connInfo = {
user: userid,
password: userPwd,
_serverSslCertificate: server_ca_file,
_clientSslCertificate: client_cert_file,
_clientSslKey: client_key_file
}
//接続を確立する
let connectionString = 'jdbc:mysql://'+ipaddress + ":3306" +'/' + dbname + "?useSSL=true";
let conn = Jdbc.getConnection(connectionString,connInfo);
//kinokoテーブルへ最大100件限定で取得
let stmt = conn.createStatement();
stmt.setMaxRows(100)
let results = stmt.executeQuery('SELECT * FROM kinoko')
//取得結果より列数を取得
let numCols = results.getMetaData().getColumnCount();
//取得結果を配列に取得
var dataArray = [];
while (results.next()) {
var row = [];
for (var col = 0; col < numCols; col++) {
row.push(results.getString(col + 1));
}
dataArray.push(row);
}
//取得内容をスプレッドシートに書き込む
let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tempdata");
ss.getRange("A2:E").clearContent();
let lastColumn = dataArray[0].length; //カラムの数を取得する
let lastRow = dataArray.length; //行の数を取得する
ss.getRange(2,1,lastRow,lastColumn).setValues(dataArray);
//接続を閉じる
conn.close;
stmt.close;
results.close;
}catch(e){
console.log('エラーが発生したようです。 %s', e.message);
return;
}
}
- Driveにアップした３つのファイルの情報をDriveAppで取得する
- 接続情報でそれらの情報やユーザのID/PWなどをまとめた連想配列を作っておく
- 旧来のgetConnectionとgetCloudConnectionではパラメータの指定方法が異なり、前者はIPアドレス、後者は接続用URLで指定します。SSL証明書添付ではgetConnectionの方式を利用します。
- SSLモードが有効化の場合は、getConnectionではSSL証明書をつけないとアクセスを拒否されます。証明書の無い接続をフィルタするのに利用可能です。
- SSLモードが有効であっても、getCloudConnectionでは証明書をつけたような様式であろうとなかろうと接続が可能です（公式ドキュメントにはそもそもSSL添付の記載自体が無い）。
- getCloudConnectionでもgetConnectionと同様の以下のような構文でも接続は可能でした。useSSL=trueという文字列を追加する必要があります。
let conn = Jdbc.getCloudSqlConnection(instanceUrl + instance + "/" + dbname + "?useSSL=true", connInfo);
- Jdbc.getCloudSqlConnectionにて、通常のインスタンス文字列と接続情報を渡すと接続が可能になります（きちんとコレがドキュメントに記載されていない）。
- 今回は100件だけ取得するクエリを実行しています。
- 取得したデータをスプシに一気に書き込みをしています。
その他
プライベートIPで接続出来ないのか？
Googleのドキュメントによると、GCP上のプライベートIPで接続できるサービスは、Google App EngineやCloud Functionsなどに限られており、Google Apps Scriptからでは接続が出来ないようです。プロジェクトをGCP側に移動してもプライベートIPでの接続文字列では接続が出来ないようです。
ここがGASからでも出来るようになると完全に自身のテナント内で閉じた環境に出来るので良いのですが・・・
パフォーマンス問題
StackOverFlowのとあるスレッドを眺めていたところ、Google CloudSQLのMySQLに対してGoogle Apps Scriptから接続しデータを取得するようなケースに於いて、何回か実行した結果の取得完了までに掛かる時間に大きな差があるというお話。
CloudSQLだけじゃなくオンプレで立てたMySQLでも同様の傾向が見られたとのこと。この問題はIssue Trackerでも以下の2スレッドに於いて現在も議論が続いてるとのことでデフォルトだとこのパフォーマンス差が現在も出ている様子（V8ランタイム由来の問題らしい）
そこで以下のコードで1000件 5列で取得を10回連続で回してみました。
//MySQLの接続テストのパフォーマンス検証
function cloudsql_performe() {
//プロパティ値を取得
var Prop = PropertiesService.getScriptProperties();
var dbname = Prop.getProperty("dbname");
var userid = Prop.getProperty("userid");
var userPwd = Prop.getProperty("passwd");
var ipaddress = Prop.getProperty("ipaddress");
//テーブル作成を実行
try{
//コネクションを確立
let connectionString = 'jdbc:mysql://'+ipaddress + ":3306" +'/' + dbname;
let conn = Jdbc.getConnection(connectionString,{user: userid, password: userPwd});
let stmt = conn.createStatement();
stmt.setMaxRows(1000)
//10回回してパフォーマンス測定
let results;
for(let i = 0;i<10;i++){
//開始時間
let starttime = new Date();
//クエリ実行
results = stmt.executeQuery('SELECT * FROM kinoko');
//終了時間
let endtime = new Date();
//時間を計測
let difftime = (endtime.getTime()-starttime.getTime())/1000;
console.log(i + '回目、処理時間:' + difftime + "秒でした");
}
//クローズ処理
conn.close;
stmt.close;
results.close;
}catch(e){
console.log('エラーが発生したようです。 %s', e.message);
return;
}
}
測定してみた結果ですが、初回接続だけはちょっと時間が掛かっていますが、それ以降については殆ど差は見受けられず。現時点では標準的な使い方ではパフォーマンス的な問題があるようには見受けられません。
しかし、問題がある場合については、SSL/TLSは未サポートのようですが、こちらのJdbcXというライブラリをGASに組み込んで利用することで改善することが出来るようです。もし同様の事例に遭遇した場合には使ってみるのも良いかもしれません。
図：優位な差は見受けられない
関連リンク
- Google Cloud MySQL の Apps Script からのパフォーマンスは大きく異なる
- 2023年11月の時点ではGASのJDBCからMySQL8.0に接続できる。
- GCPのCloud SQLデータベースにSQLでテーブルを作成する方法
- GASからGCPのCloudSQLへの接続が確立できない
- GASから"Google Cloud SQL for MySQL"への接続で試したこと
- Cloud SQLを徹底解説！
- AppsScript の JDBC サービスは MySQL 8.0 に対応してないっぽい
- Google Apps Scriptを使ってGoogleスプレッドシートとCloud SQLを連携
- Cloud SQLの料金
- プライベート IP を使用して Cloud SQL for MySQL インスタンスに接続する
- Jdbc connection error from Google Apps Script
- GASとCloud SQLのSSL接続
- 日本産菌類のチェックリスト（日本産菌類集覧和名リスト）
- SSL / TLS 証明書の構成
- MySQLのおすすめ便利クライアントツール
