Setting variable parameters in Big query CLI

Question

I'm aware of the fact that we can run parameterized queries in BQ CLI. Below is the working example taken from Big query documentation:

bq query --use_legacy_sql=False \
--parameter=corpus::romeoandjuliet \
--parameter=min_word_count:INT64:250 \
'SELECT word, word_count
FROM `bigquery-public-data.samples.shakespeare`
WHERE corpus = @corpus
AND word_count >= @min_word_count
ORDER BY word_count DESC;'

This way for every parameterized query we need to pass the parameters within the query execution statement itself. Suppose, if I have 'n' number of queries to be executed one after another. In that case is there any way to set the parameter values once. In all the following queries the same parameter values should be reflected. Any leads will be appreciated.

Elliott Brossard · Accepted Answer · 2018-03-14 13:50:23Z

3

You can use an alias that incorporates the parameters, e.g.:

alias bqparams="bq query --use_legacy_sql=False --parameter=corpus::romeoandjuliet --parameter=min_word_count:INT64:250"

Now you can use this alias to run each query:

bqparams 'SELECT word, word_count
  FROM `bigquery-public-data.samples.shakespeare`
  WHERE corpus = @corpus
  AND word_count >= @min_word_count
  ORDER BY word_count DESC;'

It's okay not to reference all parameters in each query that you run.

answered Mar 14, 2018 at 13:50

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting variable parameters in Big query CLI

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related