Google BigQuery table Patch/Update doesn't work

Question

Google_Http_Request object {
  batchHeaders => array(3) (
    [Content-Type] => (string) application/http
    [Content-Transfer-Encoding] => (string) binary
    [MIME-Version] => (string) 1.0
  )
  queryParams => array(0)
  requestMethod => (string) PATCH
  requestHeaders => array(3) (
    [content-type] => (string) application/json; charset=UTF-8
    [authorization] => (string) *edited*
    [accept-encoding] => (string) gzip
  )
  baseComponent => (string) https://www.googleapis.com
  path => (string) /bigquery/v2/projects/aerobic-forge-504/datasets/wr_dev/tables/user_profile
  postBody => (string) [{"name":"user_id","type":"integer","mode":"nullable"},{"name":"is_complete","type":"integer","mode":"nullable"},{"name":"country_id","type":"integer","mode":"nullable"},{"name":"state","type":"string","mode":"nullable"},{"name":"city","type":"string","mode":"nullable"},{"name":"zip","type":"string","mode":"nullable"},{"name":"language","type":"string","mode":"nullable"},{"name":"industry_id","type":"integer","mode":"nullable"},{"name":"subscribed_status","type":"integer","mode":"nullable"},{"name":"tracking_prod_type","type":"string","mode":"nullable"},{"name":"tracking_prod_id","type":"integer","mode":"nullable"},{"name":"timestamp","type":"timestamp","mode":"required"},{"name":"tags","type":"string","mode":"repeated"},{"name":"utm","type":"record","mode":"nullable","fields":[{"name":"source","type":"string","mode":"nullable"},{"name":"medium","type":"string","mode":"nullable"},{"name":"campaign","type":"string","mode":"nullable"},{"name":"content","type":"string","mode":"nullable"},{"name":"term","type":"string","mode":"nullable"}]},{"name":"meta","type":"string","mode":"nullable"}]
  userAgent => (string) Client_Library_Examples google-api-php-client/1.0.3-beta (gzip)
  canGzip => (bool) true
  responseHttpCode => null
  responseHeaders => null
  responseBody => null
  expectedClass => (string) Google_Service_Bigquery_Table
  accessKey => null
}

I had this call, it returns successful but the required changes are not reflected on the schema. tracking_prod_type is a new field, and it's not added to the schema. We tried Patch and the Update way, and we can't add the additional columns. The update returns: Provided Schema does not match. Patch returns valid.

On the postBody, we tried different syntax's and none of them works. [etag] => (string) "wRHWmN_1J7FEq2j8vIkltiyoyRw/GVGxfUY15UK1iZLhzZzfWOf-1Ow"

We tried following this Q&A Bigquery add columns to table schema

We see the Last Modified field get modified on the table schema, when we issue the call, but the structure doesn't have the new columns.

updated

Google_Http_Request object {
  batchHeaders => array(3) (
    [Content-Type] => (string) application/http
    [Content-Transfer-Encoding] => (string) binary
    [MIME-Version] => (string) 1.0
  )
  queryParams => array(0)
  requestMethod => (string) PATCH
  requestHeaders => array(2) (
    [content-type] => (string) application/json; charset=UTF-8
    [authorization] => (string) edited
  )
  baseComponent => (string) https://www.googleapis.com
  path => (string) /bigquery/v2/projects/aerobic-forge-504/datasets/wr_dev/tables/user_profile
  postBody => (string) {"fields":[{"name":"user_id","type":"integer","mode":"nullable"},{"name":"is_complete","type":"integer","mode":"nullable"},{"name":"country_id","type":"integer","mode":"nullable"},{"name":"state","type":"string","mode":"nullable"},{"name":"city","type":"string","mode":"nullable"},{"name":"zip","type":"string","mode":"nullable"},{"name":"language","type":"string","mode":"nullable"},{"name":"industry_id","type":"integer","mode":"nullable"},{"name":"subscribed_status","type":"integer","mode":"nullable"},{"name":"timestamp","type":"timestamp","mode":"required"},{"name":"tags","type":"string","mode":"repeated"},{"name":"utm","type":"record","mode":"nullable","fields":[{"name":"source","type":"string","mode":"nullable"},{"name":"medium","type":"string","mode":"nullable"},{"name":"campaign","type":"string","mode":"nullable"},{"name":"content","type":"string","mode":"nullable"},{"name":"term","type":"string","mode":"nullable"}]},{"name":"meta","type":"string","mode":"nullable"},{"name":"tracking_prod_type","type":"string","mode":"nullable"},{"name":"tracking_prod_id","type":"integer","mode":"nullable"}]}
  userAgent => null
  canGzip => null
  responseHttpCode => nu...

update2 using PATCH

This is when I have tableResource and schema, and using PATCH. Two additional columns listed in the schema. And I've got: Provided Schema does not match Table aerobic-forge-504:wr_dev.user_profile

Google_Http_Request object {
  batchHeaders => array(3) (
    [Content-Type] => (string) application/http
    [Content-Transfer-Encoding] => (string) binary
    [MIME-Version] => (string) 1.0
  )
  queryParams => array(0)
  requestMethod => (string) PATCH
  requestHeaders => array(3) (
    [content-type] => (string) application/json; charset=UTF-8
    [authorization] => (string) edited
    [accept-encoding] => (string) gzip
  )
  baseComponent => (string) https://www.googleapis.com
  path => (string) /bigquery/v2/projects/aerobic-forge-504/datasets/wr_dev/tables/user_profile
  postBody => (string) {"tableReference":{"datasetId":"wr_dev","projectId":"aerobic-forge-504","tableId":"user_profile"},"schema":{"fields":[{"name":"user_id","type":"integer","mode":"nullable"},{"name":"is_complete","type":"integer","mode":"nullable"},{"name":"country_id","type":"integer","mode":"nullable"},{"...

update3 using PUT

When using the update node I still get Provided Schema does not match Table aerobic-forge-504:wr_dev.user_profile

Google_Http_Request object {
  batchHeaders => array(3) (
    [Content-Type] => (string) application/http
    [Content-Transfer-Encoding] => (string) binary
    [MIME-Version] => (string) 1.0
  )
  queryParams => array(0)
  requestMethod => (string) PUT
  requestHeaders => array(4) (
    [content-type] => (string) application/json; charset=UTF-8
    [authorization] => (string) edited
    [accept-encoding] => (string) gzip
    [content-length] => (int) 1221
  )
  baseComponent => (string) https://www.googleapis.com
  path => (string) /bigquery/v2/projects/aerobic-forge-504/datasets/wr_dev/tables/user_profile
  postBody => (string) {"tableReference":{"datasetId":"wr_dev","projectId":"aerobic-forge-504","tableId":"user_profile"},"schema":{"fields":[{"name":"user_id","type":"integer","mode":"nullable"},{"name":"is_complete","type":"integer","mode":"nullable"},{"name":"country_id","type":"integer","mode":"nullable"},{"...

Answer 1

When calling patch or update, you need to provide something that looks like a table. In your example above, it looks like you're not passing a table resource, just the fields inside the table resource. You may need to wrap your postBody in something like {"schema": {"fields": postBody } }

This is a little bit confusing from the other question, because bq update does this wrapping for you.

The reason you're seeing update fail is because the bigquery server doesn't see any schema at all, so it thinks you're trying to delete the schema. And patch succeeds but does nothing, because it doesn't see any valid fields at all, so it just does a null patch operation.

Here is an example of a curl session that adds fields to a schema. It should be relatively easy to adapt to php. It uses auth.py from here

PROJECT_ID=<your_project_here>
DATASET_ID=scratch

BASE_URL=https://www.googleapis.com/bigquery/v2/projects
TABLES_URL=${BASE_URL}/${PROJECT_ID}/datasets/${DATASET_ID}/tables

### Make a scratch dataset.
bq --project_id=${PROJECT_ID} mk –d ${DATASET_ID}

SCHEMA="{'fields': [{'name':'foo', 'type': 'STRING'}]}"
TABLE_REF="{'tableId': 'table1', \
    'datasetId': 'scratch', \
    'projectId': '${PROJECT_ID}'}"

### Make a temporary table with a schema.
curl -H "$(python auth.py)" \
    -H "Content-Type: application/json" \
    -X POST \
    -d "{'tableReference': ${TABLE_REF}, \
         'schema': ${SCHEMA}}" \
    "${TABLES_URL}"

### Tables.update()
SCHEMA2="{'fields': [ \
    {'name':'foo', 'type': 'STRING'}, \
    {'name': 'bar', 'type': 'FLOAT'}]}"
TABLE_JSON="{'tableReference': ${TABLE_REF}, 'schema': ${SCHEMA2}}"
curl -H "$(python auth.py)" \
    -H "Content-Type: application/json" \
    -X PUT \
    -d "${TABLE_JSON}" \
    "${TABLES_URL}/table1"

### Tables.patch()
EXPIRATION_TIME=$(($(date +"%s")+24*60*60))000
curl -H "$(python auth.py)" \
    -H "Content-Type: application/json" \
    -X PATCH \
    --data-binary "{'expirationTime': '${EXPIRATION_TIME}'}" \
    "${TABLES_URL}/table1"