This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(19.2, 28.999999999999996%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETW%3C/text%3E%3C/svg%3E)
We have SAP and Hybris hosted onprem, which are the book of record for product and promo. The goal is to build a ETL solution to send to partner systems on products on promotion (effectively joining the feeds from both systems), the technical objective is to leverag azure cloud to build something scalable and reusable.
Shown in the diagram, I am thinking to use Azure Event Hub to build a steaming ETL, with SAP to replicate the product catalog to SQL Azure then use asa job to filter out by the Hybris promo events. However, with the size of SAP product, it does not seems to be a better fit.
Please help share some thoughts or best practices for this use case. Thanks
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
Hello @Tommy Wang and welcome to Microsoft Q&A. Thank you for your question. Please let me know if I understand your ask correctly.
You have the above architecture, but when it comes time to filter the streaming data using Stream Analytics, the reference data (stored in Azure SQL DB) is too large, and the Stream Analytics query chokes. You would like to know of alternatives for the filtering step.
Given that reference data > 300MB for Stream Analytics is still in preview, I assume you are open to using other services.
Two solutions are HDInsight and Azure Databricks. HDInisght is better for "always on" workloads.
thanks for the update, at this point only use of event hub, i have no go ahead for the proposed alternatives.
what if i subscribe the function directly to the event hub then do a query to the on prem database while process the event directly from there, is this anti pattern?
@Tommy Wang I had another idea.
Are you using partitions in your Event Hub? I started thinking, if you could partition the event streams based upon segments of the lookup you will later have to do, then you could break up the stream analytics job so the lookup table is smaller.
You might even be able to do some of the filtering via partition keys itself.
As to using a generic database for the lookups, I worry about the scalability. If the formatter is querying the database for each individual event, that does not sound like a good idea. If the formatter is doing a batch fetch to load table into memory to handle multiple incoming events, that sounds more reasonable.
If you found your own solution, could oyu please share it here with the community, @Tommy Wang ?
No I haven’t found my solution, the question is really how to do real time etl on two delta feeds, the workaround i am in now only works because it is more a daily batch, if it is real time then I agree it is not a scalable solution for each call to do a generic database invocation.
Load everything to memory is not realistic due to the dataset size. Also it is more a dynamic ‘lookup’, as my upstream system (sap) keep send out deltas. If ASA job can support cosmosdb lookup, it might work by keep a separate stream updating the reference dataset..
Any better idea? I was told Kafka can handle this, not much expertise there but wondering if event hub has similar capability..