Azure Data Factory查询报错：未命名列/表需添加别名
2026-6-15
问题描述
在Azure Data Factory（ADF）复制活动中使用以下动态查询：
@concat('SELECT *, LOWER(CONVERT(VARCHAR(64), HASHBYTES(''SHA2_256'',(SELECT * FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)), 2)) AS signature FROM [', pipeline().parameters.Domain, '].[', pipeline().parameters.TableName, ']')
执行时触发报错：
操作目标Copy Table to Lake失败：错误发生在源端。'Type=Microsoft.Data.SqlClient.SqlException,Message=必须指定要选择的表。
使用FOR JSON子句时，无法将无名称或别名的列表达式和数据源格式化为JSON文本。请为未命名的列或表添加别名。,Source=Framework Microsoft SqlClient Data Provider,'
但对应的SQL Server原生语句可正常运行，生成包含signature字段的输出：
SELECT T.*, LOWER(CONVERT(VARCHAR(64), HASHBYTES('SHA2_256',(SELECT T.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)), 2)) AS signature FROM Data.Country AS T;
样本数据集
========================================================================================================================================================================================================================== | CountryName | CountryISO2 | CountryISO3 | SalesRegion | CountryFlag | FlagFileName | FlagFileType | ========================================================================================================================================================================================================================== | Belgium | BE | 10 | EMEA | null | null | a | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Germany | DE | deletedupd | EMEA | null | null | c | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Italy | IT | ITA | EMEA | null | null | d | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Spain | ES | Updated | EMEA | null | null | e | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | United Kingdom | GB | GBR | EMEA | null | null | f | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | United States | US | USA | North America | null | null | g | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Scotland | SC | SCO | Britanny | null | null | z | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Kenya | KY | KEN | Africa | null | null | h | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Finland | FL | FIN | Finny | null | null | i | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
期望输出
========================================================================================================================================================================================================================================= | CountryName | CountryISO2 | CountryISO3 | SalesRegion | CountryFlag | FlagFileName | FlagFileType | signature | ========================================================================================================================================================================================================================================= | Belgium | BE | 10 | EMEA | null | null | a |a8a765cbe18b0956eda8d0cccf77| | | | | | | | |8e9aae302a21b397d2a1c88b61ff| | | | | | | | | 0b680bc6 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Germany | DE | deletedupd | EMEA | null | null | c |1232b1bd91d14a87ed830f770d74| | | | | | | | |cd8cabb871535c4c2b7ff5bcb873| | | | | | | | | fa80d851 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Italy | IT | ITA | EMEA | null | null | d |584cf66de2f4af9eb4dbfebefea8| | | | | | | | |08b1b4e6a35787fcac1061de88cf| | | | | | | | | b79856df | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Spain | ES | Updated | EMEA | null | null | e |9147f93453c0c35324e104b1d1ca| | | | | | | | |5750991de30cfda14c2e9ea8b916| | | | | | | | | c53d71a5 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | United Kingdom | GB | GBR | EMEA | null | null | f |76ac23d2a4ee9778791a4cb6f244| | | | | | | | |13e4e0523f65e233b691c548bdb7| | | | | | | | | 70bf0613 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | United States | US | USA | North America | null | null | g |e1c65ae69af6fdf75e0222ce6dba| | | | | | | | |e312298bf765eb95f3acf1bf2603| | | | | | | | | 22fc161b | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Scotland | SC | SCO | Britanny | null | null | z |075fba3109e831d879df282fcadb| | | | | | | | |36b370d28a62c75df2f71b9ee889| | | | | | | | | 1c335a5c | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Kenya | KY | KEN | Africa | null | null | h |222e9d81e47954d14e4c5a91b066| | | | | | | | |788ac06ce5ea2c80cf75ec00e3cf| | | | | | | | | 28e7b138 | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Finland | FL | FIN | Finny | null | null | i |87558fedd73139b13b7a969fe2ee| | | | | | | | |544e731bab3e4607e4732a03ab08| | | | | | | | | ea96fbe8 | -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
解决方案
问题根源：ADF生成的动态SQL中，嵌套的SELECT * FOR JSON语句未给主表指定别名，而SQL Server要求使用FOR JSON时，数据源必须有明确别名。
修改后的动态查询需为主表添加别名，并在嵌套查询中引用该别名：
@concat('SELECT T.*, LOWER(CONVERT(VARCHAR(64), HASHBYTES(''SHA2_256'',(SELECT T.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES)), 2)) AS signature FROM [', pipeline().parameters.Domain, '].[', pipeline().parameters.TableName, '] AS T')
修改后生成的SQL结构与本地测试的正常语句一致，通过AS T给主表指定别名，嵌套查询中的T.*可正确识别数据源，从而解决FOR JSON的别名报错问题。
内容的提问来源于stack exchange，提问作者Patterson
相关产品推荐
相关产品推荐
