21  ✅ Bancos de dados

21.1 Introdução

Um grande volume de dados são armazenados em bancos de dados (databases), portanto, é essencial que você saiba como acessá-los. Em alguns casos você pode pedir para alguém fazer uma cópia para um .csv para você, mas isto se torna problemático rapidamente: toda vez que você precisar fazer uma mudança, você precisará se comunicar com outra pessoa. Você deve ser capaz de acessar diretamente o banco de dados para obter os dados necessários quando quiser.

Neste capítulo, você aprenderá primeiro o básico do pacote DBI: como utilizá-lo para se conectar a um banco de dados e então obter dados através de uma consulta SQL1. SQL é uma abreviação de structured query language, e é a lingua franca dos bancos de dados, portanto uma linguagem muito importante que todas pessoas que praticam ciência de dados devem aprender. Dito isto, não iremos começar com SQL, mas sim, iremos ensinar você sobre o dbplyr, um pacote capaz de traduzir código dplyr para código SQL. Faremos isto de maneira a ensinar algumas das características mais importantes do SQL. Você não se tornará um especialista em SQL ao final deste capítulo, mas será capaz de identificar a maioria de seus componentes principais e entender o que fazem.

21.1.1 Pré-requisitos

Neste capítulo, faremos uma introdução aos pacotes DBI e dbplyr. DBI é uma interface de baixo nível que se conecta aos bancos de dados e executa SQL; dbplyr é uma interface de alto nível que traduz seu código dplyr para um código de consultas SQL e então as executa através do DBI.

21.2 O básico sobre bancos de dados

Em um nível mais simples, você pode imaginar um banco de dados como uma coleção de data frames, que são chamados de tabelas na terminologia dos bancos de dados. Assim como um data frame, uma tabela de um banco de dados é uma coleção de colunas com nomes, onde cada valor na coluna tem o mesmo tipo. Existem três diferenças de alto nível entre data frames e as tabelas de um banco de dados:

  • Tabelas são armazenadas em disco e podem ser arbitrariamente grandes. Data frames são armazenados na memória do computador, e são fundamentalmente limitados (apesar deste limite ser suficientemente grande para muitos problemas).

  • Tabelas quase sempre possuem um índice. Assim como um índice de um livro, um índice no banco de dados torna possível encontrar rapidamente as linhas de interesse, sem a necessidade de examinar uma a uma todas as linhas. Data frames e tibbles não possuem índices, porém data.tables possuem, e esta é umas das razões para serem tão rápidos.

  • Muitos bancos de dados clássicos são otimizados para coletar dados rapidamente, mas não para analizar dados existentes. Estes bancos de dados são chamados orientados à linhas (row-oriented), pois os dados são armazenados linha-a-linha ao invés de coluna-por-coluna como no R. Mais recentemente, tem-se visto o desenvolvimento de bancos de dados orientados à colunas (column-oriented), o que torna a análise de dados existentes mais rápida.

Bancos de dados são executados por sistemas de gerenciamento de banco de dados (SGBD), os quais podem ser de três categorias:

  • SGBDs Cliente-servidor rodam em um servidor central poderoso, ao qual você se conecta do seu computador (o cliente). Eles são muito bons para compartilhar dados com várias pessoas em uma organização. SGBDs cliente-servidor populares incluem o PostgreSQL, MariaDB, SQL Server e Oracle.
  • SGBDs na Nuvem (Cloud), como o Snowflake, RedShift da Amazon e o BigQuery do Google, são similares aos SGBDs cliente-servidor, mas eles rodam na nuvem. Isto significa que eles podem facilmente lidar com bancos de dados extremamente grandes e podem automaticamente obter mais recursos computacionais quando necessário.
  • SGBDs No-processo (In-process), como o SQLite ou duckdb, rodam inteiramente em seu computador. Eles são ótimos para trabalhar com conjunto de dados grandes nos quais você é o principal usuário.

21.3 Conectando a um banco de dados

Para se conectar a um banco de dados pelo R, você usa um par de pacotes:

  • Você sempre usuará o DBI (database interface), pois este fornece um conjunto de funções genéricas que se conectam com os bancos de dados, enviam dados, executam consultas SQL, etc.

  • Você também usará um pacote feito especificamente para o SGBD ao qual você está se conectando. Este pacote traduz os comandos genéricos do DBI para as necessidades específicas de um determinado SGBD. Existe geralmente um pacote para cada SGBD, ex: RPostgres para PostgreSQL e RMariaDB para MySQL.

Se você não puder encontrar um pacote específico para seu SGBD, você pode utilizar o pacote odbc. Este usa o protocolo ODBC suportado por muitos SGBDs. odbc exige uma configuração um pouco mais elaborada, pois você deve instalar o driver ODBC e informar o pacote odbc onde encontrá-lo.

Efetivamente, você cria uma conexão com o banco de dados usando DBI::dbConnect(). O primeiro argumento seleciona o SGBD2, e os argumentos seguintes descrevem como se conectar a ele (ou seja, onde está localizado e as credenciais necessárias para acessá-lo). O código a seguir demonstra alguns exemplos típicos:

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

Os detalhes precisos de conexão variam muito de SGBD para SGBD, portanto infelizmente não poderemos cobrir todos aqui. Isto significa que você deverá fazer um pouco de pesquisa por conta própria. Geralmente, você pode perguntar a outras pessoas cientistas de dados do time ou falar com a pessoa que administra o banco de dados (DBA) (database administrator). A configuração inicial geralmente exige um pouco de ajuste (e talvez um pouco de pesquisa no Google) para ser feita corretamente, mas em geral você precisará fazer uma única vez.

21.3.1 Neste livro

Configurar um SGBD cliente-servidor ou SGBD da nuvem neste livro seria bastante chato, portanto usaremos um SGBD “no-processo” que vem inteiramente em um pacote do R: duckdb. Graças à magia do DBI, a única diferença entre usar o duckdb e outro SGBD é como você se conectará ao banco de dados. Isto se torna muito bom para ensinar, pois você pode executar facilmente este código e, da mesma forma, aplicar o que aprendeu em outros lugares com facilidade.

Conectar ao duckdb é particularmente simples, pois por padrão, um banco de dados temporário é criado e depois removido ao sair do R. Isto é muito bom para o aprendizado, pois garante que você iniciará de um estado limpo toda vez que reiniciar o R:

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

duckdb é um banco de dados de alto desempenho desenhado muito para as necessidades da pessoa cientista de dados. O usaremos aqui pois é muito fácil de se iniciar, mas também porque é capaz de lidar com gigabytes de dados com grande velocidade. Se você quiser usar o duckdb em um projeto de análise de dados real, será necessário incluir o argumento dbdir para um banco de dados persistente e dizer ao duckdb onde salvar. Assumindo que você está usando um projeto (Capítulo 6), é razoável armazená-lo no diretório duckdb do projeto atual:

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

21.3.2 Carregando alguns dados

Já que este é um banco de dados novo, precisaremos começar adicionando alguns dados. Aqui adicionaremos os conjuntos de dados milhas e diamante do pacote dados usando DBI::dbWriteTable(). O exemplo de uso mais simples de dbWriteTable() precisa de três argumentos: uma conexão ao banco de dados, o nome da tabela a ser criada no banco de dados e um data frame com os dados.

dbWriteTable(con, "milhas", dados::milhas)
dbWriteTable(con, "diamante", dados::diamante)

Se você está usando o duckdb em um projeto real, recomendamos fortemente ler sobre duckdb_read_csv() e duckdb_register_arrow(). Estas funções te dão formas poderosas e de alto desempenho para carregar dados diretamente ao duckdb sem ter que os carregar primeiro no R. Nós também demonstraremos uma técnica útil para carregar vários arquivos em um banco de dados na Seção 26.4.1.

21.3.3 O básico do DBI

Você pode confirmar se os dados foram carregados corretamente usando um par de funções do DBI: dbListTables() lista todas as tabelas da banco de dados3 e dbReadTable() retorna o conteúdo de uma tabela.

dbListTables(con)
#> [1] "diamante" "milhas"

con |> 
  dbReadTable("diamante") |> 
  as_tibble()
#> # A tibble: 53,940 × 10
#>   preco quilate corte     cor   transparencia profundidade tabela     x     y
#>   <int>   <dbl> <fct>     <fct> <fct>                <dbl>  <dbl> <dbl> <dbl>
#> 1   326    0.23 Ideal     E     SI2                   61.5     55  3.95  3.98
#> 2   326    0.21 Premium   E     SI1                   59.8     61  3.89  3.84
#> 3   327    0.23 Bom       E     VS1                   56.9     65  4.05  4.07
#> 4   334    0.29 Premium   I     VS2                   62.4     58  4.2   4.23
#> 5   335    0.31 Bom       J     SI2                   63.3     58  4.34  4.35
#> 6   336    0.24 Muito Bom J     VVS2                  62.8     57  3.94  3.96
#> # ℹ 53,934 more rows
#> # ℹ 1 more variable: z <dbl>

dbReadTable() retorna um data.frame, portanto usamos as_tibble() para converter em um tibble para que imprima de forma mais bonita na tela.

Se você já sabe SQL, você pode usar dbGetQuery() pata obter os resultados de uma consulta executada no banco de dados:

sql <- "
  SELECT quilate, corte, transparencia, cor, preco 
  FROM diamante 
  WHERE preco > 15000
"
as_tibble(dbGetQuery(con, sql))
#> # A tibble: 1,655 × 5
#>   quilate corte     transparencia cor   preco
#>     <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  Muito Bom VVS2          G     15013
#> 6    1.73 Muito Bom VS1           G     15014
#> # ℹ 1,649 more rows

Se você nunca viu SQL antes, não se preocupe! Em breve você aprenderá mais sobre isto. Mas se você ler atentamente, você pode adivinhar que estamos selecionando cinco colunas do conjunto de dados diamante e todas as linhas onde o preco é maior que 15.000.

21.4 O básico do dbplyr

Agora que você se conectou ao banco de dados e carregou alguns dados, você pode começar a aprender sobre dbplyr. dbplyr roda por tráz do dplyr, isso significa que você continua a escrever códigos dplyr, mas o backend o executa de maneira diferente. Neste caso, o dbplyr traduz para SQL; existem outros backends incluindo dtplyr que traduz o código para data.table e multidplyr que executa o código em vários núcleos (cores).

Para usar dbplyr, primeiro você usa tbl() para criar um objeto que representa a tabela do banco de dados:

diamantes_bd <- tbl(con, "diamante")
diamantes_bd
#> # Source:   table<diamante> [?? x 10]
#> # Database: DuckDB v0.10.0 [root@Darwin 23.6.0:R 4.3.3/:memory:]
#>   preco quilate corte     cor   transparencia profundidade tabela     x     y
#>   <int>   <dbl> <fct>     <fct> <fct>                <dbl>  <dbl> <dbl> <dbl>
#> 1   326    0.23 Ideal     E     SI2                   61.5     55  3.95  3.98
#> 2   326    0.21 Premium   E     SI1                   59.8     61  3.89  3.84
#> 3   327    0.23 Bom       E     VS1                   56.9     65  4.05  4.07
#> 4   334    0.29 Premium   I     VS2                   62.4     58  4.2   4.23
#> 5   335    0.31 Bom       J     SI2                   63.3     58  4.34  4.35
#> 6   336    0.24 Muito Bom J     VVS2                  62.8     57  3.94  3.96
#> # ℹ more rows
#> # ℹ 1 more variable: z <dbl>

Existem duas outras formas comuns de interagir com banco de dados. Primeiro, muitas organizações possuem banco de dados muito grandes, então você precisa de alguma hierarquia para manter todas as tabelas organizadas. Neste caso, você deve precisar fornecer um esquema (schema) ou um catálogo (catalog) e um esquema, de maneira a obter a tabela que você tem interesse:

diamantes_bd <- tbl(con, in_schema("vendas", "diamante"))
diamantes_bd <- tbl(con, in_catalog("america_norte", "vendas", "diamante"))

Outras vezes você pode querer usar seu próprio SQL como ponto de partida:

diamantes_bd <- tbl(con, sql("SELECT * FROM diamante"))

Este é um objeto lazy; quando você usa verbos do dpluyr nele, dplyr não faz nada, ele apenas registra a sequência de operações que você deseja executar e as executa apenas quando necessário. Por exemplo, veja o seguinte pipeline:

grandes_diamantes_bd <- diamantes_bd |> 
  filter(preco > 15000) |> 
  select(quilate:transparencia, preco)

grandes_diamantes_bd
#> # Source:   SQL [?? x 5]
#> # Database: DuckDB v0.10.0 [root@Darwin 23.6.0:R 4.3.3/:memory:]
#>   quilate corte     cor   transparencia preco
#>     <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  Muito Bom G     VVS2          15013
#> 6    1.73 Muito Bom G     VS1           15014
#> # ℹ more rows

Você pode dizer que este objeto representa uma consulta ao banco de dados pois imprime o nome do SGBD no topo e apesar de também dizer o número de colunas, geralmente não sabe o número de linhas. Isso ocorre porque encontrar o número total de linhas geralmente requer a execução da consulta completa, algo que estamos tentando evitar.

Você também pode ver o código SQL gerado através da função show_query() do dplyr. Se você conhece dplyr, esta é uma boa maneira de aprender SQL! Escreva algum código dplyr, deixe o dbplyr traduzir para o SQL, e então tente entender como as duas linguagens se relacionam.

grandes_diamantes_bd |>
  show_query()
#> <SQL>
#> SELECT quilate, corte, cor, transparencia, preco
#> FROM diamante
#> WHERE (preco > 15000.0)

Para puxar os dados de volta para o R, você chama a função collect(). Por trás dos panos, isto gera o SQL, chama dbGetQuery() para obter os dados, e então transforma o resultado em um tibble:

grandes_diamantes <- grandes_diamantes_bd |> 
  collect()
grandes_diamantes
#> # A tibble: 1,655 × 5
#>   quilate corte     cor   transparencia preco
#>     <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  Muito Bom G     VVS2          15013
#> 6    1.73 Muito Bom G     VS1           15014
#> # ℹ 1,649 more rows

Geralmente, você usará dbplyr para selecionar os dados que você deseja do conjunto de dados, efetuar alguns filtros básicos e agregações usando as traduções descritas abaixo. Então, assim que estiver pronto para analisar os dados com as funções que são únicas do R, você chamará collect() para obter os dados em um tibble na memória do seu computador, e continuará o trabalho com código R puro.

21.5 SQL

O resto do capítulo irá te ensinar um pouco de SQL pelas lentes do dbplyr. É uma introdução não tradicional ao SQL, mas esperamos que te leve rapidamente a um conhecimento básico. Felizmente, se você conhece dplyr você está em um bom lugar para rapidamente aprender SQL, pois muitos conceitos são iguais.

Nós iremos explorar o relacionamento entre dplyr e SQL usando alguns velhos amigos do pacote dados: voos e avioes. Estes conjuntos de dados são fáceis para usar no aprendizado sobre bancos de dados, e podemos copiá-las 4:

# Copiando as tabelas para o banco de dados
dbWriteTable(con, name = "voos", value = voos)
dbWriteTable(con, name = "avioes", value = avioes)

# Lendo as tabelas do banco de dados
voos <- tbl(con, "voos")
avioes <- tbl(con, "avioes")

21.5.1 O básico do SQL

Os componentes de nível mais alto do SQL são chamados declarações (statements). Declarações comuns incluem CREATE para definir novas tableas, INSERT para adicionar dados e SELECT para retornar dados. Iremos focar em declarações SELECT, também chamadas de consultas (queries), pois são quase que exclusivamente o que você precisa para atuar em ciência de dados.

Um consulta é formada por cláusulas (clauses). Existem cinco cláusulas importantes: SELECT, FROM, WHERE, ORDER BY e GROUP BY. Toda consulta precisa ter cláusulas SELECT5 e FROM6 e a consulta mais simples é SELECT * FROM tabela, que seleciona todas colunas de determinada tabela . Isto é o que dbplyr gera de uma tabela sem mudanças :

voos |> show_query()
#> <SQL>
#> SELECT *
#> FROM voos
avioes |> show_query()
#> <SQL>
#> SELECT *
#> FROM avioes

WHERE e ORDER BY controlam quais linhas serão incluídas e como estarão ordenadas:

voos |> 
  filter(destino == "IAH") |> 
  arrange(atraso_saida) |>
  show_query()
#> <SQL>
#> SELECT voos.*
#> FROM voos
#> WHERE (destino = 'IAH')
#> ORDER BY atraso_saida

GROUP BY converte a consulta em uma sumarização, fazendo com que aconteça uma agregação:

voos |> 
  group_by(destino) |> 
  summarize(atraso_saida = mean(atraso_saida, na.rm = TRUE)) |> 
  show_query()
#> <SQL>
#> SELECT destino, AVG(atraso_saida) AS atraso_saida
#> FROM voos
#> GROUP BY destino

Existem duas principais diferenças entre verbos dplyr e cláusulas SELECT:

  • No SQL, a maiúscula e minúscula não fazem diferença: você pode escrever select, SELECT ou até SeLeCt. Neste livro ficaremos com a convenção comum de escrever as palavras-chaves SQL usando letras maiúsculas para distinguir de nomes de tabelas ou variáveis.
  • No SQL, a ordem importa: você sempre deve escrever as declarações em ordem SELECT, FROM, WHERE, GROUP BY, ORDER BY. É um pouco confuso, pois esta ordem não é a mesma de como as declarações são realmente avaliadas com FROM em primeiro, depois WHERE, GROUP BY, SELECT e ORDER BY.

As seções seguintes exploram com mais detalhes cada cláusula.

Observe que apesar do SQL ser um padrão, ele é extramamente complexo e nenhum banco de dados o segue exatamente. Enquanto os componentes principais que iremos focar neste livro são muito similares entre os SGBDs, há muitas pequenas variações. Felizmente, dbplyr é desenhado para gerenciar este problema e gerar diferentes traduções para diferentes bancos de dados. Não é perfeito, mas está melhorando continuamente e se você encontrar um problema, pode abrir um caso (issue) no GitHub para nos ajudar a melhorar.

21.5.2 SELECT

A cláusula SELECT é o motor das consultas e faz o mesmo trabalho que select(), mutate(), rename(), relocate() e, como você aprenderá na próxima seção, summarize().

select(), rename() e relocate() tem uma tradução muito direta para o SELECT já que apenas afetam onde uma coluna aparece (e se aparece) assim como seu nome:

avioes |> 
  select(codigo_cauda, tipo, fabricante, modelo, ano) |> 
  show_query()
#> <SQL>
#> SELECT codigo_cauda, tipo, fabricante, modelo, ano
#> FROM avioes

avioes |> 
  select(codigo_cauda, tipo, fabricante, modelo, ano) |> 
  rename(ano_construcao = ano) |> 
  show_query()
#> <SQL>
#> SELECT codigo_cauda, tipo, fabricante, modelo, ano AS ano_construcao
#> FROM avioes

avioes |> 
  select(codigo_cauda, tipo, fabricante, modelo, ano) |> 
  relocate(fabricante, modelo, .before = tipo) |> 
  show_query()
#> <SQL>
#> SELECT codigo_cauda, fabricante, modelo, tipo, ano
#> FROM avioes

Este exemplo também mostra como SQL renomeia. Na terminologia SQL, renomear é chamado de aliasing e é feito com AS. Note que diferente de mutate(), o nome antigo vai ao lado esquerdo e o novo nome ao lado direito.

Nos exemplos acima, se tivéssemos os nomes de "ano" e "tipo", elas apareceriam entre aspas duplas. Isto é devido a year e type serem palavras reservadas (reserved words) no duckdb, então dbplyr coloca aspas para evitar potencial confusão entre nome de colunas/tabelas e os operadores SQL.

Quando estiver trabalhando com outros bancos de dados é provavel que você veja todas as variáveis com aspas, pois apenas alguns poucos pacotes clientes, como o duckdb, sabem quais são todas as palavras reservadas, então eles colocam aspas em todas para evitar problemas.

SELECT "codigo_cauda", "tipo", "fabricante", "modelo", "ano"
FROM "avioes"

Alguns outros bancos de dados usam a crase ao invés de aspas duplas:

SELECT `codigo_cauda`, `tipo`, `fabricante`, `modelo`, `ano`
FROM `avioes`

A tradução para mutate() é da mesma forma bastante direta: cada variável se torna uma nova expressão no SELECT:

voos |> 
  mutate(
    velocidade = distancia / (tempo_voo / 60)
  ) |> 
  show_query()
#> <SQL>
#> SELECT voos.*, distancia / (tempo_voo / 60.0) AS velocidade
#> FROM voos

Retornaremos para a tradução de componentes individuais (como /) na Seção 21.6.

21.5.3 FROM

A cláusula FROM define a fonte de dados. Será um pouco desinteressante por um período, pois estamos usando apenas uma única tabela. Você verá exemplos mais complexos quando chegarmos nas funções de união (join).

21.5.4 GROUP BY

group_by() é traduzido como a clásula GROUP BY7 e summarize() é traduzido como a cláusula SELECT:

diamantes_bd |> 
  group_by(corte) |> 
  summarize(
    n = n(),
    avg_price = mean(preco, na.rm = TRUE)
  ) |> 
  show_query()
#> <SQL>
#> SELECT corte, COUNT(*) AS n, AVG(preco) AS avg_price
#> FROM diamante
#> GROUP BY corte

Retornaremos em o que acontece com a tradução de n() e mean() na Seção 21.6.

21.5.5 WHERE

filter() é traduzido como a cláusula WHERE:

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

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

Existem alguns detalhes importantes a serem observados aqui:

  • | se torna OR e & se torna AND.
  • SQL usa = para comparação, e não ==. SQL não possui atribuição (assignment), portanto não há potencial para confusão aqui.
  • SQL usa somente '' para strings, não usa "". No SQL, "" é usado para identificar variáveis, como a `` do R.

Outro operator SQL útil é o IN, o qual se parece muito com o %in%do R:

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

SQL usa NULL ao invés de NA. NULL se comporta de forma similar ao NA. A principal diferença é que enquanto são considerados nas comparações e aritmética, eles são silenciosamente ignorados quando sumarizados. dbplyr irá te lembrar disto a primeira vez que você encontrar:

voos |> 
  group_by(destino) |> 
  summarize(atraso = mean(atraso_chegada))
#> 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.0 [root@Darwin 23.6.0:R 4.3.3/:memory:]
#>   destino atraso
#>   <chr>    <dbl>
#> 1 CLT      7.36 
#> 2 MDW     12.4  
#> 3 HOU      7.18 
#> 4 SDF     12.7  
#> 5 LAS      0.258
#> 6 PHX      2.10 
#> # ℹ more rows

Se você quiser saber mais em como o NULL funciona, você irá gostar do artigo “Three valued logic” de Markus Winand.

Em geral, você pode trabalhar com NULL usando as funções que você usaria para NA no R:

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

Esta consulta SQL ilustra uma das desvatagens do dbplyr: apesar do SQL estar correto, ela não é tão simples quanto se estivesse sido escrita à mão. Neste caso, você poderia eliminar os parênteses e usar um operador especial que é mais simples de se ler:

WHERE "atraso_saida" IS NOT NULL

Note que se você usar filter() em uma variável que você criou usando um summarize, dbplyr irá gerar uma cláusula HAVING, ao invés de uma cláusula WHERE. Esta é uma das indiosincrasias do SQL: WHERE é avaliado antes do SELECT e GROUP BY, então o SQL precisa de uma outra cláusula que seja avaliada depois.

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

21.5.6 ORDER BY

Ordenar linhas involve uma tradução direta de arrange() para a cláusula ORDER BY:

voos |> 
  arrange(ano, mes, dia, desc(atraso_saida)) |> 
  show_query()
#> <SQL>
#> SELECT voos.*
#> FROM voos
#> ORDER BY ano, mes, dia, atraso_saida DESC

Note como desc() é traduzido para DESC: esta é uma das muitas funções dplyr cujo o nome foi diretamente inspirado pelo SQL.

21.5.7 Subconsultas

Algumas vezes não é possível traduzir um pipeline dplyr em uma única declaração SELECT e você precisa usar uma subconsulta. Uma subconsulta é apenas uma consulta usada como uma fonte de dados na cláusula FROM ao invés de uma tabela normal.

O dbplyr tipicamente usa subconsultas para solucionar paleativamente limitações do SQL. Por exemplo, expressões na cláusula SELECT não podem referenciar colunas que acabaram de ser criadas. Isto significa que o seguinte pipeline (muito simples) precisa acontecer em duas etapas: a primeira consulta (interna) computa ano1 e então a segunda consulta (externa) pode computar ano2.

voos |> 
  mutate(
    ano1 = ano + 1,
    ano2 = ano1 + 1
  ) |> 
  show_query()
#> <SQL>
#> SELECT q01.*, ano1 + 1.0 AS ano2
#> FROM (
#>   SELECT voos.*, ano + 1.0 AS ano1
#>   FROM voos
#> ) q01

Você também verá isso se tentar filtrar com filter() a variável que você criou recentemente. Lembre-se que, apesar de WHERE ser escrita depois de SELECT, ela é avaliada antes, por isso você precisa de uma subconsulta neste simples exemplo:

voos |> 
  mutate(ano1 = ano + 1) |> 
  filter(ano1 == 2014) |> 
  show_query()
#> <SQL>
#> SELECT q01.*
#> FROM (
#>   SELECT voos.*, ano + 1.0 AS ano1
#>   FROM voos
#> ) q01
#> WHERE (ano1 = 2014.0)

Algumas vezes, dbplyr irá criar uma subconsulta mesmo onde não é necessário, pois não sabe ainda como otimizar tal tradução. Conforme dbplyr melhora, estes casos vão ficando mais raros, mas provavelmente nunca desaparecerão por completo.

21.5.8 Uniões (Joins)

Se você está familiarizado com uniões (joins) com dplyr, as uniões do SQL são bem parecidas. Aqui está um exemplo simples:

voos |> 
  left_join(avioes |> rename(ano_construcao = ano), by = "codigo_cauda") |> 
  show_query()
#> <SQL>
#> SELECT
#>   voos.*,
#>   avioes.ano AS ano_construcao,
#>   tipo,
#>   fabricante,
#>   modelo,
#>   motores,
#>   assentos,
#>   velocidade,
#>   tipo_motor
#> FROM voos
#> LEFT JOIN avioes
#>   ON (voos.codigo_cauda = avioes.codigo_cauda)

A principal coisa a se notar aqui é a sintaxe: as uniões SQL usam sub-cláusulas da cláusula FROM para associar tabelas adicionais, usando ON para definir como as tabelas estão relacionadas.

Os nomes das funções dplyr são tão parecidas com as do SQL que você pode facilmente adivinhar o SQL equivalente para inner_join(), right_join() e full_join():

SELECT voos.*, "tipo", fabricante, modelo, motores, assentos, velocidade
FROM voos
INNER JOIN avioes ON (voos.codigo_cauda = avioes.codigo_cauda)

SELECT voos.*, "tipo", fabricante, modelo, motores, assentos, velocidade
FROM voos
RIGHT JOIN avioes ON (voos.codigo_cauda = avioes.codigo_cauda)

SELECT voos.*, "tipo", fabricante, modelo, motores, assentos, velocidade
FROM voos
FULL JOIN avioes ON (voos.codigo_cauda = avioes.codigo_cauda)

É muito provavel que você precise de muitas uniões (joins) quando trabalhar com dados de um banco de dados. Isto porque tabelas são frequentemente armazenadas de uma forma altamente normalizada, onde cada “fato” é armazenado em um único local e para manter um conjunto de dados completo para análise, você precisa navegar por uma rede complexa de tabelas conectadas por chaves primárias (primary key) e chaves estrangeiras (foreign key). Se você encontrar este cenário, o pacote dm, de Tobias Schieferdecker, Kirill Müller e Darko Bergant é um salva-vidas. Ele pode automaticamente determinar as conexões entre as tabelas usando restrições (constraints) que as pessoas que adminstram bancos de dados (DBAs) geralmente fornecem, gera visualizações para que você entenda o que está acontecendo e gera as uniões (joins) que você precisa para conectar uma tabela à outra.

21.5.9 Outros verbos

O dbplyr também traduz outros verbos como distinct(), slice_*(), intersect(), e uma seleção crescente de funções do tidyr como pivot_longer() e pivot_wider(). O jeito mais fácil de ver a lista completa do que está disponível no momento é visitando o website dbplyr: https://dbplyr.tidyverse.org/reference/.

21.5.10 Exercícios

  1. Em que se traduz a distinct()? E a head()?

  2. Explique o que cada um desses comandos SQL fazem e tente recriá-los usando dbplyr.

    SELECT * 
FROM voos
WHERE atraso_saida < atraso_chegada

SELECT *, distancia / (tempo_voo / 60) AS velocidade
FROM voos

21.6 Tradução de funções

Até agora focamos na visão geral de como os verbos dplyr são traduzidos para cláusulas em uma consulta. Agora iremos focar um pouco mais e falar sobre a tradução de funções do R que trabalham com colunas, por exemplo, o que acontece quando você usa mean(x) em um summarize()?

Para ajudar a entender o que acontece, usaremos algumas funções de ajuda que chamam um summarize() ou mutate() and mostram o SQL produzido. Isto tornará um pouco mais fácil explorar algumas variações e ver como sumarizações e transformações podem ser diferentes.

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

Vamos mergulhar em algumas sumarizações! Olhando o código abaixo, você perceberá que algumas funções de sumarização como mean(), tem uma tradução relativamente simples, enquanto outras, como median() são muito mais complexas. Esta complexidade é geralmente maior para operações que são comuns na estatística mas menos comuns em bancos de dados.

voos |> 
  group_by(ano, mes, dia) |>  
  consulta_summarize(
    media = mean(atraso_chegada, na.rm = TRUE),
    mediana = median(atraso_chegada, na.rm = TRUE)
  )
#> `summarise()` has grouped output by "ano" and "mes". You can override using
#> the `.groups` argument.
#> <SQL>
#> SELECT
#>   ano,
#>   mes,
#>   dia,
#>   AVG(atraso_chegada) AS media,
#>   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY atraso_chegada) AS mediana
#> FROM voos
#> GROUP BY ano, mes, dia

A tradução de funções de sumarização se tornam mais complicadas quando você as usa dentro de um mutate() pois elas se transformam naquilo que conhecemos como funções de janela (window functions). No SQL, você transforma uma funções normal em uma função de janela adicionando OVER depois dela:

voos |> 
  group_by(ano, mes, dia) |>  
  consulta_mutate(
    media = mean(atraso_chegada, na.rm = TRUE),
  )
#> <SQL>
#> SELECT
#>   ano,
#>   mes,
#>   dia,
#>   AVG(atraso_chegada) OVER (PARTITION BY ano, mes, dia) AS media
#> FROM voos

No SQL, a clásula GROUP BY é usada exclusivamente para sumarizações, portanto aqui você pode ver que o agrupamento foi movido do argumento PARTITION BY para o OVER.

Funções de janela incluem todas as funções que olham para trás ou para frente como lead() e lag() que olham para os valores “anteriores” ou “posteriores” respectivamente:

voos |> 
  group_by(destino) |>  
  arrange(data_hora) |> 
  consulta_mutate(
    lead = lead(atraso_chegada),
    lag = lag(atraso_chegada)
  )
#> <SQL>
#> SELECT
#>   destino,
#>   LEAD(atraso_chegada, 1, NULL) OVER (PARTITION BY destino ORDER BY data_hora) AS lead,
#>   LAG(atraso_chegada, 1, NULL) OVER (PARTITION BY destino ORDER BY data_hora) AS lag
#> FROM voos
#> ORDER BY data_hora

Aqui é importante ordenar com arrange() os dados, pois tabelas SQL não possuem um ordem intrínseca. Na verdade, se você não usar arrange() você pode obter resultados em ordens diferentes toda vez! Note que nas funções de janela, a ordenação da informação se repete: a cláusula ORDER BY da consulta principal não se aplica automaticamente às funções de janela.

Outra função SQL importante é a CASE WHEN. É usada para traduzir as funções if_else() e case_when() que dplyr se inspirou diretamente. Aqui estão alguns exemplos simples:

voos |> 
  consulta_mutate(
    descricao = if_else(atraso_chegada > 0, "atrasado", "no horario")
  )
#> <SQL>
#> SELECT CASE WHEN (atraso_chegada > 0.0) THEN 'atrasado' WHEN NOT (atraso_chegada > 0.0) THEN 'no horario' END AS descricao
#> FROM voos

voos |> 
  consulta_mutate(
    descricao = 
      case_when(
        atraso_chegada < -5 ~ "adiantado", 
        atraso_chegada < 5 ~ "no horario",
        atraso_chegada >= 5 ~ "atrasado"
      )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (atraso_chegada < -5.0) THEN 'adiantado'
#> WHEN (atraso_chegada < 5.0) THEN 'no horario'
#> WHEN (atraso_chegada >= 5.0) THEN 'atrasado'
#> END AS descricao
#> FROM voos

CASE WHEN também é usado para algumas funções que não tem tradução direta do R para o SQL. Um bom exemplo disso é cut():

voos |> 
  consulta_mutate(
    descricao =  cut(
      atraso_chegada, 
      breaks = c(-Inf, -5, 5, Inf), 
      labels = c("adiantado", "no horario", "atrasado")
    )
  )
#> <SQL>
#> SELECT CASE
#> WHEN (atraso_chegada <= -5.0) THEN 'adiantado'
#> WHEN (atraso_chegada <= 5.0) THEN 'no horario'
#> WHEN (atraso_chegada > 5.0) THEN 'atrasado'
#> END AS descricao
#> FROM voos

O dbplyr também traduz funções comuns de manipulação de string e data/hora (datetime), as quais você pode aprender sobre na vignette("translation-function", package = "dbplyr"). As traduções do dbplyr certamente não são perfeitas, e ainda existem muitas funções do R que ainda não são traduzidas, mas o dbplyr faz um surpreendente bom trabalho em traduzir as funções que você usará na maior parte do tempo.

21.7 Resumo

Neste capítulo você aprendeu como acessar dados de um banco de dados. Nós focamos no dbplyr, um “backend” do dplyr que permite que você escreva o código dplyr que está familiarizado e o tenha automaticamente traduzido em SQL. Nós utilizamos esta tradução para te ensinar um pouco de SQL; é importante aprender SQL pois é a linguagem mais comumente utilizada para se trabalhar com dados e conhecê-la um pouco facilitrá sua comunicação com outras pessoas de dados que não usam o R. Se você terminou este capítulo e gostaria de aprender mais sobre SQL. Nós temos duas recomendações:

  • SQL for Data Scientists de Renée M. P. Teate é uma introdução ao SQL desenhada especificamente para as necessidades de cientistas de dados e inclui exemplos com dados altamente interconectados que você geralmente encontra em organizações reais.
  • Practical SQL de Anthony DeBarros é escrito sob a perspectiva de uma pessoa jornalista de dados (cientista de dados especialista em contar histórias atraentes) e entra em mais detalhes sobre ter seus dados em um banco de dados e rodar seu próprio SGBD.

No próximo capítulo, você aprenderá sobre outro backend dplyr para trabalhar com grandes volumes de dados: arrow. Arrow é desenhado para trabalhar com grandes arquivos em disco e é um complemento natural aos bancos de dados.

  1. SQL é pronunciado “s”-“q”-“l”.↩︎

  2. Normalmente, esta é a única função que você usará do pacote cliente, por isso recomendamos usar o :: para referenciar esta função específica, ao invés de carregar todo o pacote usando library().↩︎

  3. Ao menos, todas as tabelas que você tem permissão para ver.↩︎

  4. Nota de tradução: O pacote dbplyr vem com a função dbplyr::copy_nycflights13() que copia as tabelas originais, em inglês, para o banco de dados. Como estamos utilizando os dados traduzidos, disponíveis no pacote dados, vamos copiar as tabelas traduzidas para o banco de dados utilizando a função dbWriteTable() do pacote DBI.↩︎

  5. É estranho, mas dependendo do contexto, SELECT pode ser tanto uma declaração quanto uma cláusula. Para evitar esta confusão, iremos usar uma consulta SELECT ao invés de um declaração SELECT.↩︎

  6. Ok, tecnicamente, somente o SELECT é necessário, já que você pode escrever consultas como SELECT 1+1 para executar cálculos básicos. Mas se você quer trabalhar com dados (como você faz sempre!) você precisará da cláusula FROM.↩︎

  7. Isto não é uma coincidência: A função dplyr foi inspirada na cláusula SQL.↩︎