Import logs from SQL Server to RStudio

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.

import sql server logs with odbc

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”.

Import SQL Server logs with RODBC

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

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