The Big R-Book

♣15♣Connecting R to an SQL Database

In this section we will study one example of one RDBMS:MySQL. There are good reasons for this choice, it is widely used as a back-end of many websites, it is fast, reliable, and free. However, there are many databases available. In general, a good starting point is the package RODBC.

MySQL

RODBC

Hint – RODBC

Some databases have their own drivers for R, others not. It might be a good idea to have a look at the package RODBC. It is a package that allows to connect to many databases via the ODBC protocol. ^a

In many cases, one will find specific drivers (or rather APIs) for the DBMS that is being used. These interfaces come in the familiar form of packages. For MySQL or MariaDB, this is RMySQL.

RMySQL

Digression –MariaDB

As mentioned in previous section, MariaDB is – almost – a drop-in replacement for MySQL. There are some differences in the configuration tables, how data is displayed, the command prompt (in MariaDB you will see the name of the database), CTRL+C is to be avoided in the MariaDB client), etc. However, till now, most other things that only rely on APIs such as phpmyadmin and RMySQL will work for both database engines.

With the package RMySQL, it is possible to both connect to MariaDB and MySQL in a convenient way and copy the data to R for further analysis. The basics of the package are to create a connection ...