How to write an R Data Frame to a Snowflake database table

Question

Does anyone know how to WRITE an R Data Frame to a new Snowflake database table? I have a successful Snowflake ODBC connection created in R, and can successfully query from Snowflake. The connection command is: conn <- DBI::dbConnect(odbc::odbc(), "Snowflake").

Now, I want to WRITE a data frame created in R back to Snowflake as a table. I used the following command: dbWriteTable(conn, "database.schema.tablename", R data frame name). Using this command successfully connects with Snowflake, but I get the following error message: "Error in new_result(connection@ptr, statement) : nanodbc/nanodbc.cpp:1344: 22000: Cannot perform CREATE TABLE. This session does not have a current database. Call 'USE DATABASE', or use a qualified name."

I am using a qualified database name in my "database.schema.tablename" argument in the dbWriteTable function. I don't see how to employ "USE DATABASE" in my R function. Any ideas?? Thank you!!

Answer 1

The API for DBI::dbWriteTable(…) requires passing either the literal table name as a string, or as a properly quoted identifier:

dbWriteTable(conn, name, value, ...)

conn: A DBIConnection object, as returned by dbConnect().

name: A character string specifying the unquoted DBMS table name, or the result of a call to dbQuoteIdentifier().

value: a data.frame (or coercible to data.frame).

dbWriteTable(conn, "database.schema.tablename", R data frame name)

Your code above will attempt to create a table literally named "database.schema.tablename", using the database and schema context associated with the connection object.

For example, if your connection had a database DB and schema SCH set, this would have succeeded in creating a table called DB.SCH."database.schema.tablename".

To define the database, schema and table names properly, use the DBI::Id class object with the right hierarchal order:

table_id <- Id(database="database", schema="schema", table="tablename")
dbWriteTable(conn, table_id, R data frame name)

Behind the scenes, the DBI::dbWriteTable(…) function recognizes the DBI::Id class argument type for name, and converts it into a quoted identifier format via DBI::dbQuoteIdentifier(…) (as a convenience).