RからODBC経由でFileMaker Proのデータベースに接続する

R
FileMaker Pro
作者

伊東宏樹

公開

2024年5月9日

「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 パッケージを読み込みます。

library(odbc)
library(dbplyr)
library(dplyr)
## 
## 次のパッケージを付け加えます: 'dplyr'
## 以下のオブジェクトは 'package:dbplyr' からマスクされています:
## 
##     ident, sql
## 以下のオブジェクトは 'package:stats' からマスクされています:
## 
##     filter, lag
## 以下のオブジェクトは 'package:base' からマスクされています:
## 
##     intersect, setdiff, setequal, union

データソースの確認

odbcListDataSources()関数でデータソースを表示します。

odbcListDataSources()
##             name                                                  description
## 1 palmerpenguins /Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so

返り値にはちゃんと palmerpenguins がありました。

データベースに接続

dbConnect()関数でデータベースに接続します。

con <- dbConnect(odbc::odbc(), dsn = "palmerpenguins", uid = "Admin")

データソースを使わずに、ドライバとサーバなどを指定しても接続できるようです。

con2 <- dbConnect(odbc::odbc(),
                 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の関数で操作することは可能です。

tbl <- db_collect(con, "SELECT * FROM penguins") |>
  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 でした。