Tag: Studio

I am using Google BigQuery API V2 for a custom Google Data Studio connector. The BigQuery.Jobs.query() calls take roughly 50 seconds which is too long, so I am looking for ways to speed things up:

function runQuery() {
  // Cloud Platform project.
  var projectId = 'XXXXXXXX';

  const sqlQuery = `SELECT orgId, status, DATETIME(createdAt,'Pacific/Auckland') as createdAt FROM publicdata:samples.shakespeare LIMIT 50000;`;

  var request = {
    query: sqlQuery,
    useLegacySql: false,
  };

  var queryResults = BigQuery.Jobs.query(request, projectId); 
  var jobId = queryResults.jobReference.jobId;
  
  // Check on status of the Query Job.
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }
  
  // Get all the rows of results.
  var rows = queryResults.rows;

  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (rows) {
    var headers = queryResults.schema.fields.map(function(field) {
      return field.name;
    });
    
    var data = ();
    for (var i = 0; i < rows.length; i++) {
      var obj = {};
      
      var cols = rows(i).f;
      for (var j = 0; j < headers.length; j++) {
        obj(headers(j)) = cols(j).v;
      }
      
      data.push(obj);
    }
    return data;
  } else {
    Logger.log('No rows returned.');
    return null;
  }
}