关于将spaCy集成到SQL Server 2022 MLS的技术求助

方案可行性结论 #

完全可行，针对你遇到的三个问题，具体解决思路和代码如下：

问题1：spaCy模型目录与VARBINARY单Blob的适配 #

spaCy的spacy.load()确实需要目录结构，但可以把预训练/自定义的spaCy模型打包成ZIP文件，存储为VARBINARY，在MLS的Python脚本中临时解压到SQL允许的临时目录后加载。

步骤：

1. 本地把spaCy模型打包：比如你的模型在./en_core_web_sm目录，执行zip -r en_core_web_sm.zip en_core_web_sm/
2. 将ZIP文件存入SQL表：

CREATE TABLE ML_Models (
    ModelID INT PRIMARY KEY,
    ModelName VARCHAR(50),
    ModelBinary VARBINARY(MAX)
);

INSERT INTO ML_Models (ModelID, ModelName, ModelBinary)
SELECT 1, 'spaCy_en_sm', * FROM OPENROWSET(BULK 'C:/path/to/en_core_web_sm.zip', SINGLE_BLOB) AS Model;

3. 在MLS脚本中解压并加载：

import tempfile
import zipfile
import spacy
import pandas as pd

def load_spacy_model_from_blob(model_blob):
    # 创建临时目录
    with tempfile.TemporaryDirectory() as temp_dir:
        # 把Blob写入临时ZIP文件
        zip_path = f"{temp_dir}/model.zip"
        with open(zip_path, 'wb') as f:
            f.write(model_blob)
        # 解压模型
        with zipfile.ZipFile(zip_path, 'r') as zip_ref:
            zip_ref.extractall(temp_dir)
        # 加载模型（注意解压后的目录名要和原模型一致）
        nlp = spacy.load(f"{temp_dir}/en_core_web_sm")
        return nlp

问题2：spaCy对ONNX的支持 #

spaCy完全支持导出为ONNX格式，这种方式更适合MLS的单Blob存储，且推理性能更优。

导出ONNX模型（本地执行）：

import spacy
from spacy.cli import convert

# 加载本地模型
nlp = spacy.load("en_core_web_sm")
# 导出为ONNX，指定输出路径
convert("en_core_web_sm", "./en_core_web_sm.onnx", format="onnx")

然后把生成的.onnx文件存入ML_Models表，在MLS中用onnxruntime加载推理：

import onnxruntime as rt
import pandas as pd
import spacy

def run_onnx_textcat(model_blob, texts):
    # 临时保存ONNX模型
    with tempfile.NamedTemporaryFile(suffix='.onnx', delete=False) as temp_file:
        temp_file.write(model_blob)
        temp_path = temp_file.name
    
    # 加载ONNX会话
    sess = rt.InferenceSession(temp_path)
    input_name = sess.get_inputs()[0].name
    output_name = sess.get_outputs()[0].name
    
    # 处理文本（匹配spaCy导出ONNX时的输入格式）
    tokenizer = spacy.tokenizer.Tokenizer(spacy.load("en_core_web_sm").vocab)
    results = []
    for text in texts:
        doc = tokenizer(text)
        input_tensor = doc.tensor.reshape(1, -1)  # 适配模型输入维度
        pred = sess.run([output_name], {input_name: input_tensor})
        results.append({"text": text, "category": pred[0].argmax(axis=1)[0]})
    
    return pd.DataFrame(results)

问题3：SQL数据与spaCy的pandas传递 #

在MLS的sp_execute_external_script中，SQL输入数据会自动映射为Python中的InputDataSet（pandas DataFrame），你只需提取文本列，传给spaCy处理后，把结果存入OutputDataSet返回SQL。

完整MLS执行脚本示例（以ZIP模型为例）：

DECLARE @ModelBinary VARBINARY(MAX);
SELECT @ModelBinary = ModelBinary FROM ML_Models WHERE ModelID = 1;

EXEC sp_execute_external_script
    @language = N'Python',
    @script = N'
import tempfile
import zipfile
import spacy
import pandas as pd

def load_spacy_model_from_blob(model_blob):
    with tempfile.TemporaryDirectory() as temp_dir:
        zip_path = f"{temp_dir}/model.zip"
        with open(zip_path, ''wb'') as f:
            f.write(model_blob)
        with zipfile.ZipFile(zip_path, ''r'') as zip_ref:
            zip_ref.extractall(temp_dir)
        nlp = spacy.load(f"{temp_dir}/en_core_web_sm")
        return nlp

# 加载模型
nlp = load_spacy_model_from_blob(ModelBinary)

# 从InputDataSet获取SQL中的文本数据
texts = InputDataSet["text_column"].tolist()

# 执行文本分类
results = []
for text in texts:
    doc = nlp(text)
    # 替换为你的textcat模型分类逻辑，示例取置信度最高的分类
    top_cat = max(doc.cats, key=doc.cats.get)
    results.append({"original_text": text, "predicted_category": top_cat, "confidence": doc.cats[top_cat]})

# 输出结果到SQL
OutputDataSet = pd.DataFrame(results)
',
    @input_data_1 = N'SELECT text_column FROM YourTextTable;',
    @input_data_1_name = N'InputDataSet',
    @params = N'@ModelBinary VARBINARY(MAX)',
    @ModelBinary = @ModelBinary
WITH RESULT SETS ((
    original_text NVARCHAR(MAX),
    predicted_category VARCHAR(50),
    confidence FLOAT
));

注意事项 #

• 确保SQL Server MLS的Python环境已安装spacy、onnxruntime（如果用ONNX）等依赖，可通过MLS对应的Python环境执行pip install命令安装。
• 临时目录权限：SQL Server运行账户需要有临时目录的读写权限，tempfile默认路径一般已授权。
• 模型选型：优先用轻量化spaCy模型（如en_core_web_sm）或自定义训练的小型模型，避免大模型带来的存储和加载性能问题。

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