Für einen Kunden durften wir kürzlich zwei spannende Projekte umsetzen, für welche sich die Nutzung der SQL Server Machine Learning Services anbot. Obwohl die beiden Projekte nichts mit Machine Learning zu tun haben, ist die Nutzung von Python in Kombination mit SQL eine sehr starke Paarung. Hinzu kommt, dass keine zusätzlichen Ressourcen notwendig sind. Aber von vorne…
Laufzeit-Versionen
Seit SQL Server 2017 sind die Machine Learning Services verfügbar. Sie ermöglichen das Ausführen von Python und R Skripten direkt auf dem jeweiligen SQL Server. Zuvor war unter R Services bereits das Ausführen von R Code möglich. Da die Laufzeit Updates bis vor kurzem an die SQL Server Updates geknüpft sind, muss genau auf die verwendete Python und R Version zur Entwicklung geschaut werden. Zum Beispiel hat SQL Server 2017 vor dem kumulativen Update 21 nur Python Version 3.5.2. Seit Oktober 2020 wird diese Version jedoch seitens Python nicht mehr unterstützt und somit auch nicht mit Bugfixes oder Sicherheitsupdates mehr versehen.
| SQL Server Version | Updatezyklus | Python Laufzeit Version | R Laufzeit Version |
| SQL Server 2016 | RTM – SP2 CU13 | – | 3.2.2 |
| SQL Server 2016 | Ab SP2 CU14 | – | 3.2.2 und 3.5.2 |
| SQL Server 2017 | RTM – CU21 | 3.5.2 | 3.3.3 |
| SQL Server 2017 | Ab CU22 | 3.5.2 und 3.7.2 | 3.3.3 und 3.5.2 |
| SQL Server 2019 | RTM – CU2 | 3.7.3 | 3.5.2 |
| SQL Server 2019 | Ab CU3 | Benutzerdefiniert* | Benutzerdefiniert* |
*Mit CU3 des SQL Server 2019 kann die Version frei installiert werden gemäss Dokumentation für Python und R
Mit dem CU3 auf SQL Server 2019 kommen die Spracherweiterungen für die Machine Learning Services, mit denen benutzerdefinierte Laufzeit Versionen installiert werden können. Zusätzlich kann jetzt auch Java Code ausgeführt werden.
Installation
Die SQL Server Machine Learning Services können beim Installieren des SQL Servers bzw. durch den Aufruf des Setup-Assistenten installiert werden. Dazu wird bei der Auswahl der Features die jeweilige Laufzeit unter Machine Learning Services und Spracherweiterungen ausgewählt. Die Installation ist hier bestens dokumentiert. Nachfolgend sind die wichtigsten Punkte zusammengefasst.
Zum Ausführen der Skripts muss dies via SQL Server Management Studio eingeschaltet werden. Die zwei folgenden Befehle sind dazu auszuführen:
sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH override;Zum Überprüfen ob dies erfolgreich war, kann der run_value (muss 1 sein) wie folgt überprüft werden:
EXECUTE sp_configure 'external scripts enabled';Der SQL Server-Launchpad-Dienst muss für die jeweilige SQL Server Instanz aktiviert sein. Dies kann über den SQL Server-Konfigurations-Manager überprüft werden.
Zum Testen ob die Installation erfolgreich war, kann das folgende Skript ausgeführt werden, es stellt ein MWE (minimal working example) für die verwendung von Python auf SQL Server dar.
EXEC sp_execute_external_script @language =N'Python',
@script=N'
OutputDataSet = InputDataSet;
'
,@input_data_1 =N'SELECT 1 AS hello'
WITH RESULT SETS (([hello] int not null));
GO
Python Packages Installieren
In Python werden oft zusätzliche Packages verwendet. Die meistbenutzen Packages kommen jedoch vorinstalliert mit den Machine Learning Services mit. Die installierten Packages können wie folgt abgefragt werden:
EXECUTE sp_execute_external_script @language = N'Python'
, @script = N'
import pkg_resources
import pandas
dists = [str(d) for d in pkg_resources.working_set]
OutputDataSet = pandas.DataFrame(dists)
'
WITH RESULT SETS(([Package] NVARCHAR(max)))
GO
Möchte man weitere Packages installieren, kann dies über die offizielle python-Package sqlmlutils geschehen. Da dies oft nicht funktioniert, hat Microsoft selbst Anleitungen zu Workarounds geschrieben. Mein persönlicher Favorit ist jedoch der Direktaufruf der pip executable mit der Windows Konsole (Administrator erforderlich!), mit der ganz einfach die benötigten Packages installiert werden können. Konkret kann zum Beispiel das Package Zeep, welches zum Aufrufen von SOAP Services dient, mit folgendem Befehl installiert werden:
"C:\Program Files\Microsoft SQL Server\MSSQL[Version].MSSQLSERVER\PYTHON_SERVICES\Scripts\pip.exe" install zeepAlternativ kann auch mit dem Python pip Package gearbeitet werden, mit demselben Prinzip.
"C:\Program Files\Microsoft SQL Server\MSSQL[Version].MSSQLSERVER\PYTHON_SERVICES\python.exe" -m pip install zeepStandardmässig taucht folgender Bug auf beim ersten Aufruf: pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available. Der Fehler kann einfach mit dem Kopieren zweier DLLs behoben werden. Die zwei DLLs libssl-1_1-x64.dll und libcrypto-1_1-x64.dll müssen von
C:\Program Files\Microsoft SQL Server\MSSSQL[Version].MSSQLSERVER\PYTHON_SERVICES\Library\bin
nach
C:\Program Files\Microsoft SQL Server\MSSSQL[Version].MSSQLSERVER\PYTHON_SERVICES\DLLs
kopiert werden. Für diesen und andere Bugs hat Microsoft Lösungen mit Anleitungen bereitgestellt.
Beispiele
Regex Suche mit Python
Szenario: Der Kunde hat Daten mit Freitext-Feldern und möchte diese gerne Filtern. Da die Möglichkeiten mit der SQL-Funktion LIKE nur beschränkt sind, wird die Suche in Python mit Regex-Patterns durchgeführt. Zu Demozwecken haben wir das Medical Transcriptions Dataset von Kaggle in eine Table abgelegt.
Das Ziel ist eine Regex-Suche im Feld transcriptions, welches Freitext zu medizinischen Behandlungen enthält. Um das Ganze etwas greifbarer zu machen gehen wir gemeinsam eine Beispielsuche durch. Angenommen wir wollen den Zusammenhang von auftretender Thrombose (Blutgerinnsel) mit dem Auftreten von Thrombose in der Familiengeschichte untersuchen für die Altersklasse 50-89.
Die Herausforderungen:
- Wir haben keine Altersangabe, diese muss im Text gefunden werden.
- Wir haben keine Aufteilung nach bestehender Erkrankung oder Familiengeschichte, die Tags HISTORY OF PRESENT ILLNESS, PAST MEDICAL HISTORY und FAMILY HISTORY definieren den darauf folgenden Text.
- Thrombosen treten durch verklumpen von Blutplättchen auf, wir wollen jedoch keine Blutwerte → Thrombozyten müssen ausgeschlossen werden.
- Für jede der drei Kategorien Bestehende Krankheit, Vergangene Krankheit und Krankheit in der Familiengeschichte soll eine Flag angezeigt werden, falls eine Thrombose auftritt/aufgetreten ist.
Zuerst definieren wir unsere Patterns im Json Format. Hier sind wir auch bereits an der ersten Einschränkung des SQL Server ML Services: Es kann nämlich nur eine Tabelle in das Skript eingelesen werden. Da wir das Transcription-Feld als Tabelle mitgeben, müssen wir die Regex-Patterns als Parameter mitgeben. Dank Json ist dies jedoch problemlos möglich.
Declare @patterns nvarchar(max) =
(select '.*(1[89]|[2-8][0-9])[-| ]years?[-| ]old.*'+ regex as regex, flag
from(
VALUES(
'.*HISTORY OF PRESENT ILLNESS.+?(thrombo|trombo|thormbo|throbbo)(?!cy|zy).+?(?=\.,).*', 'PRESENT_ILLNESS')
,('.*PAST MEDICAL HISTORY.+?(thrombo|trombo|thormbo|throbbo)(?!cy|zy).+?(?=\.,).*', 'PAST_HISTORY')
, ('.*FAMILY HISTORY.+?(thrombo|trombo|thormbo|throbbo)(?!cy|zy).+?(?=\.,).*', 'FAMILY_HISTORY')
) as suchbegriffe(regex, flag)
FOR JSON AUTO);
Das regex Stored Procedure wird mit den Parametern patterns und der Abfrage des Freitextes als String mitgegeben. Als Rückgabe des Python Skripts erhalten wir eine Tabelle mit der ID, dem Text, dem Regex-Ausdruck und der Flag der entsprechenden Kategorie zurück. Die Tabelle wird gruppiert nach ID und target_text ausgegeben.
DROP TABLE IF EXISTS #regex_result
CREATE TABLE #regex_result (ID int null, target_text nvarchar(max) null,regex nvar-char(max) null, flag nvarchar(100) null)
INSERT #regex_result (ID, target_text, regex, flag)
Exec regex @patterns, 'SELECT cast(ID as int) as ID, transcription as target_text FROM medical_transcriptions'
Select ID, STRING_AGG(flag, ', '), target_text
from #regex_result
group by ID, target_text
Nun ist es aber Zeit, die Magie hinter dem Ganzen zu entdecken. Das oben ausgeführte Stored Procedure regex sieht wie folgt aus:
ALTER PROCEDURE [dbo].[regex]
@patterns_json nvarchar(max),
@data_query nvarchar(max)
AS
EXEC sp_execute_external_script @language =N'Python',
@script=N'
# import libraries
import pandas as pd
import numpy as np
# drop missing
data = data[["ID","target_text"]]
data.dropna(inplace=True)
# convert pattern json
pattern_list = pd.read_json(patterns)
# if no flag entry create one
if ("flag" not in pattern_list.columns):
pattern_list["flag"] = 1
out = pd.DataFrame()
# loop over patterns and check for a match in data, set flag
for index, row in pattern_list.iterrows():
ind = data.target_text.str.match(row.regex, case=False)
if (np.dtype(ind) != bool):
ind = ind.str.len() > 0
out = out.append(data.loc[ind].assign(regex=row.regex, flag=row.flag))
out = out[["ID","target_text","regex","flag"]]
'
,@input_data_1 = @data_query
,@input_data_1_name = N'data'
,@output_data_1_name = N'out'
,@params = N'@patterns nvarchar(max)'
,@patterns = @patterns_json
WITH RESULT SETS ((id int not null, target_text nvarchar(max) null,regex nvarchar(max) null, flag nvarchar(100) null))
Die Parameter @patterns_json und @data_query werden dem Skript in einem vordefinierten Weg übergeben und können dann innerhalb des Python Codes als Variablen patterns und data verwendet werden. Als Rückgabewert wird das Pandas DataFrame out definiert. Die Regex Abfrage wird durch den Pandas Befehl Series.str.match() ausgeführt. Da wir vor allem auf die SQL ML Services fokussieren wollen, gehen wir nicht näher auf den Code ein.
Abfragen einer REST-API
Szenario: Der Kunde möchte eine API direkt vom SQL Server aus benützen und das Resultat in ein Tabelle schreiben. Obwohl es Wege gibt, um APIs direkt aus SQL abzufragen, haben wir uns auch hier für Python entschieden, da sich auch hier direkt eine Filterung der Daten anbietet. Auch hier werden wir ein konkretes Beispiel durcharbeiten. Ziel ist die automatisierte Abfrage der Kalender aller Mitarbeiter eines Unternehmens mithilfe der Microsoft Graph API. Eine Anzeige soll die Ferien, Frei und Abwesenheiten visualisieren.
Die Abfrage des Stored Procedures gestaltet sich hier einfacher; Einziger Parameter ist das Client Secret für die API-Abfrage.
DECLARE @cs nvarchar(255) = (SELECT * FROM [SQLML].[dbo].[creds])
INSERT INTO [MA_Anwesenheit] EXEC graphAPI @cs
Der Python Code für die API-Abfrage gestaltet sich jedoch etwas aufwändiger. Da das App ohne User Login funktionieren soll, wurde vorgängig im Azure Portal eine Anwendungsberechtigung eingerichtet. Die HTTP Authentifizierung erfolgt nach OAuth 2.0 mit einem Access Token, welches zuerst via Client ID/Secret abgefragt werden muss. Die Kalendereinträge werden danach nach den Schlagwörtern Ferien, Frei und Abwesend abgefragt und entsprechende Flags als Rückgabewert gesetzt.
ALTER PROCEDURE [dbo].[graphAPI]
@cs nvarchar(255)
AS
EXEC sp_execute_external_script @language =N'Python',
@script=N'
import requests
import pandas as pd
headers = {"Content-Type": "application/x-www-form-urlencoded"}
params = {"client_id": "------------------------------------",
"scope": "https://graph.microsoft.com/.default",
"client_secret": client_secret,
"grant_type": "client_credentials",
"response_type": "code"
}
tenant_id = "------------------------------------"
token_info = re-quests.post(f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token", head-ers=headers,
data=params).json()
token = token_info["access_token"]
headers = {"Authorization": "Bearer " + token,
"Content-Type": "application/json",
"Prefer": "outlook.timezone=\"W. Europe Standard Time\""}
base_url = "https://graph.microsoft.com/v1.0"
response = requests.get(f"{base_url}/users", headers=headers)
ids = []
names = []
for item in response.json()["value"]:
ids.append(item["id"])
names.append(item["displayName"])
df = pd.DataFrame({"ID": ids, "NAME": names, "FERIEN": False, "FREI": False, "ABWESEND": False})
params = {
"startDateTime": pd.to_datetime("today").strftime("%Y-%m-%dT00:00:00+01:00"),
"endDateTime": pd.to_datetime("today").strftime("%Y-%m-%dT23:59:59+01:00")
}
df.set_index("ID", inplace=True)
for user_id in df.index:
print(f"checking user: {df.loc[user_id]}")
calendar = requests.get(f"{base_url}/users/{user_id}/calendarView", head-ers=headers, params=params).json()
if "error" in calendar.keys():
continue
there_is_more = True
while there_is_more:
for event in calendar["value"]:
if "ferien" in event["subject"].lower():
df.loc[user_id, "FERIEN"] = True
elif "frei" in event["subject"].lower():
df.loc[user_id, "FREI"] = True
elif "abwesend" in event["subject"].lower():
df.loc[user_id, "ABWESEND"] = True
if "@odata.nextLink" in calendar.keys():
events = requests.get(events["@odata.nextLink"], headers=headers).json()
else:
there_is_more = False
df = df.reset_index()
'
,@output_data_1_name = N'df'
,@params = N'@client_secret nvarchar(255)'
,@client_secret = @cs
WITH RESULT SETS ((ID nvarchar(50) NOT NULL,
NAMES nvarchar(255) NULL,
FERIEN nvar-char(50) NOT NULL,
FREI nvarchar(50) NOT NULL,
ABWESEND nvarchar(50) NOT NULL))