How to add media upload for BigQuery Rest API using UrlFetchApp?

Question

I need to stream data into BigQuery from my Google Apps Script addon.

But I need to use my service account only (I need to insert data into my BigQuery table, not user's BigQuery table)

I followed this example: https://developers.google.com/apps-script/advanced/bigquery#load_csv_data

Because Apps Script Advanced Service doesn't support service account natively, so I need to change this example a bit:

Instead of using Advanced Service BigQuery, I need to get the OAuth token from my service account, then using BigQuery Rest API to handle the same job:

This is what I did:

function getBigQueryService() {
  return (
    OAuth2.createService('BigQuery')
      // Set the endpoint URL.
      .setTokenUrl('https://accounts.google.com/o/oauth2/token')

      // Set the private key and issuer.
      .setPrivateKey(PRIVATE_KEY)
      .setIssuer(CLIENT_EMAIL)

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getScriptProperties())

      // Caching
      .setCache(CacheService.getUserCache())

      // Locking
      .setLock(LockService.getUserLock())

      // Set the scopes.
      .setScope('https://www.googleapis.com/auth/bigquery')
  )
}

export const insertLog = (userId, type) => {
  const bigQueryService = getBigQueryService()
  if (!bigQueryService.hasAccess()) {
    console.error(bigQueryService.getLastError())
    return
  }

  const projectId = bigqueryCredentials.project_id
  const datasetId = 'usage'
  const tableId = 'logs'
  const row = {
    timestamp: new Date().toISOString(),
    userId,
    type,
  }

  const data = Utilities.newBlob(convertToNDJson(row), 'application/octet-stream')

  // Create the data upload job.
  const job = {
    configuration: {
      load: {
        destinationTable: {
          projectId,
          datasetId,
          tableId,
        },
        sourceFormat: 'NEWLINE_DELIMITED_JSON',
      },
    },
  }

  const url = `https://bigquery.googleapis.com/upload/bigquery/v2/projects/${projectId}/jobs`
  const headers = {
    Authorization: `Bearer ${bigQueryService.getAccessToken()}`,
    'Content-Type': 'application/json',
  }

  const options = {
    method: 'post',
    headers,
    payload: JSON.stringify(job),
  }

  try {
    const response = UrlFetchApp.fetch(url, options)
    const result = JSON.parse(response.getContentText())

    console.log(JSON.stringify(result, null, 2))
  } catch (err) {
    console.error(err)
  }
}

As you can see in my code, I get the Blob data (which is the actual json data that I need to put in BigQuery table) using this line:

const data = Utilities.newBlob(convertToNDJson(row), 'application/octet-stream')

But I don't know where to use this data with the BigQuery Rest API

The documentation doesn't mention it: https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/insert

How this can be done? Thank you.

You need to send file and the job config as ['multipart/related'](https://cloud.google.com/bigquery/docs/loading-data-local#multipart) data. See related [example](https://stackoverflow.com/a/48310991/). Advanced Google services used to handle it for you. Now, you need to do this on your own — TheMaster, Sep 23 '19 at 17:42
As an additional information for @TheMaster 's comment, how about using [this GAS library](https://github.com/tanaikech/FetchApp)? This library creates and requests the type of `multipart/form-data` using Google Apps Script. — Tanaike, Sep 23 '19 at 22:32
@Tanaike: I see that what TheMaster mentioned was your code. Is there any documentation about those "\r\n" and "--". It's hard to understand that convention just by reading code. Also, thanks for your library, I think I will use it later if the manual way doesn't work. — 0xh8h, Sep 24 '19 at 01:32
@TheMaster: for the job.insert API, which parameters are corresponding to the "job" and "data"? The documentation is quite unclear for me — 0xh8h, Sep 24 '19 at 02:32
@Tanaike: your library FetchApp is neat. It solved the problem now and I will post it as the answer. But please give me an explanation about the manual way. I will also update the manual way in the answer when I understand it. — 0xh8h, Sep 24 '19 at 03:14
Thank you for replying. I'm glad your issue was resolved. About the documents for multipart/form-data, how about [this](https://developers.google.com/drive/api/v3/manage-uploads) and [this](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Disposition)? I created the GAS library from these documents. The library creates the request body for multipart/form-data by inputting parameters and request it. So I cannot understand about `the manual way`. If you want to get the request body, for example, how about checking the library under the debug mode of the script editor? — Tanaike, Sep 24 '19 at 03:25
@Tanaike: thank you. I mean `the manual way` is just how you built the string with those `\r\n` and `--`. But I think the link you provided is what I need to understand it https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Disposition — 0xh8h, Sep 24 '19 at 03:34
Also look into [rfc2387](http://tools.ietf.org/html/rfc2387) and [rfc7578](https://tools.ietf.org/html/rfc7578). Despite bad font, it's actually easier to understand. — TheMaster, Sep 24 '19 at 07:03

0xh8h · Accepted Answer · 2019-09-24T03:30:45.237

I can solve this problem using Tanaike's FetchApp library:

https://github.com/tanaikech/FetchApp#fetch

Anyone has this issue in the future: please check my comment in code to understand what was done.

Turn out, the job variable is treated as metadata, and the data variable is treated as file in the form data object

// First you need to convert the JSON to Newline Delimited JSON,
// then turn the whole thing to Blob using Utilities.newBlob

const data = Utilities.newBlob(convertToNDJson(row), 'application/octet-stream')

  // Create the data upload job.
  const job = {
    configuration: {
      load: {
        destinationTable: {
          projectId,
          datasetId,
          tableId,
        },
        sourceFormat: 'NEWLINE_DELIMITED_JSON',
      },
    },
  }

  const url = `https://bigquery.googleapis.com/upload/bigquery/v2/projects/${projectId}/jobs?uploadType=multipart`
  const headers = {
    Authorization: `Bearer ${bigQueryService.getAccessToken()}`,
  }

  const form = FetchApp.createFormData() // Create form data
  form.append('metadata', Utilities.newBlob(JSON.stringify(job), 'application/json'))
  form.append('file', data)

  const options = {
    method: 'post',
    headers,
    muteHttpExceptions: true,
    body: form,
  }

  try {
    FetchApp.fetch(url, options)
  } catch (err) {
    console.error(err)
  }

Note: When you create the service account, choose role BigQuery Admin, or any role that has permission bigquery.jobs.create

https://cloud.google.com/bigquery/docs/access-control#bigquery-roles

Because if you don't, you will have the error

User does not have bigquery.jobs.create permission...

How to add media upload for BigQuery Rest API using UrlFetchApp?

1 Answers1

Linked