CData Japan 技術ディレクター桑島です。本記事では、多くのお問い合わせをいただいている Microsoft AccessのリンクテーブルでGoogle BigQueryのデータを利用する方法をご紹介します。
利用製品
Microsoft Accessのリンクテーブルは、ODBC データソースを利用した作成が可能です。CData Google BigQuery ODBC Driverは、Google BigQueryのデータをODBCインタフェースでアクセスできる製品です。本製品を利用することで、Microsoft AccessのリンクテーブルからGoogle BigQueryにアクセス出来るようになります。
www.cdata.com
本製品は、30日間ご試用いただける無償評価版がございます。
利用方法
Google BigQuery側の設定
CData Google BigQuery ODBC Driverは、Google BigQueryへのアクセスには、BigQuery Web Service APIを使用しています。事前にGoogle Developer Consoleにてプロジェクトを作成して、APIを有効にする必要があります。
ODBC Driverのインストール
Microsoft Access がインストールされているマシンに、上記製品ページよりCData Google BigQuery ODBC Driver のWindows版をダウンロードします。
ダウンロードしたインストーラ（.exe）を起動してウィザードに従いセットアップを完了します。
途中、オンラインでのアクティベーションが必要となります。プロキシサーバーが設置されているNW環境などでアクティベーションが失敗する場合は、オフラインでのアクティベーション方法をご案内いたしますので弊社サポートデスクまでお問い合わせください。
Google BigQueryへの接続設定
インストールが完了するとブラウザで製品ヘルプが起動するとともに、ODBCのDSN構成画面が表示されます。
本、ODBC DSN構成画面を一時閉じてしまった場合には、Windows管理ツール内のODBC データソース(32ビット/64ビット)から再度開くことができます。なお、この際のビット数（32/64）は、インストールされているMicrosoft Access製品のビット数にあったものをお選びください。
OAuthでの接続方法
CData Google BigQuery ODBC Driverは、Google BigQueryへの認証としてOAuthの以下の方法をサポートしています。
- ユーザーアカウント認証
- サービスアカウント認証
- GCPインスタンスアカウント認証
詳細は製品マニュアルのこちらの章をご参照ください。
cdn.cdata.com
サービスアカウント認証をご利用の場合は以下の記事をご参照ください。
www.cdatablog.jp
本手順では最も簡単なユーザーアカウントの認証方法についてご紹介します。
本製品は、CData社が事前に作成されたOAuthアプリを利用する埋め込みOAuthクレデンシャルと呼ばれる方式をご利用できます。本方式では、カスタムOAuthアプリを作成することなく、ブラウザでのユーザ認証のみで利用することができます。詳細は製品マニュアルのこちらの章をご覧ください。
cdn.cdata.com
本方式で設定が必要なプロパティは、以下のAuth SchemeとProject Idのみです。Auth SchemeについてはOAuth、もしくは、デフォルトのAutoのままでも構いません。
- Auth Scheme : OAuthもしくはデフォルトのAuto
- Project Id：BigQueryのプロジェクトId
DSN構成画面からの設定
DSN構成画面より、接続プロパティを設定したら「接続のテスト」ボタンをクリックします。
デフォルトブラウザが起動してGoogleのログイン画面が表示されログインすると、以下のような認可範囲をOAuthアプリに許可する画面が表示されます。
Allow（認可）ボタンをクリックすると以下の認証が成功したメッセージが表示されます。
ブラウザを閉じてDSN構成画面を戻ると以下のように「接続テストに成功しました。」とのメッセージが表示されていればODBC DriverからGoogle BigQueryへの接続は成功です。
本ダイアログを閉じて、DSN構成画面もOKボタンで保存して閉じてください。ODBC データソースアドミニストレーターから起動している場合は、本画面もOKボタンで保存して閉じてください。
Microsoft Accessリンクテーブルからのアクセス
リンクテーブルの作成
Microsoft Accessの「外部データ > 新しいデータソース > 他のソースから > ODBCデータソース」を開きます。
外部データの取り込みウィザードにて「リンクテーブルを作成してソースデータにリンクする」を選択してOKボタンで進みます。
データソースの選択画面が開くのでコンピュータデータソース タブを開き、接続設定を行ったデータソース（デフォルトでは、ユーザDSNのCData GoogleBigQuery Source）を選択してOKボタンで進みます。
テーブルのリンク画面が開きます。テーブル タブにBigQuery内のテーブル情報がリストで表示されるので選択します。なお、BigQueryのビューについてもテーブルとしてアクセス可能です。
本例では、test1データセット内のProductsテーブルを選択します。
テーブルを選択すると「固有レコードの識別子の選択」ウィンドウが表示されますが、BigQueryには主キーや一意制約は無いので選択せずにOKボタンを押して終了します。
BigQueryのデータへのリンクテーブルが作成されました。
リンクテーブルからの利用
最も簡単な方法はリンクテーブルをクリックすることでBigQueryデータにアクセス可能です。
Tips 集
データセット、課金プロジェクトの指定
ODBC DSN構成の接続プロパティにて DatasetIdを指定すると指定したデータセットのみにアクセスが可能となります。
また、運用しているGoogle BigQueryの環境で課金用のプロジェクトを作成してジョブを実行するプロジェクトが異なる場合は、Billing ProjectIdを追加に指定することで課金プロジェクトを指定することが可能です。
Billing ProjectIdの詳細は製品マニュアルのこちらの章をご参照ください。
cdn.cdata.com
プロキシ・ファイアウォール設置環境での利用
Microsoft AccessのインストールされているマシンからGoogle BigQueryにアクセスする際にプロキシサーバやファイアウォールが設置されている場合は、ODBC DSN構成の追加のプロパティ設定を行うことで通信することが可能です。
詳細は以下の製品マニュアルをご参照ください。
cdn.cdata.com
大量データが格納されているテーブルへのアクセス
リンクテーブルの実行では、該当テーブルのデータから全件取得しようとするんのでレコード数が膨大な場合には注意が必要です。そのような場合は、行数を絞り込んでアクセスするように変更が可能です。ODBC DSN構成の接続プロパティに、その他 > Max Rowsプロパティがあります。デフォルトでは「-1」と全件取得する仕様となっていますが、こちらの値に整数をセットすることで、指定した行数のみ取得するようになります。
上記画面の例では、1をセットしているので、リンクテーブルにアクセスすると1レコードのみ取得されるようになります。
実際にはBigQueryには、以下のようなLIMIT句の追加されたSQLが発行されます。
SELECT `id`, `code`, `name` FROM `cdatajp-support`.`test1`.`Products` LIMIT 1 OFFSET 0
選択クエリからの利用
Microsoft Accessではリンクテーブルをソースとした選択クエリを作成することができます。以下の例では、BigQueryのProductsテーブルのリンクテーブルを参照したクエリを作成した例です。
本クエリでは、抽出条件に"1"と絞り込み条件が付与されています。この選択クエリを実行すると実際にはBigQueryには以下のようなフィルタが追加されたSQLが発行されます。
SELECT `id` FROM `cdatajp-support`.`test1`.`Products` WHERE `id` = '1'
なお、選択クエリでAccess固有の関数を含めたり、複雑なクエリになると、BigQueryからは全件取得してから、ローカルマシンのメモリにオフロードされて処理されることになるのでご注意ください。
データ型が長いテキスト型となってしまう
デフォルトでは、BigQueryのString型は、Microsoft Accessのリンクテーブルのデータ型である「長いテキスト型」にマップされます。
長いテキスト型の場合、JOINする時の項目に利用しようとするとMicrosoft Accessの仕様でエラーとなります。本問題を回避する方法として、ODBC DSN構成の接続プロパティに、その他 > Maximum Column Sizeプロパティがあります。デフォルトでは「16000」となっていますが、こちらに「255」以下の値セットすることで「短いテキスト型」にマップされるようになります。
ただし、本設定は、DSN内の全体設定となるので、全文字列項目が指定した文字数になるのでご注意ください。
BigQueryへのAPIリクエストの確認
ODBC Driverのログファイルを確認することで、Microsoft Accessのリンクテーブルや選択クエリからBigQueryのデータにアクセスした時に、CData ODBC Driverが実際にBigQueryに発行しているAPIリクエストを確認することができます。
ODBC DSN構成の接続プロパティに、Logging > Logfile, Verbosityプロパティがあります。Logfileにはファイル名を含むログファイルの出力パス、Verbosityには3をセットすることでログファイルを出力することが出来るようになります。
ただし、本設定は、パフォーマンスやエラーが発生している場合の調査の際に設定いただき、調査が終わりましたら、本設定をクリア、もしくは、Verbosityを1にセットしてログファイルの出力量を少なくした運用をお勧めします。
ログファイルについては、下記の記事もご参照ください。
https://www.cdata.com/jp/blog/2018-12-07-110000
テクニカルサポートについて
その他、Google BigQuery Driverでは様々な機能・設定がサポートされています。
もし気になる点、こんなことできるの？ こんな調整できないの？ などの質問・疑問があれば、以下の CData Software Japan テクニカルサポートまでお問い合わせください。
https://www.cdata.com/jp/support/submit.aspx
ちなみに、課金プロジェクトの機能は日本のお客様の要望で追加された機能だったりしますので、機能要望などもお待ちしています。
まとめ
本記事では、Microsoft AccessのリンクテーブルでGoogle BigQueryのデータを利用する方法をご紹介しました。CData Google BigQuery ODBC Driverは、30日間ご試用いただける無償評価版がございますので、Microsoft AccessでGoogle BigQueryのデータを利用したいと考えられてる方は是非お試しください。
www.cdata.com
関連コンテンツ
