title: Joining across databases with dbplyr
body: <p>I am working with database tables with dbplyr</p>
  
  <p>I have a local table and want to join it with a large (150m rows) table on the database</p>
  
  <p>The database <code>PRODUCTION</code> is read only </p>
  
  <pre><code># Set up the connection and point to the table
  
  library(odbc); library(dbplyr)    
  
  my_conn_string &lt;- paste("Driver={Teradata};DBCName=teradata2690;DATABASE=PRODUCTION;UID=",
                              t2690_username,";PWD=",t2690_password, sep="")
  
  t2690 &lt;- dbConnect(odbc::odbc(), .connection_string=my_conn_string)
  
  order_line &lt;- tbl(t2690, "order_line") #150m rows
  </code></pre>
  
  <p>I also have a local table, let's call it orders</p>
  
  <pre><code># fill df with random data
  
  orders &lt;- data.frame(matrix(rexp(50), nrow = 100000, ncol = 5))
  
  names(orders) &lt;- c("customer_id", paste0(rep("variable_", 4), 1:4))
  </code></pre>
  
  <p>let's say I wanted to join these two tables, I get the following error:</p>
  
  <pre><code>complete_orders &lt;- orders %&gt;% left_join(order_line)
  
  &gt; Error: `x` and `y` must share the same src, set `copy` = TRUE (may be slow)
  </code></pre>
  
  <p>The issue is, if I were to set <code>copy = TRUE</code>, it would try to download the whole of <code>order_line</code> and my computer would quickly run out of memory</p>
  
  <p>Another option could be to upload the <code>orders</code> table to the database. The issue here is that the <code>PRODUCTION</code> database is read only - I would have to upload to a different database. Trying to copy across databases in dbplyr results in the same error.</p>
  
  <p>The only solution I have found is to upload into the writable database and use sql to join them, which is far from ideal</p>