AdWord Script Export to BigQuery "Empty Response" -
utilizing following adwords script export bigquery, bigquery.jobs.insert causing script terminate due "empty response". reason call not getting response?
var accounts = ['xxx','xxx']; var config = { bigquery_project_id: 'xxx', bigquery_dataset_id: 'xxx', // truncate existing data, otherwise append. truncate_existing_dataset: true, truncate_existing_tables: true, // reports google drive. write_data_to_drive: false, // folder put intermediate files. drive_folder: 'adwords big query test', // default date range on statistics fields retrieved. default_date_range: '20140101,20140105', // lists of reports , fields retrieve adwords. reports: [{name: 'keywords_performance_report', conditions: 'where impressions>0', fields: {'accountdescriptivename' : 'string', 'date' : 'string', 'campaignid' : 'string', 'campaignname' : 'string', 'adgroupid' : 'string', 'adgroupname' : 'string', 'id' : 'string', 'criteria' : 'string', 'keywordmatchtype' : 'string', 'adnetworktype1' : 'string', 'adnetworktype2' : 'string', 'device' : 'string', 'averageposition' : 'string', 'qualityscore' : 'string', 'cpcbid' : 'string', 'topofpagecpc' : 'string', 'impressions' : 'string', 'clicks' : 'string', 'convertedclicks' : 'string', 'cost' : 'string', 'conversions' : 'string' } }], recipient_emails: [ 'xxx', ] }; function main() { createdataset(); (var = 0; < config.reports.length; i++) { var reportconfig = config.reports[i]; createtable(reportconfig); } folder = getdrivefolder(); // account iterator. var accountiterator = mccapp.accounts().withids(accounts).withlimit(10).get(); var jobidmap = {}; while (accountiterator.hasnext()) { // current account. var account = accountiterator.next(); // select child account. mccapp.select(account); // run reports against child account. var accountjobids = processreports(folder, account.getcustomerid()); jobidmap[account.getcustomerid()] = accountjobids; } waittilljobscomplete(jobidmap); sendemail(jobidmap); } function createdataset() { if (datasetexists()) { if (config.truncate_existing_dataset) { bigquery.datasets.remove(config.bigquery_project_id, config.bigquery_dataset_id, {'deletecontents' : true}); logger.log('truncated dataset.'); } else { logger.log('dataset %s exists. not recreate.', config.bigquery_dataset_id); return; } } // create new dataset. var dataset = bigquery.newdataset(); dataset.friendlyname = config.bigquery_dataset_id; dataset.datasetreference = bigquery.newdatasetreference(); dataset.datasetreference.projectid = config.bigquery_project_id; dataset.datasetreference.datasetid = config.bigquery_dataset_id; dataset = bigquery.datasets.insert(dataset, config.bigquery_project_id); logger.log('created dataset id %s.', dataset.id); } /** * checks if dataset exists in project. * * @return {boolean} returns true if dataset exists. */ function datasetexists() { // list of datasets in project. var datasets = bigquery.datasets.list(config.bigquery_project_id); var datasetexists = false; // iterate through each dataset , check id match. if (datasets.datasets != null) { (var = 0; < datasets.datasets.length; i++) { var dataset = datasets.datasets[i]; if (dataset.datasetreference.datasetid == config.bigquery_dataset_id) { datasetexists = true; break; } } } return datasetexists; } function createtable(reportconfig) { if (tableexists(reportconfig.name)) { if (config.truncate_existing_tables) { bigquery.tables.remove(config.bigquery_project_id, config.bigquery_dataset_id, reportconfig.name); logger.log('truncated dataset %s.', reportconfig.name); } else { logger.log('table %s exists. not recreate.', reportconfig.name); return; } } // create new table. var table = bigquery.newtable(); var schema = bigquery.newtableschema(); var bigqueryfields = []; // add account column table. var accountfieldschema = bigquery.newtablefieldschema(); accountfieldschema.description = 'accountid'; accountfieldschema.name = 'accountid'; accountfieldschema.type = 'string'; bigqueryfields.push(accountfieldschema); // add each field table schema. var fieldnames = object.keys(reportconfig.fields); (var = 0; < fieldnames.length; i++) { var fieldname = fieldnames[i]; var bigqueryfieldschema = bigquery.newtablefieldschema(); bigqueryfieldschema.description = fieldname; bigqueryfieldschema.name = fieldname; bigqueryfieldschema.type = reportconfig.fields[fieldname]; bigqueryfields.push(bigqueryfieldschema); } schema.fields = bigqueryfields; table.schema = schema; table.friendlyname = reportconfig.name; table.tablereference = bigquery.newtablereference(); table.tablereference.datasetid = config.bigquery_dataset_id; table.tablereference.projectid = config.bigquery_project_id; table.tablereference.tableid = reportconfig.name; table = bigquery.tables.insert(table, config.bigquery_project_id, config.bigquery_dataset_id); logger.log('created table id %s.', table.id); } function tableexists(tableid) { // list of tables in dataset. var tables = bigquery.tables.list(config.bigquery_project_id, config.bigquery_dataset_id); var tableexists = false; // iterate through each table , check id match. if (tables.tables != null) { (var = 0; < tables.tables.length; i++) { var table = tables.tables[i]; if (table.tablereference.tableid == tableid) { tableexists = true; break; } } } return tableexists; } function processreports(folder, accountid) { var jobids = []; // iterate on each report type. (var = 0; < config.reports.length; i++) { var reportconfig = config.reports[i]; logger.log('running report %s account %s', reportconfig.name, accountid); // data csv var csvdata = retrieveadwordsreport(reportconfig, accountid); // if configured, data. if (config.write_data_to_drive) { var filename = reportconfig.name + '_' + accountid; folder.createfile(filename, csvdata, mimetype.csv); logger.log('exported data drive folder ' + config.drive_folder + ' report ' + filename); } // convert blob format. var blobdata = utilities.newblob(csvdata, 'application/octet-stream'); // load data var jobid = loaddatatobigquery(reportconfig, blobdata); jobids.push(jobid); } return jobids; } function retrieveadwordsreport(reportconfig, accountid) { var fieldnames = object.keys(reportconfig.fields); var report = adwordsapp.report( 'select ' + fieldnames.join(',') + ' ' + reportconfig.name + ' ' + reportconfig.conditions + ' during ' + config.default_date_range); var rows = report.rows(); var csvrows = []; // header row csvrows.push('accountid,'+fieldnames.join(',')); // iterate on each row. while (rows.hasnext()) { var row = rows.next(); var csvrow = []; csvrow.push(accountid); (var = 0; < fieldnames.length; i++) { var fieldname = fieldnames[i]; var fieldvalue = row[fieldname].tostring(); var fieldtype = reportconfig.fields[fieldname]; /* strip off % , perform other formatting here. if ((fieldtype == 'float' || fieldtype == 'integer') && fieldvalue.charat(fieldvalue.length - 1) == '%') { fieldvalue = fieldvalue.substring(0, fieldvalue.length - 1); }*/ // add double quotes string values. if (fieldtype == 'string') { fieldvalue = fieldvalue.replace(',', ''); //handle fields comma in value returned fieldvalue = fieldvalue.replace('"', ''); //handle fields double quotes in value returned fieldvalue = fieldvalue.replace('+', ''); //handle fields "+" in value returned fieldvalue = '"' + fieldvalue + '"'; } csvrow.push(fieldvalue); } csvrows.push(csvrow.join(',')); } logger.log('downloaded ' + reportconfig.name + ' account ' + accountid + ' ' + csvrows.length + ' rows.'); return csvrows.join('\n'); } function getdrivefolder() { var folders = driveapp.getfoldersbyname(config.drive_folder); // assume first folder correct one. if (folders.hasnext()) { logger.log('folder name found. using existing folder.'); return folders.next(); } return driveapp.createfolder(config.drive_folder); } function loaddatatobigquery(reportconfig, data) { function guid() { function s4() { return math.floor((1 + math.random()) * 0x10000) .tostring(16) .substring(1); } return s4() + s4() + s4() + s4() + s4() + s4() + s4() + s4(); } var makeid = guid(); var job = { jobreference: { jobid: makeid }, configuration: { load: { destinationtable: { projectid: config.bigquery_project_id, datasetid: config.bigquery_dataset_id, tableid: reportconfig.name }, skipleadingrows: 1, ignoreunknownvalues: true, allowjaggedrows: true, allowlargeresults: true } } }; var insertjob = bigquery.jobs.insert(job, config.bigquery_project_id, data); logger.log('load job started %s. check on status of here: ' + 'https://bigquery.cloud.google.com/jobs/%s', reportconfig.name, config.bigquery_project_id); return job.jobreference.jobid; } function waittilljobscomplete(jobidmap) { var complete = false; var remainingjobs = []; var accountids = object.keys(jobidmap); (var = 0; < accountids.length; i++){ var accountjobids = jobidmap[accountids[i]]; remainingjobs.push.apply(remainingjobs, accountjobids); } while (!complete) { if (adwordsapp.getexecutioninfo().getremainingtime() < 5){ logger.log('script timeout, jobs ' + remainingjobs.join(',') + ' still incomplete.'); } remainingjobs = getincompletejobs(remainingjobs); if (remainingjobs.length == 0) { complete = true; } if (!complete) { logger.log(remainingjobs.length + ' jobs still being processed.'); // wait 5 seconds before checking status again. utilities.sleep(5000); } } logger.log('all jobs processed.'); } function getincompletejobs(jobids) { var remainingjobids = []; (var = 0; < jobids.length; i++) { var jobid = jobids[i]; var getjob = bigquery.jobs.get(config.bigquery_project_id, jobid); if (getjob.status.state != 'done') { remainingjobids.push(jobid); } } return remainingjobids; } it appears "empty response" error being thrown on:
var insertjob = bigquery.jobs.insert(job, config.bigquery_project_id, data); have tried quite few tweaks, answer doesn't appear obvious me. help!
i can wrong - think problem jobid because of issue guid() function - missing "+" sign.
function guid() { function s4() { return math.floor((1 + math.random()) * 0x10000) .tostring(16) .substring(1); } return s4() + s4() + s4() + s4() + s4() s4() + s4() + s4(); } why not use jobid response below?
var job = { configuration: { load: { destinationtable: { projectid: config.bigquery_project_id, datasetid: config.bigquery_dataset_id, tableid: reportconfig.name }, skipleadingrows: 1, ignoreunknownvalues: true, allowjaggedrows: true, allowlargeresults: true } } }; var insertjob = bigquery.jobs.insert(job, config.bigquery_project_id, data); logger.log('load job started %s. check on status of here: ' + 'https://bigquery.cloud.google.com/jobs/%s', reportconfig.name, config.bigquery_project_id); return insertjob.jobreference.jobid; added
in case suggest log jobid (makeid = guid()) , job status following below link https://cloud.google.com/bigquery/docs/reference/v2/jobs/get#try-it
enter projectid , jobid , @ least see going on job!!
Comments
Post a Comment