Google Apps Script - How to stream JSON data into BigQuery?

Question

In this reference https://developers.google.com/apps-script/advanced/bigquery,

In order to load CSV data into BigQuery, they use:

var file = DriveApp.getFileById(csvFileId);
  var data = file.getBlob().setContentType('application/octet-stream');

  // Create the data upload job.
  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        },
        skipLeadingRows: 1
      }
    }
  };
  job = BigQuery.Jobs.insert(job, projectId, data);

As I understand, they send a blob to BigQuery file.getBlob().setContentType('application/octet-stream');, which is not friendly

How to send a JSON to BigQuery in Apps Script?

With the library @google-cloud/bigquery (using in a project outside of Apps Script), I can do something like this:

https://cloud.google.com/bigquery/streaming-data-into-bigquery#streaminginsertexamples

// Import the Google Cloud client library
const { BigQuery } = require('@google-cloud/bigquery')
const moment = require('moment')

exports.insertUsageLog = async (userId) => {
  const datasetId = 'usage'
  const tableId = 'logs'
  const rows = [
    // The JSON data is collected here
    {
      timestamp: moment.utc().toISOString(),
      userId,
      // Something else ...
    },
  ]

  // Create a client
  const bigqueryClient = new BigQuery()

  // Insert data into a table
  await bigqueryClient
    .dataset(datasetId)
    .table(tableId)
    .insert(rows)
  console.log(`Inserted ${rows.length} rows`)
}

score 3 · Accepted Answer · answered Sep 20 '19 at 12:00

3

The data payload to BigQuery.Jobs.insert() has to be a blob.

You can create that blob from either CSV content or a newline delimited JSON. Newline delimited JSON is a distinct form of JSON that is required by BigQuery. It is not natively supported by Apps Script. However, you should be able to convert standard JSON to that format by creating a custom replacer function and passing it as a parameter to JSON.stringify(). Alternatively, you might be able to leverage an existing Javascript library (you might be able to find something via NPM or just doing a search on Github).

Once you've generated a newline delimited JSON (as either a string or a byte array) you'll need to convert it to a blob using Utilities.newBlob() and pass it to the BigQuery.Jobs.insert() method.

answered Sep 20 '19 at 12:00

TheAddonDepot

8,408
2
20
30

2

Note that Newline delimited json is NOT a json in and of itself, but it's a list of valid json separated by new lines. – TheMaster Sep 20 '19 at 15:37
@Dimu Designs: Thanks. Because the data sent to BigQuery must be blob, so as I understand, the library `@google-cloud/bigquery` will handle all these tasks that you mentioned for us? You can check the example here: https://cloud.google.com/bigquery/streaming-data-into-bigquery#streaminginsertexamples. – 0xh8h Sep 21 '19 at 04:59
Btw, I checked this article https://hackernoon.com/json-lines-format-76353b4e588d and I finally know why BigQuery uses ndjson – 0xh8h Sep 21 '19 at 06:16
@Dimu Designs: There are some packages on npm that support ndjson, but for Apps Script, I can not find anything. Can you suggest a function to do the job? – 0xh8h Sep 21 '19 at 06:54
Dimu and @TheMaster: I got another error "GoogleJsonResponseException: API call to bigquery.jobs.insert failed with error: Insufficient Permission". Both the apps script project and the big query are under the same GCP project, also I don't see where to configure the service account for apps script, so I thought it should have the permission automatically, right? – 0xh8h Sep 21 '19 at 07:55
@Hoang Setting required scopes might help. See references [here](https://stackoverflow.com/a/58025407/) – TheMaster Sep 21 '19 at 08:03
@TheMaster: This is my appsscript.json https://prnt.sc/p90ba3. I had scope "script.external_request" – 0xh8h Sep 21 '19 at 08:08
@Hoang Add big query scopes as seen in bigquery.insert documentation. – TheMaster Sep 21 '19 at 08:10
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/199762/discussion-between-hoang-trinh-and-themaster). – 0xh8h Sep 21 '19 at 08:15
This worked for getting rid of the newlines in a json string jsonText= jsonText.replace(/(\r\n|\n|\r)/gm,""); – makmak Aug 22 '20 at 02:53

Google Apps Script - How to stream JSON data into BigQuery?

1 Answers1