如何在Python中为BigQuery传递动态日期变量？

问题原因 #

用f-string直接插入日期变量时，生成的SQL语句中日期值没有被引号包裹，BigQuery会将2022-03-08解析为整数减法运算（2022-3-8），导致TIMESTAMP函数接收到INT64类型参数，不符合函数支持的参数类型，因此报错。

解决方法 #

方法1：在f-string中为日期变量添加引号

修改查询语句，将日期变量用双引号包裹，确保生成的SQL中TIMESTAMP的参数是带引号的字符串，和硬编码格式一致：

start_date= "2022-03-08"
end_date = "2022-05-03"
query = f"""
    SELECT SUM(CostInBillingCurrency) as cost,EXTRACT(MONTH from date) as MONTH, SubscriptionId, SubscriptionName
    FROM `table_name`
    WHERE
    Date BETWEEN TIMESTAMP("{start_date}")
    AND TIMESTAMP("{end_date}")
    GROUP BY
    SubscriptionId, SubscriptionName,MONTH 
"""

方法2：使用参数化查询（推荐）

利用BigQuery Python客户端的参数绑定功能，无需手动处理引号，还能避免SQL注入风险：

from google.cloud import bigquery

client = bigquery.Client()

start_date= "2022-03-08"
end_date = "2022-05-03"

query = """
    SELECT SUM(CostInBillingCurrency) as cost,EXTRACT(MONTH from date) as MONTH, SubscriptionId, SubscriptionName
    FROM `table_name`
    WHERE
    Date BETWEEN TIMESTAMP(@start_date)
    AND TIMESTAMP(@end_date)
    GROUP BY
    SubscriptionId, SubscriptionName,MONTH 
"""

job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ScalarQueryParameter("start_date", "STRING", start_date),
        bigquery.ScalarQueryParameter("end_date", "STRING", end_date),
    ]
)

query_job = client.query(query, job_config=job_config)
results = query_job.result()

内容的提问来源于stack exchange，提问作者Kanika Singla