Как передать SQL в dplyr R?

• union_all, которая принимает две таблицы, доступ к которым осуществляется через dbplyr, и выводит одну таблицу с использованием некоторого настраиваемого кода SQL.
• write_to_datebase функция, которая принимает таблицу, доступ к которой осуществляется через dbplyr, и преобразует ее в код, который может быть выполнен через DBI::dbExecute

Автоматическая обвязка

dbplyr автоматически перенаправляет ваш код в следующий запрос, когда вы работаете со стандартными dplyr глаголами, для которых определены переводы SQL. Пока определены переводы sql, вы можете связать вместе много каналов (я использовал 10 или более одновременно) с (почти) единственным недостатком, заключающимся в том, что переведенный запрос sql становится трудным для чтения человеком.

Например, рассмотрим следующее:

library(dbplyr)
library(dplyr)

tmp_df = data.frame(col1 = c(1,2,3), col2 = c("a","b","c"))

df1 = tbl_lazy(tmp_df, con = simulate_postgres())
df2 = tbl_lazy(tmp_df, con = simulate_postgres())

df = left_join(df1, df2, by = "col1") %>%
  distinct()

Когда вы затем вызываете show_query(df), R возвращает следующий автоматически сгенерированный код SQL:

SELECT DISTINCT *
FROM (

SELECT `LHS`.`col1` AS `col1`, `LHS`.`col2` AS `col2.x`, `RHS`.`col2` AS `col2.y`
FROM `df` AS `LHS`
LEFT JOIN `df` AS `RHS`
ON (`LHS`.`col1` = `RHS`.`col1`)

) `dbplyr_002`

Но не так красиво отформатирован. Обратите внимание, что начальная команда (левое соединение) отображается как вложенный запрос с отдельным запросом во внешнем запросе. Следовательно, df - это ссылка R на таблицу удаленной базы данных, определенную вышеуказанным запросом sql.

Создание пользовательских функций SQL

Вы можете перенаправить dbplyr в пользовательские функции SQL. Трубопровод означает, что передаваемый по трубопроводу объект становится первым аргументом принимающей функции.

custom_distinct <- function(df){
  db_connection <- df$src$con

  sql_query <- build_sql(con = db_connection,
                         "SELECT DISTINCT * FROM (\n",
                         sql_render(df),
                         ") AS nested_tbl"
  )
  return(tbl(db_connection, sql(sql_query)))
}

df = left_join(df1, df2, by = "col1") %>%
  custom_distinct()

Когда вы затем вызываете show_query(df), R должен вернуть следующий код SQL (я говорю «должен», потому что я не могу заставить его работать с смоделированными соединениями sql), но не в таком красивом формате:

SELECT DISTINCT * FROM (

SELECT `LHS`.`col1` AS `col1`, `LHS`.`col2` AS `col2.x`, `RHS`.`col2` AS `col2.y`
FROM `df` AS `LHS`
LEFT JOIN `df` AS `RHS`
ON (`LHS`.`col1` = `RHS`.`col1`)

) nested_tbl

Как и в предыдущем примере, df - это ссылка R на таблицу удаленной базы данных, определенную вышеупомянутым запросом sql.

Преобразование dbplyr в DBI

Вы можете взять код из существующей dbplyr удаленной таблицы и преобразовать его в строку, которая может быть выполнена с помощью DBI::db*.

Как еще один способ написания отдельного запроса:

df1 = tbl_lazy(tmp_df, con = simulate_postgres())
df2 = tbl_lazy(tmp_df, con = simulate_postgres())

df = left_join(df1, df2, by = "col1")

custom_distinct2 = paste0("SELECT DISTINCT * FROM (",
                          as.character(sql_render(df)),
                          ") AS nested_table")

local_table =   dbGetQuery(db_connection, custom_distinct2)

Что вернет локальный фрейм данных R с эквивалентной командой sql, как в предыдущих примерах.