Python codes uitvoeren met SQL Server Machine Learning Services

Stel je wilt een Machine Learning model gebruiken en de uitkomsten in je SQL Server database toevoegen, hoe doe je dat? Hiervoor kun je geen T-SQL gebruiken, maar zul je een andere programmeertaal moeten gebruiken zoals Python of R. Deze blog focust zich op het combineren van Python en SQL Server.

Er zijn meerdere manieren dit te bereiken: je kunt vanuit een Python IDE werken met de pyodbc package en een connectie maken met je SQL Server of je kunt de Machine Learning Services van SQL Server gebruiken, waarmee je direct vanuit SQL Server Management Studio (SSMS) met Python kunt programmeren.

Het voordeel van Machine Learning Sevices van SQL Server is dat je geen data hoeft te verplaatsen, wat vooral in het geval van gevoelige data een groot voordeel is. Het is ook makkelijk om de uitkomsten van je model te integreren in je database. Deze blog gaat over de technische implementatie van SQL Server Machine Learning Services en de foutmeldingen die ik in dit proces tegenkwam en heb opgelost.

Installeer Machine Learning Services and Languages Extensions met Python
Als eerste zul je ervoor moeten zorgen dat de Machine Learning Services and Languages Extensions optie is geïnstalleerd met Python als taal.

Open de SQL Server Installation Center en klik op ‘New SQL Server stand-alone installation or add features to an existing installation’. Er wordt je dan gevraagd om de media folder te selecteren als installatie folder. In mijn geval kon ik deze folder: C:\SQLServerFull gebruiken. Zet vervolgens het vinkje met ‘include SQL Server updates’ uit. Op de pagina ‘Installation Type’ selecteer ‘Add featuers to an existing instance of SQL Server 2019’ en selecteer hierbij jouw installatie van SQL Server 2019. Vervolgens kom je op de pagina ‘Feature Selection’ en hier selecteer je Python in Machine Learning Services and Languages.

Om te controleren of de installatie geslaagd is, kun je checken of de volgende map beschikbaar is: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES.

Zorg ervoor dat je externe scripts kunt draaien in SQL Server
Om Python scripts in SQL Server te mogen draaien, moet je de optie ‘external scripts enabled’ aanzetten. Deze optie staat namelijk standaard uit. Je kunt deze optie aanzetten door de volgende query te draaien.
EXECUTE sp_configure 'external scripts enabled', 1 RECONFIGURE WITH OVERRIDE

Draai je Python query in SQL Server
Je hebt nu alle instellingen doorlopen om een Python script te kunnen draaien in SQL Server. Mocht dit niet meteen lukken en je een foutmelding krijgt, lees dan hieronder wat de veel voorkomende foutmeldingen en oplossingen zijn.
Een Python script in SQL Server stel je op de volgende manier op:

EXECUTE sp_execute_external_script

@language = N'Python'        
,@script = N'SQL_out = SQL_in.dropna();'
,@input_data_1 = N'SELECT Column1, Column2 FROM Table;'
,@input_data_1_name  = N'SQL_in'
,@output_data_1_name = N'SQL_out'

WITH RESULT SETS(Column1 NVARCHAR(255) NOT NULL, Column2 NVARCHAR(255) NOT NULL);

Zoals je ziet wordt er een stored procedure uitgevoerd die het externe script gaat uitvoeren. Hierin geef je eerst aan welke taal je gebruikt. Je geeft de query ook een input query mee. Let op, je kunt maar 1 input meegeven. Wil je meerdere tabellen combineren als input, dan zul je hiervoor eerst een SQL query moeten schrijven of een view maken die je als input kunt gebruiken. Geef je input en output dataset een naam die je in de Python code kunt gebruiken.

V­e­r­v­olgens kun je je Python script invoegen. Let erop dat als je aanhalingstekens in je script gebr­uikt, dat je dit nu 2 keer moet doen om deze te ‘escapen’. Bijvoorbeeld in de selectie van een kolom: df = df.drop(columns = [''Column'']). In je Python script hoef je geen data meer te importeren, je kunt meteen de naam ‘SQL_in’ gebruiken in dit voorbeeld. Je kunt de output ook wegschrijven in een tabel, in plaats van alleen te tonen. Hiervoor haal je de ‘WITH RESULT SETS’ statement op het einde van de query weg. En voeg je een ‘INSERT INTO’ statement toe aan het begin van de query.

Foutmeldingen in je script
De kans is groot dat als je voor de eerste keer een Python Script draait, hier een foutmelding uit voortkomt. Uitzoeken waar de melding vandaan komt en wat je hiervoor moet aanpassen, kan best veel tijd kosten.

ModuleNotFoundError: No module named '…'.
De Python installatie in SQL Server 2019 komt met een aantal standaard packages. Het kan voorkomen dat je een bepaalde package mist, of dat je een nieuwere versie van een package nodig hebt. Je kunt met het volgende script controleren welke packages en versies er geïnstalleerd staan:

EXECUTE sp_execute_external_script
@language = N'Python'
@script = N'import pkg_resources
import pandas
OutputDataSet = pandas.DataFrame(sorted([(i.key, i.version) for i in pkg_resources.working_set]))'
WITH result sets((Package NVARCHAR(128), Version NVARCHAR(128)));

Om een nieuwe package te installeren, open je de command promt als administrator. Zet de directory naar C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Scripts en voer vervolgens de volgende code uit:
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Scripts>pip.exe install "package name"

Als je een package wilt upgraden, voer je de volgende code uit: pip.exe install ‘package’ --upgrade. Wil je naar een specifieke versie upgraden of een specifieke versie installeren? Voeg dan het versienummer toe aan het commando: pip.exe install ‘package’==’versienummer’ voor installeren en : pip.exe install ‘package’==’versienummer’ --upgrade, voor upgraden.

EXECUTE statement failed because its WITH RESULT SET clause specified 1 result set(s), and the statement tried to send more result sets than this.
Als je een ‘WITH RESULT SETS’ statement toevoegt aan je query, dien je ervoor te zorgen dat je net zoveel kolommen specificeert in dit statement, als je Python code genereert. Krijg je deze foutmelding? Zorg er dan voor dat je de juiste kolommen specificeert in je ‘WITH RESULT SETS’ statement.

Invalid BXL stream error while running BxlServer: caught exception: Error communicating between BxlServer and client: 0x000000e9
Soms krijg je deze error. Het spreekt niet direct voor zich hoe deze error is op te lossen. Als je deze error krijgt, zal er bovenin waarschijnlijk ook iets van ‘Access denied’ bij staan. In mijn geval kreeg ik deze error, doordat het model dat ik op de dataset wilde toepassen, tekst uitprintte en ik geen ‘write access’ heb. In dit geval kon ik aan mijn model fit het stukje ‘disp = False’ toevoegen om de error te voorkomen. Per model zal het verschillen hoe je dit kunt voorkomen.

ImportError: cannot import name 'Panel' from 'pandas'
Als je de standaard versie van Pandas in SSMS wilt upgraden of de installatie van een andere package zorgt er uit zichzelf voor dat Pandas wordt geüpgraded, dan krijg je deze foutmelding te zien. Zoek vervolgens het volgende Python script op: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\PYTHON_SERVICES\Lib\site-packages evoscalepy\functions\RxSummary.py en open deze als administrator in bijvoorbeeld Notepad++. In dit bestand wordt ‘panel’ geïmporteerd, echter deze functie wordt helemaal niet meer in het script gebruikt. Verwijder het stukje code waar ‘panel’ wordt geïmporteerd, sla het script op en de foutmelding is verholpen.

Na het volgen van deze stappen, zul je in staat moeten zijn om Python scripts in SQL Server te draaien en de output in je database toe te voegen om vervolgens te gebruiken waar je dit wilt.

Nathalie van Diepen is Business Intelligence consultant bij Ensior.