Apps script write to Big Query unknown error

Question

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);}
}

Do you have an answer to your question? thanks

David Cesar Santos
– David Cesar Santos

2015-05-13 09:29:22 +00:00
Commented May 13, 2015 at 9:29 — David Cesar Santos
– David Cesar Santos, Commented May 13, 2015 at 9:29

Pentium10 · Accepted Answer · 2014-09-11 07:45:12Z

3

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

edited Sep 11, 2014 at 7:45

answered Sep 10, 2014 at 21:25

Pentium10

209k124 gold badges435 silver badges513 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charles Wallis Over a year ago

I made changed the sourceFormat to CSV and removed the setSourceUris, but still it does not write and still it does not show an error. I put in the setSourceUris again and there are no errors. I am really unsure what to do. All I need to do is get this part working and I am finished with the Bigquery aspect of this project.

Collectives™ on Stack Overflow

Apps script write to Big Query unknown error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related