SQL in R Markdown
Are you still transferring data from SQL server to R environment through excel spreadsheet or rigorously switching between R studio and Microsoft SQL Server? Then this article might provide you with a spot on solution to the problem.
You can view tables from SQL database, write SQL queries, and store tables as data frame in R environment altogether in R Studio without switching screens. We will utilize R markdown for doing so! This will allow you to run code in chunks and provides a feature to simply copy and paste SQL queries without making any changes. Kindly follow the steps mentioned below :
1. Make sure ODBC Driver for SQL is installed and configured.
You may check this by typing ODBC in the Start menu and by clicking on “ODBC Data Sources”. It will display the drivers installed on your system as per the version(32-bit or 64-bit). If the drivers are not installed or visible you may follow the steps as explained here to configure the driver.
2. Open new or existing R Markdown Script
Kindly open a new or existing R Markdown Script where you would like to introduce the SQL connection feature.
New Script :- R Studio < File < R Markdown
Existing Script :- R Studio < Open < .RMD Script
You may input Title and Author but it can be edited later on in Script. Select the desired output format (HTML, PDF, or Word). When you publish your script after writing code, R markdown provides a report view such as Jupyter notebook in Python as per the desired output format of the author.
You may refer to the R markdown cheat sheet later to play around with chunk options which makes it one of the best tools for automated reporting.
3. Install R library DBI
Insert a new R chunk by clicking on the Insert option and choosing R. In R markdown we have options to run other programming languages within R code.
First, we will install package(“DBI”) to make connection with SQL server.
4. Make connection to SQL Server
Add another R chunk through Insert Option. Define a connection to the server using “dbConnect” function of DBI package.
Enter the ODBC driver name for Driver(In our case SQL Server), SQL Server Name for Server, name of the Database to be accessed inside Server can be entered for Server. When chunk is completed without error, the Connections tab will display the information about all other databases available on the Server as well. On clicking further you can view the tables inside a particular database along with names of columns and their class(eg. nvarchar, float, string, etc.) inside a particular table.
5. Write SQL queries in R
Click on Insert < SQL. SQL chunk will be inserted. You need to define the connection and the name of the output table in which data will be stored in the R environment.
Voila! Once Query is completed in the SQL chunk, the output table will be displayed in R environment.
Conclusion
This is one way of doing this, and it worked for me as I am using only a single library to conduct the activity.
Please let me know in the comments section if it works for someone else or you face any error while following this method.
