Hvordan filtrere data fra OData-spørring i Azure Data Factory?

Jeg trenger å opprette en spørring inne i OData-kildetilkoblingen i Azure Data Factory mot Invantive Cloud.

For eksempel: Databasenavnet mitt er Simplicate, alias er spe, og kolonnen jeg skal filtrere på er en dato som heter created_at. Oppsett som beskrevet på Connect Azure Data Factory to Simplicate.

Hvordan kan jeg lage en OData-tjeneste-URL-spørring med filter for bruk i Azure Data Factory?

Azure Data Factory

Dataene fra Simplicate hentes fra Invantive Cloud inn i Microsoft Azure Data Factory. Dette fungerer for Simplicate, men også for mange andre skyplattformer som Exact Online, Salesforce eller ActiveCampaign, som beskrevet steg for steg på Retrieve a specific Exact Online OData4 data set using Azure Data Factory.

Ser du etter filtrering av OData i Power BI: en mer praktisk veiledning er tilgjengelig på Reduce data volume and improve performance of your Power BI report by Filtering.

Sørg for at du forstår strukturen til Invantive Cloud før du begynner å optimalisere ytelsen og redusere nedlastingen. Lær strukturen på Invantive Cloud Structure. Den inneholder også et fint bilde!

Azure Data Factory lar deg transportere og rute dataene fra OData-kontakten (OData connector) til Invantive Cloud og andre OData-produsenter (OData-kilder) til datasjøen din eller en database på din SQL Server-instans.

Invantive Cloud er en OData-produsent (OData-kilde): den bygger bro mellom Simplicates interne API-protokoller og OData, legger til metadata og produksjonsfunksjoner som caching, nye forsøk (retries) og logging.

Merk at det finnes to versjoner av OData-protokollen med små forskjeller. Eldre produsenter (kilder) bruker OData versjon 3, men OData 4 anbefales på det sterkeste for nåværende arbeid siden den har flere operatorer, gir bedre gjennomstrømning og har en lengre gjenværende levetid samtidig som den fortsetter å utvikle seg til å bli enda mer avansert.

OData v3-spesifikasjonen er tilgjengelig på OData Version 3.0 · OData - the Best Way to REST. Den godkjente OData v4-spesifikasjonen er tilgjengelig på Documentation · OData - the Best Way to REST, mens kommende endringer finnes på GitHub - OData/odataorg.github.io: Staging site and collaboration repository for http://www.odata.org · GitHub.

OData v4 støttes for bruk av Azure Data Factory, Power Query, Power BI og alle andre kjente nyere versjoner av Microsoft-produkter.

For filtreringsformål blir en OData-spørring:

  • Først omskrevet til Invantive SQL, hvor hvert OData-filter oversettes til et filter i SQL where-setningen.
  • Deretter kjøres Invantive SQL mot eksisterende cacher, og når nødvendig utføres API-kall mot webtjenestekilden til Simplicate av Invantive-driveren (en slags ODBC).

Merk at Invantive Cloud alltid bruker gjeldende versjon 4 av OData i OData-kontakten. Den faktiske datakilden kan bruke et API med OData 2 eller OData 3, eller til og med XML, SOAP eller bare JSON, eller den kan være et opprinnelig relasjonsdatabaseformat som PostgreSQL, IBM DB2 eller SQL Server.

Filtrering på serversiden

Oversettelsen utført av Invantive SQL til kall på Invantive-driveren (sammenlign det med bruk av en ODBC-driver) abstraherer bort den faktiske protokollen.

Denne oversettelsen til Invantive SQL som et mellomliggende språk for forespørslene dine via Azure Data Factory, før forespørselen videresendes (når nødvendig for å laste inn cacher på nytt) til den faktiske datakilden, støtter også videresending av filteret til datakilden. For eksempel blir et OData-filter med en likhetsoperator omskrevet til en likhetsoperator i Invantive SQL. Likheten videresendes til datakilden med mange API-er. Ikke alle API-er har omfattende filtreringsstøtte, men det har Simplicate. Andre populære datakilder med fin filtrering er AFAS Profit, Twinfield, Chargebee, Visma.net Financials, Visma eAccounting og Exact Online. Denne videresendingen av filtre kalles «filtrering på serversiden» (server-side filtering).

Dette konseptet med å videresende filterkriterier (kalt «filtrering på serversiden») gjelder ikke alltid.

For eksempel videresendes filtre på felt som er manuelt lagt til av Invantive som oftest ikke. Det finnes noen unntak, som DivisionLabel på Exact Online. Selv om det ikke er en faktisk kolonne i Exact Online-API-et, oversettes filtre på den til spesifikke avdelinger (divisions) som skal spørres.

Heller ikke alle API-er er i stand til å filtrere på et hvilket som helst felt. For eksempel har Exact Online kolonnespesifikke unntak. Men også Teamleader har betydelige begrensninger.

Om filtre på felt til syvende og sist kan videresendes til skyplattformen, finner du i dokumentasjonen. Se på kolonnen CAN_FILTER_SERVER_SIDE i SystemTableColumns@DataDictionary.

Til slutt, merk at filtrering på serversiden ikke er mulig med mange mer avanserte filtre, som komplekse kombinasjoner av and, or og kanskje til og med beregninger. Invantive SQL har en omfattende optimerer som forenkler uttrykk, utfører «peephole»-optimaliseringer og omskriver spørringer, men det finnes fortsatt mange tenkelige konstruksjoner som ennå ikke kan optimaliseres. Under regelmessig overvåking prøver vi å identifisere ytterligere optimaliseringsmuligheter som påvirker mange brukere, og forsøker å optimalisere disse over tid også.

OData-filter

Det gjenværende steget er å konstruere OData $filter. Anta at du ønsker å oppnå denne effekten i SQL:

select *
from   table
where  created_at >= VALUE

må du først sjekke datatypen til created_at.

Du vil legge merke til at Simplicate-API-et har en typisk vri: datatypen til datoer er egentlig ikke datoer, men en tekst-datatype. Den gode nyheten er at datoens tekstrepresentasjon sorteres leksikografisk identisk med datoene som en dato-datatype, så for eksempel kommer 1. januar 2020 før 31. desember 2020, både som tekstrepresentasjon og som dato. Du finner mer informasjon om datoformatet til Simplicate på Limit Data load from Simplicate on Invantive Cloud to Azure Data Factory.

Oversett filter til OData i Power BI

SQL-setningen må oversettes til OData. OData-implementasjonen i Power BI og Power Query er godt gjennomført, så den enkleste måten å gjøre dette på er å bruke en OData-datakilde i Power BI og deretter observere de faktiske OData-spørringene som sendes til OData-tjenesten.

Trinnene for å sette opp et OData-filter på Simplicate (eller en annen støttet datakilde) er:

Observasjonen gjøres enklest ved hjelp av Invantive Bridge Online-overvåkingen (monitor) i Invantive Cloud, så utfør følgende trinn:

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

OData-filteret som skal brukes, er teksten som starter ved $filter. Filteret vil ha blitt escapet for bruk i en URL, så du må «unescape» teksten. Du kan gjøre dette manuelt eller bruke en nettbasert tjeneste som https://www.url-encode-decode.com/.

Siden juli 2021 viser overvåkingen også OData-filteret ved siden av SQL-en som er generert for det, som vist på https://forums.invantive.com/t/more-insight-with-new-bridge-online-monitoring/2463.

Konstruer et OData-filter manuelt

Et OData-filter kan også konstrueres manuelt. OData-spesifikasjonen er tilgjengelig på nett. Filtrene er en del av seksjon 11.2.6 om OData-spørringer.

Det grunnleggende er å angi filtrene dine etter $filter i URL-en. Filtre kan kombineres med and og or, men du kan også bruke kriterier bestående av en venstreside (for eksempel et kolonnenavn), en operator som eq for «lik» og en høyreside (for eksempel en konstant verdi). Filtergrupper kan settes sammen ved hjelp av grupperingsoperatorene ( og ).

OData-implementasjonen i Invantive Bridge støtter mange OData-filteroperatorer, men ikke alle. For eksempel er hassubset ikke tilgjengelig. Hvis du trenger å inkludere et filter som ikke støttes, kan du rapportere dette til Invantive support med et kort bruksscenario. Avhengig av kompleksiteten og levedyktigheten til bruksscenariet vil vi vurdere å legge til det spesifikke OData-filteret i Invantive Bridge Online.

Som en løsning kan du selvfølgelig alltid bruke et filter i selve Azure Data Factory for å filtrere dataene dine etter behov.

Konstruer et OData-filter med spørreverktøy (Query Tool)

Alle nyere Invantive SQL-baserte produkter inkluderer en driver for OData-datakilder på Bridge Online-tjenesten. De lar deg hente data fra Invantive Cloud ved hjelp av Invantive SQL og OData-broen (bridge).

Utfør følgende trinn:

select * 
from   [Hours@Spe] 
where  created_at >= '2020-11-01'
  • Merk at tabellnavnet er Hours@spe, inkludert spe, siden spe tolkes på Bridge Online-tjenesten. Når du har et lokalt alias som bol for Bridge Online, vil det fulle navnet være [Hours@spe]@bol.
  • Invantive SQL oversetter kallet til en OData-spørring, inkludert et filter.
  • Den faktiske OData-spørringen finner du i Bridge Online-overvåkingen.
  • Og den faktiske OData-spørringen kan hentes fra den lokale Invantive SQL-instansen ved hjelp av:
select url
from   sessionios@datadictionary

med resultat:

Noen eksempler på OData-filtre

Som inspirasjon kan følgende liste med eksempler på OData-filtre for bruk med spørringer være til hjelp:

  • ExactOnlineREST.Cashflow.PaymentsBulk@eol?$filter=DivisionCompanyName ne 'ACME Holding' and InvoiceDate ge 2019-01-01T00:00:00+01:00: hent alle betalinger etter 1. januar 2019 på tvers av alle partisjoner unntatt holdingselskapet.
  • ExactOnlineREST.Financial.GLAccountClassificationMappings@eol?$filter=GLSchemeDescription eq 'G/L Account Scheme': hent alle hovedbokklassifiseringer for rapporteringsskjemaet «G/L Account Scheme».
  • ExactOnlineREST.Financial.ExchangeRates@eol?$filter=Created gt 2020-01-01T00:00:00+01:00: hent alle valutakurser opprettet etter 1. januar 2020.
  • Teamleader.V1Flat.ProjectMilestones@tlr?$filter=responsible_user_id eq 1 or responsible_user_id eq 2: hent alle milepæler på Teamleader-prosjekter der brukerne med ID 1 eller 2 er ansvarlige.
  • ExactOnlineREST.FinancialTransaction.TransactionLinesBulk@eol?$filter=FinancialYear ge 2018 and Division eq 123456: hent alle finansielle transaksjoner for regnskapsåret 2018 for Exact Online-selskap 123456.

Bruk OData $filter med Azure Data Factory på OData-kilde

Med filteret konstruert for OData-tjenesten kan spørringen tilpasses som vist i følgende bilde (eksempel med bruk av Data Factory på Exact Online Frankrike):