ODBCの仕組みと利用方法について紹介します。
仕組みを知っていると問題が起きたときにいろいろ対処できるようになります。
１.ODBCドライバのインストール
２.データソース（DSN)を作成
３.作成したDSNをツールで指定
ODBCの基本的な仕組み
LinuxでもODBC接続可能なものもありますが、一般的な利用ケースであるWindows OSに絞って説明します。
ODBCに対応したアプリケーションから、WindowsのODBCドライバマネージャを経由して各データベースに対応したODBCドライバが呼び出され、データベースに接続します。
ODBCドライバとデータベースサーバの間は、通常TCP/IPのネットワーク通信が行われます。
データベースサーバのOSは、どんなOS(Windows, Linux ,… )でも接続可能です。
・BIツール
Power BI/Tableau/Qlik Sense/MotionBoardなど
・ExcelやAccessなどWindowsアプリ
・汎用SQLクライアントツール
A5:SQL Mk-2 など(https://a5m2.mmatsubara.com/)
ちなみにBIツールのデータベースの接続方法は、JDBC接続、ODBC接続、その他の接続方法があります。
ODBCドライバについて
データベースごとにODBCドライバのインストールが必要です。
64bit Windowsで 32bitのODBCドライバを使うことも可能です。
・ODBCドライバには、３２ビット版と６４ビット版が存在する
・利用するデータベースのバージョンによってドライバが異なる
ODBCドライバには、３２ビット版と６４ビット版があります。
ODBCを使うアプリケーションが３２ビット版か６４ビット版かで決まります。
最近のアプリケーションの主流は６４ビット版ですが、たまに３２ビットの場合があります。
簡単な確認方法は、アプリケーションを起動して、Windowsの「タスクマネージャー」-「プロセス」でアプリケーション名の後ろに(３２ビット)と表示されているかで判断できます。
下記の場合、「Microsoft OneDrive(３２ビット)」は。３２ビットのアプリケーションです。
・３２ビットアプリケーションは３２ビットODBCドライバが必要（x86というファイル名だったりします）
・６４ビットアプリケーションは６４ビットODBCドライバが必要
使っているデータベースによってはデータベースのバージョンも確認する必要があります。
PostgreSQLの場合、ドライバのダウンロードサイトには以下のようにバージョンごとにファイルが用意されています。
psqlodbc_12_02_0000-x64.zip ：バージョン１２の６４ビットODBCドライバ
psqlodbc_12_02_0000-x86.zip ：バージョン１２の３２ビットODBCドライバ
特に歴史の長いデータソースの場合は、互換性などの問題で適切なものを選ぶ必要があります。
ODBCドライバを設定する（DSNを作成）
ODBCドライバをインストール後に接続情報の設定が必要になります。
データソース(DSN)を作成する作業が必要です。
ODBCデータソース アドミニストレーターも３２ビット版と６４ビット版があります。
使うODBCドライバと合わせる必要があります。
接続するデータベースサーバのマシン名（IPアドレス）、接続するユーザ名、パスワードなどを設定します。
検索ボックスで、「ODBC」と入力します。
“ODBC データ ソース(64ビット)” と “ODBC Data Sources(32-bit)”が選択肢として表示されます。
または、[スタート] – [Windows 管理ツール] - [ODBC データ ソース(64ビット)] を選択します。
選択すると「ODBCデータソース アドミニストレーター(64ビット)」が起動されます。
[追加]ボタンを押す
データソースを新規作成します。
セットアップするデータソースのドライバーを選択します。
以降は、データベースごとに設定が異なりますので、データベース専用の設定になります。
接続サーバのホスト名(IPアドレス)、接続DBユーザ、パスワードなどを設定します。
★PostgreSQLを選択時
★MariaDBを選択時
★SQLServerを選択時
ODBCデータソース アドミニストレーター
ODBCデータソース アドミニストレーターは、ODBCの接続情報を管理するものです。
DSN（データソース）という単位で管理します。
・ユーザDSN
Windowsユーザごとに設定する場合に利用。
ログインしているユーザのみが使用する場合は、ユーザDSNを使用する。
同じＰＣで複数のユーザアカウントで使用していた場合、他のユーザが登録したＤＳＮは使用できません。
・システムDSN
利用しているPCのWindowsの全ユーザが利用可能なDSNになります。
・ファイルDSN
設定情報がファイルとして作成されます。
接続情報をファイルとしてコピーできるので、複数のPCに設定が可能です。
手軽だけどどセキュリティ面は考慮する必要があります。ファイルの漏洩など。
・ドライバー
インストール済みのドライバーが表示されます。
ドライバの種類やバージョンを確認できます。
接続予定のデータベースのドライバがインストールされていない場合、ODBCドライバをダウンロードしてインストールが必要です。
・トレース
何か問題があったときに設定し、ログファイルを見ると原因が分かる場合があります。
ただし、ODBC関数の呼び出しのトレースで、どの処理でエラーになったかが分かります。
各データベースのドライバ側にもログ機能があるので、それを有効にすると更に詳細をみることができます。
PostgreSQLの場合、[全体設定]でログ出力を指定可能です。
BIツールで利用
接続テスト用に新規に「MariaDB test」「PostgreSQL-test」「SQLServer-test」というDSNを作成して説明します。
Excelで利用
BIツールではありませんが、
ExcelからもODBC経由でデータベースへアクセスが可能です。
データベースから取得したデータをExcelのシートに読み込むことが可能です。
[データ]-[データを取得]-[その他のデータソースから(O)]-[ODBCから(D)] でデータソース名(DSN)が選択可能です。
PowerBIで利用
[データ]-[データを取得]-[詳細]-[データを取得]-[ODBC] でデータソース名(DSN)が選択可能です。
PowerBIもMicrosoftのツールなので、操作方法はExcelと同じような操作です。
Tableauで利用
[その他]-[その他のデータベース([ODBC)] でデータソース名(DSN)かドライバを選択可能です。
実は有名なDBは、専用の接続ダイアログが用意されている
ツール側で専用の接続メニューが用意されている場合は、それを使っていいです。
使用メモリが少ないとか、処理が高速だとか、標準のODBCドライバではできないことがサポートされているようです。
・ツールが提供している接続設定ではうまく動かない
・専用の接続メニューが用意されていないデータベースに接続する必要がある
・新しいバージョンのデータベースにツールの接続設定が対応していない
PowerBI でPostgreSQLを使う場合
Tableau でPostgreSQLを使う場合
まとめ
ODBC自体は古い規格のAPIでODBCを使ってプログラミングすることはないですが、仕組みを知っているとデータベースの接続で何か問題あったときに対応できるようになります。
ドライバが古いから最新版に入れ替えてみようとか、ODBCの設定を変更してみようとか。
