如何使用DBI::dbConnect()从多个数据库读取和写入表
如何使用DBI::dbConnect()从多个数据库读取和写入表
提问于 2020-03-09 16:58:28
我有一个使用DBI::dbConnect连接到的Netezza服务器。服务器有多个数据库，我们将命名为db1和db2。
我希望尽可能多地使用dbplyr，并跳过必须在RODBC::sqlQuery()中编写SQL代码，但我不知道如何执行以下操作：
1)如何在db1中读取表，如何在不遍历桌面的情况下让服务器将结果写入db2中的表中？
2)如何在db1中的表和db2中的另一个表之间进行左连接？
看起来可能有一种方法可以连接到数据库="SYSTEM“，而不是数据库= "db1”或"db2"，但我不确定下一步会是什么。
con <- dbConnect(odbc::odbc(),
driver = "NetezzaSQL",
database = "SYSTEM",
uid = Sys.getenv("netezza_username"),
pwd = Sys.getenv("netezza_password"),
server = "NETEZZA_SERVER",
port = 5480)回答 1
Stack Overflow用户
发布于 2020-03-09 22:04:42
我在SQL server上使用in_schema和dbExecute解决这个问题，如下所示。假设Netezza没有太大的不同。
第1部分:共享连接
第一个问题是通过相同的连接连接到两个表。如果使用不同的连接，那么连接两个表会导致数据从一个连接复制到另一个连接，这是非常慢的。
con <- dbConnect(...) # as required by your database
table_1 <- dplyr::tbl(con, from = dbplyr::in_schema("db1", "table_name_1"))
table_2 <- dplyr::tbl(con, from = dbplyr::in_schema("db2.schema2", "table_name_2"))虽然in_schema用于传递模式名称，但您也可以使用它传递数据库名称(或者两者之间都有一个点)。
下面的工作现在应该是没有问题的：
# check connection
head(table_1)
head(table_2)
# test join code
left_join(table_1, table_2, by = "id") %>% show_query()
# check left join
left_join(table_1, table_2, by = "id") %>% head()第2部分:写入数据库
远程表由两件事定义
- 连接
- 当前查询的代码(例如
show_query的结果)
我们可以使用这些与dbExecute一起写入数据库。我的例子是server (它使用INTO作为关键字，如果SQL语法不同，您必须适应自己的环境)。
# optional, extract connection from table-to-save
con <- table_to_save$src$con
# SQL query
sql_query <- paste0("SELECT *\n",
"INTO db1.new_table \n", # the database and name you want to save
"FROM (\n",
dbplyr::sql_render(table_to_save),
"\n) subquery_alias")
# run query
dbExecute(con, as.character(sql_query))这样做的目的是创建一个查询，该查询可以由将写入新表的数据库执行。我是通过将现有查询作为SELECT ... INTO ... FROM (...) subquery_alias模式的子查询来实现这一点的。
备注：
- 如果
show_query或sql_render生成的sql查询在直接访问数据库时能够工作，那么上面的操作就会正常工作(所有更改都是通过R而不是通过sql到达的)。 - 我为使这一过程顺利进行而编写的函数可以在这里上找到。它们还包括附加、删除、压缩、索引和处理视图。
- 如果数据库中已经存在表，则通过
dbExecute编写表将出错，因此我建议首先检查该表。 - 我在其他地方使用这个工作，但是用
in_schema插入数据库名并不适用于创建视图。要创建(或删除)视图，我必须确保连接到我想要的视图所在的数据库。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/60605192
复制相关文章
相似问题
腾讯云开发者
