Microsoft Fabric is a powerful unified analytics solution that allows users to seamlessly connect to various data sources, including Azure Databricks, and create insightful reports and visualizations without the need to move the data.
In this tutorial, we'll show you step-by-step how to connect to Azure Databricks generated Delta Tables and create a report in Microsoft Fabric.
By the end of this tutorial, you'll have the knowledge needed to read Azure Databricks generated Delta Tables from a Microsoft Fabric using Notebook and SQL query. You will also learn how to create a Power BI report that can help drive business decisions. So, let's get started!
Before you connect, complete these steps:
In this case, I am using parquet files stored in ADLS Gen2 account to create a delta table.
%SQL
--create external table from parquet files stored in ADLS Gen2
CREATE TABLE fact_internet_sales
LOCATION 'abfss://demo@[yourdatalake].dfs.core.windows.net/fact_internet_sales'
AS
SELECT * FROM PARQUET.`abfss://data@[yourdatalake].dfs.core.windows.net/fact_internet_sales`;
%Python
#Read parquet files stored in ADLS Gen2
df = spark.read.format('parquet')\
.load("abfss://data@[yourdatalake].dfs.core.windows.net/fact_internet_sales")
#Write to an external delta table
df.write \
.format("delta") \
.option("path", "abfss://demo@[yourdatalake].dfs.core.windows.net/fact_internet_sales") \
.saveAsTable("fact_internet_sales")
Open the Fabric workspace to read, analyse and visualise the data.
Field |
Details |
URL |
In this case, I am using ‘Organization Account’ Authentication kind and hence need to sign in.
Field |
Details |
Connection |
Existing connections for the specified storage location will appear in the drop-down. If none exist, create a new connection. |
Connection name |
The Azure Data Lake Storage Gen2 connection name. |
Authentication kind |
The supported models are: Organizational account, Account Key, Shared Access Signature (SAS), and Service principal. For more information, see ADLS shortcuts. |
Field |
Details |
Shortcut Name |
Name of your shortcut |
URL |
The Azure Data Lake Storage Gen2 URL from the last page. |
Sub Path |
The directory where the delta table resides. |
Click on the table (fact_internet_sales) and the data in the table will show up.
The data in the table can now be queried directly from the notebook in Fabric.
Select the Run Cell button or press Ctrl+Enter to execute the query and view the data.
The data in the table can also be queried directly using T-SQL query in Fabric.
After opening SQL Endpoint from the workspace, expand the database, schema and tables folder in the object Explorer to see all tables listed.
Right-click on the table (fact_internet_sales) or click on ellipses (…) next to the table, click New SQL Query and then Select TOP 100 rows.
The data in delta table can now be accessed and analysed in Power BI. You can either create a new dataset or use the default dataset created as part of lakehouse provisioning for a new report. For more information, see Direct Lake in Power BI and Microsoft Fabric
If you are in the SQL endpoint mode, click on New Power BI dataset from Reporting tab.
In conclusion, this guide provides a seamless solution for accessing Azure Databricks generated delta tables from Microsoft Fabric and visualizing the data in Power BI without the need to move the data. By following the steps outlined in this guide, you can easily connect to your delta tables and extract valuable insights from your data. With the power of Azure Databricks and Microsoft Fabric combined, you can take your data analysis to the next level and make informed decisions that drive business success.
Give it a try and let me know if this was helpful.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.