在Python中向BigQuery传递数组参数

Question

我有一组ID（约20万个），需要获取BigQuery表中具有这些ID的所有行。我尝试在Python中构建一个列表，并将其作为参数传递给SQL查询，使用@，但是我遇到了TypeError：'ArrayQueryParameter' object is not iterable错误。这是我尝试的代码（非常类似于https://cloud.google.com/bigquery/querying-data#running_parameterized_queries）：

id_list = ['id1', 'id2'] 
query = """
    SELECT id
    FROM `my-db`
    WHERE id in UNNEST(@ids)
"""
query_job = client.run_async_query(
    str(uuid.uuid4()),
    query,
    query_parameters=(
        bigquery.ArrayQueryParameter('ids', 'ARRAY<STRING>', id_list)
    )
)

- thn

Answer 1

可能问题在于您没有向函数传递元组。

尝试在关闭括号之前添加逗号，像这样：

id_list = ['id1', 'id2'] 
query = """
    SELECT id
    FROM `my-db`
    WHERE id in UNNEST(@ids)
"""
query_job = client.run_async_query(
    str(uuid.uuid4()),
    query,
    query_parameters=(
        bigquery.ArrayQueryParameter('ids', 'STRING', id_list),
    )
)

在Python中，如果你这样做：

t = (1)

然后运行：

type(t)

你会发现结果为int。但如果你这样做：

t = (1,)

那么它将会产生一个元组。

- Willian Fuks

---

运行得非常好 :) 我简直不敢相信我花了两天时间解决这个问题！谢谢你，威尔。 - thn

Answer 2

你需要使用'STRING'而不是'ARRAY<STRING>'作为数组元素类型，例如：

query_parameters=(
    bigquery.ArrayQueryParameter('ids', 'STRING', id_list)

“querying data”主题中的示例为：

def query_array_params(gender, states):
    client = bigquery.Client()
    query = """
        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;
        """
    query_job = client.run_async_query(
        str(uuid.uuid4()),
        query,
        query_parameters=(
            bigquery.ScalarQueryParameter('gender', 'STRING', gender),
            bigquery.ArrayQueryParameter('states', 'STRING', states)))
    query_job.use_legacy_sql = False

    # Start the query and wait for the job to complete.
    query_job.begin()
    wait_for_job(query_job)
    print_results(query_job.results())

- Elliott Brossard

---

您也设置了 query_job.use_legacy_sql = False，对吧？ - Elliott Brossard

是的，我将它设置为False。 - thn

啊，我认为可能是因为 query_parameters 预期是一个元组。如果你在 bigquery.ArrayQueryParameter(...) 周围多加一组括号会怎么样？ - Elliott Brossard

我也考虑过这个问题。在 bigquery.ArrayQueryParameter(...) 周围加上单括号会使它变成一个元组，但似乎元组的元素需要是可迭代的，而在这种情况下它们不是，它们是 ArrayQueryParameter 的实例。我也尝试了双括号，但没有成功。 - thn

Answer 3

如果您想使用简单的查询，例如client.query，而不是像上面的答案中所示的client.run_async_query。您可以通过传递一个额外的参数QueryJobConfig来实现。只需使用bigquery.ArrayQueryParameter将您的数组添加到query_parameters中即可。

以下代码对我有效：

    query = f"""
                SELECT distinct pipeline_commit_id, pipeline_id, name
                FROM `{self.project_id}.{self.dataset_id}.pipelines_{self.table_suffix}`,
                UNNEST(labels) AS label
                where label.value IN UNNEST(@labels)
           """
    job_config = bigquery.QueryJobConfig(
        query_parameters=[
            bigquery.ArrayQueryParameter('labels', 'STRING', labels)
        ]
    )
    query_job = self.client.query(query, job_config=job_config)

基于这些示例： https://cloud.google.com/bigquery/docs/parameterized-queries

- Andrey E

Answer 4

以上的回答是更好的解决方案，但是在草稿本上快速起草时，您可能会发现以下内容也很有用：

将列表转换为逗号分隔并带引号的日期值字符串。然后像这样将字符串传递到查询中：

id_list = ['id1', 'id2'] 
# format into a query valid string
id_string = '"'+'","'.join(id_list)+'"'

client = bigquery.Client()
query = f"""
    SELECT id
    FROM `my-db`
    WHERE id in {id_string}
"""
query_job=client.query(query) 
results = query_job.result()

- Krystian