Better support for parameterized queries

[brunokim]

Thanks for stopping by to let us know something could be better!

PLEASE READ: If you have a support contract with Google, please create an issue in the support console instead of filing on GitHub. This will ensure a timely response.

Is your feature request related to a problem? Please describe.

The top-voted issue in the Bigquery SQL Issue Tracker is the lack of support to parameters in the UI, and I was pleasantly surprised to discover that I could use them via bq and also pandas-gbq. However, neither is particularly ergonomic:

• bq requires you to pass a flag multiple times, like bq query --parameter 'a:INTEGER:3' --parameter 'b:INTEGER:2' --parameter 'c:INTEGER:7' 'SELECT @a * @b * @c'
• %%bigquery magic is a bit better, though (IMO) the inline flag parser[1] and JSON format was unexpected, like --params {"a": 3, "b": 2, "c": 7}
• read_gbq provides this feature only via the configuration option in a verbose format, that is hidden three levels deep in the documentation (Jobs.Query > QueryConfiguration > QueryParameters)

a_param = {
    'name': 'a',
    'parameterType': {'type': "INTEGER"},
    'parameterValue': {'value': 3}
}
b_param = {
    'name': 'b',
    'parameterType': {'type': "INTEGER"},
    'parameterValue': {'value': 2}
}
c_param = {
    'name': 'c',
    'parameterType': {'type': "INTEGER"},
    'parameterValue': {'value': 7}
}
config = {
    'query': {'queryParameters': [a_param, b_param, c_param]},
}
read_gbq(project_id=project_id, configuration = config, query="SELECT @a * @b * @c")

Describe the solution you'd like

I believe read_gbq should have a readable, intuitive interface for params, mimicking other DB wrappers like Psycopg2 where params are passed as a tuple or map to the execute method. Types should be inferred from the values themselves, and when not possible, allow the QueryParameter to be passed in the mapping.

params = {"a": 3, "b": 2, "c": 7}
params = dict(a=3, b=2, c=7)  # To save some precious quotes
read_gbq(project_id=project_id, params=params, query="SELECT @a * @b * @c")

Describe alternatives you've considered

I can probably write my own library to make the conversion between a dict and a config object, but then I'd also need to handle merging other configurations other than parameters onto it, which looks like a job for read_gbq itself.

Additional context

[1]: I'd expect the params arg to be wrapped in quotes like --params '{...}', because that's how command-line flags are supposed to work. I was surprised reading the code that we're indeed able to pass another flag after the closing brace. Just a comment, I don't think it's possible to change this interface now.

[tswast]

Thanks for the feature request. I like the idea to mimic the DB-API, though the DB-API diverges from BigQuery syntax since BigQuery's @ params weren't one of the standard DB-API parameter styles. https://peps.python.org/pep-0249/#paramstyle

Regarding implementation, I wonder if it would make sense to use the helper we already have as part of the conversion. https://github.com/googleapis/python-bigquery/blob/2c57533a1e50a64512c9211ccc94289d783ccef6/google/cloud/bigquery/dbapi/_helpers.py#L367

[brunokim]

For sure, this function even accepts an empty parameter_types dict, in which case it discovers the type from the value itself.