Working in R - dbReadTable and dbExistsTable claims a database table written using dbWriteTable exists, but table does not exist in database

Question

Goal: be able to conduct SQL queries on a data frame in R.

Approach used: using dbWriteTable to write the table to the database that I would then be able to query on using SQL and join to other tables existing in the DB.

Issue: Seems to execute successfully, but table does not seem to actually exist in the db. Errors thrown when attempting to query table. Details below:

Data frame name: testing_df = 1 column dataframe

channel <- DBI::dbConnect(odbc::odbc(), "data_source_name", uid="user_name", pwd='password')
dbGetQuery(channel,"use role role_name;")
dbGetQuery(channel,"use warehouse warehouse_name;")
dbGetQuery(channel,"use schema schema_name;") 
dbGetQuery(channel,"use database db_name;") 

table_name = Id(database="database_name",schema="schema_name",table="table_testing")

dbWriteTable(conn = channel,
             name = table_name,
             value = testing_df,
             overwrite=TRUE)

dbReadTable(channel,name=table_name)

dbExistsTable(channel,name=table_name)

dbReadTable provides output of data frame expected.

dbExistsTable provides the following output:

> dbExistsTable(channel,name=table_name)
[1] TRUE

Issue: The table cannot be located in the actual database UI, and when running the following in R:

desired_output <- dbGetQuery(channel,sprintf("select * from database_name.schema_name.table_testing;"))

desired_output

I get the following error:

SQL compilation error: Object 'table_testing' does not exist or not authorized.

I am able to check in the database and see that the table actually does not exist.

Question: Does anyone know if dbWriteTable is actually supposed to be writing the table to the database, or if I'm misunderstanding the purpose of dbWriteTable? Any better ways to approach this task?

(1) When doing things like `use role`, this is a *statement* and not a query, so the canonical mechanism should be `dbExecute`, not `dbGetQuery`. Perhaps minor. (2) Can you try `'select * from "database_name"."schema_name"."table_name"'`? [This answer](https://stackoverflow.com/a/62187322/3358272) suggests that your attempt of `database_name.schema_name.table_name` will not work with snowflake (which, to me, seems so anti-sql ...). — r2evans, Aug 03 '21 at 13:36
Another thought: https://community.snowflake.com/s/question/0D50Z00008zPWU8SAO/writing-from-r-to-snowflake suggests executing `"use schema schema_name"`, perhaps there's also a `"use database database_name"` after which you can simply do `"select * from table_name"`? (Disclaimer: I don't use or have access to snowflake, just guessing here.) — r2evans, Aug 03 '21 at 13:37
Apologies for a slow response. @r2evans - I am using dbGetQuery because when I use dbExecute I get the following: > dbExecute(channel,"use warehouse warehouse_name;") [1] 0 Warning message: In new_result(connection@ptr, statement, immediate) : Cancelling previous query The format that I'm querying the table in isn't the underlying issue, since I am able to check the database and see that the table does not exist. — Emily Reed, Aug 13 '21 at 13:36
@r2evans - In response to the second thought, I have added the following lines and rerun the script and the issue remains: dbGetQuery(channel,"use schema schema_name;") dbGetQuery(channel,"use database db_name;") — Emily Reed, Aug 13 '21 at 13:40
*"cancelling previous query"* says that the previous call (not this one) did not properly close out the resultset, it has no bearing on the current statement/query. — r2evans, Aug 13 '21 at 13:46
(Sorry, I'm not familiar enough nor have access to snowflake to be able to help much more, so I think I'm out. Good luck!) — r2evans, Aug 13 '21 at 13:46

Working in R - dbReadTable and dbExistsTable claims a database table written using dbWriteTable exists, but table does not exist in database

0 Answers0