PostgreSQLからスキーマを指定してデータを参照する

RからPostgreSQLのデータベースに接続するには主にDBI、dplyr (dbplyr)、sfの3つの方法だと思う。各方法についてまとめるとともに、表題のスキーマ指定でのデータ参照の方法を整理しておく(2017年10月現在の状況なので、今後対応状況が変わるかもしれない)。

記述しているデータベースの情報についてはダミーなので、参考とする際は適宜利用するものに書き換えてほしい。

コネクションの確立

データベースへの接続(コネクション)はいずれもDBIを通して行う。ここでもドライバとしてRPostgreSQL、odbcのいずれかか、RPostgreSQL::PostgreSQL()をラップしたdpyr::src_postgres()が選択できる。

library(DBI)
con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(),
                      dbname = "database",
                      host = "localhost",
                      port = 5432,
                      user = "username",
                      password = "password")
class(con)
# [1] "PostgreSQLConnection"
# attr(,"package")
# [1] "RPostgreSQL"

odbc::odbc()を利用する場合、引数名が異なる。

con <- dbConnect(odbc::odbc(),
                 driver = "PostgreSQL",
                 database = "database",
                 servername = "localhost",
                 port = 5432,
                 uid = "username",
                 pwd = "password")
class(con)
# [1] "PostgreSQL"
# attr(,"package")
# [1] ".GlobalEnv"

library(dplyr)

con <- src_postgres(
  "database",
  host = "localhost",
  port = 5432,
  user = "username",
  password = "password")
class(con)
# [1] "src_dbi" "src_sql" "src"

データ取得

コネクションを利用してテーブルのデータを取得する。PostgreSQLではスキーマが利用でき、publicのテーブルとスキーマ内のテーブルを参照する方法が異なる。

DBI

ポピュラー(?)な方法。後述のdplyrもsfもこれを利用している。dbReadTable()やdbGetQuery()という関数が利用できる。

# publicテーブルはテーブル名を指定するだけ
con %>% dbReadTable("table")

# 対象がスキーマの中にある場合、c()を使って参照する
con %>% dbReadTable(c("schema", "table"))
# con %>% dbGetQuery("SELECT * FROM schama.table")

dplyr & dbplyr

tbl()を使ってデータベースのテーブルを参照する。ただ、スキーマの場合、c()ではできない。schema.tableのようにしてもだめ。さて困ったぞ、という時にdbplyr::in_schema()が使える。

con %>% tbl(from = "table")

con %>% tbl(from = dbplyr::in_schema("schema", "table"))

in_schema()はPostgreSQLだけでなく、SQLiteのようなスキーマが利用可能なデータベースに対応しているようだ。

sf

DBIと同じく、スキーマのテーブルにはc()を使う。dbplyr::in_schema()には未対応なようだ。

sf::st_read_db(con, table = "table")

sf::st_read_db(con, table = c("table", "schema"))

体感速度としてdbplyrの関数を利用した方が高速に読み込みができているような気がする。とわいえgeometryの扱いはsfだけが対応している状況なので、PostgreSQLの魅力でもあるgeometryを扱う場合はsfから読み込んだ方が良さそう、という結論。

RStudio Connectionタブを利用

便利すぎて泣いた。コネクションは一度作っておけば再利用できるし、GUI操作でテーブル構造の確認やデータの確認まで行える。