R Academy · Aula

dbplyr: SQL com a sintaxe do dplyr

Escreva código dplyr que seja traduzido para SQL e executado no banco de dados.

Aula 3 de 413 etapas

dbplyr: SQL com a sintaxe do dplyr é uma aula grátis de R Academy no CoddyKit. Esta é a aula 3 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de R Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de R Academy inclui 4 aulas no total.

O que é dbplyr?

dbplyr é um pacote R que traduz automaticamente verbos do dplyr para SQL. Em vez de escrever SQL puro, você escreve um código dplyr familiar, e o dbplyr o converte para o dialeto SQL correto do seu sistema de banco de dados. A consulta é executada no banco de dados — não na memória do R.

# install.packages(c('dbplyr', 'DBI', 'RSQLite'))
library(DBI)
library(dbplyr)
library(dplyr)

# dbplyr sits between dplyr and your database:
# Your dplyr code -> dbplyr -> SQL -> Database -> result

# Supported backends: PostgreSQL, MySQL, SQLite,
#                     SQL Server, BigQuery, Snowflake, ...

cat('dbplyr translates dplyr to SQL')

Conectando-se a um banco de dados

O dbplyr funciona sobre uma conexão DBI. Você estabelece a conexão com DBI::dbConnect() usando o pacote de driver apropriado e, em seguida, passa esse objeto de conexão para as funções do dbplyr.

library(DBI)
library(dplyr)
library(dbplyr)

# SQLite example (no server needed — great for demos)
con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Write a test table to the in-memory database
copy_to(con, nycflights13::flights, 'flights',
        temporary = FALSE, overwrite = TRUE)

# PostgreSQL example (real server):
# con <- dbConnect(
#   RPostgres::Postgres(),
#   host     = 'db.example.com',
#   dbname   = 'analytics',
#   user     = Sys.getenv('DB_USER'),
#   password = Sys.getenv('DB_PASS')
# )

cat('Connected to database')

tbl() — Referenciando uma tabela do banco de dados

tbl(con, 'table_name') cria uma referência adiada a uma tabela do banco de dados. Nenhum dado é buscado ainda — você apenas obtém um ponteiro. Depois, você pode encadear verbos do dplyr a essa referência, e o dbplyr construirá a consulta SQL gradualmente.

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
mtcars_df <- mtcars
dbWriteTable(con, 'cars', mtcars_df)

# Create a lazy table reference
cars_tbl <- tbl(con, 'cars')

# Printing shows top rows and indicates it is a database source
print(cars_tbl)
# Source: table<cars> [?? x 11]
# Database: sqlite 3.x [:memory:]

cat('tbl() = lazy reference, no data fetched yet')

Aplicando filter() a uma tabela do banco de dados

Você pode encadear filter() a uma referência tbl(), assim como faria com um quadro de dados local. O dbplyr o traduz para uma cláusula SQL WHERE. A filtragem ocorre no banco de dados — somente as linhas correspondentes serão enviadas ao R.

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Filter in the database (generates WHERE clause)
high_mpg <- cars_tbl |>
  filter(mpg > 25, cyl == 4)

# Still lazy! No data fetched yet.
cat('Class:', class(high_mpg)[1], '\n')

# Collect to pull data into R:
result <- collect(high_mpg)
cat('Rows matching filter:', nrow(result))

Aplicando select() e mutate()

select() corresponde a SQL SELECT, e mutate() corresponde a colunas calculadas na cláusula SELECT. O dbplyr gerencia a tradução, incluindo muitas expressões comuns do R que têm equivalentes em SQL.

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Select specific columns + add a computed column
result <- cars_tbl |>
  filter(am == 1) |>              # manual transmission
  select(mpg, cyl, hp, wt) |>    # pick columns
  mutate(wt_kg = wt * 453.592)   # add computed column

# Pull into R
df <- collect(result)
cat('Columns:', names(df), '\n')
cat('Rows:', nrow(df))

group_by() e summarise() — Agregação

group_by() e summarise() são traduzidos para SQL GROUP BY com funções de agregação. Isso permite calcular resumos no banco de dados sem primeiro trazer todas as linhas para o R — algo essencial para tabelas grandes.

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Aggregate in the database
summary_tbl <- cars_tbl |>
  group_by(cyl) |>
  summarise(
    avg_mpg  = mean(mpg, na.rm = TRUE),
    max_hp   = max(hp),
    n_models = n()
  )

result <- collect(summary_tbl)
print(result)

show_query() — Inspecionando o SQL gerado

show_query() exibe o SQL que o dbplyr enviará ao banco de dados. Isso é extremamente útil para depuração, ajuste de desempenho e aprendizado de SQL, pois permite ver como seu código dplyr é traduzido.

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Build a query
q <- cars_tbl |>
  filter(mpg > 20) |>
  group_by(cyl) |>
  summarise(avg_mpg = mean(mpg, na.rm = TRUE))

# See the SQL dbplyr generated:
show_query(q)
# <SQL>
# SELECT cyl, AVG(mpg) AS avg_mpg
# FROM cars
# WHERE mpg > 20.0
# GROUP BY cyl

collect() — Trazendo dados para o R

collect() executa a consulta adiada e recupera os resultados em um quadro de dados local do R (tibble). Até você chamar collect(), nenhum dado é transferido do banco de dados para o R — todas as operações são traduzidas para SQL e executadas no servidor.

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Build up a lazy query chain
lazy_q <- cars_tbl |>
  filter(hp > 100) |>
  select(mpg, hp, cyl) |>
  arrange(desc(hp))

# Nothing fetched yet
cat('Is lazy?', inherits(lazy_q, 'tbl_sql'), '\n')

# NOW pull data into R
local_df <- collect(lazy_q)
cat('Class after collect:', class(local_df)[1], '\n')
cat('Rows:', nrow(local_df))

copy_to() — Enviando um quadro de dados local para o banco

copy_to() grava um quadro de dados local do R no banco de dados como uma tabela (geralmente temporária). Isso é útil para unir tabelas locais de consulta a tabelas remotas grandes ou para realizar testes sem um banco de dados preexistente.

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')

# Push a local data frame into the database
local_df <- data.frame(
  cyl       = c(4, 6, 8),
  category  = c('efficient', 'balanced', 'powerful')
)

# copy_to creates a temporary table in the DB
copy_to(con, local_df, name = 'cyl_labels', temporary = TRUE)

# Now reference it with tbl()
labels_tbl <- tbl(con, 'cyl_labels')
cat('Rows in DB table:', collect(labels_tbl) |> nrow())

Unindo tabelas do banco de dados

Você pode unir duas referências tbl() usando as mesmas funções left_join(), inner_join() etc. do dplyr. O dbplyr as traduz para cláusulas SQL JOIN — a união é executada no banco de dados.

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
copy_to(con, data.frame(cyl = c(4,6,8),
                         label = c('four','six','eight')),
        'cyl_ref', temporary = TRUE)

cars_tbl  <- tbl(con, 'cars')
labels_tbl <- tbl(con, 'cyl_ref')

# Join in the database
joined <- left_join(cars_tbl, labels_tbl, by = 'cyl') |>
  select(mpg, cyl, label, hp)

show_query(joined)   # See the SQL JOIN
result <- collect(joined)
cat('Joined rows:', nrow(result))

Quando NÃO usar dbplyr

O dbplyr não consegue traduzir todas as expressões do R para SQL. Funções personalizadas complexas, manipulação de datas do R básico ou funções estatísticas específicas do R podem não ter equivalentes em SQL. Primeiro use collect() para trazer os dados para o R e, em seguida, aplique localmente as operações exclusivas do R.

library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'cars', mtcars)
cars_tbl <- tbl(con, 'cars')

# Do as much as possible in the database:
prepped <- cars_tbl |>
  filter(mpg > 15) |>
  select(mpg, hp, cyl) |>
  collect()               # <- pull only what you need

# Now apply R-only operations locally:
cor_result <- cor(prepped$mpg, prepped$hp)
cat('Correlation mpg~hp:', round(cor_result, 3))

# Rule: filter and aggregate in DB, model in R

Verificação rápida

Depois de criar uma cadeia de verbos do dplyr em uma referência de banco de dados tbl(), qual função executa a consulta e retorna os resultados como um quadro de dados local do R?

Responda na aula interativa

dbplyr — Principais conclusões

O dbplyr permite consultar bancos de dados com a sintaxe do dplyr — sem exigir SQL:

tbl(con, 'table') — referência adiada a uma tabela do banco de dados
filter(), select(), mutate() — traduzidos para cláusulas SQL
group_by() |> summarise() — torna-se SQL GROUP BY
show_query() — inspeciona o SQL gerado (ótimo para aprender)
collect() — executa a consulta e traz os dados para o R
copy_to() — envia um quadro de dados local para o banco de dados
As uniões também funcionam: left_join(), inner_join() etc.
Filtre e agregue no banco de dados; use o R apenas para o que o SQL não consegue fazer

# Complete dbplyr workflow example:
library(DBI)
library(dplyr)
library(dbplyr)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'sales', data.frame(
  region  = c('North','South','North','East','South'),
  revenue = c(100, 200, 150, 300, 250),
  year    = c(2023, 2023, 2024, 2024, 2024)
))

tbl(con, 'sales') |>
  filter(year == 2024) |>
  group_by(region) |>
  summarise(total = sum(revenue, na.rm = TRUE)) |>
  arrange(desc(total)) |>
  collect() |>
  print()

Grátis para começar

Aprenda R com um tutor de IA — grátis

Escreva e execute código real no seu navegador, obtenha ajuda instantânea de um tutor de IA 24/7 e continue de onde parou na web ou no app.

Cursos: 43
Aulas: 159

Começar a aprender grátis Ver o curso completo

Perguntas Frequentes

A aula “dbplyr: SQL com a sintaxe do dplyr” é grátis?

Sim — o texto completo de “dbplyr: SQL com a sintaxe do dplyr” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de R Academy, atualize para CoddyKit PRO. O curso de R Academy inclui 4 aulas no total.

O que vou aprender em “dbplyr: SQL com a sintaxe do dplyr”?

Escreva código dplyr que seja traduzido para SQL e executado no banco de dados. Você pratica R Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar R Academy?

Nenhuma experiência prévia é necessária. R Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 3 de 4.

Quanto tempo leva a aula “dbplyr: SQL com a sintaxe do dplyr”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de R Academy?

Sim. Cada aula de R Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

← Voltar para R Academy