2

I have a rather long and convoluted query that I use as a starting point for doing some analysis. I have replicated that one from scratch using dbplyr so I can use dplyr verbs without getting the database into memory. How can I achieve this without having to replicate the starting query using dbplyr?

Werner Hertzog
  • 2,002
  • 3
  • 24
  • 36
Andrés Mejía
  • 21
  • 1
  • Are you asking for a way to automatically translate SQL into dplyr (so dbplyr can then translate it back into SQL)? An example would assist. – Simon.S.A. Nov 18 '20 at 20:08
  • This post gives the SQL to do that: https://stackoverflow.com/questions/75524437/emulating-sliders-slide-index-functions-in-sql-for-non-consecutively/75524892#75524892 – G. Grothendieck Feb 24 '23 at 14:16

1 Answers1

2

Take a look at this answer. It explicitly sets the SQL query for a dbplyr table. You can use the same idea:

# read file containing existing query
start_query = your_favorite_way_to_read_file_here(file_name)

# create connection
con = DBI::dbConnect(...)

# initial table
my_table = tbl(con, sql(start_query))

To verify that it has worked as intended try show_query(my_table) or head(my_table).

Note that dbplyr does not translate dplyr into highly optimized queries. So if your initial query is complex / slow to run, adding further manipulation via dbplyr could perform poorly. In this case it might be better to write your complex query to another table.

Simon.S.A.
  • 6,240
  • 7
  • 22
  • 41
  • 1
    "Adding further manipulation via `dbplyr` could perform poorly." Indeed! One way to " write your complex query to another table" would be simply to append `%>% compute()` to the end of the line creating `my_table`. – Ian Gow Apr 01 '21 at 19:15