CODEWITHSUNDEEP
rsnowflake-cloud-data-platform
Chuck
Chuck

Reputation: 53

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

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!!

Upvotes: 4

Views: 5860

Answers (2)

user71216
user71216

Reputation: 63

For people coming to this now (2023), the method in the other answer no longer works. The current solution is to simply wrap your table's name in SQL from the DBI package. If df is your local dataframe and you want to create a table named database.schema.tablename, it would look like this:

dbWriteTable(conn, SQL("database.schema.tablename"), df)

Upvotes: 1

user13472370
user13472370

Reputation:

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

Upvotes: 8

Related Questions