Come filtrare i dati da una query OData su Azure Data Factory?

Ho bisogno di creare una query all’interno della connessione sorgente OData in Azure Data Factory verso Invantive Cloud.

Ad esempio: il nome del mio database è Simplicate, l’alias è spe e la colonna su cui filtrare è una data chiamata created_at. Configurazione come su Connect Azure Data Factory to Simplicate.

Come posso creare una query URL del servizio OData con filtro da utilizzare in Azure Data Factory?

Azure Data Factory

I dati di Simplicate vengono recuperati da Invantive Cloud in Microsoft Azure Data Factory. Questo funziona per Simplicate, ma anche per molte altre piattaforme cloud come Exact Online, Salesforce o ActiveCampaign, come descritto passo dopo passo su Retrieve a specific Exact Online OData4 data set using Azure Data Factory.

Stai cercando di filtrare OData su Power BI: una guida più pratica è disponibile su Reduce data volume and improve performance of your Power BI report by Filtering.

Assicurati di capire la struttura di Invantive Cloud prima di iniziare a ottimizzare le prestazioni e ridurre il download. Scopri la struttura su Invantive Cloud Structure. Contiene anche una bella immagine!

Azure Data Factory ti permette di trasportare e instradare i dati dal connettore OData di Invantive Cloud e da altri produttori OData (fonti OData) verso il tuo data lake o un database sulla tua istanza SQL Server.

Invantive Cloud è un produttore OData (fonte OData): collega i protocolli API interni di Simplicate a OData, aggiungendo metadati e funzionalità di produzione come caching, retry e logging.

Nota che esistono due versioni del protocollo OData con lievi differenze. I produttori (fonti) più datati lavorano con OData versione 3, ma OData 4 è fortemente raccomandato per il lavoro attuale poiché dispone di più operatori, offre un throughput migliore e ha una maggiore durata residua, pur continuando a evolversi per diventare ancora più avanzato.

La specifica OData v3 è disponibile su OData Version 3.0 · OData - the Best Way to REST. La specifica OData v4 approvata è disponibile su Documentation · OData - the Best Way to REST, mentre le modifiche future sono consultabili su GitHub - OData/odataorg.github.io: Staging site and collaboration repository for http://www.odata.org · GitHub.

OData v4 è supportato per l’uso da Azure Data Factory, Power Query, Power BI e tutte le altre versioni recenti conosciute dei prodotti Microsoft.

Ai fini del filtraggio, una query OData viene:

  • Prima riscritta in Invantive SQL, traducendo ogni filtro OData in un filtro sulla clausola where SQL.
  • Poi Invantive SQL viene eseguito contro le cache esistenti e, quando necessario, vengono effettuate chiamate API sulla fonte dei servizi web di Simplicate dal driver Invantive (una sorta di ODBC).

Nota che Invantive Cloud utilizza sempre l’attuale versione 4 di OData nel connettore OData. La fonte dati effettiva potrebbe utilizzare un’API con OData 2 o OData 3 o persino XML, SOAP o semplicemente JSON, oppure essere un formato di database relazionale nativo come PostgreSQL, IBM DB2 o SQL Server.

Filtraggio lato server

La traduzione da parte di Invantive SQL in chiamate sul driver Invantive (paragonabile all’uso di un driver ODBC) astrae il protocollo effettivo.

Questa traduzione in Invantive SQL come linguaggio intermedio per le tue richieste usando Azure Data Factory, prima di inoltrare la richiesta (quando necessario per ricaricare le cache) alla fonte dati effettiva, supporta anche l’inoltro del filtro alla fonte dati. Per esempio, un filtro OData con un operatore di uguaglianza viene riscritto in un operatore di uguaglianza in Invantive SQL. L’uguaglianza viene inoltrata alla fonte dati con molte API. Non tutte le API dispongono di un ampio supporto per il filtraggio, ma Simplicate sì. Altre fonti dati popolari con filtraggio preciso sono AFAS Profit, Twinfield, Chargebee, Visma.net Financials, Visma eAccounting ed Exact Online. Questo inoltro dei filtri è chiamato “filtraggio lato server”.

Questo concetto di inoltro dei criteri di filtro (chiamato “filtraggio lato server”) non si applica sempre.

Ad esempio, i filtri sui campi aggiunti manualmente da Invantive il più delle volte non vengono inoltrati. Ci sono alcune eccezioni come DivisionLabel su Exact Online. Sebbene non sia una colonna effettiva nell’API di Exact Online, i filtri su di essa vengono tradotti in divisioni specifiche da interrogare.

Non tutte le API sono nemmeno in grado di filtrare su qualsiasi campo. Ad esempio, Exact Online ha eccezioni specifiche per colonna. Ma anche Teamleader ha notevoli limitazioni.

Se i filtri su determinati campi possano essere infine inoltrati alla piattaforma cloud lo si può verificare nella documentazione. Guarda la colonna CAN_FILTER_SERVER_SIDE in SystemTableColumns@DataDictionary.

Infine nota che il filtraggio lato server non è possibile con molti filtri più avanzati, come combinazioni complesse di and, or e magari persino calcoli. Invantive SQL dispone di un ottimizzatore estensivo che semplifica le espressioni, esegue ottimizzazioni a buco di spillo (peephole) e riscrive le query, ma esistono ancora molti costrutti pensabili che non possono ancora essere ottimizzati. Durante il monitoraggio regolare cerchiamo di identificare ulteriori possibilità di ottimizzazione che abbiano un impatto su molti utenti e cerchiamo di ottimizzarle nel tempo.

Filtro OData

Il passaggio rimanente consiste nel costruire il $filter OData. Supponendo che tu voglia ottenere questo effetto in SQL:

select *
from   table
where  created_at >= VALUE

prima devi controllare il tipo di dato di created_at.

Noterai che l’API di Simplicate ha una tipica particolarità: il tipo di dato delle date non è in realtà una data ma un tipo di dato testo. La buona notizia è che la rappresentazione testuale della data si ordina lessicograficamente in modo identico alle date come tipo di dato data, quindi per esempio il 1° gennaio 2020 viene prima del 31 dicembre 2020, sia come rappresentazione testuale sia come data. Puoi trovare maggiori informazioni sul formato data di Simplicate su Limit Data load from Simplicate on Invantive Cloud to Azure Data Factory.

Tradurre il filtro in OData in Power BI

L’istruzione SQL deve essere tradotta in OData. L’implementazione OData di Power BI e Power Query è ben fatta, quindi il modo più semplice per farlo è usare una fonte dati OData in Power BI e poi osservare le query OData effettive inviate al servizio OData.

I passaggi per impostare un filtro OData su Simplicate (o qualsiasi altra fonte dati supportata) sono:

L’osservazione è più semplice da effettuare usando il monitor Invantive Bridge Online di Invantive Cloud, quindi esegui i seguenti passaggi:

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

Il filtro OData da usare è il testo che inizia con $filter. Il filtro sarà stato escaped per l’uso in un URL, quindi devi fare l’unescape del testo. Puoi farlo manualmente o usare un servizio online come https://www.url-encode-decode.com/.

Da luglio 2021, il monitor mostra anche il filtro OData accanto all’SQL generato per esso, come mostrato su https://forums.invantive.com/t/more-insight-with-new-bridge-online-monitoring/2463.

Costruire manualmente un filtro OData

Un filtro OData può anche essere costruito manualmente. La specifica OData è disponibile online. I filtri fanno parte della sezione 11.2.6 sulle query OData.

Le basi consistono nello specificare i tuoi filtri dopo $filter nell’URL. I filtri possono essere combinati usando and e or, ma puoi anche usare criteri composti da un lato sinistro (ad esempio il nome di una colonna), un operatore come eq per “uguale a” e un lato destro (ad esempio un valore costante). I gruppi di filtri possono essere composti usando gli operatori di raggruppamento ( e ).

L’implementazione OData di Invantive Bridge supporta molti operatori di filtro OData, ma non tutti. Ad esempio, hassubset non è disponibile. Nel caso in cui tu abbia bisogno di includere un filtro non supportato, ti preghiamo di segnalarlo al supporto Invantive con un breve caso d’uso. A seconda della complessità e della fattibilità del caso d’uso, valuteremo l’aggiunta di quello specifico filtro OData a Invantive Bridge Online.

Come soluzione alternativa puoi naturalmente sempre usare un filtro all’interno di Azure Data Factory stesso per filtrare i tuoi dati secondo le tue esigenze.

Costruire un filtro OData usando lo strumento di query

Tutti i prodotti recenti basati su Invantive SQL includono un driver per le fonti dati OData sul servizio Bridge Online. Essi ti permettono di recuperare dati da Invantive Cloud usando Invantive SQL e il bridge OData.

Esegui i seguenti passaggi:

  • Avvia Invantive Query Tool.
  • Apri il gruppo di database “Various”.
  • Scegli il database “Invantive Bridge Online”.
  • Inserisci i parametri del database come segue:
  • Clicca su “Connect”.
  • Costruisci la tua query del database come:
select * 
from   [Hours@Spe] 
where  created_at >= '2020-11-01'
  • Nota che il nome della tabella è Hours@spe incluso spe, poiché spe viene interpretato sul servizio Bridge Online. Se hai un alias locale come bol per Bridge Online, il nome completo sarebbe [Hours@spe]@bol.
  • Invantive SQL traduce la chiamata in una query OData, incluso un filtro.
  • La query OData effettiva può essere trovata nel monitoraggio di Bridge Online.
  • E la query OData effettiva può essere recuperata dall’istanza locale di Invantive SQL usando:
select url
from   sessionios@datadictionary

con risultato:

Alcuni esempi di filtro OData

Come ispirazione, il seguente elenco di esempi di filtri OData per l’uso con le query potrebbe esserti utile:

  • ExactOnlineREST.Cashflow.PaymentsBulk@eol?$filter=DivisionCompanyName ne 'ACME Holding' and InvoiceDate ge 2019-01-01T00:00:00+01:00: recupera tutti i pagamenti dopo il 1° gennaio 2019 in tutte le partizioni eccetto la holding.
  • ExactOnlineREST.Financial.GLAccountClassificationMappings@eol?$filter=GLSchemeDescription eq 'G/L Account Scheme': recupera tutte le classificazioni dei conti del libro mastro per lo schema di reportistica “G/L Account Scheme”.
  • ExactOnlineREST.Financial.ExchangeRates@eol?$filter=Created gt 2020-01-01T00:00:00+01:00: recupera tutti i tassi di cambio creati dopo il 1° gennaio 2020.
  • Teamleader.V1Flat.ProjectMilestones@tlr?$filter=responsible_user_id eq 1 or responsible_user_id eq 2: recupera tutte le milestone sui progetti Teamleader con gli utenti con ID 1 o 2 come responsabili.
  • ExactOnlineREST.FinancialTransaction.TransactionLinesBulk@eol?$filter=FinancialYear ge 2018 and Division eq 123456: recupera tutte le transazioni finanziarie per l’anno finanziario 2018 dell’azienda Exact Online 123456.

Applicare il $filter OData usando Azure Data Factory su una fonte OData

Usando il filtro costruito per il servizio OData, la query può essere adattata come nella seguente immagine (esempio che usa Data Factory su Exact Online Francia):