Python внутри хранимой процедуры SQL Server: когда это действительно оправдано

Что это вообще такое

Machine Learning Services — отдельный компонент SQL Server, который появился в 2016-м с поддержкой R, а в 2017-м получил Python. Идея простая: разрешить T-SQL-движку передавать DataFrame во внешний Python-процесс, получать DataFrame обратно и обращаться с ним как с обычным result set.

Говорят о нём редко, и этому есть понятные причины:

• Установка тяжёлая — отдельный компонент, отдельная служба, отдельный процесс.
• Многие DBA не включают его по инерции: «зачем, если есть SSIS».
• Microsoft явно переключил фокус на Azure ML — новые фичи идут туда, on-premise поддерживается, но не развивается.
• В русскоязычных туториалах конкуренцию ему составляют SSIS+Python и внешние микросервисы — про них пишут чаще.

Но для нескольких задач это ровно то решение, которое экономит день работы и пачку лишних звеньев.

Как это работает под капотом

Главное архитектурное решение — Python НЕ запускается внутри процесса sqlservr.exe. Иначе первый же import numpy с C-extension мог бы уронить весь инстанс.

Вместо этого рядом работает отдельная служба — SQL Server Launchpad (mssqllaunchpad). Когда T-SQL встречает sp_execute_external_script, движок передаёт запрос и данные в Launchpad, тот поднимает изолированный Python-процесс в AppContainer-песочнице, скармливает ему DataFrame, ждёт результат и возвращает его обратно в T-SQL.

Пара следствий из этой архитектуры:

• Python не трогает треды sqlservr.exe. Долгая ML-обработка не вешает основной движок.
• У Python нет прямого доступа к сети, файловой системе и реестру. Никакого requests.get, никакого open('C:\\...') — песочница.
• Данные между процессами передаются через бинарный канал, похожий по логике на BCP — быстро, но каждый вызов = сериализация всего входа.
• Учётная запись Launchpad — отдельный service-account с урезанными правами. По умолчанию NT Service\MSSQLLaunchpad.

Минимальный рабочий пример

Сначала включаем возможность на сервере:

EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;

После этого нужен рестарт SQL Server — без него настройка висит в «configured», но не в «run». И очевидное: сам Machine Learning Services с Python должен быть установлен (отдельный чекбокс в инсталляторе SQL Server).

Самый простой «hello world» — сумма двух чисел, чтобы убедиться что Launchpad живой:

EXEC sp_execute_external_script
    @language = N'Python',
    @script = N'
import pandas as pd
OutputDataSet = pd.DataFrame({"result": [InputDataSet["a"][0] + InputDataSet["b"][0]]})
',
    @input_data_1 = N'SELECT 2 AS a, 3 AS b'
WITH RESULT SETS (([result] INT));

Если вернулась пятёрка — поздравляю, Python внутри SQL Server работает. Если вернулось «Unable to launch runtime for 'Python' script» или «external script execution is disabled» — ищите проблему в службе Launchpad или в sp_configure.

Передача данных: InputDataSet, OutputDataSet, @params

У процедуры три механизма передачи:

• @input_data_1 — один (и только один) SQL-запрос на вход. Превращается в pandas DataFrame, доступный внутри скрипта под именем InputDataSet. Имя можно переопределить через @input_data_1_name = N'MyData'.
• OutputDataSet — единственный DataFrame, который Python возвращает наружу. Обязательно pandas DataFrame, не список и не dict. Схему колонок описываем в WITH RESULT SETS — без этого SQL Server не поймёт, какие типы у колонок.
• @params — отдельный механизм для скалярных параметров. Удобно прокинуть одно-два значения без того, чтобы зашивать их в SELECT-запрос входных данных.

Рабочий пример со всем сразу — прогоняем номенклатуру через надбавку:

DECLARE @multiplier FLOAT = 1.15;

EXEC sp_execute_external_script
    @language = N'Python',
    @script = N'
OutputDataSet = InputDataSet.copy()
OutputDataSet["Цена_с_надбавкой"] = OutputDataSet["Цена"] * multiplier
',
    @input_data_1 = N'SELECT [Номенклатура], [Цена] FROM [dbo].[tr Номенклатура]',
    @params       = N'@multiplier FLOAT',
    @multiplier   = @multiplier
WITH RESULT SETS ((
    [Номенклатура]      NVARCHAR(500),
    [Цена]              DECIMAL(18,2),
    [Цена_с_надбавкой]  DECIMAL(18,2)
));

Две детали, на которых спотыкаются:

• В WITH RESULT SETS порядок и имена колонок должны совпасть с порядком колонок OutputDataSet. Если pandas переставил колонки после copy()+assign — SQL напишет «ошибка преобразования». Надёжнее в конце скрипта делать явный OutputDataSet = OutputDataSet[[...нужный порядок...]].
• Русские имена колонок работают, но внутри Python к ним обращение через InputDataSet["Номенклатура"] — как к обычной строке. Без магии, просто юникод.

Четыре сценария, где это окупается

Сценарий А. Scoring готовой ML-модели при загрузке факта

Классический кейс: на стороне ML-команды обучили модель (классификатор «рискованный заказ / нормальный», регрессор срока доставки, что угодно). Обучение идёт раз в месяц в Jupyter. Применять её надо каждый день, при загрузке новых строк в факт — скорить каждую запись и складывать предсказание рядом.

Стандартное решение — поднять отдельный Python-сервис с REST-эндпоинтом, ETL-пакет дёргает его батчами. Получается: ещё один сервер, ещё один контейнер, ещё один Dockerfile, ещё один мониторинг.

Альтернатива — хранить сериализованную модель как VARBINARY(MAX) в служебной таблице te Модели, а scoring делать одним вызовом sp_execute_external_script. Модель достаётся из BLOB'а, применяется к DataFrame, результат летит обратно колонкой факта. Никакого отдельного сервиса.

DECLARE @model_blob VARBINARY(MAX) =
    (SELECT TOP 1 [Модель]
     FROM [dbo].[te Модели]
     WHERE [Код] = 'risk_classifier_v4'
     ORDER BY [Дата] DESC);

EXEC sp_execute_external_script
    @language = N'Python',
    @script = N'
import pickle
model = pickle.loads(model_bytes)

features = ["сумма_заказа", "дней_с_первой_покупки",
            "среднее_время_доставки", "количество_возвратов"]

predictions = model.predict(InputDataSet[features])
OutputDataSet = InputDataSet.copy()
OutputDataSet["risk_class"] = predictions
',
    @input_data_1 = N'
        SELECT [ID_Заказа], [сумма_заказа], [дней_с_первой_покупки],
               [среднее_время_доставки], [количество_возвратов]
        FROM [dbo].[tf Заказы_to_score]
        WHERE [Дата_загрузки] = CAST(GETDATE() AS DATE)
    ',
    @params       = N'@model_bytes VARBINARY(MAX)',
    @model_bytes  = @model_blob
WITH RESULT SETS ((
    [ID_Заказа]                 BIGINT,
    [сумма_заказа]              DECIMAL(18,2),
    [дней_с_первой_покупки]     INT,
    [среднее_время_доставки]    DECIMAL(10,2),
    [количество_возвратов]      INT,
    [risk_class]                INT
));

Когда этого достаточно: модель влезает в память (условно до нескольких сотен мегабайт), batch scoring раз в день/час, никакой GPU не требуется. На реальных задачах — подавляющее большинство моделей классификации и регрессии на табличных данных под это подходит.

Сценарий Б. Fuzzy matching при нормализации номенклатуры

Больная точка почти любого DWH, который собирает данные из нескольких 1С-баз. Одна и та же позиция — в разных названиях:

• «Мука в/с 50 кг»
• «Мука В.С 50 кг»
• «Мука высший сорт 50 КГ»
• «Мука ВС 50 кг (фас)»

Руками маппить — месяц работы одного человека. T-SQL в лоб (LIKE, PATINDEX, SOUNDEX) покрывает процентов 30-40 — поверхностные совпадения по первым буквам. А pip-пакет rapidfuzz (или python-Levenshtein, если лень искать свежий) даёт 85-90% при разумных порогах.

Решение через sp_execute_external_script — отдельная процедура, которая берёт «сырой справочник» и «эталонный», прогоняет каждую строку сырого через rapidfuzz.process.extractOne и возвращает таблицу мэппинга с score'ом. Никаких промежуточных выгрузок в CSV, никакого отдельного Python-сервиса — всё внутри базы.

Один из проектов — справочник на 40 тысяч позиций после такой процедуры требовал ручной выверки только по 4 тысячам строк (10% с низким score'ом), остальное принималось автоматом.

Сценарий В. Декомпозиция временного ряда и anomaly detection

Производственная аналитика, себестоимость партий, расход ингредиентов. Задача: найти партии с аномальным расходом — где на тонну готового продукта ушло больше сырья, чем обычно. Простой порог (> среднее + 3σ) не работает, потому что есть сезонность (зимой линия остывает, летом — нет), тренд и выбросы, которые искажают среднее.

В T-SQL на оконных функциях это тоже реализуется, но скрипт получается на три экрана и читается только автором. В Python одной строкой:

from statsmodels.tsa.seasonal import seasonal_decompose

result = seasonal_decompose(
    InputDataSet["расход_на_тонну"],
    model="additive",
    period=7   # недельная сезонность
)
residual = result.resid.fillna(0)

# z-score от остатка, а не от исходного ряда
z = (residual - residual.mean()) / residual.std()

OutputDataSet = InputDataSet.copy()
OutputDataSet["residual"]   = residual.values
OutputDataSet["anomaly_z"]  = z.values
OutputDataSet["is_anomaly"] = (z.abs() > 3).astype(int)

Дальше T-SQL фильтрует по is_anomaly = 1, кладёт результат в витрину для дашборда — и технолог утром видит список партий, в которые стоит заглянуть.

Сценарий Г. Генерация PNG-картинок в SQL

Экзотический, но рабочий случай. Если нужно отдать картинку (heatmap, график, плашку со статусом) в отчёт SSRS или в мобильное приложение — можно сделать её прямо в процедуре. Matplotlib рисует в io.BytesIO, на выходе — VARBINARY(MAX):

import io, matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt

fig, ax = plt.subplots(figsize=(6, 3), dpi=120)
ax.plot(InputDataSet["Дата"], InputDataSet["Выручка"])
ax.set_title("Выручка по дням")

buf = io.BytesIO()
fig.savefig(buf, format="png", bbox_inches="tight")
plt.close(fig)

import pandas as pd
OutputDataSet = pd.DataFrame({"png": [buf.getvalue()]})

С WITH RESULT SETS ((png VARBINARY(MAX))) на выходе — готовый бинарник, который SSRS показывает как Image. Применяется редко, но когда применяется — экономит отдельный Report Service с REST-эндпоинтом.

Установка: что ставить и на какие грабли наступают

• Редакция. Machine Learning Services поддерживается в Enterprise, Standard, Developer и Express with Advanced Services. В обычном Express — нет.
• Инсталлятор. При установке или модификации SQL Server в списке фич выбираете «Machine Learning Services and Language Extensions» → ставите галочку Python. R и Java — по желанию, независимо.
• Включение. sp_configure 'external scripts enabled', 1 + рестарт. Без рестарта — конфиг висит в «pending».
• Launchpad. Отдельная служба MSSQLLaunchpad. Должна быть запущена под ограниченной учётной записью. Если служба падает при старте — первым делом смотрим лог в C:\Program Files\Microsoft SQL Server\MSSQL<версия>.INSTANCE\MSSQL\Log\ExtensibilityLog\.
• Дополнительные пакеты. Стандартный набор (pandas, numpy, scikit-learn, scipy) приезжает с установкой. Если нужен rapidfuzz или statsmodels — удобнее всего через пакет sqlmlutils: ставится на машину разработчика, подключается к SQL, заливает wheel прямо в базу, а оттуда Launchpad их раскатывает в sandbox.

• Кластеры. В Always On Availability Groups Launchpad нужен на каждом узле — иначе процедура отработает на primary, упадёт при failover на secondary.
• SQL Server 2022. Machine Learning Services поддерживается, но Microsoft в документации настойчиво рекомендует Azure ML. Для on-premise-стека это пока не критично — проблем с работоспособностью нет, новых фич тоже не будет.

Ограничения sandbox: что можно, что нельзя

Вывод из таблицы простой: процедура годится для работы с данными, которые УЖЕ в SQL. Для внешних источников (REST-API, Excel из шары, SOAP) это не инструмент — sandbox закрыт. Для таких задач существует пара «SSIS + Python через Execute Process Task», про которую у нас отдельная статья.

Производительность и подводные камни

• Накладные расходы на старт. Каждый вызов — это подъём Python-процесса Launchpad'ом, сериализация входных данных, десериализация выходных. На сотне строк это заметно: 300-500 мс накладные поверх полезной работы. На десятке тысяч — уже не видно в общем времени. Оправдано начиная с десятков тысяч строк — при меньших объёмах Т-SQL-версия обычно быстрее.
• Memory limit. Управляется через Resource Governor, параметр MAX_MEMORY_PERCENT на external resource pool. По умолчанию — 20% от памяти сервера. Если Python стабильно падает с MemoryError — первым делом сюда.
• Debug. Тяжёлый. Ошибки Python возвращаются через Launchpad, часто в обрезанном виде или без traceback'а. Полный стек — в ExtensibilityLog на диске сервера. В консольном SSMS возвращается только верхний уровень ошибки.
• Отладочный print. Работает, но идёт не в stdout клиентского соединения, а в messages — увидите во вкладке Messages в SSMS. Удобно для простого трейсинга.
• Типы колонок. Pandas может подсунуть int64 туда, где SQL ожидает INT — обычно съедает, но при DECIMAL c конкретной precision может упасть. Надёжнее в конце скрипта явно кастовать колонки под ожидаемый тип SQL.

Когда применять и когда не надо

Применять:

• Scoring ML-моделей построчно при загрузке факта.
• Операции, где T-SQL не умеет: Levenshtein/fuzzy matching, time series decomposition, кластеризация, простая ML-регрессия прямо в процедуре.
• Численные расчёты с pandas/numpy, которые в T-SQL получаются на три страницы CTE.
• Когда хочется убрать промежуточное ETL-звено — данные уже в SQL, и результат должен остаться в SQL.

Не применять:

• Если задачу тянет чистый T-SQL — всегда быстрее и без лишней службы.
• Если нужны HTTP-запросы к внешним API — Python в sandbox'е без сети. Это работа для SSIS + Python с sys.argv.
• Если модель большая и требует GPU — процедура работает только на CPU.
• Если DBA не даёт ставить Machine Learning Services (консервативный регламент, compliance-ограничения) — проще использовать внешний ML-сервис, чем биться за разрешение.
• Для рассылки отчётов, файловых операций, записи в сторонние системы — sandbox всё блокирует.

Как это дополняет паттерн SSIS + Python

Эти два подхода часто путают или считают конкурирующими, хотя на самом деле они про разные участки пайплайна:

В одном зрелом DWH обычно живут все три — и это нормально. Важно выбирать инструмент под задачу, а не натягивать один на все случаи.

Связанные материалы

• SSIS + Python: параметризация ETL-скрипта через sys.argv — второй Python-паттерн, для внешних источников
• DWH на Microsoft SQL Server — архитектура стека, куда встраивается ML Services
• Как построить корпоративное DWH поэтапно — где в общей картинке живут scoring-процедуры
• Наша методология BI-проекта — общий подход к выбору инструментов для DWH