CODEWITHSUNDEEP
google-bigquery
kg.
kg.

Reputation: 561

How do I specify query parameters for a scheduled query in BigQuery?

I am trying to set up some Scheduled Queries against my tables in BigQuery.

https://cloud.google.com/bigquery/docs/scheduling-queries

I would like these queries to use parameters, e.g.

bq query --use_legacy_sql=false \
    --parameter=label::TEST_LABEL \
    "SELECT @label AS label"

I cannot find any way to do this via the bq commandline tool or the API. Passing it in as a --parameter flag, or a field to the params JSON does not work.

bq mk \
    --transfer_config \
    --project_id=my_project \
    --location=US \
    --display_name='Test Scheduled Query' \
    --params='{"query":"INSERT my_project.my_data.test SELECT @label AS label;"}' \
    --data_source=scheduled_query \
    --service_account_name=my-svc-act@my_project.iam.gserviceaccount.com

Upvotes: 1

Views: 2441

Answers (1)

Pentium10
Pentium10

Reputation: 207838

You got some concept wrong. Not sure what is your use case.

Scheduled Queries

A scheduled query is setup to run periodically in the background, and it's triggers automatically. Hence as it's automatically scheduled, there is no way to provide custom parameters in the way you have described. This is due to scheduled queries using features of BigQuery Data Transfer Service.

A scheduled query has only a few inbuilt runtime parameters such as @run_time, more on this here. You will find further information in this other link.

You probably want to execute a query on-demand, and not a scheduled query.

On-demand queries

Example:

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'

More on running parameterized queries here.

Upvotes: 1

Related Questions