【R】RODBCパッケージでSQLのバックアップファイル(.bak)を開いた話
基本的にRしか使えない私が、データベースファイルを受け取ってなんとかRで読み込めるようにするまでのドタバタを記録しておく。
共同研究者からもらったデータのファイルが見慣れない拡張子だったので、調べてみた。いかんせん現在使われてるはずない拡張子(.dms)だった上に開くはずのアプリでも開けなかったので、中身違うんじゃないの、と思ってデータの供給元に聞いてもらった。するとやはり違って、本当はSQLのバックアップファイル(.bak)だった。
どうやってRで開けるか調べたところ、ざっくりいうと以下のステップで開く。
１．データベースファイルをリストアする。
２．リストアしたデータベースをRで読み込み、データテーブルを取り出す。
私はデータベースというものがなにか、おぼろげにしか理解していない。（データフレームの集合で、それぞれが関連付けられている、といった程度のイメージ）。
データベースファイルを復元する
まず、そもそもデータベースをどうやって個人のPCで開くのか、と調べたところ、Microsoft SQL Server Expressというのが無料で使えるらしい。無料版のExpressは10GBまでの大きさのデータベースしか扱えないらしいが、幸いなことに受け取ったファイルはそこまで大きくなかった。
SQL Server 2019 | Microsoft
このMicrosoft SQL Server Expressをインストールしたあとに、Microsoft SQL Server Management Studio (SSMS)をダウンロードしてインストールする。
SSMSを開いて、SQLのサーバーを構築する。といってもそんなたいそうなことではなく、とりあえず開いて、サーバーに接続（Connect To Server）からデフォルト状態で（自分のパソコン/SQLEXPRESS）で接続。AuthenticationはWindows Authenticationのままにしている。
このとき、パソコンのアカウントが管理者でないと新しくデータベースが作れないので注意。
左のサーバー内容一覧で、「データベース」を右クリックし、「新しいデータベースを作成」を選ぶ。
そして、新しいデータベースに適当に名前をつける。
データベースができたら、その上でまた右クリックし、「タスク」→「復元」→「データベース」を選ぶ。
表示されたダイアログ内で「デバイス」を選択し、「…」をクリックして、さらに出てきたダイアログで「追加」をクリック。
そして、自分が復元したいバックアップファイルを選択して、OK。すると、データベースが復元される。
Rで読み込む
ここでは、Rでデータベースを読み込むためにRODBCパッケージを使う。
RODBCパッケージを読み込んで、使い方に従うとobdcConnect()なのだが、データソースを指定するやり方がいまいちわからない。
read_csvみたいにパスで指定するわけではないらしい。
わかりにくいので、代わりにobdcDriverConnect()関数を使って詳しく指定してやるとよいらしい。
stackoverflow.com
library(RODBC) connection = odbcDriverConnect("Driver={SQL Server};Server=.\\SQLExpress;Database=mydb;Trusted_Connection=Yes;Uid=｜*****;Pwd=******;")
Microsoft SQL server expressを使ってる私の場合は上のようになった。
mydbのところに上でつけたデータベースの名前を、Uidに自分のウィンドウズのログイン名、Pwdにパスワードを入力する。上でWindows Authenticationを使ってサーバーにログインしているとこれでいいはず。
.\\SQLExpressのバックスラッシュは一本で説明されているが、うごかなかったので2本にするとうまくいった。
https://stackoverflow.com/questions/47706184/r-connecting-to-local-sql-server
うまく読み込むことができれば、RODBCというクラスのオブジェクトになる。
データベースが持っているテーブルの名前のリストを見るには、
sqlTables(connection)
で一覧が表示される。
テーブルをデータフレームに読み込ませるには
df = sqlFetch(connection, "TableName")
TableNameを欲しいテーブル名にすると、読み込ませることができる。あとは、通常のデータフレームと同様に扱える。
テーブルについての詳細に関するメタデータや、各テーブル内の変数名の説明が見たければ同じ関数で
df_column = sqlFetch(connection, "INFORMATION_SCHEMA.COLUMNS") df_tables = sqlFetch(connection, "INFORMATION_SCHEMA.TABLES")
とすることで、データフレームに読み込ませることができる。
ROBDCについては以下のブログの記事がまとまっています。
a-habakiri.hateblo.jp
本記事では読み込むだけにフォーカスしましたが、データベースの扱いになれている人ならRからクエリを打ったり(sqlQuery)、
Rで作業したデータフレームをデータベースに返したり(sqlSave)できるみたいですね。
