Refer to existing object inside sqlQuery

General
,
#1

Hi,

I'm a beginner in RStudio, but been looking for the answer for quite a while already.

I'm using RODBC sqlQuery to retrieve data from database with ODBC connection.
However, inside sqlQuery I would like to refer to an existing object (table) in my RStudio project.

Lets say I've loaded data from xlsx file onto RStudio into "table1". I want to join some data to this table using sqlQuery and ODBC from database. How do I go about this? If I just refer to this table as "from table1" inside the SQL query, it says "table or view does not exist".

It feels to me there should be a way to do this. I can't just load the whole table B from database and then join them with R, because the table is huge.

Ray

#2

If you have write permissions you can copy table1 temporarily to the RDBMS so you can join them.

#3

Hi. I don’t unfortunately.
Anyhow I want to believe there must be a simpler solution. This action seems very basic to me.

In WPS (world programming service), for instance, you can specify the source when calling for sql. Like to get data from db I would add a prefix to table name with the db name. Likewise, to get data from temporary tables loaded into wps I would add prefix “work”.

#4

The thing is that the sql query you write in R is executed on the RDBMS not in your R environment and to manipulate both tables at the same time they both need to be on the same environment. There are some implementations that make this transparent to the user by copying the tables in the background either to the RDBMS or locally (like dbplyr) but I'm afraid you have to choose one of the two approaches.

Even with your example a temporary copy is made on the RDBMS environment.

#5

You can use local() to refer to objects in the working environment when interacting with databases in R.

#6

When using dbplyr to perform a join operation, if you set the copy argument to false, I think it embeds the table on the generated sql query itself, that way you don't need to have write permissions. I think this could be a good option as long as you can define the table within the sql query programmatically (like with dbplyr).

Even if I'm wrong with my assumption, there is a way to explicitly tell dbplyr to copy the table in-line.

#7

Thanks, but I can't use local() inside of a sqlQuery, right?

#8

I'm able to use it using dbplyr via DBI and odbc. I don't know if there are any restrictions in using it with other database packages.

#11

Hmm, I'm also doing the same.

I tried something like this:

dataframe2 <- sqlQuery(channel = con_odbc, "
SELECT
*
FROM local .dataframe1
;
")

It returns an error that it can't find said table. Where and how did you place "local" in the query?

#12

I cannot remember where I first found out about it, but this post has an example:

#13

Went through the article, but don't see them using 'local' inside of sqlQuery.
If you want any of your code that work, please share it. Thanks!

#14

I like the idea, but I've tried installing dbplyr for at least an hour now, there is always some errors. I'll get back to it once I'll have regained my patience.

#15

If you are interested, we can also help you with that if you open a separate topic and post the console output you get when you try to install it.

1 Like
#16

I will do that, thanks!

#17

Here is the topic:

#18

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.