Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
4
Help us understand the problem. What is going on with this article?
@uri

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

More than 3 years have passed since last update.

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

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

コネクションの確立

データベースへの接続(コネクション)はいずれもDBIを通して行う。ここでもドライバとしてRPostgreSQLodbcのいずれかか、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

ポピュラー(?)な方法。後述のdplyrsfもこれを利用している。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操作でテーブル構造の確認やデータの確認まで行える。

AVOOBkPAnC.gif

4
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
hoxom
Machine Learning and Data Analysis Company for Your Smiles :)

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
4
Help us understand the problem. What is going on with this article?