Dynamically switch databases and create their views from deltalake

Question

I am a newbie to Synapse. I need your help with a task I've been working on for a few days. I want to create views in SA for my delta tables from Deltalake. I have the below folder structure in my gold container.

Gold:

1. P3138

   • Frames (delta folder)

   • Columns (delta folder)

   • Walls (delta folder)

2. P3139

   • Frames (delta folder)

   • Columns (delta folder)

   • Walls (delta folder)

   • Beams (delta folder)

In the above structure P3138, and P3139 are the Projects(id) in the Gold container, and the Frames, columns, and so on are delta tables with some data. Using the ADF pipeline I can create a database with the project_id if it does not already exist in the synapse serverless SQL.

"Now, the task is to create views for each delta table from the Delta Lake into its respective database in Synapse (which has already been created). I am unable to dynamically switch between databases and create views within them."

I attempted to use stored procedures and script activities coupled with For-Each and GetMetadata activities to achieve this. However, I encountered difficulties in creating views. The image below depicts the inner pipeline responsible for creating views, and I intend to pass the project IDs as parameters from the outer pipeline

Synapse pipeline:

I want something like this:

USE @projectID
GO

CREATE OR ALTER PROC CreatesQLserverlessView_gold @ViewName nvarchar(100)
AS
BEGIN

DECLARE @statement VARCHAR(MAX)

    SET @statement = N'CREATE OR ALTER VIEW ' + @ViewName + ' AS
    SELECT *
    FROM
        OPENROWSET (
        BULK ''https://kkdadventureworks.dfs.core.windows.net/gold/'+@projectID+'/'+ @ViewName + '/'',
        FORMAT = ''DELTA''
        )as [result]
    '
EXEC (@statement)

END
GO

Answer 1

I solved it.

After creating the database, I simultaneously create a stored procedure with the following code. The key aspect here is that I made the linked service itself dynamic for databases:

CREATE OR ALTER PROC CreatesQLserverlessView_gold 
@DatabaseName VARCHAR(100),
@ViewName VARCHAR(100)
AS
BEGIN
DECLARE @statement NVARCHAR(MAX)

-- Switch the database context
SET @statement = N'USE ' + @DatabaseName + ';'
EXEC (@statement)

-- Create or alter the view
SET @statement = N'CREATE OR ALTER VIEW ' + @ViewName + N' AS
SELECT *
FROM OPENROWSET (
    BULK ''https://keveebimdata.dfs.core.windows.net/gold/' + @DatabaseName + N'/' + @ViewName + '/'',
    FORMAT = ''DELTA''
) AS [result]'

EXEC (@statement)
END

Then, in my Synapse environment, I trigger the stored procedure for each table. Since each database already has the stored procedure within it, I simply need to switch the database at the linked service level. Subsequently, the stored procedure creates views for each delta table within their respective databases.