0Pricing
R Academy · Leçon

dbplyr : SQL avec la syntaxe dplyr

Écrivez du code dplyr traduit en SQL et exécuté sur la base de données.

dbplyr : SQL avec la syntaxe dplyr est une leçon R Academy gratuite sur CoddyKit. Ceci est la leçon 3 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage R Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours R Academy comprend 4 leçons au total.

Qu’est-ce que dbplyr ?

dbplyr est un package R qui traduit automatiquement les verbes dplyr en SQL. Au lieu d’écrire du SQL brut, vous écrivez du code dplyr familier, puis dbplyr le convertit dans le dialecte SQL approprié pour votre moteur de base de données. La requête s’exécute dans la base de données, et non dans la mémoire de 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')

Se connecter à une base de données

dbplyr fonctionne au-dessus d’une connexion DBI. Vous établissez la connexion avec DBI::dbConnect() en utilisant le package de pilote approprié, puis vous transmettez cet objet de connexion aux fonctions de 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() — Référencer une table de base de données

tbl(con, 'table_name') crée une référence différée vers une table de base de données. Aucune donnée n’est encore récupérée : vous obtenez simplement un pointeur. Vous pouvez ensuite lui appliquer des verbes dplyr en chaîne, et dbplyr construira progressivement la requête SQL.

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')

Appliquer filter() à une table de base de données

Vous pouvez enchaîner filter() sur une référence tbl(), comme vous le feriez avec un bloc de données local. dbplyr le traduit en clause SQL WHERE. Le filtrage s’effectue dans la base de données : seules les lignes correspondantes sont envoyées à 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))

Appliquer select() et mutate()

select() correspond à SELECT en SQL, tandis que mutate() correspond aux colonnes calculées de la clause SELECT. dbplyr prend en charge la traduction, y compris pour de nombreuses expressions R courantes ayant un équivalent 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() et summarise() — Agrégation

group_by() et summarise() sont traduits en GROUP BY SQL avec des fonctions d’agrégation. Vous pouvez ainsi calculer des résumés dans la base de données sans charger d’abord toutes les lignes dans R, ce qui est essentiel pour les grandes tables.

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() — Examiner le SQL généré

show_query() affiche le SQL que dbplyr enverra à la base de données. Cette fonction est extrêmement utile pour déboguer, optimiser les performances et apprendre le SQL en observant la traduction de votre code dplyr.

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() — Récupérer les données dans R

collect() exécute la requête différée et récupère les résultats dans un bloc de données R local (tibble). Tant que vous n’appelez pas collect(), aucune donnée ne passe de la base de données à R : toutes les opérations sont traduites en SQL et exécutées côté serveur.

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() — Envoyer un bloc de données local vers la base

copy_to() écrit un bloc de données R local dans la base de données sous forme de table (généralement temporaire). C’est utile pour joindre des tables de référence locales à de grandes tables distantes ou pour effectuer des tests sans base de données préexistante.

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())

Joindre des tables de base de données

Vous pouvez joindre deux références tbl() avec les mêmes fonctions left_join(), inner_join(), etc. que celles de dplyr. dbplyr les traduit en clauses SQL JOIN : la jointure s’effectue dans la base de données.

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))

Quand ne pas utiliser dbplyr

dbplyr ne peut pas traduire toutes les expressions R en SQL. Les fonctions personnalisées complexes, la manipulation des dates avec R de base ou les fonctions statistiques propres à R peuvent ne pas avoir d’équivalent SQL. Utilisez d’abord collect() pour récupérer les données dans R, puis appliquez localement les opérations propres à 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

Vérification rapide

Après avoir construit une chaîne de verbes dplyr sur une référence de base de données tbl(), quelle fonction exécute la requête et renvoie les résultats sous forme de bloc de données R local ?

dbplyr — Points clés

dbplyr vous permet d’interroger des bases de données avec la syntaxe dplyr, sans SQL :

  • tbl(con, 'table') — référence différée vers une table de base de données
  • filter(), select(), mutate() — traduits en clauses SQL
  • group_by() |> summarise() — devient GROUP BY en SQL
  • show_query() — examiner le SQL généré (idéal pour apprendre)
  • collect() — exécuter la requête et récupérer les données dans R
  • copy_to() — envoyer un bloc de données local vers la base de données
  • Les jointures fonctionnent également : left_join(), inner_join(), etc.
  • Filtrez et agrégez dans la base de données ; utilisez R uniquement pour ce que SQL ne peut pas faire
# 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()

Questions Fréquemment Posées

La leçon « dbplyr : SQL avec la syntaxe dplyr » est-elle gratuite ?

Oui — le texte complet de « dbplyr : SQL avec la syntaxe dplyr » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours R Academy, passe à CoddyKit PRO. Le cours R Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « dbplyr : SQL avec la syntaxe dplyr » ?

Écrivez du code dplyr traduit en SQL et exécuté sur la base de données. Tu pratiques R Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer R Academy ?

Aucune expérience préalable n'est requise. R Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 3 sur 4.

Combien de temps prend la leçon « dbplyr : SQL avec la syntaxe dplyr » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon R Academy ?

Oui. Chaque leçon R Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. Bases de DBI et RSQLite
  2. Se connecter à PostgreSQL et MySQL
  3. dbplyr : SQL avec la syntaxe dplyr
  4. Requêtes paramétrées et transactions
← Retour à R Academy