1 Introducción

Hasta ahora, se ha tratado con pequeños conjuntos de datos que caben fácilmente en la memoria de su computadora. Pero ¿qué pasa con los conjuntos de datos que son demasiado grandes para que su computadora los maneje en conjunto? En este caso, es útil almacenar los datos fuera de R y organizarlos en una base de datos o bien conectarse a base de datos publicas o privadas, donde recupere solo los fragmentos necesarios para el análisis actual.

Puede un softare como R conectarse a esas bases de datos ?, será que existen paquetes que se pueden utilizar para realizar consultas, cúales serían unas buenas prácticas para realizar consultas adecuadamente?

Estas preguntas y más, son la guía para el desarrollo de este tema.

2 Objetivos

  • Identificar la infraestructura de conectividad de aquellas bases de datos más comunes en el mercado de acuerdo al cuadrante mágico de Gardner.

  • Identificar los paquetes más utilizados para acceder a la bases de datos desde R.

  • Conocer el procedimiento para la conexión de SQL desde R studio.

  • Conocer las buenas prácticas para establecer una adecuada conexión de BD.

  • Realizar un caso práctico: Video Caso y Ejercicio práctico

3 Infraestructura de conectividad

Existen diferentes interfaces y sistemas de conectividad que permiten la ejecución de operaciones sobre bases de datos desde el lenguaje de programación, independientemente del sistema operativo donde se ejecute o de la base de datos a la cual se esté accediendo. El objetivo es acceder a cualquier dato desde cualquier aplicación, sin importar qué sistema de gestión de bases de datos almacene los datos. Sin embargo, según el tipo de base de datos, su infraestructura es distinta.

3.1 Opensource

El centro de este enfoque es el paquete DBI. Este paquete actúa como middle-ware entre los paquetes para permitir la conectividad con la base de datos del usuario u otros paquetes. A su vez proporciona un conjunto coherente de funciones independientemente del tipo de base de datos al que se accede. El paquete dplyr depende del paquete DBI para la comunicación con las bases de datos. Actualmente, dichos paquetes existen para las siguientes bases de datos: MySQL, SQLite, PostgreSQL y bigquery.

3.2 Comercial source

La mayoría de las bases de datos comerciales, como Oracle y Microsoft SQL Server, ofrecen controladores *ODBC que le permiten conectar su herramienta a la base de datos. Aunque hay paquetes R que le permiten usar controladores ODBC, la conexión probablemente no sea compatible con DBI. El nuevo paquete odbc resuelve ese problema al proporcionar un backend DBI a cualquier conexión de controlador ODBC.

4 Paquetes de R para realizar consultas

Para que un proyecto sea manejable, es importante tener una gestión no solo del código R, pero también de los datos. A lo largo de cualquier proyecto de análisis van a ocurrir correcciones, modificaciones y actualizaciones. Es posible manejar datos en archivos, pero con un poco de complejidad en los datos o en los cambios, rápidamente se vuelve inmanejable.

4.1 DBI

El paquete DBI (Data Base Interface) define una interfaz común entre R y los sistemas de administración de bases de datos (DBMS) que permite que los paquetes puedan acceder a las bases de datos. En otras palabras permite que el coódigo R pueda conectarse a distintas bases de datos para poder manipular, crear sentencias en el DBMS, extraer resultados entre otras funciones.

Estructura básica

Hay cuatro principales clases que deben incluirse en la estructura para el inicio de la conexión:

*DBIObject :Creación de un objeto base para estructura.

*DBIDriver: Se instala un backend específico para la base de datos a la que desea conectarse como: RSQLite(), RPostgreSQL(), RMySQL() etc.

*DBIConnection:representa la conexión para una base de datos especifíca

*DBIResult: El resultado de una consulta proveniente del DBMS.

Ejemplo de conexión a SQLlite

#library(DBI)
#conexion <- dbConnect(RSQLite::SQLite(), dbname = "mi_bd")
##dbListTables(conexion)
#resultado <- dbSendQuery(conexion, "SELECT * FROM mi_bd")

4.2 dplyr

Con la conexión del sistema DBI, dplyrtiene acceso a las bases de datos más comunes : SQLite, PostgreSQL, MySQL. Lo importante es que ya no se necesitan ingresar búsquedas en SQL, si no que se puede usar una sintaxis mucho más consistente con el lenguaje R.

En las expresiones dplyr: filter (filtrar condicionalmente), arrange (ordenar), select (seleccionar variables / columnas) y summarize (resumir agregando filas o columnas) entre otros, son ultilizadas para manipular las tablas de la BD. Una combinación de verbos hace posible crear búsquedas que serían complejas y difíciles de leer en SQL de forma rápida y legible.

Comparación de comando R- SQL syntax

#*select() -> SELECT
#*mutate() -> user-defined columns
#*summarize() -> aggregated columns
#*left_join() -> LEFT JOIN
#*filter() -> WHERE
#*group_by() -> GROUP BY
#*filter() -> HAVING
#*arrange() -> ORDER BY

4.3 odbc

El objetivo del paquete odbc es proporcionar una interfaz compatible con DBI para los controladores de Open Database Connectivity (ODBC). Esto permite una conexión eficiente y fácil de configurar a cualquier base de datos con controladores ODBC disponibles,incluyendo SQL Server, Oracle, MySQL, PostgreSQL, SQLite y otros.Read.

4.4 dbplot

Al tener dplyr se puede procesar los cálculos de un gráfico dentro de una base de datos. Este paquete proporciona funciones para la visualización gráfico de los datos.

Histogrmas

Histogrmas

boxplot

boxplot

line plot

line plot

5 Video caso 1: Preparación para la conexión de SQL server

5.1 Configuración de parámetros de Sql Server

[]()

5.2 Ejemplo Práctico 1

###
##Una vez completados los pasos desde el escritrio debemos 

##instalar el paquete RODBC

#install.packages("RODBC")

##Llamamos la libreria##

#library(RODBC)

##Creamos un objeto asociando la base previamente seleccionada

##Usando el comando odbcConnect

#database <- odbcConnect ("Prueba")

##Podemos comprobar la conexion a traves de un SQL Fetch ##

#sqlFetch(database, "Agents")

6 Ejercicio práctico 2 :

Objetivo: Crear una base de datos SQLite en memoria de forma que se pueda mostrar de forma general, los elementos vistos hasta el momento, con el fin de utilizar ciertos paquetes de R para establecer consultas.

#  Instalación de librerías
install.packages(c("tidyverse", "RSQLite", "nycflights13"))
## Error in contrib.url(repos, "source"): trying to use CRAN without setting a mirror
library(tidyverse)
## ── Attaching packages ───────────────────────────────────────────────────────────────────────────── tidyverse 1.2.1 ──
## ✔ ggplot2 3.1.1     ✔ purrr   0.3.1
## ✔ tibble  2.1.1     ✔ dplyr   0.8.1
## ✔ tidyr   0.8.3     ✔ stringr 1.4.0
## ✔ readr   1.3.1     ✔ forcats 0.4.0
## ── Conflicts ──────────────────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()
library(DBI)
library(nycflights13)
  • Creación de una base de datos SqLite en memoria.
# Se conecta con el comando de dbconnect y se especifica el driver según la base de datos que se utiliza, en este caso SQLite.
con <- dbConnect(RSQLite::SQLite(), ":memory:")
con
## <SQLiteConnection>
##   Path: :memory:
##   Extensions: TRUE
  • Hasta el momento hemos creado una base de datos. Ahora llenemos las tablas con información.
dbWriteTable(con, "flights", nycflights13::flights)
dbWriteTable(con, "planes", nycflights13::planes)
dbWriteTable(con, "airlines", nycflights13::airlines)

Consultas Básicas

Usando el lenguaje SQL

##Pasamos las "queries" de SQL as character string usando dbGetQuery.
# Selecionaremos los vuelos que llegan más de 20 minutos tardes. 

delays <- dbGetQuery(con, "SELECT * FROM flights WHERE arr_delay > 20")

head(delays)
##   year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## 1 2013     1   1      542            540         2      923            850
## 2 2013     1   1      559            600        -1      941            910
## 3 2013     1   1      608            600         8      807            735
## 4 2013     1   1      624            630        -6      909            840
## 5 2013     1   1      628            630        -2     1016            947
## 6 2013     1   1      635            635         0     1028            940
##   arr_delay carrier flight tailnum origin dest air_time distance hour
## 1        33      AA   1141  N619AA    JFK  MIA      160     1089    5
## 2        31      AA    707  N3DUAA    LGA  DFW      257     1389    6
## 3        32      MQ   3768  N9EAMQ    EWR  ORD      139      719    6
## 4        29      EV   4626  N11107    EWR  MSP      190     1008    6
## 5        29      UA   1665  N33289    EWR  LAX      366     2454    6
## 6        48      AA    711  N3GKAA    LGA  DFW      248     1389    6
##   minute  time_hour
## 1     40 1357034400
## 2      0 1357038000
## 3      0 1357038000
## 4     30 1357038000
## 5     30 1357038000
## 6     35 1357038000

Podemos usar la función tbl () para crear punteros a las tablas remotas en la base de datos. Podemos guardar ese puntero en una variable y realizar manipulaciones en él como si fuera un data frame local.

flights <- tbl(con, "flights")

head(flights)
## # Source:   lazy query [?? x 19]
## # Database: sqlite 3.22.0 [:memory:]
##    year month   day dep_time sched_dep_time dep_delay arr_time
##   <int> <int> <int>    <int>          <int>     <dbl>    <int>
## 1  2013     1     1      517            515         2      830
## 2  2013     1     1      533            529         4      850
## 3  2013     1     1      542            540         2      923
## 4  2013     1     1      544            545        -1     1004
## 5  2013     1     1      554            600        -6      812
## 6  2013     1     1      554            558        -4      740
## # … with 12 more variables: sched_arr_time <int>, arr_delay <dbl>,
## #   carrier <chr>, flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## #   air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>,
## #   time_hour <dbl>

Ahora que tenemos una referencia a la tabla que queremos, pero no hemos obstruido la memoria local con un gran data frame , intentemos obtener el resultado que queremos. Podemos usar el comando dplyr filter () para mantener solo los vuelos que llegan más de 20 minutos tarde.

delays <- flights %>%
  filter(arr_delay > 20)

head(delays)
## # Source:   lazy query [?? x 19]
## # Database: sqlite 3.22.0 [:memory:]
##    year month   day dep_time sched_dep_time dep_delay arr_time
##   <int> <int> <int>    <int>          <int>     <dbl>    <int>
## 1  2013     1     1      542            540         2      923
## 2  2013     1     1      559            600        -1      941
## 3  2013     1     1      608            600         8      807
## 4  2013     1     1      624            630        -6      909
## 5  2013     1     1      628            630        -2     1016
## 6  2013     1     1      635            635         0     1028
## # … with 12 more variables: sched_arr_time <int>, arr_delay <dbl>,
## #   carrier <chr>, flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## #   air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>,
## #   time_hour <dbl>

Inspeccionando el SQL de Dplyr

Podemos inspeccionar la consulta nosotros mismos usando la función sql_render () de dbplyr (que se instaló con tidyverse).

## El dbplyr convierte el código dplyr en SQL. 
delays %>% 
  dbplyr::sql_render()
## <SQL> SELECT *
## FROM `flights`
## WHERE (`arr_delay` > 20.0)

Examinando datos

Cuando estamos manipulando datos, es muy importante para nosotros tener alguna información básica al respecto. Con los data frame locales, siempre podemos ejecutar comandos como dim (), ncol () y nrow () para comprender el tamaño y la forma de nuestros datos. Intentemos:

dim(flights)
## [1] NA 19
# Número de columnas
ncol(flights)
## [1] 19
# Número de filas
nrow(flights)
## [1] NA

Consultas Avanzadas

La mayoría de las funciones principales de dplyr funcionarán con tablas remotas, que incluyen (pero no se limitan a):

group_by() summarize() filter() select() mutate() arrange()

El dplyr pipe%>% también es compatible. Intentemos ver qué aeropuerto tiene las llegadas más retrasadas.

delays_by_airport <- flights %>%
  group_by(origin) %>%
  summarize(mean_dep_delay = mean(dep_delay), mean_arr_delay = mean(arr_delay)) %>%
  arrange(-mean_arr_delay)

head(delays_by_airport)
## Warning: Missing values are always removed in SQL.
## Use `AVG(x, na.rm = TRUE)` to silence this warning

## Warning: Missing values are always removed in SQL.
## Use `AVG(x, na.rm = TRUE)` to silence this warning
## # Source:     lazy query [?? x 3]
## # Database:   sqlite 3.22.0 [:memory:]
## # Ordered by: -mean_arr_delay
##   origin mean_dep_delay mean_arr_delay
##   <chr>           <dbl>          <dbl>
## 1 EWR              15.1           9.11
## 2 LGA              10.3           5.78
## 3 JFK              12.1           5.55

Explicación: Sin nada más, hemos confirmado de que el aeropuerto de Newark es el peor.

Revisemos el SQL detrás de ese código: podemos ver que es más complicado, pero como la mayoría de las funciones de dplyr están diseñadas para actuar como comandos SQL, se transfieren con bastante facilidad.

delays_by_airport %>%
  dbplyr::sql_render()
## Warning: Missing values are always removed in SQL.
## Use `AVG(x, na.rm = TRUE)` to silence this warning

## Warning: Missing values are always removed in SQL.
## Use `AVG(x, na.rm = TRUE)` to silence this warning
## <SQL> SELECT *
## FROM (SELECT `origin`, AVG(`dep_delay`) AS `mean_dep_delay`, AVG(`arr_delay`) AS `mean_arr_delay`
## FROM `flights`
## GROUP BY `origin`)
## ORDER BY -`mean_arr_delay`

Join

También podemos usar cualquiera de las funciones * _join (). En lugar de realizar uniones similares a SQL con data frame locales , en realidad estamos realizando uniones SQL de forma remota! Intentemos encontrar qué aerolínea tiene el mayor promedio de demoras, separadas por aeropuerto.

delays_airport_airline <- flights %>%
  group_by(origin, carrier) %>%
  summarize(mean_dep_delay = mean(dep_delay), mean_arr_delay = mean(arr_delay)) %>%
  arrange(-mean_arr_delay) %>%
  left_join(airlines)
## Joining, by = "carrier"
## `x` and `y` must share the same src, set `copy` = TRUE (may be slow)

Explicación:La tabla de vuelos contiene el id carrier , pero no los nombres de las aerolíneas. Eso está en la tabla de las aerolíneas. Sin embargo, tienen la columna portadora en común ( primary key), por lo que podemos realizar una unión izquierda para reunir esa información.

Plotting

Si estuviéramos interesados en ver cómo se realiza cada aeropuerto mes a mes, podríamos hacer algo como esto:

flights %>%
  group_by(month, origin) %>%
  summarize(mean_arr_delay = mean(arr_delay)) %>%
  collect() # Todos los registros
## Warning: Missing values are always removed in SQL.
## Use `AVG(x, na.rm = TRUE)` to silence this warning
## # A tibble: 36 x 3
## # Groups:   month [12]
##    month origin mean_arr_delay
##    <int> <chr>           <dbl>
##  1     1 EWR             12.8 
##  2     1 JFK              1.37
##  3     1 LGA              3.38
##  4     2 EWR              8.78
##  5     2 JFK              4.39
##  6     2 LGA              3.15
##  7     3 EWR             10.6 
##  8     3 JFK              2.58
##  9     3 LGA              3.74
## 10     4 EWR             14.1 
## # … with 26 more rows

GGplot

flights %>%
  group_by(month, origin) %>%
  summarize(mean_arr_delay = mean(arr_delay)) %>%
  collect() %>%
  ggplot(aes(x = month, y = mean_arr_delay)) +
  geom_line(aes(color = origin)) +
  geom_point(aes(color = origin))
## Warning: Missing values are always removed in SQL.
## Use `AVG(x, na.rm = TRUE)` to silence this warning

Una vez que hayamos terminado, es importante cerrar la conexión de nuestra base de datos. Lo hacemos con otra función DBI, dbDisconnect ():

dbDisconnect(con)

7 Recursos adicionales

7.2 Comando de conexión a otras bases de datos

IBM DB2

El paquete ibmdbR está diseñado para funcionar con los productos y servicios de base de datos de IBM. Este proporciona métodos para hacer que el trabajo con bases de datos sea más eficiente al insertar sin problemas las operaciones de R en la base de datos subyacente para su ejecución. Para configurar el entorno de desarrollo local y para conectar aplicaciones y herramientas a la base de datos Db2 desde R, debe saber los detalles de base de datos y las credenciales de conexión.

Nombre de host - El nombre de host del servidor. Número de puerto - Utilizado por el gestor de base de datos para la comunicación TCP/IP. (Tenga en cuenta que el servicio se suministra con un número de puerto Nombre de base de datos - La base de datos Db2. Credenciales de conexión ID de IBM - Si utiliza IBM Cloud, este es el ID que utilizará para iniciar sesión en IBM Cloud. Esto no es lo que utiliza para conectar aplicaciones o herramientas a la base de datos Db2. Credenciales de BD de Db2 - ID de usuario y una contraseña de base de datos que se pueden utilizar para conectar aplicaciones y herramientas a la base de datos. Usuarios creados por administrador - Db2 Warehouse y algunos planes de Servicio gestionado de Db2 permiten a los usuarios administrativos crear nuevos usuarios.

Procedimiento

#install.packages("ibmdbR")
#library (ibmdbR)
#driver.name <- "{IBM i Access ODBC Driver}"
#db.name <- "DBName"
#host.name <- "HostName"
#port <- "·"
#user.name <-"rUserName"
#pwd <- "Password"
#con.text <- paste("ConnectDb2;DRIVER=",driver.name,
                #";Database=",db.name,
                #";Hostname=",host.name,
                #";Port=",port,
                #";UID=", user.name,
                #";PWD=",pwd,sep="")

Oracle

Las opciones más conocidas para acceder a una base de datos de Oracle desde R son; RODBC, RJDBC, and ROracle que se pueden instalar en el ambiente de R. RJDBC puede conectarse a una base de datos Oracle y ejecutar comandos SQL directamente en R

Hay 5 configuraiones necesaras para realzar la conexiòn:

  • Driver - onsulte la sección ControladoresHost - una ruta de red al servidor de base de datos
  • UID - el ID de red del usuario o la cuenta local del servidor
  • PWD - La contraseña de la cuenta
  • Port - puerto

Procedimiento

#install.packages(“RJDBC”)
#library(RJDBC)
## 1.Indicar locaclizaciòn del driver
#driver <- JDBC("com.ddtek.jdbc.sparksql.SparkSQLDriver", "C:/Users/jmoore/Desktop/R POC/Spark Drivers/JDBC/sparksql.jar", identifier.quote="`")
# 2.Realizar la conexión utilizando JDBC driver y conexion URL
#conn <- dbConnect(driver, "jdbc:datadirect:sparksql:// <hostname>:<portnumber>;Database=perf;", "username", "password")
# 3.Ejecutar SQL Tables call
#dbListTables(conn)
# 4.Cerrar conexiòn con la base de datos 
#dbDisconnect(conn)

SQLite

Procedimiento

#Para conectarse a una base de datos SQLite, primero debe instalar y cargar el paquete DBI y el paquete de backend RSQLite:
#library(DBI)
#library(RSQLite)
#después debe definir  que el driver de la base de datos sea del tipo "SQLite" y configure una conexión para la base de datos, en este caso nombrando el archivo:
#controlador <- dbDriver("SQLite")
#bd_file <- system.file(
#"extdata",
#"crabtag.sqlite",
#package = "learningr"
#)
#conexion <- dbConnect(controlador, bd_file)

MySQL

El equivalente para una base de datos MySQL sería cargar el paquete RMySQL y establecer el tipo de controlador para ser “MySQL”:

Procedimiento

controlador <- dbDriver("SQLite")
bd_file <- system.file(
"extdata",
"crabtag.sqlite",
package = "learningr")
conexion <- dbConnect(controlador, bd_file)

7.3 Reflexione

¿CUANDO ES RECOMENDABLE USAR BASES DE DATOS?

Escalabilidad

En instituciones financieras, organizaciones de investigación y telecoms, es posible encontrar varios terabytes de data al mismo tiempo. Para las bases de datos relacionales esto es un trabajo muy sencillo puesto que algunas de estas están diseñadas para manejar petabytes de data de ser necesario.

Limpieza de datos Para convertir strings a fechas, remover “nulo” de columnas numéricas, records corruptos y agregar columnas para clasificar diversos elementos de la data como, por ejemplo, una fecha de pago por valor un como “tardío”, “a tiempo”, etc. Esta operación puede tomar mucho tiempo al equipo si debe realizarla cada vez que se desea realizar un análisis, por tanto, es mejor realizarlos en la fuente de los datos.

Seguridad La mayoría de los motores de RDBMS incluyen cualidades de seguridad robustas que permiten a la data tener una disposición de “solo lectura” para ciertos usuarios, inaccesible para otro y capacidades de lectura/escritura para “super usuarios”. Si estamos trabajando con data confidencial se pueden implementar funciones de seguridad aún más estrictas que bloqueen el acceso a ciertas porciones de la información o también oscurecer ciertas columnas que contengan cosas como números de identificación o datos sensibles para la compañía.

¿Cuándo NO es recomendable usar una Base de Datos?

  • Se cuenta con un dataset pequeño

  • El análisis no será recurrente

Es más efectivo desde un punto de vista de tiempo, esperar unos minutos adicionales para que las tareas se completen, de lo que sería invertirlo creando, cargando e indexando una base de datos que solo será utilizada una vez.

  • Ser el único usuario de la data

La data puede permanecer en nuestra estación de trabajo local u otro ambiente de usuario sin la preocupación de que sea accedida por tercero, si el data set no es demasiad grande no vale la pena implementar una base de datos.