Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/* 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;
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.