如何在Azure Synapse无服务器SQL池用TSQL创建提取JSON键值的外部表

实现思路 #

原companyDetail外部表的employeeInfo列是非标准JSON格式（键名、字符串值无双引号），需先转换为标准JSON格式，再通过OPENJSON解析为键值对，最后使用CETAS（Create External Table As Select）语法创建目标外部表并落地数据。

前提准备 #

确保已配置好：

• 用于存储结果的外部数据源（可复用现有数据源或新建）
• 对应的文件格式（示例使用Parquet格式，可按需调整）

具体TSQL代码 #

1. （可选）创建结果存储用的外部数据源 #

若已有合适的数据源可跳过此步骤：

CREATE EXTERNAL DATA SOURCE employee_datasource
WITH (
    LOCATION = 'https://<你的存储账户名>.dfs.core.windows.net/<容器名>/employee_results/',
    TYPE = HADOOP
);

2. （可选）创建Parquet文件格式 #

若已有Parquet格式配置可跳过此步骤：

CREATE EXTERNAL FILE FORMAT parquet_format
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

3. 创建employee外部表 #

CREATE EXTERNAL TABLE employee
WITH (
    LOCATION = 'employee_results/',
    DATA_SOURCE = employee_datasource,
    FILE_FORMAT = parquet_format
)
AS
SELECT
    cd.companyName,
    ISNULL(j.[key], '') AS employeeKey,
    ISNULL(j.value, '') AS employeeValue
FROM companyDetail cd
OUTER APPLY (
    SELECT [key], value
    FROM OPENJSON(
        -- 将非标准JSON转换为标准格式，兼容空值场景
        CASE 
            WHEN cd.employeeInfo IS NULL OR cd.employeeInfo = '' THEN '{}'
            ELSE '{"' + REPLACE(REPLACE(TRIM(cd.employeeInfo), ': ', '": "'), ', ', '", "') + '"}'
        END
    )
) j;

代码说明 #

• JSON格式转换：通过REPLACE函数给非标准JSON的键名、字符串值添加双引号，转换为OPENJSON可识别的标准格式；空值/空字符串替换为{}避免解析报错。
• OUTER APPLY：保证employeeInfo为空时，仍能保留对应行（键值列为空）。
• CETAS：直接将查询结果写入指定存储路径，同时创建外部表指向该路径，适配无服务器SQL池的使用场景。

验证结果 #

执行以下查询查看最终数据：

SELECT * FROM employee;

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