Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
st_0999
Helper II
Helper II

TSQL vs Gen2 Dataflow

Hi, 

 

Will support be added soon to be able to write tables into a Lakehouse via T-SQL?

I know it's possible for a Warehouse, but not yet for a Lakehouse. 

I've found that some queries are just 10x faster with T-SQL vs a Gen2 Dataflow, so I've been migrating over into T-SQL Views where possible (saving them in the SQL Endpoint environment). Trouble with Views is that you can't apply any ordering. That's only possible with Stored Procedures, and Stored procedures are only available so far with Warehouses.

 

I'm not Spark SQL via a notebook since I don't like how you can't retain upper case naming on the Table names). 

 

Would be awesome to find out more!

3 REPLIES 3
miguel
Community Admin
Community Admin

Hey!

 

I will be moving this to the Synapse forum given that this goes out of the scope of what Dataflows can do. This might also be a good suggestion for the ideas site aka.ms/FabricIdeas

DennesTorres
Skilled Sharer
Skilled Sharer

Hi,

 

The explanation about what exactly you are doing is a bit confused. But let me try to add something.

I don't think this support will be added soon, it doesn't seems to be the purpose. But I'm not in position to answer this part.

 

You can use Spark SQL in a notebook to create tables in a lakehouse, I'm not sure if this helps.

 

About views, there is an old trick about views and order by: If you add a TOP 100 PERCENT in your query, you are able to add an order by to your view. I haven't tested in a lakehouse yet, but it should work.

 

Kind Regards,

 

Dennes

I'm not Spark SQL via a notebook since I don't like how you can't retain upper case naming on the Table names.

 

With T-SQL you have this control. That's the only reason I'm using them - T-SQL Views

 

Otherwise, would happily use Spark SQL. There I wouldn't even use Views, (not sure if Spark SQL even supports views), but would instead do a write back to the Lakehouse, with Ordering added too. 

 

Hopefully support for T-SQL to write back to the Lakehouse might be added soon...? Or the ability to write from a Spark SQL notebook, where I can make sure that the Table name retains it's Upper Casing.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Fabric Hack Slide Banner

Hack Together: The Microsoft Fabric AI Global Hack

Learn from experts, get hands-on experience, and win awesome prizes.