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

1
votes

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]

1
google-bigquery

1 Answers

4
votes

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