在RMarkdown的BigQuery SQL代码块中传递参数的方法探究

最近我一直在折腾怎么在RMarkdown里给连接BigQuery的SQL代码块传递参数，踩了好几个坑，最后终于找到可行方案，分享一下整个过程：

初始尝试：直接用变量注入失败 #

一开始我写了这样的测试代码，期望能把R里的变量传到SQL块里：

library(bigrquery)
bq_auth(path = "access_token.json")
db <- dbConnect(
  bigquery(),
  dataset = 'my_data',
  project = 'my-project',
  use_legacy_sql = FALSE
)
parameter_value = 10L

SELECT @parameter_value

print(x) # 期望输出10

结果完全达不到预期，参数根本没传进去。

第一次改进：标量可用但向量报错，且非原生参数化 #

后来试了用?parameter_value的方式注入，标量参数倒是能正常工作了，但换成向量参数就直接报错。比如我定义：

parameter_value = c(10L, 20L)

然后SQL块这么写：

SELECT UNNEST(?parameter_value)

得到的错误是：

Error in vapply(values, function(x) dbQuoteLiteral(conn, x), character(1)) : 
  values must be length 1, but FUN(X[ 1 ]) result is length 2

而且更遗憾的是，就算标量能跑，也只是简单的文本注入，并没有用到BigQuery原生的参数化查询机制。

一度陷入困境：怀疑API实现缺失 #

当时我甚至觉得这个功能根本没法实现，因为bigrquery包没有完全实现DBI的相关API，针对这个问题我还提交了涉及knitr包的issue。

最终解决方案：用SQL代码块的params参数 #

不过现在终于找到正确的打开方式了——直接在SQL代码块里使用params参数来传递！

标量参数示例 #

library(bigrquery)
bq_auth(path = "access_token.json")
db <- dbConnect(
  bigquery(),
  dataset = 'my_data',
  project = 'my-project',
  use_legacy_sql = FALSE
)
param_scalar = 10L

SELECT @param_scalar

向量参数示例 #

param_vector = c(10L, 20L)

SELECT UNNEST(@param_vector)

这样不仅能正常传递标量和向量参数，还能利用BigQuery原生的参数化查询机制，安全又高效。

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