Escaping quote characters for query in bigquery's bq command line tool

Question

I'm trying to automatically generate a new daily table using the bq command line too

bq query \
  --destination_table=<destination> \
  --allow_large_results \
  --noflatten_results \
  '<query>'

where is given below. The problem is that there are a bunch of single and double quotes in the sql query, and the bq command line tool is also using single quotes to demarcate the query to be executed.

So I know the issue is that I have to escape the quotes, but I can't figure out how to do it. I've tried replacing the single quotes in the query with "\'" (without the double quotes), but that didn't help and it seems like the logical thing to do. I assume there is something obvious that I'm not doing, so hopefully someone can help me out without a lot of work.

SELECT
  time,
  userId,
  deviceId,
  systemName,
  LEFT(systemVersion,3) AS systemVersionMajor,
  systemVersion AS systemVersionMinor,
  deviceModel,
  appVersion,
  country,
  city,
  firstRun,
  priorTimeSpentInApp,
  priorSessions,
  priorEvents,
  event_name,
  event_info,
  event_time,
  event_value,
  MAX(CASE WHEN user_attribute.Name = 'Gen_Total_Sessions' THEN FLOAT(user_attribute.Value) END) AS Gen_Total_Sessions.attr,
  MAX(CASE WHEN user_attribute.Name = 'Gen_Last_Achievement' THEN STRING(user_attribute.Value) END) AS Gen_Last_Achievement.attr,
  MAX(CASE WHEN event_parameters.Name = 'Gamemode' THEN STRING(event_parameters.Value) END) AS Gamemode.param,
  MAX(CASE WHEN event_parameters.Name = 'Player_Level' THEN FLOAT(event_parameters.Value) END) AS Player_Level.param,

FROM
  TABLE_QUERY([projectId:dataSet], "table_id CONTAINS 'table1' OR table_id CONTAINS 'table2' OR table_id CONTAINS 'table3'"),
WHERE
  event_name = 'Started'
GROUP BY
  time,
  userId,
  deviceId,
  systemName,
  systemVersionMajor,
  systemVersionMinor,
  deviceModel,
  appVersion,
  country,
  city,
  firstRun,
  priorTimeSpentInApp,
  priorSessions,
  priorEvents,
  event_name,
  event_info,
  event_time,
  event_value 

*** Added more info I've read about using escape codes and I have some understanding of it, but I can't get it to work. Here is a very simple example of what I'm trying to run on the command line:

bq query 'SELECT time, userId, event_name, event_time, FROM 
TABLE_QUERY([rhi-localytics-db:LEANPLUM_DATA], \"table_id CONTAINS \'table1\'
OR table_id CONTAINS \'table2\' OR table_id CONTAINS \'table3\' \" ), WHERE 
event_name = \'Error\' GROUP BY time, userId, event_time, event_value' 

Which looks good to me, but when I run it bash says "-bash: syntax error near unexpected token `)'"

But I don't understand why. I've left the outer level single quotes being passed to bq unescaped, and I've escaped every single quote inside. It seems that maybe bash isn't transmitting the quotes without processing the escape codes itself..?

Billiebilling

Possible duplicate of Replacing apostrophe or quotes in bq SQL – Casemate

Answer 1

I think your problem is that bash does not allow you to escape quotes inside single quotes. Double quotes allow you to do fancy escaping and substitutions, but single quotes give you exactly what you type, up to the very next single quote character.

See here for more: How to escape single-quotes within single-quoted strings?

You've got a couple of options for dealing with this issue:

• Use one of the quoting strategies in the link above
• Switch to double quotes and escape as needed inside the query string
• Put your raw query text in a file, and slurp in the file with bq query "$(< myquery.txt)"
• Use the web UI at https://bigquery.cloud.google.com/, which supports all of the options you need in the options panel

Interface

Thanks Jeremy! That was exactly the problem. – Billiebilling

Answer 2

you should use escape char \ as in below

select 'ab\"c\'123'

read "Escaping special characters in strings" section under https://cloud.google.com/bigquery/query-reference#stringfunctions

Letdown

I added some more detail to the question. I read the escaping special characters in strings section and I did my best to follow the instructions given there, but it still doesn't work. I'm getting a bash error now, shown above in the question. – Billiebilling

Answer 3

Or use a HERE doc:

bq query <<HERE
... your query
HERE

There is one gotcha: backticks in the query must be escaped, and are often necessary if you explicitly name the GCP project. (Project names can't contain _ so people often use - which is a SQL metacharacter.)

Wedded