こんにちは、CData Software Japanテクニカルサポートの大川です。
本記事では、CData SyncでMicrosoft 365の予定表データをPostgreSQLに同期する方法についてご紹介します。
はじめに
最近多いお問い合わせの１つに、Microsoft 365の予定表データを分析したいがどのような方法があるか、といったものがあります。
関連する既存の記事としては以下があります。
前者の記事のシナリオの場合、ユーザに「ApplicationImpersonation」という比較的強い権限を付与する必要があるため、IT所管部署の方の許可が得られにくいことがあると認識しています。また、後者の記事のシナリオの場合、作成したアプリケーションのアクセス許可（ClientCredentials）をユーザに利用させることが難しいことがあると認識しています。また、セキュリティの観点からユーザが参照可能な予定表データを所属等の条件で絞り込みたいというお問い合わせもありましたが、絞り込みのために強い権限が必要である場合も多く、実現が難しいという声もお聞きします。
上記のような場合、次のような実現方法があるかと思います。
- IT所管部署の方などが弊社Sync製品を使ってMicrosoft 365の予定表データを定期的にPostgreSQLやMySQL、SQL ServerなどのDB、BigQueryやSnowflakeなどのDWHへ同期する
- 予定表データを分析したいユーザがセキュリティ上問題ない形で閲覧可能なビュー等を用意する
- ユーザは用意されたビュー等を利用し、Power BI等を使ってデータの分析作業を行う
本記事では上記を実現するための一例として「弊社Sync製品を使ってMicrosoft 365の予定表データを定期的にPostgreSQLへ同期する」というシナリオの具体的な実現方法について記載します。
事前準備
本記事ではWindowsマシン上で環境を構築していきますが、弊社Sync自体はMacやLinux上でも稼働します。
必要なソフトウェアのダウンロード
以下のソフトウェアをダウンロードしておきます。
- 弊社CData Sync 自動データレプリケーション | CData Software Japan 右上の「無償トライアルへ」からWindows版をダウンロード
- PostgreSQL ダウンロード | 日本PostgreSQLユーザ会 「Windows」のリンクから「Download the installer」のリンクで最新版（執筆時点で14.2）の「Windows x86-64」をダウンロード
- A5:SQL Mk-2 A5:SQL Mk-2 - フリーのSQLクライアント/ER図作成ソフト (松原正和) 「Vectorからダウンロード」のリンクからx64 editionをダウンロード
ダウンロードしたソフトウェアのインストール
- 弊社CData Syncについてはダウンロードしたexeファイルを管理者権限で実行するだけです。インストール中に聞かれる設定項目はすべて規定値で問題ありません。
- PostgreSQLについてもダウンロードしたexeファイルを管理者権限で実行するだけとなります。インストール中にpostgresユーザのパスワードを聞かれますので、任意のパスワードを決めて入力してください。そのほかの設定項目は規定値で問題ありませんが、インストールの最後に聞かれる「Launch Stack Builder at exit?」の画面では（今回は不要なため）チェックを外して「Finish」ボタンで終了してください。
- A5:SQL Mk-2についてはzipファイルとなっていますので、任意のフォルダ（とりあえずでよければデスクトップでも可）に展開するのみとなります。
アプリケーションの登録と権限付与
弊社Syncが予定表の取得で接続するための接続情報を作成します。下記の記事の「1. Azure AD にアプリケーション登録」から「5. 管理者の同意を実施」までを実施します。
ただし、今回のシナリオでは以下の箇所について読み替えてください。
- 「1. Azure AD にアプリケーション登録」において「リダイレクトURI」の登録は不要です。
- 「3. ClientSecretの取得」について、「シークレット ID」と「値」がありますが、「値」を控えておく必要があることに注意してください。また、ページを移動すると表示できなくなりClientSecretを作成しなおす必要がありますので、必ず作成直後に「値」を控えておくようにしてください。
- 「4. API アクセス許可の付与」の部分では、今回のシナリオでは「User.Read.All」（全ユーザのプロファイルの読み取り）と「Calendars.Read」（全ユーザのカレンダ情報の読み取り）を追加してください。
CData Syncの設定
インストールしたSyncの設定をしていきます。スタートメニューから「CData Sync」にある「Start CData Sync Server」をクリックすると、Syncが起動し始めます。約１分ほどで起動が完了し、自動的にブラウザが開きます。
初回はadminユーザのパスワードの設定を求められますので、任意のパスワードを設定してください。その後、adminユーザで設定したパスワードでログインしてください。
ログインするとライセンスが無い旨の表示となりますので、「30日の評価版をアクティベート」ボタンから評価版のライセンスを有効化してください。
これでSyncのフル機能を30日間ご試用いただけるようになりました。
データソースの接続設定
それではまずはデータソース（同期元）の接続設定を行います。初期状態では今回使用するMicrosoft 365用のコネクタが入っていませんので、以下の手順でインストールします。
「接続」タブ→「データソース」→「+Add More」をクリック
「コネクタをダウンロード」ダイアログで「Office」と入力すると出てくる「Office 365」をクリック
「ダウンロード＆インストール」をクリック
自動的にドライバがダウンロードされインストールされます。その後、自動的にSyncが再起動されます。
画面が「Server Too Busy」という表示になった場合はまだ再起動が完了していませんので、しばらくしてからリロードしていただくと管理コンソールのログイン画面に戻りますので、再度adminユーザでログインします。
再度「接続」タブ→「データソース」と選択すると「Office 365」のアイコンがありますのでクリックします。
接続情報を入力する画面になりますので、以下のとおり入力します。
| 設定項目 | 設定値 |
|---|---|
| 名前 | 任意の名前（今回は「Office365Connection」としました） |
| Auth Scheme | 「AzureServicePrincipal」を選択 |
| Azure Tenant | 対象のテナントドメイン（例：XXXXX.onmicrosoft.com、Azure Active Directory管理センターの「概要」→「基本情報」→「プライマリドメイン」の値） |
| OAuth Client Id | 「アプリケーションの登録と権限付与」の「2. ClientIDの取得」で確認したアプリケーション（クライアント）ID |
| OAuth Client Secret | 「アプリケーションの登録と権限付与」の「3. ClientSecretの取得」で確認したClient Secretの「値」 |
設定項目を入力したら、「次に接続 Office365」ボタンをクリックすると、接続が行われます。成功すると、以下のように「Success!」と表示されます。
接続に成功したら、「変更を保存」ボタンで接続設定を保存します。
同期先の接続設定
次に、同期先となるPostgreSQLの接続設定を行います。「接続」タブ→「同期先」と選択し「PostgreSQL」のアイコンをクリックします。
接続情報を入力する画面になりますので、以下のとおり入力します。
| 設定項目 | 設定値 |
|---|---|
| 名前 | 任意の名前（今回は「PostgreSQLConnection」としました） |
| Server | 今回はSyncと同じサーバにPostgreSQLをインストールしたので「localhost」 |
| Port | 今回はデフォルト設定でインストールしたので「5432」 |
| Database | 今回は簡単のためデフォルトのDBを使用するため「postgres」 |
| User | 今回は簡単のためデフォルトのユーザを使用するため「postgres」 |
| Password | PostgreSQLのインストール時に設定したpostgresユーザのパスワード |
設定項目を入力したら、「接続テスト」ボタンをクリックすると、接続テストが行われます。成功すると、以下のように「接続に成功しました。」と表示されます。
接続テストに成功したら、「変更を保存」ボタンで接続設定を保存します。
同期ジョブの作成
データソースおよび同期先の接続設定が完了したので、同期ジョブを作成します。
「ジョブ」タブから「ジョブを追加」をクリックします。
「新しいジョブを作成」ダイアログで以下のように項目を設定し、「作成」をクリックします。
| 設定項目 | 設定値 |
|---|---|
| ジョブ名 | 任意の名前（今回は「Office365ToPostgreSQL」としました） |
| ソース | 「Office365Connection」を選択 |
| 同期先 | 「PostgreSQLConnection」を選択 |
| レプリケーションの種類 | 「スタンダード」を選択 |
Usersテーブルの同期設定
ジョブを作成した直後は以下のような画面となり同期するテーブルが設定されていない状態です。まずは基本的なテーブルである「Users」テーブルの同期を設定してみます。なお、テーブル一覧については以下のドキュメントを参照してください。
「テーブルを追加」ボタンをクリックします。
「テーブルを追加」ダイアログにOffice365ドライバで利用できるテーブルおよびビューの一覧が表示されますので、「Users」にチェックを入れ「選択したテーブルを追加」をクリックします。
すると、以下のようにジョブに「Users」テーブルが追加されます。
Usersテーブルの同期の手動実行
この後、テーブル名をクリックすることによってこのテーブルの同期処理の詳細な設定を行うこともできるのですが、ここでは簡単のためデフォルトの設定のままでひとまずUsersテーブルを同期してみます。
「Users」テーブルにチェックを入れ、「実行」ボタンをクリックします。
するとテーブルの同期処理が実行され、完了すると当該テーブルの「最後の更新」および「ステータス」の列が更新されます。今回はテスト用のMicrosoft 365環境に２ユーザ用意していましたので、上記のように「Records affected: 2」という結果が表示されました。
同期先のUsersテーブルの確認
今回は同期先のPostgreSQLの接続設定でpostgresユーザを設定していますので、publicスキーマのUsersテーブルに同期されます。同期先テーブルが存在しなかった場合はSyncによって自動的に作成されます。
同期されたデータをA5:SQL Mk-2で確認してみます。
ダウンロード・展開しておいたA5M2.exeをダブルクリックして起動します。初回起動時に「WindowsによってPCが保護されました」という表示が出る場合がありますので、「詳細表示」をクリック後に「実行」をクリックすることで次回以降は表示されなくなります。
また、初回起動時は設定情報の保存先を選択する必要があります。どちらでも問題ありませんが、今回は「レジストリ」をクリックします。
すると接続設定を促すダイアログが表示されます。
「追加」ボタンをクリックします。
「PostgreSQL（直接接続）」をクリックします。
SyncのPostgreSQLの接続設定で設定した値と同じ値を設定し、「テスト接続」をクリックします。問題なければ「接続に成功しました」と出ますので、「OK」で保存します。保存名を聞かれますが、今回はデフォルトのまま「localhost/postgres」としました。
保存したら、開いている「データベースの追加と削除」ダイアログを閉じます。するとデータベースのツリーに先ほど登録したpostgresがありますので、ダブルクリックします。
PostgreSQLのログインダイアログが表示されますので、postgresユーザのパスワードを入力して「接続」をクリックします。
すると接続されますので、「public」→「テーブル」→「Users」と開いていき、「Users」をダブルクリックするとテーブルの内容が表示されます。
今回は２ユーザの情報が同期されているのが確認できました。
予定表データ同期の絞り込み条件の検討
この後予定表データの同期を行いますが、すべてのユーザの予定表データを同期しようとすると、特にユーザの多い組織の場合は処理にどれぐらいの時間や通信量が発生するかが見込めないかと思います。また、以下のマイクロソフトのAPIレート制限に抵触する可能性もあります。
そのため、最初は条件を絞り込んだうえで同期を行い、だんだんに条件を緩めていって試していただくのがよいと思います。条件を検討するにあたっては、すでに同期したUsersテーブルの内容が参考となります。
今回のシナリオでは、「department」が「総務部」のユーザについて予定表データを同期してみることとします。
ジョブに予定表ビュー（CalendarView）を追加
現在「Users」テーブルを同期するジョブに、「CalendarView」ビューを追加します。
「テーブルを追加」ボタンをクリックします。
「CalendarView」にチェックを入れて「選択したテーブルを追加」をクリックします。
追加された「CalendarView」をクリックします。
タスク設定ダイアログが開きますので、「カスタムクエリを記述」にチェックを入れ、クエリ内容に以下を設定して「OK」をクリックします。
REPLICATE [CalendarView] SELECT * FROM CalendarView WHERE UserId IN (SELECT Id from Users WHERE department = '総務部')
REPLICATEコマンドについては以下のヘルプを参照してください。
今回は、CalendarViewのUserIdの絞り込み条件としてUsersテーブルのdepartmentカラムが「総務部」のユーザIdを使用しています。
各テーブルやビューにどんなカラムがあるかは以下のヘルプで確認できます。
これでタスク設定ができましたので、Usersテーブルと同じようにCalendarViewのデータを同期してみます。
「CalendarView」テーブルにチェックを入れ、「実行」ボタンをクリックします。
実行が完了すると、当該テーブルの「最後の更新」および「ステータス」の列が更新されます。
同期先のCalendarViewテーブルの確認
Usersテーブルと同様にCalendarViewテーブルの同期されたデータをA5:SQL Mk-2で確認してみます。
今回テーブルが追加されたので、A5:SQL Mk-2上で「テーブル」を右クリックして「すべてのデータベース情報の再読み込み」をクリックします。そして再度「public」→「テーブル」と開いていくと、「CalendarView」テーブルができているのが分かります。
CalendarViewについての補足
CalendarViewは、期間を指定しない場合はデフォルトで直近の30日分のデータが対象となるビューです。
今回の例でもREPLICATEコマンドのSELECT文で期間を指定していないので、直近の30日分が同期されています。
必要に応じて、カスタムクエリのWHERE句で上記ヘルプを参考にStart_DateTimeおよびEnd_DateTimeに対して期間を設定することで任意の期間のデータを同期することができます。
さいごに
今回はMicrosoft 365の予定表データをPostgreSQLに同期する手順をご紹介しました。いったんDBにデータが入ってしまえば、あとは必要に応じてユーザに公開したいデータをビューとして定義するなど、様々なデータ利活用の手法が使えるようになると思います。
もちろん、今回ご紹介したSyncでデータ同期を定期的に自動実行することも可能です。製品ヘルプや、弊社blog記事もご参考にしていただき、ぜひ皆さんのデータ利活用にご活用いただければと思います。
