Parameterized Query in Google BigQuery using GOLANG V2 API

Question

Im currently unable to use parameterized queries to Google BigQuery using the V2 API in GOLANG. Typically without the paramters the code to process the query would be:

database_query := client.Query(report.Query) database_query.QueryConfig.Dst = table_result job, err := database_query.Run(ctx)

assume that we dont use the named paramters. lets mock 2 parameters and append this to the request as expected from the API(https://github.com/GoogleCloudPlatform/google-cloud-go/blob/master/bigquery/query.go) :

var params [2]string
params[0] = "currency"
params[1] = "price"

ParametersL := make([]bigquery.QueryParameter, 0)
for _,element := range params {
    temp := bigquery.QueryParameter{}
    temp.Value=element
    ParametersL = append(ParametersL,temp)
}

database_query := client.Query(report.Query)
database_query.QueryConfig.Dst = table_result
database_query.QueryConfig.Parameters= ParametersL

provided you manage to squeeze the '?' into the appropriate place into the query you either get a error prompting missing , after FROM or the other ? is not recognized. The same goes for named paramters using @. Am I missing something cruzial or is the paramters not supported for GOLANG API?

Is case someone else looks into this the error log is: Job failed with error {Location: "query"; Message: "Syntax error: Trailing comma before the FROM clause is not allowed at [1:680]"; Reason: "invalidQuery"} and im printing out the array of input to make sure its string: the string%!(EXTRA string='autoRecurring') — Gustav Sandström
– Gustav Sandström, Commented Mar 13, 2017 at 16:14

Elliott Brossard · Accepted Answer · 2017-03-09 15:43:02Z

1

You need to set UseStandardSQL as part of QueryConfig, since query parameters are supported only with standard SQL. You will also need to make sure to set the Type attribute of the QueryParameters, which should be "STRING". While positional parameters are supported, of course, I would still suggest using named parameters, since they make refactoring easier if you need to modify the query string at some point.

answered Mar 9, 2017 at 15:43

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gustav Sandström Over a year ago

I tried the standard SQL with no effect. How am I supposed to set a type on QueryParameters when its a slice of QueryParameter (which only has Fields name and value).

Gustav Sandström Over a year ago

Also IF you look into the github.com/GoogleCloudPlatform/google-cloud-go/blob/master/… you will see that in the end of the file LegacySQL is disabled for either IF We set parameterns or send the standard SQL true. So IT should not matter

Gustav Sandström Over a year ago

is maybe needed for feedback

Elliott Brossard Over a year ago

I'm not able to provide any additional help, unfortunately. I have not worked with the Go library myself, but I tried to provide suggestions based on the API. I filed a request for documentation in the public issue tracker.

Collectives™ on Stack Overflow

Parameterized Query in Google BigQuery using GOLANG V2 API

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related