【検証】Google Apps Script × Fusion Tables ["Query:sql" vs "Table:importRows"]

Google Apps Scriptから，Fusion Tablesへのデータ挿入に関して調査をしました。

Google Apps ScriptからFusion Tablesにデータを挿入する方法

GASとして特別なAPIはなくて，Fusion Tables APIが使えるという認識。

The Fusion Tables service allows you to use the Google Fusion Tables API in Apps Script.

引用元：Fusion Tables Service   |  Apps Script   |  Google Developers

Fusion Tables APIでデータを挿入する系のAPIは2種類。

• Query:sql
• Table:importRows

Query:queryはSQL文を実行して挿入。Table:importRowsはSQLを使わずに挿入。すごく便利そう。

Query:queryとTable:importRowsに関する黒い噂

「Query:sql」⇒すぐに何かの上限に引っかかる？

GASから、Fusion Tables を使用してると、すぐに何かの上限にひっかかるようでろくにinsertできない。

引用元：Fusion Tables importRows を使用するサンプル - shima111の日記

上限については，公式ドキュメントより引用。

The default request quota limits are the following:
・25,000 requests per day per API project, where reads count as one request and writes count as five requests.
・30 write requests per minute per table

• 書き込み系の上限は１日あたり25,000リクエスト＆書き込みには5リクエスト消費
  ⇒１日あたり上限5,000件
• さらにテーブルごとに1分あたり30件の書き込みが上限

「Table:importRows」⇒Table:importRowsは連続実行で内部エラー？

Table.importRows
SQLを使わずに、Fusion Tablesにデータを挿入します。
Query.sqlよりも比較的早くデータ挿入ができますが、一度実行した後は一定の時間が経過しないと、内部エラーが発生します。

引用元：GoogleAppsScriptからFusion Tablesのテーブルを作成・データ挿入するときのメモ - Qiita

「一定の時間」がどのくらいで，どんな内部エラーなのかは知っておきたいところ。公式ドキュメントではファイルサイズが250MBが上限だということしか記載されていません。

Maximum file size: 250MB

いまいち，正確に理解できないので，サンプルスクリプトを書いて検証！（スクリプトは最後にご紹介）

検証内容

データを10,000件挿入する。挿入する方法は，以下の３パターン

1. Query:sqlを10,000回ぶんまわす
   API仕様通りなら31件目の書き込みでコケるはず
2. Table:importRowsを10,000回ぶんまわす
   噂通りなら2回目でコケるはず
3. 10,000行挿入するSQLを生成しイッパツでTable:importRowsでぶっこむ
   おそらく成功するはず

なお，登録するデータは以下の通り。

[id]:NUMBER

シーケンシャルな数値

[name]:STRING

全角250字の文字列

実行結果

[15-07-28 20:41:08:040 JST] insertDatas Start  
[15-07-28 20:41:36:322 JST] Exception: Rate Limit Exceeded  
[15-07-28 20:41:36:322 JST] 31.0  
[15-07-28 20:41:36:322 JST] insertDatas End  
[15-07-28 20:41:36:323 JST] importRowsTestA Start  
[15-07-28 20:41:40:626 JST] Exception: Internal error when processing import.  Please try again.  
[15-07-28 20:41:40:626 JST] 2.0  
[15-07-28 20:41:40:627 JST] importRowsTestA End  
[15-07-28 20:41:40:627 JST] importRowsTestB Start  
[15-07-28 20:42:03:969 JST] importRowsTestB End  

1. Query:sqlを10,000回ぶんまわす
   Exception: Rate Limit Exceeded
   【仕様通り】31件目挿入時にエラーが発生
2. Table:importRowsを10,000回ぶんまわす
   Exception: Internal error when processing import. Please try again.
   【噂通り】2件目挿入時にエラー発生…（補足あり／後述）
3. 10,000個のSQLを生成しTable:importRowsでぶっこむ
   【成功】おめでとう（課題あり／後述）

実行結果の補足

Table:importRowsを10,000回ぶんまわすケースについては，あとで再実行したらこんなログも。

[15-07-28 21:21:57:581 JST] importRowsTestA Start
[15-07-28 21:25:59:245 JST] Exception: Internal error when processing import.  Please try again.
[15-07-28 21:25:59:245 JST] 164.0
[15-07-28 21:25:59:245 JST] importRowsTestA End

164件目を挿入時にエラー発生。

また文字数を倍(500字)にしたところ，10,000個のSQLを生成しTable:importRowsでぶっこむケースも完走できなかったのでご紹介。

[15-07-28 20:33:03:253 JST] importRowsTestB Start
[15-07-28 20:33:33:601 JST] Exception: Empty response
[15-07-28 20:33:33:601 JST] importRowsTestB End

一瞬，API仕様で謳われている上限250MBの制約かと思ったんだけど，全角100文字×10,000件だと15～16MB程度。上限に対して余裕があるため，GASからFusion Tablesへの通信上の問題であるような気がします。

じゃあどうすりゃ10,000件挿入できるんだよ

• Query:sqlでやろうとすると最低でも2日間はかかる模様（１日の上限は実質5,000件）
  諦めたらそこで試合終了だけど，この用途に限って言えば，試合終了でも構わない。
• Table:importRowsでやるのが良いが，課題は残る。
  • 1行ずつ実行する
    Internal errorとやらが本当に時間の制限なのかどうかはよくわからないが，エラーメッセージにあるとおり，try againするようにプログラミングすれば良いのかもしれない。次回，改善案を検証。
  • 1回のSQLで実行
    文字数が多いとレスポンスが帰ってこない可能性がある(15～16MBでは帰ってこなかった)。これも次回，改善案を検証。

今回の検証のまとめ

• Query:sqlで1分間に31件以上のデータ挿入を行おうとすると以下の例外が発生する。
  Exception: Rate Limit Exceeded
• Table:importRowsでは2種類の例外を検知した。
  
  1. 複数回実行時（明確な制約があるわけではなさそう）
     Exception: Internal error when processing import. Please try again.
  2. 大量データ挿入時（レスポンスが帰ってこないだけで制約の問題ではない）
     Exception: Empty response
• 数千行のデータ挿入はTable:importRowsのほうが現実的。数行程度であれば， Query:sqlでも問題ない。
• いずれにしても一筋縄ではいかないけど楽しそう！（笑）。

次回予告

それでも僕はGASからFusion Tablesに10,000件のデータを挿入する！

※追記　2015/08/06　【検証】Google Apps Script × Fusion Tables ～"Table:importRows"との闘い編～

サンプルスクリプト

GoogleAppsScriptからFusion Tablesのテーブルを作成・データ挿入するときのメモ - Qiitaにあるコードを参考にさせていただきました。

sample.gs

function run() {
  insertDatas();
  importRowsTestA();
  importRowsTestB();
}

function createTable(tableName) {
  var resource = {
  "name": tableName,
  "isExportable": false,
  "kind": "fusiontables#table",
  "columns": [
    {
      "name": "id",
      "type": "NUMBER",
      "kind": "fusiontables#column"
    },
    {
      "name": "name",
      "type": "STRING",
      "kind": "fusiontables#column"
    },
    ],
  };
  return FusionTables.Table.insert(resource).tableId;
}

function insertDatas() {
  Logger.log("insertDatas Start");
  var tableName = "insertDatas";
  var tableId = createTable(tableName); 
  for(var i=1;i<=10000;i++){
    var sql = "INSERT INTO " + tableId + " (id,name) VALUES ("+i+",'"+shimono()+"');";
    try{
      FusionTables.Query.sql(sql);
    }catch(e){
      Logger.log(e);
      Logger.log(i);
      break;
    }
  }
  Logger.log("insertDatas End");
}

function importRowsTestA() {
  Logger.log("importRowsTestA Start");
  var tableName = "importRowsTestA";
  var tableId = createTable(tableName); 
  for(var i=1;i<=10000;i++){
    var rowsData = i+","+shimono()+"\n" ;
    var rowsBlob = Utilities.newBlob(rowsData, "application/octet-stream");
    try{
      FusionTables.Table.importRows(tableId, rowsBlob);
    }catch(e){
      Logger.log(e);
      Logger.log(i);
      break;
    }
  }
  Logger.log("importRowsTestA End");
}

function importRowsTestB() {
  Logger.log("importRowsTestB Start");
  var tableName = "importRowsTestB";
  var tableId = createTable(tableName); 
  var rowsData="";
  for(var i=1;i<=10000;i++){
    rowsData += i+","+shimono()+"\n" ;
  }
  var rowsBlob = Utilities.newBlob(rowsData, "application/octet-stream");
  try{
    FusionTables.Table.importRows(tableId, rowsBlob);
  }catch(e){
    Logger.log(e);
  }
  Logger.log("importRowsTestB End");
}
var shimono = function(){
  var tmp ='';
  for(var i=0;i<50;i++){
    tmp += "下野由貴💛";
  }
  return tmp;
};

500字の文字列を生成しているところに登場してくる下野由貴ちゃんは僕の推しメンで，あまり深い意味はありません。由貴ちゃん愛してる