Forum Discussion
8000 Character limit SQL Endpoint For Lakehouse Fabric
Hi!
We saw this blog post.
https://blog.fabric.microsoft.com/en-us/blog/working-with-large-data-types-in-fabric-warehouse/
It mentioned expanding capabiltiy of fabric warehouse to handle varchar(max). And it said
The VARCHAR(MAX) type will open new scenarios by enabling you to store semi-structured data formatted as JSON without worrying about potential parsing errors due to truncation. JSON formatted text commonly exceeds 8KB in length, and with the new 1MB storage size, most JSON documents will fit into the warehouse columns. Also, the introduction of VARCHAR(MAX) and VARBINARY(MAX) opens the possibility to enhance SQL endpoints for Lakehouse and mirrored databases, as the string or binary data will no longer be truncated to 8KB.
Does this mean after this update we should be able to use the sql endpoint for fabric Lakehouse on fields > 8000 characters? Or is this a future update
14 Replies
Hi Anonymous ,
This update allows you to use Fabric Lakehouse's SQL endpoint on fields larger than 8,000 characters.The introduction of the VARCHAR(MAX) and VARBINARY(MAX) types allows for storing larger data, such as text in JSON format, without truncation issues.
However, there may still be some ongoing tweaks and improvements after the update, so please feel free to contact community support if you need help.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!Thanks @v-huijiey-msft for your response.
I am also exploring similar solution. I am not sure how do I save more than 8000 chars inside a table column? Are there VARCHAR(MAX) and VARBINARY(MAX) column types supported in Lakehouse table. Can you please point me to some reference / documentation please?
For now I have stored my large text (about 9000 chars) data in a String column but unable to pull the same using the Endpoint as the endpoint only gets me 8000 chars. Can you help on this please?
Thanks,
Prabhat
Assuming you have changed the table data type and reloaded the data, could it be that the export to excel is what is truncating the data? Have you tried to query the last occurance for the last word in the string?
had a similar issue, was using a dataflow in PowerApps, had an encoded image as Base64 ,too large and was truncated to 8000 caracters when i was using the SQL connector ; Lakehouse.contents connector doesn't work in power query inside dataflows so for power apps, had to use :
let // Lecture directe de la table Delta du Lakehouse via OneLake Source = AzureStorage.DataLake( "https://onelake.dfs.fabric.microsoft.com/" & "blablabla/" & // workspace GUID "blablabla/" & // lakehouse GUID "Tables/dbo/NAMEOFTHETABLE/", [HierarchicalNavigation = true] ), dbo_nameofthetable = DeltaLake.Table(Source), in dbo_nameofthetable
this connects direclty to the delta tables bypassing the SQL endpoint
Hi! I don't see the VARCHAR(max) in effect on the SQL endpoint - it still limits to VARCHAR(8000). Is there anything that one should do in order for it to take effect?
I am also experiencing the same problem. I did a complet new upload into a new lakehouse and everything over 8000 was cut of.
Does the extension only apply to warehouses or also lakehouses ?
Or is it still in the rollout phase?Hello, found this old post so I better write here then start a new.
I have columns with data of length > 8k in my lakehouse. Querying them in notebook using pyspark, there is no issues. However, when I query this using SQL endpoint, it truncates the data. Is this expected behavior or not?
I have tested this again casting the column in the delta table containing the json column as varchar(max) then attempting to query the JSON using cross apply. Error: JSON text is not properly formatted. Unexpected character '"' is found at position 7999
I can use lateral view explode to query the json column in a pyspark notebook. This is a significant limitation of fabric and means that json columns have to be exploded at the point of ingestion or exploded using notebooks. I will see if I am able to create a view of the data in the lakehouse json column in a Fabric data warehouse using the classic cross apply.
Tested same script in data warehouse querying lakehouse delta table:
with cpu_utilisation as(selectclient_id,status,[data.resultType],cast([data.result] as varchar(max)) json_datafromlakehouse_ukdp_opsramp.raw.cpu_utilisationWHEREclient_id = '764a11a3-37f4-4c07-b86f-6520e5a82b4b')SELECTdata_fields.*fromcpu_utilisationcross apply openjson(json_data)with(bios_code VARCHAR(50) '$.metric.bios_code',category VARCHAR(50) '$.metric.category')as data_fieldsSame error as lakehouse - JSON text is not properly formatted. Unexpected character '"' is found at position 7999I was hoping to deploy data model using visual studio database project. The ability to query json files was a highlight of Azure Synapse and despite the documentation I have tried porting my views which use open json and cross apply to fabric but they just hang. I have only 2 options 1. explode json at point of ingestion which is error prone as the schema could change. 2. Use materialised lake views created in pyspark.
