library(odbc)
library(dbplyr)
library(dplyr)
##
## 次のパッケージを付け加えます: 'dplyr'
## 以下のオブジェクトは 'package:dbplyr' からマスクされています:
##
## ident, sql
## 以下のオブジェクトは 'package:stats' からマスクされています:
##
## filter, lag
## 以下のオブジェクトは 'package:base' からマスクされています:
##
## intersect, setdiff, setequal, union
「Rのdbplyrでサブクエリを構築すると分かりやすい」を読んで、ふとFileMaker ProのデータベースにRから接続してみようと思ったので、やってみました。以前は、RODBCパッケージをつかいましたが、今回はodbcパッケージを使用しました。
準備
FileMaker Proデータベース
palmerpenguisパッケージのデータをFileMaker Proのデータベースに変換しておきました。このデータベースを使用します。テーブル名は「penguins」としてあります。
[ファイル] メニュー の [共有設定] > [ODBC/JDBC を有効にする…] で、[ODBC/JDBC 共有:] を「オン」にしておきます。
palmerpenguins のデータベースは開いたままにしておきます。
ODBCクライアントドライバのインストール
FileMaker Pro同梱のODBCクライアントドライバをインストールします。詳細は『FileMaker ODBC と JDBC ガイド』の第3章にあります。
データソースの設定
ODBC Managerをインストールしておきます。これをつかってデータソースを追加します。
ODBC Managerを起動します。データソースとして「ユーザ DSN」と「システム DSN」がありますが、「ユーザ DSN」を選択しておいてデータソースを追加しても、追加されるのはシステム DSNの方になります。
データソースを追加するため、まず右の「追加…」ボタンを押します。
ドライバの選択では「FileMaker ODBC」を選択し、「OK」ボタンを押します。すると、「FileMaker DSN の構成」ウィンドウが出ます。
「続行」ボタンを押し、「データソースへの参照に使用する名前」は「palmerpenguins」としました。「続行」ボタンを押して、次にすすみます。
次の画面でホストは「localhost」とし、「ホストに接続して使用可能なデータベースの名前を取得する。」のチェックを入れて「続行」します。
その次の「データベース情報を入力」の画面ではデータベースとして「palmerpenguins」を選択して、「続行」ボタンを押します。
最後に、さらにその次の画面で「テスト」ボタンを押して接続テストをおこないます。ユーザ IDは「Admin」、パスワードは空白(デフォルト設定ならこれで良いはず)とします。テスト成功ならOKです。
ただ、ここからもう一手間あります。
これで設定されるファイルは/Libary/ODBC/odbc.ini
および/Libary/ODBC/odbcinst.ini
なのですが、あとでつかうodbcパッケージでは~/.odbc.ini
と~/.odbcinst.ini
を見に行くようです。そして、~/.odbc.ini
と~/.odbcinst.ini
はそれぞれ~/Libary/ODBC/odbc.ini
および~/Libary/ODBC/odbcinst.ini
へのシンボリックリンクになっています。
そこでとりあえず、~/Library/ODBC
以下に/Library/ODBC
以下のファイルをコピーしました。
cd ~/Library/ODBC
cp /Library/ODBC/odbc.ini .
cp /Library/ODBC/odbcinst.ini .
odbc.ini
の内容は以下のとおりです。
[ODBC Data Sources]
palmerpenguins = FileMaker ODBC
[palmerpenguins]
Driver = /Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so
Description = palmer penguins
Server = localhost
CertificateFailureType = Warning
Database = palmerpenguins
UseLongVarchar =
AutoDetectEncoding =
WideAPI =
UnicodeTextTypes =
MultiByteEncoding =
QueryLog_On =
QueryLogTime =
QueryLogFile =
RからFileMaker Proデータベースに接続
odbc, dbplyr, dplyr パッケージを読み込みます。
データソースの確認
odbcListDataSources()
関数でデータソースを表示します。
odbcListDataSources()
## name description
## 1 palmerpenguins /Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so
返り値にはちゃんと palmerpenguins がありました。
データベースに接続
dbConnect()
関数でデータベースに接続します。
<- dbConnect(odbc::odbc(), dsn = "palmerpenguins", uid = "Admin") con
データソースを使わずに、ドライバとサーバなどを指定しても接続できるようです。
<- dbConnect(odbc::odbc(),
con2 driver = "/Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so",
server = "localhost",
database = "palmerpenguins",
uid = "Admin")
dbListTables()
関数でテーブルのリストを取得します。
dbListTables(con)
## [1] "penguins"
ちゃんとpenguinsがありました。
データの取得
dbGetQuery()
関数を使って SQL でデータを取得できます。
dbGetQuery(con, sql("SELECT * FROM penguins WHERE body_mass_g >= 6000"))
## species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
## 1 Gentoo Biscoe 49.2 15.2 221 6300
## 2 Gentoo Biscoe 59.6 17.0 230 6050
## 3 Gentoo Biscoe 51.1 16.3 220 6000
## 4 Gentoo Biscoe 48.8 16.2 222 6000
## sex year
## 1 male 2007
## 2 male 2007
## 3 male 2008
## 4 male 2009
ここで、tbl(con, "penguins")
として、dbplyrでデータベースにアクセスしたいところなのですが、現状ではエラーになります。
Error in `collect()`:
! Failed to collect lazy table.
Caused by error:
! nanodbc/nanodbc.cpp:1528: 00000
[FileMaker][FileMaker] FQL0001/(3:8): There is an error in the syntax of the query.
Run `rlang::last_trace()` to see where the error occurred.
ただ、db_collect() 関数でデータを取得して、それをdplyrの関数で操作することは可能です。
<- db_collect(con, "SELECT * FROM penguins") |>
tbl as_tibble()
|>
tbl filter(!is.na(sex)) |>
group_by(species, sex) |>
summarise(bill_length_mean = mean(body_mass_g, na.rm = TRUE))
## `summarise()` has grouped output by 'species'. You can override using the
## `.groups` argument.
## # A tibble: 6 × 3
## # Groups: species [3]
## species sex bill_length_mean
## <chr> <chr> <dbl>
## 1 Adelie female 3369.
## 2 Adelie male 4043.
## 3 Chinstrap female 3527.
## 4 Chinstrap male 3939.
## 5 Gentoo female 4680.
## 6 Gentoo male 5485.
バージョン情報
使用したソフトウェアのバージョンは以下のとおりです。
sessionInfo()
## R version 4.4.0 (2024-04-24)
## Platform: aarch64-apple-darwin20
## Running under: macOS Sonoma 14.4.1
##
## Matrix products: default
## BLAS: /Library/Frameworks/R.framework/Versions/4.4-arm64/Resources/lib/libRblas.0.dylib
## LAPACK: /Library/Frameworks/R.framework/Versions/4.4-arm64/Resources/lib/libRlapack.dylib; LAPACK version 3.12.0
##
## locale:
## [1] ja_JP.UTF-8/en_US.ISO8859-1/ja_JP.UTF-8/C/ja_JP.UTF-8/ja_JP.UTF-8
##
## time zone: Asia/Tokyo
## tzcode source: internal
##
## attached base packages:
## [1] stats graphics grDevices utils datasets methods base
##
## other attached packages:
## [1] dplyr_1.1.4 dbplyr_2.5.0 odbc_1.4.2
##
## loaded via a namespace (and not attached):
## [1] vctrs_0.6.5 cli_3.6.2 knitr_1.46 rlang_1.1.3
## [5] xfun_0.43 DBI_1.2.2 generics_0.1.3 jsonlite_1.8.8
## [9] glue_1.7.0 bit_4.0.5 htmltools_0.5.8.1 hms_1.1.3
## [13] fansi_1.0.6 rmarkdown_2.26 tibble_3.2.1 evaluate_0.23
## [17] fastmap_1.1.1 yaml_2.3.8 lifecycle_1.0.4 compiler_4.4.0
## [21] blob_1.2.4 htmlwidgets_1.6.4 Rcpp_1.0.12 pkgconfig_2.0.3
## [25] rstudioapi_0.16.0 digest_0.6.35 R6_2.5.1 tidyselect_1.2.1
## [29] utf8_1.2.4 pillar_1.9.0 magrittr_2.0.3 tools_4.4.0
## [33] bit64_4.0.5
FileMaker Pro のバージョンは 20.3.2.201 でした。