R and databases

## INE Educa: Clases abiertas de R

## Proyecto Ciencia de Datos

### Mayo 2023

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Gracias por el interés

]

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Motivación de la actividad

]

### Esta presentación también está hecha con `R` 
]

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Comentario preliminar

### Público objetivo: Usuarios y usuarias intermedios de R

]

**Objetivo del taller:**

- Mostrar a los y las participantes cómo interactuar con una base de datos desde R

---

# I. Introducción

---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# ¿Por qué es relevante trabajar con BBDD?

### Muchos datos viven en lugares diferentes a nuestros computadores

]

Hay colecciones de datos que no entran en la memoria de nuestros computadores

### Como analistas, debemos ser capaces de trabajar con esos datos

### Las aplicaciones en general utilizan bases de datos

`Shiny` no es la excepción (*sesión 2*)

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# ¿Qué es una base de datos?

]

### Nos centraremos en el mundo SQL (bases de datos estructuradas)

Si quieres saber más sobre el mundo NoSql, puedes partir por [wikipedia](https://es.wikipedia.org/wiki/NoSQL)

**¡Advertencia!: Esta no es una clase sobre bases de datos**

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# ¿Qué es una base de datos estructurada?

Colección de tablas relacionadas por un campo común (key)

Archivos csv, dta (Stata) o sav (SPSS) **NO** son bases de datos

Coloquialmente, usamos el concepto "base de datos" para referirnos a esto

]

---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# ¿Cómo se ve una base de datos?

]

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# ¿Qué es una base de datos?

**Aclaremos un poco más**

**Las tablas se parecen a los dataframes**

- Colecciones de columnas

- Las columnas contienen datos del mismo tipo

**Diferencias**

- Las tablas no viven en memoria

- Los *dataframe* no tienen índices (*data.table* sí tiene)

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Gestores populares de bases de datos

]

### Tienen diferencias, pero todas utilizan SQL

### [Aquí](https://en.wikipedia.org/wiki/List_of_relational_database_management_systems) puedes encontrar aún más gestores de bases de datos

<br>

### Mucho blabla ¡Veamos un poco de código!
]

---

# II. Conexión a una base de datos

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Conexión a una base de datos

Vamos a conectarnos a una base que contiene datos de la Encuesta Nacional de Empleo (ENE) para varios trimestres

```r
library(DBI)
library(dplyr)
```

```r
conn <-  DBI::dbConnect(
  drv =  RMariaDB::MariaDB(),
  dbname = "ine", # nombre de la base de datos
  host =   "143.198.79.143" , # IP del servidor
  port =   1111, # puerto
  password = "XXXXXXXXXX", #  pass
  user = "reader" #  solo permiso de lectura
)
```

]

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Conexión a una base de datos

]

### El parámetro driver corresponde al gestor de base de datos que estamos usando

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Consultas simples con DBI

Listemos las tablas dentro de la base de datos

```r
DBI::dbListTables(conn)
```

```
## [1] "ene"
```

Veamos el nombre de las columnas dentro de ene

```r
columns <- DBI::dbListFields(conn, "ene")
columns
```

```
##  [1] "idrph"               "region"              "provincia"          
##  [4] "mes_central"         "id_identificacion"   "ano_encuesta"       
##  [7] "estrato"             "fact_cal"            "sexo"               
## [10] "edad"                "cae_especifico"      "activ"              
## [13] "categoria_ocupacion" "habituales"          "efectivas"
```

Podemos leer la tabla completa, sin embargo, no siempre es buena idea

```r
table_ene <-  DBI::dbReadTable(conn, "ene")
```

### La tabla puede ser demasiado grande y no entrar en la memoria

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Consultas simples con SQL

Contemos el número de filas que tiene la tabla

```r
sql_output_df <- dbGetQuery(conn, "SELECT COUNT(*) AS `contar` FROM `ene`")
sql_output_df
```

```
##   contar
## 1  94042
```

Podemos ver que la tabla contiene 94042

No es tan buena idea pedir la tabla completa

Noten que `dbGetQuery` devuelve un *dataframe* con el resultado de la consulta 👀

---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Consultas por lotes

`dbSendQuery` envía la consulta sin hacer *fetch* (traer)

```r
sql_query <- dbSendQuery(conn, "SELECT COUNT(*) AS `contar` FROM `ene`")
sql_query
```

```
## <MariaDBResult>
##   SQL  SELECT COUNT(*) AS `contar` FROM `ene`
##   ROWS Fetched: 0 [incomplete]
##        Changed: 0
```
]

Podemos hacer consultas por "lotes"

```r
sql_query <- dbSendQuery(conn, "SELECT sexo, edad FROM `ene`")
output1 <- dbFetch(sql_query, 10) 
DBI::dbHasCompleted(sql_query) 
```

```
## [1] FALSE
```

```r
output2 <- dbFetch(sql_query, 10) 
DBI::dbHasCompleted(sql_query) 
```

```
## [1] FALSE
```

```r
output3 <- dbFetch(sql_query, 94023) 
DBI::dbHasCompleted(sql_query) 
```

```
## [1] TRUE
```

]
---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Consultas por lotes

```r
sql_query <- dbSendQuery(conn, "SELECT sexo, edad FROM `ene`")
```

```r
count <- 0
# Mientras queden filas por extraer...
while (DBI::dbHasCompleted(sql_query) == FALSE ) {
  # Traer las 20.000 filas siguientes
  output <- dbFetch(sql_query, 20000) 
  count <- count + 1
  print(count)
}
```

```
## [1] 1
## [1] 2
## [1] 3
## [1] 4
## [1] 5
```
Con esta estrategia podemos procesar información de manera fragmentada, incluso en una máquina pequeña 😎

---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# dplyr al rescate

```r
library(dplyr)
connection <- tbl(conn, "ene")

connection %>% 
  summarise(freq = n()) 
```

```
## # Source:   SQL [1 x 1]
## # Database: mysql  [reader@143.198.79.143:NA/ine]
##      freq
##   <int64>
## 1   94042
```

]

Con `show_query` podemos ver la traducción a SQL

```r
connection %>% 
  summarise(freq = n()) %>% 
  show_query()
```

```
## <SQL>
## SELECT COUNT(*) AS `freq`
## FROM `ene`
```

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Funcionamiento dbplyr

```r
library(tictoc)
tic()
output <-  connection %>% 
  filter(edad <= 25) %>% 
  group_by(sexo) %>% 
  summarise(freq = n()) 
toc()
```

```
## 0.016 sec elapsed
```

]

```r
tic()
connection %>% 
  filter(edad <= 25) %>% 
  group_by(sexo) %>% 
  summarise(freq = n()) 
```

```
## # Source:   SQL [2 x 2]
## # Database: mysql  [reader@143.198.79.143:NA/ine]
##   sexo     freq
##   <chr> <int64>
## 1 1       15652
## 2 2       14986
```

```r
toc()
```

```
## 0.949 sec elapsed
```

]

### ¿Por qué el tiempo de ejecución es tan distinto?

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Evaluación lazy

La consulta funciona por detrás con `dbplyr`

`dbplyr` intenta retrasar la evaluación lo que más pueda

El objeto `output` no es un `dataframe`, sino una conexión a la base de datos

```r
class(output)
```

```
## [1] "tbl_MariaDBConnection" "tbl_dbi"               "tbl_sql"              
## [4] "tbl_lazy"              "tbl"
```

Con `collect` podemos traer la información desde la base de datos

```r
output %>%
  collect() %>% 
  class()
```

```
## [1] "tbl_df"     "tbl"        "data.frame"
```

### Recuerda que la consulta se ejecuta en el servidor

El resultado de la consulta no necesariamente entrará en tu memoria

---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Ejercicio

Queremos calcular la cantidad de ocupados para cada una de las regiones del país usando el factor de expansión.

```r
dbListFields(conn, "ene")
```

```r
ene_data <-  tbl(conn, "ene")

df <- ene_data %>% 
  filter(activ == 1) %>% 
  group_by(region) %>% 
  summarize(total = sum(fact_cal) ) %>% 
  collect()
```

---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Demostración

Para ver la consulta en SQL, usamos `show_query`

```r
ene_data %>% 
  filter(activ == 1) %>% 
  group_by(region) %>% 
  summarize(total = sum(fact_cal) ) %>% 
  show_query()
```

```
## <SQL>
## SELECT `region`, SUM(`fact_cal`) AS `total`
## FROM `ene`
## WHERE (`activ` = 1.0)
## GROUP BY `region`
```
--

### Podemos ir aprendiendo SQL de manera pausada

---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Resumiendo...

Hasta el momento hemos consultado una base de datos

Estamos usando un usuario que solo tiene permisos de lectura

```r
df <- data.frame(var1 = c(1:3), var2 = 4:6)
DBI::dbCreateTable(conn, "ene2", df)
```

```
## Error: CREATE command denied to user 'reader'@'186.106.141.123' for table `ine`.`ene2` [1142]
```
--

```r
DBI::dbRemoveTable(conn, "ene")
```

```
## Error: DROP command denied to user 'reader'@'186.106.141.123' for table `ine`.`ene` [1142]
```

### Vamos a crear nuestra propia base de datos y prender fuego a todo

---

# III. Creando nuestra propia base de datos

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Creando nuestra base de datos

Este código genera un archivo sqlite

```r
library(RSQLite)
my_db_file <- "data/my_db.sqlite"
new_conn <- dbConnect(RSQLite::SQLite(), my_db_file)
```

También podemos crear una base que viva en la memoria

```r
library(RSQLite)
new_conn2 <- dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbDisconnect(new_conn2)
```

Veamos qué contiene nuestra base de datos

```r
dbListTables(new_conn)
```

```
## character(0)
```

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Poblando la base de datos

Usaremos información de la base inicial

```r
sex_frequency <-  output %>%
  collect() 
sex_frequency
```

```
## # A tibble: 2 × 2
##   sexo     freq
##   <chr> <int64>
## 1 1       15652
## 2 2       14986
```

```r
dbCreateTable(new_conn, name = "sex_frequency", fields = sex_frequency)
dbListTables(new_conn)
```

```
## [1] "sex_frequency"
```

Podemos ver que nuestra tabla existe 😄

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Poblando la base de datos

### Hagamos una prueba...

```r
dbReadTable(new_conn, "sex_frequency")
```

```
## [1] sexo freq
## <0 rows> (or 0-length row.names)
```

### Algo no salió bien 😔

---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Poblando la base de datos

Si consultamos por las columnas de la tabla, podemos ver que estas existen

```r
dbListFields(new_conn, "sex_frequency")
```

```
## [1] "sexo" "freq"
```
Significa que creamos una tabla, pero esta aun no tiene datos

Escribamos en nuestra tabla los datos de frecuencia

```r
dbAppendTable(new_conn, name = "sex_frequency", value = sex_frequency )
```

```
## [1] 2
```

```r
dbReadTable(new_conn, "sex_frequency")
```

```
##   sexo  freq
## 1    1 15652
## 2    2 14986
```

### ¡Ahí están nuestros datos!

---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Editando la base de datos

Dado que ahora tenemos permisos de edición, podemos agregar filas

```r
dbAppendTable(new_conn, "sex_frequency", data.frame(sexo = 3, freq = 9))
```

```
## [1] 1
```

```r
dbReadTable(new_conn, "sex_frequency")
```

```
##   sexo  freq
## 1    1 15652
## 2    2 14986
## 3  3.0     9
```

Podemos encadenar algún proceso sistemático en R a nuestra base de datos 😎

También podemos eliminar tablas, pero esto debe hacerse con cuidado

```r
dbRemoveTable(new_conn, "sex_frequency")
```

---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Permisos

Al trabajar con varios usuarios, es importante definir claramente los privilegios

Las bases de datos permiten establecer permisos muy específicos

- select
- insert
- update
- delete
- drop
- etc

Estos permisos podemos asignarlos para cada tabla o esquema

---

background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Para terminar...

Para desconectarnos, usamos dbDisconnect()

```r
DBI::dbDisconnect(conn)
DBI::dbDisconnect(new_conn)
```

---
background-image: url("imagenes/fondo2.PNG")
background-size: contain;
background-position: 100% 0%

# Otros temas relacionados

]

]

Exponer una base de datos a terceros

]

---

## INE Educa: Clases abiertas de R

## Proyecto Ciencia de Datos

### Mayo 2023