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?
-
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.– Simon.S.A.2020-11-18 20:08:19 +00:00Commented Nov 18, 2020 at 20:08
-
This post gives the SQL to do that: stackoverflow.com/questions/75524437/…G. Grothendieck– G. Grothendieck2023-02-24 14:16:12 +00:00Commented Feb 24, 2023 at 14:16
Add a comment
|
1 Answer
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.
1 Comment
Ian Gow
"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.