Use the copy_inline https://dbplyr.tidyverse.org/reference/copy_inline.html函数从dbplyr
包,这是在以下添加的issue https://github.com/tidyverse/dbplyr/issues/628就此主题提交。另请参阅问题here https://stackoverflow.com/questions/66910165/how-do-i-get-local-data-into-a-read-only-database-using-dplyr.
找到了它的使用示例here https://iangow.github.io/far_book/beaver68.html还有下面。
在运行下面的代码之前,请在 R 控制台中运行如下代码:
Sys.setenv(PGHOST = "wrds-pgdata.wharton.upenn.edu",
PGPORT = 9737L,
PGDATABASE = "wrds",
PGUSER = "YOUR_WRDS_ID",
PGPASSWORD = "YOUR_WRDS_PASSWORD")
然后(假设你有dplyr
, dbplyr
, and RPostgres
安装的软件包)您应该能够复制粘贴以下代码来检查它是否适合您。还可以找到更多例子here https://iangow.github.io/far_book/intro.html.
library(dplyr, warn.conflicts = FALSE)
library(DBI)
library(dbplyr, warn.conflicts = FALSE)
pg <- dbConnect(RPostgres::Postgres(), sslmode='require')
events <- tibble(permno = 14593L,
event_desc = c("WWDC 2005 ", "Apple Special Event",
"WWDC 2006"),
date = as.Date(c("2005-06-06", "2006-02-28",
"2006-02-28")))
events_pg <- copy_inline(pg, events)
dsf <- tbl(pg, sql("SELECT * FROM crsp.dsf"))
results <-
dsf %>%
inner_join(events_pg, join_by(permno, date)) %>%
select(permno, date, event_desc, ret, prc) %>%
collect()
results
#> # A tibble: 3 × 5
#> permno date event_desc ret prc
#> <dbl> <date> <chr> <dbl> <dbl>
#> 1 14593 2005-06-06 "WWDC 2005 " -0.00837 37.9
#> 2 14593 2006-02-28 "Apple Special Event" -0.0352 68.5
#> 3 14593 2006-02-28 "WWDC 2006" -0.0352 68.5
Created on 2023-04-23 with reprex v2.0.2 https://reprex.tidyverse.org