When you use R for data analysis, sometimes you have to import data from a Database (e.g. SQL), instead of just import data by reading the csv, excel, or mat file. To do this, we have to do several steps. Good thing Datacamp course provide us step-by-step guidance.
There are 5 steps you need to do if you did not install RMySQL package yet. If you have installed the package, you could skip step 1. In step 2, you need to establish connection with database with dbconnect() function. Then, in step 3 you can list the database tables using dbListTables(), in which three tables are available: users, tweats, and comments. In Step 4, you can import the table and assign into a data frame variable. Last but not least, if you have finish importing data, the polite way must be performed is disconnecting the database using dbDisconnect() function.
You can find the R-script described as follow:
#Step 1, install the RMySQL package (only if you did not install the package) install.packages("RMySQL") #Step 2, Establish a connection library(DBI) con <- dbConnect(RMySQL::MySQL(), dbname = "tweater", host = "courses.csrrinzqubik.us-east-1.rds.amazonaws.com", port = 3306, user = "student", password = "datacamp") #Step 3, List the database tables tables <- dbListTables(con) str(tables) #display structure of tables #Step 4, Import data from a table users <- dbReadTable(con,"users") users #print users tweats <- dbReadTable(con,"tweats") tweats #print tweats comments <- dbReadTable(con,"comments") comments #print comments #Step5, disconnect database dbDisconnect(con)
Hope it helps! ;D