How to use SQL Code to write to Delta Tables in a Python Notebook
In this blog post I am going to explain how to use SQL Code to write to Delta Tables in a Microsoft Fabric Lakehouse.
If you are interested in more details on why I am using a Python Notebook please read this blog post: What are the advantages of using Python Notebook vs a Spark Notebook in Microsoft Fabric – FourMoo | Microsoft Fabric | Power BI
In my previous blog post I explained how to use SQL code to read from Delta tables in Microsoft Fabric which can be found here: How to use SQL Code to read from Delta Tables in a Python Notebook – FourMoo | Microsoft Fabric | Power BI
At the end of this blog post I will have a link to the Python notebook if you want to try this code out in your own environment.
Why use DuckDB to Write to Delta Tables
When I was exploring how to easily write to Delta Tables with a Python notebook, it took me a considerable amount of time to find out how to do this.
This is my learnings below, and from my point of view it makes it easy to write to a Lakehouse table, like what is done with a PySpark notebook.
NOTE: Please note the one thing to remember is that DuckDB is CASE Sensitive. Please make sure when using Lakehouse tables that you make sure they match exactly.
If there is an easier or faster way to get this done, please leave it in the comments section.
SQL Code for writing data to Lakehouse tables
As I mentioned previously in this blog post I have found that the easiest way to write the data is to use DuckDB using SQL to then write to a Lakehouse table in Microsoft Fabric.
In this example I am first getting some weather data from a public API and then storing it into a data frame.
This is to allow me to then write from the data frame to a Lakehouse table.
In the next code cell is now where I can write the data to a Lakehouse table, with specific items that need to be changed.
- This is the table name in the Lakehouse.
- NOTE: If the table does not exist it will automatically create it the first time it runs.
- This is the data frame which holds the data you want to be written.
- This is the table mode, which is where you can overwrite the existing table or append to the existing.
I then ran the above code cell.
As shown below, it successfully wrote the data on the Lakehouse table.
NOTE: I have found that at times it can take a minute or so for the table to show in Lakehouse View.
What I typically do is to go into Lakehouse View and click on the Refresh button, and only once the refresh completes do I then see the table in Lakehouse View.
As you can see below, when I look at the table in the Lakehouse View the columns “date” and “time” are set to type String.
How to write with DateTime to Lakehouse Table
In the section below I will show you how to ensure that Date or DateTime columns are valid data types before they are written to Lakehouse tables.
As shown below, what happens when there is a Date or DateTime column that is not in a valid format, it will be written to the Lakehouse table, and the data type will be set to “Complex”
What this means is if you try and query the table from the SQL End point, the column is not available to be queried.
As shown below in the SQL analytics endpoint, it has a warning.
Next, when I query the table, the column is not included in the query, or the table results as shown below.
To get a valid DateTime data type I need to convert it to a datetime as shown below.
What you will notice as highlighted above is that the columns “date” and “time” are in the Unix timestamp annotation. This will enable them to be written as a DateTime data type in the Lakehouse table.
Below is the updated code to write to the Lakehouse table
What I typically do is to go into Lakehouse View and click on the Refresh button, and only once the refresh completes do I then see the table in Lakehouse View.
I then went to the table, where I can validate the columns for date and time are of the date time data types as shown below.
Summary
In this blog post I have shown you some examples of how to use SQL Code to write from a data frame to a Lakehouse Delta table.
In the next blog post I will show you how to run a DAX query and write the output to a Lakehouse table.
You can find a link to the notebook I used above here: Fabric/Blog – Python – DuckDB – Writing to Lakehouse Table.ipynb at main · GilbertQue/Fabric
Thanks for reading, any comments or suggestions please let me know!
[…] Gilbert Quevauvilliers does a bit of writing: […]