在Google BigQuery中使用GOLANG V2 API进行参数化查询

huangapple go评论82阅读模式
英文:

Parameterized Query in Google BigQuery using GOLANG V2 API

问题

我目前无法使用GOLANG中的V2 API对Google BigQuery进行参数化查询。通常情况下,如果没有参数,处理查询的代码将如下所示:

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

假设我们不使用命名参数。让我们模拟两个参数,并将其附加到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

假设你设法将'?'插入查询的适当位置,你要么会得到一个错误提示"missing , after FROM",要么会提示另一个'?'无法识别。对于使用'@'的命名参数也是一样。我是否遗漏了关键的东西,或者GOLANG API不支持参数?

英文:

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?

答案1

得分: 1

你需要在QueryConfig中设置UseStandardSQL,因为查询参数仅支持标准SQL。你还需要确保设置QueryParameterType属性,它应该是"STRING"。虽然支持位置参数,但我仍建议使用命名参数,因为如果你需要在某个时候修改查询字符串,使用命名参数会更容易进行重构。

英文:

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.

huangapple
  • 本文由 发表于 2017年3月9日 23:38:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/42699445.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定