Rのインストール、RODBCでDBテーブルのDELTE,INSERTを行う。

Rはじめました。
統計解析やるぞーって段階ではなく、Rの実行環境を作ってる最中です。
データベースとの連携で色々面倒だったのでメモ。

Linux(Cent OS)への導入

(最新だとわかりませんが)CentOS 6.4には、yumにRはありませんのでEPEL導入します。
導入後もRのライブラリインストールでこけたりするので、どちらにしろEPELは入れたほうがいい。 sudo rpm -Uvh http://ftp-srv2.kddilabs.jp/Linux/distributions/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm
sudo yum install R
あと、必要ならR Studio Server入れます。 RStudio
R Studio ServerすごいよR Studio Server。

RODBC, XMLを使うならこれらのライブラリから参照されるヘッダファイルが必要なのでインストール
sudo yum install unixODBC unixODBC-devel libxml2-devel
今後も必要に応じて追加していく。

あとは、DB用意したり、ODBCドライバ用意したり、 /etc/odbc.ini を書いたりする。

RODBCの利用

Rを起動し、

    #パッケージインストール,初回のみ
    install.packages("RODBC")
    library(RODBC)
    # ODBC名を引数に渡す。ユーザー、パスワードが必要なら引数を追加。
    conn <-odbcConnect("myodbc")
    # テーブルの全データ取得
    data <- sqlFetch(conn, "Table")
    # 適当にデータ加工
    
    # テーブルにデータ反映。第三引数がないと、第二引数の変数名(この場合data)でテーブルをcreateする。
    sqlSave(conn, data , "Table2")

SQL書かなくてもDBへデータ反映できるのはすばらしい!
のだけど、このsqlSave() デフォルトの動作は指定されたテーブルをcreateする。
(既存テーブルがある場合は実行不可で、 実行前に sqlDrop(conn, "Table2") を実行する。)
Delete&Insertでどうにかできないものか。

エラー

sqlSave()の引数を見てみると、saferオプションがある。TRUEだとCleate, FALSEだとテーブルがなければcreate,あればDELETE&INSERTを行うとあるので、これで解決
sqlSave(conn, data, "Table", safer=FALSE)
と思いきや、こんなエラー

Query: INSERT INTO `Member` ( `ID`, `NAME`, `AGE`, `GENDER` ) VALUES ( ?,?,?,?,? )
Binding: 'ID' DataType 4, ColSize 10
Binding: 'NAME' DataType -9, ColSize 120
Binding: 'AGE' DataType 4, ColSize 10
Binding: 'GENDER' DataType -8, ColSize 1
Parameters:
no: 1: ID 1/***/no: 2: NAME test/***/no: 3: AGE 32/***/no: 4: GENDER 0/***/
 *** caught segfault ***
address 0x2028b3780, cause 'memory not mapped'

Traceback:
 1: .Call(C_RODBCUpdate, attr(channel, "handle_ptr"), as.character(query),     data, ds - 1L, params, as.integer(vflag))
 2: odbcUpdate(channel, query, mydata, coldata[m, ], test = test,     verbose = verbose, nastring = nastring)
 3: sqlwrite(channel, tablename, dat, verbose = verbose, fast = fast,     test = test, nastring = nastring)
 4: sqlSave(conn, data, "Member", verbose = TRUE, safer = FALSE)

SEGVやめて、、、。ちなみにWin版だとこんなログ。

 odbcUpdate(channel, query, mydata, coldata[m, ], test = test,  : 
  missing columns in 'data'

解決

何か、列数とパラメータ数あってないぞ。

    Query: INSERT INTO `Member` ( `ID`, `NAME`, `AGE`, `GENDER` ) VALUES ( ?,?,?,?,? )

というわけで、よく見てみると、通常のsqlSaveでテーブルcreateした場合、一番最初にrownamesという列が勝手に追加されている。
設定されているのはデータフレームの行番号みたいで、これはデータフレームをcsvとかに出力した場合でも付与されるので、よくある話なのかな?
というわけで、解決方法は以下の通り。

sqlSave(conn, data, "Table", safer=FALSE, rownames=FALSE) 引数 rownames=FALSE を追加すれば,rownames列は設定されず、データフレーム内のデータのみでINSERTを行ってくれる。

ちなみに、ODBCドライバのせいかはよくわからないが、テーブル名やカラム名の大文字、小文字の違いよってもエラーが出たりするので、解決しない場合はDB側にも調査が必要。