R Statistical Programming Using MariaDB as the Background Database - Source

Revision: 93563
User: Juan Telleria
Date: 2020-03-04 15:52

<<toc title='' layout=standalone>>
== 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, machine learning packages 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
=== R Installation
Some basic notions / tips on how to use R along with MariaDB are the following:

A. The recommended R distribution is “Base R”: [[https://cran.r-project.org/bin/windows/base/|CRAN]]

B. The recommended R GUIs are RStudio Desktop, or RStudio Server: [[https://www.rstudio.com/products/rstudio/|RStudio]]

Alternative GUIs would be:
* RCode (PGM Solutions): [[https://www.pgm-solutions.com/rcode|RCode]].

“R” 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

==== Package: "odbc"
For the transfer of data between MariaDB Server and R Environment, it is recommended R's "odbc"  Package: [[https://cran.r-project.org/web/packages/odbc/index.html|CRAN odbc]]

* “odbc" 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/|DB RStudio Drivers]]
** "odbc" R Package: [[https://db.rstudio.com/odbc/#usage|DB RStudio odbc Usage]]

The "odbc" package requires to have previously installed the MariaDB or MySQL ODBC connector:

* [[https://downloads.mariadb.org/connector-odbc/|MariaDB ODBC Connector]]
* [[https://dev.mysql.com/downloads/connector/odbc/5.3.html|MySQL ODBC Connector]]

For installing the "odbc" package from CRAN, execute in R:
<<code>>
install.packages("odbc")
<</code>>

==== Package: "RMariaDB"
“RMariaDB” R library, is a modern 'MariaDB' client based on 'Rcpp'.

For installing RMariaDB package through CRAN, execute the following R statement:
<<code>>
install.packages("RMariaDB")
<</code>>

And for connecting to MariaDB:
<<code>>
library(RMariaDB)

con <- dbConnect(
  drv = RMariaDB::MariaDB(), 
  username = NULL,
  password = NULL, 
  host = NULL, 
  port = 3306
)

<</code>>

==== Other Packages: "readr", "RODBC"
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|CRAN RODBC]]
**It is slightly slower than RStudio's new "odbc" package (See benchmarks): [[https://db.rstudio.com/odbc/|RStudio odbc]]
**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|RODBC CRAN Vignette]]

=== R Programming Resources
==== A) Programming
Recommended resources for learning how to program in R are the following:

* [[https://rc2e.com/|R Cookbook Second Edition (O’Reilly Media; Paul Teetor; James (JD) Long)]]
* [[https://r-graphics.org/|R Graphics Cookbook Second Edition (O’Reilly Media; Winston Chang)]]
* [[https://r4ds.had.co.nz/|R for Data Science (O’Reilly Media; Garrett Grolemund, Hadley Wickham)]]
* [[https://adv-r.hadley.nz/|Advanced R Second Edition (CRC R Series; Hadley Wickham)]]
* [[https://therinspark.com/|Mastering Spark with R (O'Reilly; Javier Luraschi, Kevin Kuo, Edgar Ruiz)]]
* [[https://r-pkgs.org/|R Packages (Hadley Wickham; O’Reilly)]]

==== B) Statistics
A recommended book for understanding the underlying statistics in the R packages is:
* [[http://shop.oreilly.com/product/0636920048992.do|Practical Statistics for Data Scientists (O’Reilly Media; Peter Bruce, Andrew Bruce)]]

==== C) Cheatsheets: Concept Summary
* Rstudio Cheatsheets are a recommended and valuable resource: [[https://www.rstudio.com/resources/cheatsheets/|RStudio Cheatsheets: Webpage]]

* Along with the following Base R reference card: [[https://cran.r-project.org/doc/contrib/Baggott-refcard-v2.pdf|R Reference Card v2]]

==== D) Search Engine & R Package Spotlight
* Search Engines:
** [[http://rseek.org/|RSeek: For searching any R related information (Based on Google).]]
** [[https://www.rpackages.io/|RPackages: Search and stats for CRAN packages.]]

* Information on new R packages is regularly published in the following websites:
** [[https://www.r-bloggers.com/|R-bloggers]]
** [[https://towardsdatascience.com/|Towards Data Science]]
** [[https://mran.microsoft.com/spotlight|MRAN: Package Spotlight]]

==== E) Statistical / Unsupervised Machine Learning, Deep Learning and Artificial Intelligence
**H2O.AI**

The R Programming language has support for the H2O.ai library ([[https://cran.r-project.org/web/packages/h2o/index.html|h2o]]), which enables to create in-memory multi-cluster GPU powered machine learning models.

For installing H2O.ai through CRAN, execute: 
<<code>>
install.packages("h2o")
<</code>>

* [[https://www.h2o.ai/|H2O.ai: Webpage]]
* [[https://github.com/h2oai/h2o-tutorials/raw/master/training/h2o_algos/h2o_algos_cheat_sheet_04_25_17.pdf|H2O.ai Algorithms: Cheatsheet]]
* [[https://github.com/rstudio/cheatsheets/raw/master/h2o.pdf|h2o R Package Functions: Cheatsheet]]
* [[http://shop.oreilly.com/product/0636920053170.do|Practical Machine Learning with H2O (O'Reilly Media; Darren Cook)]]
* [[http://docs.h2o.ai/h2o/latest-stable/h2o-docs/booklets/RBooklet.pdf|Machine Learning with R and H2O (Mark Landry): Booklet Online Version]]
* [[http://docs.h2o.ai/h2o/latest-stable/h2o-docs/booklets/DeepLearningBooklet.pdf|Deep Learning with H2O: Vignette]]

The following R Statements can be used for importing a MariaDB table to H2O.ai using the R Front End:
* **import_sql_table**: "This function imports a SQL table to H2OFrame in memory".

* **import_sql_select**: "This function imports the SQL table that is the result of the specified SQL query to H2OFrame in memory".
<<code>>
connection_url <- "jdbc:mariadb://172.16.2.178:3306/ingestSQL?&useSSL=false"
username <- "root"
password <- "abc123"

# Whole Table:
table <- "citibike20k"
my_citibike_data <- h2o.import_sql_table(connection_url, table, username, password)

# SELECT Query:
select_query <-  "SELECT  bikeid  FROM citibike20k"
my_citibike_data <- h2o.import_sql_select(connection_url, select_query, username, password)
<</code>>

NOTE: Be sure to start the h2o.jar in the terminal with your downloaded JDBC driver in the classpath:
<<code>>
java -cp <path_to_h2o_jar>:<path_to_jdbc_driver_jar> water.H2OApp
<</code>>

**KERAS**

[[https://cran.r-project.org/web/packages/keras/index.html|R package  keras]] offers an interface to [[https://keras.io|Python's 'Keras']], a high-level neural networks 'API'.

'Keras' was developed with a focus on enabling fast experimentation, supports both convolution based networks and recurrent networks (as well as combinations of the two), and runs seamlessly on both 'CPU' and 'GPU' devices.

* [[https://keras.rstudio.com/|R interface to Keras: Webpage]]
* [[https://www.manning.com/books/deep-learning-with-r|Deep Learning With R (François Chollet with J. J. Allaire, Manning)]]
* [[https://github.com/rstudio/cheatsheets/raw/master/keras.pdf|Keras Rstudio Cheatsheet]]

**R LIBRARIES: CARET**

A book which introduces core Machine Learning concepts:

* [[http://shop.oreilly.com/product/0636920058885.do|Introduction to Machine Learning with R (O'Reilly; Scott Burger)]]

==== F) Text Mining
Documentation on how to perform Text Mining in R can be found in the book "Text Mining With R":

* [[http://tidytextmining.com/|Text Mining With R: A Tidy Approach (O’Reilly Media; Julia Silge and David Robinson): Book Online Version]]

==== G) Shiny Web Apps & RMarkdown Documents

**SHINY WEB APPS**

[[https://cran.r-project.org/web/packages/shiny/index.html|Shiny]] R Package makes it incredibly easy to build interactive web applications with R.

Automatic "reactive" binding between inputs and outputs and extensive prebuilt widgets make it possible to build beautiful, responsive, and powerful applications with minimal effort.

* [[https://shiny.rstudio.com/tutorial/written-tutorial/lesson1/|Shiny Written Tutorials]]
* [[https://github.com/rstudio/cheatsheets/raw/master/shiny.pdf|Shiny R Package Cheatsheet]]

For deploy Shiny Web Applications using Open Source Alternatives, you can either use:
* [[https://cran.r-project.org/web/packages/RInno/index.html|RInno: CRAN Webpage (Windows)]]
* [[https://www.shinyproxy.io/|ShinyProxy: Webpage]]
* [[https://www.rstudio.com/products/shiny/shiny-server/|Shiny Server (Open Source Edition): Webpage]]

**RMARKDOWN DOCUMENTS**

* [[https://bookdown.org/yihui/rmarkdown/|R Markdown: The Definitive Guide (Book).]]
* [[https://github.com/rstudio/cheatsheets/raw/master/rmarkdown-2.0.pdf|R Markdown Cheatsheet.]]

==== H) Advanced R Resources
Some of the most advanced R resources for fully understanding the internals and nuances of the R Programming Language are the following:

* [[https://www.crcpress.com/Chapman--HallCRC-The-R-Series/book-series/crctherser|Chapman & Hall/CRC The R Series: Subject-specific Books]]

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

R Statistical Programming Using MariaDB as the Background Database - Source

Products

Services

Pricing

Resources

About Us

Download MariaDB