Convert dplyr pipeline into SQL string

Question

I would like to convert a (short) dplyr pipeline into a string representation of its equivalent SQL. For example:

library(dplyr)
dbplyr::lazy_frame() %>% filter(foo == 'bar')

will print out essentially what I'm looking for, namely:

<SQL>
SELECT *
FROM `df`
WHERE (`foo` = 'bar')

the problem is that this is merely printed out. In particular I don't see how to assign it to a string. I've tried appending %>% show_query() but I believe that has the same result (i.e., displaying the query rather conversion to a string). Appending %>% as.character() does produce something but it is not SQL (it's a character vector whose first element is "list(name = \"filter\", x = list(x = list(), vars = character(0)), dots = list(~foo == \"bar\"), args = list())").

DanielBonnery · Accepted Answer · 2020-02-25 15:13:34Z

4

You can capture the output:

library(dplyr)
x<-capture.output(dbplyr::lazy_frame() %>% filter(foo == 'bar'))
x
[1] "<SQL>"                 "SELECT *"              "FROM `df`"             "WHERE (`foo` = 'bar')"

or

dbplyr::lazy_frame() %>% filter(foo == 'bar')%>%capture.output

answered Feb 25, 2020 at 15:13

DanielBonnery

4053 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

banbh Over a year ago

Thank you -- that does answer my question. I am going to wait to see if someone comes up with a dplyr/dbplyr function that converts it directly to a string (which after all must be possible in some way). If no one does, then I'll accept this.

G. Grothendieck · Accepted Answer · 2020-02-25 15:49:50Z

3

Use remote_query to get an c("sql", "character") and then convert that to character.

library(dbplyr)

lazy_frame() %>%
  filter(foo == 'bar') %>%
  remote_query %>%
  as.character
## [1] "SELECT *\nFROM `df`\nWHERE (`foo` = 'bar')"

answered Feb 25, 2020 at 15:49

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

1 Comment

banbh Over a year ago

As of dbplyr 2.3.0 this needs the columns listed in the lasy-frame. I.e., something like lazy_frame(foo = 0). It would be nice if there was a way to avoid this, but I think there may not be.

Collectives™ on Stack Overflow

Convert dplyr pipeline into SQL string

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related