R 使用 dbi 更新数据库

Question

我在 R 中使用过 DBI，第一个问题更多的是最佳实践，因为目前将新数据附加到 DB 花费的时间比我希望的要多。其次是我在尝试更新数据库中的旧信息时收到的错误。这是将新数据插入数据库中现有表时我当前的工作流程:

con <- dbConnect(odbc(), "myDSN")

# Example table 1
tbl1 <- tibble(Key = c("A", "B", "C", "D", "E"),
               Val = c(1, 2, 3, 4, 5))

# Original table in DB
dbWriteTable(con, "tbl1", tbl1, overwrite = TRUE)

# Link to Original table
db_tbl <- tbl(con, in_schema("dbo", "tbl1"))

# New data
tbl2 <- tibble(Key = c("D", "E", "F", "G", "H"),
               val = c(10, 11, 12, 13, 14))

# Write it to Staging
dbWriteTable(con, "tbl1_staging", tbl2, overwrite = TRUE)

# Get a link to staging
db_tblStaging <- tbl(con, in_schema("dbo", "tbl1_staging"))

# Compare Info
not_in_db <- db_tblStaging %>% 
  anti_join(db_tbl, by="Key") %>% 
  collect()

# Append missing info to DB
dbWriteTable(con, "tbl1", not_in_db, append = TRUE)

# Voila!
dbReadTable(con, "tbl1")

那会成功，但我正在寻找更好的解决方案，因为我讨厌代码的 collect() 部分，这意味着我要在 R 内存中添加一些东西(据我了解)当我拥有更大的数据时，将来可能会成为一个问题。我希望能工作的是这样的东西，它允许我快速将新数据附加到数据库，而无需访问内存。

# What I hoped to have
db_tblStaging %>% 
  anti_join(db_tbl, by="Key") %>% 
  dbWriteTable(con, "tbl1", ., append = TRUE)

第二个问题是更新现有表。这是我尝试过的，但是会出现错误并且无法弄清楚。这是我试图复制答案的链接:How to pass data.frame for UPDATE with R DBI .我想用 val 中的新值更新键 E 和 D。

# Trying to update tbl1
update_values <- db_tblStaging %>% 
  semi_join(db_tbl, by="Key") %>% 
  collect()

update <- dbSendQuery(con, 'UPDATE tbl1 
                            SET "val" = ? 
                            WHERE Key = ?')

dbBind(update, update_values)

Error in result_bind(res@ptr, as.list(params)) : 
  nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near the keyword 'Key'. 

包是否以某种方式发生了变化？我无法发现我的语法错误。

Answer 1

考虑在表暂存上传后运行纯 SQL，因为看起来您需要 NOT EXISTS(以避免重复)和 UPDATE INNER JOIN(用于现有记录)。这避免了任何 R 客户端查询导入和导出。

和Key是 SQL Server 中的保留字。因此，用方括号将其转义。

apn_sql <- "INSERT INTO dbo.tbl (s.[Key], s.[Val])
            SELECT s.[Key], s.[Val] FROM dbo.tbl_staging s
            WHERE NOT EXISTS
              (SELECT 1 FROM dbo.tbl t
               WHERE t.[Key] = s.[Key])"

dbSendQuery(con, apn_sql)

upd_sql <- "UPDATE t
            SET t.Val = s.Val
            FROM dbo.tbl t 
            INNER JOIN dbo.tbl_staging s 
               ON t.[Key] = s.[Key]"

dbSendQuery(con, upd_sql)

Rextester demo

事实上，SQL Server 有MERGE在一个调用中处理这两个问题的查询:

MERGE dbo.tbl AS Target
USING (SELECT [Key], [Val] FROM dbo.tbl_staging) AS Source
  ON (Target.[Key] = Source.[Key])
WHEN MATCHED THEN
    UPDATE SET Target.Val = Source.Val
WHEN NOT MATCHED BY TARGET THEN
    INSERT ([Key], [Val])
    VALUES (Source.[Key], Source.[Val]);

Rextester demo