#postgresql #dbplyr #wrds
#postgresql #dbplyr #wrds
Вопрос:
WRDS является ведущим поставщиком исследовательских данных для ученых и других исследователей в бизнесе и смежных областях. WRDS предоставляет базу данных PostgreSQL, но это база данных только для чтения.
Для некоторых задач невозможность записи данных в базу данных очень ограничивает. Например, если я хочу запустить исследование событий с использованием ежедневных возвратов акций, мне нужно будет объединить мой (относительно небольшой) локальный набор данных events
crsp.dsf
, который составляет около 18 ГБ данных.
Один из вариантов — поддерживать мою собственную базу данных с копией crsp.dsf
и записывать events
в эту базу данных и объединять там. Но я ищу вариант, который позволяет мне использовать базу данных WRDS для этой цели. К сожалению, нет способа использовать copy_to
or dbWriteTable
, поскольку база данных WRDS доступна только для чтения.
Ответ №1:
Один из вариантов — использовать что-то вроде следующей функции, которая превращает локальный фрейм данных в удаленный фрейм данных с использованием SQL даже при использовании соединения только для чтения.
df_to_pg <- function(df, conn) {
collapse <- function(x) paste0("(", paste(x, collapse = ", "), ")")
names <- paste(DBI::dbQuoteIdentifier(conn, names(df)), collapse = ", ")
values <-
df %>%
lapply(DBI::dbQuoteLiteral, conn = conn) %>%
purrr::transpose() %>%
lapply(collapse) %>%
paste(collapse = ",n")
the_sql <- paste("SELECT * FROM (VALUES", values, ") AS t (", names, ")")
temp_df_sql <- dplyr::tbl(conn, dplyr::sql(the_sql))
return(temp_df_sql)
}
Вот иллюстрация используемой функции. Функция была протестирована на PostgreSQL и SQL Server, но не будет работать на SQLite (из-за отсутствия VALUES
ключевого слова, которое работает таким образом).
Я считаю, что это должно работать на MySQL или Oracle, поскольку у них есть VALUES
ключевое слово.
library(dplyr, warn.conflicts = FALSE)
library(DBI)
pg <- dbConnect(RPostgres::Postgres())
events <- tibble(firm_ids = 10000:10024L,
date = seq(from = as.Date("2020-03-14"),
length = length(firm_ids),
by = 1))
events
#> # A tibble: 25 x 2
#> firm_ids date
#> <int> <date>
#> 1 10000 2020-03-14
#> 2 10001 2020-03-15
#> 3 10002 2020-03-16
#> 4 10003 2020-03-17
#> 5 10004 2020-03-18
#> 6 10005 2020-03-19
#> 7 10006 2020-03-20
#> 8 10007 2020-03-21
#> 9 10008 2020-03-22
#> 10 10009 2020-03-23
#> # … with 15 more rows
events_pg <- df_to_pg(events, pg)
events_pg
#> # Source: SQL [?? x 2]
#> # Database: postgres [iangow@/tmp:5432/crsp]
#> firm_ids date
#> <int> <date>
#> 1 10000 2020-03-14
#> 2 10001 2020-03-15
#> 3 10002 2020-03-16
#> 4 10003 2020-03-17
#> 5 10004 2020-03-18
#> 6 10005 2020-03-19
#> 7 10006 2020-03-20
#> 8 10007 2020-03-21
#> 9 10008 2020-03-22
#> 10 10009 2020-03-23
#> # … with more rows
Создано 2021-04-01 пакетом reprex (v1.0.0)
Комментарии:
1. Это здорово! Однако я не могу воспроизвести ваш пример в Oracle. Знаете ли вы, как переписать функцию, чтобы она функционировала и в Oracle? Вот сообщение об ошибке, которое я получаю (сокращенное, чтобы соответствовать комментарию): Ошибка: nanodbc / nanodbc.cpp:1617: 42S02: [Oracle][ODBC][Ora]ORA-00903: недопустимое имя таблицы <SQL> ‘ВЫБЕРИТЕ * ИЗ (ВЫБЕРИТЕ * ИЗ (ЗНАЧЕНИЯ (10000,’2020-03-14 UTC’), (10001, ‘2020-03-15 UTC’), … (10024, ‘2020-04-07 UTC’) ) КАК t ( «firm_ids», «date» )) «q01» ГДЕ (0 = 1)’
2. @CAJ я не знаю. Я предполагаю, что может иметь смысл настроить функцию для возврата SQL, а затем (используя небольшую таблицу) настроить SQL в соответствии с требованиями синтаксиса Oracle. Похоже, что часть «AS t (» может быть проанализирована Oracle как имя таблицы.