I need help to load the data from Simplicate to Azure Data Factory. For now, we load full data from Simplicate to Invantive Cloud database and from Invantive Cloud Database to the Data Factory, but we want to limit the data size loaded from Invantive Cloud to Azure Data Factory and it should be the newst 3 days data from current date.
What table are you loading to Azure Data Factory? And what is the approximate size in rows? Can you define the date refers to: creation date, modified, or something functional?
I am loading all the table from Simplicate api to Azure Data Factory. The approximate size is more than 10000 rows. What do you mean define the date?
What is the name of the table?
Regarding the “date”: what column are you using to restrict the data to 3 days?
In every table, i had the column “created at” with the following format “2020-03-25 07:03:41”. Now i want to restrict transfer the data to ADF in the newest 3 days.
You can apply a filter from Azure Data Factory to solely retrieve the last 3 days as follows:
- Calculate date 3 days ago as a text in the format “YYYY-MM-DD HH24:MI:SS”.
- Note that Simplicate has chosen to project all dates to text values in their API using the Swagger metadata definition.
- Note that this textual representation has as nice feature that the same ordering of values applies: when date increases, the lexicographical ordering of the text increases too.
- Include the filter in your OData4 query with column, greater than or equals and textual representation.
- Invantive Bridge Online receives the OData4 and rewrites it into Invantive SQL, including the filter you have sent being rewritten to a
where-clause. - The Invantive SQL is then translated into calls for the Simplicate API. The
where-clause is rewritten to the Simplicate specific filter implementation. - The Simplicate API receives a request on behalf of your Azure Data Factory to get the data for the last 3 days.
Some more background at this topic on copying Simplicate to SQL Server.
Background: server-side filtering
This concept of forwarding filter criteria (called “server-side filtering”) does not apply always.
For instance, filters on fields added manually by Invantive are most often not forwarded. There are some exceptions like DivisionLabel on Exact Online. Although it is not an actual column in the Exact Online API filters on it are translated into specific divisions to be queried.
Neither are all APIs capable on filtering on any field. For instance, Exact Online has column-specific exceptions. But also Teamleader has considerable limitations.
Whether filters on fields can ultimately be forwarded to the cloud platform can be found in the documentation. Look at the column CAN_FILTER_SERVER_SIDE in SystemTableColumns@DataDictionary.
Finally note that server-side filtering is not possible with many more advanced filters, such as complex combinations of and, or and maybe even calculations. Invantive SQL has an extensive optimizer which simplifies expressions, performs peephole optimizations and rewrites queries, but still there are many thinkable constructs that can not be optimized yet. During regular monitoring we try to identify further optimization possibilities that impact many users and try to optimize these over time too.
how can i apply the filter in ADF? Do you mean use the query inside the OData source?
Yes, you can use the standard functionality of Azure Data Factory for providing the filter, for instance as described on the Microsoft site.
Thanks for your help. Could you help me more in detail? what is the URL server root to Invantive Cloud? and resource path? For example: My database name is Simplicate, alias is spe and columns to filter date is created_at. so How can i create a URL query?
Best is to create a new question for this. The original question has been addressed and using a separate topic helps people find answers.