昨日、In-Database Python Analytics for SQL Developers の意訳 を書きましたが、書きながら思った、データベース内の Python スクリプト実行の覚書を。
Python をきちんと理解できていないので、間違っている箇所があるかもしれませんが…。
冒頭で紹介したドキュメントでは、以下のように記載されています。
IDE でソリューションを作成し、テストした後に、使い慣れた SQL Server Management Studio でストアドプロシージャを使用して、開発した Python のコードを SQL Server に展開することができます。
ML サービスのスクリプトについては sp_execute_external_script を使用して実行することになりますが、@script パラメーターには、Python または、R のスクリプトをそのまま記述する必要があり、一回で作成するのはかなり困難ではと思います。
そのため、データベース内で実行する Python スクリプトを作成する場合には、元になる Python のスクリプトを IDE を使用して作成し、一通り作成が完了したら、SQL Server 内で実行できるようにするのがよいのかと。
外部スクリプトで使用することができるデータ型については、Python Libraries and Data Types に記載されています。
sp_execute_external_script のヘルプには以下のように記載されています。
[ @input_data_1_name = ] ‘input_data_1_name‘
Specifies the name of the variable used to represent the query defined by @input_data_1. The data type of the variable in the external script depends on the language. In case of R, the input variable will be a data frame. In the case of Python, input must be tabular. input_data_1_name is sysname.
Default value is “InputDataSet”.
外部スクリプトを実行する際にクエリの実行結果を入力データとして渡すことができ、スクリプト内では「InputDataSet」として、データフレームとして認識が行われます。
そこで、最初のステップとしては、IDE を使用して以下のようなクエリを作成するとよいのかと。
import pickle import numpy import pandas import pyodbc from sklearn import metrics cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER={接続先サーバー};DATABASE={Python};UID={ユーザー ID};PWD={パスワード}') modelsql = "select model from nyc_taxi_models where name = 'SciKit_model'" cursor = cnxn.cursor() cursor.execute(modelsql) model = cursor.fetchall() lmodel2 = model[0][0] mod = pickle.loads(lmodel2) inputsql = "select TOP 100 tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) as direct_distance from nyctaxi_sample_testing where tipped=1" InputDataSet = pandas.read_sql(inputsql, cnxn) X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]] probArray = mod.predict_proba(X) probList = [] for i in range(len(probArray)): tmp = [(InputDataSet["tipped"])[i], (probArray[i])[1]] probList.append(tmp) print(pandas.DataFrame(data = probList, columns = ["tipped", "predictions"])) OutputDataSet = pandas.DataFrame(data = probList, columns = ["tipped", "predictions"])
このスクリプトは、Step 6: Operationalize the Model の「Scoring using the scikit-learn model」の内容をベースにしたものとなります。
ストアドプロシージャから実行した場合は、
- モデルの Python オブジェクトのバイナリ値
- モデルに与える SQL の実行結果
については、ストアドプロシージャのパラメーターとして設定をしますが、それらについては、暫定として、スクリプト内で設定を行っています。
InputDataSet については、データストリームとして生成をしておけば、ストアドプロシージャ化した場合にも、後続の処理は同一の書き方にできるのかと。
データベース内で実行する、Python のスクリプトでは、「InputDataSet」「OutputDataSet」が予約変数として用意されているようですが、こちらについてはどちらも「<class ‘pandas.core.frame.DataFrame’>」となっているようなので、入力と出力の結果をテーブルやクエリの実行結果として利用する場合には、この辺を意識しておけばよさそうですかね。
これで正常に動作することが確認できてから、モデルや、データストリームは、パラメーターとして与えられる前提で、ストアドプロシージャ化すればよいのかなと。
ストアドプロシージャのひな形は以下のような形でしょうか。
-- https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-execute-external-script-transact-sql DECLARE @query nvarchar(max) = N'select TOP 100 tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) as direct_distance from nyctaxi_sample_testing where tipped=1' DECLARE @model varbinary(max) = (select model from nyc_taxi_models where name = 'SciKit_model') EXECUTE sp_execute_external_script @language = N'Python', @input_data_1 = @query, @script = N' import pickle import numpy import pandas from sklearn import metrics mod = pickle.loads(lmodel2) X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]] probArray = mod.predict_proba(X) probList = [] for i in range(len(probArray)): tmp = [(InputDataSet["tipped"])[i], (probArray[i])[1]] probList.append(tmp) print(pandas.DataFrame(data = probList, columns = ["tipped", "predictions"])) OutputDataSet = pandas.DataFrame(data = probList, columns = ["tipped", "predictions"]) ', @params = N'@lmodel2 varbinary(max)', @lmodel2 = @model WITH RESULT SETS ((C1 int, C2 float))
Python の描画系のライブラリを使用して、ファイルに出力することも可能なようです。
今回はプロットを使用して保存しているのですが、「import matplotlib」の後に「matplotlib.use(“Agg”)」を実行し、ウィンドウを表示せずに描画を実行するようにしておかないと、ストアドプロシージャが終了しないところが注意点でしょうか。
Generate images without having a window appear
EXECUTE sp_execute_external_script @language = N'Python', @script = N' import matplotlib matplotlib.use("Agg") import matplotlib.pyplot as plt plt.plot( [1, 10] ) plt.savefig("Plot.png") # C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\ExtensibilityData の実行ユーザー配下に出力されるが、スクリプトの実行が完了すると削除される plt.savefig("C:/Output/Plot.png") # https://matplotlib.org/faq/howto_faq.html#generate-images-without-having-a-window-appear '
ファイルの出力ですが、ML サービスの「ユーザーアカウントプール」のユーザー (<インスタンス名01 ~ 20>) で実行されますので、出力先にこれらのユーザーアカウントがファイルを書き込める必要があります。
図形の保存時に、明示的にパスを指定しなかった場合、既定のインスタンスを使用している場合は「C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\ExtensibilityData」配下の、ユーザーアカウントのディレクトリに一時的に出力が行われているようです。
ただし、このディレクトリの出力内容については、スクリプトの実行が完了したタイミングで、削除が行われているようですので、きちんと保存した状態にしたい場合には、保存時にパスを指定しておくのがよさそうです。