Rのデータベース接続：DBIとdplyrによるSQL変換の完全ガイド

Q: データベースへの書き込みには、 dbWriteTable とSQLのどちらを使うべきですか？

dbWriteTable はシンプルで便利です。複雑な書き込みを行う場合は、 dbExecute(con, "INSERT...") またはパラメータ化されたSQLを使用してください。

Q: 接続のリークを防ぐにはどうすればよいですか？

on.exit(dbDisconnect(con)) 関数を使用して接続を自動的に閉じたり、 pool パッケージの接続プールを使用したりしてください（本番環境ではこちらを推奨します）。

これまでの3つのレッスンでは、ファイルベースのデータ（CSV、Excel、JSON）について学びました。しかし、エンタープライズレベルのデータの90％は、MySQL、PostgreSQL、SQL Server、Oracleなどのデータベースに格納されています。このレッスンでは、Rをデータベースに接続する方法、SQLを使用してデータをクエリする方法、およびデータフレームをデータベースに書き込む方法について学びます。

このレッスンを修了すると、R を使用して数百万行ものデータを含むデータベーステーブルに対してクエリを実行し、分析結果をデータベースに書き戻すことができるようになります。

1. 学習内容

データベースの基礎（リレーショナルデータベース、SQL）
DBI インターフェース仕様および ODBC ドライバ
RSQLite ローカルデータベース（サーバー不要） MySQL/PostgreSQL/SQL Server に接続する
dbGetQuery / dbExecute / dbReadTable
dbWriteTable: データフレームへの書き込み
dplyr は SQL を自動的に変換します
開発の実践：コネクションプール、接続の切断

2. データアナリストの物語

(1) 課題：データがデータベースに保存されている

ボブは、会社のMySQLデータベースからデータを取得する必要があるアナリストです：

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;

彼はNavicatでSQLを実行し、CSVファイルをエクスポートしてから、RでそのCSVファイルを読み込んでいます。この作業には毎回5分かかります。もしRをデータベースに直接接続すれば――

(2) 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行＋SQLの1行。これこそが、Rのデータベース接続の真骨頂です。

3. Rデータベースのエコシステム

(1) 4つのコアパッケージ

パッケージ	関数	依存関係
DBI	データベースインターフェース仕様（統一API）	ピュアR
RSQLite	SQLite ドライバー（ローカルデータベース）	純粋な R（サーバー不要）
RMySQL / RMariaDB	MySQL/MariaDB ドライバー	MySQL クライアントライブラリが必要
RPostgreSQL	PostgreSQL ドライバ	PostgreSQL クライアントライブラリが必要
odbc	ODBC ユニバーサルインターフェース	ODBC ドライバのインストールが必要
dbplyr	dplyrのSQL変換	DBI

(2) インストール

# 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：ローカルデータベース（初心者におすすめ）

(1) 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 = ファイルベースのデータベース（各ファイルがデータベースとなる）であり、サーバーも設定も一切必要としません。SQLiteは、スマートフォン、iPhone、Android端末、Python、Rに組み込まれています。

(2) データベースの作成／接続

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) 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) データの読み取り

# 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) その他の業務

# 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. 本番データベースに接続する

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（SQL Server／Oracleへの接続）

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

⚠️ 注意：本番用データベースの場合は、まず対応するデータベース用のクライアントライブラリ（例：MySQLの場合はlibmysqlclient-dev）をインストールする必要があります。

6. dbplyr：dplyrによるSQLの自動変換

(1) 基本概念

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) 実践的な応用

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

💡 ヒント：tbl() ＋ dplyr ＋ collect() は、データベース分析における最強の組み合わせです。SQLを手動で記述する必要はなく、Rコードが自動的にSQLに変換され、データベース上で実行されます。

(3) 性能上の利点

データベースでの実行とRのメモリへの読み込みの比較：

データ量	Rへの読み込み	データベースでの実行
@,000 行	0.1秒	0.1秒（差なし）
@,000行	10秒	0.5秒（データベースにインデックスあり）
1億行	フリーズ	5秒（データベースの処理能力）

💡 ヒント: 大規模なテーブルの分析には、必ず tbl() + collect() を使用してください—これにより、テーブル全体ではなく、結果のみが R に取り込まれます。

7. DataFrameをデータベースに書き込む

(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) 実践的な応用

# 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. 制作の実践

(1) 接続設定のベストプラクティス

# 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) エラー処理

# 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) バッチ処理

# 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. 完全な例：SQLiteの「Sales」データベースの分析

以下は、このレッスンで取り上げたすべての概念を結びつけた完全なワークフローの例です。

▶ サンプル：地域別販売データベースの包括的な分析

# ============================================
# 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")

▶ 試してみよう

期待される出力（抜粋）：

=== 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
...

❓ よくある質問

Q リモートのMySQLサーバーにはどのように接続すればよいですか？

A dbConnect(RMariaDB::MariaDB(), dbname, host, user, password)。パスワードをハードコーディングしないように、環境変数Sys.getenv("DB_PASSWORD")を使用してください。

Q データベースへの書き込みには、dbWriteTableとSQLのどちらを使うべきですか？

A dbWriteTableはシンプルで便利です。複雑な書き込みを行う場合は、dbExecute(con, "INSERT...")またはパラメータ化されたSQLを使用してください。

Q 接続のリークを防ぐにはどうすればよいですか？

A on.exit(dbDisconnect(con)) 関数を使用して接続を自動的に閉じたり、pool パッケージの接続プールを使用したりしてください（本番環境ではこちらを推奨します）。

📖 まとめ

DBI は R データベース向けのインターフェース仕様であり、特定のドライバ（RSQLite、RMariaDB、RPostgreSQL）がこの仕様を実装しています。
RSQLite：おすすめの入門ガイド—サーバー不要、純粋なR言語、ファイルベースのデータベース（単一の.dbファイル）
5つの主要機能：dbConnect 接続 / dbDisconnect 切断 / dbGetQuery SQLクエリ / dbWriteTable 書き込み / dbReadTable テーブル全体の読み取り
dbplyrは画期的なツールです：tbl(con, "table") + dplyr + collect() Rコードを自動的にSQLに変換します
書き込み：dbWriteTable(con, "name", df, overwrite = TRUE) で上書き / append = TRUE で追加
実務演習：パスワード Sys.getenv() およびトランザクション dbBegin/dbCommit/dbRollback と on.exit を使用して、接続を切断してください。
大規模なテーブルの分析を行う場合は、必ず tbl() + collect() を使用してください—計算はデータベース内でのみ行い、Rのメモリには読み込まないでください

📝 練習問題

基本演習：RSQLite を使用してローカルデータベース test.db を作成し、データフレーム（5 行、3 列）を 1 つ挿入した後、dbReadTable を使用してそれを読み込み、データが完全であることを確認してください。
基本問題：前の問題で扱ったデータベースに対して、以下の3つのSQLクエリを実行してください：① SELECT * FROM table ② SELECT COUNT(*) FROM table ③ SELECT col, COUNT(*) FROM table GROUP BY col。
基本演習：dbExecute を使用して新しいテーブル（id、name、age フィールドを含む）を作成し、dbWriteTable を使用して 5 行のデータを挿入し、dbRemoveTable を使用してそれらを削除してください。各操作の結果を確認してください。
応用演習：サンプル販売データベース（customers と orders の 2 つのテーブル）を使用し、tbl() + dplyr + collect() を用いて、① 注文数が 3 回以上の顧客を特定する、② 月ごとの売上合計を算出する、③ 購入額が最も多い上位 3 人の顧客を特定する。スクリーンショットを撮り、保存してください。
課題：dbplyr を使用して、複雑な dplyr 操作を SQL に変換してください：① 複数のフィルタ ② group_by ＋ summarise ③ arrange ＋ head ④ 2 つのテーブルの inner join。show_query() を使用して SQL 文字列を確認し、手書きの SQL と一致していることを確認してください。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて

404 Not Found

Rのデータベース接続：DBIとdplyrによるSQL変換の完全ガイド

1. 学習内容

2. データアナリストの物語

(1) 課題：データがデータベースに保存されている

(2) Rを用いた解決策

3. Rデータベースのエコシステム

(1) 4つのコアパッケージ

(2) インストール

4. RSQLite：ローカルデータベース（初心者におすすめ）

(1) SQLiteとは何ですか？

(2) データベースの作成／接続

(3) DataFrameへの書き込み

(4) データの読み取り

(5) その他の業務

5. 本番データベースに接続する

(3) ODBC（SQL Server／Oracleへの接続）

6. dbplyr：dplyrによるSQLの自動変換

(1) 基本概念

(2) 実践的な応用

(3) 性能上の利点

7. DataFrameをデータベースに書き込む

(1) dbWriteTable

(2) 実践的な応用

8. 制作の実践

(1) 接続設定のベストプラクティス

(2) エラー処理

(3) バッチ処理

9. 完全な例：SQLiteの「Sales」データベースの分析

▶ サンプル：地域別販売データベースの包括的な分析

❓ よくある質問

📖 まとめ

📝 練習問題