参数类型STRING和{ARRAY<STRING>}的运算符IN没有匹配的签名(谷歌BigQuery)
我使用的是v0.23，并尝试使用参数化查询。我正在关注API docs作为参考。
当我在没有参数的情况下运行查询时，一切正常。然而，为了使它们成为动态的，当我使用数组参数时，我得到了错误。例如，当我运行这个查询时
bigquery.query("SELECT COUNT(*) FROM oven.sensor_counts WHERE _PARTITIONTIME = TIMESTAMP('2016-04-04') AND sensor_id IN (@sensor_ids)", params: { sensor_ids: ['48-6', '48-2'] })我明白了
#<Harley::Response POST https://www.googleapis.com/bigquery/v2/projects/sensors-160421/queries == 400 (413 bytes) 3458ms>
Caught error invalidQuery: No matching signature for operator IN for argument types STRING and {ARRAY<STRING>} at [1:116]
Error - #<Google::Apis::ClientError: invalidQuery: No matching signature for operator IN for argument types STRING and {ARRAY<STRING>} at [1:116]>
Google::Cloud::InvalidArgumentError: invalidQuery: No matching signature for operator IN for argument types STRING and {ARRAY<STRING>} at [1:116]
from /usr/local/var/rbenv/versions/2.3.1/lib/ruby/gems/2.3.0/gems/google-cloud-bigquery-0.23.0/lib/google/cloud/bigquery/service.rb:662:in `rescue in execute'任何真知灼见都将不胜感激。
回答 2
Stack Overflow用户
发布于 2017-01-12 04:58:05
我认为数组只是一个不正确的参数。根据Functions & Operators的说法，语法是：
x IN (y, z, ...)请注意，这里没有数组。
并且用单个字符串替换数组是可行的：
require "google/cloud/bigquery"
bigquery = Google::Cloud::Bigquery.new
sql = "SELECT word, SUM(word_count) AS word_count " \
"FROM `bigquery-public-data.samples.shakespeare`" \
"WHERE word IN (@words) GROUP BY word"
data = bigquery.query sql, params: { words: 'you' }
#=> [{"word"=>"you", "word_count"=>12527}]根据上面链接的语法指南，array参数适用于添加UNNEST：
UNNEST表单处理数组扫描的方式与FROM子句中的UNNEST类似：
x [NOT] IN UNNEST(<array expression>)
这种形式通常与数组参数一起使用。例如：
x IN UNNEST(@array_parameter)
因此，解决方案是：
require "google/cloud/bigquery"
bigquery = Google::Cloud::Bigquery.new
sql = "SELECT word, SUM(word_count) AS word_count " \
"FROM `bigquery-public-data.samples.shakespeare`" \
"WHERE word IN UNNEST(@words) GROUP BY word"
data = bigquery.query sql, params: { words: ['me', 'I', 'you'] }
#=> [{"word"=>"I", "word_count"=>21028}, {"word"=>"me", "word_count"=>8030}, {"word"=>"you", "word_count"=>12527}]Stack Overflow用户
发布于 2019-07-04 20:01:50
有一种更好的方法是使用数组，即using parameterized approach
从google.cloud导入
客户端= bigquery.Client()
sql = "SELECT COUNT(*) FROM oven.sensor_counts WHERE _PARTITIONTIME = TIMESTAMP('2016-04-04') AND UNNEST中的sensor_id (@sensor_ids)“
query_params =[ bigquery.ArrayQueryParameter("sensor_ids"，"STRING"，'48-6'，'48-2')，]
job_config = bigquery.QueryJobConfig() job_config.query_parameters = query_params query_job = client.query( query，job_config=job_config，) client.query(sql，job_config=query_config)
https://stackoverflow.com/questions/41492736
复制相似问题
腾讯云开发者
