How to use show_query? Returning "no applicable method"

Question

After connecting RStudio to Amazon Athena, I am attempting to look under the hood to extract the SQL statements using show_query. The code runs smoothly until I attempt to use this function.

library(tidyverse)
library(dplyr)
library(dbplyr)
library(odbc)
library(DBI)
library(rJava)

con <- dbConnect(noctua::athena(),
                 aws_access_key_id = "***",
                 aws_secret_access_key = "***",
                 s3_staging_dir = "s3://bucket-folder/",
                 region_name = 'region_name')

querytest <- dbGetQuery(con, "SELECT * FROM schema.table") 
test1 <- querytest %>% 
  filter(category != "NA") %>% 
  show_query()

Error in UseMethod("show_query") : 
  no applicable method for 'show_query' applied to an object of class "c('data.table', 'data.frame')" 

querytest <- dbGetQuery(con, "SELECT * FROM schema.table")
test2 <- querytest %>% 
  filter(category != "NA") %>% 
  remote_query()

Error in UseMethod("db_sql_render") : 
  no applicable method for 'db_sql_render' applied to an object of class "NULL"

Neither show_query or remote_query run successfully.

To my understanding, show_query should run successfully when applied to DB connection or DB table.

How could I go about fixing this issue?

Answer 1

If you are using dbplyr, rather than using dbGetQuery, you should be using tbl(). For example

querytest <- tbl(con, "schema.table")
test1 <- querytest %>% 
  filter(category != "NA") %>% 
  show_query()

The tbl function with a connection should return an object with class "tbl_sql" which is what show_query() is looking for. You can check class(querytest)