BigQuery: Add support for query parameters

[jlowin]

The CLI command bq query now supports query parameters for Standard SQL queries and the API does as well (https://cloud.google.com/bigquery/docs/reference/v2/jobs/query#request-body). It would be great to be able to provide these via google.cloud.

[tseaver]

@jlowin Thanks for the report! Except for the API reference page you linked, I can't find any other docs (especially narrative ones with samples) which explain how query parameters should be used. Do you know of any?

[jlowin]

I wish there were but so far I've just been figuring it out via trial and error. For example:

bq query --use_legacy_sql=False --parameter param1:INTEGER:5 --parameter param2:INTEGER:100 "select @param1 + @param2 as result"

Waiting on bqjob_r665b4bfadeedbee9_00000157ce6674a0_1 ... (0s) Current status: DONE
+--------+
| result |
+--------+
|    105 |
+--------+

Putting @param in the Web UI raises errors about undeclared variables, but I haven't figured out how to declare them...

[tswast]

@tseaver It's on my TODO list to document this feature better.

[tswast]

@tseaver I found the docs on this:

https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query

Search for queryParameters.

Depending on what parameterMode is set to, the parameter can be positional ? or names @somename

[c0b]

+1 on request of this, even reading that reference didn't help a lot, because it didn't give a working example I was researching on how to pass an array and struct value, but see googleapis/google-cloud-go#390 (comment) what I found from reading /opt/google-cloud-sdk/platform/bq/bq.py source code. @jlowin

what I found parameterMode is optional because bq tool doesn't send that at all, it still works.

if you give the parameter a name it is named and you may refer it in the SQL by @thename; or if omit the name, it is positional, refer it by ?.

you may continue research by turning on --apilog - to see all the REST API interaction to bigquery server, that way to know how queryParameters are constructed, have fun 😄

$ ./opt/google-cloud-sdk/platform/bq/bq.py --apilog - query --use_legacy_sql=false \
  --parameter arr:'ARRAY<INTEGER>':'[2,3,4]' 'SELECT 3 IN UNNEST(@arr) AS res'
[...]
+------+
| res  |
+------+
| true |
+------+

$ ./opt/google-cloud-sdk/platform/bq/bq.py --apilog - query --use_legacy_sql=false \
  --parameter :INTEGER:3 'SELECT ? + 2 AS res'
+-----+
| res |
+-----+
|   5 |
+-----+

############ the bq's default parameter type is STRING
$ ./opt/google-cloud-sdk/platform/bq/bq.py --apilog - query --use_legacy_sql=false \
  --parameter name::'Mr Smith' 'SELECT CONCAT("Hello, ", @name) AS msg'
+-----------------+
|       msg       |
+-----------------+
| Hello, Mr Smith |
+-----------------+

################# other BigQuery supported data types also work
$ ./opt/google-cloud-sdk/platform/bq/bq.py --apilog - query --use_legacy_sql=false \
  --parameter pt:TIMESTAMP:'2016-11-13 23:23:59' 'SELECT TIMESTAMP_TRUNC(@pt, DAY) AS dt'
+---------------------+
|         dt          |
+---------------------+
| 2016-11-13 00:00:00 |
+---------------------+

but the WebUI at https://bigquery.cloud.google.com/ doesn't understand these parameters at all. You'll have to write a program to call the REST APIs

[tseaver]

@tswast Working on this now. I note that the feature is still documented as "experimental," which makes it a bit odd to have as "blocking beta" for us.

[danoscarmike]

Hi @tseaver, are you actively working this one? If you have any blockers or need input from BQ team please let me know ASAP. Otherwise, do you have a sense for remaining effort, ETC? Thanks!

[tseaver]

@danoscarmike PR #2776 implements the feature.

[danoscarmike]

Great stuff. Thanks Tres!