Vertex AI Notebook中BigQuery的WEEK(WEEKDAY)参数注入失效问题

在Vertex AI笔记本使用%%bigquery魔法命令时，传入字符串类型的星期参数（比如"WEDNESDAY"）会被自动添加引号，导致DATE_DIFF函数的WEEK参数报错——因为WEEK需要的是WEEKDAY枚举值，而非带引号的字符串。

下面提供两种可行的解决方案：

方案1：用CASE语句映射字符串到枚举值 #

通过CASE表达式把传入的字符串参数转换成对应的WEEKDAY枚举，兼容参数注入的字符串格式：

params = {"day_of_week": "WEDNESDAY"}

%%bigquery sales_df --project $project_id --params $params

SELECT created_date, num_sales
FROM sales
WHERE DATE_DIFF(created_date, "2023-01-01", 
  CASE @day_of_week
    WHEN 'MONDAY' THEN WEEK(MONDAY)
    WHEN 'TUESDAY' THEN WEEK(TUESDAY)
    WHEN 'WEDNESDAY' THEN WEEK(WEDNESDAY)
    WHEN 'THURSDAY' THEN WEEK(THURSDAY)
    WHEN 'FRIDAY' THEN WEEK(FRIDAY)
    WHEN 'SATURDAY' THEN WEEK(SATURDAY)
    WHEN 'SUNDAY' THEN WEEK(SUNDAY)
  END
) BETWEEN -12 and 11

方案2：用EXECUTE IMMEDIATE动态拼接SQL（注意风险） #

如果允许使用动态SQL，可以通过字符串拼接生成正确的枚举调用，绕开参数自动加引号的问题：

params = {"day_of_week": "WEDNESDAY"}

%%bigquery sales_df --project $project_id --params $params

EXECUTE IMMEDIATE '''
SELECT created_date, num_sales
FROM sales
WHERE DATE_DIFF(created_date, "2023-01-01", WEEK(''' || @day_of_week || ''')) BETWEEN -12 and 11
'''

⚠️ 注意：这种方式要确保传入的day_of_week参数是完全可信的，避免SQL注入风险。

目前BigQuery原生没有直接将字符串转为WEEKDAY枚举的函数，只能通过上述间接方式处理。你提交的功能请求后续若被支持，会有更简洁的解决办法。

内容的提问来源于stack exchange，提问作者Ben Watson