Azure Synapse Python执行SQL脚本遇AttributeError反序列化错误

我编写了Python代码，旨在Azure Synapse Analytics上运行SQL脚本，相关代码如下：

from azure.identity import DefaultAzureCredential
from azure.synapse.artifacts import ArtifactsClient
from azure.mgmt.synapse import SynapseManagementClient
from azure.synapse.artifacts.models import SqlScript
import json

workspace_name = 'a'
subscription_id = 'b'
resource_group = 'c'
workspace_endpoint = f"https://xyz.dev.azuresynapse.net"

credential = DefaultAzureCredential()
sql_script_name = 'SQLScript1.sql'
sql_script_path = 'C:\\Users\\Downloads\\SQLScript1.sql'

with open(sql_script_path, 'r') as f:
    sql_script_content = f.read()

client = ArtifactsClient(
    credential = credential,
    subscription_id = subscription_id,
    endpoint = workspace_endpoint
)

sql_script = SqlScript(
    content = sql_script_content
)

sql_scripts_operations = client.sql_script

create_or_update_script_response = sql_scripts_operations.begin_create_or_update_sql_script(
    resource_group_name = resource_group,
    workspace_name = workspace_name,
    sql_script_name = sql_script_name,
    sql_script = sql_script
)

execute_script_response = sql_scripts_operations.begin_execute_sql_script(
    resource_group_name = resource_group,
    workspace_name = workspace_name,
    sql_script_name = sql_script_name,
    output_type = "DataTable"
)

print(execute_script_response.result())

该SQL脚本在Azure平台直接运行正常，但执行上述代码时出现反序列化错误，完整报错信息如下：

Traceback (most recent call last):
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1441, in _deserialize
found_value = key_extractor(attr, attr_desc, data)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1210, in rest_key_case_insensitive_extractor
return attribute_key_case_insensitive_extractor(key, None, working_data)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1242, in attribute_key_case_insensitive_extractor
return data.get(found_key)
AttributeError: 'str' object has no attribute 'get'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 695, in body
data = deserializer._deserialize(data_type, data)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1412, in _deserialize
setattr(data, attr, self._deserialize(local_type, value))
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1456, in _deserialize
raise_with_traceback(DeserializationError, msg, err)
File "C:\Python310\lib\site-packages\azure\core\exceptions.py", line 78, in raise_with_traceback
raise error.with_traceback(exc_traceback)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1441, in _deserialize
found_value = key_extractor(attr, attr_desc, data)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1210, in rest_key_case_insensitive_extractor
return attribute_key_case_insensitive_extractor(key, None, working_data)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1242, in attribute_key_case_insensitive_extractor
return data.get(found_key)
azure.core.exceptions.DeserializationError: (", AttributeError: 'str' object has no attribute 'get'", 'Unable to deserialize to object: type', AttributeError("'str' object has no attribute 'get'"))

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "C:\Users\Downloads\ASA.py", line 45, in 
create_or_update_script_response = sql_scripts_operations.begin_create_or_update_sql_script(
File "C:\Python310\lib\site-packages\azure\core\tracing\decorator.py", line 78, in wrapper_use_tracer
return func(*args, **kwargs)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts\operations_sql_script_operations.py", line 453, in begin_create_or_update_sql_script
raw_result = self._create_or_update_sql_script_initial(
File "C:\Python310\lib\site-packages\azure\synapse\artifacts\operations_sql_script_operations.py", line 297, in _create_or_update_sql_script_initial
_json = self._serialize.body(sql_script, "SqlScriptResource")
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 697, in body
raise_with_traceback(SerializationError, "Unable to build a model: " + str(err), err)
File "C:\Python310\lib\site-packages\azure\core\exceptions.py", line 78, in raise_with_traceback
raise error.with_traceback(exc_traceback)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 695, in body
data = deserializer._deserialize(data_type, data)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1412, in _deserialize
setattr(data, attr, self._deserialize(local_type, value))
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1456, in _deserialize
raise_with_traceback(DeserializationError, msg, err)
File "C:\Python310\lib\site-packages\azure\core\exceptions.py", line 78, in raise_with_traceback
raise error.with_traceback(exc_traceback)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1441, in _deserialize
found_value = key_extractor(attr, attr_desc, data)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1210, in rest_key_case_insensitive_extractor
return attribute_key_case_insensitive_extractor(key, None, working_data)
File "C:\Python310\lib\site-packages\azure\synapse\artifacts_serialization.py", line 1242, in attribute_key_case_insensitive_extractor
return data.get(found_key)
azure.core.exceptions.SerializationError: (', DeserializationError: (", AttributeError: 'str' object has no attribute 'get'", 'Unable to deserialize to object: type', AttributeError("'str' object has no attribute 'get'")))', 'Unable to build a model: (", AttributeError: 'str' object has no attribute 'get'", 'Unable to deserialize to object: type', AttributeError("'str' object has no attribute 'get'")))', DeserializationError(", AttributeError: 'str' object has no attribute 'get'", 'Unable to deserialize to object: type', AttributeError("'str' object has no attribute 'get'")))

解决方案 #

错误原因 #

报错核心是SqlScript对象的content参数格式不符合SDK要求。Azure Synapse Artifacts SDK要求content为字典结构，而非直接传入SQL字符串。字典需要包含query字段存储SQL脚本内容，还可指定metadata等属性。

修正后的代码 #

from azure.identity import DefaultAzureCredential
from azure.synapse.artifacts import ArtifactsClient
from azure.synapse.artifacts.models import SqlScript
import json

workspace_name = 'a'
subscription_id = 'b'
resource_group = 'c'
workspace_endpoint = f"https://xyz.dev.azuresynapse.net"

credential = DefaultAzureCredential()
sql_script_name = 'SQLScript1.sql'
sql_script_path = 'C:\\Users\\Downloads\\SQLScript1.sql'

with open(sql_script_path, 'r') as f:
    sql_script_content = f.read()

client = ArtifactsClient(
    credential=credential,
    subscription_id=subscription_id,
    endpoint=workspace_endpoint
)

# 关键修改：content传入包含query字段的字典
sql_script = SqlScript(
    content={
        "query": sql_script_content,
        "metadata": {
            "language": "sql"
        }
    }
)

sql_scripts_operations = client.sql_script

# 等待脚本创建/更新完成，避免资源未就绪
create_or_update_script_response = sql_scripts_operations.begin_create_or_update_sql_script(
    resource_group_name=resource_group,
    workspace_name=workspace_name,
    sql_script_name=sql_script_name,
    sql_script=sql_script
)
create_or_update_script_response.wait()

# 执行脚本并获取结果
execute_script_response = sql_scripts_operations.begin_execute_sql_script(
    resource_group_name=resource_group,
    workspace_name=workspace_name,
    sql_script_name=sql_script_name,
    output_type="DataTable"
)
result = execute_script_response.result()
print(result)

额外说明 #

1. 等待操作完成：添加create_or_update_script_response.wait()确保脚本在Synapse中创建/更新完成后再执行，避免因资源未就绪引发的错误。
2. content结构：content字典的query字段存储SQL语句，metadata指定语言为sql，符合SDK序列化要求，解决了原代码中字符串无法反序列化的问题。

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