R Academy · 강의

PostgreSQL 및 MySQL 연결하기

RPostgres와 RMySQL 드라이버를 사용하여 서버 기반 데이터베이스에 연결합니다.

PostgreSQL 및 MySQL 연결하기은(는) CoddyKit의 무료 R Academy 강의입니다. 이것은 4개 중 2번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 R Academy 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. R Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

운영 환경의 데이터베이스 연결

RSQLite은 개발과 테스트에 유용하지만, 운영 데이터는 PostgreSQL, MySQL, SQL Server 또는 클라우드 데이터베이스에 저장됩니다. DBI 인터페이스는 그대로 유지되며, 백엔드 드라이버 패키지와 연결 인수만 바뀝니다. 이것이 DBI의 핵심 장점입니다.

library(DBI)

# Driver packages for each database:
# PostgreSQL  -> RPostgres::Postgres()
# MySQL       -> RMySQL::MySQL() or RMariaDB::MariaDB()
# SQL Server  -> odbc::odbc()
# BigQuery    -> bigrquery::bigquery()
# Redshift    -> RPostgres::Postgres()  (same protocol)
# DuckDB      -> duckdb::duckdb()

# All use the same DBI functions:
# dbConnect, dbGetQuery, dbExecute, dbDisconnect

PostgreSQL에 연결하기

RPostgres::Postgres()는 PostgreSQL용 드라이버입니다. host, port(기본값 5432), dbname, user, password와 같은 연결 매개변수를 전달하십시오. 자격 증명을 코드에 직접 작성하지 말고 환경 변수를 사용하십시오.

library(DBI)
# library(RPostgres)  # Uncomment when available

# PostgreSQL connection pattern:
# con <- dbConnect(
#   RPostgres::Postgres(),
#   host     = 'db.example.com',
#   port     = 5432,
#   dbname   = 'analytics',
#   user     = 'analyst',
#   password = 'secretpassword'
# )

# Query exactly like SQLite:
# result <- dbGetQuery(con, 'SELECT * FROM sales LIMIT 5')
# dbDisconnect(con)

cat('PostgreSQL uses the same DBI interface as SQLite!')

자격 증명에 환경 변수 사용하기

코드에 비밀번호를 직접 작성하면 보안 위험이 발생합니다. 자격 증명을 환경 변수로 저장하고 Sys.getenv()로 읽으십시오. 운영 환경에서는 .Renviron, git에 추가하지 않은 .env 파일 또는 비밀 관리 시스템을 통해 이러한 값을 설정하십시오.

library(DBI)

# Set environment variables (normally done outside R):
# In .Renviron file:
# DB_HOST=db.example.com
# DB_NAME=analytics
# DB_USER=analyst
# DB_PASS=secretpassword

# Read credentials from environment
get_pg_connection <- function() {
  dbConnect(
    RPostgres::Postgres(),  # driver
    host     = Sys.getenv('DB_HOST'),
    port     = as.integer(Sys.getenv('DB_PORT', '5432')),
    dbname   = Sys.getenv('DB_NAME'),
    user     = Sys.getenv('DB_USER'),
    password = Sys.getenv('DB_PASS')
  )
}

cat('Sys.getenv() reads environment variables safely.')
cat('\nDB_HOST value:', nchar(Sys.getenv('DB_HOST')), 'chars')

MySQL / MariaDB에 연결하기

MySQL 연결에는 RMariaDB::MariaDB()(최신 MySQL 드라이버) 또는 RMySQL::MySQL()을 사용합니다. 연결 인수는 PostgreSQL과 유사합니다. MySQL 및 MariaDB 서버 모두에 MariaDB 드라이버를 사용하는 것이 좋습니다.

library(DBI)

# MySQL / MariaDB connection pattern:
# library(RMariaDB)
# con <- dbConnect(
#   RMariaDB::MariaDB(),
#   host     = Sys.getenv('MYSQL_HOST'),
#   port     = 3306,
#   dbname   = Sys.getenv('MYSQL_DB'),
#   user     = Sys.getenv('MYSQL_USER'),
#   password = Sys.getenv('MYSQL_PASS')
# )

# SSL connection:
# con <- dbConnect(
#   RMariaDB::MariaDB(),
#   host = 'secure-db.example.com',
#   ssl.ca = '/path/to/ca-cert.pem'
# )

cat('RMariaDB supports both MySQL and MariaDB servers.')

연결 시간 초과와 재연결

오래 실행되는 스크립트에서는 연결 시간 초과가 발생할 수 있습니다. dbIsValid(con)으로 연결이 여전히 유효한지 확인하십시오. ETL 스크립트에서는 하나의 연결을 몇 시간 동안 유지하기보다 각 처리 배치의 시작 시점에 다시 연결하는 방법을 고려하십시오.

library(DBI)
library(RSQLite)

# Safe function with reconnection
query_with_check <- function(con, sql) {
  if (!dbIsValid(con)) {
    stop('Connection is no longer valid. Reconnect.')
  }
  dbGetQuery(con, sql)
}

# Demonstrate with SQLite
con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 't', data.frame(x=1:3))

cat('Connection valid:', dbIsValid(con), '\n')
print(query_with_check(con, 'SELECT * FROM t'))

dbDisconnect(con)
cat('After disconnect, valid:', dbIsValid(con))

연결 풀링 개념

쿼리마다 새 데이터베이스 연결을 여는 작업은 비용이 많이 듭니다. 연결 풀링은 여러 연결을 열린 상태로 유지하고 재사용합니다. 초당 많은 요청이 들어오는 웹 API와 Shiny 앱에서는 연결 풀링이 매우 중요합니다.

# The 'pool' package provides connection pooling for R
# library(pool)

# Create a pool (connections managed automatically)
# my_pool <- pool::dbPool(
#   drv      = RPostgres::Postgres(),
#   dbname   = Sys.getenv('DB_NAME'),
#   host     = Sys.getenv('DB_HOST'),
#   user     = Sys.getenv('DB_USER'),
#   password = Sys.getenv('DB_PASS'),
#   minSize  = 2,   # Always keep 2 connections ready
#   maxSize  = 10   # Maximum 10 simultaneous connections
# )

# Use the pool like a regular connection:
# result <- dbGetQuery(my_pool, 'SELECT * FROM table')

# Close the pool on shutdown:
# pool::poolClose(my_pool)

cat('pool package: connection reuse for high-traffic apps!')

SQL 매개변수를 사용한 안전한 쿼리

사용자 입력을 SQL 문자열에 직접 이어 붙이지 마십시오(SQL 삽입 위험). sqlInterpolate(con, sql, .dots=list(...)) 또는 glue 패키지의 glue_sql()을 사용해 매개변수화된 쿼리를 작성하고 값을 안전하게 삽입하십시오.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'students',
  data.frame(id=1:4, name=c('Alice','Bob','Carol','Dave'), score=c(85,92,78,88)))

# UNSAFE (SQL injection possible):
# name_input <- 'Alice' # imagine user-provided
# dbGetQuery(con, paste('SELECT * FROM students WHERE name =', name_input))

# SAFE: use sqlInterpolate
name_input <- 'Alice'
safe_sql <- sqlInterpolate(con,
  'SELECT * FROM students WHERE name = ?name',
  name = name_input
)
print(dbGetQuery(con, safe_sql))
dbDisconnect(con)

원격 데이터베이스를 위한 SSH 터널링

많은 운영 데이터베이스는 인터넷에서 직접 액세스할 수 없습니다. 일반적인 방법은 SSH 터널링입니다. SSH를 통해 로컬 포트를 원격 데이터베이스 포트로 전달한 다음 R을 localhost:local_port에 연결합니다.

# SSH tunnel setup (run in terminal before connecting from R):
# ssh -N -L 5433:db-server.internal:5432 user@jump-host.example.com

# Then connect in R as if the DB is local:
# con <- dbConnect(
#   RPostgres::Postgres(),
#   host     = 'localhost',
#   port     = 5433,   # Local forwarded port
#   dbname   = 'analytics',
#   user     = Sys.getenv('DB_USER'),
#   password = Sys.getenv('DB_PASS')
# )

# You can also script the tunnel with:
# system('ssh -fN -L 5433:db:5432 user@jump-host')

cat('SSH tunneling makes private databases accessible to R.')

큰 테이블을 청크로 읽기

데이터베이스 테이블에 수백만 개의 행이 있는 경우 모든 데이터를 한 번에 불러오면 메모리가 부족해질 수 있습니다. LIMIT/OFFSET 또는 커서 기반 가져오기를 사용해 테이블을 청크 단위로 처리하십시오. 스트리밍 결과를 얻으려면 dbFetch()와 결합하십시오.

library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con, 'big_table', data.frame(id=1:100, value=rnorm(100)))

# Process in chunks of 25 rows
chunk_size <- 25
offset <- 0
total_processed <- 0

repeat {
  chunk <- dbGetQuery(con, sprintf(
    'SELECT * FROM big_table LIMIT %d OFFSET %d',
    chunk_size, offset
  ))
  if (nrow(chunk) == 0) break
  total_processed <- total_processed + nrow(chunk)
  offset <- offset + chunk_size
}

cat('Total rows processed:', total_processed, '\n')
dbDisconnect(con)

dbGetInfo() — 연결 메타데이터

dbGetInfo(con)는 서버 버전, 데이터베이스 이름, 사용자 등의 연결 메타데이터를 반환합니다. 여러 환경을 대상으로 실행되는 스크립트에서 기록, 진단, 올바른 데이터베이스 인스턴스에 연결했는지 확인하는 데 유용합니다.

library(DBI)
library(RSQLite)

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

info <- dbGetInfo(con)
print(info)

# For PostgreSQL, info would include:
# $host, $port, $dbname, $username, $server.version

dbDisconnect(con)

SQLite와 PostgreSQL 사용 비교

프로토타이핑, 임베디드 데이터, 테스트에는 SQLite를 사용하고, 운영 환경, 다중 사용자 액세스, 고급 SQL 기능에는 PostgreSQL을 사용하십시오. DBI 코드는 거의 동일하며, 드라이버와 연결 매개변수만 다릅니다.

library(DBI)
library(RSQLite)

# SQLite (development/testing)
con_dev <- dbConnect(RSQLite::SQLite(), ':memory:')
dbWriteTable(con_dev, 'data', mtcars)
dev_result <- dbGetQuery(con_dev, 'SELECT COUNT(*) n FROM data')
cat('SQLite dev DB rows:', dev_result$n, '\n')
dbDisconnect(con_dev)

# PostgreSQL (production) — same interface:
# con_prod <- dbConnect(RPostgres::Postgres(),
#   host=Sys.getenv('PG_HOST'), dbname=Sys.getenv('PG_DB'),
#   user=Sys.getenv('PG_USER'), password=Sys.getenv('PG_PASS'))
# prod_result <- dbGetQuery(con_prod, 'SELECT COUNT(*) n FROM data')
# dbDisconnect(con_prod)

cat('Same DBI code works for both!')

빠른 확인

R 스크립트에서 데이터베이스 자격 증명을 제공하는 권장 방법은 무엇입니까?

복습: PostgreSQL 및 MySQL 연결

운영 데이터베이스 연결의 핵심 요점:

PostgreSQL 드라이버: 호스트, 포트, 데이터베이스 이름, 사용자, 비밀번호 인수를 사용하는 RPostgres::Postgres()
MySQL/MariaDB 드라이버: 비슷한 인수를 사용하는 RMariaDB::MariaDB()
자격 증명에는 항상 Sys.getenv('VAR')를 사용하고 하드코딩하지 마십시오
dbIsValid(con)는 연결이 아직 활성 상태인지 확인합니다
pool 패키지는 트래픽이 많은 앱의 연결 풀을 관리합니다
sqlInterpolate()는 사용자가 제공한 값으로 인한 SQL 인젝션을 방지합니다
SSH 터널: 로컬 포트를 전달하여 비공개 데이터베이스에 액세스합니다
모든 DBI 함수는 백엔드가 달라도 동일하게 작동합니다

library(DBI)
library(RSQLite)

# Production-ready connection pattern (SQLite for demo)
open_connection <- function() {
  con <- dbConnect(RSQLite::SQLite(), ':memory:')
  if (!dbIsValid(con)) stop('Failed to connect')
  cat('Connected successfully\n')
  con
}

run_query <- function(con, sql) {
  if (!dbIsValid(con)) stop('Connection lost')
  dbGetQuery(con, sql)
}

con <- open_connection()
dbWriteTable(con, 't', data.frame(x=1:3, y=4:6))
print(run_query(con, 'SELECT * FROM t'))
dbDisconnect(con)

자주 묻는 질문

“PostgreSQL 및 MySQL 연결하기” 강의는 무료인가요?

네 — “PostgreSQL 및 MySQL 연결하기” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 R Academy 강의 전체를 잠금 해제할 수 있습니다. R Academy 강의에는 총 4개의 강의가 포함되어 있습니다.

“PostgreSQL 및 MySQL 연결하기”에서 뭘 배우나요?

RPostgres와 RMySQL 드라이버를 사용하여 서버 기반 데이터베이스에 연결합니다. 브라우저에서 직접 실행하는 실습 코드로 R Academy을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

R Academy을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 R Academy은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 2번째 강의입니다.

“PostgreSQL 및 MySQL 연결하기” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 R Academy 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 R Academy 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← R Academy(으)로 돌아가기