Forum Discussion

JeffGray's avatar
JeffGray
Icon for Advocate I rankAdvocate I
2 years ago

Column added to Lakehouse table as string shows up as wrong type in SQL Endpoint and Power BI

I have a notebook that adds a column to Lakehouse table using:      spark.sql("alter table EKKO add columns (STATU string);")   Seems to work fine, and I can load data into the column  using a m...
data engineering
lakehouse
notebook
frithjof_v's avatar
frithjof_v
Icon for Community Champion rankCommunity Champion
2 years ago

Anonymous I think the documentation you are referring to, is about T-SQL limitations in Data Warehouse and/or SQL Analytics Endpoint.

 

However JeffGray seems to be using SQL inside PySpark in a Lakehouse notebook. From what I have heard earlier, that is a different topic than T-SQL for data warehouse and SQL Analytics Endpoint.

Is there any documentation about limitations for using SQL in Spark?

 

Here are some other posts which show that we have been able to add columns to Lakehouse tables:

 

https://youtu.be/2RuoHpNZbc4?si=IoUopjtCXYozEgh2

 

https://community.fabric.microsoft.com/t5/General-Discussion/SQL-ALTER-command/m-p/3800910/highlight/true#M5715

 

https://community.fabric.microsoft.com/t5/General-Discussion/Dropping-and-recreating-lakehouse-table/m-p/3835426/highlight/true#M6312

JeffGray's avatar
JeffGray
Icon for Advocate I rankAdvocate I
2 years ago

Yes, exactly... I have a theory that I will test tomorrow, that the SQL endpoint (and power bi) are typing based on a profile of the first written data (as happens with Power BI imports) rather than from metadata of the Lakehouse table.  In my case, the first several thousand values written into this column are all '9'.  I think the SQL endpoint may be typing this as numeric based on those values.  I'll try explicitly writing string data first tomorrow to see if I get a string datatype.