Exception when updating bigquery schema

Question

I tried insert to bigquery with schema:

require 'gcloud'

gc = Gcloud.new 'PROJECT_ID'
bq = gc.bigquery
ds = bq.dataset 'MY_DATASET'
t = ds.create_table 'MY_TABLE'
t.schema = { fields: [ { name: 'Name', type: 'STRING' } ] }
t.insert [{'name' => 'test1'}]

As expected, my terminal console showed the output error:

[{"reason"=>"invalid", "location"=>"name", "debugInfo"=>"generic::not_found: no such field.", "message"=>"no such field."}]

When I tried to update schema to insert the key Name:

t.schema = { 
  fields: [ 
    { name: 'Name', type: 'STRING' }, 
    { name: 'name', type: 'STRING' }
  ] 
}

Displayed the exception:

Gcloud::Bigquery::ApiError: Field name already exists in schema

Any suggestion how can I solve this? This is a BigQuery bug?

I've not used bigquery before, but it looks like it lowercases the field names. So you are basically trying to create a field name twice, despite uppercasing the field name the first time. — Justin Wood
No, the key returned is Name and I can't create a lowecase key after a defined schema with a uppercase letter in the key. I can't insert data with lowercase key also. It's pretty confusing. — Victor Lellis

Danny Kitt Danny Kitt · Accepted Answer · 2016-03-04T22:57:41

When inserting data (via streaming, at least, which t.insert appears to use), field names are case-sensitive. So if you updated to 'Name', it should work with your schema.

t.insert [{'Name' => 'test1'}]

However, within queries, field names are case-insensitive, so it's invalid for a table to have field names that differ only by case: they'd be indistinguishable. This leads to your second error with "Field name already exists in schema".

This is admittedly pretty confusing. I'll look into whether we can make case-sensitivity more predictable for users.

Exception when updating bigquery schema

1 Answers