3.3 Conexión con bases de datos desde R

3.3.1 Introducción a SQL en R

SQL se usa para manipular datos dentro de una base de datos. Si la base de datos no es muy grande se puede cargar toda en un data.frame. No obstante, por escalabilidad y offloading de la carga de trabajo al servidor SGBD utilizaremos SQL.

Existen varios SGBD (SQLite, Microsoft SQL Server, MySQL, PostgreSQL, etc) los cuales comparten el soporte de SQL (en concreto ANSI SQL) aunque cada gestor extiende SQL de formas sutiles buscando minar cierta portabilidad de código (vendor-locking). En efecto, un código SQL desarrollado para SQLite es probable que falle con MySQL aunque tras aplicar ligeras modificaciones ya funcionará. Asimismo el mecanismo de conexión, configuración, rendimiento y operación suele diferir entre SGBD.

A continuación se lista una serie de paquetes utilizados en el acceso a los datos, lo que suele ser el principal esfuerzo a realizar cuando se trabaja con SGBD:

• DBI
• RODBC
• dbConnect
• RSQLite
• RMySQL
• RPostgreSQL

3.3.2 El paquete sqldf

A continuación se presenta una serie de ejercicios con la sintaxis de SQL operando sobre un data.frame con el paquete sqldf. Esto inicialmente no incluye los detalles de conectarse a un SGBD, ni modificar los datos, solamente el uso de SQL para extraer datos con el objetivo de ser analizados en R.

library(sqldf)

sqldf('SELECT age, circumference FROM Orange WHERE Tree = 1 ORDER BY circumference ASC')

##    age circumference
## 1  118            30
## 2  484            58
## 3  664            87
## 4 1004           115
## 5 1231           120
## 6 1372           142
## 7 1582           145

3.3.3 SQL Queries

El comando inicial es SELECT. SQL no es case-sensitive, por lo que esto va a funcionar:

sqldf("SELECT * FROM iris")
sqldf("select * from iris")

pero lo siguiente no va a funcionar (a menos que tengamos un objeto IRIS:

sqldf("SELECT * FROM IRIS")

La sintaxis básica de SELECT es:

SELECT variable1, variable2 FROM data

3.3.3.1 Asterisco/Wildcard

Lo extrae todo

bod2 <- sqldf('SELECT * FROM BOD')

3.3.3.2 Limit

Limita el número de resultados

sqldf('SELECT * FROM iris LIMIT 5')

##   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 1          5.1         3.5          1.4         0.2  setosa
## 2          4.9         3.0          1.4         0.2  setosa
## 3          4.7         3.2          1.3         0.2  setosa
## 4          4.6         3.1          1.5         0.2  setosa
## 5          5.0         3.6          1.4         0.2  setosa

3.3.3.3 Order By

Ordena las variables

ORDER BY var1 {ASC/DESC}, var2 {ASC/DESC}

sqldf("SELECT * FROM Orange ORDER BY age ASC, circumference DESC LIMIT 5")

##   Tree age circumference
## 1    2 118            33
## 2    4 118            32
## 3    1 118            30
## 4    3 118            30
## 5    5 118            30

3.3.3.4 Where

Sentencias condicionales, donde se puede incorporar operadores lógicos AND y OR, expresando el orden de evaluación con paréntesis en caso de ser necesario.

sqldf('SELECT demand FROM BOD WHERE Time < 3')

##   demand
## 1    8.3
## 2   10.3

sqldf('SELECT * FROM rock WHERE (peri > 5000 AND shape < .05) OR perm > 1000')

##   area     peri    shape perm
## 1 5048  941.543 0.328641 1300
## 2 1016  308.642 0.230081 1300
## 3 5605 1145.690 0.464125 1300
## 4 8793 2280.490 0.420477 1300

Y extendiendo su uso con IN o LIKE (es último sólo con %), pudiendo aplicárseles el NOT:

sqldf('SELECT * FROM BOD WHERE Time IN (1,7)')

##   Time demand
## 1    1    8.3
## 2    7   19.8

sqldf('SELECT * FROM BOD WHERE Time NOT IN (1,7)')

##   Time demand
## 1    2   10.3
## 2    3   19.0
## 3    4   16.0
## 4    5   15.6

sqldf('SELECT * FROM chickwts WHERE feed LIKE "%bean" LIMIT 5')

##   weight      feed
## 1    179 horsebean
## 2    160 horsebean
## 3    136 horsebean
## 4    227 horsebean
## 5    217 horsebean

sqldf('SELECT * FROM chickwts WHERE feed NOT LIKE "%bean" LIMIT 5')

##   weight    feed
## 1    309 linseed
## 2    229 linseed
## 3    181 linseed
## 4    141 linseed
## 5    260 linseed