dbWriteTable() does not respect database and schema parameters for Snowflake?

[SwampThing0331]

A table (df_table) exists in two Snowflake databases, dev and prod. Both dev and prod have schemas of the same name (test) which house the tables. The table is successfully dropped in the prod environment, but remains in dev. Using dbWriteTable() to recreate the table in prod results in "Error: Table database = prod, schema = test, table = df_table exists in database, and both overwrite and append are FALSE".

If the table is also dropped in dev, the code executes without error and the table is created in the prod database and correct schema.

library(DBI)
library(odbc)

connection <-
  DBI::dbConnect(drv = odbc::odbc()
                 ,UID = 'BeMe'
                 ,PWD = rstudioapi::askForPassword("Please enter your password") 
                 ,Server = 'foo.snowflakecomputing.com' 
                 ,Driver = "SnowflakeDSIIDriver"
                 ,authenticator = 'externalbrowser')

dbExecute(connection, "drop table if exists prod.test.df_table;") # This works and the table is dropped from prod. A table of the same name and schema still exists in the dev database

df <- data.frame(col_1 = c("Foo",  "Bar",  "Baz"))

dbWriteTable(connection
             ,name = DBI::Id(database = 'prod', schema = 'test', table = 'df_table')
             ,df) # This throws the error in the description above

[ciasom]

I can confirm this as well. We can't have the same schema and table in two different snowflake databases which are visible by the role/user. But I'm not sure about where the problem is. It might be in DBI, or in snowflake ODBC driver, or even related to snowflake role/privilege management.

[simonpcouch]

tl;dr: you need to use catalog = rather than database = .

Thanks for the details, yall. I'm not able to reproduce the issue as you've described with odbc 1.4.1 and Snowflake 7.44.2:

# setup
library(DBI)
library(odbc)

con <- dbConnect(odbc(), dsn = "Snowflake", pwd = Sys.getenv("snowflakePass"))

con
#> <OdbcConnection> Simon@Snowflake
#>   Snowflake Version: 7.44.2

dbExecute(con, "USE ROLE ACCOUNTADMIN")
#> [1] 0
dbExecute(con, "USE WAREHOUSE odbc")
#> [1] 0

# no database currently selected:
dbGetQuery(con, 'SELECT CURRENT_DATABASE()')
#>   CURRENT_DATABASE()
#> 1               <NA>

# create a database.schema.table
dbExecute(con, "CREATE DATABASE ODBC1")
#> [1] 0
dbExecute(con, "CREATE SCHEMA ODBC1.TEST")
#> [1] 0
dbExecute(con, "CREATE TABLE ODBC1.TEST.TBL (x NUMBER)")
#> [1] 0

# make sure it exists:
dbExistsTable(con, name = Id(catalog = "ODBC1", schema = "TEST", table = "TBL"))
#> [1] TRUE

# create a second database.schema.table
dbExecute(con, "CREATE DATABASE ODBC2")
#> [1] 0
dbExecute(con, "CREATE SCHEMA ODBC2.TEST")
#> [1] 0
dbExecute(con, "CREATE TABLE ODBC2.TEST.TBL (y NUMBER)")
#> [1] 0

dbExistsTable(con, name = Id(catalog = "ODBC2", schema = "TEST", table = "TBL"))
#> [1] TRUE

# drop the table from the first database:
dbRemoveTable(con, name = Id(catalog = "ODBC1", schema = "TEST", table = "TBL"))

# ensure it's removed:
dbExistsTable(con, name = Id(catalog = "ODBC1", schema = "TEST", table = "TBL"))
#> [1] FALSE

# write it back to the same place:
tbl <- data.frame(x = numeric(0))
dbWriteTable(con, name = Id(catalog = "ODBC1", schema = "TEST", table = "TBL"), tbl)

# teardown
dbExecute(con, "DROP DATABASE ODBC1")
#> [1] 0
dbExecute(con, "DROP DATABASE ODBC2")
#> [1] 0

^{Created on 2023-12-21 with reprex v2.0.2}

That said, we definitely have an issue here. Unfortunately, the machinery that handles Id() doesn't know what to do with a "database" argument in this case—the terminology that it expects is "catalog"—but just silently ignores it. If I try to use that "database" key, I can arrive at a result that seems very similar to what you've described since all that odbc sees is the schema entry:

# setup
library(DBI)
library(odbc)

con <- dbConnect(odbc(), dsn = "Snowflake", pwd = Sys.getenv("snowflakePass"))

con
#> <OdbcConnection> Simon@Snowflake
#>   Snowflake Version: 7.44.2

dbExecute(con, "USE ROLE ACCOUNTADMIN")
#> [1] 0
dbExecute(con, "USE WAREHOUSE odbc")
#> [1] 0

# no database currently selected:
dbGetQuery(con, 'SELECT CURRENT_DATABASE()')
#>   CURRENT_DATABASE()
#> 1               <NA>

# create a database.schema.table
dbExecute(con, "CREATE DATABASE ODBC1")
#> [1] 0
dbExecute(con, "CREATE SCHEMA ODBC1.TEST")
#> [1] 0
dbExecute(con, "CREATE TABLE ODBC1.TEST.TBL (x NUMBER)")
#> [1] 0

# make sure it exists:
dbExistsTable(con, name = Id(database = "ODBC1", schema = "TEST", table = "TBL"))
#> [1] TRUE

# out of curiosity, does the thing i haven't made exist?
dbExistsTable(con, name = Id(database = "ODBC2", schema = "TEST", table = "TBL"))
#> [1] TRUE

# create a second database.schema.table
dbExecute(con, "CREATE DATABASE ODBC2")
#> [1] 0
dbExecute(con, "CREATE SCHEMA ODBC2.TEST")
#> [1] 0
dbExecute(con, "CREATE TABLE ODBC2.TEST.TBL (y NUMBER)")
#> [1] 0

dbExistsTable(con, name = Id(database = "ODBC2", schema = "TEST", table = "TBL"))
#> [1] TRUE

# drop the table from the first database:
dbRemoveTable(con, name = Id(database = "ODBC1", schema = "TEST", table = "TBL"))

# ensure it's removed:
dbExistsTable(con, name = Id(database = "ODBC1", schema = "TEST", table = "TBL"))
#> [1] TRUE

# ope, it seems not to have been! just to make sure:
dbGetQuery(con, 'DESCRIBE TABLE ODBC1.TEST.TBL')
#> Error in eval(expr, envir, enclos): nanodbc/nanodbc.cpp:1710: 00000
#> SQL compilation error:
#> Table 'ODBC1.TEST.TBL' does not exist or not authorized.

# okay, it's actually gone... dbExistsTable(), called in the backend of dbWriteTable, doesn't see the `database` field.

# try to write it back to the same place:
tbl <- data.frame(x = numeric(0))
dbWriteTable(con, name = Id(database = "ODBC1", schema = "TEST", table = "TBL"), tbl)
#> Error: Table <Id> database = ODBC1, schema = TEST, table = TBL exists in database, and both overwrite and append are FALSE

# teardown
dbExecute(con, "DROP DATABASE ODBC1")
#> [1] 0
dbExecute(con, "DROP DATABASE ODBC2")
#> [1] 0

^{Created on 2023-12-21 with reprex v2.0.2}

[hadley]

I'm working to resolve this general problem across multiple packages:

• In the next version of DBI, you'll be able to skip naming Id()'s argument so you'd write Id("ODBC1", "TEST", "TBL")`. Obviously if you don't provide names, there's no way to get them wrong.
• In the next version of dbplyr, I'm hoping to add support for I() so you can write con |> tbl(I("ODBC1.TEST.TBL"))

And I think we can do the same in odbc so that you can write (e.g.) dbExistsTable(con, I("ODBC1.TEST.TBL")).

[hadley]

I've turned that problem into a new issue: #708

[eriksquires]

More of a question. Sorry I don't know the guts of this well enough to read the comments accurately. Just want to be sure we understand that the problem isn't JUST about objects in different DB's but also same DB but different schemas. From a consumer's point of view, when we use db[Write|Append]Table() we expect to just use the table name if the DB and schema have been set.

Ty, sorry if this is basic/obvious.

[simonpcouch]

we expect to just use the table name if the DB and schema have been set.

You should be able to do this just fine, yes. :)