如何使用 BigQuery 补丁? [英] How do I use BigQuery patch?

问题描述

在 BigQuery API 文档中，有一个称为 patch 的方法.我希望我可以使用它来更改现有表的架构.不幸的是，bq 不支持它.但是根据他们的网站，您可以在 https://developers 上试用.google.com/bigquery/docs/reference/v2/tables/patch.但是，当我尝试发送以下请求时:

PATCH https://www.googleapis.com/bigquery/v2/projects/(my project id)/datasets/tmp_bt/tables/change_cols?key={YOUR_API_KEY}内容类型:应用程序/json授权:承载(已删除)X-JavaScript-User-Agent:Google APIs Explorer{架构":{领域":[{},{},{模式":可为空"，"name": "gotchahere",类型":字符串"}]}}

(我不知道空元素从何而来，编辑器太痛苦了，无法仅粘贴我现有的表定义.我注意到它缺少必需的元素，例如我的项目 ID，这是我期望的包括在内，因为它们在表单中是必需的)，然后我得到了回复:

cache-control: private, max-age=0内容编码:gzip内容长度:122内容类型:应用程序/json；字符集=UTF-8日期:2013 年 6 月 13 日星期四 22:22:09 GMT到期时间:2013 年 6 月 13 日星期四 22:22:09 GMT服务器:GSE{错误": {错误":[{"域": "全局",原因":后端错误"，"message": "后端错误"}],代码":503，"message": "后端错误"}}

完全没用.我已经进行了网络搜索，但没有找到任何正在使用的示例.

谁能给我一个使用 BigQuery 补丁更改表的示例，并说明它实际上可以做什么?

解决方案

TLDR:您需要在补丁请求的正文中提供完整的架构，而不仅仅是您尝试提供的字段添加.后端错误很可能是由该数组中的空字段引起的.

<小时>

BigQuery 的 API 允许通过两种方式更新表(和其他资源):更新和补丁.

update 方法将表资源替换为您提供的新资源.如果您想要获取现有表资源，对其进行修改，然后将该修改后的表资源完整地发布回 BigQuery，则此方法非常有用.(但是请注意，对象的某些字段，例如 creationTime，被认为是不可变的，因此为这些字段提供的新值将被忽略.)

patch 方法仅替换您在请求中包含的字段，并且保留其余资源不变.如果您想对一个字段进行单独更改而不用担心其他字段，则此方法很有用.该算法递归地应用于任何嵌套对象，但不应用于嵌套数组.换句话说，您随请求发送的补丁资源将与现有资源递归合并，直到遇到数组或标量值，此时补丁对象中的数组或值将替换现有资源中的数组或值.

由于您尝试更新的架构包含一个字段数组，并且由于 patch 方法会批量更新数组，因此您的 patch 对象需要在结果架构中包含您想要的完整字段数组.(不能通过在补丁对象中指定单字段数组来添加字段.)

请注意，表 ID 在请求 URL 中给出，因此不需要包含在对象本身中.

最后，后端错误是我们这边的问题，可能是您的请求中的两个空架构字段造成的.我们将进一步挖掘并希望在未来改进错误消息.

In the BigQuery API documentation there is a method called patch. I am hoping that I can use it to alter the schema of an existing table. Unfortunately it is not supported by bq. But according to their website, you can try it at https://developers.google.com/bigquery/docs/reference/v2/tables/patch. However when I try it sends the following request:

PATCH https://www.googleapis.com/bigquery/v2/projects/(my project id)/datasets/tmp_bt/tables/change_cols?key={YOUR_API_KEY}

Content-Type:  application/json
Authorization:  Bearer (removed)
X-JavaScript-User-Agent:  Google APIs Explorer

{
 "schema": {
  "fields": [
   {
   },
   {
   },
   {
    "mode": "nullable",
    "name": "gotchahere",
    "type": "string"
   }
  ]
 }
}

(I have no idea where the empty elements came from, and the editor is too painful to use to just paste in my existing table definition. I note that it is missing required elements like my project ID, which I expected to be included because they were required in the form) and then I get the response:

cache-control:  private, max-age=0
content-encoding:  gzip
content-length:  122
content-type:  application/json; charset=UTF-8
date:  Thu, 13 Jun 2013 22:22:09 GMT
expires:  Thu, 13 Jun 2013 22:22:09 GMT
server:  GSE

{
 "error": {
  "errors": [
   {
    "domain": "global",
    "reason": "backendError",
    "message": "Backend Error"
   }
  ],
  "code": 503,
  "message": "Backend Error"
 }
}

which is utterly useless. I've done a web search, and failed to find any examples of it in use.

Can anyone give me an example of using BigQuery patch to alter a table, and a description of what it can actually do?

解决方案

TLDR: You need to supply the complete schema in the body of the patch request, not just the fields you're trying to add. The backend error is likely caused by the empty fields in that array.

---

BigQuery's API allows tables (and other resources) to be updated in two ways: update and patch.

The update method replaces the table resource with the new resource that you supply. This method is useful in cases where you want to take an existing table resource, modify it, and then post that modified table resource back to BigQuery in its entirety. (Note, however, that some fields of the object, such as creationTime, are considered immutable, so the new values supplied for these fields will be ignored.)

The patch method only replaces the fields that you include in the request, and leaves the rest of the resource unchanged. This method is useful if you want to make an isolated change to one field without worrying about the rest. This algorithm is applied recursively to any nested objects, but not to nested arrays. In other words, the patch resource that you send with the request is merged recursively with the existing resource until an array or scalar value is encountered, at which point the array or value in the patch object replaces the one in the existing resource.

Since the schema you're trying to update contains an array of fields, and since the patch method updates arrays wholesale, your patch object needs to contain the complete array of fields that you want in the resulting schema. (You can't add a field by specifying a one-field array in the patch object.)

Note that the table ID is given in the request URL, so it doesn't need to be included in the object itself.

Finally, the backend error is a problem on our end, likely a result of the two empty schema fields in your request. We'll dig further and hopefully improve the error message for the future.

这篇关于如何使用 BigQuery 补丁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！