Introducing API for GraphQL and User Data Functions in Microsoft Fabric | BRK164

[Music] all right to go all right hello everyone thank you so much for joining us here today I'm dipy borar uh VP and GM at Microsoft responsible for our strategic isvs and our fabric data development experiences uh with me is my awesome co-speaker Monica Boris hi everyone I'm Monica Boris I am group program manager for the data development team in fabric owning the features you're going to see today awesome uh so before we get started a a quick poll with a show of hands how many of you here are developers all right that's that's great well you're here at build right it's a show for developers great to see how many data Engineers all right a few more uh data architects perhaps all right and then how many of you work with data that was a trick question thank you so much well here's uh a quick agenda of what we're going to take you through today uh we will we have two new items in fabric uh the API for graphql as well as user data functions we'll take you through what they are uh as well as real world examples using our favorite company cont alsoo Outdoors uh and uh we'll walk you through uh an example and demos and then we'll uh close it off with some resources and takeaways now for our Koso Outdoors introductions hello everyone I'm dipti borar I am actually the manager for our internal applications team we build data Centric apps and uh it's an awesome team I also have uh Monica here Monica I am Monica I am data engineer and data API developer at Koso outdoors and dpy is my boss all right so we'll see how it goes today all right um now with that uh as you might know contoso Outdoors is a retail company right and uh given we work with data our analytical systems of course use fabric if you haven't heard about it yet you should you should have and there's a lot of resources out there and this is our solution architecture for our backend so our sales data lives in uh the sales Warehouse that you see here we've populated it using a data Pipeline and then we also have our inventory data partner inventory in another Warehouse uh which is um uh which has another set of tables now we're working on an internal portal our planning team wants to use this information uh of from the analytical systems and make it available this is sales data Revenue data inventory data and they need this immediately so Monica I need your help here because I think we're not going to be on time for this project I think I can help there is a new API for graphql item in fabric that can definitely help with this problem uh would you believe me if I told you that we can fetch all the data for our portal with just one API call one API call not really I don't think so I mean this is our application here uh we've built out the ux we've built the front end we have some data in there so it's not really real data uh and the back end needs to be built out uh but you're saying it's really easy uh so maybe you can walk us through uh you know how we can get there of course let me show you how this uh Works in uh in fabric um so our API for graphql will take care of um fetching the data from fabric from our data warehouse and also from our other Warehouse which is our partner inventor and provide it to our portal uh all with the power of graphql well from what I remember a graphql server managing it is really complicated right so you have to get uh the configuration done then the schema Discovery or the definition needs to be done you have the resolvers you have uh of course security a range of things uh it's it's complicated right yes it is um but it's not complicated anymore okay not in fabric um and I'm going to show you why before we get there um just another quick show of hands how many people um are familiar with graphql all right that's great to see uh but before we go into the details let me share a few things about graphql for those who might not be uh familiar graphql is a popular query language and runtime for data apis uh it is um um built upon rest techn technology uh but instead of having multiple endpoints you have one single intelligent endpoint that lets you fetch only the data that you care about um the schema it is decoupled and abstracted away from the underlying data sources schema which makes this uh more resilient and flexible and speaking of the schema it exposes types and fields which correspond to your um tables and columns in your database uh relationships are for joints uh queries are for retrieving data and mutations are for creating updating and deleting data so now that being said let me show you how um uh it all works in fabric because today we are announcing we are announcing API for graphql now in public preview um it's a complete s experience as you would expect um and we'll talk a little bit more about it range of data sources that we support and it's all fabric native um so if you move on to the next slide um you'll see here that uh graphql sits on top of all our data sources that fabric supports so lake houses warehouses all of the mirrored databases this can be Azure SQL DB it would can be Cosmos DB or even Snowflake and so now you have a very easy API on top of all of these data sources to to to that you can expose to any of uh any endpoint applications any any applications at all uh all of this is sassified uh you will see the experience in just a minute uh and more importantly the security and the governance is built in uh all of it is integrated into the fabric portal and uh the capacity model uses the CU building model so it will it's it's an expansion of our existing fabric Model uh and uh it would use it now what what graphql allows you to do is essentially abstract away the complex database schemas that you might have because every database every data source data warehouse might have a completely different schema and this abstracts the way that complexity creates a new intelligent API for you on the top that you can then access within your application so let's see it in action Monica that's right let's start by creating a new graphql API item in fabric as you know all we need to do is uh give it a name and we will be ready to attach a data source in our case it's our contoso sales Warehouse as you can see fabric automatically discovers the schema uh which lets you expose only what you want and it automatically creates the graphql API schema all the types all the fields based on the uh data source you selected you can further inspect its properties uh you and uh you will see that um queries and mutations are automatically created and added to the schema you can further update the schema if you want um without impacting the underlying data source structure so let's go ahead and and create our first query for the customer's type and let's select a few of its Fields let's say customer ID first name and last name and run this query right here in the fabric portal and the results are immediate uh returned we can even further filter or um decide to just return the first few rows let's say the first 10 uh and um we can rewrite and we see that only those many rows were returned uh notice how easy it is to use the uh query editor in the fabric portal it supports intelligence and auto completion I barely had to type anything it's that simple all right well it seems really easy and simple um uh you've you know you have uh the customers information here but we also have our orders which is in a completely different table Monica so it's a little bit more complicated can you show us how we can do that that's right and in tql we would do this with joints but in graphql we need to use relationships so uh in the uh manage relationship dialogue in fabric we will create a new relationship between the customer type and the sales order header on the common uh customer ID field and we will um pick one too many cardinality and once the relationship is created we will see that a new field has been added to the customer type to den not this uh relationship and we can then just add that field to our query and uh then select a few of its uh property such as sales order ID uh and maybe order date and why not toal you and we are ready to run it again and there you have it now we have order details information for every customer and and this will help fill that table Grid in our portal all right okay maybe we can see all this data it's now it's real world data it's not uh you know madeup data maybe we can see it within the application oh yes it's very easy uh but first remember this is the query we have um created and we're going to use to populate that table notice how simple the query language is this makes it very uh very easy for any client application to fetch only the data it needs nothing less nothing more so let's go to our application which right now it's empty because I removed all the hardcoded data and first thing we'll have to do is to copy this endpoint for our graphical API from the fabric portal and paste it in our code this is where all the magic happens so um also we're going to have to paste the query that we wrote and with this we'll have to form a post request to this URL with the query as body in Json format and issue a fetch let's save this and see if it worked and there you have it our react portal automatically uh gets uh populated with the data returned by our API that's wonderful I mean that's it's still just a single API what we've seen here is you've been able to get uh the tables so the customer data from the customer tables as well as the order table uh we've used relationship uh for the join that would have we would have done in SQL uh and now it's all populated uh but we still have some missing charts up there uh and so it's a little bit more complicated uh we have we need aggregated data right so we we want the top 10 selling products we want uh some monthly Revenue numbers uh let's keep going Monica this is this is sounding like it's really easy yes and we're getting really close um and in uh you know the the data warehouseing fabric is very powerful for aggregating data so I'm just going to leverage that and I'll uh uh create a view and uh a store procedure for aggregating data and here is um you know our view just uh Returns the the um total revenue uh per month and uh because it returns data the graphql API just exposes this as a query that we can uh just add um however the store procedure that I implemented uh it's called top end products and it Returns the uh top and product it takes a um integer variable um so it returns this top and products by name and uh quantity sold uh and um this is very cool because this sort procedure returns data fabric automatically detects that and it exposes it as a query now if it would have updated data it would have exposed it as a mutation so this uh query I can just use like before but moreover if I want uh to make this a dynamic query I can use a variable for n uh this is how I'm going to use it in the code anyway I don't like to hard code things and I can test it in the portal by giving a value for n so these two queries will be now uh be part of our one API request as well and uh it will populate they will populate our two charts in oural that's great um so we've we've seen uh you know just simple queries we've seen relationships now you're saying we support views and store procedures as well within um within graphql uh but it's uh it's a little bit more complicated because if you remember our solution architecture we have another Warehouse so our uh you know we have a uh partner data that's sitting somewhere else uh and we need to get that and access that one as well so it's going to be harder right that's right but not really okay um you don't have to create a new API or anything like that all we have to do is to add that other Warehouse which is our partner inventory to the schema just like before select the table that we want and as you can see it has been added to the API schema as uh and we have the inventory type that we can easily query for um in the editor just add it to our total uh query with a few of its items name and uh um quantity and now maybe also return the first 10 or so and we will be ready to run this entire query and get the results with the power of graphql from multiple data sources in just one request wow all right that was really awesome let's let's see you know how it all works in uh seed in action sure I mean all we have to do is just update this query in the code and uh we will be ready to see the application pull the data the users our users will just authenticate using entra authentication and our UI elements react will automatically get populated with the data returned by our one API call note that uh ENT ID is um supported out of the box you don't have to um to manage any secrets or any credentials the uh uh the tokens are being passed through all the way to the API and to the underlying data sources yep all right so this is this is great um you know but I almost forgot uh now that you've shown me the full application sort of uh populated here uh I see some incorrect data there and uh the our you know our planning team probably wants to go in and update and fix that incorrect information so is that called A mutation is that right yes does that we can use a mutation um to update data and I'm going to show you how easy that is um we're going to update data for um the inventory uh type and for that we're going to use one of the automatically generated mutations uh which is called update inventory um we also have mutations for creation delete as well so let's um build this um uh request for product with the ID let's say 879 and let's update its quantity field for uh let's say let's give it 57 okay um and return the results and we'll be ready to execute this and this actually updates the data in our warehouse uh we can even verify that it was updated by running a uh a query that um uh retrieves the exact item that we updated by the primary key which is the product ID and we can see the value is updated as expected that's awesome um Monica is doing really well here I am very happy with this application thank you so much for your help in building this Monica um and so um folks as you've seen here through this demo um uh if you click click on uh graphql the API for graphql is extremely rich right what you saw through this entire experience is number one it's quick quick creation complete SAS experience everything's in the portal we support queries mutations relationships which is the equivalent of joints uh as well as we have the intelligent uh editor with in intellisense uh we've already created uh mutations simple mutations for you that you could use it works with a range of data sources so it works on warehouses lake houses all of our mirror databases uh and again that's SQL Cosmos and Snowflake and as others get added on it will just work with any other mirror database with the tsql endpoint that gets created for it now it's also fabric native and what that means is uh what you expect out of uh the rest of fabric you should expect out of our graphql API uh the pass through that you saw with the ENT ID is very seamless so you don't actually have to store any uh any sort of um uh special Secrets Etc all of that is handled for you and uh the lineage works the security and governance is taken care of and also we will use CU billing uh which will kick off in um uh in a in a couple of months um based on the capacity model so very excited for all of you to try it out in public preview here at build uh and uh and now it's back to Koso Outdoors yes so our application our internal app was a very big hit uh a lot of people are actually using it internally and now they want more you know they they like having this analytical application that's powered by all of this uh all of this data that's in Fabric and uh Monica now they actually need information um to sort of look ahead so they want to use Predictive Analytics and they want to have you know analysis on all of the historical data and then predict what the revenue might look like so this is going to be a lot more complicated than our your graph ql API but hopefully you have something in your pocket that you can pull out yes indeed indeed that would require us to implement some custom business logic that uh uses our some of our internal libraries that we've implemented to calculate uh these type of predictions um and we'll have to um integrate the this custom business logic with the rest of our solution in fabric now luckily there is a new feature in fabric called user data functions that will make this a breeze all right so here is the updated architecture uh for our application as I mentioned we're going to use um user data functions in fabric to read the data from our orders table from our warehouse and feed that to our internal um sales projections Library which will calculate the revenue um projections for the rest of the year and then also from our user data function we're going to write the data back to the warehouse where via graphi it will be fed back to our portal and all this will be kicked off by our data pipeline periodically as data is coming in the projections will be calculated and the data will always be up to date and voila we are also have uh announcing a sneak peek of user data functions uh it's a new item type within fabric uh here you can bring in your reusable code right you might have a lot of business logic that that actually sits outside fabric that you want to pull in use via your pipelines um maybe update your um your data sources uh invoke it through uh event streams and various different mechanisms so same SAS experience uh Works in a range of data sources and fabric native in addition we are introducing a functions Hub so this is your go-to place to see all your functions ENT that would be your entry point you can create new functions you can discover and collaborate on these uh within the functions Hub and then even drill down into each function to see what data sources are being invoked uh and perhaps even collaborate on that uh on that function and share it out so we will have a range of uh different items that allow the invocation of a function we're starting off with pipelines and you'll see that in action in just a bit uh and then the functions Hub is a horizontal experience across all of the other um uh um other personas like uh you know data engineering Etc all working on the range of uh different uh data sources as well so warehouses lake houses and mirrored uh databases now you can have a lot of reusable code so it's more maintainable uh it's a collaborative and you will have the same security uh governance experience that you get with fabric also it's part of our capacity model so we've expanded that further uh CU billing will uh kick this off and um uh and and get you going so with that um uh you know back to um back to uh contoso uh perhaps uh you can uh maybe show us an example of what a fabric function data function might look like yeah it's very simple as you can see all you need to provide are the input parameters and the output type then Implement your logic and that's pretty much it all right so yeah let me Me Now quickly jump into the fabric portal and show you this in action so we're going to go ahead and create a new user data functions item again just provid it a name and um we'll be ready to create a new function this is a sample function uh fabric creates for us automatically called hello fabric we can inspect the code as you can see it takes a string argument and it returns a welcoming uh string uh it can also be test it right here from the portal by supplying a value for the parameter let's say monah uh and there it is that's the output moreover we can uh take a look at logs from previous um runs of the function and that's very very useful for troubleshooting later all right well this seems uh you know simple right uh create and there's a great experience of for our developers to go in and debug uh test it out even see the invocations but our code is a little bit more complicated right and we're using our our sales Warehouse um I mean Predictive Analytics right come on it's got to be complicated indeed indeed and let me show you um an example of what a more complex user data function is hence the name um this example right here reads data from a warehouse that's identified by this employees alas um so note that um in fabric um you don't have to worry about connection strings you don't have to worry about Secrets or anything you are are given a connection for the data source that you select and with that connection you can just read the data then Implement your logic to validate it to augment it anything that makes uh sense for your application and then even write it back um this makes it very very easy and developer friendly uh to implement custom business logic in fabric all right so now let me show you exactly how we would go about um implementing such a more complex function in fabric first thing that we need to do is to select which data source Source we want to use consume in our case of course it's our Koso sales Warehouse so as you can see we have that alas that is used in the code um automatically U selected for us now let's open in vs code note here that we have a new um experience a new extension in vs code that's dedicated to fabric here you can log in fabric select one of your workspaces inspect any all your items we have you have you can even create new items you can take actions that are specific to those and we also have an experience that's dedicated to user data functions that make implementation and uh uh local uh uh development experience a breed I have added two new user data functions here one just reads data from the warehouse and perform some very simple validation and this other one is the one that uh calculates the projections it invokes our internal library and that with the data return from that uh we just connect to the warehouse and write the data back to the warehouse uh so now let's start debugging uh as you can see we have here an experience that makes it very easy to run functions locally uh pick the one that I want to execute provide an input uh uh variable value hit break points inspect local ver all everything you'd expect from your usual debug sessions uh and uh once I am happy with how it works while it returns we will be ready to uh deploy this to fabric uh using help from the fabric extension in vs code and uh once it's published back to fabric I can go back to the portal and there are my new uh functions that I've just created I can see the updated code I will be able to um you know execute they're all ready to go up and running that was uh very very uh easy um you know maybe we can see it now integrated into the application um as well of course so like I mentioned before this will be invoked by uh our data pipeline so um data functions uh user data functions in fabric are really deeply integrated n natively uh with uh the rest of the fabric ecosystem and and they can be easily invoked from pipelines or um you know also other uh items in fabric for example uh event streams as destinations um or even real time triggers many many other um uh fabric items uh but for pipelines all we have to do is add an activity of type functions give it a name and uh then we're going to uh update it settings to point to the workspace the item and the specific function we want to invoke which is our sales projections we can even provide values for its arguments uh and this could be you know pipeline variables output from other activities uh anything that you can uh compose in the pipeline Expression Builder and that's how easy it is with the Deep Integrations between user data functions and the rest of the fabric ecosystem great this looks like a very seamless integration we we showed uh uh pipelines here and and now here's this is our application so uh is that is that a new button yes our our planning team wants to see the projections in the portal easily so we've added a button that when you uh when they will click it when new chart is going to be added and populated with the data uh that the function um wrote back into our warehouse and that is how easy it is to create you know not only very simple um connections to the data in fabric but even uh further custom powerful custom business logic within fabric um with uh user data functions and graphql got it well don't worry about that downturn and upturn we're we're a seasonal company so it's all good it's all good here uh but uh functions is now data functions are now available in private preview uh it's a sneak peek that we gave you here today but we're looking forward to uh many of you trying it out and giving us feedback um as you saw with the demos it's really a very seamless experience so it's all sassified within the portal uh you uh it's easy to create uh you have a very simple programming model so there's no you know complex uh uh headers Etc very very simple with parameters that can be passed in we have vs code integration and I'll talk more about that in just a minute you saw python uh you know here but we also support C and there will be more that we add over time and so this gives you a broad range for your developers depending on which L whichever language which they're more comfortable with uh in addition uh they work with fabric data sources similar to graphql you have data warehouses uh lake houses as well as our mirrored databases and all of this will be these udfs will be invocable uh from a range of other fabric items so we're starting off with pipelines because that's a very very common um scenario and ask as well uh so that you can have your business logic your own logic sort of embedded within a pipeline uh to to do whatever whether it's Predictive Analytics or whether it's U something else you might have existing libraries that can be used um as well um now in terms of uh the the local experience what you also saw is a brand new extension for fabric and this includes the workspaces that seamlessly now um uh can be used within vs code so uh if you uh if you take a look at this you see here that uh you can pick your workspace whichever workspace you you would like to use so you enter it here uh we used um the contoso outdoors workspace that we had within the application uh and once the workspace is selected you have all of your items within that workspace that actually shows so you see the the fabric I you know the logo on the the left that essentially is the the fabric extension uh for vs code so this is also in private preview uh once you click on a particular item uh you can then go you know uh and get more information about it here is functions uh the functions uh experience within vs code you see the hello hello fabric that that you we have to get you started but of course you can write your own functions uh that then can be published in back into your workspace within the fabric portal so when you click on a particular data function on the function set which has a range of functions defined underneath you can then click uh published to fabric and then and all your local code that uh gets uh synced back to the portal and and is available to run via other items so it's very seamless experience of course when you're when you're coding you really need a local experience so this completes the local Dev Loop end to end um and allows for um debugging testing uh locally in your in your environment itself so super excited uh about the extension along with the functions as well well so private preview for functions as well as the the vs code extension is now open I'm going to just be quiet for a moment and and let you take a let uh let you uh pick up the the QR code here uh we have a a lot of interest in this and uh really looking forward to your feedback um so that in the next next uh you know few months uh we can iterate over it and uh also decide on what the road map looks like in terms of what other integration should follow right after that um and so uh with that we have a range of resources available the slides will be available to you as well we have a blog post for each of these new items um that you can uh that you can read to learn more about we have documentation and then we have some links to uh contact us and uh we're uh we'll be looking forward and uh to your um uh to your engagement uh and to your feedback and um with that thank thank you very much we hope you've enjoyed uh this interactive presentation um big shout out uh to the team uh we have uh uh folks on our chat online as well uh and the entire fabric data Dev team uh it's been quite the journey in in pushing these out as it is uh with uh every project and uh like we uh you know Koso Outdoors the project ran really well and so did our uh items thank you all so much take care thank you so much is there a question question I'm about control and lineage the idea oful yeah so lineage will be supported uh just like with any other fabric item so you'll be able of course to uh take advantage of all the governance the usual governance functionality that is built into fabric control used that is absolutely on the road map okay yes thanks for the question any other questions yes please yeah yes that is absolutely supported and that is part of uh what makes this such a powerful solution you can bring in your own libraries and packages yeah so just to repeat the question uh for the folks online uh if you have existing packages or libraries can they be pulled in uh into functions uh and Monica the answer is yes yes yeah so it's all supported you can pull them in especially with python we expect there to be U you know a lot of libraries that you might want to pull in uh and it would work um for that yeah yeah more question go ahead uh be the question so if if uh you can Define your own mutation in graphql not at this point uh we have uh only the automatically generated mutations and queries but you can yeah so the way you can do it is by um creating your store procedures in your underlying data sources and then using graph on top of that so that is for the time being yeah and the back one more question that is on the road map as well yeah yeah please reach out to us and we'll be uh happy to work with you to get you unblocked yeah so F folks on the online the question was around service principles for graphql so that is on the road map uh right now it's essentially pass through uh through uh entra uh ID and uh other mechanisms will come in the future yeah all right thank you so much thank you so much folks great questions have a great rest of great day e e