Create SQL views with Fabric lakehouse SQL endpoint from Fabric notebook

[yongshao]

Our ETL uses Fabric notebooks to extract/clean/transform big dataset into Fabric lakehouses

We want to create/update SQL Views with a lakehouse SQL endpoint. The  SQL views could be created manually via lakehouse SQL endpoint query console/panel. But we want to automate View creation/update by Fabric notebook

Spark.sql() could create temprary sql views but not persistent views with SQL endpoint

We try to use ODBC/PYDOBC to connect the SQL endpoint and issue the view creattion query, which seems not working either

Please help - how to create SQL views with Fabric lakehouse SQL endpoint from Fabric notebook?

[doxxx]

@yongshao have you found a decent solution for your problem?

We are experiencing similar issues. 
We want to create views in the T-sql end point of the lakehouse as well, to expose data to downstream users. 

But the views created in this T-sql endpoint are not in source control, which makes them impossible to deploy to the next environment. 
What would be the solution?

Another option might be to create views in a warehouse, using the lakehouse tables as reference objects, which leads to issues when deploying the warehouse, because the corresponding lakehouse object reference does not exist (yet). 

Currently, we don't find a suitable solution to have a view as the final layer exposed to end users, or to powerbi, which is decently source-controlled... 

Please give us some advice anyone who has a working solution...

[chetnachaudhari]

@yongshao We automate our sql views creation using 'script activity' under Data pipelines. 

Thanks,

Chetna

[yongshao]

Thanks for response

 with Solved: Re: Creating views from spark notebook into SQL an... - Microsoft Fabric Community, a pipeline with script to create a view does work

I wonder why pyodbc driver connection could only query the data but cannot create views from SQL endpoint

import pyodbc

server = "?"

database = "?"

username = “?”

pwd = "?"

connection_string = f"Driver={{ODBC Driver 18 for SQL Server}};Server={server};Database={database};Authentication=ActiveDirectoryPassword;UID={username};PWD={pwd};ConnectRetryCount=4"

conn = pyodbc.connect(connection_string)

rows  = conn.execute("select top(10) * from dbo.building")

for row in rows:

     print(row) # works fine

#following doesn't work

conn.execute("create view dbo.testView as select top(10) * from dbo.building")

[Anonymous]

Hi @yongshao ,

Thanks for using Fabric Community.

" Spark views won't show up in the SQL endpoint. It is the same behavior with Synapse Spark too. Views created in Synapse won't show up in Synapse serverless. "

The only option left is to create the views directly in the Lakehouse's SQL Analytics Endpoint.

Similar Threads -
Re: Can you read a Fabric lakehouse view from spar... - Microsoft Fabric Community
Solved: Re: Creating views from spark notebook into SQL an... - Microsoft Fabric Community

Hope this is helpful. Please do let me know incase of further queries.

[Anonymous]

Hi @yongshao ,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

Thanks

[Anonymous]

Hi @yongshao ,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

Thanks