SQL-Ex blog

Установка

Возможность выполнять код Python не предоставляется по умолчанию в SQL Server. Нужно выполнить пару шагов прежде, чем вы сможете выполнять код. Во-первых, вам потребуется установить Machine Learning Services (MLS) при установке SQL Server. Если вы не делали этого, то можете выполнить установку и выбрать добавление опции MLS. Проверьте, что вы выбрали опцию "Machine Learning Services (In-Database)", а затем выберите поддержку Python. При желании вы можете добавить также поддержку R.

После установки вы должны разрешить экземпляру выполнять скрипты. Мы можем сделать это, изменив конфигурацию. Запустите следующий код на вашем экземпляре (под администратором).

EXEC sp_configure 'external scripts enabled', 1;

RECONFIGURE;

GO

Это даст возможность выполнять скрипты с помощью службы Launchpad и хранимой процедуры sp_execute_external_script.

Ваша первая программа на Питоне

После того, как мы сконфигурировали SQL Server, можно начинать выполнение кода Python. Мы принимаем наш код как строковую переменную, поэтому мы не будем работать с файлами. Итак, мы объявляем переменную, присваиваем ей строковое значение, содержащее код, а затем выполняем его. При этом используется хранимая процедура sp_execute_external_script, которая принимает несколько параметров. Здесь мы будем использовать только два из них, а остальные рассмотрим позже:

• @language - язык кода в нашем скрипте


• @script - текст нашей программы

Для начала давайте создадим базу данных, которую мы можем использовать на экземпляре SQL Server 2017. Я сделаю это с помощью следующего кода:

CREATE DATABASE MLDemo

GO

USE MLDemo

GO

После этого давайте напишем скрипт. Я будут использовать следующий простой скрипт на Python.

for i in {x for x in range(10) if x % 2 == 1}:

    print(i)

Если выполнить это в Python REPL, то получим:



Теперь выполним это в SQL Server. Я помещу мой код в значение параметра скрипта. Т.е. я буду использовать такой код:

EXEC sys.sp_execute_external_script

 @language = N'Python',

 @script = N'for i in {x for x in range(10) if x % 2 == 1}:

     print(i)

 '

При выполнении получим такие результаты.



Ура, мы выполнили код Питона в SQL Server.

Передача данных

Выполнение скрипта без данных не очень полезно. Давайте посмотрим, как мы можем передать данные в скрипт. Процедура sp_execute_external_script включает параметр с именем @input_data_1, который предоставляет вход для нашего кода Python. Это - запрос, который мы передаем, вернее, результирующий набор выполнения этого запроса. Мы можем использовать имя для этого запроса с помощью другого параметра, @input_data_1_name, чтобы создать переменную, которую использует наш код Python.

Это несколько сбивает с толку, но это просто означает, что мы соединяем вместе код T-SQL с кодом Python. Давайте посмотрим, как это работает. Я напишу короткий запрос для получения простого скалярного значения. Я не буду указывать имя, поэтому мы получим доступ к нему внутри как InputDataSet. Для соответствующего вывода по умолчанию применяется OutputDataSet. Мы просто возвращаем вход, чтобы проверить, что код работает правильно.

exec sp_execute_external_script 

@language =N'Python',

@script=N'OutputDataSet = InputDataSet', 

@input_data_1 = N'SELECT n from (values (1), (2), (3)) a(n)'

Выполнив код, мы получим следующие результат:



Заметим, что у нас нет никаких метаданных для результатов, и мы просто передаем на выход входные данные. Это демонстрирует, как мы можем передавать данные в наш скрипт в виде запроса. Запрос должен иметь тип NVARCHAR, и рекомендуется определять запрос отдельно, как показано ниже:

DECLARE @InputQuery NVARCHAR(2000) = N'

SELECT n from (values (1), (2), (3)) a(n)

'

INSERT dbo.Result1

EXEC sp_execute_external_script 

@language =N'Python',

@script=N'OutputDataSet = InputDataSet', 

@input_data_1 = @InputQuery

Получение конкретных результатов

Столбцы выше не имеют имен, но мы можем изменить это, включив предложение WITH RESULT SETS в наш код. Это позволит описать результаты метаданными об имени столбца и его типе данных. При этом мы заключаем результирующий набор в скобки, как и столбцы, которые описываем, как мы сделали бы это в определении таблицы. У меня имеется один столбец, но я добавлю второй, чтобы это продемонстрировать.

В этом случае я получу на входе два столбца, и дам им новые имена на выходе.

DECLARE @InputQuery NVARCHAR(2000) = N'

SELECT n, o 

FROM (values (1,10), (2,20), (3,30)) a(n, o)

'

EXEC sp_execute_external_script 

@language =N'Python',

@script=N'OutputDataSet = InputDataSet', 

@input_data_1 = @InputQuery

WITH RESULT SETS

( (  INT NOT NULL,

[iplusTen] INT NULL)

)

Предложение WITH RESULT SETS является частью оператора EXEC(), и позволяет нам определить множественные результирующие наборы конкретных видов. Вот почему у нас имеется два набора скобок. Внешний набор скобок ограничивает само предложение WITH RESULT SETS. Внутренний набор используется для первого результирующего набора.

Получение результата на выходе - это хорошо, но иногда хочется где-нибудь сохранить данные, и использовать их в другом коде. Я могу сделать это с помощью INSERT .. EXEC. Чтобы продемонстрировать это, я создам таблицу для сохранения моих данных. В этом случае я буду использовать полученный выше результирующий двухстолбцовый набор.

CREATE TABLE TwoNumbers

(   LowNumber  INT

  , HighNumber NUMERIC(4, 1));

Теперь я воспользуюсь следующим кодом для вставки данных из моей процедуры в эту таблицу:

DECLARE @InputQuery NVARCHAR(2000) = N'

SELECT n, cast(o as float) 

 FROM (values (1,10.1), (2,20.2), (3,30.3), (4, 40.4)) a(n, o)

 WHERE n < 4

';

INSERT dbo.TwoNumbers 

EXEC sp_execute_external_script 

@language =N'Python',

@script=N'OutputDataSet = InputDataSet', 

@input_data_1 = @InputQuery

SELECT tn.LowNumber, tn.HighNumber FROM dbo.TwoNumbers AS tn;

Заметим, что мы не используем предложение WITH RESULT SETS с оператором INSERT. Это дает нам следующие результаты:



Существуют другие способы помещения результатов в таблицу, но этот кажется мне наиболее простым.

Использование пакетов Python

То, что мы часто хотим делать с данными - это обработка их с помощью функций Питона. Давайте рассмотрим простой пример. Я вызову функцию SUM() из пакета numpy. Для этого мне потребуется использовать ту же структуру кода, что и в Питоне, для получения этой функции.

DECLARE @InputQuery NVARCHAR(2000) = N'

SELECT n 

 FROM (values (1), (2), (3), (4)) a(n)

';

EXEC sp_execute_external_script 

@language =N'Python',

@script=N'

import numpy as np

import pandas as pd

OutputDataSet = pd.DataFrame(np.sum(InputDataSet))', 

@input_data_1 = @InputQuery

В этом случае нам необходимы два пакета: numpy и pandas. numpy дает нам функцию sum, которая работает с фреймом данных (dataframe). pandas преобразует скалярный результат обратно в dataframe. Если не использовать эти пакеты, мы получим ошибки.

Вы можете использовать любой их этих пакетов, которые распространяются с Anaconda 3.5.2 Python в SQL Server 2017, или можете добавить свои собственные. Но об этом в другой статье.

Заключение

Это краткое введение в выполнение кода внутри SQL Server (в версиях, начиная с SQL Server 2017). Мы проверили основные возможности и использование процедуры sp_execute_external_script, которая будет проксировать вызов среды python на экземпляре хоста. Мы кратко рассмотрели некоторые варианты входных данных и поработали с результатами.

Существует множество способов работы с кодом Python, и еще больше тех, которые следует учитывать при выполнении кода внутри служб машинного обучения.