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.
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:
In the opened query window, select your parameters to get the value as follows:
SELECT @corpus, @min_word_count;
It is not a straightforward option, but helps for immediate troubleshooting.
