google-apps-script - 如何使用 UrlFetchApp 为 BigQuery Rest API 添加媒体上传？

Question

我需要将数据从我的 Google Apps 脚本插件流式传输到 BigQuery。

但我只需要使用我的服务帐户(我需要将数据插入我的 BigQuery 表，而不是用户的 BigQuery 表)

我按照这个例子:https://developers.google.com/apps-script/advanced/bigquery#load_csv_data

因为 Apps Script Advanced Service 本身不支持服务帐户，所以我需要稍微更改一下这个示例:

我需要从我的服务帐户获取 OAuth token ，而不是使用 Advanced Service BigQuery，然后使用 BigQuery Rest API 来处理相同的工作:

这是我做的:

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)
  }
}

正如您在我的代码中所见，我使用以下行获取了 Blob 数据(这是我需要放入 BigQuery 表中的实际 json 数据):

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

但我不知道在哪里使用这个data 和 BigQuery Rest API

文档没有提到它:https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/insert

如何做到这一点？谢谢。

Answer 1

我可以使用 Tanaike 的 FetchApp 库解决这个问题:

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

任何人以后遇到这个问题:请检查我在代码中的评论以了解所做的事情。

原来，job 变量被视为metadata，data 变量被视为file在表单数据对象中

// 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)
  }

注意:创建服务帐户时，选择角色 BigQuery Admin，或任何具有权限 bigquery.jobs.create

的角色

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

因为如果你不这样做，你就会有错误

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