Q:

Google BigQuery: bq query from command line fails, although it runs fine from the UI Query Editor

When I run the following query from the BQ UI, it runs fine and gives the desired output.
But when I run the same from the command line, it gives the following error

bq query --destination_table
  Chicago_Traffic_Sensor_Data.Latest_Traffic_Data_with_Geocoding
  --replace --use_legacy_sql=false 'SELECT segmentid, _lif_lat, start_lon, _lit_lat, _lit_lon, _traffic, _last_updt, CASE WHEN
  _traffic < 20 THEN '#FF0000' WHEN _traffic >= 20 and _traffic < 40 THEN '#FFFF00' WHEN _traffic >= 40 THEN '008000' ELSE '#666666' END as
  strokeColor FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY segmentid
  ORDER BY _last_updt DESC) col FROM
  Chicago_Traffic_Sensor_Data.traffic_sensor_data) x WHERE x.col = 1
  ORDER BY segmentid' Error in query string: Error processing job
  'bedrock-gcp-testing:bqjob_r5d944587b08c4e54_000001626fa3f61d_1':
  Syntax error: Unexpected end of statement at [1:412]

A:

You need to escape any characters in your SQL that the command line is trying to interpret. What I find much easier & quicker is to put my SQL in a file, and pipe it instead. For example:
bq query --destination_table grey-sort-challenge:partitioning_magic.foobarred --use_legacy_sql=false "$(cat data.sql)"

