BigQueryStorageHandler.js

/* Docs: 
 * https://developers.google.com/adwords/scripts/docs/examples/google-bigquery
 * https://cloud.google.com/bigquery/docs/reference/rest/v2/
 * https://developers.google.com/apps-script/advanced/bigquery
 * https://www.googleapis.com/bigquery/v2/projects/projectId/datasets/datasetId/tables/tableId
 */

/**
 * API-Doc:
 var SCRIPT_NAME = "your-script-name";
 var GLOBAL_STORAGE_CONFIG = {
  projectId : "your-project-name",
};

/**
 * fieldArray:
 * [
 * "id",
 * "campaignId"
 * ]
 *
 * whereClauseArray:
 * [
 *  {
 *  inputField: "date",
 *  operator: "<",
 *  value: "2017-09-07"
 *  }
 * ]
 * optionalClauses:
 * [
 *   "GROUP BY id"
 * ]
 */


/***************************************************/
/**
 * @constructor StorageHandler
 * @param {String} entityType entity type of the data to be handled
 */
function StorageHandler(entityType) {
  if (typeof SCRIPT_NAME == "undefined") {
    throw "Exception: SCRIPT_NAME not defined.";
  }
  this.entityType = entityType;
  this.projectId = GLOBAL_STORAGE_CONFIG.projectId;
  this.dataSetId = AdWordsApp.currentAccount().getName().replace(/[^a-zA-Z0-9 ]/g, "") + "_" + SCRIPT_NAME;
  this.fullTableName = this.projectId + "." + this.dataSetId + "." + this.entityType;
  var fieldSchemaObject = {};
  var fieldSchemaArray = [{ // siehe SEASEO-548
    "name": "id",
    "description": "primary key",
    "type": "STRING"
  }, {
    "name": "name",
    "description": "entity name",
    "type": "STRING"
  }, {
    "name": "type",
    "description": "entity type",
    "type": "STRING"
  }, {
    "name": "creationDate",
    "description": "inseration date",
    "type": "DATE"
  }, {
    "name": "isActive",
    "description": "is active",
    "type": "STRING"
  }, {
    "name": "campaignName",
    "description": "campaign name",
    "type": "STRING"
  }, {
    "name": "campaignId",
    "description": "campaign id",
    "type": "STRING"
  }, {
    "name": "adGroupName",
    "description": "adGroupName",
    "type": "STRING"
  }, {
    "name": "adGroupId",
    "description": "adGroupId",
    "type": "STRING"
  }, {
    "name": "updateDate",
    "description": "adGroupId",
    "type": "DATE"
  }];

  switch (entityType) {
    case "sitelink":
      break;
    case "campaign":
      break;
    case "adgroup":
      break;
    case "keyword":
      break;
    case "ad":
      break;
    case "negativeKeyword":
      break;
    case "productGroup":
      break;
    case "shoppingCampaign":
      break;
  }

  for (var i = fieldSchemaArray.length - 1; i >= 0; i--) {
    fieldSchemaObject[fieldSchemaArray[i].name] = fieldSchemaArray[i];
  }

  /**
   * priviliged method to access the field schema of the StorageHandler as Array
   * @return {Array} fieldSchemaArrray the field schema of the StorageHandler
   */
  this.getFieldSchemaArray = function() {
    return fieldSchemaArray;
  };

  /**
   * * priviliged method to access the field schema of the StorageHandler as Object
   * @return {Object} fieldSchemaArrray the field schema of the StorageHandler
   */
  this.getFieldSchemaObject = function() {
    return fieldSchemaObject;
  };
}

/**
 * initializes the DB with generic query. If data table exists it will succeed. If not, the StorageHandler creates a new data set and tabel in the given project.
 * @return {void}
 */
StorageHandler.prototype.initDb = function() {
  var queryRequest = BigQuery.newQueryRequest();
  queryRequest.query = 'select * from `' + this.fullTableName + '` LIMIT 1;';
  queryRequest.useLegacySql = false;
  try {
    var query = BigQuery.Jobs.query(queryRequest, this.projectId);
    Logger.log("BigQuery database '" + this.fullTableName + "' initialized.");
  } catch (e) {
    Logger.log(e);
    try {
      this.createDataSet();
    } catch (e) {
      Logger.log("Error: " + e);
      Logger.log("Message: " + e.message);
      Logger.log("Stacktrace: " + e.stack);
    }
    try {
      this.createTable(this.getFieldSchemaArray());
    } catch (e) {
      Logger.log("Error: " + e);
      Logger.log("Message: " + e.message);
      Logger.log("Stacktrace: " + e.stack);
      throw "Database could not be initialized. Exiting...";
    }
  }
};

/**
 * creates a new data set for the given entity type
 * @return {void}
 */
StorageHandler.prototype.createDataSet = function() {
  try {
    var dataSet = BigQuery.newDataset();
    dataSet.id = this.dataSetId;
    dataSet.friendlyName = this.dataSetId;
    dataSet.datasetReference = BigQuery.newDatasetReference();
    dataSet.datasetReference.projectId = this.projectId;
    dataSet.datasetReference.datasetId = this.dataSetId;
    dataSet = BigQuery.Datasets.insert(dataSet, this.projectId);

    Logger.log('Data set with ID = %s, Name = %s created.', dataSet.id, dataSet.friendlyName);
  } catch (e) {
    Logger.log("The dataSetId " + this.dataSetId + " already exists. ");
    Logger.log(e);
  }
};

/**
 * creates a new table for the given entity type
 * @return {void} 
 */
StorageHandler.prototype.createTable = function() {
  var table = BigQuery.newTable();
  var schema = BigQuery.newTableSchema();
  schema.fields = this._convertSchemaIntoFields();

  table.schema = schema;
  table.id = this.entityType;
  table.friendlyName = this.entityType;

  table.tableReference = BigQuery.newTableReference();
  table.tableReference.datasetId = this.dataSetId;
  table.tableReference.projectId = this.projectId;
  table.tableReference.tableId = this.entityType;
  table = BigQuery.Tables.insert(table, this.projectId, this.dataSetId);

  Logger.log('Data table with ID = %s, Name = %s created.', table.id, table.friendlyName);
};

/**
 * writes data to the specific table of the storage handler
 * @param  {Array} createdEntitiesLog contains all data to be written
 * @return {void}
 */
StorageHandler.prototype.writeRowstoStorage = function(createdEntitiesLog) {
  var insertAllRequest = BigQuery.newTableDataInsertAllRequest();
  insertAllRequest.rows = [];
  //insertAllRequest.ignoreUnknownValues = true;
  //insertAllRequest.skipInvalidRows = true;

  for (var i = 0; i < createdEntitiesLog.length; i++) {
    var newRow = BigQuery.newTableDataInsertAllRequestRows();
    newRow.insertId = createdEntitiesLog[i].id;
    newRow.json = createdEntitiesLog[i];
    insertAllRequest.rows.push(newRow);
  }

  var result = BigQuery.Tabledata.insertAll(insertAllRequest, this.projectId, this.dataSetId, this.entityType);

  if (result.insertErrors != null) {
    var allErrors = [];
    for (var i = 0; i < result.insertErrors.length; i++) {
      var insertError = result.insertErrors[i];
      allErrors.push(Utilities.formatString('Error inserting item: %s', insertError.index));

      for (var j = 0; j < insertError.errors.length; j++) {
        var error = insertError.errors[j];
        allErrors.push(Utilities.formatString('- ' + error));
      }
    }
    Logger.log(allErrors.join('\n'));
  } else {
    Logger.log(Utilities.formatString('%s data rows inserted successfully.', insertAllRequest.rows.length));
  }
};

/**
 * builds a new query from a given set of instructions
 * @param  {Array} fieldArray Array of fields that shall be accessed
 * @param  {Array} whereClauseArray Array of Objects (see API doc) of where clauses
 * @param  {Array} optionalClauses  Array of optional clauses
 * @return {String}
 */
StorageHandler.prototype.buildSelectQuery = function(fieldArray, whereClauseArray, optionalClauses) {
  var whereClauseArray = whereClauseArray || [];
  var fieldArray = fieldArray || [];
  var optionalClauses = optionalClauses || [];

  var fullQuery = 'select ';
  var schemaObject = this.getFieldSchemaObject();

  if (fieldArray.length == 0) {
    fullQuery += ' * ';
  } else {
    for (var i = 0; i < fieldArray.length; i++) {
      if (!schemaObject[fieldArray[i]]) {
        Logger.log("The field %s is not defined in the table %s and will be ignored.", fieldArray[i], this.fullTableName);
      }
      fullQuery += fieldArray[i];
      if (i != fieldArray.length - 1) {
        fullQuery += ',';
      }
    }
  }
  fullQuery += ' from `' + this.fullTableName + '` ';

  if (whereClauseArray != 0) {
    fullQuery += 'where ';
    for (var i = 0; i < whereClauseArray.length; i++) {
      if (!schemaObject[whereClauseArray[i].inputField]) {
        Logger.log("The field %s is not defined in the table %s and will be ignored.", whereClauseArray[i], this.fullTableName);
      }
      fullQuery += '(' + whereClauseArray[i].inputField + ' ' + whereClauseArray[i].operator + ' "' + whereClauseArray[i].value + '"';
      if (i != whereClauseArray.length - 1) {
        fullQuery += ') AND ';
      } else {
        fullQuery += ');';
      }
    }
  }

  for (var i = 0; i < optionalClauses.length; i++) {
    fullQuery += " " + optionalClauses[i];
  }
  Logger.log("fullQuery: '" + fullQuery + "'");
  return fullQuery;
};

/**
 * updates a row in the specific data table
 * @param  {Object} id  {idField:"aaa", idValue:"bbb"}
 * @param  {Array} args Array of Objects [{fieldName: "aaa", fieldValue:"bbb"},{fieldName: "ccc", fieldValue:"ddd"}]
 * @return {String} query
 */
StorageHandler.prototype.buildUpdateQuery = function(identifierMap, args) {
  if (typeof identifierMap == "undefined") {
    throw "No identifier for update process set.";
  }
  var query = "UPDATE ";
  query += "`" + this.fullTableName + "`";
  query += "SET "
  for (var i = 0; i < args.length; i++) {
    if (i < args.length - 1) {
      query += args[i].fieldName + " = '" + args[i].fieldValue + "',";
    } else {
      query += args[i].fieldName + " = '" + args[i].fieldValue + "'";
    }
  }
  query += " WHERE " + id.idField + " = '" + id.idValue + "'";

  return query;
};

//To be finished
StorageHandler.prototype.buildInsertQuery = function(entities) {
  var query = "INSERT (";
  for (var i = 0; i < this.fieldSchemaArray.length; i++) {
    if (i < this.fieldSchemaArray.length - 1) {
      query += this.fieldSchemaArray[i].name + ",";
    } else {
      query += this.fieldSchemaArray[i].name +")";
    }
  }
  for(var j = 0;j<entities.length;j++){
    query += "(";
  }

};

/**
 * queries the data table of the storage handler with a previously built query
 * @param  {String} queryString 
 * @return {Array} values of the response
 */
StorageHandler.prototype.queryDataTable = function(queryString) {
  var queryRequest = BigQuery.newQueryRequest();
  var fullTableName = this.projectId + ':' + this.dataSetId + '.' + this.tableId;
  queryRequest.query = queryString;
  queryRequest.useLegacySql = false;
  var query = BigQuery.Jobs.query(queryRequest, this.projectId);
  var values = [];

  while (!query.jobComplete) {
    Utilities.sleep(2000);
  }

  if (query.jobComplete) {
    for (var i = 0; i < query.rows.length; i++) {
      values[i] = [];
      var row = query.rows[i];
      for (var j = 0; j < row.f.length; j++) {
        values[i].push(row.f[j].v);
      }
    }
    return values;
  }
};

/**
 * Converts the standard javascript schema array into a BigQuery.TabelFieldSchema
 * @return {BigQuery.TabelFieldSchema}
 */
StorageHandler.prototype._convertSchemaIntoFields = function() {
  var schema = [];
  var internalSchema = this.getFieldSchemaArray();

  for (var i = 0; i < internalSchema.length; i++) {
    var newFieldSchema = BigQuery.newTableFieldSchema();
    newFieldSchema.name = internalSchema[i].name;
    newFieldSchema.type = internalSchema[i].type;
    newFieldSchema.description = internalSchema[i].description;
    schema.push(newFieldSchema);
  }
  return schema;
};