こんにちは!ドライバー周りのヘルプドキュメントを担当している古川です。
Microsoft Query を使うことで、Excel からデータベースやファイル、外部データソースにアクセスしてデータをインポート、更新できます。CData ODBC Driver for Bitbucket はMicrosoft Query の連携先を拡張して、270種類以上のCRM、ERP、MA、会計ツールなどのデータソースへのアクセスを実現します。
この記事では、Microsoft Query を使ってBitbucket データをスプレッドシートにインポートする方法をご紹介します。
CData ODBC ドライバとは?
CData ODBC ドライバは、以下のような特徴を持った製品です。
- Bitbucket をはじめとする、CRM、MA、会計ツールなど多様なカテゴリの270種類以上のSaaS / オンプレデータソースに対応
- Excel やMicrosoft Query をはじめとする多様な帳票・BI ツールにBitbucket データを連携
- ノーコードでの手軽な接続設定
- 標準SQL での柔軟なデータ読み込み・書き込み
CData ODBC ドライバでは、1.データソースとしてBitbucket の接続を設定、2.Microsoft Query でODBC Driver との接続を設定、という2つのステップだけでデータソースに接続できます。以下に具体的な設定手順を説明します。
Bitbucket への接続を設定
まずは、本記事右側のサイドバーからBitbucket ODBC Driver の無償トライアルをダウンロード・インストールしてください。30日間無償で、製品版の全機能が使用できます。
ODBC ドライバーのインストール完了時にODBC DSN 設定画面が立ち上がります。または、Microsoft ODBC データソースアドミニストレーターを使ってDSN を作成および設定できます。
ほとんどのクエリでは、ワークスペースを設定する必要があります。唯一の例外は、Workspacesテーブルです。このテーブルはこのプロパティの設定を必要とせず、クエリを実行すると、Workspaceの設定に使用できるワークスペーススラッグのリストが提供されます。このテーブルにクエリを実行するには、スキーマを'Information'に設定し、SELECT * FROM Workspacesクエリを実行する必要があります。
Schemaを'Information'に設定すると、一般的な情報が表示されます。Bitbucketに接続するには、以下のパラメータを設定してください。
- Schema: ワークスペースのユーザー、リポジトリ、プロジェクトなどの一般的な情報を表示するには、これを'Information'に設定します。それ以外の場合は、クエリを実行するリポジトリまたはプロジェクトのスキーマに設定します。利用可能なスキーマの完全なセットを取得するには、sys_schemasテーブルにクエリを実行してください。
- Workspace: Workspacesテーブルにクエリを実行する場合を除き、必須です。Workspacesテーブルへのクエリにはこのプロパティは必要ありません。そのクエリはWorkspaceの設定に使用できるワークスペーススラッグのリストのみを返すためです。
Bitbucketでの認証
BitbucketはOAuth認証のみをサポートしています。すべてのOAuthフローからこの認証を有効にするには、カスタムOAuthアプリケーションを作成し、AuthSchemeをOAuthに設定する必要があります。
特定の認証ニーズ(デスクトップアプリケーション、Webアプリケーション、ヘッドレスマシン)に必要な接続プロパティについては、ヘルプドキュメントを必ず確認してください。
カスタムOAuthアプリケーションの作成
Bitbucketアカウントから、以下のステップを実行します。
- 設定(歯車アイコン)に移動し、ワークスペース設定を選択します。
- アプリと機能セクションで、OAuthコンシューマーを選択します。
- コンシューマーを追加をクリックします。
- カスタムアプリケーションの名前と説明を入力します。
- コールバックURLを設定します。
- デスクトップアプリケーションとヘッドレスマシンの場合、http://localhost:33333または任意のポート番号を使用します。ここで設定するURIがCallbackURLプロパティになります。
- Webアプリケーションの場合、信頼できるリダイレクトURLにコールバックURLを設定します。このURLは、ユーザーがアプリケーションにアクセスが許可されたことを確認するトークンを持って戻るWebの場所です。
- クライアント認証情報を使用して認証する予定の場合、これはプライベートコンシューマーですを選択する必要があります。ドライバーでは、AuthSchemeをclientに設定する必要があります。
- OAuthアプリケーションに与える権限を選択します。これにより、読み取りおよび書き込みできるデータが決まります。
- 新しいカスタムアプリケーションを保存するには、保存をクリックします。
- アプリケーションが保存された後、それを選択して設定を表示できます。アプリケーションのKeyとSecretが表示されます。これらを将来の使用のために記録してください。Keyを使用してOAuthClientIdを設定し、Secretを使用してOAuthClientSecretを設定します。
Microsoft ODBC データソースアドミニストレーターで必要なプロパティを設定する方法の詳細は、ヘルプドキュメントの「はじめに」を参照してください。
Excel で[データ]タブを開き[その他のデータソースから]->[Microsoft Query から]を選びます。
マシンにインストールされているMicrosoft Office のバージョンに合わせて、32 bit CData Bitbucket Source または64 bit CData Bitbucket Source x64 DSN を選びます。Query Wizard を使うオプションを選択し、クエリを作成/編集します。
Query Wizard で、スプレッドシートにインポートしたいテーブルのノードを展開します。インポートしたいカラムを選択し、矢印をクリックしてクエリに追加します。あるいはテーブル名を選択して、テーブルにあるすべてのカラムを追加します。
[Filter Data]ページではクライテリアを指定できます。例えば、日付の範囲を設定して結果を絞れます。
クエリでパラメータを使いたい場合は、Microsoft Query でクエリを編集するオプションを選択します。
クエリにパラメータを設定するには、SQL を直接修正する必要があります。その場合は、[Query Editor]の[SQL]ボタンをクリックします。フィルタクライテリアを事前に設定している場合は、クエリ内にWHERE 句が存在します。
パラメータを使うには、WHERE 句のフィールド値にワイルドカード文字として "?" を使います。例えばIssues をインポートしている場合は、"Id=?" を設定できます。
SQL ステートメントの編集が終わったら[SQL]ダイアログを閉じます。パラメータ値を入力するように促されますが、次のステップでセルを選択して値を入れることになるので、このダイアログではボックスは空のままにしておきます。
Microsoft Query を閉じます。[Import Data]ダイアログが表示されます。結果をインポートするセルを入力します。
[Import Data]ダイアログを閉じます。パラメータ値を入力するように促されます。パラメータボックスの隣にあるボタンをクリックしてセルを選択します。値が変更されたらスプレッドシートを自動的にリフレッシュするオプションを選択します。
データはこれでExcel にインポートされました。セルB1の値を変更すると、データは指定した検索クライテリアによってフィルタされます。
おわりに
このようにCData ODBC ドライバと併用することで、270を超えるSaaS、NoSQL データをMicrosoft Query からコーディングなしで扱うことができます。30日の無償評価版が利用できますので、ぜひ自社で使っているクラウドサービスやNoSQL と合わせて活用してみてください。
日本のユーザー向けにCData ODBC ドライバは、UI の日本語化、ドキュメントの日本語化、日本語でのテクニカルサポートを提供しています。