【R】Ubuntuで稼働中のShiny ServerからODBCを使ってSQL Serverへ接続

2022-10-11

仕事必要なで定型的な統計解析やデータ集計はShiny Serverで構築したシステムを使っています。

大元のデータはSQL Serverで保存されているため、データの一元管理からもデータベースに直接接続できれば便利だと思い、R（Shiny Server）からSQL Serverへ接続できるようにしました。

ただ、PostgreSQLやSQLiteは専用のライブラリがあるので簡単ですが、UbuntuからSQL Serverへの接続は色々とひと手間かかったので手順を掲載しています。

実現したかった事

基本的には、赤丸のボタンクリックで指定期間のデータをSQL Serverから抽出する事がメインです。

抽出するデータ量も多くなく、条件に応じてRで加工するためSQLは簡単なデータ抽出（select）だけです（下記の画面は生データの時系列プロット）。

後から追加で、コメント等の情報を更新（update）できるようにしました。

Shinyで作ったシステム構成は後々手順を公開できればと考えています。

RからSQL Serverへ接続する手順

Rにライブラリをインストール

RでODBCを使うライブラリは「RODBC」と「DBI」+「odbc」がありますが、今回は「DBI」+「odbc」を使いました。

ちょっとした違いはありますが、好みや運用に応じて使い分けて下さい。

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

ODBCドライバ・データソースの役割については、上記のリンクに（素人なりの）解説を掲載しています。

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

ODBCインターフェースから呼び出すドライバをUbuntu Serverにインストールします。

#UnixODBCは全てのデータベースに接続するために必要になります。
sudo apt install unixodbc unixodbc-dev --install-suggests

#SQL Serverに接続するドライバでFree TDSをインストールします。
sudo apt install tdsodbc

ドライバ・データソースを設定

参考サイト

ドライバファイルの確認

ドライバファイルがインストールされた場所を確認します。

locateがインストールされていない場合は、先にaptでインストールしてください。

$ locate tdsodbc
/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so　#←FreeTDSのドライバファイル
/usr/share/tdsodbc
----以下略----

ドライバ構成ファイルとデータソースの確認

次に、ドライバ構成ファイルとデータソースを設定するためにファイルの場所を確認します。

odbcinstはunixodbcと一緒にインストールされています。

$ odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini #ドライバ構成ファイル
SYSTEM DATA SOURCES: /etc/odbc.ini #システムデータソース
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/ユーザーフォルダ/.odbc.ini #ユーザーデータソース
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

ドライバ構成ファイルの修正に加えて、今回はShiny Serverから接続するため「システムデータソース」を編集する必要があります。

ドライバ構成ファイルの修正

/etc/odbcinst.iniを編集・作成します。

[FreeTDS]
Description=FreeTDS for SQL Server
Driver=/usr/lib/x86_64-linx-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
UsageCount=1

システムデータソースの編集

注：データソースファイルを作らず、Rのスクリプト中に直接記述する方法もあります（ドライバ構成ファイルの作成は必要です）。

/etc/odbc.iniを編集します。

[データソース名]
Description=SQL Server 14.0.2027.2
Driver=FreeTDS
Trace=No
Server=サーバーのFQDNやIPアドレス
Database=データベース名
Port=1433
ClientCharset=UTF-8　#←必要なら？

Rから接続

内部ネットワークだけでの使用で色々調べる労力を避けるため、ここではUID（ユーザー名）とPWD（パスワード）を直接記述していますがセキュリティの問題はかなり残っています。

conn <- dbConnect(odbc::odbc(), "データソース名", UID="*****", PWD="*****")
d <- dbSendQuery(conn, select * from テーブル)
d <- dbFetch(d)
dbDisconnect(conn)

環境に応じて方法は変更してください。

まとめ

以上が、Ubuntu Serverで稼働しているShiny ServerからSQL Serverへ接続してデータを抽出するために必要な設定です。

正直、ODBCの基本もわかってなく、コピペだけで動作させていたので改めて理解する事ができました。

ODBCドライバのインストールは必要ですが、PostgreSQLやSQLiteなど他のデータベースへの接続もドライバ構成ファイルとデータソースを修正するだけで「odbc」から接続できるようになります。

Library (Package),R-Statisticデータベース

Posted by ううら