如何限制Google Bigquery中的作业数量

In order to have a better overview of what happens in our CRM (ActiveCampaign) and to create our own reports, I now post all changes to a BigQuery database using webhooks and a Google Apps Script.

为了更好地概述我们的CRM（ActiveCampaign）中发生的事情并创建我们自己的报告，我现在使用webhooks和Google Apps脚本将所有更改发布到BigQuery数据库。

This works very well, however I found out there is a limit of 1000 load jobs, and every change in the CRM creates at least one, sometimes more load jobs. This limit cannot be increased in any way. So I am looking for a way to batch the entries and load them every 10 minutes.

这非常有效，但是我发现有1000个加载作业的限制，并且CRM中的每个更改都会创建至少一个，有时更多的加载作业。不能以任何方式增加此限制。所以我正在寻找一种批处理条目并每10分钟加载一次的方法。

I don't know however how to create that in an Apps script that is triggered by a webhook and I cannot find it anywhere online. Does anyone have a suggestion?

我不知道如何在由webhook触发的Apps脚本中创建它，我无法在网上找到它。有人有建议吗？

2 个解决方案

#1

You can add a time delay to your event handling by serializing the events into a data storage device (like Google Sheets) in the initial webhook, and then using a time-based trigger to read saved events and send them to BigQuery.

您可以通过在最初的webhook中将事件序列化为数据存储设备（如Google表格），然后使用基于时间的触发器来读取已保存的事件并将其发送到BigQuery，从而为事件处理添加时间延迟。

function doPost(e) {
  var hdd = SpreadsheetApp.openById("some id");
  /* Pick the sheet to log to based on parameters of the event */
  var params = e.parameters, sector = "";
  if(...) {
    sector = "some sheet name";
  } else if(...) {
    sector = "some other sheet name"
  }
  ... // Do other stuff with event that needs immediate handling.
  // Serialize for batch processing later.
  hdd.getSheetByName(sector).appendRow([JSON.stringify(e)]);
}
// Fire events in bulk. Create a time based trigger to call this function every so often.
function refireEvents() {
  var sectors = SpreadsheetApp.openById("some id").getSheets();
  for(var i = 0; i < sectors.length; ++i) {
    var events = sectors[i].getDataRange().getValues();
    var sheetName = sectors[i].getName();
    for(var r = 0; r < events.length; ++r) {
      var event = JSON.parse(events[r][0]); // Assuming only single column of data
      /* Do stuff with the event response that was originally done in the webhook */
    }
    // Remove handled events.
    sectors[i].clearContent();
  }
}

#2

You can use you Google Apps Script to create a web service (web app) that will receive the request and then perform your batch operation.

您可以使用Google Apps脚本创建将接收请求然后执行批处理操作的Web服务（Web应用程序）。

Simply handle either the doGet() or doPost() method in your script, then when the script is published the function corresponding method will be invoked whenever a GET or POST request is made to the script's URL.

只需在脚本中处理doGet（）或doPost（）方法，然后在发布脚本时，只要对脚本的URL发出GET或POST请求，就会调用函数对应的方法。

See the Web Apps guide: https://developers.google.com/apps-script/guides/web

请参阅网络应用指南：https：//developers.google.com/apps-script/guides/web

#1

function doPost(e) {
  var hdd = SpreadsheetApp.openById("some id");
  /* Pick the sheet to log to based on parameters of the event */
  var params = e.parameters, sector = "";
  if(...) {
    sector = "some sheet name";
  } else if(...) {
    sector = "some other sheet name"
  }
  ... // Do other stuff with event that needs immediate handling.
  // Serialize for batch processing later.
  hdd.getSheetByName(sector).appendRow([JSON.stringify(e)]);
}
// Fire events in bulk. Create a time based trigger to call this function every so often.
function refireEvents() {
  var sectors = SpreadsheetApp.openById("some id").getSheets();
  for(var i = 0; i < sectors.length; ++i) {
    var events = sectors[i].getDataRange().getValues();
    var sheetName = sectors[i].getName();
    for(var r = 0; r < events.length; ++r) {
      var event = JSON.parse(events[r][0]); // Assuming only single column of data
      /* Do stuff with the event response that was originally done in the webhook */
    }
    // Remove handled events.
    sectors[i].clearContent();
  }
}

#2