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.
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.
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;

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
6 people are following this question.