How do I use BigQuery patch?

Question

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?

Answer 1

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.