Использование purrr :: map2 () с dbplyr

Я пытаюсь выбрать строки из одной таблицы ("positons") со значениями для определенного столбца ("position"), которые попадают в диапазоны, определенные в другой ("my_ranges") таблице, а затем добавить тег группировки из Таблица "my_ranges".

Я могу сделать это, используя тибблы и пару вызовов purrr::map2, но тот же подход не работает с тибблами базы данных dbplyr. Это ожидаемое поведение, и если да, то есть ли другой подход, который я должен использовать для использования dbplyr для такого рода задач?

Вот мой пример:

library("tidyverse")
set.seed(42)

my_ranges <-
  tibble(
    group_id = c("a", "b", "c", "d"),
    start = c(1, 7, 2, 25),
    end = c(5, 23, 7, 29)
    )

positions <-
  tibble(
    position = as.integer(runif(n = 100, min = 0, max = 30)),
    annotation = stringi::stri_rand_strings(n = 100, length = 10)
  )

# note: this works as I expect and returns a tibble with 106 obs of 3 variables:
result <- map2(.x = my_ranges$start, .y = my_ranges$end,
             .f = function(x, y) {between(positions$position, x, y)}) %>%
  map2(.y = my_ranges$group_id,
              .f = function(x, y){
                positions %>%
                  filter(x) %>%
                  mutate(group_id = y)}
) %>% bind_rows()

# next, make an in-memory db for testing:
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")

# copy data to db
copy_to(con, my_ranges, "my_ranges", temporary = FALSE)
copy_to(con, positions, "positions", temporary = FALSE)

# get db-backed tibbles:
my_ranges_db <- tbl(con, "my_ranges")
positions_db <- tbl(con, "positions")

# note: this does not work as I expect, and instead returns a tibble with 0 obsevations of 0 variables:
# database range-based query:
db_result <- map2(.x = my_ranges_db$start, .y = my_ranges_db$end,
                  .f = function(x, y) {
                    between(positions_db$position, x, y)
                    }) %>%
  map2(.y = my_ranges_db$group_id,
       .f = function(x, y){
         positions_db %>%
           filter(x) %>%
           mutate(group_id = y)}
  ) %>% bind_rows()

r tidyverse purrr dbplyr
person bheavner    schedule 26.03.2018    source источник
comment
Я могу сделать это в прямом SQL: range_query ‹- paste0 (SELECT range.group_id как group_id, position.position как position, position.annotation как аннотация FROM (SELECT * FROM my_ranges) как диапазоны, позиции WHERE position.position BETWEEN range.start И range.end;) результаты ‹- DBI :: dbGetQuery (con, range_query)   -  person bheavner    schedule 26.03.2018

Ответы (2)


arrow_upward
3
arrow_downward

dbplyr переводит R в SQL. Списки отсутствуют в SQL. map создает списки. Таким образом, невозможно перевести map в SQL.

В основном переведены dplyr функций и некоторые base функции, они работают и над tidyr функциями, как я понял. При использовании dbplyr постарайтесь использовать SQL логику в своем подходе, иначе она легко сломается.

person Moody_Mudskipper    schedule 27.03.2018
comment
Спасибо! Это помогает прояснить мои мысли! - person bheavner; 28.03.2018
comment
так что нет возможности запустить что-то подобное удаленно iris_db %>% sapply(unique) (iris_db %>% map(unique))? - person user63230; 29.10.2019
comment
Попробуйте что-нибудь вроде map(names(iris_db), ~select_at(iris_db,.x) %>% distinct() %>% collect()). Он будет запускать отдельный запрос sql для каждого столбца - person Moody_Mudskipper; 29.10.2019
comment
большое спасибо, я смог заставить его работать, используя такой подход, основанный на примере виньетки здесь: map(dbListFields(con, "flights"), ~select_at(flights_db,.x) %>% distinct() %>% collect()) Мне пришлось заменить names(flights_db) на dbListFields(con, "flights"). Есть ли способ использовать команду names в наборе данных db для печати имен столбцов? Это намного более интуитивно понятно, чем набирать dbListFields - person user63230; 29.10.2019
comment
Ах да, прости. Я думаю, colnames() тоже подойдет. - person Moody_Mudskipper; 29.10.2019

arrow_upward
3
arrow_downward

Пока каждая итерация создает таблицу с одинаковыми размерами, может существовать изящный способ передать всю операцию в базу данных. Идея состоит в том, чтобы использовать как map(), так и reduce() из purrr. Каждая tbl_sql() операция является ленивой, поэтому мы можем выполнять итерацию по ним, не беспокоясь об отправке кучи запросов, а затем мы можем использовать union(), который в основном добавит результирующий SQL от каждой итерации к следующей, используя предложение UNION из данной базы данных. Вот пример:

library(dbplyr, warn.conflicts = FALSE)
library(dplyr, warn.conflicts = FALSE)
library(purrr, warn.conflicts = FALSE)
library(DBI, warn.conflicts = FALSE)
library(rlang, warn.conflicts = FALSE)

con <- DBI::dbConnect(RSQLite::SQLite(), path = ":dbname:")

db_mtcars <- copy_to(con, mtcars)

cyls <- c(4, 6, 8)

all <- cyls %>%
  map(~{
    db_mtcars %>%
      filter(cyl == .x) %>%
      summarise(mpg = mean(mpg, na.rm = TRUE)
      )
  }) %>%
  reduce(function(x, y) union(x, y)) 

all
#> # Source:   lazy query [?? x 1]
#> # Database: sqlite 3.22.0 []
#>     mpg
#>   <dbl>
#> 1  15.1
#> 2  19.7
#> 3  26.7

show_query(all)
#> <SQL>
#> SELECT AVG(`mpg`) AS `mpg`
#> FROM (SELECT *
#> FROM (SELECT *
#> FROM `mtcars`)
#> WHERE (`cyl` = 4.0))
#> UNION
#> SELECT AVG(`mpg`) AS `mpg`
#> FROM (SELECT *
#> FROM (SELECT *
#> FROM `mtcars`)
#> WHERE (`cyl` = 6.0))
#> UNION
#> SELECT AVG(`mpg`) AS `mpg`
#> FROM (SELECT *
#> FROM (SELECT *
#> FROM `mtcars`)
#> WHERE (`cyl` = 8.0))

dbDisconnect(con)
person edgararuiz    schedule 22.09.2018