Hello everyone, today we are going to talk about how to import records from SQL Server to RStudio. For this, we will make use of the RODBC package. As its name indicates, it works through ODBC, so we must first configure it. Let us begin.
What do we need to import logs from SQL Server to RStudio?
Before we begin, we must have the following components installed:
- R programming language installed (required to install RStudio)
- RStudio
- Connection data with SQL Server
Once everything is installed, we can start configuring our ODBC connection, which will act as an intermediary between R and SQL Server.
ODBC Configuration
First of all, we will have to establish the ODBC connection (64 bits) with our database. In our case we will use SQL Server 2019 with the AdventureWorks test database. We will have to select “SQL Sever Native Client 11.0”.
Once you have filled in the connection data that you are asking for on later screens. A button will appear to test the connection. If the following appears, it will have been done correctly and you can click Finish.
RODBC installation and configuration
Once the ODBC connection is configured, we will have to go to RStudio to install “RODBC”. A package, which will allow us to execute SQL Server statements from within R. This will be done via the ODBC connection we just created.
Once inside RStudio we execute the following to install the package:
install.packages("RODBC")
And later we will activate it with the following command:
library(RODBC)
Import SQL Server logs with RODBC
After this we will be able to perform SQL Server queries using RStudio. For example:
require(RODBC) #open the ODBC connection ch
We hope that this post will be useful to you. Do not hesitate to subscribe to our monthly newsletter if you want to be up to date with all our publications from the world of databases and more. With a single email a month you will be informed.
See you in next innings.
Sources:
https://hablamosr.blogspot.com/2020/10/rodbc-importar-registros-sqlserver.html
https://gist.github.com/tgh0831/6379253fd6d82671005127a843456aaa
https://stackoverflow.com/questions/5720508/sql-server-rodbc-connection