21 Bases de datos

21.1 Introducción

Una gran cantidad de datos reside en las bases de datos, por lo que es esencial que sepa cómo acceder a ellos. A veces, puede pedirle a alguien que descargue una instantánea en un .csv para usted, pero esto se vuelve doloroso rápidamente: cada vez que necesite hacer un cambio, tendrá que comunicarse con otro ser humano. Desea poder acceder directamente a la base de datos para obtener los datos que necesita, cuando los necesita.

En este capítulo, primero aprenderá los conceptos básicos del paquete DBI: cómo usarlo para conectarse a una base de datos y luego recuperar datos con una consulta SQL¹. SQL, abreviatura de structured query llanguage, es la lingua franca de las bases de datos y es un lenguaje importante que deben aprender todos los científicos de datos. Dicho esto, no vamos a comenzar con SQL, sino que le enseñaremos dbplyr, que puede traducir su código dplyr a SQL. Usaremos eso como una forma de enseñarle algunas de las características más importantes de SQL. No se convertirá en un maestro de SQL al final del capítulo, pero podrá identificar los componentes más importantes y entender lo que hacen.

21.1.1 Requisitos previos

En este capítulo, presentaremos DBI y dbplyr. DBI es una interfaz de bajo nivel que se conecta a bases de datos y ejecuta SQL; dbplyr es una interfaz de alto nivel que traduce su código dplyr a consultas SQL y luego las ejecuta con DBI.

library(DBI)
library(dbplyr)
library(tidyverse)

21.2 Bases de datos básicos

En el nivel más simple, puede pensar en una base de datos como una colección de marcos de datos, llamados tablas en la terminología de la base de datos. Al igual que un marco de datos, una tabla de base de datos es una colección de columnas con nombre, donde cada valor en la columna es del mismo tipo. Hay tres diferencias de alto nivel entre los marcos de datos y las tablas de la base de datos:

Las tablas de la base de datos se almacenan en el disco y pueden tener un tamaño arbitrario. Los marcos de datos se almacenan en la memoria y están fundamentalmente limitados (aunque ese límite sigue siendo bastante grande para muchos problemas).
Las tablas de bases de datos casi siempre tienen índices. Al igual que el índice de un libro, el índice de una base de datos permite encontrar rápidamente filas de interés sin tener que mirar cada una de ellas. Los marcos de datos y los tibbles no tienen índices, pero las tablas de datos sí, que es una de las razones por las que son tan rápidos.
La mayoría de las bases de datos clásicas están optimizadas para recopilar datos rápidamente, no para analizar los datos existentes. Estas bases de datos se denominan orientadas a filas porque los datos se almacenan fila por fila, en lugar de columna por columna como R. Más recientemente, ha habido mucho desarrollo de bases de datos orientadas a columnas que hacen que el análisis de los datos existentes sea mucho más rápido.

Las bases de datos se ejecutan mediante sistemas de administración de bases de datos (DBMS para abreviar), que vienen en tres formas básicas:

Cliente-servidor Los DBMS se ejecutan en un poderoso servidor central, que usted conecta desde su computadora (el cliente). Son excelentes para compartir datos con varias personas en una organización. Los DBMS cliente-servidor populares incluyen PostgreSQL, MariaDB, SQL Server y Oracle.
Los DBMS de Cloud, como Snowflake, RedShift de Amazon y BigQuery de Google, son similares a los DBMS del servidor del cliente, pero se ejecutan en la nube. Esto significa que pueden manejar fácilmente conjuntos de datos extremadamente grandes y pueden proporcionar automáticamente más recursos informáticos según sea necesario.
Los DBMS en proceso, como SQLite o duckdb, se ejecutan completamente en su computadora. Son excelentes para trabajar con grandes conjuntos de datos en los que usted es el usuario principal.

21.3 Conexión a una base de datos

Para conectarse a la base de datos desde R, utilizará un par de paquetes:

Siempre usará DBI (database interface) porque proporciona un conjunto de funciones genéricas que se conectan a la base de datos, cargan datos, ejecutan consultas SQL, etc.
También utilizará un paquete diseñado para el DBMS al que se está conectando. Este paquete traduce los comandos DBI genéricos a los específicos necesarios para un DBMS dado. Por lo general, hay un paquete para cada DBMS, p. RPostgres para PostgresSQL y RMariaDB para MySQL.

Si no puede encontrar un paquete específico para su DBMS, generalmente puede usar el paquete odbc en su lugar. Esto usa el protocolo ODBC soportado por muchos DBMS. odbc requiere un poco más de configuración porque también necesitará instalar un controlador ODBC e indicarle al paquete odbc dónde encontrarlo.

Concretamente, crea una conexión a la base de datos usando DBI::dbConnect(). El primer argumento selecciona DBMS², luego el segundo argumento y los subsiguientes describen cómo conectarse a él (es decir, dónde reside y las credenciales que necesita para acceder a él). El siguiente código muestra un par de ejemplos típicos:

con <- DBI::dbConnect(
  RMariaDB::MariaDB(), 
  username = "foo"
)
con <- DBI::dbConnect(
  RPostgres::Postgres(), 
  hostname = "databases.mycompany.com", 
  port = 1234
)

Los detalles precisos de la conexión varían mucho de DBMS a DBMS, por lo que lamentablemente no podemos cubrir todos los detalles aquí. Esto significa que tendrás que investigar un poco por tu cuenta. Por lo general, puede preguntar a los otros científicos de datos de su equipo o hablar con su DBA (database aadministrador). La configuración inicial a menudo requerirá un poco de manipulación (y tal vez un poco de google) para hacerlo bien, pero generalmente solo necesitará hacerlo una vez.

21.3.1 En este libro

Configurar un DBMS cliente-servidor o en la nube sería una molestia para este libro, por lo que en su lugar usaremos un DBMS en proceso que vive completamente en un paquete R: duckdb. Gracias a la magia de DBI, la única diferencia entre usar duckdb y cualquier otro DBMS es cómo te conectarás a la base de datos. Esto hace que sea excelente para enseñar porque puede ejecutar fácilmente este código y tomar fácilmente lo que aprende y aplicarlo en otro lugar.

Conectarse a duckdb es particularmente simple porque los valores predeterminados crean una base de datos temporal que se elimina cuando sale de R. Eso es excelente para aprender porque garantiza que comenzará desde cero cada vez que reinicie R:

con <- DBI::dbConnect(duckdb::duckdb())

duckdb es una base de datos de alto rendimiento diseñada en gran medida para las necesidades de un científico de datos. Lo usamos aquí porque es muy fácil de usar, pero también es capaz de manejar gigabytes de datos a gran velocidad. Si desea utilizar duckdb para un proyecto de análisis de datos real, también deberá proporcionar el argumento dbdir para crear una base de datos persistente y decirle a duckdb dónde guardarla. Asumiendo que estás usando un proyecto (Capítulo 6), es razonable guardarlo en el directorio duckdb del proyecto actual:

con <- DBI::dbConnect(duckdb::duckdb(), dbdir = "duckdb")

21.3.2 Cargar algunos datos

Dado que esta es una base de datos nueva, debemos comenzar agregando algunos datos. Aquí agregaremos conjuntos de datos mpg y diamonds de ggplot2 usando DBI::dbWriteTable(). El uso más simple de dbWriteTable() necesita tres argumentos: una conexión de base de datos, el nombre de la tabla para crear en la base de datos y un marco de datos de datos.

dbWriteTable(con, "mpg", ggplot2::mpg)
dbWriteTable(con, "diamonds", ggplot2::diamonds)

Si está utilizando duckdb en un proyecto real, le recomendamos que aprenda sobre duckdb_read_csv() y duckdb_register_arrow(). Estos le brindan formas potentes y eficaces de cargar rápidamente datos directamente en duckdb, sin tener que cargarlos primero en R. También mostraremos una técnica útil para cargar varios archivos en una base de datos en Sección 26.4.1.

21.3.3 DBI básico

Puede comprobar que los datos se cargan correctamente utilizando un par de otras funciones de DBI: dbListTables() enumera todas las tablas de la base de datos³ y dbReadTable() recupera el contenido de una tabla.

dbListTables(con)
#> [1] "diamonds" "mpg"

con |> 
  dbReadTable("diamonds") |> 
  as_tibble()
#> # A tibble: 53,940 × 10
#>   carat cut       color clarity depth table price     x     y     z
#>   <dbl> <fct>     <fct> <fct>   <dbl> <dbl> <int> <dbl> <dbl> <dbl>
#> 1  0.23 Ideal     E     SI2      61.5    55   326  3.95  3.98  2.43
#> 2  0.21 Premium   E     SI1      59.8    61   326  3.89  3.84  2.31
#> 3  0.23 Good      E     VS1      56.9    65   327  4.05  4.07  2.31
#> 4  0.29 Premium   I     VS2      62.4    58   334  4.2   4.23  2.63
#> 5  0.31 Good      J     SI2      63.3    58   335  4.34  4.35  2.75
#> 6  0.24 Very Good J     VVS2     62.8    57   336  3.94  3.96  2.48
#> # ℹ 53,934 more rows

dbReadTable() devuelve un data.frame por lo que usamos as_tibble() para convertirlo en un tibble para que se imprima bien.

Si ya conoce SQL, puede usar dbGetQuery() para obtener los resultados de ejecutar una consulta en la base de datos:

sql <- "
  SELECT carat, cut, clarity, color, price 
  FROM diamonds 
  WHERE price > 15000
"
as_tibble(dbGetQuery(con, sql))
#> # A tibble: 1,655 × 5
#>   carat cut       clarity color price
#>   <dbl> <fct>     <fct>   <fct> <int>
#> 1  1.54 Premium   VS2     E     15002
#> 2  1.19 Ideal     VVS1    F     15005
#> 3  2.1  Premium   SI1     I     15007
#> 4  1.69 Ideal     SI1     D     15011
#> 5  1.5  Very Good VVS2    G     15013
#> 6  1.73 Very Good VS1     G     15014
#> # ℹ 1,649 more rows

Si nunca ha visto SQL antes, ¡no se preocupe! En breve aprenderás más al respecto. Pero si lo lee detenidamente, puede adivinar que selecciona cinco columnas del conjunto de datos de diamantes y todas las filas donde el precio es mayor que 15,000.

21.4 dbplyr básico

Ahora que nos conectamos a una base de datos y cargamos algunos datos, podemos comenzar a aprender sobre dbplyr. dbplyr es un backend de dplyr, lo que significa que sigues escribiendo código dplyr pero el backend lo ejecuta de manera diferente. En esto, dbplyr se traduce a SQL; otros backends incluyen dtplyr que se traduce en data.table, y multidplyr que ejecuta su código en múltiples núcleos.

Para usar dbplyr, primero debe usar tbl() para crear un objeto que represente una tabla de base de datos:

diamonds_db <- tbl(con, "diamonds")
diamonds_db
#> # Source:   table<diamonds> [?? x 10]
#> # Database: DuckDB v0.10.2 [unknown@Linux 6.5.0-1025-azure:R 4.4.0/:memory:]
#>   carat cut       color clarity depth table price     x     y     z
#>   <dbl> <fct>     <fct> <fct>   <dbl> <dbl> <int> <dbl> <dbl> <dbl>
#> 1  0.23 Ideal     E     SI2      61.5    55   326  3.95  3.98  2.43
#> 2  0.21 Premium   E     SI1      59.8    61   326  3.89  3.84  2.31
#> 3  0.23 Good      E     VS1      56.9    65   327  4.05  4.07  2.31
#> 4  0.29 Premium   I     VS2      62.4    58   334  4.2   4.23  2.63
#> 5  0.31 Good      J     SI2      63.3    58   335  4.34  4.35  2.75
#> 6  0.24 Very Good J     VVS2     62.8    57   336  3.94  3.96  2.48
#> # ℹ more rows

Hay otras dos formas comunes de interactuar con una base de datos. Primero, muchas bases de datos corporativas son muy grandes, por lo que necesita cierta jerarquía para mantener todas las tablas organizadas. En ese caso, es posible que deba proporcionar un esquema, o un catálogo y un esquema, para elegir la tabla que le interesa.:

diamonds_db <- tbl(con, in_schema("sales", "diamonds"))
diamonds_db <- tbl(con, in_catalog("north_america", "sales", "diamonds"))

Otras veces, es posible que desee utilizar su propia consulta SQL como punto de partida:

diamonds_db <- tbl(con, sql("SELECT * FROM diamonds"))

Este objeto es perezoso; cuando usa verbos dplyr en él, dplyr no hace ningún trabajo: solo registra la secuencia de operaciones que desea realizar y solo las realiza cuando es necesario. Por ejemplo, tome la siguiente canalización:

big_diamonds_db <- diamonds_db |> 
  filter(price > 15000) |> 
  select(carat:clarity, price)

big_diamonds_db
#> # Source:   SQL [?? x 5]
#> # Database: DuckDB v0.10.2 [unknown@Linux 6.5.0-1025-azure:R 4.4.0/:memory:]
#>   carat cut       color clarity price
#>   <dbl> <fct>     <fct> <fct>   <int>
#> 1  1.54 Premium   E     VS2     15002
#> 2  1.19 Ideal     F     VVS1    15005
#> 3  2.1  Premium   I     SI1     15007
#> 4  1.69 Ideal     D     SI1     15011
#> 5  1.5  Very Good G     VVS2    15013
#> 6  1.73 Very Good G     VS1     15014
#> # ℹ more rows

Puede decir que este objeto representa una consulta de base de datos porque imprime el nombre de DBMS en la parte superior y, aunque le dice el número de columnas, normalmente no sabe el número de filas. Esto se debe a que encontrar el número total de filas generalmente requiere ejecutar la consulta completa, algo que estamos tratando de evitar.

Puede ver el código SQL generado por la función dbplyr show_query(). Si conoce dplyr, ¡esta es una excelente manera de aprender SQL! Escriba algo de código dplyr, obtenga dbplyr para traducirlo a SQL y luego intente averiguar cómo coinciden los dos idiomas.

big_diamonds_db |>
  show_query()
#> <SQL>
#> SELECT carat, cut, color, clarity, price
#> FROM diamonds
#> WHERE (price > 15000.0)

Para recuperar todos los datos en R, llama a collect(). Detrás de escena, esto genera el SQL, llama a dbGetQuery() para obtener los datos, luego convierte el resultado en un tibble:

big_diamonds <- big_diamonds_db |> 
  collect()
big_diamonds
#> # A tibble: 1,655 × 5
#>   carat cut       color clarity price
#>   <dbl> <fct>     <fct> <fct>   <int>
#> 1  1.54 Premium   E     VS2     15002
#> 2  1.19 Ideal     F     VVS1    15005
#> 3  2.1  Premium   I     SI1     15007
#> 4  1.69 Ideal     D     SI1     15011
#> 5  1.5  Very Good G     VVS2    15013
#> 6  1.73 Very Good G     VS1     15014
#> # ℹ 1,649 more rows

Por lo general, usará dbplyr para seleccionar los datos que desea de la base de datos, realizando filtrado y agregación básicos utilizando las traducciones que se describen a continuación. Luego, una vez que esté listo para analizar los datos con funciones que son exclusivas de R, collect() los datos para obtener un tibble en memoria y continuar su trabajo con código R puro.

21.5 SQL

El resto del capítulo le enseñará un poco de SQL a través de la lente de dbplyr. Es una introducción bastante no tradicional a SQL, pero esperamos que lo ponga rápidamente al día con los conceptos básicos. Afortunadamente, si entiende dplyr, está en un buen lugar para aprender SQL rápidamente porque muchos de los conceptos son los mismos.

Exploraremos la relación entre dplyr y SQL usando un par de viejos amigos del paquete nycflights13: flights y planes. Estos conjuntos de datos son fáciles de ingresar a nuestra base de datos de aprendizaje porque dbplyr viene con una función que copia las tablas de nycflights13 a nuestra base de datos:

dbplyr::copy_nycflights13(con)
#> Creating table: airlines
#> Creating table: airports
#> Creating table: flights
#> Creating table: planes
#> Creating table: weather
flights <- tbl(con, "flights")
planes <- tbl(con, "planes")

21.5.1 SQL básico

Los componentes de nivel superior de SQL se denominan declaraciones. Las declaraciones comunes incluyen CREATE para definir nuevas tablas, INSERT para agregar datos y SELECT para recuperar datos. Nos centraremos en las declaraciones SELECT, también llamadas consultas, porque son casi exclusivamente lo que usará como científico de datos.

Una consulta se compone de cláusulas. Hay cinco cláusulas importantes: SELECT, FROM, WHERE, ORDER BY y GROUP BY. Cada consulta debe tener las cláusulas SELECT⁴ y FROM⁵ y la consulta más simple es SELECT * FROM table, que selecciona todas las columnas de la tabla especificada . Esto es lo que genera dbplyr para una tabla sin adulterar :

flights |> show_query()
#> <SQL>
#> SELECT *
#> FROM flights
planes |> show_query()
#> <SQL>
#> SELECT *
#> FROM planes

WHERE y ORDER BY controlan qué filas se incluyen y cómo se ordenan:

flights |> 
  filter(dest == "IAH") |> 
  arrange(dep_delay) |>
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (dest = 'IAH')
#> ORDER BY dep_delay

GROUP BY convierte la consulta en un resumen, lo que hace que se produzca la agregación:

flights |> 
  group_by(dest) |> 
  summarize(dep_delay = mean(dep_delay, na.rm = TRUE)) |> 
  show_query()
#> <SQL>
#> SELECT dest, AVG(dep_delay) AS dep_delay
#> FROM flights
#> GROUP BY dest

Hay dos diferencias importantes entre los verbos dplyr y las cláusulas SELECT:

En SQL, el caso no importa: puede escribir select, SELECT o incluso SeLeCt. En este libro nos apegaremos a la convención común de escribir palabras clave de SQL en mayúsculas para distinguirlas de los nombres de tablas o variables.
En SQL, el orden importa: siempre debe escribir las cláusulas en el orden SELECT, FROM, WHERE, GROUP BY, ORDER BY. De manera confusa, este orden no coincide con la evaluación real de las cláusulas, que es primero FROM, luego WHERE, GROUP BY, SELECT y ORDER BY.

Las siguientes secciones exploran cada cláusula con más detalle.

Tenga en cuenta que, si bien SQL es un estándar, es extremadamente complejo y ninguna base de datos lo sigue exactamente. Si bien los componentes principales en los que nos centraremos en este libro son muy similares entre los DBMS, existen muchas variaciones menores. Afortunadamente, dbplyr está diseñado para manejar este problema y genera diferentes traducciones para diferentes bases de datos. No es perfecto, pero está mejorando continuamente, y si encuentra un problema, puede presentar un problema en GitHub para ayudarnos a hacerlo mejor.

21.5.2 SELECT

La cláusula SELECT es el caballo de batalla de las consultas y realiza el mismo trabajo que select(), mutate(), rename(), relocate() y, como aprenderá en la próxima sección, summarize().

select(), rename() y relocate() tienen traducciones muy directas a SELECT ya que solo afectan el lugar donde aparece una columna (si es que aparece) junto con su nombre:

planes |> 
  select(tailnum, type, manufacturer, model, year) |> 
  show_query()
#> <SQL>
#> SELECT tailnum, "type", manufacturer, model, "year"
#> FROM planes

planes |> 
  select(tailnum, type, manufacturer, model, year) |> 
  rename(year_built = year) |> 
  show_query()
#> <SQL>
#> SELECT tailnum, "type", manufacturer, model, "year" AS year_built
#> FROM planes

planes |> 
  select(tailnum, type, manufacturer, model, year) |> 
  relocate(manufacturer, model, .before = type) |> 
  show_query()
#> <SQL>
#> SELECT tailnum, manufacturer, model, "type", "year"
#> FROM planes

Este ejemplo también muestra cómo SQL cambia el nombre. En la terminología SQL, el cambio de nombre se denomina aliasing y se realiza con AS. Tenga en cuenta que, a diferencia de mutate(), el nombre anterior está a la izquierda y el nuevo nombre está a la derecha.

En los ejemplos anteriores, tenga en cuenta que "year" y "type" están entre comillas dobles. Esto se debe a que estas son palabras reservadas en duckdb, por lo que dbplyr las cita para evitar cualquier posible confusión entre los nombres de columnas/tablas y los operadores de SQL.

Cuando trabaje con otras bases de datos, es probable que vea todos los nombres de variables entre comillas porque solo un puñado de paquetes de clientes, como duckdb, saben cuáles son todas las palabras reservadas, por lo que citan todo para estar seguros.

SELECT "tailnum", "type", "manufacturer", "model", "year"
FROM "planes"

Algunos otros sistemas de bases de datos usan acentos graves en lugar de comillas:

SELECT `tailnum`, `type`, `manufacturer`, `model`, `year`
FROM `planes`

Las traducciones de mutate() son igualmente sencillas: cada variable se convierte en una nueva expresión en SELECT:

flights |> 
  mutate(
    speed = distance / (air_time / 60)
  ) |> 
  show_query()
#> <SQL>
#> SELECT flights.*, distance / (air_time / 60.0) AS speed
#> FROM flights

Volveremos a la traducción de componentes individuales (como /) en Sección 21.6.

21.5.3 FROM

La cláusula FROM define la fuente de datos. Va a ser poco interesante por un tiempo, porque solo estamos usando tablas individuales. Verá ejemplos más complejos una vez que lleguemos a las funciones de unión.

21.5.4 GROUP BY

group_by() se traduce a la cláusula GROUP BY⁶ y summarize() se traduce a la cláusula SELECT:

diamonds_db |> 
  group_by(cut) |> 
  summarize(
    n = n(),
    avg_price = mean(price, na.rm = TRUE)
  ) |> 
  show_query()
#> <SQL>
#> SELECT cut, COUNT(*) AS n, AVG(price) AS avg_price
#> FROM diamonds
#> GROUP BY cut

Volveremos a lo que sucede con la traducción n() y mean() en Sección 21.6.

21.5.5 WHERE

filter() se traduce a la cláusula WHERE:

flights |> 
  filter(dest == "IAH" | dest == "HOU") |> 
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (dest = 'IAH' OR dest = 'HOU')

flights |> 
  filter(arr_delay > 0 & arr_delay < 20) |> 
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (arr_delay > 0.0 AND arr_delay < 20.0)

Hay algunos detalles importantes a tener en cuenta aquí:

| se convierte en OR y & se convierte en AND.
SQL usa = para comparar, no ==. SQL no tiene asignación, por lo que no hay posibilidad de confusión allí.
SQL usa solo '' para cadenas, no "". En SQL, "" se usa para identificar variables, como `` de R.

Otro operador SQL útil es IN, que está muy cerca del %in% de R:

flights |> 
  filter(dest %in% c("IAH", "HOU")) |> 
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (dest IN ('IAH', 'HOU'))

SQL usa NULL en lugar de NA. NULLs se comportan de manera similar a NAs. La principal diferencia es que, si bien son “infecciosos” en las comparaciones y la aritmética, se descartan silenciosamente al resumir. dbplyr le recordará este comportamiento la primera vez que lo presione:

flights |> 
  group_by(dest) |> 
  summarize(delay = mean(arr_delay))
#> Warning: Missing values are always removed in SQL aggregation functions.
#> Use `na.rm = TRUE` to silence this warning
#> This warning is displayed once every 8 hours.
#> # Source:   SQL [?? x 2]
#> # Database: DuckDB v0.10.2 [unknown@Linux 6.5.0-1025-azure:R 4.4.0/:memory:]
#>   dest   delay
#>   <chr>  <dbl>
#> 1 ORD    5.88 
#> 2 FLL    8.08 
#> 3 IAD   13.9  
#> 4 PBI    8.56 
#> 5 TPA    7.41 
#> 6 DFW    0.322
#> # ℹ more rows

Si desea obtener más información sobre cómo funcionan los valores NULL, puede disfrutar de “La lógica de tres valores de SQL” de Markus Winand.

En general, puedes trabajar con NULLs usando las funciones que usarías para NAs en R:

flights |> 
  filter(!is.na(dep_delay)) |> 
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> WHERE (NOT((dep_delay IS NULL)))

Esta consulta SQL ilustra uno de los inconvenientes de dbplyr: si bien el SQL es correcto, no es tan simple como podría escribirse a mano. En este caso, podría quitar los paréntesis y usar un operador especial que sea más fácil de leer:

WHERE "dep_delay" IS NOT NULL

Tenga en cuenta que si filtra, filter(), una variable que creó utilizando un resumen, dbplyr generará una cláusula ‘HAVING’, en lugar de una cláusula ‘WHERE’. Esta es una de las idiosincrasias de SQL: WHERE se evalúa antes que SELECT y GROUP BY, por lo que SQL necesita otra cláusula que se evalúa después.

diamonds_db |> 
  group_by(cut) |> 
  summarize(n = n()) |> 
  filter(n > 100) |> 
  show_query()
#> <SQL>
#> SELECT cut, COUNT(*) AS n
#> FROM diamonds
#> GROUP BY cut
#> HAVING (COUNT(*) > 100.0)

21.5.6 ORDER BY

Ordenar filas implica una traducción directa de arrange() a la cláusula ORDER BY:

flights |> 
  arrange(year, month, day, desc(dep_delay)) |> 
  show_query()
#> <SQL>
#> SELECT flights.*
#> FROM flights
#> ORDER BY "year", "month", "day", dep_delay DESC

Observe cómo desc() se traduce a DESC: esta es una de las muchas funciones dplyr cuyo nombre se inspiró directamente en SQL.

21.5.7 Subconsultas

A veces, no es posible traducir una canalización dplyr en una sola declaración SELECT y necesita usar una subconsulta. Una subconsulta es solo una consulta utilizada como fuente de datos en la cláusula FROM, en lugar de la tabla habitual.

dbplyr normalmente usa subconsultas para sortear las limitaciones de SQL. Por ejemplo, las expresiones en la cláusula SELECT no pueden hacer referencia a columnas que se acaban de crear. Eso significa que la siguiente canalización (tonta) de dplyr debe ocurrir en dos pasos: la primera consulta (interna) calcula year1 y luego la segunda consulta (externa) puede calcular year2.

flights |> 
  mutate(
    year1 = year + 1,
    year2 = year1 + 1
  ) |> 
  show_query()
#> <SQL>
#> SELECT q01.*, year1 + 1.0 AS year2
#> FROM (
#>   SELECT flights.*, "year" + 1.0 AS year1
#>   FROM flights
#> ) q01

También verá esto si intentó filtrar, filter(), una variable que acaba de crear. Recuerda, aunque WHERE se escribe después de SELECT, se evalúa antes, por lo que necesitamos una subconsulta en este (tonto) ejemplo:

flights |> 
  mutate(year1 = year + 1) |> 
  filter(year1 == 2014) |> 
  show_query()
#> <SQL>
#> SELECT q01.*
#> FROM (
#>   SELECT flights.*, "year" + 1.0 AS year1
#>   FROM flights
#> ) q01
#> WHERE (year1 = 2014.0)

A veces, dbplyr creará una subconsulta donde no es necesaria porque aún no sabe cómo optimizar esa traducción. A medida que dbplyr mejore con el tiempo, estos casos se volverán más raros pero probablemente nunca desaparezcan.

21.5.8 Uniones

Si está familiarizado con las uniones de dplyr, las uniones de SQL son muy similares. Aquí hay un ejemplo simple:

flights |> 
  left_join(planes |> rename(year_built = year), by = "tailnum") |> 
  show_query()
#> <SQL>
#> SELECT
#>   flights.*,
#>   planes."year" AS year_built,
#>   "type",
#>   manufacturer,
#>   model,
#>   engines,
#>   seats,
#>   speed,
#>   engine
#> FROM flights
#> LEFT JOIN planes
#>   ON (flights.tailnum = planes.tailnum)

Lo principal a notar aquí es la sintaxis: las uniones SQL usan subcláusulas de la cláusula FROM para traer tablas adicionales, usando ON para definir cómo se relacionan las tablas.

Los nombres de dplyr para estas funciones están tan estrechamente relacionados con SQL que puede adivinar fácilmente el SQL equivalente para inner_join(), right_join() y full_join():

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
INNER JOIN planes ON (flights.tailnum = planes.tailnum)

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
RIGHT JOIN planes ON (flights.tailnum = planes.tailnum)

SELECT flights.*, "type", manufacturer, model, engines, seats, speed
FROM flights
FULL JOIN planes ON (flights.tailnum = planes.tailnum)

Es probable que necesite muchas uniones cuando trabaje con datos de una base de datos. Esto se debe a que las tablas de la base de datos a menudo se almacenan en una forma altamente normalizada, donde cada “hecho” se almacena en un solo lugar y para mantener un conjunto de datos completo para el análisis, debe navegar por una red compleja de tablas conectadas por claves primarias y externas. Si te encuentras en este escenario, el paquete dm, de Tobias Schieferdecker, Kirill Müller y Darko Bergant, es un salvavidas. Puede determinar automáticamente las conexiones entre tablas usando las restricciones que los administradores de bases de datos suelen proporcionar, visualizar las conexiones para que pueda ver lo que está pasando y generar las uniones que necesita para conectar una tabla con otra.

21.5.9 Otros verbos

dbplyr también traduce otros verbos como distinct(), slice_*() e intersect(), y una creciente selección de funciones tidyr como pivot_longer() y pivot_wider(). La forma más fácil de ver el conjunto completo de lo que está disponible actualmente es visitar el sitio web de dbplyr: https://dbplyr.tidyverse.org/reference/.

21.5.10 Ejercicios

¿A qué se traduce distinct()? ¿Qué tal head()?

Explique qué hace cada una de las siguientes consultas SQL e intente recrearlas usando dbplyr.

SELECT * 
FROM flights
WHERE dep_delay < arr_delay

SELECT *, distance / (air_time / 60) AS speed
FROM flights

21.6 Traducciones de funciones

Hasta ahora nos hemos centrado en el panorama general de cómo se traducen los verbos dplyr a las cláusulas de una consulta. Ahora vamos a acercarnos un poco y hablar sobre la traducción de las funciones R que funcionan con columnas individuales, p.ej., ¿Qué pasa cuando usas mean(x) en summarize()?

Para ayudar a ver lo que está pasando, usaremos un par de pequeñas funciones auxiliares que ejecutan summarize() o mutate() y muestran el SQL generado. Eso hará que sea un poco más fácil explorar algunas variaciones y ver cómo los resúmenes y las transformaciones pueden diferir.

summarize_query <- function(df, ...) {
  df |> 
    summarize(...) |> 
    show_query()
}
mutate_query <- function(df, ...) {
  df |> 
    mutate(..., .keep = "none") |> 
    show_query()
}

¡Vamos a sumergirnos con algunos resúmenes! Si observa el código siguiente, notará que algunas funciones de resumen, como mean(), tienen una traducción relativamente simple, mientras que otras, como median(), son mucho más complejas. La complejidad suele ser mayor para las operaciones que son comunes en las estadísticas pero menos comunes en las bases de datos.

flights |> 
  group_by(year, month, day) |>  
  summarize_query(
    mean = mean(arr_delay, na.rm = TRUE),
    median = median(arr_delay, na.rm = TRUE)
  )
#> `summarise()` has grouped output by "year" and "month". You can override
#> using the `.groups` argument.
#> <SQL>
#> SELECT
#>   "year",
#>   "month",
#>   "day",
#>   AVG(arr_delay) AS mean,
#>   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY arr_delay) AS median
#> FROM flights
#> GROUP BY "year", "month", "day"

La traducción de las funciones de resumen se vuelve más complicada cuando las usa dentro de un mutate() porque tienen que convertirse en las llamadas funciones de ventana. En SQL, convierte una función de agregación ordinaria en una función de ventana agregando OVER después de ella:

flights |> 
  group_by(year, month, day) |>  
  mutate_query(
    mean = mean(arr_delay, na.rm = TRUE),
  )
#> <SQL>
#> SELECT
#>   "year",
#>   "month",
#>   "day",
#>   AVG(arr_delay) OVER (PARTITION BY "year", "month", "day") AS mean
#> FROM flights

En SQL, la cláusula GROUP BY se usa exclusivamente para resúmenes, por lo que aquí puede ver que la agrupación se ha movido desde el argumento PARTITION BY a OVER.

Las funciones de ventana incluyen todas las funciones que miran hacia adelante o hacia atrás, como lead() y lag() que miran el valor “anterior” o “siguiente” respectivamente:

flights |> 
  group_by(dest) |>  
  arrange(time_hour) |> 
  mutate_query(
    lead = lead(arr_delay),
    lag = lag(arr_delay)
  )
#> <SQL>
#> SELECT
#>   dest,
#>   LEAD(arr_delay, 1, NULL) OVER (PARTITION BY dest ORDER BY time_hour) AS lead,
#>   LAG(arr_delay, 1, NULL) OVER (PARTITION BY dest ORDER BY time_hour) AS lag
#> FROM flights
#> ORDER BY time_hour

Aquí es importante arreglar, arrange(), los datos, porque las tablas SQL no tienen un orden intrínseco. De hecho, si no usa arrange(), ¡podría recuperar las filas en un orden diferente cada vez! Aviso para las funciones de ventana, la información de pedido se repite: la cláusula ORDER BY de la consulta principal no se aplica automáticamente a las funciones de ventana.

Otra función SQL importante es CASE WHEN. Se usa como la traducción de if_else() y case_when(), la función dplyr que inspiró directamente. Aquí hay un par de ejemplos simples:

flights |> 
  mutate_query(
    description = if_else(arr_delay > 0, "delayed", "on-time")
  )
#> <SQL>
#> SELECT CASE WHEN (arr_delay > 0.0) THEN 'delayed' WHEN NOT (arr_delay > 0.0) THEN 'on-time' END AS description
#> FROM flights
flights |> 
  mutate_query(
    description = 
      case_when(
        arr_delay < -5 ~ "early", 
        arr_delay < 5 ~ "on-time",
        arr_delay >= 5 ~ "late"
      )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (arr_delay < -5.0) THEN 'early'
#> WHEN (arr_delay < 5.0) THEN 'on-time'
#> WHEN (arr_delay >= 5.0) THEN 'late'
#> END AS description
#> FROM flights

CASE WHEN también se usa para algunas otras funciones que no tienen una traducción directa de R a SQL. Un buen ejemplo de esto es cut():

flights |> 
  mutate_query(
    description =  cut(
      arr_delay, 
      breaks = c(-Inf, -5, 5, Inf), 
      labels = c("early", "on-time", "late")
    )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (arr_delay <= -5.0) THEN 'early'
#> WHEN (arr_delay <= 5.0) THEN 'on-time'
#> WHEN (arr_delay > 5.0) THEN 'late'
#> END AS description
#> FROM flights

dbplyr también traduce funciones comunes de manipulación de cadenas y fecha y hora, sobre las que puede obtener información en vignette("funcion-de-traduccion", package = "dbplyr"). Las traducciones de dbplyr ciertamente no son perfectas, y hay muchas funciones de R que aún no están traducidas, pero dbplyr hace un trabajo sorprendentemente bueno al cubrir las funciones que usará la mayor parte del tiempo..

21.7 Resumen

En este capítulo aprendió cómo acceder a los datos de las bases de datos. Nos enfocamos en dbplyr, un “backend” de dplyr que le permite escribir el código dplyr con el que está familiarizado y hacer que se traduzca automáticamente a SQL. Usamos esa traducción para enseñarle un poco de SQL; es importante aprender algo de SQL porque es el lenguaje más utilizado para trabajar con datos y conocer algunos le facilitará la comunicación con otras personas de datos que no usan R. Si ha terminado este capítulo y desea obtener más información sobre SQL. Tenemos dos recomendaciones:

SQL for Data Scientists de Renée M. P. Teate es una introducción a SQL diseñada específicamente para las necesidades de los científicos de datos e incluye ejemplos del tipo de datos altamente interconectados que es probable que encuentre en organizaciones reales.
Practical SQL de Anthony DeBarros está escrito desde la perspectiva de un periodista de datos (un científico de datos especializado en contar historias convincentes) y entra en más detalles sobre cómo obtener sus datos en una base de datos y ejecutar su propio DBMS.

En el próximo capítulo, aprenderemos sobre otro backend de dplyr para trabajar con datos de gran tamaño: arrow. Arrow está diseñado para trabajar con archivos grandes en disco y es un complemento natural para las bases de datos.

SQL es pronunciado “s”-“q”-“l” o “sequel”.↩︎
Por lo general, esta es la única función que usará del paquete del cliente, por lo que recomendamos usar :: para extraer esa función, en lugar de cargar el paquete completo con library().↩︎
Al menos, todas las tablas que tiene permiso para ver.↩︎
De manera confusa, según el contexto, SELECT es una declaración o una cláusula. Para evitar esta confusión, generalmente usaremos la consulta SELECT en lugar de la instrucción SELECT.↩︎
Ok, técnicamente, solo se requiere SELECT, ya que puedes escribir consultas como SELECT 1+1 para realizar cálculos básicos. Pero si quieres trabajar con datos (¡como siempre lo haces!) también necesitarás una cláusula FROM.↩︎
Esto no es coincidencia: el nombre de la función dplyr se inspiró en la cláusula SQL.↩︎