CODEWITHSUNDEEP
google-apps-scriptgoogle-bigquery
Adventurous Leo
Adventurous Leo

Reputation: 46

Can't use Google App Script to upload an unzipped csv into BigQuery with bigger size data

I use a platform to send a report with CSV/ZIP file to my Gmail automatically, the platform will decide which type to send based on the size. And I build this Google Apps Script to push this file to BigQuery.

I try this on a smaller size zip (zip like few hundred KBs, 25MB when unzipped), and it has no problem. However, when it goes to a bigger size (zip like 3MB, 77MB when unzipped), I got an error message when processing the Job.Load. Wondering if anyone had experience this issue and willing to share some tips. Thanks.

Sorry that the error message showing on my UI is Chinese, so I just skip attaching that to this thread:(

Here's the code:

function BQLoad() {
var projectId = 'quantum-feat-211902';
var datasetId = 'DBM_dataset01';
var tableId = 'test';
var thread = GmailApp.getInboxThreads(0,1)[0]; 
var message = thread.getMessages()[0]; 
var attachments = message.getAttachments()[0];
var firstThread = GmailApp.getInboxThreads(0,1)[0];
var messages = firstThread.getMessages();
var job = {
configuration: {
  load: {
    destinationTable: {
      projectId: projectId,
      datasetId: datasetId,
      tableId: tableId,
    },
    skipLeadingRows: 1,
    ignoreUnknownValues: true,
    writeDisposition: "WRITE_TRUNCATE",
    sourceFormat: 'CSV',
    fieldDelimiter: ',',
    allowJaggedRows: true,
    encoding: "UTF-8",
    maxBadRecords: 1000,
  },
},
};
for (var i = 0; i < messages.length; i++) {
Logger.log(messages[i].getSubject());
}
Logger.log(attachments.getContentType());
if (attachments.getContentType() === "application/zip") {
var data = Utilities.unzip(attachments)[0];
}
else {
var data = attachments;
}
job = BigQuery.Jobs.insert(job, projectId, data);
Logger.log('Load job started. Check on the status of it here: ' +
  'https://bigquery.cloud.google.com/jobs/%s', projectId);
}

Upvotes: 0

Views: 137

Answers (1)

Stu
Stu

Reputation: 148

Posting answer from @Adventurous Leo for visibility

I think I just realized why this happened. It's my quota, I have only 50MB per URI, I'll just reduce the size and do a batch upload. Still thanks to you two for giving me directions

Upvotes: 1

Related Questions