Can a local dataframe be accesses from a SQL chunk in R Markdown

user_3482393 0
Viewed 153

I have run a SQL statements and stored the results in a local data frame called "test", `{sql connection=Prod, output.var="test"}.

Now, I need to access "test" the local data frame in another R SQL chunk, is that possible?


     SELECT COUNT(*) AS 'RecordCount'
         , EMPD
         , Department 

     FROM "test"
     GROUP BY EMPD
         , Department
r sql
1 Answers

Generally no. The SQL database server you've connected to doesn't know anything about your R session, so it can't process your R data.

The sqldf package lets you use SQL on R objects by creating a local database. It's a good solution if you're more comfortable using SQL than R, or if you want to do something that's easier in SQL than in R.

In most cases, the point of running a SQL query and storing the results in R is to use R, not SQL, for the next steps. Your example SQL code can be translated to dplyr like this:

library(dplyr)
count(test, EMPD, Department, name = "RecordCount")

If you need to reference the results in a new SQL query, for example to do a join with another table in the database, the best solution will depend on your use case, what flavor of SQL database you're using, and how big the results are. You may be able to use one big SQL query instead of two small ones, or perhaps write the intermediate results to a temporary table.

user_9030610
Related