R语言调用Google BigQuery时IN运算符报错如何解决

问题根因 #

核心问题有两点：

1. 你通过dplyr::select() + distinct()得到的pages是单列tibble数据框，不是纯字符向量，直接拼接SQL时R会自动带出c()的向量声明语法，这不属于BigQuery支持的SQL语法
2. 字符串类型的字段值传入SQL的IN条件时，需要给每个值单独包裹单引号，并用英文逗号分隔

解决方法 #

方案1：手动格式化IN条件参数（快速适用） #

修改pages取值逻辑和SQL拼接逻辑，完整代码如下：

library(xlsx)
library(dplyr)
library(bigrquery)

test_control_group <- xlsx::read.xlsx('./test_control_group.xlsx',
                                      sheetName = 'Test_Control')

# 用pull直接提取page列为向量，而非保留tibble结构
pages <- test_control_group %>%
  dplyr::pull(page) %>%
  unique()

# 给每个页面URL包裹单引号，转义内容中可能存在的单引号后用逗号拼接
pages_sql <- paste0("'", gsub("'", "\\'", pages), "'", collapse = ", ")

projectid <- "googleproject"

sql <- paste0(
  "SELECT * FROM `googleproject.dataset.table`
   WHERE page IN (", pages_sql, ")"
)

tb <- bigrquery::bq_project_query(projectid, sql)

方案2：使用bigrquery自带参数化查询（更安全，推荐） #

如果需要规避SQL注入风险，或者参数数量较多，推荐用bigrquery原生支持的参数化查询，无需手动拼接字符串：

library(xlsx)
library(dplyr)
library(bigrquery)

test_control_group <- xlsx::read.xlsx('./test_control_group.xlsx',
                                      sheetName = 'Test_Control')

pages <- test_control_group %>%
  dplyr::pull(page) %>%
  unique()

projectid <- "googleproject"

# 用UNNEST解析传入的数组参数
sql <- "SELECT * FROM `googleproject.dataset.table` WHERE page IN UNNEST(@pages)"

tb <- bigrquery::bq_project_query(
  projectid, 
  sql,
  parameters = list(pages = pages)
)

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