Convert ODBC to dataframe for using R packages

[divchops44]

Hello,

I am trying to find a way to use R packages like dplyr, summarytools in R, but my data is the ODBC format. I am trying to convert the ODBC into dataframes, however facing some issues.

x <- as.data.frame(res)
Error in as.data.frame.default(res):
Cannot coerce class 'structure ("OdbCResult", package = "odbc")' to a data.frame

[pieterjanvc]

@divchops44

ODBC

Hi,

Welcome to the RStudio community!

It is not clear from your post what the exact issues is, but you can start by reading these tips on using ODBC driver within R
https://db.rstudio.com/r-packages/odbc/

If still stuck, please create a reprex. A reprex consists of the minimal code and data needed to recreate the issue/question you're having. You can find instructions how to build and share one here:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

Good luck,
PJ

[edgararuiz]

Hi, the reason is that the res variable contains not the results of the query, but a SQL statement. In order to pull down the actual data from the database, you would have to use the collect() function, that will convert the data transmitted by the DB to R into a tibble. So, you can try: res %>% collect(). Please keep in mind that that operation will bring back everything, so if the result of your query contains 1,000's of rows, that is what you will get. If you have exploring at this time, you can consider using res %>% head(100) %>% collect(), that will bring back first 100 results.

[divchops44]

Thank you edgararuiz,

I understand that res is nothing but obtained from an SQL output from an ODBC database as follows:
res <- dbSendQuery(con, "select * from X.Y.Z")
dbFetch(res)

Is there a way to obtain a data frame from the odbc database X.Y.Z directly ?

Thank you for your suggestion to use res %>% head(100) %>% collect(), however it is giving me the following broad error:
"Error in x[seq_len(n)] : object of type 'S4' is not subsettable"

Thank you and any help is appreciated.

[mikecrobp]

There are 2 ways of reading from ODBC with that package:

  switchdbGetQuery = TRUE
  switchdbGetQuery = FALSE
  
  if (switchdbGetQuery) {
    DataRead = dbGetQuery(dbcon,QueryString)
  } else {
    QueryRS = dbSendQuery(dbcon,QueryString)
    DataRead = dbFetch(QueryRS)
    dbClearResult(QueryRS)
  }

[edgararuiz]

+1 to @mikecrobp 's response, for that you would use dbGetQuery() . In dbplyr, you would use tbl(), as in tbl(con, "xyz"), and that would become your pointer to that table that then you can use to pass more dplyr commands: tbl(con, "xyz") %>% group_by(field1) %>% summarise(x = sum(field2))

[divchops44]

Thank you edgararuiz,

The dbGetQuery() function worked for me. Appreciate your help with this

[divchops44]

Thank you mikecropbp,

The dbGetQuery() function worked for me. Appreciate your help with this