odbc
odbc copied to clipboard
Published
20 hours ago •
r-dbi
r-dbi
dbWriteTable unable to append data to existing Snowflake table
Issue Description and Expected Result
Related to #480. dbWriteTable returns an error message when appending data to an existing table in Snowflake. Error is specific to the Snowflake ODBC driver (works for SQL Server ODBC driver). dbWriteTable should be able to append data to an existing table with no errors.
Database
Snowflake (ODBC Client 2.24.1)
Reproducible Example
library(tidyverse)
library(odbc)
library(DBI)
snowCon <- dbConnect(odbc(), dsn='snowflake')
snowCon
#> <OdbcConnection> wchiu@Snowflake
#> Database: FINANCEANALYTICS_SB
#> Snowflake Version: 6.23.0
dbWriteTable(snowCon, value=mtcars, name = "t_example_mtcars", row.names=FALSE)
dbWriteTable(snowCon, value=mtcars, name = "t_example_mtcars", row.names=FALSE,
overwrite = FALSE, append = TRUE)
#> Error in new_result(connection@ptr, statement, immediate): nanodbc/nanodbc.cpp:1556: 00000: SQL compilation error:
#> Object 'T_EXAMPLE_MTCARS' already exists.
Created on 2022-07-17 by the reprex package (v0.3.0)
Traceback
traceback()
#> 11: stop(list(message = "nanodbc/nanodbc.cpp:1556: 00000: SQL compilation error:\nObject 'T_EXAMPLE_MTCARS' already exists. ",
#> call = new_result(connection@ptr, statement, immediate),
#> cppstack = NULL))
#> 10: new_result(connection@ptr, statement, immediate)
#> 9: OdbcResult(connection = conn, statement = statement, params = params,
#> immediate = immediate)
#> 8: .local(conn, statement, ...)
#> 7: dbSendStatement(conn, statement, ...)
#> 6: dbSendStatement(conn, statement, ...)
#> 5: dbExecute(conn, sql, immediate = TRUE)
#> 4: dbExecute(conn, sql, immediate = TRUE)
#> 3: .local(conn, name, value, ...)
#> 2: dbWriteTable(snowCon, value = mtcars, name = "t_example_mtcars",
#> row.names = FALSE, overwrite = FALSE, append = TRUE)
#> 1: dbWriteTable(snowCon, value = mtcars, name = "t_example_mtcars",
#> row.names = FALSE, overwrite = FALSE, append = TRUE)
ODBC ini file (scrubbed)
[snowflake]
DESCRIPTION=SNOWFLAKE DW
DRIVER=SNOWFLAKE
SERVER=XXXXXXXX
CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=TRUE
CLIENT_METADATA_USE_SESSION_DATABASE=TRUE
AUTHENTICATOR=SNOWFLAKE_JWT
JWT_TIME_OUT=90
PRIV_KEY_FILE=XXXXXXXX
ROLE=XXXXXXXX
DATABASE=XXXXXXXX
SCHEMA=XXXXXXXX
WAREHOUSE=XXXXXXXX
PRIV_KEY_FILE_PWD=XXXXXXXX
UID=XXXXXXXX
PROXY=XXXXXXXX
NO_PROXY=XXXXXXXX
Session info
devtools::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#> setting value
#> version R version 3.6.3 (2020-02-29)
#> os Red Hat Enterprise Linux
#> system x86_64, linux-gnu
#> ui X11
#> language (EN)
#> collate en_US.UTF-8
#> ctype en_US.UTF-8
#> tz America/Los_Angeles
#> date 2022-07-17
#>
#> ─ Packages ───────────────────────────────────────────────────────────────────
#> package * version date lib source
#> assertthat 0.2.1 2019-03-21 [2] RSPM (R 3.6.3)
#> backports 1.1.10 2020-09-15 [2] RSPM (R 3.6.3)
#> bit 4.0.4 2020-08-04 [2] RSPM (R 3.6.3)
#> bit64 4.0.5 2020-08-30 [2] RSPM (R 3.6.3)
#> blob 1.2.1 2020-01-20 [2] RSPM (R 3.6.3)
#> broom 0.7.0 2020-07-09 [2] RSPM (R 3.6.3)
#> callr 3.7.0 2021-04-20 [2] RSPM (R 3.6.3)
#> cellranger 1.1.0 2016-07-27 [2] RSPM (R 3.6.3)
#> cli 3.1.0 2021-10-27 [2] RSPM (R 3.6.3)
#> colorspace 1.4-1 2019-03-18 [2] RSPM (R 3.6.3)
#> crayon 1.3.4 2017-09-16 [2] RSPM (R 3.6.3)
#> DBI * 1.1.0 2019-12-15 [2] RSPM (R 3.6.3)
#> dbplyr 1.4.4 2020-05-27 [2] RSPM (R 3.6.3)
#> desc 1.2.0 2018-05-01 [2] RSPM (R 3.6.3)
#> devtools 2.3.2 2020-09-18 [2] RSPM (R 3.6.3)
#> digest 0.6.25 2020-02-23 [2] RSPM (R 3.6.3)
#> dplyr * 1.0.2 2020-08-18 [2] RSPM (R 3.6.3)
#> ellipsis 0.3.1 2020-05-15 [2] RSPM (R 3.6.3)
#> evaluate 0.14 2019-05-28 [2] RSPM (R 3.6.3)
#> fastmap 1.1.0 2021-01-25 [2] RSPM (R 3.6.3)
#> forcats * 0.5.0 2020-03-01 [2] RSPM (R 3.6.3)
#> fs 1.5.0 2020-07-31 [2] RSPM (R 3.6.3)
#> generics 0.0.2 2018-11-29 [2] RSPM (R 3.6.3)
#> ggplot2 * 3.3.2 2020-06-19 [2] RSPM (R 3.6.3)
#> glue 1.4.2 2020-08-27 [2] RSPM (R 3.6.3)
#> gtable 0.3.0 2019-03-25 [2] RSPM (R 3.6.3)
#> haven 2.3.1 2020-06-01 [2] RSPM (R 3.6.3)
#> highr 0.8 2019-03-20 [2] RSPM (R 3.6.3)
#> hms 0.5.3 2020-01-08 [2] RSPM (R 3.6.3)
#> htmltools 0.5.2 2021-08-25 [2] RSPM (R 3.6.3)
#> httr 1.4.2 2020-07-20 [2] RSPM (R 3.6.3)
#> jsonlite 1.7.1 2020-09-07 [2] RSPM (R 3.6.3)
#> knitr 1.30 2020-09-22 [2] RSPM (R 3.6.3)
#> lifecycle 0.2.0 2020-03-06 [2] RSPM (R 3.6.3)
#> lubridate 1.7.9 2020-06-08 [2] RSPM (R 3.6.3)
#> magrittr 1.5 2014-11-22 [2] RSPM (R 3.6.3)
#> memoise 1.1.0 2017-04-21 [2] RSPM (R 3.6.3)
#> modelr 0.1.8 2020-05-19 [2] RSPM (R 3.6.3)
#> munsell 0.5.0 2018-06-12 [2] RSPM (R 3.6.3)
#> odbc * 1.2.3 2020-06-18 [2] RSPM (R 3.6.3)
#> pillar 1.4.6 2020-07-10 [2] RSPM (R 3.6.3)
#> pkgbuild 1.1.0 2020-07-13 [2] RSPM (R 3.6.3)
#> pkgconfig 2.0.3 2019-09-22 [2] RSPM (R 3.6.3)
#> pkgload 1.1.0 2020-05-29 [2] RSPM (R 3.6.3)
#> prettyunits 1.1.1 2020-01-24 [2] RSPM (R 3.6.3)
#> processx 3.5.2 2021-04-30 [2] RSPM (R 3.6.3)
#> ps 1.3.4 2020-08-11 [2] RSPM (R 3.6.3)
#> purrr * 0.3.4 2020-04-17 [2] RSPM (R 3.6.3)
#> R6 2.4.1 2019-11-12 [2] RSPM (R 3.6.3)
#> Rcpp 1.0.5 2020-07-06 [2] RSPM (R 3.6.3)
#> readr * 1.3.1 2018-12-21 [2] RSPM (R 3.6.3)
#> readxl 1.3.1 2019-03-13 [2] RSPM (R 3.6.3)
#> remotes 2.2.0 2020-07-21 [2] RSPM (R 3.6.3)
#> reprex 0.3.0 2019-05-16 [2] RSPM (R 3.6.3)
#> rlang 0.4.12 2021-10-18 [2] RSPM (R 3.6.3)
#> rmarkdown 2.11 2021-09-14 [2] RSPM (R 3.6.3)
#> rprojroot 1.3-2 2018-01-03 [2] RSPM (R 3.6.3)
#> rstudioapi 0.11 2020-02-07 [2] RSPM (R 3.6.3)
#> rvest 0.3.6 2020-07-25 [2] RSPM (R 3.6.3)
#> scales 1.1.1 2020-05-11 [2] RSPM (R 3.6.3)
#> sessioninfo 1.1.1 2018-11-05 [2] RSPM (R 3.6.3)
#> stringi 1.5.3 2020-09-09 [2] RSPM (R 3.6.3)
#> stringr * 1.4.0 2019-02-10 [2] RSPM (R 3.6.3)
#> testthat 3.1.0 2021-10-04 [2] RSPM (R 3.6.3)
#> tibble * 3.0.3 2020-07-10 [2] RSPM (R 3.6.3)
#> tidyr * 1.1.2 2020-08-27 [2] RSPM (R 3.6.3)
#> tidyselect 1.1.0 2020-05-11 [2] RSPM (R 3.6.3)
#> tidyverse * 1.3.0 2019-11-21 [2] RSPM (R 3.6.3)
#> usethis 1.6.3 2020-09-17 [2] RSPM (R 3.6.3)
#> vctrs 0.3.4 2020-08-29 [2] RSPM (R 3.6.3)
#> withr 2.4.2 2021-04-18 [2] RSPM (R 3.6.3)
#> xfun 0.28 2021-11-04 [2] RSPM (R 3.6.3)
#> xml2 1.3.2 2020-04-23 [2] RSPM (R 3.6.3)
#> yaml 2.2.1 2020-02-01 [2] RSPM (R 3.6.3)
#>
#> [1] /home/FRB/wchiu/R/x86_64-pc-linux-gnu-library/3.6
#> [2] /opt/R/3.6.3/lib64/R/library
Reprex simplified on 07/17/2022. Traceback added on 07/18/2022. Added odbc.ini details.
Would you mind simplifying your reprex to something like below, and then running it with the reprex package?
library(DBI)
con <- dbConnect(RSQLite::SQLite())
dbWriteTable(con, "mtcars", mtcars)
dbWriteTable(con, "mtcars", mtcars, append = TRUE)
Reprex simplified. The work-around is to replace the second dbWriteTable with dbAppendTable.
I added the traceback manually. Reprex does not appear to support traceback.
Thanks for the issue and reprex, @crossxwill!
I'm unable to reproduce at the moment:
# setup
library(DBI)
library(odbc)
con <- dbConnect(odbc(), dsn = "Snowflake", pwd = Sys.getenv("snowflakePass"))
con
#> <OdbcConnection> Simon@Snowflake
#> Snowflake Version: 7.44.2
# writing
dbWriteTable(con, "mtcars", mtcars, row.names = FALSE)
dbWriteTable(con, "mtcars", mtcars, row.names = FALSE, overwrite = FALSE, append = TRUE)
# no error; check results to confirm
nrow(dbReadTable(con, "mtcars"))
#> [1] 64
Created on 2023-12-21 with reprex v2.0.2
There may have been updates in odbc and/or the Snowflake driver that addressed this issue since it was filed. With the most recent versions of those softwares, do you still see this error?
@simonpcouch I was one of the commenters in #480 and can confirm I am able to write to Snowflake successfully with v1.4.1 of odbc. It also worked with v1.4.0, but not 1.3.5, so something in 1.4.0 must have done the trick. Thank you for the heads up!
