R から JDBC を使ってデータベースに接続

R から JDBC を使ってデータベースに接続した時のメモです。

なんで JDBC？

JDBC だったら JDBC ドライバー（ jar ファイル）探して指定するだけで良さそうですし、 Win でも Linux も同じドライバーが使えそうなので、そのあたりで悩まなくてもよさそう、っていうのが理由でしょうか。例えば ODBC 使おうと思うと OS 側で色々と設定が必要だったり、ドライバも OS によって異なったりすると思うので…。

動作環境

• Lubuntu 16.04
• R 3.6.1
• Java 1.8.0

さぁはじめよう

まずは以下のコマンドを実行して R に対して Java の設定を行います。

Javaの設定

R CMD javareconf -e

Javaの設定（実行結果）

$ java -version
openjdk version "1.8.0_212"
OpenJDK Runtime Environment (build 1.8.0_212-8u212-b03-0ubuntu1.16.04.1-b03)
OpenJDK 64-Bit Server VM (build 25.212-b03, mixed mode)

$ R CMD javareconf -e
*** JAVA_HOME is not a valid path, ignoring
Java interpreter : /usr/bin/java
Java version     : 1.8.0_232
Java home path   : /usr/lib/jvm/java-8-openjdk-amd64/jre
Java compiler    : /usr/bin/javac
Java headers gen.: /usr/bin/javah
Java archive tool: /usr/bin/jar

trying to compile and link a JNI program 
detected JNI cpp flags    : -I$(JAVA_HOME)/../include -I$(JAVA_HOME)/../include/linux
detected JNI linker flags : -L$(JAVA_HOME)/lib/amd64/server -ljvm
gcc -std=gnu99 -I"/usr/share/R/include" -DNDEBUG -I/usr/lib/jvm/java-8-openjdk-amd64/jre/../include -I/usr/lib/jvm/java-8-openjdk-amd64/jre/../include/linux    -fpic  -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -g  -c conftest.c -o conftest.o
gcc -std=gnu99 -shared -L/usr/lib/R/lib -Wl,-Bsymbolic-functions -Wl,-z,relro -o conftest.so conftest.o -L/usr/lib/jvm/java-8-openjdk-amd64/jre/lib/amd64/server -ljvm -L/usr/lib/R/lib -lR

The following Java variables have been exported:
JAVA_HOME JAVA JAVAC JAVAH JAR JAVA_LIBS JAVA_CPPFLAGS JAVA_LD_LIBRARY_PATH
Running: /bin/bash

RJDBC をインストールします。

RJDBCをインストール（実行結果）

$ R

R version 3.6.1 (2019-07-05) -- "Action of the Toes"
Copyright (C) 2019 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)
（省略）

> install.packages("RJDBC")

さぁ接続してみよう

以下は接続するサンプルです。データ取得時に列名が大文字だと面倒な人ので取得直後に小文字に変換しちゃっています。 "com.ibm.db2.jcc.DB2Driver" 等のドライバー名は省略可能なことが多いんですが、省略不可の時に「なんだっけなー」って悩むことが多いので明記しています。

サンプル

# ヒープ領域の最大サイズを指定（確かデフォルトが 64M とか小さかった記憶があるので明示的に指定）
options(java.parameters = "-Xmx1024m")
#
library(RJDBC)
library(dplyr)

# IBM DB2 の場合 (db2jcc4.jar は事前に IBM のサイトからゲットしておく )
con <- JDBC("com.ibm.db2.jcc.DB2Driver", "db2jcc4.jar") %>%
	dbConnect("jdbc:db2://{サーバアドレス}:50000/{DB名}", user="{ユーザーID}", password="{パスワード}")

# Microsoft SQL Server の場合 (mssql-jdbc-7.4.1.jre8.jar はマイクロソフトのサイトからゲットしておく )（試したはかなり古そうな SQL Server だったので最新版で大丈夫かはかなり不安）
con <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver", "mssql-jdbc-7.4.1.jre8.jar") %>%
    dbConnect("jdbc:sqlserver://{サーバアドレス}:1433;databaseName={DB名}", user="{ユーザーID}", password="{パスワード}")

# SQLite の場合 （sqlite-jdbc-3.27.2.1.jar は事前にゲットしておく )
con <- JDBC("org.sqlite.JDBC", "sqlite-jdbc-3.27.2.1.jar") %>%
	dbConnect("jdbc:sqlite:{ファイル名}")

# H2 Database( ローカル ) の場合 (h2-1.4.200.jar は事前にゲットしておく )
con <- JDBC("org.h2.Driver", "h2-1.4.200.jar") %>%
    dbConnect("jdbc:h2:{ファイル名}")

# PostgreSQL の場合 (postgresql-42.2.9.jar は事前にゲットしておく )
con <- JDBC("org.postgresql.Driver", "postgresql-42.2.9.jar") %>%
	dbConnect("jdbc:postgresql://{サーバアドレス}/{DB名}", user="{ユーザーID}", password="{パスワード}")

# MariaDB の場合 (mariadb-java-client-2.5.2.jar は事前にゲットしておく )
con <- JDBC("org.mariadb.jdbc.Driver", "mariadb-java-client-2.5.2.jar") %>%
	dbConnect("jdbc:mariadb://{サーバアドレス}/{DB名}", user="{ユーザーID}", password="{パスワード}")

# MySQL の場合 （mysql-connector-java-8.0.18.jar は事前にゲットしておく )
con <- JDBC("com.mysql.cj.jdbc.Driver", "mysql-connector-java-8.0.18.jar") %>%
	dbConnect("jdbc:mysql://{サーバアドレス}/{DB名}", user="{ユーザーID}", password="{パスワード}")

# MySQL でもし The server time zone value 'unknown' って言われちゃう時は明示的にタイムゾーンを指定
con <- JDBC("com.mysql.cj.jdbc.Driver", "mysql-connector-java-8.0.18.jar") %>%
	dbConnect("jdbc:mysql://{サーバアドレス}/{DB名}?serverTimezone=UTC", user="{ユーザーID}", password="{パスワード}")

# テーブル一覧の取得
dbListTables(con)

# テーブル指定でデータ取得 + 列名を小文字に変換 ※ MariaDB, MySQL, MS SQL Server ではエラーになって取得できなかった
dbReadTable(con, "SAMPLE") %>% rename_all(tolower)

# SQLを使ってデータ取得 + 列名を小文字に変換
dbGetQuery(con,"SELECT * FROM SAMPLE") %>% rename_all(tolower)

# 100行だけデータ取得 + 列名を小文字に変換
dbSendQuery(con, "SELECT * FROM SAMPLE") %>% fetch(n=100) %>% rename_all(tolower)

なんで Oracle ないの？

単に私が Oracle サーバーを構築したことはあっても、 Oracle に ODBC や　JDBC で接続して作業した経験がなかったのと、手元に自由に使えそうな環境も見当たらなかったからです。私自身も今回書いてて気づきました。わ、わたしってそうだったのねって。

メモリーエラーが出たときは？

レコード数が多いと以下のようなエラーで失敗してしまうことがあります。

Error in .jcall(rp, "I", "fetch", stride, block) :
  java.lang.OutOfMemoryError: Java heap space

その場合は以下の 1024 の部分を増やしてみます。 R を対話モードで使用している場合は、一旦 R を終了してから再度試す必要があるようです。

ヒープ領域の最大サイズを指定

options(java.parameters = "-Xmx1024m") #ヒープ領域の最大サイズを指定（確かデフォルトが 64M と小さかったような気がするので…）

思った以上に簡単だった

DB2 あたりなんかはもっともっと苦戦するかと思っていたのですが拍子抜けするくらいさくっと繋がってしまいました。またデータベースが変わっても JDBCドライバー（ jar ファイル）をダウンロードして指定するだけで本当に使えました。

ただデータベースによっては dbReadTable がエラーになっちゃうから dbGetQuery 使っとこうかとか MySQL だとタイムゾーンのエラー出ちゃうとかあったので、本格的に使い出したら他にもあるかもしれないです。でもとりあえずは良しとしておきたいと思います。よしよし。