question

ShatamjeevDewan-8468 avatar image
0 Votes"
ShatamjeevDewan-8468 asked SamaraSoucy-MSFT answered

Creating Complex Views in Synapse Serverless

Hi ,

Can we create complex sql views with joins on top of multiple external tables pointing to a data file Azure Data lake storage.

Also Is there anyway to automate pause and resume of Synapse dedicated sql pool based on some schedule.

Thanks.

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SamaraSoucy-MSFT avatar image
0 Votes"
SamaraSoucy-MSFT answered

Can we create complex sql views with joins on top of multiple external tables pointing to a data file Azure Data lake storage?

You should be able to create a view on any SQL statement that is valid in Synapse serverless pools. Just to test this out I ran the following script and got the expected results. Test.csv just has a single column with the integers 1-5, but a more complex file would work as well:

 DROP VIEW IF EXISTS testView;
 GO
    
 CREATE VIEW testView AS
 SELECT * 
 FROM OPENROWSET(
         BULK '/test.csv',
         DATA_SOURCE = '<datasource>',
         FORMAT = 'CSV', 
         FIELDTERMINATOR =',', 
         ROWTERMINATOR = '\n'
     )
     WITH (
     [number] SMALLINT
 ) AS [r]
 FULL OUTER JOIN
 (SELECT 7 AS number2) s ON number = number2
 FULL OUTER JOIN
 (SELECT * 
 FROM OPENROWSET(
         BULK '/test.csv',
         DATA_SOURCE = '<datasource>',
         FORMAT = 'CSV', 
         FIELDTERMINATOR =',', 
         ROWTERMINATOR = '\n'
     )
     WITH (
     [number3] SMALLINT
 ) AS [r]) t ON number = number3;

104022-2021-06-09-21-21-03-analyticssynapsews-azure-synap.png

Is there anyway to automate pause and resume of Synapse dedicated sql pool based on some schedule?

Currently there isn't a built-in feature for this for dedicated pools, but people have written guides on how to do this a few different ways by calling the Azure API's or the CLI.

Most popular is with a Synapse Pipeline (also works in Data Factory): https://www.techtalkcorner.com/pause-and-resume-azure-synapse-analytics/#Trigger_pipeline_to_pause_and_resume_your_Azure_Synapse_Analytics

Azure Automation: https://www.sentryone.com/blog/briandavis/using-azure-automation-pause-sqldw

Azure Functions: https://dinu.blog/post/627186560247431168/azure-function-to-pause-resume-synapse-compute



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.