如何基于model.json在Synapse Serverless SQL池创建外部表

当前未绑定Synapse工作区、仅关联ADLS Gen2的Synapse Link导出的CDM数据，不需要重新配置Synapse Link，直接按以下步骤操作即可，引擎会自动读取model.json中的元数据完成列映射，不需要手动定义表结构。

1. 配置基础访问权限 #

• 进入存储ADLS Gen2的访问控制（IAM）面板，给Synapse工作区的系统分配托管标识授予存储Blob数据读者角色，作用域覆盖CDM数据所在的容器或目录层级。
• 确认当前登录Synapse Studio的账号拥有对应存储路径的Blob读取权限，以及Serverless SQL池的CREATE EXTERNAL DATA SOURCE、CREATE EXTERNAL TABLE等DDL操作权限。
• 检查存储账户的网络配置，确保Synapse工作区在受信任服务访问列表内，或出口IP已加入访问白名单。

2. 配置Serverless SQL池的访问凭据 #

在你用于存储外部表的Serverless SQL自定义数据库下执行以下语句（不要使用内置master库），如果已经配置过对应ADLS存储的数据库范围凭据可跳过本步：

-- 创建数据库主密钥用于加密凭据，替换为自定义强密码即可
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '你的自定义强密码';
GO

-- 创建基于托管标识的存储访问凭据
CREATE DATABASE SCOPED CREDENTIAL ADLS_Gen2_CDM_Cred
WITH IDENTITY = 'Managed Identity';
GO

3. 创建外部数据源与通用文件格式 #

3.1 创建指向CDM根目录的外部数据源 #

注意LOCATION需要填写到model.json文件所在的父目录层级，不要指向具体表的子文件夹。示例：如果model.json存放在存储账户crmadls的容器dataverse-export下的synapse-link/cdm-root/路径，LOCATION就填到该根路径：

CREATE EXTERNAL DATA SOURCE CDM_Root_DataSource
WITH (
    LOCATION = 'https://<你的ADLS存储账户名>.dfs.core.windows.net/<model.json所在容器名>/<model.json所在父目录路径>/',
    CREDENTIAL = ADLS_Gen2_CDM_Cred
);
GO

3.2 创建匹配Dataverse导出规则的CSV文件格式 #

Dataverse导出的CDM表为无表头、逗号分隔、UTF8编码的CSV，提前创建通用文件格式供所有外部表复用：

CREATE EXTERNAL FILE FORMAT CDM_CSV_NoHeader
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 1,
        ENCODING = 'UTF8'
    )
);
GO

4. 基于model.json自动解析元数据操作 #

4.1 验证元数据读取（无需提前建表） #

使用OPENROWSET直接传入model.json路径和实体名，引擎会自动读取元数据、映射无表头CSV的列，直接返回带正确列名和类型的查询结果，可先做验证：

-- 替换ENTITY_NAME为你要查询的Dataverse表名，需和model.json中定义的实体名完全一致
SELECT TOP 100 *
FROM OPENROWSET(
    BULK = 'model.json',
    DATA_SOURCE = 'CDM_Root_DataSource',
    FORMAT = 'cdm',
    ENTITY_NAME = 'account'
) AS account_query;

4.2 创建持久化外部表 #

验证查询正常后，可直接创建可复用的外部表，不需要手动编写列定义，引擎会自动从model.json同步所有列结构：

-- 以创建account外部表为例，LOCATION参数填写该表对应的文件夹名，一般和实体名一致
CREATE EXTERNAL TABLE account
WITH (
    LOCATION = 'account',
    DATA_SOURCE = CDM_Root_DataSource,
    FILE_FORMAT = CDM_CSV_NoHeader
)
AS
SELECT * FROM OPENROWSET(
    BULK = 'model.json',
    DATA_SOURCE = 'CDM_Root_DataSource',
    FORMAT = 'cdm',
    ENTITY_NAME = 'account'
) AS account_source;
GO

4.3 批量生成所有表的建表脚本 #

如果需要创建model.json中包含的所有实体表，可先查询所有实体名，自动生成批量建表语句：

-- 查询model.json中所有实体名称
SELECT entityName
FROM OPENROWSET(
    BULK = 'model.json',
    DATA_SOURCE = 'CDM_Root_DataSource',
    FORMAT = 'cdm'
) AS cdm_model
CROSS APPLY OPENJSON(cdm_model.entities) WITH (entityName VARCHAR(100) '$.name') AS entity_list;

常见问题排查 #

• 提示找不到model.json：检查外部数据源的LOCATION路径是否为model.json的父目录，BULK参数固定填model.json即可，不要额外拼接路径。
• 列数据错位：确认文件格式的FIRST_ROW参数设为1，不要设为2——导出的CSV本身没有表头行，设为2会跳过第一行有效数据导致映射错位。
• 权限报错：确认Synapse托管标识、当前登录账号的存储RBAC权限配置正确，存储账户防火墙没有拦截Synapse的访问请求。

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