CODEWITHSUNDEEP
google-bigquery
Steve Scherer
Steve Scherer

Reputation: 345

Replacing apostrophe or quotes in bq SQL

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.

Upvotes: 1

Views: 6652

Answers (2)

Jordan Tigani
Jordan Tigani

Reputation: 26617

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;"

Upvotes: 0

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

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

Upvotes: 2

Related Questions