在来自两个不同数据库的两个表上使用来自dplyr的anti_join() [英] Using anti_join() from the dplyr on two tables from two different databases

问题描述

我正在从事ETL测试项目，我的需要是比较来自两个不同数据库的两个表之间的数据.为此，我首先使用如下所示的查询下载了整个表.

I am working on a ETL testing project, where my need is to compare data between two tables from two different databases. to do this, I first downloaded entire tables using query like below.

  query_table_a <- paste0("SELECT * FROM   MBR_MEAS (NOLOCK)")
  table_a <- as.data.frame(sqlQuery(cn, query_table_a))

然后，我使用了dplyr中的anti_join().如果两个数据框中的列名都相同，那么我的结果很好.例如(这将返回良好的预期结果)

Then, I used anti_join() from the dplyr. If the column name is same in both data frames, then my result is good. for example(this returns good and expected results)

mismatch_records <- anti_join(table_a, table_b, by="client_id")

但是在另一种情况下，列名已更改(表'c'的列名称为client_id，表'd'的列具有clientid，我不知道该怎么做.我尝试使用合并功能，但没有似乎很有希望.

But in another scenario, column name is changed (table 'c' has column name as client_id and table 'd' has clientid, I couldn't figure out what to do. I tried using merge function but that doesn't seems to be very promising.

merge(x = table_c, y = table_d, by.x ="CLIENT_ID", by.y = "ClientId", all.x = "TRUE")

有什么建议吗?

推荐答案

尝试一下:

mismatch_records <- anti_join(table_c, table_d, by = c("CLIENT_ID" = "ClientId"))

这篇关于在来自两个不同数据库的两个表上使用来自dplyr的anti_join()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！