@IT の Microsoft、SQL Server 2017の「Machine Learning Services」を使った「データサイエンス実践ガイド」を公開で記事が公開されていますが、SQL Server 2017 の ML サービスを使用した機械学習のチュートリアルが公開されています。
元の記事は、Data Science Walkthrough with SQL Server 2017 and Microsoft Machine Learning Services ですかね。
SQL Server 2017 の ML サービスの勉強がてら、In-Database Python Analytics for SQL Developers の意訳を。
SQL Server 2017 のドキュメントですので、そのうち公式に日本語訳されるかと。
Contents
- 1 In-Database Python Analytics for SQL Developers
- 2 概要
- 3 シナリオ
- 4 要件
- 5 Step 1: Download the Sample Data
- 6 データとスクリプトをダウンロードする
- 7 Step 2: Import Data to SQL Server using PowerShell
- 8 SQL オブジェクトとデータの作成
- 9 スクリプトの実行
- 10 Step 3: Explore and Visualize the Data
- 11 データの確認
- 12 タクシーの識別子
- 13 輸送および運賃記録
- 14 T-SQL で Python を使用してプロットを作成
- 15 プロットを varbinary データ型に格納する
- 16 Plot_Python ストアドプロシージャの作成
- 17 表示可能なグラフィックファイルを varbinary のデータから出力する
- 18 Step 4: Create Data Features using T-SQL
- 19 関数を定義
- 20 fnCalculateDistanceを使用して輸送距離を計算する
- 21 fnEngineerFeatures を使用して、特徴を保存する
- 22 Step 5: Train and Save a Model using T-SQL
- 23 サンプルデータをトレーニングセットとテストセットに分割する
- 24 scikit-learn を使用してロジスティック回帰モデルを構築
- 25 revoscalepy パッケージを使用して、ロジスティック回帰モデルを構築
- 26 Step 6: Operationalize the Model
- 27 scikit-learn モデルを使用したスコアリング
- 28 revoscalepy モデルを使用したスコアリング
- 29 SELECT クエリを使用したバッチによるスコアリング
- 30 scikit-learn モデルを使用して、個々の行にスコアを付ける
- 31 結論
In-Database Python Analytics for SQL Developers
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sqldev-in-database-python-for-sql-developers
このドキュメントは、Python を使用したストアドプロシージャによる機械学習を体験するためのハンズオンとなります。
R でも 同様のチュートリアル がありますが、本ドキュメントでは、SQL Server 2017 から利用可能となった Python を利用してます。
概要
エンドツーエンドでのソリューションの構築は、一般的には、データの取得 / データのクリーニング (クレンジング) / データの探索 / 機能のエンジニアリング / モデルのトレーニングとチューニング / 本番環境で利用するモデルの展開により構成されています。
コードの開発とテストを実施する環境については、Python ツールなどの専用の開発環境 (IDE) を使用するのが望ましいです。
利用される IDE としては、以下のようなものがあります。
- PyCharm
- よく使われるている IDE です。
- Spyder
- データサイエンス用ワークロードで Visual Studio 2017 をインストールすると含まれています。
- Visual Studio 用の Python 拡張機能
IDE でソリューションを作成し、テストした後に、使い慣れた SQL Server Management Studio でストアドプロシージャを使用して、開発した Python のコードを SQL Server に展開することができます。
本チュートリアルでは、ソリューションに必要な Python コードがすべて提供されている前提で、SQL Server を使用したソリューションの構築および、展開を実施することを主目的としています。
- Step 1 : サンプルデータのダウンロード
- サンプルデータセットと、チュートリアルに必要なデータ / スクリプトをローカルコンピューターにダウンロードします。
- Step 2 : PowerShell を使用して SQL Server にデータをインポート
- 指定した SQL Server インスタンス上にデータベースとテーブルを作成し、サンプルデータをインポートする PowerShell スクリプトを実行します。
- Step 3 : データの探索と可視化
- T-SQL ストアドプロシージャから、Python を呼び出して、基本的なデータの探索とデータの可視化を実行します。
- Step 4 : T-SQL を使用した新しいデータ機能
- カスタム SQL 関数を使用して、新しいデータ機能を作成します。
- Step 5 : T-SQL を使用したモデルのトレーニングと保存
- ストアドプロシージャから、Python を使用して、機械学習モデルを構築し保存します。
- Step 6 : モデルの運用
- データベースにモデルを保存したら、T-SQL を使用して、予測を行うためにモデルを呼び出します。
ノート
SQL Server Management Studio を使用して、Python のコードの作成 / テストを行うことは推奨しません。
これは、ストアドプロシージャに埋め込んだ Python のコードに問題がある場合、ストアドプロシージャに返される情報では、エラーの原因を理解するのに不十分なことが考えられるためです。
シナリオ
本チュートリアルでは、よく知られている NYC Taxi のデータセットを使用しています。
本演習を素早く、簡単に実施するために必要となるデータがサンプリングされたものとなっています。
このデータを使用して、時間 / 距離 / 送迎場所などの列に基づいて、特定の移動のヒントを予測するためのバイナリ分析モデルを作成します。
要件
本チュートリアルは、データベースやテーブルの作成 、テーブルへのデータインポート、SQL クエリの作成といった、基本的なデータベース操作が実施できるユーザーを対象としてます。
使用するすべての Python コードが提供されています。
SQL を使用することができるプログラマは、SQL Server Management Studio で、T-SQL を使用するか、提供されている PowerShell スクリプトを実行することで、チュートリアルを完了させることができます。
本演習を開始する前には、次の準備が行われている必要があります。
- Python を使用した ML サービスが有効となっている SQL Server 2017 (CTP 2.0 以降) のインスタンスがインストールされた環境
- 本演習で使用する SQL Server ログイン
- このログインは、データベースやその他に演習で必要となるオブジェクトの作成、データのインポート、データの検索、ストアドプロシージャを実行sるうための権限が必要となります。
Step 1: Download the Sample Data
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sqldev-py1-download-the-sample-data
このステップでは、サンプルデータセットとスクリプトのダウンロードを行います。
使用するデータと、スクリプトファイルは Github で共有されています。
今回は、PowerShell スクリプトを使用し、データとスクリプトファイルをローカルディレクトリにダウンロードします。
データとスクリプトをダウンロードする
- PowerShell のコマンドコンソールを起動します
ダウンロードファイルの保存先の作成、指定した保存先にファイルを書き込むために、管理者権限が必要となる場合は、管理者として実行オプションを使用して、PowerShell を起動します。 - 次の PowerShell コマンドを実行します。
必要に応じて、保存先ディレクトリの変数の値をローカルの適切なディレクトリ変更します。
デフォルトは「C:\TempPythonSQL」となります。
// スクリプトは原文を参照
ディレクトリが存在しない場合、PowerShell スクリプトによって作成されます。
エラーが発生した場合、Bypass 引数を使用して、現在のセッションに関して、PowerShell スクリプトの実行ポリシーの制限を一時的に変更して再度実施してみてください。 - インターネットからデータを取得するため、ダウンロードに時間がかかることがあります。
(340MB 程度のサンプルデータの CSV と各種スクリプトがダウンロードされます。)
すべてのファイルのダウンロードが完了すると、保存先に指定したディレクトリに、PowerShell スクリプトが保存されます。
Step 2: Import Data to SQL Server using PowerShell
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sqldev-py2-import-data-to-sql-server-using-powershell
このステップでは、ダウンロードしたスクリプトを実行して、演習に必要なデータベースオブジェクトを作成します。
スクリプトでは、使用するストアドプロシージャの大部分を作成し、指定したデータベースに作成されたテーブルに対してデータのインポートも行われます。
SQL オブジェクトとデータの作成
ダウンロードしたファイルの中には、PowerShell スクリプトが含まれています。
演習用の環境を準備するには、このスクリプトを実行します。
スクリプトでは以下のアクションが実行されます。
- SQL Native Client および、SQL コマンドラインユーティリティがインストールされていない場合、これらのツールをインストールします。
これらのユーティリティは、BCP を使用してデータを一括挿入するために必要となります。 - SQL Server のインスタンスにデータベースとテーブルを作成し、テーブルにデータを一括挿入します。
- 複数の SQL 関数とストアドプロシージャを作成します。
スクリプトの実行
- 管理者として、PowerShell コマンドプロンプトを開き、次のコマンドを実行します。
// コマンドは原文を参照
コマンドを実行すると、次の情報の入力が求められます。- Python が有効な ML サービスがインストールさている SQL Server 2017インスタンスの名前または、IP アドレス
- 作成するデータベース名
- インスタンスに接続するために必要なユーザー名とパスワード
入力したユーザーで、データベース/テーブル/ストアドプロシージャの作成、テーブルへのデータインポートが実施できる必要があります。
ユーザー名とパスワードの入力を省略した場合は、Windows 認証が使用されます。 - ダウンロードが行われたサンプルデータのファイルのパスとファイル名 (フルパスで指定)
- 上記の手順で指定した内容で、T-SQL スクリプト内のプレースホルダの情報が置き換えら、実行されます。
- 実行が終了したら、スクリプトで作成されるストアドプロシージャと関数を確認して下さい。
- create-db-tb-upload-data.sql
- データベースと 2 つのテーブルを作成します。
- nyctaxi_sample
- NYX Taxi のデータセットが含まれています。
クラスタ化列ストアインデックスがテーブルに追加され、ストレージとクエリのパフォーマンスを向上させていいます。
NYC Taxi データセットの 1% のサンプルがこのテーブルに挿入されます。
- NYX Taxi のデータセットが含まれています。
- nyc_taxi_model
- 機械学習により訓練された、高度な分析モデルを維持するために使用されます。
- nyctaxi_sample
- データベースと 2 つのテーブルを作成します。
- fnCalculateDistance.sql
- 乗車 / 降車の位置間の距離を計算するスカラー値関数を作成します。
- fnEngineerFeatures.sql
- モデルをトレーニングするための新しいデータ機能を作成する、テーブル地関数を作成します。
- TrainingTestingSplit.sql
- nyctaxi_sample テーブルのデータを nyctaxi_sample_training と nyctaxi_sample_testing の 2 つに分割します。
- PredictTipSciKitPy.sql
- 訓練されたモデル (scikit-learn) を呼び出し、モデルを使用して、予測を行うストアドプロシージャを作成します。
ストアドプロシージャを入力パラメータとして、問い合わせを受け付け、入力行のスコアを含んだ数値の列を返します。
- 訓練されたモデル (scikit-learn) を呼び出し、モデルを使用して、予測を行うストアドプロシージャを作成します。
- PredictTipRxPy.sql
- 訓練されたモデル (revoscalepy) を呼び出し、モデルを使用して、予測を行うストアドプロシージャを作成します。
ストアドプロシージャを入力パラメータとして、問い合わせを受け付け、入力行のスコアを含んだ数値の列を返します。
- 訓練されたモデル (revoscalepy) を呼び出し、モデルを使用して、予測を行うストアドプロシージャを作成します。
- PredictTipSingleModeSciKitPy.sql
- 訓練されたモデル (scikit-learn) を呼び出し、予測を行うストアドプロシージャを作成します。
ストアドプロシージャを入力は、インラインパラメーターとして渡された個々の値を観測地として受け付け、新しい観測地の結果を予測する値を返します。
- 訓練されたモデル (scikit-learn) を呼び出し、予測を行うストアドプロシージャを作成します。
- PredictTipSingleModeRxPy.sql
- 訓練されたモデル (revoscalepy) を呼び出し、予測を行うストアドプロシージャを作成します。
ストアドプロシージャを入力は、インラインパラメーターとして渡された個々の値を観測地として受け付け、新しい観測地の結果を予測する値を返します。
- 訓練されたモデル (revoscalepy) を呼び出し、予測を行うストアドプロシージャを作成します。
- create-db-tb-upload-data.sql
- チュートリアルの後半で、いくつかのストアドプロシージャを追加します。
- SerializePlots.sql
- データ探索用のストアドプロシージャを作成します。
このストアドプロシージャは、Python を使用して、グラフィックを作成し、グラフオブジェクトをシリアライズします。
- データ探索用のストアドプロシージャを作成します。
- TrainTipPredictionModelSciKitPy.sql
- ロジスティック回帰モデル (scikit-learn) をトレーニングするストアドプロシージャを作成します。
このモデルは傾きのある列の値を予測し、ランダムに選択された 60% のデータを使用して訓練されます。
ストアドプロシージャの出力は訓練されたモデルとなり、テーブル nyc_taxi_models に保存されます。
- ロジスティック回帰モデル (scikit-learn) をトレーニングするストアドプロシージャを作成します。
- TrainTipPredictionModelRxPy.sql
- ロジスティック回帰モデル (revoscalepy) をトレーニングするストアドプロシージャを作成します。
このモデルは傾きのある列の値を予測し、ランダムに選択された 60% のデータを使用して訓練されます。
ストアドプロシージャの出力は訓練されたモデルとなり、テーブル nyc_taxi_models に保存されます。
- ロジスティック回帰モデル (revoscalepy) をトレーニングするストアドプロシージャを作成します。
- SerializePlots.sql
- SQL Server Management Studio と指定したログインを使用して、SQL Server インスタンスにログインし、データベース等のオブジェクトが表示されることを確認します。
ノート
データベースオブジェクトが存在する場合、再作成は行われません。
テーブルがすでに存在している場合は、データは上書きではなく追加されます。
そのため、スクリプトを実行する前には、既存のオブジェクトを削除してください。
Step 3: Explore and Visualize the Data
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sqldev-py3-explore-and-visualize-the-data
データサイエンスソリューションを開発する場合は、一般的には、集中的なデータの探索とデータの視覚化が行われます。
このステップでは、サンプルデータを調べ、いくつかのプロットを生成します。
その後、Python グラフィックオブジェクトでシリアライズし、それらのオブジェクトをデシリアライズするしてプロットを作成する方法を学習します。
ノート
本チュートリアルでは、バイナリ分類タスクのみを利用します。
回帰 / マルチクラス分類の機械学習タスクを実施するためには別のモデルとして構築することを推奨します。
データの確認
オリジナルのデータセットでは、タクシーの識別子と輸送記録が別々のファイルで提供されていました。
サンプルデータではデータを使いやすくするために、元となった 2 つのデータセットが medallion / hack_license / pikup_datetime 列に追加されています。
レコードは、元のレコード数の 1 % を得るためにサンプリングされています。
使用されるデータはダウンサンプリングされた 1,703,957 行と 23 列により構成されています。
タクシーの識別子
- medallion 列は、タクシーの一意の ID 番号を表しています。
- hack_license 列は、タクシー運転免許証番号 (匿名化された番号) が含まれています。
輸送および運賃記録
- 各輸送記録には、乗車と降車の場所と時間、および輸送距離が含まれます。
- 各運賃記録には、支払いタイプ / 合計支払金額 / チップの金額などの支払い情報が含まれます。
- 最後の 3 つの列は、様々な機械学習タスクで利用することができます。
- tip_amount 列に、連続した数値が入り、回帰分析のラベル列として使用できます。
- tipped 列は、Yes / no の値を持ち、バイナリ分類に使用できます。
- tip_class 列は、複数のクラスラベルがあり、複数クラス分類タスクのラベルとして使用できます。
- ラベル列に使用される値は、tip_amount 列の値に基づき、次のビジネスルールにより設定されています。
- tipped
- tip_amount > 0 の場合 1
- それ以外 : 0
- tip_class
- Class 0 : tip_amount = $0
- Class 1 : tip_amount > $0 and tip_amount <= 5
- Class 2 : tip_amount > $5 and tip_amount <= 10
- Class 3 : tip_amount > $10 and tip_amount <= 20
- Class 4 : tip_amount > $20
- tipped
T-SQL で Python を使用してプロットを作成
視覚化は、データと異常値の分布を理解するための強力なツールであり、Python はデータを視覚化するための多くのパッケージを提供しています。
mataplotlib モジュールは、ヒストグラム / 散布図 / ボックスプロット、その他のデータ探索グラフを作成するための多くの機能を含んでいる一般的なライブラリとなります。
本セクションでは、ストアドプロシージャを使用して、プロットを操作する方法を学習します。
プロットの Python オブジェクトを varbinary データ型として保存し、生成されたプロットをサーバー内に保存します。
プロットを varbinary データ型に格納する
SQL Server 2017 ML サービスに含まれる revoscalepy モジュールには、RevoScaleR パッケージの R ライブラリが含まれています。
今回の例では、 rxHistogram に相当する Python を使用して、T-SQL クエリのデータを使用したヒストグラムのプロットを行います。
簡単に実行するために、PloHistogram ストアドプロシージャとしてラップします。
ストアドプロシージャはシリアライズされた、Python Figureオブジェクトを varbinary データ型のストリームとして返します。
バイナリデータをクエリから直接表示することはできませんが、クライアント上で Python コードを使用して、値をデシリアライズして、その結果の画像ファイルをクライアント上に保存することができます。
Plot_Python ストアドプロシージャの作成
-
- SQL Server Management Studio で、新しいクエリを開きます。
- 演習用のデータベースを選択して、次のステートメントを使用して、ストアドプロシージャを作成します。
必要に応じて、正しいテーブル名を使用するように変更してください。// ステートメントは原文を参照
ノート
- 変数 @query はスクリプトの入力変数である、 @input_data_1 として、Python のコードブロックに渡されるクエリテキストを定義します。
- Python スクリプトは非常にシンプルです
- metaplotlib figure オブジェクトを使って、ヒストグラムと散布図を作成し、これらのオブジェクトを picle ライブラリを使ってシリアライズします。
- Python グラフィックオブジェクトは出力のために、Python pandas DataFrame にシリアライズされます
表示可能なグラフィックファイルを varbinary のデータから出力する
-
- Management Studio で次のステートメントを実行します。
// ステートメントは原文を参照 - クライアントマシンで次の Python コードを実行します。
サーバー名 / データベース名 / 資格情報は適宜置き換えます。// Python コードは原文を参照
- Management Studio で次のステートメントを実行します。
ノート
Python のバージョンが、クライアントとサーバーで同じであることを確認してください。
また、クライアントで使用している Python ライブラリ (mataplotlib 等) が、サーバーにインストールされているライブラリと同じか、それ以上のバージョンであることを確認してください。
- 接続が成功すると、以下の結果が表示されます。
The plots are saved in directory: xxxx - 出力ファイルは、Python の作業ディレクトリに出力されます。
プロットされたデータを表示するには、Python の作業ディレクトリを開きます。
次の図は、クライアントに保存されたプロットの例となります。
Step 4: Create Data Features using T-SQL
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sqldev-py4-create-data-features-using-t-sql
データの探索後、データからいくつかの洞察を収集し、機能エンジニアリングに移ります。
生データから機能を作成する、このプロセスは、高度な分析モデリングにおいて重要なステップとなります。
このステップでは、T-SQL 関数を使用して、生データから特徴となる値を作成する方法を学習します。
その後、ストアドプロシージャから作成した関数を呼び出し、特徴となる値を含むテーブルを作成します。
関数を定義
元となるデータで報告された距離の値は、報告されたメートルの距離に基づいており、必ずしも地理的 / 輸送された距離を表すものではありません。
したがって、 NYC Taxi データセットで利用可能な座標を使用して、乗車位置と降車位置間の距離を計算する必要があります。
これを行うために、カスタム TSQL 関数で Haversine 式を使用します。
最初に、カスタム T-SQL 関数 fnCalculateDistance を使用して、Haversine 式により距離を計算し、2 つめのカスタム T-SQL 関数 fnEngineerFeatures を使用して特徴となる値を含む表を作成します。
fnCalculateDistanceを使用して輸送距離を計算する
- fnCalculateDistance 関数は、このチュートリアルの準備作業の実施により、SQL Server に登録されている必要があります。
以下のステートメントが登録されているかを確認してください。// ステートメントは原文を参照
ノート
関数はスカラー値関数であり、事前に定義された型の単一のデーたちを返します。
緯度と経度の値は、入力から取得され、輸送の乗車と降車のそれぞれの場所の情報が指定されます。
Haversine 式は、場所をラジアンに変換し、それらの値を使用して、2 つの場所の間の直接の距離を計算します。
計算された値をモデルの学習に使用するテーブルに追加するには、別の関数である fn_EngineerFeatures を使用します。
fnEngineerFeatures を使用して、特徴を保存する
- このチュートリアルの準備作業を実施したことにより登録された、fnEngineerFeatures 関数のステートメントを確認してください。
この関数は、複数の列を入力として、使用し、複数の特徴値を含む表を出力するテーブル値関数です。
この関数の目的はモデルの構築に使用する特徴のセットを作成することです。
fnEngineerFeatures は、先に作成されている fnCalculateDistance を呼び出して、乗車と降車位置間の直接の距離を取得します。
// ステートメントは原文を参照 - この機能は、元データの距離が 0 となっているが、乗車位置と降車位置が異なっている輸送についての、距離を計算することで、動作することを確認することができます。
このように、メーターによって報告された距離が必ずしも、地理的な距離に対応するとは限りません。
これが特徴値を選択 (算出) するための開発が重要な利用です。
次のステップでは、これらのデータ機能を使用して、Python niyoru機械学習モデルを作成し、トレーニングする方法を学びます。
Step 5: Train and Save a Model using T-SQL
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sqldev-py5-train-and-save-a-model-using-t-sql
このステップでは、Python パッケージの scikit-learn と revoscalepy を使用して、機械学習モデルを学習する方法について学びます。
これらの Python ライブラリは、ML サービスとともにインストールされているため、モジュールをロードし、ストアドプロシージャ内から必要となる関数を呼び出すことができます。
作成したデータ機能を使用して、モデルをトレーニングし、トレーニングしたモデルを SQL Server のテーブルに保存します。
サンプルデータをトレーニングセットとテストセットに分割する
-
- 次の T-SQL ステートメントを実行して、nyctaxi_sample テーブルのデータを、nyctaxi_sample_traning とnyctaxi_sample_testing の 2 つに分割するストアドプロシージャ (TrainTestSplit) を作成します。// ステートメントは原文を参照
- トレーニングセットに割り当てられたデータのパーセンテージを表す整数を入力して、ストアドプロシージャを実行します。
例えば、次のステートメントは、トレーニングセットにデータの 60% を割り当ててています。
トレーニングとテストのデータは 2 つの異なるテーブル (nyctaxi_sample_training 60% のデータ / nyctaxi_sample_testing 40% のデータ) に格納されています。// ステートメントは原文を参照
scikit-learn を使用してロジスティック回帰モデルを構築
このセクションでは、作成したトレーニングデータ (nyctaxi_sample_training テーブルのデータ) を使用して、モデルをトレーニングするためのストアドプロシージャを作成します。
このストアドプロシージャでは、入力データを受け取り、ロジスティック回帰モデルをトレーニングするために、scikit-learn 関数を使用しています。
システムストアドプロシージャ sp_execute_external_script を使用して、SQL Server にインストールされた Python ランタイムを呼びだします。
モデルの再トレーニングを容易にするために、別のストアドプロシージャで、sp_execute_external_script の呼び出しをラップして、新しいトレーニングデータをパラメーターとして渡すことができます。
このセクションではこのプロセスを順を追って説明します。
- Management Studio で、新しいクエリを開き、次のステートメントを実行して、ストアドプロシージャ、TrainTipPredicationModelSciKitpy を作成します。
ストアドプロシージャでは、入力データの定義が含まれており、モデルにデータを入力するためのクエリを用意する必要はありません。// ステートメントは原文を参照 - 次の SQL ステートメントを実行して、トレーニングされたモデルを nyc_taxi_models テーブルに挿入します。// ステートメントは原文を参照
データの処理とモデルのフィットには、数分かかります。
Python の stdout ストリームに出力されるメッセージは、Management Studio のメッセージウィンドウに表示されます。 - nyc_taxi_models テーブルを開きます。
新しい行が追加されていることがわかります。
この行の列には、シリアライズされたモデルが含まれています。
revoscalepy パッケージを使用して、ロジスティック回帰モデルを構築
次に revoscalepy パッケージを使用して、ロジスティック回帰モデルをトレーニングするための新しいストアドプロシージャを作成します。
Python の revoscalepy パッケージには、R 言語の RevoCaleR パッケージで提供されているものと同様のオブジェクト/変換/アルゴリズムが含まれています。
このライブラリでは、ロジスティックや線形回帰、決定木などの一般的なアルゴリズムを使用して、コンピュートコンテキスト間のデータの移動、データの変換、予測モデルのトレーニングを行うことができます。
詳細は revoscalepy とは? で確認できます。
- Management Studio で、新しいクエリを開き、次のステートメントを実行して、ストアドプロシージャ、TrainTipPredicationModelRxPy を作成します。
ストアドプロシージャでは、入力データの定義が含まれており、モデルにデータを入力するためのクエリを用意する必要はありません。// ステートメントは原文を参照
このストアドプロシージャは、モデルのトレーニングの一部として、次の手順を実行しています。
revoscalepy パッケージを使用して、nyctaxi_sample_training データでロジスティック回帰モデルをトレーニングします。
SELECT クエリはカスタムのスカラ関数 fn_CalculateDistance を使用して、乗車と降車間の直接の距離を計算します。
クエリの結果は、デフォルトの Python 入力変数である、InputDateset に格納されます。
Python スクリプトは R サービス (データベース内) に含まれている、revoscalepy の LogsitcRegression 関数を呼び出して、ロジスティック回帰モデルを作成します。
バイナリ変数 tipped は、ラベルまたは、結果行として使用され、モデルは、passenger_count / trip_distance / trip_time_in_secs / direct_distance という特徴的な項目を使用してフィットします。
Python 変数の logitObj に含まれる、訓練されたモデルはシリアライズされ、SQL Server の出力パラメータとして設定されます。
この出力は、データベースの nyc_taxi_models テーブルに新しい名前を持つ行として挿入され、将来的な予測を行うために、モデルを取得して使用することができます。 - 次の SQL ステートメントを実行して、トレーニングされたモデルを nyc_taxi_models テーブルに挿入します。// ステートメントは原文を参照
- データの処理とモデルのフィットには、数分かかります。
Python の stdout ストリームに出力されるメッセージは、Management Studio のメッセージウィンドウに表示されます。 - nyc_taxi_models テーブルを開きます。
新しい行が追加されていることがわかります。
この行の列には、シリアライズされたモデルが含まれています。
次のステップでは、訓練されたモデルを使用して予測を作成します。
Step 6: Operationalize the Model
https://docs.microsoft.com/en-us/sql/advanced-analytics/tutorials/sqldev-py6-operationalize-the-model
このステップでは、前のステップで保存した、訓練したモデルを操作する方法を学習します。
Operationalize は「スコアリングのために、モデルを本番環境に展開する」ことを意味しています。
Python コードがストアドプロシージャに含まれている場合には、これを簡単に実行することができ餡巣。
アプリケーションからは、ストアドプロシージャを呼び出して、新しい観測値による予想を行うことができます。
ストアドプロシージャから Python のモデルを呼び出すには、次の 2 つの方法があります。
- バッチスコアリングモード
- 複数の行のデータを渡すために、SELECT クエリを使用します。
ストアドプロシージャは入力に対応した観測のテーブルを返します。
- 複数の行のデータを渡すために、SELECT クエリを使用します。
- 個々のスコアリングモード
- 個々のパラメーターセットを入力として渡します。
ストアドプロシージャは、単一の行または、値を返します。
- 個々のパラメーターセットを入力として渡します。
scikit-learn モデルを使用したスコアリング
ストアドプロシージャ PredicitTipSciKitPy は、scikit-learn モデルを使用します。
このストアドプロシージャは、ストアドプロシージャで、Python による予測を行うための呼び出しをラップしています。
基本的な構文は、次のようになります。
- 使用するモデルの名前は、ストアドプロシージャの入力パラメーターとして指定されます。
- ストアドプロシージャは、プロシージャ内の SELECT 文を使用して、nyc_taxi_models テーブルからシリアライズされたモデルをロードします。
- シリアライズされたモデルは、Python の変数 mod に格納され、Python でさらに処理が行われます。
- スコアリングをする必要がある新しい情報は、@input_data_1 で指定された T-SQL から取得されます。
クエリのデータが読み込まれると、行の情報は、デフォルトのデータフレームである、InputDataSet に保存されます。 - このデータフレームは、scikit-learn モデルを使用して作成された、ロジスティック回帰モデル の 変数 mod のpredict_proba 関数に渡されます。
- predict_proba 関数 (probArray = mod.predict_roba(X)) は任意の量のチップが与えられる確率を表す float 値を返します。
- ストアドプロシージャは、AUC 評価メトリック (曲線下面積) についても計算を行います。
AUC などの評価メトリックは、ターゲットラベル (ヒント列) を指定した場合にのみ生成されます。
予測はターゲットラベル (変数 y) を必要としませんが、評価メトリック計算は行いません。 - スコアリングするデータのターゲットラベルがない場合、ストアドプロシージャを変更して、AUC の計算を削除し、チップがヒットする確率 (ストアドプロシージャの変数 x) を返すこともできます。
//ステートメントは原文を参照
revoscalepy モデルを使用したスコアリング
ストアドプロシージャ PredictTipRxPy は、revoscalepy ライブラリを使用して作成されたモデルを使用します。
これは、ストアドプロシージャ PredictTipSciKitPy と同じように動作しますが、reposcalepy 関数にいくつかの変更が加えられています。
// ステートメントは原文を参照
SELECT クエリを使用したバッチによるスコアリング
ストアドプロシージャ PredictTipSciKitPy および、PredictTipRxPy には、2 つの入力パラメータが必要となります。
- スコアリングするデータを取得するクエリ
- 本チュートリアルでは、nyctaxi_sample_testing テーブルのデータを使用しています。
- トレーニングされたモデルの名前
このセクションでは、これらの引数をストアドプロシージャに渡して、スコアリングに使用するモデルとデータの両方を簡単に変更する方法を学習します。
- スコアリングを行うため、次のように入力のデータを定義し、ストアドプロシージャを呼びだします。
この例では、スコアリングにストアドプロシージャ PredictTIpSciKitPy を使用し、モデルの名前とクエリの文字列を渡します。// ステートメントは原文を参照
ストアドプロシージャは、クエリの一部として渡された、各輸送に対して、チップが払われる予測の確率を返します。
クエリを実行するために、SSMS を使用している場合、結果ペインにテーブルとして表示されます。
メッセージペインでは、評価メトリック (AUC または、曲線下面積) が約 0.56 の値で出力されます。 - revoscalepy モデルをスコアリングに使用するには、ストアドプロシージャ、PredictTipRxPy を呼び出し、モデル名とクエリ列を渡します。
scikit-learn モデルを使用して、個々の行にスコアを付ける
状況によっては、バッチスコアリングの代わりに、アプリケーションから値を取得して、単一のケースを渡し、それらの値に基づいて単一の結果を取得することができます。
例えば、Excel ワークシート / Web アプリケーション / SSRS レポートから、ユーザーが入力または選択した値を使用して、ストアドプロシージャを呼び出します。
このセクションでは、ストアドプロシージャを呼び出して、単一の予測を作成する方法を学習します。
- 事前準備でダウンロードしたスクリプトに含まれる、PredictTipSingleModeScikitPy と、PredicitTipSingleModeRxPy のコードを確認してください。
これらのストアドプロシージャは、scikit-learn および、revoscalepy モデルを使用し、次のようにスコアリングを実行します。- モデルの名前と複数の単一値が入力として渡されます。
これらの入力には、乗客数 / 移動距離などが含まれます。 - テーブル値関数 fnEnginnerFeatures は緯度と経度の入力値から、直接の距離に変換します。
このテーブル値関数の説明は、Step 4 に含まれています。 - ストアドプロシージャを外部のアプリケーションから呼び出す場合は、入力データが Python のモデルの必要な入力機能を一致することを確認してください。
これには、入力データが Python データ型にキャストまたは変換されていること、データ型とデータ長の検証が含まれます。 - ストアドプロシージャは格納されている Python モデルに基づいてスコアを作成します。
- モデルの名前と複数の単一値が入力として渡されます。
PredictTipSingleModeSciKitPy
scikit-learn モデルを使用して、スコアリングを実行するストアドプロシージャの定義を次に示します。
// ステートメントは原文を参照
PredictTipSingleModeRxPy
revoscalepy モデルを使用して、スコアリングを実行するストアドプロシージャの定義を次に示します。
// ステートメントは原文を参照
- これを試すには、新しクエリを開いて、各列のパラメーターを指定してストアドプロシージャを呼び出します。
// ステートメントは原文を参照
パラメーターの 7 つの値の順序は、次のようになります。- 乗客数
- 移動距離
- 移動時間
- 乗車の緯度
- 乗車の経度
- 降車の緯度
- 降車の経度
- これにより、上記のパラメーターまたは、特徴を用いて、タクシー移動時にチップが支払われる確率が算出される。
結論
本チュートリアルでは、ストアドプロシージャに埋め込まれた Python コードを操作する方法を学習しました。
T-SQL との統合により、予測のための Python モデルの展開やエンタープライズデータのワークフローの一部として、モデルの再トレーニングの組み込みがより簡単なものとなります。