R Statistical Programming Using MariaDB as the Background Database
Introduction to R
R is a language and environment for statistical computing and graphics. R provides a wide variety of statistical (linear and nonlinear modelling, classical statistical tests, time-series analysis, classification, clustering, …), graphical techniques (ggplot2), machine learning packages (Keras, TensorFlow) and is highly extensible.
One of R’s strengths is the ease with which well-designed publication-quality plots can be produced, including mathematical symbols and formulae where needed. Great care has been taken over the defaults for the minor design choices in graphics, but the user retains full control.
The R environment
R is an integrated suite of software facilities for data manipulation, calculation, and graphical display. It includes:
• an effective data handling and storage facility,
• a suite of operators for calculations on arrays, in particular matrices,
• a large, coherent, integrated collection of intermediate tools for data analysis,
• graphical facilities for data analysis and display either on-screen or on hardcopy, and
• a well-developed, simple and effective programming language which includes conditionals, loops, user-defined recursive functions and input and output facilities.
Using R with MariaDB
Some basic notions / tips on how to use R along with MariaDB are the following:
A. The recommended R distribution is “Microsoft R Open”: https://mran.microsoft.com/open/
B. The recommended R GUIs are RStudio Desktop, or RStudio Server: https://www.rstudio.com/products/rstudio/
An alternative would be Microsoft Visual Studio 2015 or 2017: https://www.visualstudio.com/vs/rtvs/
“Microsoft R Open” and “MariaDB Server” can be installed either in the same server, or in different servers, as an ODBC communication protocol will be used for the exchange of data between the two environments.
Data Transfer between R and MariaDB
For the transfer of data between MariaDB Server and R Environment, it is recommended R's "ODBC" Package:
- “I is a new R package available on CRAN (Since 2017-02-05), and maintained by RStudio, which is designed to comply with the DBI specification.
- Tutorials on how to use R's "ODBC" package can be found here:
- Setting up ODBC Drivers: https://db.rstudio.com/drivers/
- "ODBC" R Package: https://db.rstudio.com/odbc/#usage
The "ODBC" package requires to have previously installed the MariaDB ODBC connector:
Other Packages: "readr", "RODBC", "RMariaDB"
There are other alternatives for data transfer between R and MariaDB:
- “readr” R package, for writing / reading CSV files. To be used in MariaDB along with “LOAD DATA INFILE”.
- "RODBC" R package: Robust and well-tested (Since 2000-05-24) package which enables data transfer between R and MariaDB by means of an ODBC connector: https://cran.r-project.org/web/packages/RODBC/index.html
- It is slightly slower than RStudio's new "ODBC" package: https://db.rstudio.com/odbc/#benchmarks
For bug report to the RODBC package maintainer, use the following R statement: bug.report(package = "RODBC") A vignette on how to use the RODBC package can be found here: https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf
- “RMariaDB” R library, a modern 'MariaDB' client based on 'Rcpp': https://github.com/rstats-db/RMariaDB
- For installing RMariaDB package on 3.4.1+ execute the following R statement: install.packages("RMariaDB")
- RMariaDB package might require the installation of MariaDB's C Connector, which can be downloaded from: https://downloads.mariadb.org/connector-c/
- “RMySQL” R library is not recommended for not being robust enough when dealing with “big data” and having too many bugs.
R Programming Resources
Two excellent and recommended books for learning how to program in R are the following:
- R Cookbook (O’Reilly Media; Paul Teetor): http://shop.oreilly.com/product/9780596809164.do
- R Graphics Cookbook (O’Reilly Media; Winston Chang): http://shop.oreilly.com/product/0636920023135.do
Books could be acquired directly in O’Reilly Store, or in Amazon, over other vendors.
An extract of both books can be found here: http://www.cookbook-r.com/
Rstudio Cheatsheets are also a recommended and valuable resource: https://www.rstudio.com/resources/cheatsheets/
Along with the following Base R reference card: https://cran.r-project.org/doc/contrib/Short-refcard.pdf
At last, but not least, for searching any R related information, the following web searcher is recommended (Based on Google): http://rseek.org/