escapinggoogle-bigquery

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

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..?

Solution

  • 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: