On-Premise Python Code to Load Data to Microsoft Fabric Lakehouse
This blog post includes the Python script used in loading file data from an on-premise location to Microsoft Fabric Lakehouse. The data is pushed using an on-premise Python script with MFA authentication using an authentication token(OAuth). The Token has been generated using Azure App. The app is registered on portal.azure.com
You can download the below CSV files for testing
https://github.com/amitchandakpbi/powerbi/raw/main/csv/
The video: Microsoft Fabric: Load on-premise data file to Lakehouse using on-premise Python using OAuth Token
The Python code
import requests
import json
import pandas as pd
import os
# Code to create get token
def get_access_token(app_id,client_secret,directory_id,user_name,password):
"""It will create a access token to access the mail apis"""
app_id = app_id #Application Id - on the azure app overview page
client_secret = client_secret
directory_id = directory_id
token_url = "https://login.microsoftonline.com/"+directory_id+"/oauth2/token"
token_data = {
"grant_type": "password",
"client_id": app_id,
"client_secret": client_secret,
"resource": "https://storage.azure.com/",
"scope":"https://graph.microsoft.com",
"username": user_name,
"password": password,
}
token_headers={
"Content-Type":"application/x-www-form-urlencoded"
}
#print(token_url)
token_response = requests.post(token_url,data=token_data,headers=token_headers)
token_response_dict = json.loads(token_response.text)
error_exists = checkKey(token_response_dict,"error")
#print(token_response.text)
if error_exists == True :
raise Exception("Error in getting in access token")
else:
token = token_response_dict.get("access_token")
refresh_token = token_response_dict.get("refresh_token")
if token == None :
print("Unable to get access token")
print(str(token_response_dict))
raise Exception("Error in getting in access token")
elif refresh_token == None :
print("Unable to get refresh token")
print(str(token_response_dict))
raise Exception("Error in getting in refresh token")
else:
return token
def patch_file(access_token):
token_url = "https://onelake.dfs.fabric.microsoft.com/6e12c13a-bc6f-47d5-9c68-3432e62bc496/7ca2d244-2442-4596-af22-50a9a4bcb535/Files/sample/item.csv?resource=file"
token_headers={
"Authorization" : "Bearer " + access_token,
"x-ms-file-name": "item.csv"
}
print("creating file in lake")
# Code to create file in lakehouse
response = requests.put(token_url, data={}, headers=token_headers)
print(response)
token_url = "https://onelake.dfs.fabric.microsoft.com/6e12c13a-bc6f-47d5-9c68-3432e62bc496/7ca2d244-2442-4596-af22-50a9a4bcb535/Files/sample/item.csv?position=0&action=append&flush=true"
token_headers={
"Authorization" : "Bearer " + access_token,
"x-ms-file-name": "item.csv",
"content-length" : "0"
}
print(token_url)
print("pushing data to file in lake")
file_path = r'D:\Transfer Jun-2023\Amit Files\Google Drive\Kanerika\power bi\Data\csv\item.csv'
#Code to push Data to Lakehouse
with open(file_path, 'rb') as file:
file_contents = file.read()
response = requests.patch(token_url, data=file_contents, headers=token_headers)
print(response)
def checkKey(dict, key):
"""It will check the given key exists in the response dictionary and return True or False"""
if key in dict.keys():
# print(dict[key])
return True
else:
return False
def checkKey(dict, key):
"""It will check the given key exists in the response dictionary and return True or False"""
if key in dict.keys():
# print(dict[key])
return True
else:
return False
access_token = get_access_token("<Your Application ID>","<Your Client Secret>","<Your Directory ID>","<Login Email>","< Login Password>")
#put_file_one_lake(access_token)
patch_file(access_token)Hope you are able to use this code with the video.
More on Microsoft Fabric:
Comprehensive Free Course for Your Team to Learn Microsoft Power BI, SQL, and Data Factory
Microsoft Fabric: A Brief Introduction
How to enable Microsoft Fabric; on Power BI Service| Start the Fabric (Preview) trial
Microsoft Fabric-What it is, Why Use, Who Should, When and How to use
Microsoft Fabric: Various Components| Microsoft Fabric Create Workspace, Lakehouse, Warehouse
Microsoft Fabric: How to load data in Lakehouse using Dataflow Gen2
Microsoft Fabric: How to load data in Warehouse using Dataflow Gen2
Microsoft Fabric: How to load data in Warehouse using Data Pipeline | End-to-End Scenario
Microsoft Fabric: How to load data in Lakehouse using Spark; Python/Notebook
Microsoft Fabric: Integrated Machine Learning- k-Means clustering
Microsoft Fabric: Create a Date table in Dataflow Gen2, and use in Lakehouse and Warehouse| Time Intelligence with Power BI
Microsoft Fabric: How to load data in Lakehouse using Data Pipeline | End-to-End Scenario
Microsoft Fabric: What are the options to load local files in Lakehouse
Microsoft Fabric: Shortcut with Fabric and for Azure Data Lake | File vs Table Shortcut
Microsoft Fabric: Import Azure SQL Data to Warehouse | Multiple tables using Pipeline
Microsoft Fabric: Incremental Data Load(ETL) for Warehouse using Dataflow Gen 2, SQL Procedure, Data Pipeline
Microsoft Fabric: How about incremental without Dataflow, Only using pipeline and SQL procedure for Microsoft Fabric Warehouse
Microsoft Fabric: Load local SQL server data in Lakehouse using on-premise Gateway and Dataflow Gen2
Microsoft Fabric: Dataflow Gen2: Native warehouse Destination- Power Query Transformation — Pivot, Unpivot, Transpose Data
Microsoft Fabric: Load Azure SQL Data to Lakehouse & Warehouse simultaneously with Dataflow Gen2
Microsoft Fabric: Load Snowflake DB Data to Lakehouse & Warehouse simultaneously with Dataflow Gen2
Microsoft Fabric: How to load local SQL server data in Warehouse using Python, Pandas, and sqlalchemy
Microsoft Fabric: How to append only Incremental data using Data Pipeline in Lakehouse
Microsoft Fabric: Load incremental data from the local SQL server to Warehouse using on-premise Python
Introduction to Microsoft Fabric in Hindi | What it is, Why to Use, Who Should, When and How to use
Microsoft Fabric in Hindi Part 2: Create Workspace, Lakehouse, Warehouse
Microsoft Fabric in Hindi Part 3: How to load data in Lakehouse using Data Pipeline | End to End Scenario
Microsoft Fabric in Hindi Part 4: How to load data in Lakehouse using Dataflow Gen 2 | End to End Scenario
Microsoft Fabric in Hindi Part 5: How to load data in Warehouse using Dataflow Gen 2 | End to End Scenario
Microsoft Fabric in Hindi Part 6: How to load data in Warehouse using Data Pipeline | End to End Scenario
Microsoft Fabric Hindi part 7: How to load data in Lakehouse using Spark; Python using the notebook
Microsoft Fabric Hindi part 8: Do k-Means clustering in spark; analyze in Power BI | Machine Learning
To take your data journey to the next level, I’ll be providing more examples in my upcoming blogs and videos.
If you want to learn Power BI from the ground up — covering everything from basic data modeling to advanced DAX and real-world analytics — be sure to explore my 20-hour Masterclass, packed with 150+ essential topics to elevate your data analytics and business intelligence skills.
Complete Power BI in one Video — 11 Hours
Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.
In addition, I have over 500 videos on my YouTube channel that are related to Power BI, Tableau, and Incorta. With the help of these videos, you will learn hundreds of DAX and Power Query functions, in addition to hundreds of Power BI, use cases that will assist you in becoming an expert in Power BI. Make sure you subscribe, like, and share it with your friends.
Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
Learn SQL
