SQL Server ile Machine Learning(ML) ve PBI Uygulaması
Bu makalede SQL Server’in ML servisini kullanarak bir ML modeli geliştireceğiz. Sonrasında bu modelin tahminlerini bir PBI raporu ile göstereceğiz.
Kurulum için Abdullah Kise Hoca’mızın makalesini inceleyebilirsiniz. Yine buradaki makale serisinde de SQL Server ile ML Örnegi anlatılmakta.
SQL Server ML servisi kurulumu tamamlandıktan sonra asagıdaki kod ile bir python modeli geliştirebiliriz. Bu Stored Procedure, örnek bir veri setini webden indirir. Üzerinde bir ML modeli geliştirir. Oluşan ML modelinin pickle nesnesini “@trained_model” parametresi ile çıktı olarak verir.
DROP PROCEDURE IF EXISTS spMaasModeli
GO
CREATE PROCEDURE spMaasModeli (@trained_model varbinary(max) OUTPUT) as
EXECUTE sp_execute_external_script @language = N'Python'
, @script = N'import numpy as np
import pandas as pd
import pickle
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import accuracy_score
from sklearn.model_selection import train_test_split
from sklearn import preprocessing
from sklearn.preprocessing import StandardScaler
df=pd.read_csv("https://datahub.io/machine-learning/adult/r/adult.csv")
df[df == "?"] = np.nan
#Impute missing values with mode
for col in ["workclass", "occupation", "native-country"]:
df[col].fillna(df[col].mode()[0], inplace=True)
#Split dataset
X = df.drop(["class"], axis=1)
y = df["class"]
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size = 0.3, random_state = 0)
#Encode categorical variables
categorical = ["workclass", "education", "marital-status", "occupation", "relationship", "race", "sex", "native-country"]
for feature in categorical:
label = preprocessing.LabelEncoder()
X_train[feature] = label.fit_transform(X_train[feature])
X_test[feature] = label.transform(X_test[feature])
#Feature Scaling
scaler = StandardScaler()
X_train = pd.DataFrame(scaler.fit_transform(X_train), columns = X.columns)
X_test = pd.DataFrame(scaler.transform(X_test), columns = X.columns)
#Logistic Regression model
LR = LogisticRegression()
modelObj = LR.fit(X_train, y_train)
trained_model = pickle.dumps(modelObj)
'
,@params = N'@trained_model varbinary(max) OUTPUT'
,@trained_model = @trained_model OUTPUT;Kod içinde geçen “https://datahub.io/machine-learning/adult/r/adult.csv” verisini download edip asagıdaki sekilde bir tabloya import ettim.
Tahmini yapılacak kayıtları rastgele oluşturabilmek için aşağıdaki şekilde yeni bir tablo oluşturdum.
INSERT INTO adult_csv_RandomData
SELECT TOP 20 *
FROM adult_csv
ORDER BY NEWID()
Aşagıdaki SQL kodu ile “spMaasModeli” procedure’ünü kullanarak modelimizi oluşturup; oluşan modeli (pickle nesnesi) bir tabloya yazabiliriz.
--modelin egitilmesi ve
--egitilen modelin pickle olarak tabloya insert edilmesi
DECLARE @model VARBINARY(MAX);
EXEC spMaasModeli @model OUTPUT;
INSERT INTO maas_models (name, model) VALUES('linear_model', @model);
Görüldüğü üzere modelimizi her eğittiğimizde oluşan modelin bir kopyasını saklayabiliriz. Hatta bu kod geliştirilerek modelin başarısına ilişkin metrikleri (accuracy, R2, …vb) de bu tabloda saklayabiliriz.
Şimdi oluşan modeli kullanarak tahminleme işlemini yapalım. Bunun için tahminleme yapacak olan stored procedure’ü aşağıdaki şekilde oluşturalım:
DROP PROCEDURE IF EXISTS spMaasTahmini;
GO
CREATE PROCEDURE spMaasTahmini
AS
BEGIN
DECLARE @lmodel2 varbinary(max) = (select top 1 model from maas_models order by id desc);
EXEC sp_execute_external_script
@language = N'Python',
@script = N'
import pickle
import numpy as np
import pandas as pd
from sklearn import metrics
from sklearn import preprocessing
from sklearn.preprocessing import StandardScaler
mod = pickle.loads(lmodel2)
df_id = InputDataSet[["id"]]
df = InputDataSet[["age","workclass","fnlwgt","education","education-num","marital-status","occupation","relationship","race","sex","capitalgain","capitalloss","hoursperweek","native-country","class"]]
df[df == "?"] = np.nan
#Impute missing values with mode
for col in ["workclass", "occupation", "native-country"]:
df[col].fillna(df[col].mode()[0], inplace=True)
#Impute missing values with mode
for col in ["workclass", "occupation", "native-country"]:
df[col].fillna(df[col].mode()[0], inplace=True)
#Split dataset
X_pred = df.drop(["class"], axis=1)
y = df["class"]
#Encode categorical variables
categorical = ["workclass", "education", "marital-status", "occupation", "relationship", "race", "sex", "native-country"]
for feature in categorical:
label = preprocessing.LabelEncoder()
X_pred[feature] = label.fit_transform(X_pred[feature])
#Feature Scaling
scaler = StandardScaler()
X_pred = pd.DataFrame(scaler.fit_transform(X_pred), columns = X_pred.columns)
maas_pred = mod.predict(X_pred)
prob_array = mod.predict_proba(X_pred)
prob_list = [item[1] for item in prob_array]
birlesik_df = pd.concat([df_id,pandas.DataFrame(data=maas_pred)],axis=1)
OutputDataSet = birlesik_df
'
,@input_data_1 = N'SELECT id,age
,workclass
,fnlwgt
,education
,[education-num]
,[marital-status]
,occupation
,relationship
,race
,sex
,capitalgain
,capitalloss
,hoursperweek
,[native-country]
,class
FROM adult_csv_RandomData
'
, @input_data_1_name = N'InputDataSet'
, @params = N'@lmodel2 varbinary(max)'
, @lmodel2 = @lmodel2
WITH RESULT SETS ((id int,Score varchar(50)));
END
GOYukarıdaki kodda kullanılacak ML modelini “DECLARE @lmodel2 varbinary(max)” satırında elde ediyor. Ve tahmin edilecek veri setini ise SP’ye parametre olarak “@input_data_1” sorgusu ile veriyoruz. Bu procedure’ü aşağıdaki şekilde çalıştırıp tahminleri görebiliriz:
Şimdi bu çıktıyı PBI raporunda kullanalım. PBI raporunda veri kaynağı olarak “exec spMaasTahmini” şeklinde stored procedure’ü çağırabiliriz. Ancak bu işlem import modda destekleniyor. DirectQuery modunda desteklenmiyor. Böylece “Score” tahminlerini raporda görebiliriz.
Sonuç:
Bir makine öğrenmesi modelini, gerçek hayatta kullanabilmek için bir çok bileşenin bir araya gelmesi gerekir. Örnek olarak;
i. Python ile model servisi
ii. Python ile tahminleme servisi
iii. Tahminleme servisinin kullanılabilmesi için yazılım ekranları (.net, java vb.)
iv. Modeller her eğitildiğinde oluşan her modelin geçmişini tutmak, bu modellere ilişkin metrikleri kaydetmek, metriklerde bir sınır aşıldığında uyarı vermek ve tüm bunları otomatik yapmak gerekmektedir.
SQL Server ML servisi ile tüm bu işlemleri SQL Server üzerinde basitçe yönetebilir ve raporlama araçları ile de bu tahminleri sunabiliriz.
Kaynaklar:
