search

snowflakedb / dplyr-snowflakedb

SnowflakeDB backend for dplyr
Apache License 2.0
65 stars 25 forks source link

Join across different schemas #7

Open saurav2608 opened 6 years ago

saurav2608 commented 6 years ago

Is there a way to join two tables from different schemas? At this time I think one option is to set copy=TRUE in dplyr::left_join. However, copy has serious performance issues.

ZacharyRSmith commented 5 years ago

Using in_schema() works for me:

options(dplyr.jdbc.classpath = "~/Projects/carrothealth_r_package/data/snowflake-jdbc-3.6.21.jar")
library(dplyr)
library(dbplyr)
devtools::load_all()
my_db <- src_snowflakedb(user = "zach_s_admin",
                         password = "REDACTED",
                         account = "REDACTED",
                         opts = list(warehouse = "development_x_small",
                                     db = "WAREHOUSE",
                                     schema = "PUBLIC",
                                     role = "DEV_USERS"))
zips_w <- tbl(my_db, in_schema('WAREHOUSE.GEO', 'ZIPS'))
counties_s <- tbl(my_db, in_schema('STAGING.GEO', 'COUNTIES'))
inner_join(
  zips_w %>% filter(ZIP == 55414),
  counties_s
) %>% show_query()
ZacharyRSmith commented 5 years ago

@saurav2608 for what it's worth, my repo handles the dbplyr::in_schema() for you: https://github.com/ZacharyRSmith/RSnowflake