dplyr::tbl()函数因权限受限无法访问SQL Server数据表求助

我通过RStudio连接Microsoft SQL Server的代码如下：

conn <- dbConnect(odbc(),"myconnection",uid="***",pwd="***",schema="dbo",access="readonly")

为了用dplyr操作数据，我用tbl()创建数据引用：

data <- tbl(conn, "data") 

但目标数据表中有一列我没有访问权限，其余列均可正常读取。tbl()默认会执行SELECT * FROM data，这直接导致权限报错。即使我尝试指定列查询：

select(tbl(conn, "data"), "columnX")

对应的SQL是SELECT columnX FROM data，但依然失败。怀疑是tbl()默认调用SELECT *获取元数据的步骤触发了权限问题，请问有什么解决办法？

解决办法 #

1. 直接用dbGetQuery执行指定列的SQL #

绕过tbl()的默认行为，直接编写SQL查询所需列，避免触发全表查询：

# 直接拉取指定列的数据
data <- dbGetQuery(conn, "SELECT columnX, columnY FROM dbo.data")

# 如果需要继续用dplyr操作，转成tibble格式
library(tibble)
data_tbl <- as_tibble(data)

2. 用sql()构造自定义查询创建延迟加载的tbl #

如果想保留dplyr的延迟加载特性（不立即拉取数据到本地），可以直接基于指定列的SQL语句创建tbl对象，避免默认的元数据查询：

library(dbplyr)
# 基于自定义SQL创建tbl，后续操作会基于此继续构建SQL
data <- tbl(conn, sql("SELECT columnX, columnY FROM dbo.data"))

后续对这个data对象使用filter()、mutate()等dplyr函数时，会在这个初始查询的基础上生成新的SQL，不会再触发全表查询。

3. 先获取有权限的列名再构造查询 #

如果不清楚具体哪些列有权限，可以先查询表的列列表，排除无权限的列后再构造查询：

# 获取数据表的所有列名
all_columns <- dbListFields(conn, "data")
# 排除无权限的列（替换成实际的受限列名）
allowed_columns <- setdiff(all_columns, "restricted_column")
# 拼接成查询语句
query <- paste0("SELECT ", paste(allowed_columns, collapse = ", "), " FROM dbo.data")
# 创建tbl对象
data <- tbl(conn, sql(query))

补充说明 #

之所以select(tbl(conn, "data"), "columnX")会失败，是因为当你调用tbl(conn, "data")时，dbplyr会先尝试查询表的元数据（比如列名），这个过程可能会执行SELECT *或者访问系统表获取列信息，而如果表中存在你无权限的列，这个元数据获取步骤就会直接报错，导致后续的select操作根本无法执行。所以核心是避免在创建tbl时触发全表元数据查询，上面的方法都是围绕这个思路展开的。

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