Summary

This document is to provide steps for R & SQL integration. MySQL database is used for this setup.

Setup MySQL instance

MySQL Server 8.0 setup


Setup MySQL Server with default settings. Remember to copy root user/password for later uses.

MySQL Server Workbench setup


MySQL components after MySQL components and Workbench install

Path update

Edit Windows path to add add MySQL executable
(Windows-> System Properties-> Environment Variables -> Path ->Edit)

Firewall / VPN rule updates

Whitelist (Allow) port : 3306

  • Add inbound and outbound rules to allow network traffic at port 3306

Goto : Control Panel->All Control Panel Items->Windows Defender Firewall -> New Inbound rules
Keep TCP selected, Allow, enter port 3006 -> Finish




Goto : Control Panel->All Control Panel Items->Windows Defender Firewall -> Out Outbound rules
Keep TCP selected, Allow, enter port 3006 -> Finish
Right click on these rules and select enable to activate these rules in the list

Make ProgramData visible

On windows Explorer: View -> Hidden Items (checked)

Edit my.ini for Secure File Priv option

Go to : C:\ProgramData\MySQL\MySQL Server 8.0 Find a line with ‘secure-file-priv=’. Update if import/export data and .SQL file location is different

Connect to local MySQL Server

Start MySQL Workbench from start menu


* Goto manage connections to setup a new database connection


* Enter database server, user and password details



* Schema view should look like this image


* Select scripts to create schema, tables and load data
or you can create schema and tables in workbench UI

* Validate schema


* Validate tables and data

R and MySQL Integration

Password less code - R is connected to MySQL database using RMySQL Database Interface driver. While running R Markdown, database user and password is asked.

Connect to local database

Using provided database user and password, a connection to MySQL Database is made. Database query executed to select movie, users and rating data set

library(RMySQL)
## Loading required package: DBI
user <- rstudioapi::askForPassword("Database username")
password <- rstudioapi::askForPassword("Database password")
host  ='localhost'
#user  ='root'
#password='root'

connection = dbConnect(MySQL(), user = user, password =password, dbname = 'movie_ratings', host = host)
sqlQuery='SELECT  usr.user_name, mv.movie_name, rt.rating FROM movie_ratings.user_movie_rating umr JOIN movies mv ON mv.movieId=umr.movieId JOIN users usr ON usr.userId=umr.userId JOIN ratings rt ON rt.rating=umr.rating'
result = dbSendQuery(connection, sqlQuery)
data.frame = fetch(result)
head(data.frame)
##   user_name             movie_name rating
## 1    Albert                Alladin      1
## 2    Albert       The Midnight Sky      1
## 3    Albert Avengers: Infinity War      1
## 4     Andoh                Alladin      1
## 5     Andoh       The Midnight Sky      1
## 6     Andoh         Space Sweepers      1

Connect to AWS RDS MySQL

  • Login / Create account on AWS

  • On AWS Management Console -> select RDS -> Create Database

  • RDS -> Databases -> Movies > Update VPC

  • public available








Connect to AWS RDS database

Using provided database user and password, a connection to MySQL Database is made. Database query executed to select movie, users and rating data set

library(RMySQL)

host  ='msds.cbs1lxtno2zh.us-east-2.rds.amazonaws.com'
user  ='AppUser'
password='!PassAppUSer'
#Above user and password can be saved as environment variable. 
#Also there is point to point security between RDS MySQL instance and local R Studio so RDS MySQL can not be misused 

connection = dbConnect(MySQL(), user = user, password =password, dbname = 'movie_ratings', host = host)
sqlQuery='SELECT  usr.user_name, mv.movie_name, rt.rating FROM movie_ratings.user_movie_rating umr JOIN movies mv ON mv.movieId=umr.movieId JOIN users usr ON usr.userId=umr.userId JOIN ratings rt ON rt.rating=umr.rating'
result = dbSendQuery(connection, sqlQuery)
data.frame = fetch(result)
head(data.frame)
##   user_name             movie_name rating
## 1    Albert                Alladin      1
## 2    Albert       The Midnight Sky      1
## 3    Albert Avengers: Infinity War      1
## 4     Andoh                Alladin      1
## 5     Andoh       The Midnight Sky      1
## 6     Andoh         Space Sweepers      1

Connect to Google Cloud MySQL

library(RMySQL)

host  ='35.224.102.212'
user  ='AppUser'
password='!PassAppUSer'
#Above user and password can be saved as environment variable. 
#Also there is point to point security between RDS MySQL instance and local R Studio so RDS MySQL can not be misused 

connection = dbConnect(MySQL(), user = user, password =password, dbname = 'movie_ratings', host = host)
sqlQuery='SELECT  usr.user_name, mv.movie_name, rt.rating FROM movie_ratings.user_movie_rating umr JOIN movies mv ON mv.movieId=umr.movieId JOIN users usr ON usr.userId=umr.userId JOIN ratings rt ON rt.rating=umr.rating'
result = dbSendQuery(connection, sqlQuery)
data.frame = fetch(result)
head(data.frame)
##   user_name             movie_name rating
## 1    Albert                Alladin      1
## 2    Albert       The Midnight Sky      1
## 3    Albert Avengers: Infinity War      1
## 4     Andoh                Alladin      1
## 5     Andoh       The Midnight Sky      1
## 6     Andoh         Space Sweepers      1

Create application user

  • Open a command prompt On local machine (Laptop) and type mysql -h <your-AWS-Instance.c4smsi3zs1gt61hm.ap-southeast-1.rds.amazonaws.com> -u -p


  • CREATE USER ‘UserName’@‘%’ IDENTIFIED BY ‘Password’;


* Grant permissions to the user mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, LOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON . TO devops_user@‘%’ with grant option;