Cannot create table with same name as the one in other schema with `DBI::dbWriteTable`

Question

I am facing a strange issue when trying to write a table to a Snowflake database. I cannot create a table with name same as the table in another schema.

library(DBI)

df <- data.frame(col1 = rnorm(5), col2 = rnorm(5))

con1 <- DBI::dbConnect(odbc::odbc(), Server = server, 
        port = 443, Driver = "SnowflakeDSIIDriver", database = "db", 
        Warehouse = "warehouse", role = "role", schema = "schema1", 
        authenticator = "oauth", token = token)

# This creates the table named TEMP with 5 rows
DBI::dbWriteTable(conn = con1,  'TEMP', value = df, append = TRUE) #works
# Repeat the same thing to append 5 more rows 
DBI::dbWriteTable(conn = con1,  'TEMP', value = df, append = TRUE) #works

# Now let's change the schema
con1 <- DBI::dbConnect(odbc::odbc(), Server = server, 
        port = 443, Driver = "SnowflakeDSIIDriver", database = "db", 
        Warehouse = "warehouse", role = "role", schema = "schema2", 
        authenticator = "oauth", token = token)
# Try to write it to TEMP table in schema2
DBI::dbWriteTable(conn = con1,  'TEMP', value = df, append = TRUE) #error

Error in new_result(connection@ptr, statement, immediate) : nanodbc/nanodbc.cpp:1412: 42S02: SQL compilation error: Table 'TEMP' does not exist or not authorized.

# Change the table name to TEMP1
DBI::dbWriteTable(conn = con1,  'TEMP1', value = df, append = TRUE) #works

I have tried to search for this a lot but everything directs me to the issue with writing to non-default schema.

Issue Writing to Non-Default Schema when Table Does Not Have "_" In Name

https://github.com/r-dbi/DBI/issues/181

https://github.com/r-dbi/odbc/issues/197

The solution that they suggest is to use the Id function to differentiate schema and table. I tried that but I still get the same error.

table_id <- Id(schema="schema2", table="TEMP")

DBI::dbWriteTable(conn = con1,  table_id, value = df, append = TRUE)

Error in new_result(connection@ptr, statement, immediate) : nanodbc/nanodbc.cpp:1412: 42S02: SQL compilation error:Table 'db.schema2.TEMP' does not exist or not authorized.

Any suggestion?

Answer 1

Errors like:

SQL compilation error: Table 'TEMP' does not exist or not authorized.

and

SQL compilation error:Table 'db.schema2.TEMP' does not exist or not authorized.

A thrown from Snowflake and might not be related specifically to R, or at least you can exclude this to move forward with the problem.

I suggest the following approach to troubleshoot this kind of issues.

1. Execute your R script to reproduce the error
2. Open Snowflake UI then on the left panel, go to Activity - Query History. If the username that you use in your R connection is different from the one you use in UI, on the top of the screen change the filter ```USER```` to select the R username.
3. It will give you the history of the queries generated by your R script. Find the recent one marked as FAILED.
4. Click on it and below the details you will see SQL Text section with the actual SQL text generated by the driver and the error message, the same that you are seeing in R.
5. Copy the Query ID displayed in the Details section.
6. Use the following query to check the session context that was used by the driver and R:

use role accountadmin;

select query_id, role_name, database_name, schema_name, query_text from snowflake.account_usage.query_history where query_id = '<paste the query id>';

7. Make sure that the role_name, database_name, schema_name, are the ones you expected.

8. If this is the case, then you can go back to step 4. And copy the SQL text of the query.

9. Open a new worksheet in UI, set the session context by use commands

USE ROLE <same role as in R>;
USE DATABASE <db_name>;
USE SCHEMA <schema name>; 
USE WAREHOUSE <warehouse_name>;

10. Try execute the same query you copied, or if it is different then just try to INSERT values into your schema2.TEMP table. Examples.
11. If it throws the same error it might mean that the ROLE does not have required permissions to operate on schema2. You can verify that by Using

Show grants on schema schema2;

and see what your role can do.

12. Since your queries work on schema1 you can use show grants on schema schema1 and see the required permissions. If some of the permissions are missed, you can grant this permissions by using GRANT command, e.g.

GRANT USAGE ON SCHEMA schema2 to role <role>
GRANT CREATE TABLE on schema schema2 to role <role>

doc reference

If it does not help you can always reach out to snowflake support.

Answer 2

It looks like you may need to use dbDisconnect() before recreating con1. You can double check that con1 is changing properly by just checking the contents of con1. It should contain a path to check.