1

I'm new to using bq, Big Query command line utility. I have a more complex SQL clause that unfortunately has both apostrophe and quote characters within the SQL statement. Since both characters are in the SQL statement I'm looking for some replacement for them, otherwise one or the other will be interpretted as an "end of query" delimitter.

Below is the query that I am trying to run, which works great on the Big Query html interface, but not so great using bq command line utility. Suggestions on replacing the apostrophes or quotes to get this thing running? Is there any option to pass file content into the bq query command so the complex query could be stored in a file? (also easier to read in a file vs crammed onto one line).

bq query 'SELECT regexp_extract(meta, r'\"bldid\":\"(.*?)\"') as bldid FROM stuff.201308010000 LIMIT 10'

[EDIT] After playing around with this a bit more, it looks like this was a simple fix. If quotes are used for BEG and END delimiters the query works.

bq query "SELECT regexp_extract(meta, r'\"bldid\":\"(.*?)\"') as bldid FROM stuff.201308010000 LIMIT 10"

Not sure why this didn't work for apostrophes to mark the beginning and end of the query.

Steve Scherer
  • 315
  • 1
  • 5
  • 16

2 Answers2

2

This is a bash problem, more than a BigQuery thing: Single quotes are "unquotable" inside a single quoted string. Bash processes the string before BigQuery even sees it.

Take a look at How to escape single-quotes within single-quoted strings?.

"Enclosing characters in single quotes preserves the literal value of each character within the quotes. A single quote may not occur between single quotes, even when preceded by a backslash." http://www.gnu.org/software/bash/manual/bashref.html#Single-Quotes

Community
  • 1
  • 1
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • A workaround for this may be to create your query as an environment variable. – Jordan Tigani Aug 28 '13 at 15:33
  • Great point about this being more of a BASH issue vs Big Query. A colleague of mine suggested to use 'cat' on a file to redirect the output of cat into the bq command line. This worked perfectly and is much cleaner. Now we can save formatted queries in a file and use the file name on the command line. – Steve Scherer Aug 29 '13 at 03:34
  • 1
    Example - `bq query "\`cat foobar.sql\`"` Where foobar contains the complex SQL string. – Steve Scherer Aug 29 '13 at 03:41
0

Try just using double quotes, but escaping them. You also need to escape the \ one more time:

bq query "SELECT regexp_extract(meta, r\"\\\"bldid\\\":\\\"(.*?)\\\"\") as bldid FROM stuff.201308010000 LIMIT 10;"
Jordan Tigani
  • 26,089
  • 4
  • 60
  • 63