RSS

Monthly Archives: November 2013

RODBC – R Connection with relational database


If your data already stored in any relational database like SQL server, Oracle, MySql. R has a privilege to connect to the relational database (using RODBC package) and pick your data from there itself with quite an ease.

RODBC package developed by Brian Ripley, Michael Lapsley and maintained by Brian Ripley. It allow us to establish connection with different RDBMS available like SQL Server, MySQL, ORACLE, SQL lite and many more and we can fire the same SQL queries that we used to fire on respective databases. To work with RODBC, we need to create an ODBC connection as we usually do with while working with oledb.

Rodbc

                            RODBC Connectivity

ODBC act as a middle layer between database and R. R submit its request to extract data from database through ODBC which later interacts with database and finally through ODBC, data will return back to R. We can create ODBC connection Control Panel -> Administrative Tools -> ODBC. After lunching the ODBC, go to System DSN and Add new ODBC connection with your database after choosing right database driver.

To connect with ODBC with R
channel -> odbcConnect([ODBC Connection name], uid=[user id], pwd=[password])

After establishing connection with ODBC, we can fire any sql query compatible with your native database and store result back in data frame.
Odbc_output <- sqlQuery(channel, “Select * from table_name)

It does not require HDD or external medium to store data to store data but it also has some limitations.

Again, data storage issue with RODBC package. It stores data into RAM and limited data will be stored in memory.

 
Leave a comment

Posted by on November 24, 2013 in RODBC

 

Tags: ,