dplyr lazy query mutate column using str_extract

user_4406123 0
Viewed 168

I try to query a table table_a, and I like to mutate a column substr_col based on an existing column col with stringr::str_extract while it is in a lazy query state. I encountered an error message complaining col does not exist.

object 'col' not found

conn <- DBI::dbConnect(...)
dplyr::tbl(conn, table_a) %>%
dplyr::mutate(substring_col = stringr::str_extract(col, "^[A-Z]-\\d{3}")) %>%
dplyr::collect()

But this code works when I collect the data first and then call stringr::str_extract

conn <- DBI::dbConnect(...)
dplyr::tbl(conn, table_a) %>%
dplyr::collect() %>%
dplyr::mutate(substring_col = stringr::str_extract(col, "^[A-Z]-\\d{3}"))

I like to use the substring_col as a filter condition while the query is lazy, how should I do that?

dbplyr stringr dplyr r
1 Answers

As @IceCreanToucan states, str_extract is not on dbplyr's list of translations. Hence it will not be able to execute this code on the database. (I assume you are using dbplyr as it is the main package for having dplyr commands translated into SQL).

We can test this as follows:

library(dbplyr)
library(dplyr)
library(stringr)

data(starwars)

# pick your simulated connection type (there are many options, not just what I have shown here)
remote_df = tbl_lazy(starwars, con = simulate_mssql())
remote_df = tbl_lazy(starwars, con = simulate_mysql())
remote_df = tbl_lazy(starwars, con = simulate_postgres())

remote_df %>%
  mutate(substring_col = str_extract(name, "Luke")) %>%
  show_query()

show_query() should return the SQL that our mutate has been translated into. But instead I receive a clear message: "Error: str_extract() is not available in this SQL variant". This makes it clear translation is not defined.

However, there is a translation defined for grep and grepl (etc.) so the following should work:

remote_df %>%
  mutate(substring_col = grepl("Luke", name)) %>%
  show_query()

But it will return you slightly different output.

user_77429810
Related