R: Conexões com bancos de dados R

Q: P: Como faço para me conectar a um servidor MySQL remoto?

R: dbConnect(RMariaDB::MariaDB(), dbname, host, user, password). Use a variável de ambiente Sys.getenv("DB_PASSWORD") para a senha, a fim de evitar codificá-la diretamente no código.

Q: P: Como o dplyr converte automaticamente o SQL?

R: Use tbl(con, "table") para criar uma referência “preguiçosa”; as operações do dplyr serão automaticamente convertidas em SQL e executadas no banco de dados. Por fim, use collect() para trazer os resultados de volta para a memória do R. Isso é essencial para analisar grandes conjuntos de dados.

Q: P: Devo usar dbWriteTable ou SQL para gravar no banco de dados?

R: dbWriteTable(con, "table", df, overwrite = TRUE) é simples e prático; para gravações complexas, use dbExecute(con, "INSERT...") ou SQL parametrizado.

Q: P: Como posso evitar vazamentos de conexão?

R: Use a função on.exit(dbDisconnect(con)) para fechar conexões automaticamente; ou use o pool de conexões do pacote pool (recomendado para produção).

Última atualização: 2026-08-26

Nas três aulas anteriores, aprendemos sobre dados em arquivos (CSV, Excel, JSON). No entanto, 90% dos dados em nível corporativo são armazenados em bancos de dados — como MySQL, PostgreSQL, SQL Server e Oracle. Nesta aula, aprenderemos como conectar o R a um banco de dados, consultar dados usando SQL e gravar data frames em um banco de dados.

Ao concluir esta lição, você será capaz de consultar uma tabela de banco de dados com milhões de linhas usando o R e gravar os resultados da análise de volta no banco de dados.

1. O que você vai aprender

Fundamentos de bancos de dados (bancos de dados relacionais, SQL)
Especificação da interface DBI e drivers ODBC
Banco de dados local RSQLite (não requer servidor) Conectar-se ao MySQL/PostgreSQL/SQL Server
dbGetQuery / dbExecute / dbReadTable
dbWriteTable: Gravar em um data frame
O dplyr traduz automaticamente o SQL
Práticas de produção: pool de conexões, desconexão de conexões

2. A história de um analista de dados

(1) Problema: Os dados são armazenados no banco de dados

Bob é um analista que precisa recuperar dados do banco de dados MySQL da empresa:

SQL

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
ORDER BY total DESC
LIMIT 100;

Ele executa o SQL no Navicat → exporta um arquivo CSV → lê o arquivo CSV no R. Isso leva 5 minutos todas as vezes. Se ele conectasse o R diretamente ao banco de dados—

(2) Solução usando R

library(DBI)
library(RMySQL)

# 1. Connect to the Database
con <- dbConnect(RMySQL::MySQL(),
                 dbname = "shop",
                 host = "localhost",
                 user = "root",
                 password = "secret")

# 2. Run straight ahead SQL
result <- dbGetQuery(con, "
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY customer_id
  ORDER BY total DESC
  LIMIT 100
")

# 3. or use dplyr Translation SQL
library(dplyr)
result2 <- tbl(con, "orders") |>
  filter(order_date >= "2024-01-01") |>
  group_by(customer_id) |>
  summarise(total = sum(amount)) |>
  arrange(desc(total)) |>
  collect()  # Forget it R Memory

# 4. Disconnect
dbDisconnect(con)

1 linha para a conexão + 1 linha de SQL. Esse é o poder das conexões com bancos de dados no R.

3. O ecossistema de bancos de dados do R

(1) 4 pacotes principais

Pacote	Função	Dependências
DBI	Especificação de interface de banco de dados (API unificada)	R puro
RSQLite	Driver do SQLite (banco de dados local)	R puro (não requer servidor)
RMySQL / RMariaDB	Driver do MySQL/MariaDB	Requer a biblioteca do cliente MySQL
RPostgreSQL	Driver do PostgreSQL	Requer a biblioteca cliente do PostgreSQL
odbc	Interface Universal ODBC	Requer a instalação de um driver ODBC
dbplyr	Tradução de SQL do dplyr	DBI

(2) Instalação

# 1. Core Interfaces(Required)
install.packages("DBI")

# 2. Local Database(No server required,Recommended for beginners)
install.packages("RSQLite")

# 3. Production Database(On Demand)
install.packages("RMySQL")        # MySQL
install.packages("RMariaDB")      # MariaDB(Recommended Alternatives RMySQL)
install.packages("RPostgreSQL")   # PostgreSQL
install.packages("odbc")          # General ODBC

4. RSQLite: Banco de dados local (recomendado para iniciantes)

(1) O que é o SQLite?

graph LR
    A["SQLite"] --> B["Serverless"]
    A --> C["Single-File Storage"]
    A --> D["Embedded"]
    A --> E["Zero Configuration"]
    A --> F["Python/R/Excel Can be read"]
    
    style A fill:#d4edda
    style B fill:#cce5ff
    style C fill:#f8d7da
    style D fill:#e1d4ff

SQLite = banco de dados baseado em arquivo (cada arquivo é um banco de dados), que não requer servidor nem configuração alguma. O SQLite vem integrado em smartphones, iPhones, dispositivos Android, Python e R.

(2) Criar/Conectar-se a um banco de dados

library(DBI)
library(RSQLite)

# 1. Create/Connect(If the file does not exist, it will be created automatically.)
con <- dbConnect(SQLite(), "my_database.db")

# 2. Disconnect(Important!Disconnect after use)
dbDisconnect(con)

# 3. Temporary Database(In memory,Restart Failed)
con <- dbConnect(SQLite(), ":memory:")

(3) Gravação em um DataFrame

# Prepare the data
sales <- data.frame(
  id = 1:5,
  product = c("A", "B", "A", "C", "B"),
  amount = c(100, 200, 150, 300, 250)
)

con <- dbConnect(SQLite(), "shop.db")

# Write to Table(Coverage:overwrite / Add:append)
dbWriteTable(con, "sales", sales, overwrite = TRUE)

# Verification
dbListTables(con)
# [1] "sales"

(4) Leitura de dados

# Method 1:Read the entire table
df <- dbReadTable(con, "sales")
print(df)

# Method 2:Execute SQL
result <- dbGetQuery(con, "SELECT * FROM sales WHERE amount > 150")
print(result)

# Method 3:dplyr(Lazy Query,Finally collect)
library(dplyr)
result2 <- tbl(con, "sales") |>
  filter(amount > 150) |>
  collect()

(5) Outras operações

# 1. List all tables
dbListTables(con)
# [1] "sales" "products" "customers"

# 2. Does the table exist?
dbExistsTable(con, "sales")
# [1] TRUE

# 3. Delete Table
dbRemoveTable(con, "sales")

# 4. View Table Fields
dbListFields(con, "sales")
# [1] "id" "product" "amount"

# 5. Commit Transaction
dbCommit(con)
dbRollback(con)

5. Conectar-se ao banco de dados de produção

MySQL/MariaDB

# MySQL
library(RMySQL)
con <- dbConnect(MySQL(),
                 dbname = "shop",
                 host = "localhost",
                 port = 3306,
                 user = "root",
                 password = "your_password")

# MariaDB(Recommendations,Open Source)
library(RMariaDB)
con <- dbConnect(MariaDB(),
                 dbname = "shop",
                 host = "localhost",
                 port = 3306,
                 user = "root",
                 password = "your_password")

PostgreSQL

library(RPostgreSQL)
con <- dbConnect(PostgreSQL(),
                 dbname = "shop",
                 host = "localhost",
                 port = 5432,
                 user = "postgres",
                 password = "your_password")

(3) ODBC (Conexão com o SQL Server/Oracle)

library(odbc)
con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 Server = "localhost",
                 Database = "shop",
                 UID = "sa",
                 PWD = "your_password")

⚠️ Observação: Para o banco de dados de produção, é necessário instalar primeiro a biblioteca cliente do banco de dados correspondente (por exemplo, libmysqlclient-dev para o MySQL).

6. dbplyr: a tradução automática de SQL do dplyr

(1) Conceito central

graph LR
    A["dplyr Chain Operation"] --> B["dbplyr Translate to SQL"]
    B --> C["Database Execution"]
    C --> D["collect Pull back R Memory"]
    
    style A fill:#cce5ff
    style B fill:#d4edda
    style C fill:#f8d7da
    style D fill:#fff3cd

(2) Aplicação prática

library(dplyr)
library(dbplyr)  # Load Translator

# 1. Create a "lazy" table(Doesn't actually query the database)
orders <- tbl(con, "orders")

# 2. Write dplyr Code(Will not be executed immediately)
query <- orders |>
  filter(order_date >= "2024-01-01", amount > 100) |>
  group_by(customer_id) |>
  summarise(
    total = sum(amount),
    n_orders = n()
  ) |>
  arrange(desc(total)) |>
  head(100)

# 3. View the translation SQL
query |> show_query()
# SELECT `customer_id`, SUM(`amount`) AS `total`, COUNT(*) AS `n_orders`
# FROM `orders`
# WHERE (`order_date` >= '2024-01-01') AND (`amount` > 100.0)
# GROUP BY `customer_id`
# ORDER BY `total` DESC
# LIMIT 100

# 4. Forget it R Memory
result <- query |> collect()

💡 Dica: tbl() + dplyr + collect() é a combinação perfeita para análise de bancos de dados — não é preciso escrever SQL manualmente; o código R é automaticamente convertido em SQL e executado no banco de dados.

(3) Vantagens de desempenho

Execução no banco de dados versus carregamento na memória do R:

Volume de dados	Carregar no R	Executar no banco de dados
@.000 linhas de	0,1 s	0,1 s (sem diferença)
@.000 linhas de	10s	0,5s (o banco de dados possui índices)
100 milhões de linhas	Congelamento	5s (capacidade de processamento do banco de dados)

💡 Dica: Sempre use tbl() + collect() para análises de tabelas grandes — isso traz apenas os resultados para o R, e não a tabela inteira.

7. Gravando um DataFrame em um banco de dados

(1) dbWriteTable

# 1. Create/Overview Table
dbWriteTable(con, "sales_summary", summary_df, overwrite = TRUE)

# 2. Append to the existing table
dbWriteTable(con, "sales_log", new_data, append = TRUE)

# 3. Temporary Table(Automatically delete at the end of the session)
dbWriteTable(con, "temp_data", df, temporary = TRUE)

# 4. Line Name Processing
dbWriteTable(con, "df", df, row.names = FALSE)  # Do not include the line number

(2) Aplicação prática

# Read R Data → Cleaning → Write back to the database
library(dplyr)
library(readr)

# 1. Read CSV
sales <- read_csv("sales.csv")

# 2. Cleaning
clean_sales <- sales |>
  filter(!is.na(amount)) |>
  mutate(date = as.Date(date)) |>
  group_by(region, product) |>
  summarise(total = sum(amount))

# 3. Write back to the database
con <- dbConnect(SQLite(), "shop.db")
dbWriteTable(con, "sales_by_region_product", clean_sales, overwrite = TRUE)

# 4. Verification
result <- dbGetQuery(con, "SELECT * FROM sales_by_region_product LIMIT 5")
print(result)

8. Prática de Produção

(1) Melhores práticas para configuração de conexão

# 1. Use .Renviron Save Password(Don't put it in the code)
# Add to ~/.Renviron:
# DB_PASSWORD=your_password
password <- Sys.getenv("DB_PASSWORD")

# 2. Connection Configuration Encapsulation
db_connect <- function() {
  dbConnect(RMariaDB::MariaDB(),
            dbname = "shop",
            host = Sys.getenv("DB_HOST", "localhost"),
            port = as.integer(Sys.getenv("DB_PORT", 3306)),
            user = Sys.getenv("DB_USER", "root"),
            password = Sys.getenv("DB_PASSWORD"))
}

# 3. Use withConnection Pattern
con <- db_connect()
on.exit(dbDisconnect(con))  # Automatically disconnect when the function ends

(2) Tratamento de erros

# 1. Simple tryCatch
result <- tryCatch(
  {
    con <- dbConnect(SQLite(), "shop.db")
    dbGetQuery(con, "SELECT * FROM sales LIMIT 10")
  },
  error = function(e) {
    message("Database Error:", e$message)
    NULL
  },
  finally = {
    if (exists("con") && !is.null(con)) dbDisconnect(con)
  }
)

# 2. Check the connection
if (dbIsValid(con)) {
  cat("Connection is normal\n")
} else {
  cat("Connection Lost\n")
}

(3) Operações em lote

# 1. Bulk Insert(Transactions)
dbBegin(con)
for (chunk in split(data, ceiling(seq_len(nrow(data)) / 1000))) {
  dbWriteTable(con, "big_table", chunk, append = TRUE)
}
dbCommit(con)

# 2. Progress Bar
library(progress)
pb <- progress_bar$new(total = nrow(data))
for (i in seq_len(nrow(data))) {
  dbExecute(con, "INSERT INTO log VALUES (?, ?)", params = list(data$id[i], data$msg[i]))
  pb$tick()
}

9. Exemplo completo: Análise do banco de dados de vendas do SQLite

A seguir, apresentamos um exemplo de um fluxo de trabalho completo que reúne todos os conceitos abordados nesta aula.

▶ Exemplo: Análise abrangente do banco de dados de vendas locais

R 📖 Somente leitura

# ============================================
# Comprehensive Analysis of the Local Sales Database
# Features:Use RSQLite Database Creation,Look up data,Analysis
# ============================================

library(DBI)
library(RSQLite)
library(dplyr)

# 1. Create a Database + Write Initial Data
con <- dbConnect(SQLite(), "shop_demo.db")
dbWriteTable(con, "customers", data.frame(
  id = 1:5,
  name = c("Alice", "Bob", "Charlie", "Diana", "Eve"),
  city = c("Beijing", "Shanghai", "Guangzhou", "Beijing", "Shenzhen"),
  register_date = as.Date("2023-01-01") + c(0, 30, 60, 90, 120)
), overwrite = TRUE)

dbWriteTable(con, "orders", data.frame(
  id = 1:20,
  customer_id = sample(1:5, 20, replace = TRUE),
  order_date = as.Date("2024-01-01") + sample(0:90, 20),
  amount = sample(100:2000, 20)
), overwrite = TRUE)

cat("=== Table Structure ===\n")
print(dbListTables(con))

# 2. SQL Search:Total Order Amount for Each Customer
cat("\n=== SQL Search(Total Customer Orders)===\n")
result_sql <- dbGetQuery(con, "
  SELECT c.name, c.city, COUNT(o.id) AS n_orders, SUM(o.amount) AS total
  FROM customers c
  LEFT JOIN orders o ON c.id = o.customer_id
  GROUP BY c.id
  ORDER BY total DESC
")
print(result_sql)

# 3. The same query using dplyr(dbplyr Translation SQL)
cat("\n=== dplyr Search(Automatic Translation SQL)===\n")
customers_tbl <- tbl(con, "customers")
orders_tbl <- tbl(con, "orders")

result_dplyr <- customers_tbl |>
  left_join(orders_tbl, by = c("id" = "customer_id")) |>
  group_by(id, name, city) |>
  summarise(
    n_orders = n(),
    total = sum(amount)
  ) |>
  arrange(desc(total)) |>
  collect()

print(result_dplyr)

# 4. Complex Analysis:Monthly Sales Trends
cat("\n=== Monthly Sales Trends ===\n")
monthly_sales <- orders_tbl |>
  mutate(month = format(order_date, "%Y-%m")) |>
  group_by(month) |>
  summarise(
    orders = n(),
    revenue = sum(amount),
    avg_amount = round(mean(amount), 2)
  ) |>
  collect()
print(monthly_sales)

# 5. Identify High-Value Customers
cat("\n=== High-value customers(Order Value > 2000)===\n")
vip_customers <- customers_tbl |>
  left_join(orders_tbl, by = c("id" = "customer_id")) |>
  group_by(id, name, city) |>
  summarise(total = sum(amount, na.rm = TRUE)) |>
  filter(total > 2000) |>
  arrange(desc(total)) |>
  collect()
print(vip_customers)

# 6. Write the analysis results back to the database
dbWriteTable(con, "vip_customers", vip_customers, overwrite = TRUE)
dbWriteTable(con, "monthly_sales", monthly_sales, overwrite = TRUE)
cat("\n=== The report has been saved to the database. ===\n")
print(dbListTables(con))

# 7. Verify the table to which data is written back
cat("\n=== Verification vip_customers ===\n")
vip_reloaded <- dbReadTable(con, "vip_customers")
print(vip_reloaded)

# 8. Disconnect
dbDisconnect(con)
cat("\n=== The database connection has been lost ===\n")

# 9. Clean Up Files
file.remove("shop_demo.db")

70 linhas de lógica (limite de 40, somente leitura)

Resultado esperado (trecho):

TEXT 📖 Somente leitura

=== SQL Search(Total Customer Orders)===
     name city n_orders total
1    Eve   Shenzhen        5  5180
2  Diana   Beijing        5  4643
3 Charlie Guangzhou        4  4156
4    Bob   Shanghai        3  3461
5  Alice   Beijing        3  3033

=== Monthly Sales Trends ===
    month orders revenue avg_amount
1  2024-01      4     5239    1309.75
2  2024-02      5     6491    1298.20
...

❓ Perguntas Frequentes

P: O que é o DBI? Por que ele deve ser instalado primeiro? R: O DBI é a especificação da interface de banco de dados do R. Todos os drivers de banco de dados (RSQLite, RMySQL, RPostgreSQL) seguem a especificação do DBI. Instale o DBI antes de instalar os drivers.

P: O RSQLite não requer um servidor? R: Sim! O SQLite é um banco de dados baseado em arquivo — um único arquivo .db constitui um banco de dados completo. Ele não requer nenhuma configuração e é ideal para desenvolvimento local, prototipagem e fins educacionais.

P: Como faço para me conectar a um servidor MySQL remoto? R: dbConnect(RMariaDB::MariaDB(), dbname, host, user, password). Use a variável de ambiente Sys.getenv("DB_PASSWORD") para a senha, a fim de evitar codificá-la diretamente no código.

P: Como o dplyr converte automaticamente o SQL? R: Use tbl(con, "table") para criar uma referência “preguiçosa”; as operações do dplyr serão automaticamente convertidas em SQL e executadas no banco de dados. Por fim, use collect() para trazer os resultados de volta para a memória do R. Isso é essencial para analisar grandes conjuntos de dados.

P: Devo usar dbWriteTable ou SQL para gravar no banco de dados? R: dbWriteTable(con, "table", df, overwrite = TRUE) é simples e prático; para gravações complexas, use dbExecute(con, "INSERT...") ou SQL parametrizado.

P: Como posso evitar vazamentos de conexão? R: Use a função on.exit(dbDisconnect(con)) para fechar conexões automaticamente; ou use o pool de conexões do pacote pool (recomendado para produção).

📖 Resumo

O DBI é uma especificação de interface para bancos de dados R; drivers específicos (RSQLite, RMariaDB, RPostgreSQL) implementam essa especificação.
RSQLite: Uma introdução recomendada — Não requer servidor, é feito inteiramente em R, banco de dados baseado em arquivo (um único arquivo .db)
5 funções principais: dbConnect Conectar / dbDisconnect Desconectar / dbGetQuery Consultar SQL / dbWriteTable Gravar / dbReadTable Ler tabela inteira
O dbplyr é uma verdadeira revolução: tbl(con, "table") + dplyr + collect() Converte automaticamente código R em SQL
Gravar: Sobrescrever com dbWriteTable(con, "name", df, overwrite = TRUE) / Acrescentar com append = TRUE
Prática de produção: Use a senha Sys.getenv() e as transações dbBegin/dbCommit/dbRollback e on.exit para se desconectar.
Para análises de tabelas grandes, sempre use tbl() + collect() — faça os cálculos apenas no banco de dados; não carregue na memória do R

📝 Exercícios

Exercício básico: Use o RSQLite para criar um banco de dados local test.db, insira uma tabela de dados (5 linhas, 3 colunas) e use dbReadTable para recuperá-la e verificar se os dados estão completos.
Questões básicas: Execute as três consultas SQL a seguir no banco de dados da questão anterior: ① SELECT * FROM table ② SELECT COUNT(*) FROM table ③ SELECT col, COUNT(*) FROM table GROUP BY col.
Exercício básico: Use dbExecute para criar uma nova tabela (contendo os campos id, nome e idade), use dbWriteTable para inserir 5 linhas de dados e use dbRemoveTable para excluí-las. Verifique cada operação.
Exercício avançado: Utilizando o banco de dados de vendas de amostra (duas tabelas: customers e orders), use a tabela tbl() + dplyr + collect() para: ① Identificar os clientes com mais de 3 pedidos; ② Calcular os totais mensais de vendas; ③ Identificar os 3 clientes que mais gastaram. Faça uma captura de tela e salve-a.
Desafio: Use dbplyr para traduzir uma operação complexa do dplyr para SQL: ① vários filtros ② group_by + summarise ③ arrange + head ④ junção interna de duas tabelas. Use show_query() para visualizar a string de SQL e verificar se ela corresponde ao código SQL escrito à mão.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós