Synapse专用SQL池：Python中Copy Into执行失败报409冲突错误

问题背景 #

通过Python的pyodbc连接Synapse执行数据加载逻辑时，触发以下错误：

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Not able to validate external location because The remote server returned an error: (409) Conflict. (105215) (SQLExecDirectW)')

即使移除插入语句，仅查询临时表仍会报错，但直接在Synapse Studio中运行生成的完整SQL代码完全正常。相关Python实现代码如下：

def load_adls_data(self, schema: str, table: str, environment: str, filepath: str, columns: list) -> str:
    if self.exists_schema(schema):
        if self.exists_table(schema, table):

            if environment.lower() == 'prod':
                schema = "lvl0"
            else:
                schema = f"{environment.lower()}_lvl0"

            temp_table = self.generate_temp_create_table(schema, table, columns)

            sql0 = """
IF OBJECT_ID('tempdb..#CopyDataFromADLS') IS NOT NULL
    BEGIN
        DROP TABLE #CopyDataFromADLS;
    END
"""

            sql1 = """
{}
COPY INTO #CopyDataFromADLS FROM 
'{}'
WITH  
( 
    FILE_TYPE = 'CSV',
    FIRSTROW = 1
)
INSERT INTO {}.{}
SELECT *, GETDATE(), '{}' from #CopyDataFromADLS

""".format(temp_table, filepath, schema, table, Path(filepath).name)
            print(sql1)

            conn = pyodbc.connect(self._synapse_cnx_str)
            conn.autocommit = True

            with conn.cursor() as db:
                db.execute(sql0)
                db.execute(sql1)

解决方案 #

1. 合并SQL语句并调整事务控制 #

当前代码将DROP临时表和COPY/INSERT操作分两次执行，且开启了自动提交，可能导致临时表作用域异常或事务状态冲突。建议将所有SQL逻辑合并为单批处理，并手动控制事务：

# 修改后的执行逻辑
conn = pyodbc.connect(self._synapse_cnx_str)
conn.autocommit = False  # 关闭自动提交

with conn.cursor() as db:
    # 合并DROP、创建临时表、COPY、INSERT为单条SQL
    combined_sql = sql0 + sql1
    db.execute(combined_sql)
    conn.commit()  # 手动提交事务

2. 启用MARS连接属性 #

在Synapse的ODBC连接字符串中添加MARS_Connection=Yes，支持多个活动结果集，避免跨语句执行时临时表作用域丢失：

# 更新连接字符串
self._synapse_cnx_str = f"{self._synapse_cnx_str};MARS_Connection=Yes"

3. 确保临时表与COPY INTO的原子性 #

确认generate_temp_create_table生成的临时表创建语句，与后续的COPY INTO、INSERT操作在同一个SQL批处理中，避免临时表在不同批处理中不可见。

4. 排查ADLS路径并发冲突 #

409错误本质是资源冲突，需确认目标ADLS文件路径没有并发写入操作：

• 确保文件已完全上传至ADLS，无未完成的写入进程
• 执行COPY INTO前可添加短暂延迟（如time.sleep(2)），等待文件状态稳定

5. 验证服务主体权限与令牌 #

如果使用服务主体访问ADLS，可能存在令牌缓存或权限同步问题：

• 重启Python进程，清除旧的令牌缓存
• 确认服务主体对ADLS路径拥有Storage Blob Data Contributor权限，且权限已同步生效

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