CODEWITHSUNDEEP
node.jsperformanceexpressgoogle-bigquery
Avi L
Avi L

Reputation: 1708

Should I call BigQuery for every row insert or should I insert a bulk?

I have a NodeJs service (build on Express) that has 100M requests per day and for every request a new data row is being sent to BigQuery. Is it better, performance wise, to keep sending the rows separately or should I gather rows and every X seconds/minutes send them to BigQuery as a bulk? (the calls are done asynchronously)

My BigQuery repository class looks something like this: (on Express service launch the repository is initialised by calling .init() and for every row insert the service call .add())

function BQRepository() {
}

BQRepository.prototype.init = async function() {
    this.bigQueryClient = new BigQuery({ projectId: ..., keyFilename: ... });    
}

BQRepository.prototype.add = async function(tableName, obj) {
    this.bigQueryClient
        .dataset(...)
        .table(tableName)
        .insert(obj)
        .then(() => {
          logger.debug(`object added`)
        })
        .catch(err => {
                    logger.error('error occurred')
        });
}

var bqRepo = new BQRepository()
module.exports = bqRepo;

Upvotes: 0

Views: 370

Answers (1)

jakemingolla
jakemingolla

Reputation: 1639

I'm not sure there is enough information in the question to provide a cut-and-dry answer to this since you do not appear to be hitting any hard limits with your current transaction per request method. Gathering up requests to do bulk transactions may help limit network I/O at the cost of potentially losing transactions if the service fails in between each bulk transaction. I would recommend doing performance testing to provide a direct comparison of each method and understand their pros & cons for your use case.

One last thing that you neglected could be cost, depending on the size of an individual row. Based off of the BigQuery cost projections:

Individual rows are calculated using a 1 KB minimum size

Depending on the size of your objects, if you have sufficiently small rows you may be being overcharged unless you bundle them up into bulk transactions.

Upvotes: 1

Related Questions