RでSQLserver(on Ubuntu16.04)への接続 - Ｒ言語による電子カルテデータの二次利用

現在当院で使用している電子カルテのデータベースは、MicrosoftのSQLサーバーです。ハードの保守期間も過ぎたため、今回ハードの更新と、Windows10版のクライアントへの変更、サーバーも変更しました。
今までは、クライアントPC（windows7）にR・RstudioをインストールしSQLサーバーへの接続していましたが、他のクライアントPCからも同じ環境を使用できるようにRstudio serverで環境を作り直すことにします。

まず、ローカルネットワーク上にUbuntu実マシンを用意し、R環境とRstudio serverをインストール。いままでは、Windowsマシンからしか接続した経験がないので、UbuntuでのODBC接続の練習をしてみたいと思います。
自宅にSQLserverがないので、SQLserver2017 CTP2.1をubuntu16.04にインストールしてみます。CTPはCommunity Technology Previewで評価版のため使用制限もあります。なおこの評価版はUbuntu16.04、Ubuntu16.10で64bitが対応しているそうです。

kledgeb.blogspot.jp

SQLserverのインストール

練習環境ですのでUbuntu16.04の仮想マシンに構築します。
Ubuntu端末から下記シェルスクリプトを実行してください。

#!bin/sh

# install R
sudo sh -c "echo 'deb http://cran.ism.ac.jp/bin/linux/ubuntu xenial/' >> /etc/apt/sources.list"
gpg --keyserver keyserver.ubuntu.com --recv-key E084DAB9
gpg -a --export E084DAB9 | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install r-base

# install Rstudio-server
sudo apt-get install gdebi-core
wget https://download2.rstudio.org/rstudio-server-1.0.143-amd64.deb
sudo gdebi rstudio-server-1.0.143-amd64.deb

# install RODBC
sudo apt-get install r-cran-rodbc

# install SQLserver
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list
sudo apt-get update
sudo apt-get install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup
# passwordを確認されるので、入力。

# install tools on Ubuntu
# curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - 
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update 
sudo apt-get -y install mssql-tools unixodbc-dev

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

途中でパスワード入力後に、評価版のため153日の試用期間と表示されますが、テスト環境なので十分です。
f:id:r_beginner:20170611132312j:plain

Linuxクライアントからの接続

まずは先程インストールした、mssql-toolsを使って接続します。
sqlcmdコマンドが使えるようになっているので、

$ sqlcmd -S localhost -U SA -P '<YourPassword>'
$ sqlcmd -S <IP address> -U SA -P '<YourPassword>'

接続できたでしょうか

$ sqlcmd -S localhost -U SA -P '********'
1>

このように出力されればOKです。

1> SELECT Name from sys.Databases;
2> GO
Name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
testdb                                                                                                                          

(5 rows affected)
1>

SELECT文でデータベース名が確認できたかと思います。

次にODBC接続の練習です。

$ sudo gedit /etc/odbcinst.ini

でODBCdriverを確認してみましょう。
f:id:r_beginner:20170611134506j:plain

次にODBC.iniの設定です。

$ sudo gedit /etc/odbc.ini

f:id:r_beginner:20170611134828j:plain
上記を参考に、ポート番号、ユーザー名、パスワード、データベース名等入力してください。 [ ] の中（今回はsqlserver）がDSN（Data Source Name）です。

準備ができいたら、ブラウザから IPaddress : 8787 でRstudio serverにログインします。
PackageタブのあるPaneでRODBCがインストールされているのも、一応確認してみてください。

あとは新規ファイルを作成し、下記コードをコピペします。

library(RODBC)
con <- RODBC::odbcConnect(dsn="sqlserver",uid="SA",pwd="********")
sqlQuery(con, "select name from sys.Databases;")

実行すると、コンソールにデータベース名が出力されます。

> sqlQuery(con, "select name from sys.Databases;")
    name
1 master
2 tempdb
3  model
4   msdb
5 testdb
>

Windowsクライアントからの接続

まず、Windows PowerShellを使います。
f:id:r_beginner:20170611141213j:plain
OSの左下、検索窓に 'pow'と入力してみてください。
シェルが立ち上がったら、先程同様に

$ sqlcmd -S <IP address> -U SA -P '<YourPassword>'
1> SELECT Name from sys.Databases;
2> GO

とコマンド入力してみてください。
f:id:r_beginner:20170611142750j:plain
データベース名が出力されます。

次にODBCドライバーを使います。

f:id:r_beginner:20170611141213j:plain
OSの左下、検索窓に 'データ'と入力してみてください。

f:id:r_beginner:20170611141604j:plain
検索候補に現れた、ODBCデータソースを選択

f:id:r_beginner:20170611141724j:plain
追加ボタンを押して、
f:id:r_beginner:20170611141752j:plain
データソース名、サーバーのIPアドレスを入力
f:id:r_beginner:20170611141852j:plain
SQLサーバー認証のため、ログインID、パスワードを入力し、次へ。
f:id:r_beginner:20170611141957j:plain
test data sourceボタンを押すと、
f:id:r_beginner:20170611142032j:plain
テスト成功の文字が出て終了です。