计划的BigQuery作业中的动态表格和数据集 [英] Dynamic Table and Dataset in Scheduled BigQuery Job

问题描述

我想知道是否有人可以帮助我.

I wonder if someone can help me please.

我一直在看以下教程:

I've been looking at the following tutorial: https://shinesolutions.com/2017/11/01/scheduling-bigquery-jobs-using-google-apps-script/

我了解脚本的原理，但是我想使脚本的两个元素动态化.

I understand the principals of the script, but I'd like to make two elements of the script dynamic.

function runQuery() {
  var configuration = {
    "query": {
    "useQueryCache": false,
    "destinationTable": {
          "projectId": "project",
          "datasetId": "dataset",
          "tableId": "ga_sessions_20181014"
        },
    "writeDisposition": "WRITE_TRUNCATE",
    "createDisposition": "CREATE_IF_NEEDED",
    "allowLargeResults": true,
    "useLegacySql": false,
    "query": "SELECT * FROM `project.dataset.ga_sessions_20181014`"
    }
  };

  var job = {
    "configuration": configuration
  };

  var jobResult = BigQuery.Jobs.insert(job, "grey-sort-challenge");
  Logger.log(jobResult);
}

这些是源"和目标"表ID中的日期，因此将显示当前的第一天.即ga_sessions_20181014，然后是ga_sessions_20181015，依此类推.

These are the dates in the Source and Destination table IDs, so that this will show current day-1. i.e. ga_sessions_20181014, then ga_sessions_20181015 and so on and so forth.

有人可以告诉我，这可能吗?

Could someone tell me please, is this possible?

非常感谢和问候

克里斯

推荐答案

您只需要定义一个保存昨天日期的变量并对其进行适当的格式化，然后将其连接到您的配置中即可.

You just need to define a variable that holds yesterday's date and format it appropriately, then concatenate it in your configuration.

执行此操作的方法很多，如评论中所指出，最直接的方法可能是使用

There are many ways of doing this, and as pointed out in a comment, the most straightforward is probably using Utilities.formatDate. Here's an example:

var timeZone = 'America/Chicago';
var yesterday = Utilities.formatDate(new Date(Date.now() - 24*60*60*1000), timeZone, 'yyyyMMdd');

var configuration = {
  "query": {
  "useQueryCache": false,
  "destinationTable": {
        "projectId": "project",
        "datasetId": "dataset",
        "tableId": "ga_sessions_" + yesterday
      },
  "writeDisposition": "WRITE_TRUNCATE",
  "createDisposition": "CREATE_IF_NEEDED",
  "allowLargeResults": true,
  "useLegacySql": false,
  "query": "SELECT * FROM `project.dataset.ga_sessions_" + yesterday + "`"
  }
};

这篇关于计划的BigQuery作业中的动态表格和数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！