Cannot create dplyr::tbl object from temporary database

[andreassoteriadesmoj]

I would like to use R’s dplyr on database tables instead of in-memory data frames. I want to do something very simple, like in the following example:

con <- Rdbtools::connect_athena()
my_data <- dplyr::tbl(con, dbplyr::in_schema('some_existing_database', 'some_existing_table'))

In the example above, dplyr will create a tbl object from table some_existing_table which is in some_existing_database.

The example above works just fine and returns a tbl object. However, when I try to create the tbl object with temporary tables, it fails:

# Create temporary table
Rdbtools::dbExecute(
  con, 
  "CREATE TABLE __temp__.my_data AS SELECT * FROM some_existing_database.some_existing_table limit 10"
)

# Create tbl from temporary table
my_data <- dplyr::tbl(con, dbplyr::in_schema('__temp__', 'my_data'))

# Error: EntityNotFoundException (HTTP 400). Database __temp__ not found.

Any tips please?

[pjrh-moj]

Potential solution in #14 and discussed on slack. Not a perfect solution, but quick and easy to understand without digging into the guts of dplyr