Изменение кода R/Python для выполнения в экземплярах SQL Server (в базе данных)

Статья
05/30/2023

Применимо к: SQL Server 2016 (13.x) и более поздних Управляемый экземпляр SQL Azure

В этой статье показано, как изменить код R или Python для выполнения в качестве хранимой процедуры SQL Server, чтобы повысить производительность при обращении к данным SQL.

Перемещенный из локальной среды IDE или другой среды в SQL Server код R/Python обычно работает без необходимости вносить дальнейшие изменения. Это в частности справедливо для простого кода, например функции, принимающей некоторые входные данные и возвращающей значение. Кроме того, удобно переносить решения с пакетами RevoScaleR/revoscalepy, которые поддерживают разные контексты выполнения с минимальными изменениями. Обратите внимание, что MicrosoftML применяется к SQL Server 2016 (13.x), SQL Server 2017 (14.x) и SQL Server 2019 (15.x) и не отображается в SQL Server 2022 (16.x).

Тем не менее, в следующих случаях может потребоваться значительная переработка кода:

Вы используете библиотеки, которые обращаются к сети или которые нельзя установить в SQL Server.
В коде выполняются отдельные вызовы источников данных, расположенных вне среды SQL Server, таких как листы Excel, файлы в общих папках или другие базы данных.
Вам необходимо параметризировать хранимую процедуру и выполнить код в параметре @script sp_execute_external_script.
Исходное решение включает несколько шагов, которые в рабочей среде эффективнее выполнять по отдельности. В качестве примера можно сравнить подготовку данных или формирование признаков с обучением модели, оценкой и созданием отчетов.
Вы хотите оптимизировать производительность, изменяя библиотеки, используя параллельное выполнение или передавая нагрузки некоторых процессов в SQL Server.

Шаг 1. Планирование ресурсов и требований

Пакеты

Определите необходимые пакеты и убедитесь, что они работают в SQL Server.
Заранее установите пакеты в заданную по умолчанию библиотеку пакетов, которую используют службы машинного обучения. Пользовательские библиотеки не поддерживаются.

Источники данных

Если вы планируете внедрить код в процедуру sp_execute_external_script, определите первичный и вторичный источники данных.
- В качестве первичных источников выступают большие наборы данных, например обучающие данные модели или входные данные для прогнозирования. Спланируйте сопоставление самого большого набора данных с входным параметром процедуры sp_execute_external_script.
- В качестве вторичных источников обычно используются наборы меньшего размера, например списки факторов или дополнительные переменные группирования.
На данный момент процедура sp_execute_external_script поддерживает только один набор данных в качестве входного параметра. При необходимости вы можете добавить несколько скалярных или двоичных входов.

Вызовы хранимой процедуры, которым предшествует EXECUTE, нельзя использовать в качестве входных данных процедуры sp_execute_external_script. При этом вы можете использовать запросы, представления и любые другие допустимые инструкции SELECT.
Определите нужные выходные данные. Если код выполняется с помощью хранимой процедуры sp_execute_external_script, она сможет вернуть только один кадр данных. Но вы можете определить несколько наборов скалярных выходных данных, таких как графики и модели в двоичном формате, а также другие скалярные значения, получаемые на основе кода или параметров SQL.

Типы данных

Подробные сведения о сопоставлении типов данных между R/Python и SQL Server см. в следующих статьях:

Рассмотрите типы данных, используемые в коде R/Python, и сделайте следующее:

Составьте контрольный список возможных проблем с типами данных.

Службы машинного обучения SQL Server поддерживают все типы данных R/Python. Но SQL Server поддерживает больше типов данных, чем R или Python. Поэтому при перемещении данных SQL Server в код и из кода выполняются некоторые неявные преобразования типов данных. Кроме того, может потребоваться явным образом привести или преобразовать некоторые данные.

Значения NULL поддерживаются. Тем не менее, R использует структуру данных na для представления отсутствующих значений, подобных значениям NULL.
Рекомендуется убрать зависимости от данных, которые нельзя использовать в R. Например, типы данных rowid и GUID из SQL Server не поддерживаются в R, а их использование приводит к возникновению ошибок.

Шаг 2. Преобразование или переупаковка кода

Объем необходимых изменений в коде зависит от того, планируете ли вы отправлять код с удаленного клиента для выполнения в контексте вычислений SQL Server или хотите развернуть код в составе хранимой процедуры. Второй способ позволяет повысить производительность и безопасность данных, хотя он и накладывает некоторые дополнительные требования.

Чтобы избежать перемещения данных, по возможности определяйте в качестве первичных входных данных SQL-запрос.

При выполнении кода в хранимой процедуры вы можете передать несколько наборов скалярных входных данных. Для всех параметров, которые требуется использовать в выходных данных, добавьте ключевое слово OUTPUT.

Например, следующий скалярный вход @model_name содержит имя модели, которое также позже изменяется с помощью скрипта R и включается в выходные данные в отдельном столбце:

-- declare a local scalar variable which will be passed into the R script
DECLARE @local_model_name AS NVARCHAR (50) = 'DefaultModel';

-- The below defines an OUTPUT variable in the scope of the R script, called model_name
-- Syntactically, it is defined by using the @model_name name. Be aware that the sequence
-- of these parameters is very important. Mandatory parameters to sp_execute_external_script
-- must appear first, followed by the additional parameter definitions like @params, etc.
EXECUTE sp_execute_external_script @language = N'R', @script = N'
  model_name <- "Model name from R script"
  OutputDataSet <- data.frame(InputDataSet$c1, model_name)'
  , @input_data_1 = N'SELECT 1 AS c1'
  , @params = N'@model_name nvarchar(50) OUTPUT'
  , @model_name = @local_model_name OUTPUT;

-- optionally, examine the new value for the local variable:
SELECT @local_model_name;

Все переменные, передаваемые в качестве параметров хранимой процедуры sp_execute_external_script, должны быть сопоставлены с переменными в коде. По умолчанию переменные сопоставляются по имени. Все столбцы во входном наборе данных также должны быть сопоставлены с переменными в скрипте.

Предположим, что скрипт R содержит следующую формулу:
```
formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek
```
Если входной набор данных не содержит столбцы с именами ArrDelay, CRSDepTime, DayOfWeek, CRSDepHour и DayOfWeek, возникает ошибка.
В некоторых случаях необходимо заранее определить схему выходных данных для результатов.

Например, чтобы вставить данные в таблицу, необходимо использовать инструкцию WITH RESULT SET для определения схемы.

Схема выходных данных также требуется, если скрипт использует аргумент @parallel=1. Дело в том, что SQL Server может распределить запрос между несколькими параллельными процессами и собирать результаты в конце. Поэтому схему вывода необходимо подготовить до того, как могут быть созданы параллельные процессы.

В других случаях схему результатов можно опустить, указав параметр WITH RESULT SETS UNDEFINED. Эта инструкция возвращает набор данных из скрипта, не присваивая имена столбцам и не определяя типы данных SQL.
Рассмотрите возможность создания данных времени и отслеживания с использованием T-SQL вместо R/Python.

Например, для передачи системного времени или других сведений, используемых при аудите и хранении, можно добавить вызов T-SQL, который передается в результаты, чтобы не создавать аналогичные данные в скрипте.

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

Не рекомендуется записывать прогнозы или промежуточные результаты в файл. Чтобы избежать перемещения данных, записывайте прогнозы в таблицу.

Выполняйте все запросы заранее и используйте планы запросов SQL Server для определения задач, которые могут выполняться параллельно.

Если входной запрос может выполняться параллельно, задайте параметр @parallel=1 как часть аргументов процедуры sp_execute_external_script.

Параллельная обработка с использованием этого параметра обычно поддерживается, если SQL Server может работать с секционированными таблицами или распределять запрос между несколькими процессами и выполнять статистическую обработку результатов в конце. Параллельная обработка с использованием этого флага обычно не поддерживается, если для обучения моделей применяются алгоритмы, требующие считывания всех данных, или если вам нужно создать агрегаты.
Определите, нет ли в коде действий, которые можно выполнять отдельно или более эффективно с помощью вызова отдельной хранимой процедуры. Например, вы можете повысить производительность, конструируя или извлекая признаки отдельно и сохраняя значения в таблице.
Найдите способы выполнения вычислений на основе наборов с использованием кода T-SQL, а не R/Python.

Например, это решение R демонстрирует, как пользовательские функции T-SQL и R могут выполнять одинаковую задачу по формированию признаков: Пошаговое руководство по обработке и анализу данных.
Вместе с разработчиком базы данных определите способы повышения производительности с помощью таких компонентов SQL Server, как таблицы, оптимизированные для памяти, или, если вы используете выпуск Enterprise, Resource Governor.
Если вы используете R, по возможности замените обычные функции R функциями RevoScaleR, которые поддерживают распределенное выполнение. Дополнительные сведения см. в статье Сравнение функций Base R и RevoScaleR.

Шаг 3. Подготовка к развертыванию

Обратитесь к администратору, чтобы установить и протестировать пакеты до развертывания кода.

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

Пользовательские библиотеки не поддерживаются, даже если вы используете хранимые процедуры или выполняете код R/Python в контексте вычислений SQL Server.

Упаковка кода R/Python в хранимую процедуру

Создайте определяемую пользователем функцию T-SQL, внедрив код с помощью инструкции sp-execute-external-script.
Если у вас более сложный код R, используйте пакет R sqlrutils, чтобы преобразовать код. С помощью этого пакета опытные пользователи R могут создавать эффективный код хранимых процедур. Вам нужно переписать код R как единую функцию с четко определенными входными и выходными данными, а затем с помощью пакета sqlrutils создать входные и выходные данные в правильном формате. Пакет sqlrutils создает полный код хранимой процедуры и также может зарегистрировать ее в базе данных.

Дополнительные сведения и примеры см. в статье, посвященной пакету sqlrutils (SQL).

Интеграция с другими рабочими процессами

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

Работая в специализированной среде разработки, вы можете извлекать данные на компьютер и выполнять их итеративный анализ, а также записывать и отображать результаты. Но при переносе кода в SQL Server большую часть этого процесса можно упростить или делегировать другим средствам SQL Server.
Используйте безопасные стратегии асинхронной визуализации.

Пользователи SQL Server часто не могут получить доступ к файлам на сервере, а клиентские средства SQL обычно не поддерживают графические устройства R/Python. Если в вашем решении создаются диаграммы и другие графические элементы, рекомендуется экспортировать их в виде двоичных данных и записывать или сохранять в таблицу.
Упакуйте функции прогнозирования и оценки в хранимые процедуры, что позволит приложениям напрямую обращаться к ним.

Дальнейшие действия

Примеры развертывания решения R и Python в SQL Server можно найти в следующих руководствах:

Учебники по R

SQL-разработка на языке R для специалистов по анализу данных