Reading Data from a SQL server in RStudio (dplyr)

Question

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.

Fleur De Lys · Accepted Answer · 2019-12-19 18:07:17Z

4

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()

edited Dec 19, 2019 at 18:07

answered Dec 19, 2019 at 17:44

Fleur De Lys

4802 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user10917479 Over a year ago

Your dbplyr query at the end is not actually grabbing the whole table because of the use of head(). It will only query the first 6 rows.

Fleur De Lys Over a year ago

@Adam You're right. I was just using it as an example of dbplyr syntax.

TTS · Accepted Answer · 2019-12-19 18:32:53Z

1

I like to use RODBC-

con <- RODBC::odbcConnect(dsn = 'your_dsn',
                          uid = 'userid',
                          pwd = 'password')

table_output <- RODBC::sqlQuery(con, 'SELECT * FROM Table')

answered Dec 19, 2019 at 18:32

TTS

1,9389 silver badges19 bronze badges

Collectives™ on Stack Overflow

Reading Data from a SQL server in RStudio (dplyr)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related