CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-bigquery
Charles Wallis
Charles Wallis

Reputation: 29

Apps script write to Big Query unknown error

This is supposed to read in a CSV and then write it to bigquery. When it runs, however, nothing is written, and there are no errors logged. I read that I need to write a csv and then turn it into an Octet Stream. I am not sure whether or not this is compatible with google bigquery.

function test(){
try{
  var tableReference = BigQuery.newTableReference();
  tableReference.setProjectId(PROJECT_ID);
  tableReference.setDatasetId(datasetId);
  tableReference.setTableId(tableId);
  var schema = "CUSTOMER:string, CLASSNUM:integer, CLASSDESC:string, CSR:string, CSR2:string, INSURANCE:string, REFERRALGENERAL:string, REFERRALSPECIFIC:string, NOTES:string, INMIN:integer, INHR:integer, OUTMIN:integer, OUTHR:integer, WAITMIN:integer, WAITHR:integer, DATETIMESTAMP:float, DATEYR:integer,DATEMONTH:integer, DATEDAY:integer";
  var load = BigQuery.newJobConfigurationLoad();
  load.setDestinationTable(tableReference);
  load.setSourceUris(URIs);
  load.setSourceFormat('NEWLINE_DELIMITED_JSON');
  load.setSchema(schema);
  load.setMaxBadRecords(0);
  load.setWriteDisposition('WRITE_TRUNCATE');

  var configuration = BigQuery.newJobConfiguration();
  configuration.setLoad(load);

  var newJob = BigQuery.newJob();
  newJob.setConfiguration(configuration);


  var loadr = DriveApp.getFilesByName("test.csv");
  var x = loadr.next().getBlob();
  Logger.log(x.getDataAsString());

  var d = DriveApp.getFilesByName("test.csv");
  var id = d.next().getId();
  Logger.log(id);
  var data = DocsList.getFileById(id).getBlob().getDataAsString();
  var mediaData = Utilities.newBlob(data, 'application/octet-stream');

  BigQuery.Jobs.insert(newJob, PROJECT_ID, mediaData) 
 }
 catch(error){Logger.log("A" + error.message);}
}

Upvotes: 1

Views: 391

Answers (1)

Pentium10
Pentium10

Reputation: 207912

Your sourceFormat is wrong for CSV files:

The format of the data files. For CSV files, specify "CSV". For datastore backups, specify "DATASTORE_BACKUP". For newline-delimited JSON, specify "NEWLINE_DELIMITED_JSON". The default value is CSV.

https://developers.google.com/bigquery/docs/reference/v2/jobs#configuration.load.sourceUris

On the other hand I think you don't need at all the load.setSourceUris(URIs); since you try to load from local file, and not from Google Cloud Storage. Check this python example https://developers.google.com/bigquery/loading-data-into-bigquery

Upvotes: 3

Related Questions