Amend BigQuery Data Using Google Apps Script

Question

I wonder whether someone may be able to help me please?

I've put together the script below which amends data in my bigQuery table:

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);
}

The code works, but I'd now like to change this line:

"`bigquery.Test.ga_sessions_20181206`"

So that rather than the end user having to type in the date, it's picked up from the 'var date'.

I've amended the code to:

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);
}

The problem I now have is that rather than amending the data, it completely deletes it.

Could someone have a look at this please and let me know where I've gone wrong.

Many thanks and kind regards

Chris

Elliott Brossard Elliott Brossard · Accepted Answer · 2018-12-20T15:52:29

The problem is here:

"  _TABLE_SUFFIX = 'date' "

The query is literally looking for a suffix named 'date', rather than whatever the user specified. Someone else who has an Apps Script environment may be able to give a more specific answer, but the basic idea is that you should use a query parameter instead, so you would use @date (no quotes) instead of 'date', and you would pass the user-specified date as a STRING type query parameter with the request.

Amend BigQuery Data Using Google Apps Script

1 Answers