Присоедините столбец из таблицы в SQL Server без прав на запись к data.frame в R с использованием dbplyr.

У меня есть data.frame в R, который содержит парные комбинации идентификаторов генетических последовательностей, сгруппированные по названиям кластеров. Список пар был дедублицирован, и нет самосравнений. Я пытаюсь добавить генетическое расстояние для каждой пары, которое доступно в таблице базы данных Microsoft SQL server, где также есть два столбца идентификаторов. У меня есть строго доступ к базе данных только для чтения (я не могу писать/создавать таблицы) и я взаимодействую с ней с помощью пакета {dbplyr}.

Обычно я просто делал бы left_join() для добавления столбца расстояний, но я не могу это сделать, потому что моя первая таблица с парными идентификаторами находится в среде R, а таблица расстояний на SQL сервере. Таблица расстояний содержит миллионы записей, поэтому импортировать всё это не вариант.

Я могу предоставить список значений для фильтрации, но предоставление моего списка идентификаторов отдельно для Id1 и Id2 недостаточно специфично, так как каждый идентификатор был сопоставлен с каждым другим идентификатором в базе данных, и мне нужны лишь сравнения внутри кластера для конкретной подгруппы кластеров и идентификаторов.

Что мне, кажется, нужно, так это способ фильтрации расстояний, которые мне нужны, из таблицы расстояний на сервере, используя пересечение моих двух списков идентификаторов, но я не знаю, как это сделать. Любые решения с предпочтением dbplyr (или SQL, но это должно быть решение с фильтрацией) будут очень полезны.

Примечание: я читал несколько постов относительно фильтрации по двум столбцам, но все ответы предполагают какой-то тип соединения, что я не могу сделать, потому что мои таблицы находятся в двух разных местах и у меня нет прав на запись в базе данных.

Вот игрушечный пример моей таблицы с парами идентификаторов:

# Загрузить необходимые библиотеки:
library(DBI)
library(dbplyr)
library(tidyverse)

# Пример data.frame в R, содержащий пары идентификаторов для фильтрации:
  idpairs <- data.frame(id = c(1,2,3,4,5,6,7,8,9,10,11,12),
                        cluster = c(rep("A", 4), 
                                    rep("B", 4), 
                                    rep("C", 4))) %>% 
    group_by(cluster) %>% 
    expand(Id1 = id, Id2 = id) %>% 
    filter(Id1 < Id2) %>% 
    ungroup()

Это дает мне следующую таблицу:

> idpairs
# A tibble: 18 × 3
   cluster   Id1   Id2
   <chr>   <dbl> <dbl>
 1 A           1     2
 2 A           1     3
 3 A           1     4
 4 A           2     3
 5 A           2     4
 6 A           3     4
 7 B           5     6
 8 B           5     7
 9 B           5     8
10 B           6     7
11 B           6     8
12 B           7     8
13 C           9    10
14 C           9    11
15 C           9    12
16 C          10    11
17 C          10    12
18 C          11    12

Мое соединение с базой данных Microsoft SQL server называется con и я создал соединение с таблицей расстояний в этой базе данных с помощью dbplyr::tbl(), которое называется distances_tbl. Вот как выглядит distances_tbl:

  # Пример таблицы расстояний (эта на сервере):
  distances_tbl <- data.frame(
    id = sample(seq(6000:7000), size = 100),
    Id1 = sample(seq(1:50), size = 100, replace = TRUE),
    Id2 = sample(seq(1:50), size = 100, replace = TRUE),
    Distance = sample(seq(0:30), size = 100, replace = TRUE)
  )

>  distances_tbl %>% 
+     head(n = 10)
    id Id1 Id2 Distance
1  477  31  34       23
2  261  36  17       14
3  132  47  34        8
4  184  31  36       19
5   24   7  35       19
6   47  27   5       27
7  759  17  38       18
8  670  21  37       19
9  145  12  38        3
10  29  42  14       30

Вы можете видеть в таблице, что много сравнений не подходят мне – либо потому, что они сравнивают идентификаторы, которые вообще не находятся в моем наборе данных, либо один из идентификаторов есть в моих данных, но он парен с другим идентификатором, который не есть в них. Например:

• 1 и 3 актуально (оба идентификатора находятся в моих данных, корректное сравнение, так как оба идентификатора в кластере A)
• 1 и 5 не актуально (оба идентификатора находятся в моих данных, но они в разных кластерах)
• 1 и 37 не актуально (только 1 в моих данных, 37 нет)