This week is our final week of classes, notes and exercises. We will have a look at using R to connect to and interact with a Database. You can use the same Oracle schema you have been using for your SQL work and lab exercises. Using R you will connect to your schema and run various R commands to query, extract and process data in your Oracle schema. There are many different R packages available for this and we will be using RJDBC.
The second part of this week we will look at how some Database vendors have taken the R language and embedded it into the Database. Our examples will be Oracle Database. See below for the notes and a video that covers most of the notes and a code demonstration of using R to run R code in the Oracle Database and then to use SQL to call R code. Some of the other enterprise level Database vendors also provide similar functionality.
Once you have completed the exercises for this week, you can concentrate on completing the Assignment for this part of the module.
Click here to download the R & Databases notes.L5-1-R_and_Databases
Click here to download the Running R in the Database notes.L5-2-R_in_the_Database
This is a video covering most of the notes from Running R in the Database. It also includes a code demonstration.
This lab exercise looks at using R and SQL together.
Task 1 – Setup and creating a connection to your Oracle schema
Download the Oracle JDBC driver and save this file to a directory on the search Path.
Using your Oracle schema name, password and the following information create a database connection using R.
Schema Name = <assigned in class>
Passsword = <Student Number>
Host = redwood.ict.ad.dit.ie
Port = 1521
Service Name = pdb12c.ict.ad.dit.ie
Test your connection
Task 2 – Explore the objects in your Oracle schema
Write some R & SQL code to extract the following information from your schema. The details are to be stored in an R dataframe and then the contains displayed.
Listing of all Tables in your schema
Listing of all Views in your schema
Listing of all Indexes in your schema
Task 3 – Extracting data – Part 1
Create an R dataframe for the tables DEPT and EMP.
Write SQL code to extract the following requests into separate R dataframes.
List the employee details and the name of their department location
List the total number of employees at each department location name
List the employees who have the job title of ‘MANAGER’
List the Minimum, Maximum and Average salaries where job title is ‘MANAGER’
Task 4 – Extracting data – Part 2
4-1 : Create an R dataframe for the table called MINING_DATA_BUILD_V.
Create an R dataframe that contains some summary statistics on this data set.
4-2 : Create an R dataframe for the table called MINING_DATA_BUILD_V, and extract the data in this table in chunks of 400 records until all the data has been extracted
Task 5 – Saving data to your Oracle Schema
Save the R dataframe created in Task 4 above, and save it to the database.
Take some of the data sets you have gathered for you R assignment and save them as a table in the database.
Task 6 – Don’t forget to disconnect
Don’t forget to disconnect from your database connection and to release any resources.