bigquery: support SQL parameters

[c0b]

Go language binding of BigQuery has this feature in 2016 Q4 milestone in googleapis/google-cloud-go#390
I hope this feature can be in node as well

See parameterMode and queryParameters in the query job reference.

[stephenplusplus]

I believe this is supported currently, but the user needs to construct their own parameterMode and queryParameters configuration. So what we really want is to make that configuration unnecessary, and convert a user's SQL string input into the verbose parameterMode and queryParameters configuration, right? Or did you see us supporting this another way?

[c0b]

right; I have spent a lot of time from the bq tool's apilog to see how queryParameters is constructed,
in googleapis/google-cloud-go#390 (comment); because even the bigquery rest api reference isn't very detailed, not having a working example how should queryParameters be constructed: same kind of struggling in the python api googleapis/google-cloud-python#2551

so far I have figured out in this way of passing parameter it indeed works

bigquery.query({
    query: 'SELECT 3 IN UNNEST(@arr)',
   "useLegacySql": false,
   "queryParameters": [
    {
     "name": "arr",
     "parameterType": {
      "type": "ARRAY",
      "arrayType": {
       "type": "INT64"
      }
     },
     "parameterValue": {
      "arrayValues": [
       {
        "value": "2"
       },
       {
        "value": "3"
       },
       {
        "value": "4"
       }
      ]
    }
})
.then( ... )        // promise

but why not give another option in the query for users' life be easier?

https://googlecloudplatform.github.io/google-cloud-node/#/docs/bigquery/0.4.0/bigquery?method=query

like:

bigquery.query({
    query: 'SELECT 3 IN UNNEST(@arr)',
    useLegacySql: false,
    queryParameters: {
      "arr": {
        type: "ARRAY<INTEGER>",
        value: [ 2, 3, 4 ]
      },
    }
})

or further:

bigquery.query({
    query: 'SELECT @name, 3 IN UNNEST(@arr) AS b, @obj.a AS obj_a',
    useLegacySql: false,
    queryParameters: {
      "name": "Mr Smith"
      "arr": [ 2, 3, 4 ],
      "obj": { a: 4 },
    }
})

then this library can infer the parameter types and do the necessary construction, provide the most flexibility to users of this library

   "queryParameters": [
    {
     "name": "name",
     "parameterType": {
      "type": "STRING"
     },
     "parameterValue": {
      "value": "Mr Smith"
     }
    },
    {
     "name": "arr",
     "parameterType": {
      "type": "ARRAY",
      "arrayType": {
       "type": "INT64"
      }
     },
     "parameterValue": {
      "arrayValues": [
       {
        "value": "2"
       },
       {
        "value": "3"
       },
       {
        "value": "4"
       }
      ]
     }
    },
    {
     "name": "obj",
     "parameterType": {
      "type": "STRUCT",
      "structTypes": [
       {
        "name": "a",
        "type": {
         "type": "INT64"
        }
       }
      ]
     },
     "parameterValue": {
      "structValues": {
       "a": {
        "value": "3"
       }
      }
     }
    }
   ]

[c0b]

the bq tool's parameter construction ability should be in this library

$ ./opt/google-cloud-sdk/platform/bq/bq.py --apilog - query --use_legacy_sql=false \
  --parameter name::'Mr Smith' \
  --parameter arr:'ARRAY<INTEGER>':'[2,3,4]' \
  --parameter obj:'STRUCT<a INTEGER>':'{"a": 3}' \
  'SELECT @name, 3 IN UNNEST(@arr) AS b, @obj.a AS obj_a'
+----------+------+-------+
|   f0_    |  b   | obj_a |
+----------+------+-------+
| Mr Smith | true |     3 |
+----------+------+-------+

[stephenplusplus]

I think that sounds great, we should definitely make this as easy as possible.

@bjwatson do you know anyone from the BQ API team that can share some code, insight, or "gotchas" to be aware of if we were to try to implement this ourselves?

[bjwatson]

@stephenplusplus I think that @tswast, @elibixby, or @chadjennings could point you in the right direction.

[danoscarmike]

Hi @stephenplusplus, are you actively working this? Or do you still need input from BQ team? If the latter please let me know ASAP. If not, do you have an estimate to completion? Thanks!

[stephenplusplus]

No work is ongoing at the moment. Is my understanding correct that we want to design an API around SQL parameters, and that is a feature that is blocking beta? If that's the case, input and prior art (have other languages implemented this yet?) would be very helpful. Is there a spec or any documentation on the full syntax of a parameterized query?

[danoscarmike]

@stephenplusplus

Yes. This is blocking beta. We need to support SQL parameters.

BQ reference docs here: https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
Refer python implementation here: googleapis/google-cloud-python#2776
Ruby here: googleapis/google-cloud-ruby#1094

Do you have bandwidth today to review and estimate effort?

[stephenplusplus]

Thanks for the links. I'll take a dive into those implementations and begin a translation into our repo.

[stephenplusplus]

@danoscarmike digging into it now and putting some code together. I'll let you know an estimate before the end of the day, once I'm a little more familiar.

[stephenplusplus]

@danoscarmike PR sent: #1854. Functionality is complete and the tests will be done today. Can someone from the BigQuery team take a look? We also need help coming up with a query that uses structs with SQL parameters.

[tswast]

@stephenplusplus I couldn't think of an example query for structs either. This is what we ended up using for the docs:

bq query --use_legacy_sql=False \
    --parameter='struct_value:STRUCT<x INT64, y STRING>:{"x": 1, "y": "foo"}' \
    'SELECT @struct_value AS s;'

For integration tests, it would be good to have one that has a struct-valued field and an array-valued field in addition to some scalar values.

Sounds like you got one for arrays, but in case you are still looking for an example, this is the query I used for the docs:

bq query --use_legacy_sql=False \
    --parameter='gender::M' \
    --parameter='states:ARRAY<STRING>:["WA", "WI", "WV", "WY"]' \
    'SELECT name, sum(number) as count
    FROM `bigquery-public-data.usa_names.usa_1910_2013`
    WHERE gender = @gender
    AND state IN UNNEST(@states)
    GROUP BY name
    ORDER BY count DESC
    LIMIT 10;'