1

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?

shs
  • 3,683
  • 1
  • 6
  • 34
user16024709
  • 151
  • 1
  • 12
  • I agree with the answer, you should always close connections when you don't need them anymore, i.e., `dbDisconnect(con1)`. Also, I know nothing about Snowflake but I find it strange that you connect only to a specific schema. In my workflow (with a PostgreSQL database) I typically connect to the database and specify the schema when querying or writing. – Roland Apr 27 '23 at 05:40
  • @Roland Thank you for your comment and reading the question. Actually, I experience the same behavior when I change `db` values and try to connect to different database. I cannot create a table with same name if it exists in other database. I have tried disconnecting the connection with `dbDisconnect` and that didn't work for me. – user16024709 Apr 27 '23 at 05:56
  • @user16024709 were you able to resolve that? I understand that it is might be a very obvious thing to ask, but have you checked if you can write into the table using the same user and role that you use in R, but using SQL in Snowlake UI? if it throws the same ```db.schema2.TEMP does not exist or not authorized.``` error it might suggest that the the ```role``` does not have enough privileges to use or create tables specifically in schema2. – aek May 01 '23 at 14:25
  • @aek I can create the same table with same name using Snowflake UI. Somehow `dbWriteTable` is throwing an error. I feel like I am doing something wrong from my end but can't figure out what. – user16024709 May 02 '23 at 04:01
  • @user16024709 thanks, did you try the steps I suggested in my answer, to ensure that all sessions parameters like role, be, wh etc. are the same in UI and R? – aek May 02 '23 at 04:22

2 Answers2

2

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>';

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

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

  3. 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>;
  1. Try execute the same query you copied, or if it is different then just try to INSERT values into your schema2.TEMP table. Examples.
  2. 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.

  1. 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.

aek
  • 1,370
  • 2
  • 10
  • 14
  • Thank you. `Activity -> Query History` is something that I didn't knew existed and that has helped me debug and understand the issue better. – user16024709 May 02 '23 at 10:14
  • @user16024709 good to hear. I hope you could resolve the issue. Otherwise, feel free to add more details that you have discovered and might find relevant. – aek May 02 '23 at 11:03
0

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.

dsdc
  • 1
  • 1