在Big Query | Python中传递动态日期变量

Question

我正试图在python中运行这个bigQuery：

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 """

这就是我得到的错误：

google.api_core.exceptions.BadRequest: 400 No matching signature for function TIMESTAMP for argument types: INT64. Supported signatures: TIMESTAMP(STRING, [STRING]); TIMESTAMP(DATE, [STRING]); TIMESTAMP(DATETIME, [STRING]); TIMESTAMP(TIMESTAMP) at [5:18]

如果我将日期硬编码为：

Date BETWEEN TIMESTAMP("2022-06-01")
AND TIMESTAMP("2022-06-30")

它运行良好。所以这个查询是正确的。

请帮忙弄清楚如何动态传递日期。

Answer 1

您必须添加引号。如果执行result = f'TIMESTAMP({start_date})'，它将渲染为TIMESTAMP(2022-06-01)。所以把它改成result = f'TIMESTAMP("{start_date}")或result = f"TIMESTAMP('{start_date}')"，你就应该很好了。

Answer 2

看起来变量只是在查询中输入值，但查询需要字符串，您更新查询如下

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 """