Use Sparklyr to join tables from 2 different databases

user_7190214 0
Viewed 1687

This is my current way after I invoke a Sparklyr session:

dbGetQuery(sparkContext, "USE DB_1")

df_1 <- tbl(sparkContext, "table_1")

dbGetQuery(sparkContext, "USE DB_2")

df_2 <- tbl(sparkContext, "table_2")

df <- df_1 %>% inner_join(df_2, by = c("col_1" = "col_2"))

nrow(df))

Errors that are I met with: 

"Error: org.apache.spark.sql.AnalysisException: Table or view not found: table_1"

My take is Sparklyr does not (directly) support joining tables from 2 databases. I am wondering if anyone has an elegant solution to this problem

sparklyr r
2 Answers

From the sparklyr book, you can use the dbplyr package to create references to each table:

library(dplyr)
library(dbplyr)
library(sparklyr)
table_1 <- tbl(sc, dbplyr::in_schema("db_1", "table_1"))
table_2 <- tbl(sc, dbplyr::in_schema("db_2", "table_2"))

Then you can do a standard R merge:

df <- merge(table_1, table2, by.x = "col_1", by.x = "col_2")

(I'm doing this right now, but it's taking forever.)

user_153701420
Related