Wie filtert man Daten aus einer OData-Abfrage in Azure Data Factory?

Ich muss eine Abfrage innerhalb der OData-Quellverbindung in Azure Data Factory zu Invantive Cloud erstellen.

Zum Beispiel: Mein Datenbankname ist Simplicate, der Alias ist spe und die Spalte, nach der gefiltert werden soll, ist ein Datum namens created_at. Einrichtung wie unter Connect Azure Data Factory to Simplicate.

Wie kann ich eine OData-Service-URL-Abfrage mit Filter für die Verwendung in Azure Data Factory erstellen?

Azure Data Factory

Die Daten von Simplicate werden aus Invantive Cloud in Microsoft Azure Data Factory abgerufen. Dies funktioniert für Simplicate, aber auch für viele andere Cloud-Plattformen wie Exact Online, Salesforce oder ActiveCampaign, wie Schritt für Schritt beschrieben auf Retrieve a specific Exact Online OData4 data set using Azure Data Factory.

Suchst du nach dem Filtern von OData in Power BI: Eine praktischere Anleitung findest du unter Reduce data volume and improve performance of your Power BI report by Filtering.

Stelle sicher, dass du die Struktur von Invantive Cloud verstehst, bevor du mit der Optimierung der Performance und der Reduzierung des Downloads beginnst. Lerne die Struktur unter Invantive Cloud Structure kennen. Dort findest du auch ein schönes Bild dazu!

Azure Data Factory ermöglicht es dir, die Daten vom OData-Connector von Invantive Cloud und anderen OData-Anbietern (OData-Quellen) in deinen Data Lake oder eine Datenbank auf deiner SQL-Server-Instanz zu transportieren und zu routen.

Invantive Cloud ist ein OData-Anbieter (OData-Quelle): Sie überbrückt die internen API-Protokolle von Simplicate zu OData, fügt Metadaten sowie Produktionsfunktionen wie Caching, Wiederholungsversuche (Retries) und Logging hinzu.

Beachte, dass es zwei Versionen des OData-Protokolls mit leichten Unterschieden gibt. Ältere Anbieter (Quellen) arbeiten mit OData Version 3, aber OData 4 wird für aktuelle Arbeiten dringend empfohlen, da es mehr Operatoren bietet, einen besseren Durchsatz ermöglicht und eine längere verbleibende Lebensdauer hat, während es sich weiterhin zu noch fortschrittlicheren Funktionen entwickelt.

Die OData-v3-Spezifikation ist verfügbar unter OData Version 3.0 · OData - the Best Way to REST. Die genehmigte OData-v4-Spezifikation ist verfügbar unter Documentation · OData - the Best Way to REST, während kommende Änderungen unter GitHub - OData/odataorg.github.io: Staging site and collaboration repository for http://www.odata.org · GitHub zu finden sind.

OData v4 wird von Azure Data Factory, Power Query, Power BI und allen anderen bekannten aktuellen Versionen von Microsoft-Produkten unterstützt.

Zum Zweck des Filterns wird eine OData-Abfrage:

  • Zunächst in Invantive SQL umgeschrieben, wobei jeder OData-Filter in einen Filter auf der SQL-Where-Klausel übersetzt wird.
  • Dann wird Invantive SQL gegen bestehende Caches ausgeführt, und bei Bedarf werden API-Aufrufe auf dem Webservice-Quelle von Simplicate vom Invantive-Treiber (eine Art ODBC) durchgeführt.

Beachte, dass Invantive Cloud im OData-Connector immer die aktuelle Version 4 von OData verwendet. Die tatsächliche Datenquelle kann eine API mit OData 2 oder OData 3 oder sogar XML, SOAP oder einfach nur JSON verwenden oder sogar ein natives relationales Datenbankformat wie PostgreSQL, IBM DB2 oder SQL Server sein.

Serverseitiges Filtern

Die Übersetzung durch Invantive SQL in Aufrufe auf dem Invantive-Treiber (vergleichbar mit der Verwendung eines ODBC-Treibers) abstrahiert das eigentliche Protokoll.

Diese Übersetzung in Invantive SQL als Zwischensprache für deine Anfragen über Azure Data Factory, bevor die Anfrage (bei Bedarf zum Neuladen von Caches) an die eigentliche Datenquelle weitergeleitet wird, unterstützt auch die Weiterleitung des Filters an die Datenquelle. Zum Beispiel wird ein OData-Filter mit einem Gleichheitsoperator in Invantive SQL in einen Gleichheitsoperator umgeschrieben. Die Gleichheit wird bei vielen APIs an die Datenquelle weitergeleitet. Nicht alle APIs verfügen über umfangreiche Filterunterstützung, aber Simplicate schon. Weitere beliebte Datenquellen mit feiner Filterung sind AFAS Profit, Twinfield, Chargebee, Visma.net Financials, Visma eAccounting und Exact Online. Diese Weiterleitung von Filtern wird „serverseitiges Filtern“ genannt.

Dieses Konzept der Weiterleitung von Filterkriterien (genannt „serverseitiges Filtern“) gilt nicht immer.

Zum Beispiel werden Filter auf Felder, die manuell von Invantive hinzugefügt wurden, meistens nicht weitergeleitet. Es gibt einige Ausnahmen wie DivisionLabel bei Exact Online. Obwohl dies keine tatsächliche Spalte in der Exact-Online-API ist, werden Filter darauf in spezifische abzufragende Divisionen übersetzt.

Auch sind nicht alle APIs in der Lage, auf jedes Feld zu filtern. Exact Online hat zum Beispiel spaltenspezifische Ausnahmen. Aber auch Teamleader hat erhebliche Einschränkungen.

Ob Filter auf Felder letztendlich an die Cloud-Plattform weitergeleitet werden können, findest du in der Dokumentation. Schau dir die Spalte CAN_FILTER_SERVER_SIDE in SystemTableColumns@DataDictionary an.

Schließlich sei angemerkt, dass serverseitiges Filtern bei vielen fortgeschritteneren Filtern nicht möglich ist, wie etwa komplexen Kombinationen von and, or und eventuell sogar Berechnungen. Invantive SQL verfügt über einen umfangreichen Optimierer, der Ausdrücke vereinfacht, Peephole-Optimierungen durchführt und Abfragen umschreibt, aber dennoch gibt es viele denkbare Konstrukte, die noch nicht optimiert werden können. Im Rahmen der regelmäßigen Überwachung versuchen wir, weitere Optimierungsmöglichkeiten zu identifizieren, die viele Nutzer betreffen, und versuchen, diese im Laufe der Zeit ebenfalls zu optimieren.

OData-Filter

Der verbleibende Schritt besteht darin, den OData-$filter zu konstruieren. Angenommen, du möchtest diesen Effekt in SQL erzielen:

select *
from   table
where  created_at >= VALUE

musst du zunächst den Datentyp von created_at überprüfen.

Du wirst feststellen, dass die Simplicate-API eine typische Besonderheit aufweist: Der Datentyp von Datumsangaben ist eigentlich kein Datumstyp, sondern ein Textdatentyp. Die gute Nachricht ist, dass die Textdarstellung des Datums lexikografisch identisch zu den Daten als Datumsdatentyp sortiert wird, sodass zum Beispiel der 1. Januar 2020 vor dem 31. Dezember 2020 kommt, sowohl als Textdarstellung als auch als Datum. Weitere Informationen zum Datumsformat von Simplicate findest du unter Limit Data load from Simplicate on Invantive Cloud to Azure Data Factory.

Filter in Power BI in OData übersetzen

Die SQL-Anweisung muss in OData übersetzt werden. Die OData-Implementierung von Power BI und Power Query ist gut gelungen, daher ist der einfachste Weg, eine OData-Datenquelle in Power BI zu verwenden und dann die tatsächlichen OData-Abfragen zu beobachten, die an den OData-Dienst gesendet werden.

Die Schritte zum Einrichten eines OData-Filters auf Simplicate (oder jeder anderen unterstützten Datenquelle) sind:

Das Beobachten lässt sich am einfachsten mit dem Invantive Bridge Online Monitor von Invantive Cloud durchführen, führe also die folgenden Schritte aus:

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

Der zu verwendende OData-Filter ist der Text, der bei $filter beginnt. Der Filter wurde für die Verwendung in einer URL escaped, du musst den Text also unescapen. Du kannst dies manuell tun oder einen Online-Dienst wie https://www.url-encode-decode.com/ verwenden.

Seit Juli 2021 zeigt der Monitor auch den OData-Filter neben dem dafür generierten SQL an, wie unter https://forums.invantive.com/t/more-insight-with-new-bridge-online-monitoring/2463 gezeigt.

Einen OData-Filter manuell konstruieren

Ein OData-Filter kann auch manuell konstruiert werden. Die OData-Spezifikation ist online verfügbar. Die Filter sind Teil des Abschnitts 11.2.6 zu OData-Abfragen.

Die Grundlagen bestehen darin, deine Filter nach $filter in der URL anzugeben. Filter können mit and und or kombiniert werden, verwenden aber auch Kriterien, die aus einer linken Seite (zum Beispiel einem Spaltennamen), einem Operator wie eq für „gleich“ und einer rechten Seite (zum Beispiel einem konstanten Wert) bestehen. Filtergruppen können mit den Gruppierungsoperatoren ( und ) zusammengesetzt werden.

Die OData-Implementierung von Invantive Bridge unterstützt viele OData-Filteroperatoren, aber nicht alle. Zum Beispiel ist hassubset nicht verfügbar. Falls du einen Filter benötigst, der nicht unterstützt wird, melde dies bitte dem Invantive-Support mit einem kurzen Anwendungsfall. Abhängig von der Komplexität und Durchführbarkeit des Anwendungsfalls werden wir in Betracht ziehen, diesen spezifischen OData-Filter zu Invantive Bridge Online hinzuzufügen.

Als Workaround kannst du natürlich immer einen Filter innerhalb von Azure Data Factory selbst verwenden, um deine Daten nach deinen Bedürfnissen zu filtern.

Einen OData-Filter mit dem Query-Tool konstruieren

Alle aktuellen Invantive-SQL-basierten Produkte enthalten einen Treiber für OData-Datenquellen auf dem Bridge-Online-Dienst. Sie ermöglichen es dir, Daten von Invantive Cloud mit Invantive SQL und der OData-Bridge abzurufen.

Führe die folgenden Schritte aus:

  • Starte das Invantive Query Tool.
  • Öffne die Datenbankgruppe „Various“.
  • Wähle die Datenbank „Invantive Bridge Online“.
  • Gib die Datenbankparameter wie folgt ein:
  • Klicke auf „Connect“.
  • Konstruiere deine Datenbankabfrage wie folgt:
select * 
from   [Hours@Spe] 
where  created_at >= '2020-11-01'
  • Beachte, dass der Tabellenname Hours@spe einschließlich spe lautet, da das spe auf dem Bridge-Online-Dienst interpretiert wird. Wenn du einen lokalen Alias wie bol für Bridge Online hast, würde der vollständige Name [Hours@spe]@bol lauten.
  • Invantive SQL übersetzt den Aufruf in eine OData-Abfrage, einschließlich eines Filters.
  • Die tatsächliche OData-Abfrage findest du im Bridge Online Monitoring.
  • Und die tatsächliche OData-Abfrage kann von der lokalen Invantive-SQL-Instanz abgerufen werden mit:
select url
from   sessionios@datadictionary

mit folgendem Ergebnis:

Einige Beispiele für OData-Filter

Als Inspiration kann dir die folgende Liste mit Beispielen für OData-Filter zur Verwendung in Abfragen helfen:

  • ExactOnlineREST.Cashflow.PaymentsBulk@eol?$filter=DivisionCompanyName ne 'ACME Holding' and InvoiceDate ge 2019-01-01T00:00:00+01:00: Ruft alle Zahlungen nach dem 1. Januar 2019 über alle Partitionen hinweg außer der Holding ab.
  • ExactOnlineREST.Financial.GLAccountClassificationMappings@eol?$filter=GLSchemeDescription eq 'G/L Account Scheme': Ruft alle Sachkontenklassifizierungen für das Berichtsschema „G/L Account Scheme“ ab.
  • ExactOnlineREST.Financial.ExchangeRates@eol?$filter=Created gt 2020-01-01T00:00:00+01:00: Ruft alle Wechselkurse ab, die nach dem 1. Januar 2020 erstellt wurden.
  • Teamleader.V1Flat.ProjectMilestones@tlr?$filter=responsible_user_id eq 1 or responsible_user_id eq 2: Ruft alle Meilensteine auf Teamleader-Projekten ab, für die die Benutzer mit der ID 1 oder 2 verantwortlich sind.
  • ExactOnlineREST.FinancialTransaction.TransactionLinesBulk@eol?$filter=FinancialYear ge 2018 and Division eq 123456: Ruft alle Finanztransaktionen für das Geschäftsjahr 2018 des Exact-Online-Unternehmens 123456 ab.

OData-$filter mit Azure Data Factory auf eine OData-Quelle anwenden

Mit dem für den OData-Dienst konstruierten Filter kann die Abfrage wie im folgenden Bild angepasst werden (Beispiel unter Verwendung von Data Factory bei Exact Online Frankreich):