TiDB Cloud へ FileMaker から ODBC 接続してみる。初期設定からパフォーマンス検証まで

はじめに

対象読者

• Claris FileMaker ユーザ
• FileMaker で ODBC 接続して MySQL と連携させているよという人
• MySQL の代わりに MySQL ODBC Driver 互換で接続できるデータベースサービスが気になる人
• どちらかというと FileMaker ユーザ向けに TiDB Cloud というものがあるよ、ということを紹介するための記事であって、その逆ではない

what TiDB

• 公式サイト
• 説明文を公式サイトから引用

TiDBは、MySQL互換のスケーラブルなデータベースです。
クラウド/DX化でのデータベースの課題解決を支援します。

what TiDB Cloud

• 公式サイト
• 説明文を公式サイトから引用

フルマネージドサービスのTiDB Cloudは、TiDBの優れた機能をクラウド上で全て使用することができます。TiDB Cloudを利用することで、データベースの複雑さを気にすることなく、アプリケーション開発に集中することが出来ます。

TiDB Cloud への登録

アカウント作成

• まずは公式サイトからメールアドレス／パスワードを入力して登録開始。メールアドレス宛てに確認が届くので、認証しておきます。
• 認証後に、先ほどのメールアドレス／パスワードでログインを求められるので、ログイン。
  • 細かいことですがログインには Auth0 が導入されているよう。
• すると以下のように Tier の選択画面が出てくるので、ここでは FREE の Developer Tier を選んでおきましょう。

Cluster 作成

• 以下のような画面で Cluster の作成を求められます。

名前

• 任意で入力。

リージョン

• 無料枠ながらなんと Tokyo (ap-northeast-1) が選べます。素晴らしい。

作成完了まで待機

• 下端の Create ボタンを押すと次の画面へ遷移。
• 以下のような感じで、ぐるぐるとしばらく作成中……になるので完了まで待機です。

作成完了

• 5 分ほど待って完了すると以下のような画面に。

Cluster の確認

• 作成した Cluster の名前を押すと、詳細なダッシュボード画面に遷移します。

• メールアドレスや接続情報なども出ているので、画面全体をキャプチャするのは避けておきます。
• リソースモニタリングのグラフなんかも完備されていますね。

• overview からタブを切り替えると以下のように。

• データのインポートなんかも GUI 上から簡単にできるようです。

• バックアップは無料版では提供されていない機能。

• 発行された SQL も GUI で見られるの便利ですね。

• SlowQuery まで確認できる。至れり尽くせり。

• 視覚化まで。タイムゾーンはデフォルトはグリニッジ標準時のようす。
  • 記事の末尾で、データベース側のタイムゾーンの変更について触れます。

• タイムゾーンは変更できます。
  • 右上に表示されている自身のアカウント名をクリックすると organization settings というメニューが表示されるので、そこから設定できる画面が開かれます。

Cluster への接続設定

• 右上の Connect ボタンを押すと以下のようなモーダルが表示される。
• まず Step 1 で IP address による接続制限がかけられるので、良きように設定。
  • デフォルトだと現在接続中の IP が入力されるようになっています。
  • 後ほど FileMaker Server からの接続をおこないたいわけなので、FileMaker Server の IP を登録しておくとよいでしょう。

• Step 2 で MySQL Client での接続テストをおこなうことになりますが、事前に Connect ボタンの右隣にある三点メニューから root パスワードを設定しておく必要があります。

• あとは Step 2 として表示されている案内にしたがって接続テストをおこない、無事に接続できればヨシ。

• 以下は Linux の MySQL Client から接続して成功した例。

テーブルデータの用意

• 上記の通り、 TiDB CLoud に MySQL コマンドで接続している状態から話を続けます。
• show databases; を実行すると、あらかじめ test データベースが用意されているのが見えます。

• ただし show tables from test; を実行してみると、テーブルは空っぽですね。

• なのでテーブルを作成します。公式ドキュメントに従ってやってみましょう。

  • PingCAP - TiDB - テーブルを作成する

• まずデータベースは既にある test を使うことにします。

use test;

• 公式ドキュメントに books テーブルを作成するコマンドがあるので、そちらを引用して実行します。
  • テーブル名を test に書き換え。
  • ついでに主キーも設定。
  • id は AUTO_RANDOM な値が入るように。
    • ここで AUTO_RANDOM を設定すると、記事の後のほうで苦労することになりますので、苦労をしたくない人は属性付与しないでおいてください……

CREATE TABLE `test`.`books` (
  `id` bigint AUTO_RANDOM,
  `title` varchar(100),
  `type` enum('Magazine', 'Novel', 'Life', 'Arts', 'Comics', 'Education & Reference', 'Humanities & Social Sciences', 'Science & Technology', 'Kids', 'Sports'),
  `published_at` datetime,
  `stock` int,
  `price` decimal(15,2),
  PRIMARY KEY (`id`)
);

• あらためて show tables from test; を実行すると、ちゃんと books テーブルが作成されている。

• show columns from books; でカラムが作成されていることも確認。

• TiDB の管理画面でも実行した SQL 文が確認できる。クエリの実行時間の詳細がここまですぐに見られるのはとても魅力的！

FileMaker Server の設定

MySQL ODBC Driver のインストール

• では次に FileMaker Server 側の設定へ進みます。
• 最初に MySQL ODBC Driver のインストールをおこないます。
• 詳細は以下、私の書いた過去記事をご参照ください。
  • Qiita - FileMakerServer19稼働のCentOSサーバにMySQLのODBCドライバを設定
• 簡易的にコマンドだけ貼り付けておきます。

sudo rpm -Uvh http://repo.mysql.com/mysql80-community-release-el7.rpm
sudo yum install mysql-connector-odbc

odbc.ini の準備

• 次に odbc.ini ファイルを用意しましょう。
  • Claris 公式ヘルプ - 外部 ODBC データソースへのアクセス
• 以下のようにエディタに応じたコマンドで作成／編集。

sudo vi /etc/odbc.ini

• 中身を以下のようにします。要注意点としては Port が 4000 であることでしょうか。

odbc.ini

[ODBC Data Sources]
TiDB_FileMakerTest = MyODBC 8.0 UNICODE Driver DSN

[TiDB_FileMakerTest]
Driver           = /usr/lib64/libmyodbc8w.so
SERVER           = [[ダッシュボード上に Endpoint として記載されているもの]]
PORT             = 4000
Database         = test
OPTION           = 3
INTERACTIVE      = 1
BIG_PACKETS      = 1
NO_PROMPT        = 1
COMPRESSED_PROTO = 1
AUTO_RECONNECT   = 1
MULTI_STATEMENTS = 1

FileMaker Pro から TiDB Cloud への接続

• さて今回、あらかじめ FileMaker Server 上にファイルが既にホストされている、という前提で話を進めます。そのファイルを開いて TiDB への接続設定をおこなっていきます。
• ファイル > 管理 > 外部データソース を選択。

• データソースの編集ウィンドウで タイプ を ODBC にして DNS の右端にある 指定 ボタンを押すと、以下のようなウィンドウが表示されて odbc.ini に記述したものが表示されます。

• 上記を選択して進めた次には、認証設定です。ユーザ名／パスワードについては、本来は root 以外のユーザを用意しておくのが正しいわけですが、ここではひとまず root で、先ほど設定したパスワードを入力しましょう。
  • なお root ユーザを入力する際には、ダッシュボード上に記載されている通りに。

• ファイル > 管理 > データベース > リレーションシップ を選択して、新しくテーブルオカレンスを追加しようとすると、データソースとして TiDB_FileMakerTest など先ほど追加したものが選べるようになっています。
• それを選ぶと、先ほど作成したテーブル books が表示されました。

• books を選んで進めると、以下のようにテーブルオカレンスとして出現。

• タブを フィールド に切り替えてみても、中身がしっかり定義されて出てくれています。
  • enum 型であった type カラムは FileMaker 上では テキスト として処理されてしまっていますね。これはいたしかたない。

表示等操作のためのレイアウト用意

• この追加した TiDB Cloud 上の books テーブルを FileMaker Pro で表示等操作するためのレイアウトを用意します。
• 用意、といっても既にテーブルオカレンスを追加した時点でレイアウトが作成されている（設定による）ので、レイアウトを books に切り替えてみると、既にフィールドもありますね。

FileMaker Pro 側からレコードを作成

• 試しにひとつレコードを作成してみましょう。
• 『方丈記』を入力して……おや、怒られました。先ほど設定した auto_random が悪さをしてしまった様子。余計なことをやってしまったな……

@@allow_auto_random_explicit_insert について

• 公式ドキュメントを確認してみます。
  • PingCAP - TiDB - AUTO_RANDOM

• 引用すると、以下とのこと。

v4.0.3 以降、値を明示的に挿入する場合は、システム変数@@allow_auto_random_explicit_insertの値を1 (デフォルトでは0 ) に設定します。この明示的な挿入はデフォルトではサポートされておらず、その理由は制限セクションに記載されています。

• ではシステム変数をどのようにして変更したら良いかもドキュメントを確認してみます。
  • PingCAP - TiDB - システム変数

• 引用すると、以下とのこと。

変更はSETステートメントを使用して行われます。

• なので MySQL client で接続して、以下のコマンドを実行します。
  • global であることを明示することが必要そうです。

SET @@global.allow_auto_random_explicit_insert = 1;

• 管理画面上でも確認できたので、届いたのでしょう。

ふたたび FileMaker Pro 側からレコードを作成

• ということで FileMaker Pro へ戻ってきて今度こそ……！
• はい、無事に登録できました。

• あれだけ苦労した AUTO_RANDOM 設定、FileMaker Pro 経由だとこのように怒られてしまって自動で採番されてくれません。残念……

速度検証_経過編

前置き

• FileMaker Pro から ODBC 接続で MySQL 等の外部データソースを閲覧する際、どうしてもネックになるのが速度が遅い、という点です。
• なので、いくつかの実験をして TiDB Cloud ならどのくらいのパフォーマンスが出るのか、検証してみます。
• この 経過編 けっこう長いので、さっくりと比較表で結果だけ見たい！　という方は、スーッと下の方に飛んでいって まとめ編 をご覧ください。

準備。データセットの用意

• まずデータセットについてですが、どこかしらかダウンロードしてきたり、ヨソで生成したきたりしてもよいのですが、せっかくなので FileMaker Pro 上でレコード作成をループでおこない、これ自体をパフォーマンステストしてみましょうか。
• FileMaker Pro からは残念ながら bulk insert みたいなことができないので、地道に n 件の SQL 発行する感じです。

事前準備

• 比較用に FileMaker Pro 側でも同じ構造のテーブルを用意しておきます。

• また TiDB 側の books テーブルにも index を張っておきます。
• TiDB の管理画面に Playground があり、そこから実際に SQL を実行することができるようになっていますので、ここからやってみましょう。

• 左側メニュー一番下の Edit and Run Your SQL を選び、実際に SQL を書いて実行すると、たとえば以下のようにその場で結果が表示されます。便利ですね。

show columns from test.books;

• title と type に index を張ってみましょう。

ALTER TABLE test.books ADD INDEX index_books_on_title(title);
ALTER TABLE test.books ADD INDEX index_books_on_type(type);

• あらためてカラムを確認すると、しっかり張られたことが確認できますね。

スクリプト作成

• レコード作成するためのスクリプトを作ります。完成形は以下です。

前提

• 今さらですが FileMaker Server を稼働させているサーバのスペックは以下の通りです。TiDB の問題というより FileMaker Server 側の問題として、スペック上げればもう少しパフォーマンス出るはず。
  • ConoHa VPS
  • リージョン 東京
  • メモリ 1GB
    • 貧弱貧弱ゥ……！
  • CPU 2 core
  • ストレージ SSD 100GB

FileMaker Pro 側のテーブル

• 上記、用意したスクリプトの変数 $env を fm にセットしてからスクリプトを実行してみます。
• ループ回数を 1,000,000 にして 100 万レコードを作成してみましょうか。

• 一晩走らせてみてこんな感じでした。……うん、100 万レコードはやめて、まず 1 万レコードくらいでやってみましょう……
• せっかく作成した 100 万レコードについては csv データとしてエクスポートして保管しておきます。後で使います。
• 1 万レコードの結果は以下の通り。

TiDB 側のテーブル

• 今度は TiDB 側のテーブルにレコードを作成してみます。変数 $env を tidb に変更して実行。
• 1 万レコードの結果は以下の通り。

• さすがに ODBC 接続している先に SQL を投げているので FileMaker 側のテーブルへ実行するより遅いですが、FileMaker ユーザ感覚からすると問題なく許容範囲です。

レコードのインポート

• では、先ほど csv として待避しておいた 100 万行のレコードを、それぞれのテーブルへ食べさせてみたいと思います。
• あらかじめ先ほど 10,000 件作成したレコードは削除しておきます。
• オマケ的にそのパフォーマンスも測ってみますが、FileMaker 側は一瞬でした。

• TiDB 側もどうか……と思って試したところ、効いてくれませんでした。削除されません。ドキュメントによると FileMaker 側としてサポートしていない様子。
  • 試したこと無かったから知らなかった……

[現在のテーブル] を選択しており、このスクリプトステップが実行されたときのアクティブなテーブルオカレンスが ODBC データソースからのものである場合は、[テーブルデータを削除] はスキップされてエラーコードが返されます。アクティブなテーブルオカレンスが外部の FileMaker Pro ファイルのものである場合、[テーブルデータを削除] は通常どおり実行されます。

• なので 対象レコードの削除 で実行。作成に比べると高速ですね。

FileMaker 側

• まずは FileMaker 側から。
• 結果は 12 分ほどと、まあまあまずまずな感じです。

TiDB 側

• お次は TiDB 側……
• ……ということで試してみたのですが、数時間経過して 75 万レコードほどがインポートされたあたりで FileMaker Pro が強制終了で落ちてしまいました。
• このように FileMaker Pro を通じて ODBC 接続先に大量データを食べさせようとする試みは、なかなかにうまくいきません。ので、別ルートでデータを用意しましょう。

SQL で TiDB へデータインポート

• TiDB の管理画面に import メニューがあるので、そこから csv を食べさせられるかどうか見てみましょう。
• どうやら AWS S3 環境が必要とのこと。ローカルから直接 csv アップロードできるともっと便利になりそうです。

• 今回はおとなしくサーバ側に csv をアップロードしてインポートさせます。カラム順はそのままにということで……

load data local infile "/home/username/csvname.csv " into table test.books fields terminated by ',' optionally enclosed by '"';

• もしまだ local_infile を有効にしていなければ、事前に有効にしてからにしましょう。TiDB においても MySQL と同様です。有効にしてあれば --local-infile オプションをつけてデータベースへ接続します。

SET GLOBAL local_infile = 1;

• ……で、これでうまくいくと思いきや。何か warnings が出て失敗します。うーむ。

• 小数点つけておけばいいのか……？　と思って置換処理かけてみてもダメでした。FileMaker Pro からのインポートならうまくいっていたのですが・・

• ということで大人しく price カラムを INT 型にしておこう……

alter table test.books modify price int;

• しかしこれでもダメ。えええ……おかしなレコードは一つもないはずなんだけれどな……
• ……ここで気づく。改行コード問題！　CRLF になっていた！　ということで SQL に lines terminated by '\r\n' を追記。
  • 恥ずかしや。初心者かい！　っていう……
  • 型を変える必要もありませんでした。まあ戻す必要もないか……

load data local infile "/home/username/csvname.csv " into table test.books fields terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

• すると今度はサーバが落ちるようになってしまった。なんで。
  • 一度ダウンすると数分何もできなくなる。数分後に復旧される。管理画面から明示的に再起動をすることもできなそう……？
  • AWS の障害でもなさそう。
  • 100 万行だと重いのかと思って 50 万行に減らしてみてもダメでした。

• ダウン中に接続を試みるとユーザ名が違うと怒られる。

• 10 レコードに絞ったインポートは正常完了しました。しかし 10 行では……

• 再度 50 万レコードを試すとやはりダウン。おおう……
• ……ということで 5 万レコードの投入に留めることに。実行直後、以下のように怒られたのですが、実際は 50,000 レコードが無事に作成されていました。何だろう……？
  • なお FileMaker 側は 100 万レコードなのですが、内部データベースと外部データベースを比較しても……というのはあるので、ハンデとして……

レコードの検索

スクリプトの準備

• 本のタイトル title に 3 という値が含まれているレコードを検索する、ということでやってみましょう。
• 以下のようなスクリプトになります。

FileMaker 側

• まず FileMaker 側からですが 1 秒以内で結果が表示されました。

TiDB 側

• TiDB のほうは、複数回実行してみましたが、毎度 3 秒でした。
• FileMaker Pro ユーザとしては、実用性は十分ですね。

レコードのソート

スクリプトの準備

• 続いて本のタイトル title で降順ソートしてみます。
• スクリプトは以下の通り。

FileMaker 側

• 25 秒ほどでした。ソートだけでも結構かかりますね。

TiDB 側

• TiDB のほうは、これも 3 秒でした。
• さすがに FileMaker 側とはレコード数が違う！　というのはあるのですが、 50,000 レコードくらいだと全く問題なさそうです。

フィールド内容の全置換

スクリプトの準備

• 最後にフィールド内容の全置換を試みます。本のタイトル title について、全く同じ内容を再度書き込むというかたちで。
• 対象レコードは 100 万だと多すぎるので 1 万に絞る処理を挟みます。
  • 試しに FileMaker 側で 100 万で試してみたところ、お察しな時間のかかり方だったので減らしました。
• スクリプトは以下の通り。

FileMaker 側

• 1 分 22 秒。いやはや、結構かかりますね。
• 100 万だと単純に 100 倍なので 2 時間くらいですか。

TiDB 側

• TiDB のほうは 2 分 41 秒。
• FileMaker 側と同レコード数でこれというのは、なかなかの健闘っぷりではないでしょうか！

比較対象の追加。PlanetScale

• えー、ここまで来ておいてナンですが、比較対象が FileMaker 本体の内部データベースだけだと、やはり実質的な比較にはならないですよね。
• ということで今回は PlanetScale の Free 版 を用意してみます。
  • PlanetScale の環境設定は別記事にて掲載予定ということで省略します。
• なお PlanetScale のレコード数は TiDB と同じく 5 万としました。

レコードの検索

• PlanetScale では 3 秒。
• TiDB と同じですね。

レコードのソート

• PlanetScale では 5 秒。
• TiDB と有意に差がつきましたね。

フィールド内容の全置換

• PlanetScale では 3 分 8 秒。
• こちらも TiDB よりも遅めです。

速度検証_まとめ編

• ということで まとめ編 として、以下、表にまとめてみました。
• 外部データベースとして ODBC 接続を経由する TiDB Cloud は、さすがに FileMaker そのものと比べると分が悪くなりますが、同じ ODBC 接続の PlanetScale と比べたら良いパフォーマンスを発揮してくれていることが分かります。

	FileMaker	TiDB	PlanetScale
レコードの検索	0 分 1 秒	0 分 3 秒	0 分 3 秒
レコードのソート	0 分 25 秒	0 分 3 秒	0 分 5 秒
フィールド内容の全置換	1 分 22 秒	2 分 41 秒	3 分 8 秒

• ※注意点
  • それぞれのテーブルのレコード数は以下の通り
    • FileMaker : 100 万
    • TiDB, PlanetScale : 5 万
  • ただし フィールド内容の全置換 はすべてにおいて 1 万レコードに絞り込まれている

おまけ

タイムゾーンの変更

• TiDB 管理画面の Playground からタイムゾーンの変更をおこなってみましょう。
• デフォルトだと以下のような設定だと確認できます。

SELECT @@global.time_zone;

• これを日本へ変更します。

SET GLOBAL time_zone = '+9:00';

• ……はい、怒られました。Playground では権限が不足するようです。そりゃそうですね、Playground ですからね。
• ということでサーバへログインしてから SQL 実行しましょう。

• 再度 Playground で確認してみると、正常に反映されています。

Playground では安心して SQL 実行できる

• 上記のように Playground では怒られて実行できないことはあります。たとえば truncate = テーブルデータ削除のような、危ないことなんかもできません。

truncate table test.books;

• なので安心して遊べます。

おわりに

まとめ

• 以上、TiDB の SaaS 環境である TiDB Cloud を、アカウント登録から基本設定を経て、FileMaker と ODBC 接続できるようにした上で、パフォーマンスの検証まで、リアルタイム的に試行錯誤しながら書いた記事でした。
• パフォーマンス検証においては、同じ SaaS で MySQL 互換性のある、今流行りの PlanetScale よりも良いパフォーマンスが出ていたというのが特徴的でしたね。本来の TiDB は単なる MySQL 互換目的で使われるべきものというわけではないのですが、このような用途でも実力が発揮されているということが分かりました。
• TiDB Cloud のフリー版は一年間という期限つきなので、本格的に導入しようかなと検討する場合には、以下ページで価格が確認できます。
  • 従量制なので、シミュレータがページ内にあると嬉しいですね。
  • そして今 ( 2022/10 時点 ) 円安なのがツラいところですね……

雑感

• 個人的には管理画面がイイ感じだったのがとても魅力的でした。マネージドサービスの良いところですよね、ほんと。
• もし本記事で分かりにくいところがあれば、お気軽にコメント等でご質問いただけたらと思います🙏
• そもそも FileMaker Server 側のサーバスペックをもっと上げたら、もう少しパフォーマンスが上がるはずなので、いつか本番環境などで試してみたいところ。