CODEWITHSUNDEEP
google-bigquery
Rahul Nimje
Rahul Nimje

Reputation: 25

How to pass dynamic parameterss in where condition in bq command line

FTIMESTAMP="2018-07-09 00:00:00"
LTIMESTAMP="2018-07-09 08:00:00"
echo $FTIMESTAMP
echo $LTIMESTAMP
bq query --nouse_legacy_sql 'insert `table1`(Time,UserId) 
  select Time,UserId from `table2` 
  WHERE _PARTITIONTIME >= "$FTIMESTAMP" AND _PARTITIONTIME < "$LTIMESTAMP"'

When I ran these commands in .sh script, it gave the following error: *Error in query string: Error processing job '************': Could not cast literal "$FTIMESTAMP" to type TIMESTAMP at [3:25].*

I want to pass those parameters dynamically once this query is successful.

Or is there any other way to extract the data for last 8 hours on the basis of partition time.

Upvotes: 1

Views: 1882

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

It's really a better idea to use query parameters instead of modifying your query text directly; you won't have issues where the query text ends up with syntax errors or other problems. Here is an example using parameters with the names from your question:

$ bq query --use_legacy_sql=false \
    --parameter=FTIMESTAMP:TIMESTAMP:"2018-07-09 00:00:00" \
    --parameter=LTIMESTAMP:TIMESTAMP:"2018-07-09 00:00:00" \
  "SELECT @FTIMESTAMP, @LTIMESTAMP;"  
+---------------------+---------------------+
|         f0_         |         f1_         |
+---------------------+---------------------+
| 2018-07-09 00:00:00 | 2018-07-09 00:00:00 |
+---------------------+---------------------+

In your case, you would want something like this:

$ bq query --nouse_legacy_sql \
    --parameter=FTIMESTAMP:TIMESTAMP:"2018-07-09 00:00:00" \
    --parameter=LTIMESTAMP:TIMESTAMP:"2018-07-09 00:00:00" \
  'insert `table1`(Time,UserId) 
  select Time,UserId from `table2` 
  WHERE _PARTITIONTIME >= @FTIMESTAMP AND _PARTITIONTIME < @LTIMESTAMP'

If you still want to set the parameter values from shell variables, you can do so like this:

$ FTIMESTAMP="2018-07-09 00:00:00"
$ LTIMESTAMP="2018-07-09 00:00:00"
$ bq query --nouse_legacy_sql \
    --parameter=FTIMESTAMP:TIMESTAMP:"$FTIMESTAMP" \
    --parameter=LTIMESTAMP:TIMESTAMP:"$LTIMESTAMP" \
  'insert `table1`(Time,UserId) 
  select Time,UserId from `table2` 
  WHERE _PARTITIONTIME >= @FTIMESTAMP AND _PARTITIONTIME < @LTIMESTAMP'

This sets the values of the query parameters from the shell variables, which are then passed to BigQuery.

Upvotes: 1

Related Questions