r - 使用 dplyr 从临时 postgres 表创建和检索数据

Question

我正在尝试弄清楚如何使用 dplyr 从在 Postgres 数据库上创建的临时表中检索数据。以下代码有效(我能够使用 R 创建一个临时表，并检索这些结果)。

flights.db <- src_postgres(dbname = dbname, host = host, port = port,
                           user = user, password=pwd)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, host = host, port = port, dbname = dbname, user = user, password = pwd)

## Writes the SQL code to create a temporary table on the Postgres Database
sql_tmp_create <- paste0('create temp table tmptblaa as 
              select * from connections
              where \"Carrier\" = \'AA\' and \"Year\" = \'2015\'   ')

## Executes the code
dbSendQuery(con, sql_tmp_create)

## Return 10 results from temporary table on Postgres
dbGetQuery(con, 'select * from tmptblaa limit 10')

但是，这对我不起作用，我收到一个错误，指出该表不存在:

flights <-tbl(flights.db, "tmptblaa")

知道我可能做错了什么吗？有关更多上下文，这是为了提高我正在为学校项目开发的 Shiny 应用程序的效率。谢谢。

Answer 1

临时表的问题在于它们只存在于由一个连接对象发起的一个 session 中。如果您使用上面的代码创建临时表，则只能使用 con 对象访问临时表。 dplyr 不允许您创建表 afaik，所以恐怕您不能做您想做的事。

但是，如果您想在数据库中预先聚合内容，您可以考虑使用物化 View 。 dplyr 可以从物化 View 进行查询，并且由于它是持久的，因此无需使用 dplyr 创建 mv。您可以使用例如从物化 View 查询数据con %>% tbl(sql("select * from my_mv")) %>% collect(n = Inf)。这里的重要细节是 sql 函数，语法与常规表略有不同。