Synapse无服务器SQL中动态切换数据库创建Delta Lake视图的难题

• 环境：Azure Synapse 无服务器SQL池（SA）
• 存储结构：Gold容器下按项目ID（如P3138、P3139）划分文件夹，每个项目文件夹内包含多个Delta表文件夹（Frames、Columns、Walls等）
• 已完成操作：通过ADF管道自动为新项目ID创建对应的无服务器SQL数据库
• 需求：将每个项目下的Delta表，在对应项目ID的数据库中创建视图
• 遇到的问题：无法实现动态切换数据库并创建视图，尝试存储过程+ADF脚本/For-Each/GetMetadata活动组合时失败

1. 修正存储过程逻辑 #

原写法中USE @projectID无法直接通过变量实现数据库切换，需将数据库名嵌入到创建视图的动态SQL中。建议将存储过程放在公共数据库（如master或专用工具库），无需在每个项目库重复创建：

CREATE OR ALTER PROC dbo.CreatesQLserverlessView_gold 
    @ProjectID nvarchar(100),
    @ViewName nvarchar(100)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @statement NVARCHAR(MAX)

    -- 拼接带数据库名的视图创建语句，确保目标数据库已通过ADF创建
    SET @statement = N'CREATE OR ALTER VIEW ' + QUOTENAME(@ProjectID) + '.dbo.' + QUOTENAME(@ViewName) + ' AS
    SELECT *
    FROM
        OPENROWSET (
        BULK ''https://kkdadventureworks.dfs.core.windows.net/gold/'+@ProjectID+'/'+ @ViewName + '/'',
        FORMAT = ''DELTA''
        )as [result]'

    EXEC sp_executesql @statement
END
GO

说明：使用QUOTENAME()函数处理项目ID和视图名，避免特殊字符导致的语法错误；用sp_executesql替代EXEC()，提升动态SQL的安全性与可维护性。

2. ADF管道配置调整 #

步骤1：获取项目下的Delta表列表 #

• 添加Get Metadata活动，指向Gold容器内的目标项目ID文件夹，设置Field list为Child items，获取该项目下的所有Delta表文件夹。

步骤2：遍历所有Delta表 #

• 添加For Each活动，将Get Metadata输出的Child items作为迭代对象，遍历每个Delta表文件夹。

步骤3：调用存储过程创建视图 #

• 在For Each内部添加Script活动，连接到Synapse无服务器SQL池，执行以下脚本：

EXEC dbo.CreatesQLserverlessView_gold 
    @ProjectID = N'@{pipeline().parameters.ProjectID}',
    @ViewName = N'@{item().name}'

说明：@{pipeline().parameters.ProjectID}为外部管道传入的项目ID参数，@{item().name}为当前遍历到的Delta表文件夹名称。

关键注意事项 #

• 确保ADF服务主体拥有对应项目数据库的CREATE VIEW权限，以及Delta Lake存储路径的读取权限。
• 无服务器SQL池的数据库为逻辑容器，无需提前创建物理文件，你已通过ADF完成数据库创建，此环节无需额外操作。

内容的提问来源于stack exchange，提问作者Kuladeep