CODEWITHSUNDEEP
google-apps-scriptgoogle-bigquery
vinay
vinay

Reputation: 1

is bigquery the right choice?

I've a table on google spreadsheet, which needs to analyzed..

I had a look at big-query and it seems like what i need..

I'm still in a testing phase so my table is pretty dam small.. since my table is just 40x10 i am wondering if its a bit of an overkill..

But i do need the results immediately and it runs every minute for roughly 7 to 8 hours per day .. 5 days a week..

Also Google says first 100gb processed is free.

I will be staying well within my limits even with billing enabled right?? I don't want to be billed 4k$ like some of the people have faced... so wanted to understand how this works..

For a table of 40x10.. max 10 queries for the one table(per minute).. 7 hours a day.. 5 days a week..

I'll stay well within my limits right?? And yes am using apps script (for now) to get this up and running..

Upvotes: 0

Views: 161

Answers (2)

Jeremy Condit
Jeremy Condit

Reputation: 7046

Nope, BigQuery is probably not appropriate for your use case.

First, BigQuery cannot query directly over data in a Google spreadsheet.

Second, BigQuery is optimized and intended for querying massive amounts of data, so using it to query a 40x10 table is not ideal in terms of speed or efficiency.

Third, each query is billed for a minimum of 1 MB of data, regardless of how small your table is (see pricing details here). At 10 queries per minute, 8 hours per day, 5 days per week, you'll hit just about exactly 100 GB of data in a 30-day month, and may very well go over your free quota now and then.

If you're just running one query that doesn't need to change very often, coding it up directly in AppsScript seems like a reasonable approach.

Upvotes: 1

Adelin
Adelin

Reputation: 8219

I'd use ScriptDB since your db is really small.

A short example of how it works (with an inbox example):

function checkMessages(name) {
var db = ScriptDb.getMyDb();
var msgs = db.query({table: "messages", IDto: name, statusreceiving: db.not("read")});
var infoMessages = [];
var messagesCount = msgs.getSize();
var stateMessages = false;
var idMessages = [];

while (msgs.hasNext()) {
var me = msgs.next();
  idMessages.push(me.messageID);
  me.statusreceiving = "received";
  db.save(me);
  }
if (messagesCount>0) {
stateMessages = true;
}
infoMessages.push(stateMessages,messagesCount,idMessages);
return infoMessages;
}

Upvotes: 1

Related Questions