时间戳

首页 > 解决方案 > 使用 Google Apps 脚本修改 BigQuery 数据

google-apps-script - 使用 Google Apps 脚本修改 BigQuery 数据

问题描述

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

我整理了下面的脚本，用于修改我的 bigQuery 表中的数据：

function runQuery() {
   var projectId = 'bigquery';
   var datasetId = 'Test';  
   var date = '20181206';
   var tableId = 'ga_sessions_'+date;
// Configuration
   var configuration = {
    "query": {
    "useQueryCache": false,
    "writeDisposition": "WRITE_TRUNCATE",
    "createDisposition": "CREATE_IF_NEEDED",
    "allowLargeResults": true,
    "useLegacySql": false,
    query: "SELECT * EXCEPT (hits),ARRAY( SELECT AS STRUCT * EXCEPT (page, appInfo, eventInfo ),(SELECT "+
"AS STRUCT page.* EXCEPT (pagePath, pagePathLevel1, pagePathLevel2, pagePathLevel3, pagePathLevel4), "+
"REGEXP_REPLACE(page.pagePath, r'([^\\?]+?)(\\?.+)','\\\\1') AS pagePath,"+
"REGEXP_REPLACE(page.pagePath, r'([^\\?]+?)(\\?.+)','\\\\1') AS pagePathLevel1, "+
"REGEXP_REPLACE(page.pagePath, r'([^\\?]+?)(\\?.+)','\\\\1') AS pagePathLevel2,"+
"REGEXP_REPLACE(page.pagePath, r'([^\\?]+?)(\\?.+)','\\\\1') AS pagePathLevel3, "+
"REGEXP_REPLACE(page.pagePath, r'([^\\?]+?)(\\?.+)','\\\\1') AS pagePathLevel4, "+
"`bigquery.Test.ga_sessions_20181206`",
  destinationTable: { projectId: projectId, datasetId: datasetId, tableId: tableId } 
   }
 };

  var job = {
    "configuration": configuration
  };

  var jobResult = BigQuery.Jobs.insert(job, "bigquery");
  Logger.log(jobResult);
}

该代码有效，但我现在想更改这一行：

"`bigquery.Test.ga_sessions_20181206`"

因此，最终用户不必输入日期，而是从“var date”中获取。

我已将代码修改为：

function runQuery() {
   var projectId = 'bigquery';
   var datasetId = 'Test';  
   var date = '20181206';
   var tableId = 'ga_sessions_'+date;
// Configuration
   var configuration = {
    "query": {
    "useQueryCache": false,
    "writeDisposition": "WRITE_TRUNCATE",
    "createDisposition": "CREATE_IF_NEEDED",
    "allowLargeResults": true,
    "useLegacySql": false,
    query: "SELECT * EXCEPT (hits),ARRAY( SELECT AS STRUCT * EXCEPT (page, appInfo, eventInfo ),(SELECT "+
"AS STRUCT page.* EXCEPT (pagePath, pagePathLevel1, pagePathLevel2, pagePathLevel3, pagePathLevel4), "+
"REGEXP_REPLACE(page.pagePath, r'([^\\?]+?)(\\?.+)','\\\\1') AS pagePath,"+
"REGEXP_REPLACE(page.pagePath, r'([^\\?]+?)(\\?.+)','\\\\1') AS pagePathLevel1, "+
"REGEXP_REPLACE(page.pagePath, r'([^\\?]+?)(\\?.+)','\\\\1') AS pagePathLevel2,"+
"REGEXP_REPLACE(page.pagePath, r'([^\\?]+?)(\\?.+)','\\\\1') AS pagePathLevel3, "+
"REGEXP_REPLACE(page.pagePath, r'([^\\?]+?)(\\?.+)','\\\\1') AS pagePathLevel4, "+
"`bigquery.Test.ga_sessions_*` "+
"WHERE "+
      "  _TABLE_SUFFIX = 'date' " ,      

  destinationTable: { projectId: projectId, datasetId: datasetId, tableId: tableId } 
   }
 };

  var job = {
    "configuration": configuration
  };

  var jobResult = BigQuery.Jobs.insert(job, "bigquery");
  Logger.log(jobResult);
}

我现在遇到的问题是，它没有修改数据，而是完全删除了它。

有人可以看看这个，让我知道我哪里出错了。

非常感谢和亲切的问候

克里斯

标签： google-apps-scriptgoogle-bigquery

解决方案

问题在这里：

"  _TABLE_SUFFIX = 'date' "

该查询实际上是在寻找名为 的后缀'date'，而不是用户指定的任何后缀。拥有 Apps Script 环境的其他人可能能够给出更具体的答案，但基本思想是您应该改用查询参数，因此您将使用@date(无引号) 而不是'date'，并且您将传递 user-将日期指定为请求的 STRING 类型查询参数。

推荐阅读