Как выполнить код Python в Microsoft SQL Server на T-SQL

Приветствую всех посетителей сайта Info-Comp.ru! Сегодня мы рассмотрим возможность выполнения кода на языке Python в базе данных Microsoft SQL Server, Вы узнаете, что необходимо сделать, чтобы включить данную возможность, и как выполнить код Python с помощью обычной T-SQL инструкции.

Предварительные требования и подготовка SQL Server

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

Microsoft SQL Server не ниже 2017 версии

Поддержка языка Python появилась только в Microsoft SQL Server 2017, поэтому выполнить код на Python возможно только начиная с 2017 версии SQL сервера.

Должны быть установлены «Службы машинного обучения»

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

В 2017 версии данный компонент стал носить название «Службы машинного обучения», в котором, как было уже отмечено, добавилась поддержка языка Python.

Таким образом, чтобы выполнить код Python, в базе данных SQL Server обязательно должны быть установлены «Службы машинного обучения», которые и добавляют нам такую возможность.

Если компонент «Службы машинного обучения» не установлен, то запустить код Python в базе данных Microsoft SQL Server не получится.

Что такое «Службы машинного обучения» в SQL Server

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

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

Установка компонента «Службы машинного обучения»

Если у Вас не установлены «Службы машинного обучения», Вы их можете доустановить. Для этого запустите «Центр установки SQL Server», на вкладке «Установка» выберите пункт «Новая установка изолированного экземпляра SQL Server или добавление компонентов к существующей установке».

Затем на вкладке «Выбор компонентов» в разделе «Службы ядра СУБД» отметьте компонент «Службы машинного обучения (в базе данных)», а также какой именно язык Вы хотите использовать, в нашем случае Python.

После этого на странице «Согласие на установку» нажмите «Принять».

После окончания установки лучше перезагрузить компьютер.

Примечание! В данном варианте установки требуется доступ к интернету. Если интернета нет, то можно использовать автономный установщик, подробней в официальной документации.

На SQL Server необходимо разрешить использование внешних скриптов

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

Как разрешить использование внешних скриптов в SQL Server

Чтобы разрешить использование внешних скриптов на языке Python, в SQL Server необходимо включить системный параметр «external scripts enabled», это делается с помощью системной процедуры sp_configure.

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

После этого необходимо перезапустить службу SQL сервер.

Для проверки значения параметра можете выполнить следующую инструкцию.

   
   EXEC sp_configure 'external scripts enabled'

Если значение параметра run_value равно 1, значит использование внешних скриптов в SQL Server разрешено.

Заметка! XACT_ABORT в T-SQL – что это такое и как использовать.

Выполнение кода Python в Microsoft SQL Server на T-SQL

Выполняется код Python, а также код других языков, таких как R, в Microsoft SQL Server с помощью системной хранимой процедуры sp_execute_external_script.

sp_execute_external_script – это системная хранимая процедура, которая выполняет скрипт на поддерживаемых языках (R, Python), переданный в качестве входящего параметра.

Синтаксис sp_execute_external_script

   
   sp_execute_external_script
   @language = N'language',
   @script = N'script'
   [ , @input_data_1 = N'input_data_1' ]
   [ , @input_data_1_name = N'input_data_1_name' ]
   [ , @output_data_1_name = N'output_data_1_name' ]
   [ , @parallel = 0 | 1 ]
   [ , @params = N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]' ]
   [ , @parameter1 = 'value1' [ OUT | OUTPUT ] [ ,...n ] ]
   [ WITH RESULT SETS ( <result_sets_definition> )] ;

Где,

• @language – параметр, в котором указывается язык скрипта;
• @script – текст скрипта;
• @input_data_1 – параметр указывает входящие данные, используемые внешним скриптом в форме запроса T-SQL;
• @input_data_1_name – параметр указывает имя переменной, используемой для представления запроса, определенного @input_data_1;
• @output_data_1_name – параметр указывает имя переменной во внешнем скрипте, которая содержит данные, возвращаемые в SQL Server после выполнения вызова хранимой процедуры;
• @parallel – параметр включает параллельное выполнение скриптов R (значение 1). Значение по умолчанию для этого параметра равно 0 (без параллелизма);
• @params – объявление входящих параметров, используемых во внешнем скрипте;
• @parameter1 – список значений входящих параметров, используемых внешним скриптом;
• WITH RESULT SETS – по умолчанию результирующий набор, возвращаемый процедурой sp_execute_external_script, выводится с неименованными столбцами. Чтобы присвоить имя столбцам результирующего набора, необходимо использовать предложение WITH RESULT SETS для команды EXECUTE.

Заметка! Еще больше статей, посвященных языку T-SQL и Microsoft SQL Server, Вы можете найти в специально созданном мной сборнике статей для изучения Microsoft SQL Server.

Примеры выполнения кода Python в SQL Server

Давайте выполним несколько примеров.

Пример 1 – Выводим результат в стандартный вывод данных

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

   
   EXECUTE sp_execute_external_script
   @language = N'Python',
   @script = N'
   a = 1
   b = 2
   c = a+b
   print("Пример инструкции на Python")
   print("Результат =", c)';

Как видите, в параметре language мы указали, что будем выполнять код на Python, затем в параметре script написали сам код, в котором мы создаем несколько переменных, сложили их значения и сформировали сообщения командой print. В результате эти сообщения мы получаем в стандартном выводе сообщений, например, так, как если бы мы выполнили одноименную команду print на T-SQL.

Пример 2 – Выводим результат в табличном виде

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

Для этого мы будем использовать библиотеку Pandas и объект Series, который является одномерным массивом индексированных данных.

Результирующий набор данных мы будем формировать с помощью инструкции WITH RESULT SETS, которая поможет нам задать имя столбца и его тип данных.

   
   EXECUTE sp_execute_external_script @language = N'Python',
   @script = N'
   import pandas as pd
   TestVar = pandas.Series([100, 200, 300]);
   OutputDataSet = pd.DataFrame(TestVar);
   '
   , @input_data_1 = N''
   WITH RESULT SETS((Column1 INT NOT NULL));

Пример 3 – Передача данных в код Python

Здесь мы рассмотрим пример передачи в процедуру данных, например, табличных, полученных с помощью обычного запроса SELECT.

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

Затем мы с помощью параметра @input_data_1 передаем этот текст запроса в процедуру с помощью переменной (в принципе можно указать и сам текст запроса).

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

В данном случае мы просто передаем данные и ничего с ними не делаем, просто возвращаем эти данные назад, для этого параметру OutputDataSet мы присвоили данные из нашей переменной.

Итоговой набор данных мы сформировали, т.е. указали названия и тип данных столбцов, также с помощью инструкции WITH RESULT SETS.

   
   SELECT ProductId, ProductName, Price
   FROM Goods;

   GO

   DECLARE @Input_Query NVARCHAR(MAX) = N'SELECT ProductId,
                                                 CAST(ProductName AS NVARCHAR(100)),
                                                 CAST(Price AS FLOAT)
                                           FROM Goods';

   EXEC sp_execute_external_script @language = N'Python',
   @script = N'
   #Здесь мы можем обработать входящие данные
   #...
   OutputDataSet = Input_Data
   '
   , @input_data_1 = @Input_Query
   , @input_data_1_name = N'Input_Data'
   WITH RESULT SETS ((ProductId INT, ProductName VARCHAR(100), Price MONEY));

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

Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.

И на сегодня это все, надеюсь, материал был Вам полезен, пока!