Azure Synapse中如何将工作区笔记本读取为字符串到另一笔记本？

我是资深Python使用者，刚接触Synapse。我的Synapse笔记本包含SQL查询，字段注释格式如下：

SELECT
    Field1    --| Some description of field 1
    Field2    --| Some description of field 2
    FieldN    --| Some description of field n
FROM SomeTable

我希望将该笔记本以纯文本字符串形式读取到另一笔记本中，使用PySpark的re包解析SQL块并输出字段与描述的表格。但尝试用本地Python的文件读取方式报错：

with open('Gold/MyNotebook') as f:
    contents = f.readlines()

报错信息：

File ~/cluster-env/env/lib/python3.10/site-packages/IPython/core/interactiveshell.py:282, in _modified_open(file, *args, **kwargs)
    275 if file in {0, 1, 2}:
    276     raise ValueError(
    277         f"IPython won't let you open fd={file} by default "
    278         "as it is likely to crash IPython. If you know what you are doing, "
    279         "you can use builtins' open."
    280     )
--> 282 return io_open(file, *args, **kwargs)

FileNotFoundError: [Errno 2] No such file or directory: 'Gold/MyNotebook'

核心问题：如何将Synapse工作区中的笔记本读取为字符串到另一笔记本中？

Synapse工作区中的笔记本并非存储在本地文件系统，因此不能用标准的open()函数读取。可以通过以下两种方式实现：

方法1：使用Azure Synapse Artifacts Python SDK #

先安装依赖包（如果未安装）：

%pip install azure-synapse-artifacts azure-identity

编写代码读取笔记本内容：

from azure.identity import DefaultAzureCredential
from azure.synapse.artifacts import ArtifactsClient

# 初始化客户端
credential = DefaultAzureCredential()
synapse_workspace_url = "https://<你的工作区名称>.dev.azuresynapse.net"
client = ArtifactsClient(endpoint=synapse_workspace_url, credential=credential)

# 读取笔记本内容，路径格式为"/Gold/MyNotebook"
notebook = client.notebook.get_notebook(notebook_name="/Gold/MyNotebook")

# 将笔记本的单元格内容拼接成纯文本字符串
content = ""
for cell in notebook.properties['nbformat']['cells']:
    content += ''.join(cell['source']) + "\n"

print(content)

方法2：使用Synapse REST API直接调用 #

利用requests库调用Synapse的REST API获取笔记本内容：

import requests
from azure.identity import DefaultAzureCredential

synapse_workspace_url = "https://<你的工作区名称>.dev.azuresynapse.net"
notebook_path = "/Gold/MyNotebook"
api_url = f"{synapse_workspace_url}/artifacts/v1.0/notebooks{notebook_path}"

# 获取访问令牌
credential = DefaultAzureCredential()
token = credential.get_token("https://dev.azuresynapse.net/.default").token

# 发送请求获取笔记本数据
headers = {"Authorization": f"Bearer {token}"}
response = requests.get(api_url, headers=headers)
response.raise_for_status()

notebook_data = response.json()

# 拼接单元格内容为纯文本
content = ""
for cell in notebook_data['properties']['nbformat']['cells']:
    content += ''.join(cell['source']) + "\n"

print(content)

后续解析SQL字段注释 #

拿到笔记本内容字符串后，即可用re包解析字段和注释：

import re
import pandas as pd

# 匹配字段和注释的正则表达式
pattern = re.compile(r'(\w+)\s+--\| (.+)')
matches = pattern.findall(content)

# 转换为DataFrame输出
df = pd.DataFrame(matches, columns=['字段名称', '字段描述'])
display(df)

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