Skip to content

Instantly share code, notes, and snippets.

@gschoppe
Last active January 14, 2021 18:50
Show Gist options
  • Save gschoppe/2241a098f56baabd5c17a0fb8c5dc342 to your computer and use it in GitHub Desktop.
Save gschoppe/2241a098f56baabd5c17a0fb8c5dc342 to your computer and use it in GitHub Desktop.
function DB(projectID) {
this.projectID = projectID;
this.types = {
'INT64' : "INT64",
'NUMERIC' : "NUMERIC",
'BIGNUMERIC': "BIGNUMERIC",
'FLOAT64' : "FLOAT64",
'BOOL' : "BOOL",
'STRING' : "STRING",
'BYTES' : "BYTES",
'DATE' : "DATE",
'DATETIME' : "DATETIME",
'TIME' : "TIME",
'TIMESTAMP' : "TIMESTAMP",
'GEOGRAPHY' : "GEOGRAPHY",
'ARRAY' : function(subtype) {
return {
'type' : "ARRAY",
'arrayType': subtype
};
},
'STRUCT' : function(subtype) {
return {
'type' : "STRUCT",
'structTypes': subtype
};
}
};
this.parameter = function(name, type, value) {
var paramType = BigQuery.newQueryParameterType();
if (typeof type === 'object') {
if (type.type == 'ARRAY') {
paramType.type = type.type;
paramType.arrayType = type.arrayType;
} else if (type.type == 'STRUCT') {
paramType.type = type.type;
paramType.structTypes = type.structTypes;
} else {
throw new Exception("Invalid Parameter Type.");
}
} else {
paramType.type = type;
}
var paramValue = BigQuery.newQueryParameterValue();
paramValue.value = value;
return {
'name' : name,
'parameterType' : paramType,
'parameterValue': paramValue
}
}
this.query = function(query, params) {
var queryRequest = BigQuery.newQueryRequest();
// configure query request
queryRequest.dryRun = false;
queryRequest.query = query;
if(params && params.length) {
queryRequest.queryParameters = params;
queryRequest.parameterMode = 'NAMED';
}
queryRequest.useLegacySql = false;
queryRequest.useQueryCache = true;
// run query job and wait for results
var queryResults = BigQuery.Jobs.query(queryRequest, this.projectID);
var jobID = queryResults.jobReference.jobId;
var sleepTimeMs = 100;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
queryResults = BigQuery.Jobs.getQueryResults(this.projectID, jobID);
}
// check for errors
if (queryResults.errors) {
var errorMessages = queryResults.errors.map(function (error) {
return error.message;
});
throw new Exception(errorMessages.join('\n'));
}
// return num affected rows for insert/modify/delete
if (typeof queryResults.numDmlAffectedRows !== 'undefined') {
return parseInt(queryResults.numDmlAffectedRows);
}
// Get the table headers.
var tableFieldSchema = queryResults.schema.fields;
var headers = tableFieldSchema.map(function (field) {
return field.name;
});
// concat all the results
var rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(this.projectID, jobID, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
// map results to headers
rows = rows.map(function (row) {
var fields = row.f;
row = {};
for (var i = 0; i < fields.length; i++) {
var fieldName = this.headers[i];
var value = fields[i].v;
row[fieldName] = value;
}
return row;
}, { headers: headers });
return rows;
}
}
// Replace this value with the project ID listed in the Google
// Cloud Platform project.
var projectId = "XXXXXXXX";
function example_string_query() {
var db = new DB(ACCT_ID);
var query = 'SELECT * FROM publicdata.samples.shakespeare WHERE LENGTH(word) > 10 LIMIT 10;';
var results = db.query(query);
Logger.log(JSON.stringify(results));
}
function example_parametric_query() {
var db = new DB(ACCT_ID);
var query = 'SELECT * FROM publicdata.samples.shakespeare WHERE LENGTH(word) > @wordlength LIMIT @limit;';
var params = [
db.parameter('wordlength', 'INT64', 10),
db.parameter('limit', 'INT64', 10)
];
var results = db.query(query, params);
Logger.log(JSON.stringify(results));
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment