在DuckDB（dbplyr）中混合dplyr与SQL的查询持久化问题

在R中采用Arrow与DuckDB结合的混合方案时，切换操作上下文后，DuckDB表无法持久化dplyr的查询结果。例如：

library(tidyverse)
library(duckdb)
library(arrow)
library(dbplyr)

iris_duck <-  iris |>
  to_duckdb(table_name = 'iris_duck') |> 
  mutate(new = Sepal.Length)

print(iris_duck)

输出显示包含新增的new列：

# Source:   SQL [?? x 6]
# Database: DuckDB 0.8.0 [unknown@Linux 4.18.0-513.24.1.el8_9.x86_64:R 4.2.3/:memory:]
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species   new
          <dbl>       <dbl>        <dbl>       <dbl> <chr>   <dbl>
 1          5.1         3.5          1.4         0.2 setosa    5.1
 2          4.9         3            1.4         0.2 setosa    4.9
 3          4.7         3.2          1.3         0.2 setosa    4.7
 4          4.6         3.1          1.5         0.2 setosa    4.6
 5          5           3.6          1.4         0.2 setosa    5  
 6          5.4         3.9          1.7         0.4 setosa    5.4
 7          4.6         3.4          1.4         0.3 setosa    4.6
 8          5           3.4          1.5         0.2 setosa    5  
 9          4.4         2.9          1.4         0.2 setosa    4.4
10          4.9         3.1          1.5         0.1 setosa    4.9

但直接通过连接访问原表时，看不到新增列：

con = iris_duck$src$con
tbl(con, sql("SELECT * from iris_duck"))

输出：

# Source:   SQL [?? x 5]
# Database: DuckDB 0.8.0 [unknown@Linux 4.18.0-513.24.1.el8_9.x86_64:R 4.2.3/:memory:]
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
          <dbl>       <dbl>        <dbl>       <dbl> <chr>  
 1          5.1         3.5          1.4         0.2 setosa 
 2          4.9         3            1.4         0.2 setosa 
 3          4.7         3.2          1.3         0.2 setosa 
 4          4.6         3.1          1.5         0.2 setosa 
 5          5           3.6          1.4         0.2 setosa 
 6          5.4         3.9          1.7         0.4 setosa 
 7          4.6         3.4          1.4         0.3 setosa 
 8          5           3.4          1.5         0.2 setosa 
 9          4.4         2.9          1.4         0.2 setosa 
10          4.9         3.1          1.5         0.1 setosa 
# ℹ more rows
# ℹ Use `print(n = ...)` to see more rows

需求是找到无需调用compute()的方法，让dplyr操作结果持久化，以便后续用SQL处理复杂任务。

方法1：创建持久化视图 #

用dbplyr::create_view()将dplyr链式操作的结果作为视图保存到DuckDB中，后续可直接通过SQL访问该视图：

# 基于dplyr操作结果创建视图
create_view(con, "iris_duck_with_new", iris_duck)

# 直接用SQL查询视图
tbl(con, sql("SELECT * FROM iris_duck_with_new"))

视图是基于原表的虚拟表，不会占用额外存储，且会自动同步原表的更新。

方法2：直接生成SQL并创建物理表 #

如果需要物理表（而非视图），可以先提取dplyr操作对应的SQL语句，再执行CREATE TABLE语句：

# 提取dplyr操作生成的SQL
query_sql <- sql_render(iris_duck)

# 执行CREATE TABLE语句将结果持久化为物理表
dbExecute(con, sql("CREATE TABLE iris_duck_with_new AS {{query_sql}}"))

# 查询物理表
tbl(con, sql("SELECT * FROM iris_duck_with_new"))

这种方式会生成独立的物理表，数据会被持久化存储，适合后续需要频繁访问或修改的场景。

方法3：使用持久化连接操作 #

先建立DuckDB持久化连接，再将数据导入为持久化表，后续dplyr操作可直接生成新的持久化表：

# 建立持久化连接（指定path参数可将数据存储到磁盘）
con <- dbConnect(duckdb())

# 将数据导入为持久化表
iris_duck <- copy_to(con, iris, name = "iris_duck", overwrite = TRUE)

# 在持久化表上执行dplyr操作并创建新表
iris_duck |> mutate(new = Sepal.Length) |> copy_to(con, "iris_duck_with_new", overwrite = TRUE)

# 查询新表
tbl(con, sql("SELECT * FROM iris_duck_with_new"))

这种方式全程基于持久化连接操作，避免临时表的上下文丢失问题。

内容的提问来源于Stack Exchange，提问作者Matthew Son