CODEWITHSUNDEEP
google-bigquery
Kineolyan
Kineolyan

Reputation: 743

Accessing query parameters for BigQuery SQL queries

We can query BigQuery with queries containing parameters, such as the one:

SELECT
  T1.product AS product_0,
  T1.date AS date_1,
  SUM(COALESCE(T1.quantity, @default_value_0)) AS Quantity_QE_SUM_2
FROM `project.dataset.table` AS T1
GROUP BY T1.product, T1.date

Looking at the query history, I can find this SQL. But I have not found a way to find the provided values for default_value_0 - or any other parameters for more complex queries? This is blocking me for investigating wrong results.

Upvotes: 0

Views: 3344

Answers (1)

Rathish Kumar B
Rathish Kumar B

Reputation: 1412

Here is my 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;'

Running parameterised queries in bq command-line tool. Here, 2 parameters used:

--parameter=corpus::romeoandjuliet 
--parameter=min_word_count:INT64:250

If you look at the Personal History page, you will be able to see the query as follows:

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

At the bottom of Query Job Details window, click on the open as new query window:

BigQuery parametrised query

In the opened query window, select your parameters to get the value as follows:

SELECT @corpus, @min_word_count;

BigQuery Parameterised queries results

It is not a straightforward option, but helps for immediate troubleshooting.

Upvotes: 2

Related Questions