Can we create views in Lakehouse's SQL Endpoint using Fabric Apis?

[senthilkannan]

I am looking for a way to create views with-in lakehouse's SQL Endpoint using Fabric Apis.

Do we have a way to do that today?

[v-huijiey-msft]

Hi @senthilkannan ,

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

Best Regards,
Yang
Community Support Team

If 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!

[senthilkannan]

@v-huijiey-msft The challenge with using the pysparks session to create a Lakehouse View is teh views doesn't show up in the UI -> see Creating views from spark notebook into SQL analytics endpoint? 

@frithjof_v I am trying to build utilities to deploy a fabric workspaces using FabricApis. The intention is to setup Automated CD process for the fabric to promote code that fits into our org's requirements.

Part of that is to figure out how to create Lakehouse views. I could get the connection string from the UI and I assumed ODBC would work. However there are no APIs to get the connection string of the Lakehouse's SQL analytics endpoint.

let me know if there are any further questions on this.

[frithjof_v]

Have you tried the Lakehouse API?

https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-api

It seems you can use Create to create a Lakehouse, and then Get Properties to get the SQL Analytics Endpoint connection string.

[v-huijiey-msft]

Hi @senthilkannan ,

Thanks for the reply from frithjof_v .

As an alternative, you can connect to the SQL endpoint corresponding to lakehouse in warehouse and then create a view to use it.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If 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!

[v-huijiey-msft]

Hi @senthilkannan ,

This is the table I have stored inside lakehouse.

Using PySpark, you can create views in Fabric Lakehouse and then view it inside the SQL endpoint.

from pyspark.sql import SparkSession

# create Spark session
spark = SparkSession.builder \
    .appName("Create View in Lakehouse") \
    .getOrCreate()

# load data and name column header
df = spark.read.format("csv").option("header", "true").load("Files/products.csv")

# select column and create view
df.select("Month").write.mode("overwrite").saveAsTable("test_view1")

print("create success!")

After running the above code successfully, go to the corresponding SQL endpoint in lakehouse and refresh it to view the view.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If 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!

[frithjof_v]

@v-huijiey-msft I think the code you provided creates a table, not view.

Also, I don't think you need to create the Spark session in the code, as sessions are managed by Fabric.

However I don't know the answer to the original question - how to create a view in SQL Analytics Endpoint via Fabric API.

@senthilkannan could you explain more about why you wish to create views by using Fabric API?

Perhaps you can use jdbc or pyodbc. I'm not experienced with either of them.

Or perhaps you can use the run pipeline API or run notebook API. These API's are currently only in preview status.

The SQL Analytics Endpoint has a SQL connection string so you can connect from client applications like SSMS.