Azure Data FactoryでODataクエリからデータをフィルタリングする方法

Azure Data Factory の OData ソース接続内で、Invantive Cloud に対するクエリを作成する必要があります。

例えば、私のデータベース名は Simplicate、エイリアスは spe、フィルタをかけたい列は created_at という日付型の列です。セットアップは Connect Azure Data Factory to Simplicate の通りです。

Azure Data Factory で使用する、フィルタ付きの OData サービス URL クエリはどのように作成すればよいですか?

Azure Data Factory

Simplicate のデータは、Invantive Cloud から Microsoft Azure Data Factory に取り込まれます。これは Simplicate だけでなく、Exact Online、Salesforce、ActiveCampaign など、他の多くのクラウドプラットフォームでも同様に機能します。詳細な手順は Retrieve a specific Exact Online OData4 data set using Azure Data Factory をご覧ください。

Power BI で OData をフィルタリングする方法をお探しの場合は、より実践的なガイドが Reduce data volume and improve performance of your Power BI report by Filtering にあります。

パフォーマンスの最適化とダウンロード量の削減に取り組む前に、Invantive Cloud の構造を理解しておいてください。構造については Invantive Cloud Structure で学ぶことができます。わかりやすい図も掲載されています!

Azure Data Factory を使うと、Invantive Cloud の OData コネクタや他の OData プロデューサー(OData ソース)からのデータを、データレイクや SQL Server インスタンス上のデータベースへ転送・ルーティングできます。

Invantive Cloud は OData プロデューサー(OData ソース)であり、Simplicate の内部 API プロトコルを OData に橋渡しし、メタデータや、キャッシュ、リトライ、ロギングといった実運用向けの機能を追加します。

なお、OData プロトコルには若干の違いがある2つのバージョンが存在することに注意してください。古いプロデューサー(ソース)は OData バージョン3で動作しますが、現在の作業には OData 4 が強く推奨されます。OData 4 はより多くの演算子を備え、スループットも優れており、さらに高度な進化を続けながら、より長い残存寿命を持っています。

OData v3 の仕様は OData Version 3.0 · OData - the Best Way to REST で公開されています。承認済みの OData v4 仕様は Documentation · OData - the Best Way to REST にあり、今後の変更点は GitHub - OData/odataorg.github.io: Staging site and collaboration repository for http://www.odata.org · GitHub で確認できます。

OData v4 は Azure Data Factory、Power Query、Power BI、その他 Microsoft 製品の最近の既知のバージョンすべてで利用がサポートされています。

フィルタリングの目的においては、OData クエリは以下のように処理されます。

  • まず Invantive SQL に書き換えられ、各 OData フィルタが SQL の where 句上のフィルタに変換されます。
  • 次に Invantive SQL が既存のキャッシュに対して実行され、必要な場合は Invantive ドライバー(いわば ODBC のようなもの)によって Simplicate の Web サービスソースへの API 呼び出しが行われます。

なお、Invantive Cloud は OData コネクタにおいて常に現行バージョンである OData 4 を使用します。実際のデータソースは OData 2 や OData 3、あるいは XML、SOAP、単なる JSON といった API を使用している場合もあれば、PostgreSQL、IBM DB2、SQL Server のようなネイティブなリレーショナルデータベース形式である場合もあります。

サーバーサイドフィルタリング

Invantive SQL による Invantive ドライバー呼び出し(ODBC ドライバーの使用と比較してみてください)への変換は、実際のプロトコルを抽象化します。

Azure Data Factory を使ったリクエストの中間言語として Invantive SQL へ変換するこの仕組みは、(キャッシュの再読み込みが必要な場合に)リクエストを実際のデータソースへ転送する前段階として、フィルタをデータソースへ転送することもサポートしています。例えば、等価演算子を使った OData フィルタは、Invantive SQL の等価演算子に書き換えられます。この等価条件は、多くの API においてデータソースへそのまま転送されます。すべての API が豊富なフィルタリングをサポートしているわけではありませんが、Simplicate はサポートしています。細かいフィルタリングが可能な他の人気データソースとしては、AFAS Profit、Twinfield、Chargebee、Visma.net Financials、Visma eAccounting、Exact Online が挙げられます。このようなフィルタの転送は「サーバーサイドフィルタリング」と呼ばれます。

このフィルタ条件の転送(「サーバーサイドフィルタリング」と呼ばれる仕組み)は、常に適用されるわけではありません。

例えば、Invantive が手動で追加したフィールドに対するフィルタは、ほとんどの場合転送されません。ただし、Exact Online の DivisionLabel のようにいくつか例外があります。これは Exact Online API 上の実際のカラムではありませんが、これに対するフィルタは特定のディビジョンへのクエリに変換されます。

また、すべての API がどのフィールドに対してもフィルタリングできるわけではありません。例えば、Exact Online にはカラムごとの例外があります。Teamleader にもかなりの制限があります。

あるフィールドへのフィルタが最終的にクラウドプラットフォームへ転送可能かどうかは、ドキュメントで確認できます。SystemTableColumns@DataDictionary の CAN_FILTER_SERVER_SIDE カラムをご覧ください。

最後に、and、or の複雑な組み合わせや、場合によっては計算を伴うようなより高度なフィルタの多くは、サーバーサイドフィルタリングができないことに注意してください。Invantive SQL には、式を簡素化し、ピープホール最適化を行い、クエリを書き換える高度なオプティマイザが備わっていますが、それでもまだ最適化できない構文は数多く考えられます。定期的なモニタリングを通じて、多くのユーザーに影響する追加の最適化の可能性を特定し、時間をかけてこれらも最適化していくよう努めています。

OData フィルタ

残るステップは、OData の $filter を構築することです。SQL で以下のような効果を実現したいとします。

select *
from   table
where  created_at >= VALUE

まず created_at のデータ型を確認する必要があります。

Simplicate API には典型的な癖があることに気づくでしょう。日付のデータ型は実際には日付型ではなく、テキストデータ型です。良いニュースとしては、日付のテキスト表現は日付データ型としての日付と辞書順で同一の順序になる、ということです。例えば、2020年1月1日はテキスト表現としても日付としても、2020年12月31日より前に来ます。Simplicate の日付形式に関する詳細情報は Limit Data load from Simplicate on Invantive Cloud to Azure Data Factory で確認できます。

Power BI でフィルタを OData に変換する

SQL 文は OData に変換する必要があります。Power BI と Power Query の OData 実装はよくできているため、最も簡単な方法は、Power BI で OData データソースを使用し、OData サービスに送信される実際の OData クエリを確認することです。

Simplicate(または他のサポートされているデータソース)に OData フィルタを設定する手順は以下の通りです。

この監視は、Invantive Cloud の Invantive Bridge Online モニターを使うのが最も簡単です。以下の手順を実行してください。

/invantive-simplicate-1/odata4/Simplicate.Hours.Hours@spe?$filter=projectservice_name%20eq%20%27Consultancy%20op%20locatie%27&$top=1000

使用する OData フィルタは $filter から始まるテキストです。このフィルタは URL で使用するためにエスケープされているため、テキストをアンエスケープする必要があります。手動で行うことも、https://www.url-encode-decode.com/ のようなオンラインサービスを利用することもできます。

2021年7月以降、モニターは https://forums.invantive.com/t/more-insight-with-new-bridge-online-monitoring/2463 で示されているように、生成された SQL と並べて OData フィルタも表示するようになりました。

OData フィルタを手動で構築する

OData フィルタは手動で構築することもできます。OData 仕様はオンラインで公開されています。フィルタは、OData クエリに関するセクション 11.2.6 に記載されています。

基本としては、URL 内の $filter の後にフィルタを指定します。フィルタは and や or で組み合わせることができ、左辺(例えばカラム名)、eq(「等しい」を表す)のような演算子、右辺(例えば定数値)からなる条件を使用します。フィルタグループは、グループ化演算子 ( と ) を使って構成できます。

Invantive Bridge の OData 実装は多くの OData フィルタ演算子をサポートしていますが、すべてではありません。例えば hassubset は利用できません。サポートされていないフィルタを含める必要がある場合は、簡単なユースケースとともに Invantive サポートまでご報告ください。ユースケースの複雑さと実現可能性に応じて、その特定の OData フィルタを Invantive Bridge Online に追加することを検討します。

回避策として、もちろん Azure Data Factory 自体の中でフィルタを使用し、必要に応じてデータを絞り込むことも常に可能です。

クエリツールを使って OData フィルタを構築する

最近の Invantive SQL ベースの製品はすべて、Bridge Online サービス上の OData データソース用ドライバーを含んでいます。これにより、Invantive SQL と OData ブリッジを使用して Invantive Cloud からデータを取得できます。

以下の手順を実行してください。

  • Invantive Query Tool を起動します。
  • データベースグループ「Various」を開きます。
  • データベース「Invantive Bridge Online」を選択します。
  • 以下のようにデータベースパラメータを入力します。
  • 「Connect」をクリックします。
  • 以下のようにデータベースクエリを作成します。
select * 
from   [Hours@Spe] 
where  created_at >= '2020-11-01'
  • なお、テーブル名は spe を含む Hours@spe です。これは spe が Bridge Online サービス上で解釈されるためです。Bridge Online に bol のようなローカルエイリアスがある場合、完全な名前は [Hours@spe]@bol となります。
  • Invantive SQL はこの呼び出しを、フィルタを含む OData クエリに変換します。
  • 実際の OData クエリは Bridge Online モニタリングで確認できます。
  • さらに、実際の OData クエリはローカルの Invantive SQL インスタンスから以下のように取得できます。
select url
from   sessionios@datadictionary

結果は以下のようになります。

OData フィルタの例

参考として、クエリで使用できる OData フィルタの例を以下に挙げます。

  • ExactOnlineREST.Cashflow.PaymentsBulk@eol?$filter=DivisionCompanyName ne 'ACME Holding' and InvoiceDate ge 2019-01-01T00:00:00+01:00: ホールディングを除く、すべてのパーティションにおける2019年1月1日以降の支払いをすべて取得します。
  • ExactOnlineREST.Financial.GLAccountClassificationMappings@eol?$filter=GLSchemeDescription eq 'G/L Account Scheme': レポーティングスキーマ「G/L Account Scheme」に対する、すべての総勘定元帳勘定科目分類を取得します。
  • ExactOnlineREST.Financial.ExchangeRates@eol?$filter=Created gt 2020-01-01T00:00:00+01:00: 2020年1月1日以降に作成されたすべての為替レートを取得します。
  • Teamleader.V1Flat.ProjectMilestones@tlr?$filter=responsible_user_id eq 1 or responsible_user_id eq 2: ID が 1 または 2 のユーザーが責任者となっている、Teamleader プロジェクトのすべてのマイルストーンを取得します。
  • ExactOnlineREST.FinancialTransaction.TransactionLinesBulk@eol?$filter=FinancialYear ge 2018 and Division eq 123456: Exact Online の会社 123456 における、2018会計年度のすべての財務取引を取得します。

OData ソース上で Azure Data Factory を使って OData $filter を適用する

OData サービス用に構築したフィルタを使用して、クエリは次の図のように調整できます(Exact Online France 上の Data Factory を使用した例)。