Search code examples
rrstudiodbplyr

Reading Data from a SQL server in RStudio (dplyr)

I am sure this question is very basic, but this is the first time I am using R connected to a server, so a few things still confuse me.

I used ODBC Data Sources on Windows to create a DNS, and used

con <- dbConnect(odbc::odbc(), "TEST_SERVER")

this worked, and now under the connection tab I can see the server, and if I double click I can see the databases and tables that exist in the server. How would I go about reading something inside one of those databases?

For Example, if the database name is db1, and the table name is t1, what is the code needed to read that table into local memory? I would prefer using dbplyr as I am familiar with the syntax. I am just unsure how to refer to a particular database and table after making the connection to the server.

Solution

  • I haven't used dbplyr before, but you can query the database using dbGetQuery.

    test <- dbGetQuery(
  con,
  "SELECT *
   FROM db1.t1
  "
)

    You can also pass the database into the connection string.

    con <- dbConnect(
  drv      = odbc(),
  dsn      = "TEST_SERVER",
  database = "db1"
)

    And then your query would just be "SELECT * FROM t1".

    EDIT: To query the table using dbplyr:

    tbl1 <- tbl(con, "t1")
qry <- tbl1 %>% head() %>% collect()