library(RODBC)RODBC
パッケージの概要
RODBCパッケージはODBC接続を実装したもので、これを用いることでSQLベースのデータベース管理システム(DBMS)をRで取り扱えるようになります。
ODBCとはSQLベースのDBMSにアクセスするための共通APIで、MySQL、PostgreSQL、Microsoft Access、SQL Server、Oracle等への接続に利用できますが、利用のためには対応するODBCドライバがインストールされている必要があります。
なお、本稿ではSQLの文法、ODBC接続の詳細、特定のDBMSに固有の情報などは取り扱いません。(Brian Ripley 2023)にはDBMSごとの差異などに関するより詳しい解説があるため、適宜参照してください。
使い方
準備
まずはRODBCパッケージを読み込みます。
ODBC接続を行う方法には大きく2種類があり、 データソース名(DSN)を指定する方法と、接続文字列を直接指定する方法があります。
前者の場合はその環境で用いることが出来るDSNの一覧を次のように取得することが出来るため、 データソースを用意している場合は確認しておきましょう。
odbcDataSources(type = c("all", "user", "system")) MS Access Database
"Microsoft Access Driver (*.mdb, *.accdb)"
Excel Files
"Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
dBASE Files
"Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)" 要素の名前にDSNが、要素にその説明(通常はMicrosoft Access Driver (*.mdb, *.accdb)などのようなドライバ名)が格納されます。
なお、ODBCドライバやデータソースには32ビットのものと64ビットのものがあり、 またRにも32ビット版と64ビット版が存在していますが、 例えば64ビット版のRだと64ビットのドライバ等しか使用できません。
Rのバージョン4.2以降は32ビット版が廃止されて64ビット版のみとなっているため、 64ビットのドライバを用意するようにしましょう。
基本的な使い方
まず、odbcConnect関数かodbcDriverConnect関数でODBC接続を開始します。 前者はDSNを指定する場合、後者はODBC接続文字列を直接指定する場合に使用します。
これらの関数の戻り値は「チャンネル」と呼ばれ、以降の処理で使用しますので、変数に格納しておきましょう。
odbc_ch <- odbcConnect("Your DSN")
#または
odbc_ch <- odbcDriverConnect(connection = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\test\\test.accdb")
#など次に、前述の手順で得たチャンネルに対して、sqlQuery関数などのデータベースを操作する関数を実行します。
sqlSave(odbc_ch, iris, tablename = "iris") #irisデータセットをテーブルに保存する
sqlQuery(odbc_ch,
"SELECT * FROM iris
WHERE Species = 'setosa' AND SepalLength < 4.5") #SELECT文の結果はdata.frameになるsqlQuery(odbc_ch,
"SELECT avg(SepalLength) as avg_SepalLength FROM iris
GROUP BY Species")ODBC接続を終了する場合は、odbcClose関数を使用します。
odbcClose(odbc_ch)関数の紹介
基本的にはsqlQuary関数でSQLクエリを実行することで大抵のことはこなせますが、 RODBCパッケージにはほかにもいくつか便利な関数が備わっています。
クエリ実行と結果の読み取り
sqlQuary関数はSQLクエリを実行し、その結果を取得する関数です。
SELECT文だけでなく、UPDATE文やCREATE TABLE文のようなものも含めてあらゆるSQLを実行可能です。
head(sqlQuery(odbc_ch, "SELECT * FROM iris"))クエリの実行と結果の読み取りを分けて行うこともできます。
odbcQuery関数でクエリを実行し、sqlGetResults関数で実行結果を読み取ります。 ここで、sqlGetResults関数に引数maxを与えることで読み取る最大行数を指定することができ、 これを活用することで実行結果が巨大な場合でも、分割してデータを取得・処理を行うことができます。
tmp <- odbcQuery(odbc_ch, "SELECT * FROM iris")
for(i in 1:3){ #結果を2行ずつ3回に分けて取得
print(sqlGetResults(odbc_ch, max = 2))
}なお、odbcQuery関数はSQLの実行でエラーとなっても特にエラーメッセージは表示されません。 その戻り値が-1の場合はエラーとなっていますが、この場合のエラーメッセージはodbcGetErrMsg関数で取得します。
また、全てのデータを読み取っているかどうかは、sqlGetResults関数の戻り値が負の数値であるかどうかで判定できますが、 残る結果がゼロ行である場合は数値にはならず常に空のデータフレームとなるため、別途判定が必要です。
以上をコードに反映すると次のようになります。
tmp <- odbcQuery(odbc_ch, "SELECT * FROM iris")
if (tmp < 0){ #エラーの場合
tmp <- odbcGetErrMsg(odbc_ch)
print(tmp)
}else{ #正常に実行できた場合
while(is.data.frame(tmp <- sqlGetResults(odbc_ch, max = 40)) && nrow(tmp) > 0){
print(paste0("データ行数:", nrow(tmp))) #逐次読み取ったデータへの処理を書く
}
print(paste0("最後の戻り値:", tmp))
}[1] "データ行数:40"
[1] "データ行数:40"
[1] "データ行数:40"
[1] "データ行数:30"
[1] "最後の戻り値:-1"テーブル全体の読み取り
sqlFetch関数を用いることで、指定したテーブル全体をデータフレームの形で取得することができます・
df_tmp1 <- sqlFetch(odbc_ch, "iris")
df_tmp2 <- sqlFetch(odbc_ch, "iris", max = 3) #3行だけ読み込み
str(df_tmp1)'data.frame': 150 obs. of 5 variables:
$ SepalLength: num 5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9 ...
$ SepalWidth : num 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1 ...
$ PetalLength: num 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5 ...
$ PetalWidth : num 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1 ...
$ Species : chr "setosa" "setosa" "setosa" "setosa" ...df_tmp2データの書き込み
本パッケージにはR側のデータをテーブルに書き込むための専用関数が用意されています。
sqlSave関数はDBMS上に新たなテーブルを作成し、指定したデータフレームのデータを書き込みます。
df_inforce <- data.frame(
配当方式 = c("有配","有配","準有配"),
商品種類コード = 1:3,
件数 = c(10, 16, 48),
保険金額 = c(100, 60, 240)
)sqlSave(odbc_ch, df_inforce, tablename = "inforce", rownames = FALSE)
#rownames = FALSE: 行の名前を保存しない
sqlFetch(odbc_ch, "inforce")引数appendをTRUEとすることで、データを既存のテーブルに追加することができます。
df_inforce_add <- data.frame(
配当方式 = c("無配","無配"),
商品種類コード = 4:5,
件数 = c(176, 190),
保険金額 = c(69, 1931)
)sqlSave(odbc_ch, df_inforce_add, tablename = "inforce", rownames = FALSE, append = TRUE)
sqlFetch(odbc_ch, "inforce")sqlUpdate関数で既存データを変更することもできます。引数indexで、データを更新する行を特定するための列名を指定します。
df_inforce_update <- data.frame(
配当方式 = c("準有配", "無配"),
商品種類コード = c(3, 5),
件数 = c(9999, 99999)
)sqlUpdate(odbc_ch, df_inforce_update, tablename = "inforce", index = c("配当方式", "商品種類コード"))
sqlFetch(odbc_ch, "inforce")クエリ実行結果の書き込み
sqlCopy関数を用いることで、クエリの実行結果を別のテーブルに書き込むことができます。
sqlCopy(odbc_ch, "SELECT * FROM inforce WHERE 配当方式 = '有配' ", "inforce_yuhai")
sqlFetch(odbc_ch, "inforce_yuhai")SELECT ... INTO [table名]の構文でも同じことはできるため実用性が無いように思えますが、 引数destchannelを使用することで他のDBMSにデータを転送できることが強みです。
テーブル一覧の取得
sqlTables関数でテーブルの一覧の取得ができます。
sqlTables(odbc_ch)#Accessの場合、引数tableType = "TABLE"を指定することで通常のテーブルだけを表示することが可能
sqlTables(odbc_ch, tableType = "TABLE")テーブルの削除
sqlDrop関数でテーブルの削除ができます。
引数errorsにFALSEを与えた場合、削除対象のテーブルが無い場合でもエラーにならず、 「存在する場合は削除」という処理を行う場合に便利です。
sqlDrop(odbc_ch, "inforce_yuhai", errors = FALSE)
sqlDrop(odbc_ch, "inforce_muhai", errors = FALSE)
sqlTables(odbc_ch, tableType = "TABLE")列一覧の取得
sqlColumns関数で、テーブルにある列の一覧を取得することが出来ます。
sqlColumns(odbc_ch, "iris")参考文献
Brian Ripley. 2023. “ODBC Connectivity.” Preprint. https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf.