ODBCドライバをセットアップしてRからデータベースに接続する #R

以下のテキストはほぼSetting up ODBC Driversの訳ですが、多少加筆しています。また、WindowsとLinuxは端折ったので、必要であれば原文を参照して下さい（Linuxについてはほぼ一緒ですが）。

ODBCドライバのインストール

UnixとMacOSではODBCドライバはunixODBCに対してコンパイルされている必要があります。iODBCは動作する可能性がありますが、完全にサポートされているわけではありません。

ドライバマネージャとドライバをインストールしたら、ドライバをodbcinst.iniファイルに登録する必要があります。正常に登録されれば、odbc::odbcListDrivers()から確認できるようになります。

MacOSの場合

homebrewをインストールしましょう。homebrewを使えばデータベースドライバを容易にMacOSにインストールできます。
まずUnixODBCをインストールします。これはすべての種類のデータベースに対して必要です。
- brew install unixodbc
目的のデータベースに応じたドライバをインストールします。

# SQL Server
brew install freetds --with-unixodbc
# PostgreSQL
brew install psqlodbc
# MySQL
brew install mysql
# SQLite
brew install sqliteodbc

データベース接続設定

MacOSとLinuxでは2種類のテキストファイル（DSN: Data Source Name ファイル）を編集する必要があります。UnixODBCにはコマンドラインで実行可能なodbcinstというツールが付属しており、これによりDSNファイルを編集可能です。しかし、DSNファイルの実態は単なるテキストファイルですから、直接編集しても構いません。

DSN情報をセットアップするために使われるのは次の2種類のファイルです。

odbcinst.ini ドライバのオプションの定義をします。
odbc.ini コネクションのオプションの定義をします。

設定ファイルの場所

DNSファイルによる設定はすべてのユーザーに対して適用することができます。その場合、/etc/odbc.iniや/opt/local/etc/odbc.iniなどにファイルを配置します。ファイルの場所はunixODBCをコンパイルした際のオプションに依存しますが、ターミナルからodbcinst -jと実行すれば正確な位置を知ることができます。もしくは、ODBCSYSINI環境変数を通じて、ODBCSYSINI=~/ODBCのように任意の場所を指定することもできます。

`odbcinst.ini`

ここでは主に利用するドライバライブラリについての情報を記述します。一つのファイル内で複数のドライバについて記述することができます。

[PostgreSQL Driver]
Driver          = /usr/local/lib/psqlodbcw.so

[SQLite Driver]
Driver          = /usr/local/lib/libsqlite3odbc.dylib

`odbc.ini`

このファイルはコネクションに関する情報、すなわちユーザー名、パスワード、データベース名、ホスト名などを記入します。Driver行のドライバ名は、odbcinst.iniで指定したものを記述します。

[PostgreSQL]
Driver              = PostgreSQL Driver
Database            = test_db
Servername          = localhost
UserName            = postgres
Password            = password
Port                = 5432

[SQLite]
Driver          = SQLite Driver
Database=/tmp/testing

Rからの接続

Rからデータベースに接続する方法は2つあります。1つはコネクションに関する情報をすべて指定するやり方、もう一つはDSNファイルの設定を利用して接続するやり方です。

まず、パッケージとしてodbcパッケージとDBIが必要となるため、無い場合はインストールしておきます。

install.packages("odbc")
install.packages("DBI")

コネクションに関する設定を渡す方法

コネクションに関するパラメータをdbConnect()関数の引数として渡します。

con <- DBI::dbConnect(odbc::odbc(),
  driver = "PostgreSQL Driver",
  database = "test_db",
  UID    = rstudioapi::askForPassword("Database user"),
  PWD    = rstudioapi::askForPassword("Database password"),
  host = "localhost",
  port = 5432)

データベースの種類に応じた設定についてはDatabasesを参照してください。

RStudioを使用している場合は、上記のコードを実行してコネクションが確立すると、コードが自動的にConnectionsペインに追加されて次回から簡単にアクセスできるようになります。

なお、上記コードのようにrstudioapi::askForPassword()を利用すると実行の都度認証情報を入力するためのプロンプトが現れるので、コード中に認証情報が現れる心配がありません。これ以外にも外部ファイルやMacOSのkeychainを利用する方法などがあります。興味のある方はSecuring Credentialsを確認してみると良いでしょう。

DSNファイルの設定を利用する方法

設定ファイルが記述されていれば、データソース名（DSN）を通じてデータベースに接続することができます。

con <- dbConnect(odbc::odbc(), "PostgreSQL")

この方法でもConnectionsペインにコードが保存されますが、Connectionsペインの「New Connection」をクリックしてコネクションを追加する方法もあります。DSNファイルが設定されていれば、設定されているデータソース名が一覧に表示されるはずです。