Forum Discussion
regarding storing multiple tables in lake house
I’m using Microsoft Fabric Event Streams to stream multiple MySQL tables (around ~1500) into a Lakehouse using the Lake house as destination.
However, I noticed that the ‘Delta table name’ must be specified manually for each table name in my event stream, and Fabric does not provide an option to auto-map or auto-create all tables simultaneously.
Is it possible to bulk-map or auto-create Delta tables in Lakehouse from Event Streams, or is the only supported approach to define a destination per table (i.e., one branch per table)?
Creating a seperate routing rule for each table would not be sustainable. at a minimum, that would be 1500 filters in your eventstream. The better approach is to do the following:
Use the eventstream to read the CDC tables.
Create an Eventhouse destination and map the entire CDC feed into a single KQL table.
Turn on Onelake availability in your Eventhouse against your raw feed table to send the data to Onelake
Use spark to break the tables apart back into their original structure. Create a configuration table and a parameterized Spark notebook to do this part that takes newly arriving data and appends them to their existing tables.
HTH
Hello
As I've mentioned earlier, I would look into making the split in the KQL database using a KQL function. Here you can create one single function and call that for each table to split the data in the original table structures.The KQL language is quite powrefull to do dynamic field generation based on the JSON object from the CDC stream.
When the data has been split, you can use the metioned OneLake availability...
10 Replies
v-hashadapuCommunity Support
Hi Microsoft876 , Thank you for reaching out to the Microsoft Community Forum.
No, Microsoft Fabric does not currently support automatic or bulk mapping of multiple MySQL tables to Delta tables in a Lakehouse using Event Streams. The only supported approach is to manually create a separate routing rule for each table, one branch per table and explicitly specify the Delta table name.
Create an eventstream in Microsoft Fabric - Microsoft Fabric | Microsoft Learn
kustortininjaMicrosoft Employee
Creating a seperate routing rule for each table would not be sustainable. at a minimum, that would be 1500 filters in your eventstream. The better approach is to do the following:
Use the eventstream to read the CDC tables.
Create an Eventhouse destination and map the entire CDC feed into a single KQL table.
Turn on Onelake availability in your Eventhouse against your raw feed table to send the data to Onelake
Use spark to break the tables apart back into their original structure. Create a configuration table and a parameterized Spark notebook to do this part that takes newly arriving data and appends them to their existing tables.
HTH
- v-hashadapu
Hi Microsoft876 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.
Hi v-hashadapu,
Thank you for checking in.I haven’t found a complete solution yet. I’ve created an Eventhouse and enabled OneLake availability, which is currently storing all the CDC data into a single Delta table. However, I’m still figuring out how to efficiently split this data back into its original table structures, since it is stored in Delta Parquet format.
For now, I’m using a Notebook inside a Data Pipeline, with two separate notebooks one for historical data loading and another for handling UPSERT operations. The UPSERT notebook is scheduled to run every hour.
- v-hashadapu
Hi Microsoft876 , Thanks for the update and the information. Hope it works for you. If you have any other queries, please feel free to start new threads. We are always happy to help.
Hi v-hashadapu,
Thank you for checking in. I have not found a complete solution yet. As of now, it seems that Microsoft Fabric Event Streams requires manual configuration for each table, which is quite limiting for large scale scenarios like mine (~1500 tables).I was wondering if there’s any alternative approach perhaps using a Notebook or another Fabric component that could help automate the creation or mapping of Delta tables in the Lakehouse. Any guidance or suggestions would be greatly appreciated!
- datacoffee
Most Valuable Professional
Hi
Another approach could be to load all the CDC data to a single table in the Eventhouse, and then use an update policy to filter and dynamically expand the json payload to the different tables.
The update policy approach keeps the data inside the Eventhouse and does not require you to load data to OneLake.
I've written a blog post on the update policies here: https://dcode.bi/blog/update-policies/
In this solution you also keep the codebase in the same solution and can manage it directly in the kusto database.
I hope any of the provided solutions helps you reach your goal.
i can also help you directly if you send me a DM here on the platform ☺️
- v-hashadapu
Hi Microsoft876 , Thank you for reaching out to the Microsoft Community Forum.
We find the answer shared by kustortininja is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily.
Thank you. - v-hashadapu
Hi Microsoft876 , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.
