How to create view in Azure Synapse Serverless pool for a Lake Database

Question

I have a Lake Database created in Synapse Studio as a Workspace and I am trying to create a VIEW for the table. I right click on table and select "New SQL Script" and I get a pre generated script as ""

SELECT TOP (100) [AssetId],[AssetName],[AssetDescription],[AssetModelId] FROM [Utilities_66_Demo].[dbo].[Asset]

Now I would like to create a view just for testing. How can I do that. I tried the simple syntax as we use to do in Sql but it fails. Can someone please show how can I create a View here. Again its a Lake Database in Workspace tab in Synapse studio

Answer 1

Follow step 8 of this guide:

https://github.com/ArtisConsulting/customer-insights-azure-data-workshop/blob/main/lab03.md#step-4-enrich-the-customer-data

I placed more details under https://learn.microsoft.com/en-us/answers/questions/720180/creating-a-view-on-lake-databse-tables-in-synapse.html

Answer 2

@HimanshuSinha , @PRADEEPCHEEKATLA

I have similar question and the work around suggested for the above issue is to create external table.

Can you suggest if the work around works for the data that gets appended with new data in the lake database tables?

Do we need to create external tables everyday to capture new data?

Is there any other alternative?

This is very important for us as we are trying to export the data using synapse link from D365 into datalake. And all the D365 objects will be available as tables in the synapse Lake database. What is the best approach to enable that data for end users?

Appreciate your response

Answer 3

Hello @Ankit Kumar ,
Thanks for the question and using MS Q&A platform.

As we understand the ask here is can we create a view in lake database . Let me know if thats not accurate .
If you try to create a view on a lake database I was getting the below error .

Operation CREATE/ALTER VIEW is not allowed for a replicated database

You cannot create a view in a lake database and I could suggest you to please log a feedback at https://feedback.azure.com/d365community/forum/9b9ba8e4-0825-ec11-b6e6-000d3a4f07b8# . If you log a feature , I request you to please share that here for other users looking for same / similar asks .

I can suggest you a workaround ,The lake database is just reading all the data from a blob which is seating in a storage ( when you create a lake database you do that ) , so in place of looking for a view we can create a external table which points to the blob file and that should help out .

The below links should be helpful .Once you have the external tables you can join them to get more meaningful insights .

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop
https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/load-data-overview

Please do let me if you have any queries.
Thanks
Himanshu

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators