Data sharing with GraphQL API in Microsoft Fabric

Abstract

With the launch of Microsoft Fabric, a multitude of tutorials and explainer videos have become available to guide us through the integration, transformation, storage, and best practices for using Fabric. However, there is a noticeable lack of blogs or resources dedicated to exposing data via GraphQL APIs.

This post focuses specifically on the GraphQL endpoint available within Fabric, as well as the capabilities for data sharing and even data mutation.

Before diving into the details, let's start with a brief refresher on several topics :

GraphQL

GraphQL is a query language for APIs and a runtime for fulfilling those queries with your existing data. GraphQL provides a complete and understandable description of the data in your API, gives clients the power to ask for exactly what they need and nothing more, makes it easier to evolve APIs over time, and enables powerful developer tools.

Source : https://graphql.org

Microsoft Fabric API for GraphQL

Microsoft Fabric API for GraphQL is a data access layer that enables quick and efficient querying of multiple data sources with a widely adopted and familiar API technology. The API allows you to abstract the specifics of backend data sources so you can concentrate on your application's logic, and provide all the data a client needs in a single call.

Supported Fabric data sources are Data Warehouse, Lakehouse via SQL Analytics Endpoint, Mirrored Databases via SQL Analytics Endpoint & Datamarts.

The Fabric API for GraphQL includes:

• Automatic data source schema discovery.

• Automatic generation of queries and mutations.

• Automatic generation of resolvers.

• Support for views for SQL databases and data warehouses.

• Support for stored procedures for SQL databases and data warehouses.

• Support for multiple data sources with corresponding fan-out queries.

• Ability to create one-to-one, one-to-many, and many-to-many relationships.

• Ability to select individual objects to be exposed from a data source.

• Ability to expose specific columns from data source tables.

Source : https://learn.microsoft.com/en-us/fabric/data-engineering/api-graphql-overview

Setup

1) Get data

To get started, you'll need to create a "GraphQL API" artifact in Fabric. Then, add data from your preferred source.

Figure 0.1 – Get Data

2) Manage relationships

The Fabric API for GraphQL allows you to manage relationships between tables so that you can obtain results with a single API call. For example, by linking the Customers and Sales tables, when you query for Customer ID 11428, the API can return both the customer's attributes from the Customers table and their sales data from the Orders table.

Figure 0.2 – API Call to retrieve customer info and sales

3) Create an application in Azure

To access data outside of Fabric, we use an Azure application for authentication. The user has permissions for the application, which uses the application ID and secret key for authentication. Since this topic is not the focus of this post, we won't delve into it here. For more information, please visit Microsoft Learn.

4) Create a React App to display data

The React application will handle API calls and display the data on a webpage.

Use cases

1) Expose data outside Fabric

To display data on our webpage, we make API calls to query our Fabric Warehouse. The returned data is then shown on the page.

Parameters in the API calls allow for different results to be displayed. For example, selecting the top 5 or top 10 will yield different results on the webpage.

Figure 1.1 – top 5 data

Figure 1.2 – top 10 data

API calls can be simple requests (see Figure 0.2) or they can involve stored procedures.

In our example, we call a stored procedure named “TopNCustomers” with a parameter that specifies the number of customers to display (either 5 or 10).

Figure 1.3 – Stored procedure TopNCustomers

Figure 1.4 – GraphQL TopNCustomers

In our example, we see that the person with the most orders is "Deanna Perez."

We are therefore interested in getting the details of her orders. To do this, we can enter her customer ID (11428), and the API will return the requested information.

Figure 1.5 – GraphQL Customer’s orders

The API below returns the orders for the client with ID "29825." However, this number can be passed as a parameter.

Figure 1.6 – GraphQL Customer’s orders

2) Update data using API

In addition to exposing data through APIs, it is also possible to create, update or delete them using mutations.

In the example below, the interface allows us to input a Customer ID. When the Fetch Data button is clicked, the API call is triggered, and the result is displayed on the webpage.

Figure 2.1 – Web UI

We can therefore confirm that the API returns the correct data from the Data Warehouse.

Figure 2.2 – Customer ID from Warehouse

The API below retrieves the information for Customer ID 29767.

Figure 2.3 – GraphQL Customer ID

In the web interface, an edit button is available for updating the email address. When you click on this button, you can modify the email value. In this example, we change the address from "test123@aventureworks.com" to hello@aventureworks.com.

By clicking the submit button, the API call triggers a stored procedure (Figure 2.7) to update the data.

Figure 2.4 – Update email address

Figure 2.5 – Update email address

If we run the select statement again for Customer ID 11428, we can verify that the email address has been updated.

Figure 2.6 – Updated email address

The script below calls the stored procedure that updates the email address.

Figure 2.7 – UpdateCustomer stored procedure

The API below executes the stored procedure with the parameters Customer ID set to “11428” and the email address value as "hello@aventureworks.com".

Figure 2.8 – GraphQL API UpdateCustomer

In the example above, we successfully demonstrated the ability to expose data through GraphQL, as well as update data from GraphQL to Microsoft Fabric.

As you may know, security can be configured on the Warehouse or Lakehouse. Depending on which user connects to the application, only the data they are authorized to access will be displayed.