Google Bigquery Command Line Query - Unexpected Error

Question

I am having trouble running a fairly complex query through the command line, though I have no problems running it through the web interface. The command line query is run through a system call in a Ruby script. The error output is as follows:

bigquery error in query operation: error processing job '123856490061:job_3794ad86856342dd9669aeb09a3da86c': unexpected. please try again.

Each day's data has its own table. We recently changed the schema by adding 5 new attributes (removing none), and this query does run across several tables with the old and new schema.

We have been using queries of the exact same format up until this time without problems, however the schemas should be compatible so this shouldn't be the problem. Moreover, the query does work through the web interface, so we're at a loss as to why it does not work through the command line.

Thanks for any suggestions.

Answer 1

I had the same issue, and realized I was using double quotes (") which works in browser but not CLI. Use single (') instead.

Answer 2

Unfortunately, you've hit an internal error within BigQuery--we'll try to get this fixed soon!

The workaround for your problem is to set the preserveNulls flag on your query job. More details about this flag are available here:

https://groups.google.com/forum/#!topic/bigquery-announce/yceNSKytd9o/discussion

The web interface automatically sets this flag, and so does the new version of bq, which is why upgrading to the latest CLI version fixed your problem.