如何在Jupyter/Colab的整单元格魔法命令中传递变量用于BigQuery查询?

有两种可落地的实现方式，优先使用原生参数传递方案避免安全风险： #

方法1：使用%%bigquery魔法命令原生参数传递功能（推荐） #

%%bigquery原生支持通过--params参数传入Python字典格式的变量，不需要手动处理转义，也不会有SQL注入风险。
步骤1：定义列表并构造参数字典

# 你的原始列表
item_list = ['a', 'b', 'c']
# 构造BigQuery可识别的参数字典，key为参数名，value为参数值
query_params = {"target_items": item_list}

步骤2：编写带参数的SQL查询

%%bigquery --project xxx --params $query_params query

SELECT *
FROM `xxx.database.table`
-- @前缀标识参数，BigQuery接收列表后会识别为ARRAY类型，需要用UNNEST拆分为单个值匹配
WHERE items IN UNNEST(@target_items)

方法2：字符串拼接实现（仅适用于列表内容完全可控的场景） #

如果是临时查询、且确认列表内容没有恶意注入风险，可以用字符串预处理拼接SQL语句：
步骤1：预处理列表为SQL可识别的格式

item_list = ['a', 'b', 'c']
# 把列表转成 'a','b','c' 格式的字符串，直接适配SQL IN子句的语法
sql_in_content = "', '".join(item_list)

步骤2：在魔法命令中引用预处理后的变量

%%bigquery --project xxx query

SELECT *
FROM `xxx.database.table`
WHERE items IN ('{sql_in_content}')

注意：该方法没有做转义处理，若列表内容来自用户输入等不可控来源，会存在严重的SQL注入风险，非临时测试场景请勿使用 #

内容的提问来源于stack exchange，提问作者Marcelo Soares