R语言尝试写入永久表时，copy_to不适用于非默认架构

2ul0zpep 于 2023-10-13 发布在其他

关注(0)|答案(2)|浏览(110)

我尝试使用copy_to将表永久写入SQL Server 2017（即temporary = 0）。它适用于默认模式，但当我指定默认模式以外的模式时，它就不起作用了。我得到一个奇怪的错误消息：
Error: <SQL> 'UPDATE STATISTICS EXISTING_SCHEMA.newTblIris' nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Table 'newTblIris' does not exist.
这很奇怪，因为很明显table不存在。我正在创建一个表。

library(odbc)
library(dbplyr)
library(dplyr)
library(DBI)

con <- dbConnect(odbc(),
                 Driver = "SQL SERVER",
                 Server = "SERVER_NAME",
                 Database = "DB_NAME",
                 UID = "USER_ID",
                 PWD = "PASSWORD")

# works, can write this table to the default schema
copy_to(con, iris, "newTblIris", temporary = FALSE)

# successfully create temporary table in non-default schema
copy_to(con, iris, in_schema("EXISTING_SCHEMA", "newTblIris"))

# does not work and gives above error message
copy_to(con, iris, in_schema("EXISTING_SCHEMA", "newTblIris"), temporary = FALSE)

我已经尝试了“正确”的方法来编写非临时表，遵循RStudio的指导，https：//db.rstudio.com/best-practices/schema/#write-non-temporary-tables，但是当我尝试他们的方法时，我也得到了一个错误。

dbWriteTable(con, SQL("EXISTING_SCHEMA.iris"), iris)

这会产生错误Error: Can't unquote EXISTING_SCHEMA.iris

- Session info --------------------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.5.2 (2018-12-20)
 os       Windows Server >= 2012 x64  
 system   x86_64, mingw32             
 ui       RStudio                     
 language (EN)                        
 collate  English_United States.1252  
 ctype    English_United States.1252  
 tz       America/New_York            
 date     2019-03-05                  

- Packages ------------------------------------------------------------------------------------------------------------------------
 package     * version date       lib source        
 assertthat    0.2.0   2017-04-11 [1] CRAN (R 3.5.2)
 backports     1.1.3   2018-12-14 [1] CRAN (R 3.5.2)
 bit           1.1-14  2018-05-29 [1] CRAN (R 3.5.2)
 bit64         0.9-7   2017-05-08 [1] CRAN (R 3.5.2)
 blob          1.1.1   2018-03-25 [1] CRAN (R 3.5.2)
 callr         3.1.1   2018-12-21 [1] CRAN (R 3.5.2)
 cli           1.0.1   2018-09-25 [1] CRAN (R 3.5.2)
 crayon        1.3.4   2017-09-16 [1] CRAN (R 3.5.2)
 DBI         * 1.0.0   2018-05-02 [1] CRAN (R 3.5.2)
 dbplyr      * 1.3.0   2019-01-09 [1] CRAN (R 3.5.2)
 desc          1.2.0   2018-05-01 [1] CRAN (R 3.5.2)
 devtools      2.0.1   2018-10-26 [1] CRAN (R 3.5.2)
 digest        0.6.18  2018-10-10 [1] CRAN (R 3.5.2)
 dplyr       * 0.8.0.1 2019-02-15 [1] CRAN (R 3.5.2)
 fs            1.2.6   2018-08-23 [1] CRAN (R 3.5.2)
 glue          1.3.0   2018-07-17 [1] CRAN (R 3.5.2)
 hms           0.4.2   2018-03-10 [1] CRAN (R 3.5.2)
 magrittr      1.5     2014-11-22 [1] CRAN (R 3.5.2)
 memoise       1.1.0   2017-04-21 [1] CRAN (R 3.5.2)
 odbc        * 1.1.6   2018-06-09 [1] CRAN (R 3.5.2)
 pillar        1.3.1   2018-12-15 [1] CRAN (R 3.5.2)
 pkgbuild      1.0.2   2018-10-16 [1] CRAN (R 3.5.2)
 pkgconfig     2.0.2   2018-08-16 [1] CRAN (R 3.5.2)
 pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.5.2)
 prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.5.2)
 processx      3.2.1   2018-12-05 [1] CRAN (R 3.5.2)
 ps            1.3.0   2018-12-21 [1] CRAN (R 3.5.2)
 purrr         0.3.0   2019-01-27 [1] CRAN (R 3.5.2)
 R6            2.4.0   2019-02-14 [1] CRAN (R 3.5.2)
 Rcpp          1.0.0   2018-11-07 [1] CRAN (R 3.5.2)
 remotes       2.0.2   2018-10-30 [1] CRAN (R 3.5.2)
 rlang         0.3.1   2019-01-08 [1] CRAN (R 3.5.2)
 rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.5.2)
 sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.5.2)
 tibble        2.0.1   2019-01-12 [1] CRAN (R 3.5.2)
 tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.5.2)
 usethis       1.4.0   2018-08-14 [1] CRAN (R 3.5.2)
 withr         2.1.2   2018-03-15 [1] CRAN (R 3.5.2)

来源：https://stackoverflow.com/questions/55050469/copy-to-does-not-work-with-non-default-schemas-when-trying-to-write-permanent-ta

2条答案

按热度按时间

mu0hgdu01#

我遇到了类似的问题，并制定了以下工作。这是我用来将R表写入数据库的函数的核心（完整的函数可以在我的dbplyr helpers repo中找到）。这个简化版本假设你正在写入的表已经存在。

copy_r_to_sql = function(db_connection, schema, sql_table_name, r_table,
                         named_list_of_columns){

  # trim r table to just variables of interest
  r_table = r_table %>%
    select(names(named_list_of_columns))

  # if column type is character or date, wrap in single quotes
  # so SQL reads it as character string
  for(coln in colnames(r_table)){
    col_type = named_list_of_columns[[coln]]
    of(grepl("char", col_type) | grepl("date", col_type))
    r_table[coln] = apply(r_table[coln], 1, function(x) paste0("'", as.character(x), "'"))
  }

  # SQL
  sql_cols = paste0("([",paste0(names(named_list_of_columns), collapse = "],["), "])")
  sql_values = paste0(apply(r_table, 1, 
                            function(x) paste0("(", paste0(x, collapse = ","),")")),
                      collapse = ",\n")

  my_sql = build_sql(con = db_connection,
                     "INSERT INTO ", sql(schema), ".",sql(sql_table_name),"\n",
                     sql(sql_cols), "\n",
                     "VALUES ", sql(sql_values),";")

  result = dbExecute(db_connection, as.character(my_sql))
}

该函数用于编写将指定行添加到表中的SQL文本。要理解这个函数的作用，你可以用print语句替换最后一行dbExecute。
通过示例函数调用：

named_list_of_columns = list(Sepal.Length = "[float](5,1) NOT NULL",
                             Sepal.Width = "[float](5,1) NOT NULL",
                             Species = "[varchar](15) NOT NULL")

copy_r_to_sql(con, "DB_NAME.SCHEMA", "newTblIris", iris, named_list_of_columns)

我没有试过把它用在大table上。如果您需要加载大量的行，我建议创建一个新表并分批追加到它。

赞(0）回复(0）举报 2023-10-13

xytpbqjk2#

如果你使用DBI::Id()而不是in_schema()，你应该会成功。例如，如果将最后一个copy_to()示例更改为以下内容，则应该可以工作：
copy_to(con, iris, DBI::Id(schema = "EXISTING_SCHEMA", table = "newTblIris"), temporary = FALSE)
仅供参考，请注意DBI::Id()要求您显式命名schema和table参数。你不能像大多数R函数那样，简单地按顺序传递模式名和表名（用逗号分隔），并依赖于参数的隐含顺序。虽然由于这个原因，DBI::Id()比in_schema()编写起来稍微冗长一点，但我发现这种权衡是值得的，因为DBI::Id()在任何地方都能稳定地工作/完全按照我的期望工作，而我注意到在使用in_schema()时，在这些方面与您在问题中详细描述的类似的一些令人惊讶的怪癖。
我知道这个问题在这一点上是相当老的，但我只是想给给予我的答案，希望它可能会帮助某人迅速解决他们的问题，并调整到一个更强大的替代方案，如果任何人再次遇到这样的事情。

赞(0）回复(0）举报 2023-10-13

我来回答

R语言尝试写入永久表时，copy_to不适用于非默认架构

2条答案

相关问题

热门标签

最新问答

R语言 尝试写入永久表时，copy_to不适用于非默认架构

2条答案

相关问题

热门标签

最新问答

R语言尝试写入永久表时，copy_to不适用于非默认架构