CODEWITHSUNDEEP
rdplyrsnowflake-cloud-data-platform
Jon M
Jon M

Reputation: 164

Inserting data into snowflake table from R

My company recently switched a table from Oracle to Snowflake, and I'm trying to make an R script which inserted data to the old Oracle database insert data into snowflake. But I haven't been able to get the dplyr.snowflakedb library to do this. Here's my code:

library(dplyr.snowflakedb)

db <- src_snowflakedb(user = credentials$login,
                      password = credentials$password,
                      account = "company",
                      opts = list(warehouse = "WH",
                                  db = "DB",
                                  schema = "STAGE"))

# calculate dataframe "rates"

db_snowflake_copy(con = db$con, from = rates, to = "FORECASTS")

causes the following error:

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for COPY INTO FORECASTS FROM c(1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 
1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 1540312565.33151, 15403

using the example from ?db_snowflake_copy, I decided to try writing the data to a csv file and submitting it to the database from the csv:

tmp <- tempfile()
write_csv(rates, tmp, col_names = FALSE)

db_snowflake_copy(con = db$con, from = paste0("file://",tmp), to = "FORECASTS",
                  format_opts = list(format = 'csv', field_delimiter = ','))

unlink(tmp)

which gives the following error:

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for COPY INTO FORECASTS FROM file:///var/folders/y1/7zsj8_x973n4xjwg5gg6s53jr4zz65/T//RtmpZ9D3dD/file2ce511189e2 file_format = (format=csv, field_delimiter=,) (SQL compilation error:
syntax error line 1 at position 152 unexpected ','.
syntax error line 1 at position 153 unexpected ')'.)

Any ideas on what to try next?

Upvotes: 1

Views: 2559

Answers (1)

Minzhen Yang
Minzhen Yang

Reputation: 26

Not sure which dplyr version you are using. If your account is on west region, you can use dplyr 0.4.3 and dplyr-snowflakedb 0.1.1 combination to make COPY working.

Latest version Combination dplyr 0.7.3 + dplyr-snowflakedb 0.3.0 works with region parm (for west region or outside of west region).

Instructions of testing new driver with east region in R console are as follows (in R):

  1. install.packages("devtools")

  2. devtools::install_version("dplyr", version = "0.7.3", repos = "http://cran.us.r-project.org")

  3. devtools::install_github("snowflakedb/dplyr-snowflakedb", ref = "v0.3.0-rc1.1")

  4. testing:

library(RJDBC) library(dplyr) library(dplyr.snowflakedb)

options(dplyr.jdbc.classpath = "/Users/myang/driver/snowflake-jdbc-3.0.9.jar")
my_db <- src_snowflakedb(user = "youruserid",
         password = "xxxxx",
         account = "lflk_merkle",
         region_id = "us-east-1",
         opts = list(warehouse = "lflk_merkle",
                   db = "LFLK",
                   schema = "DEV"))    
dplyr::copy_to works for COPY.

Upvotes: 1

Related Questions