Python data analytics for SQL developers Intro

Jiayi Yang ML • Python • SQL Server 2019年12月23日

Demoは下記のURLのチュートリアルを利用しています。

https://docs.microsoft.com/ja-jp/sql/advanced-analytics/tutorials/sqldev-in-database-python-for-sql-developers?view=sql-server-ver15

--チュートリアルURL
--Ref:https://docs.microsoft.com/ja-jp/sql/advanced-analytics/tutorials/sqldev-in-database-python-for-sql-developers?view=sql-server-ver15

--NCY TAXIのDBダウンロードURL:
--https://sqlmldoccontent.blob.core.windows.net/sqlml/NYCTaxi_Sample.bak

--リストアしてから、互換性レベルを150に変更する

USE [NYCTaxi_Sample]
go

--NYC TAXIのデータの確認
select top 100 *
from [NYCTaxi_Sample].[dbo].[nyctaxi_sample]
go

--元のデータセットは多くのファイルにまたがっており、非常に大きくなっていました。 
--レコードをダウンサンプリングし、元のレコード数の 1% のみを取得しています。 
--現在のデータ テーブルには、1,703,957 行と 23 列が含まれています。
select count(1)
from [NYCTaxi_Sample].[dbo].[nyctaxi_sample]
go

/************************************
レッスン1:Python を使用したデータの探索および視覚化
************************************/

--matplotlibモジュールを使って、分析しようとするデータを分布図にする
--pandas DataFrameを使って、イメージ化にする
--イメージのデータストリームをバイナリにして、SQL Serverに保存する。

--Pythonに投げるデータセット
SELECT cast(tipped as int) as tipped, tip_amount, fare_amount FROM [dbo].[nyctaxi_sample]
go

--ストアドプロシージャの定義
ALTER PROCEDURE [dbo].[PyPlotMatplotlib]
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @query nvarchar(max) =
    N'SELECT cast(tipped as int) as tipped, tip_amount, fare_amount FROM [dbo].[nyctaxi_sample]'
    EXECUTE sp_execute_external_script
    @language = N'Python',
    @script = N'
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
import pandas as pd
import pickle

fig_handle = plt.figure()

plt.hist(InputDataSet.tipped)
plt.xlabel("Tipped")
plt.ylabel("Counts")
plt.title("Histogram, Tipped")
plot0 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
plt.clf()

plt.hist(InputDataSet.tip_amount)
plt.xlabel("Tip amount ($)")
plt.ylabel("Counts")
plt.title("Histogram, Tip amount")
plot1 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
plt.clf()

plt.hist(InputDataSet.fare_amount)
plt.xlabel("Fare amount ($)")
plt.ylabel("Counts")
plt.title("Histogram, Fare amount")
plot2 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
plt.clf()

plt.scatter( InputDataSet.fare_amount, InputDataSet.tip_amount)
plt.xlabel("Fare Amount ($)")
plt.ylabel("Tip Amount ($)")
plt.title("Tip amount by Fare amount")
plot3 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])
plt.clf()

OutputDataSet = plot0.append(plot1, ignore_index=True).append(plot2, ignore_index=True).append(plot3, ignore_index=True)
',
@input_data_1 = @query
WITH RESULT SETS ((plot varbinary(max)))
END

--実行
EXEC [dbo].[PyPlotMatplotlib]
go

--PythonからSQLServerに接続して、ストアドプロシージャを実行した結果のイメージを書き出す
--★Demo memo:Open <plot1.py> with VS code.★

/************************************
レッスン2:カスタム SQL 関数を使用してデータ機能を作成する
************************************/

--元のデータで報告されている距離の値は報告されたメーター距離に基づき、必ずしも地理的距離や走行距離を表さない。 
--そのため、ソースである NYC タクシー データセットの座標を利用し、乗車地点と降車地点の間の直接距離を計算する必要がある。

--haversine fomula(半正矢関数)で2点間の距離を計算する(球面三角法)
--Ref:https://ja.wikipedia.org/wiki/%E7%90%83%E9%9D%A2%E4%B8%89%E8%A7%92%E6%B3%95
--Ref:https://paulownia.hatenablog.com/entry/2018/12/16/212203
--Ref:https://docs.microsoft.com/ja-jp/sql/advanced-analytics/tutorials/demo-data-nyctaxi-in-sql?view=sql-server-ver15

--距離計算のFunctionを定義する
--単一レコードが返される
--fnCalculateDistanceスカラー値関数 (scalar-valued function)	乗車と降車の場所間の直線距離を計算する。 
--この関数は、データ機能の作成、モデルのトレーニングと保存、ML(R/Python)モデルの運用に使用される
ALTER FUNCTION [dbo].[fnCalculateDistance] (@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
RETURNS float
AS
BEGIN
  DECLARE @distance decimal(28, 10)
  -- Convert to radians
  SET @Lat1 = @Lat1 / 57.2958
  SET @Long1 = @Long1 / 57.2958
  SET @Lat2 = @Lat2 / 57.2958
  SET @Long2 = @Long2 / 57.2958
  -- Calculate distance
  SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))
  --Convert to miles
  IF @distance <> 0
  BEGIN
    SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance)
  END
  RETURN @distance
END
go

--メーター距離が0であったが、乗車場所と降車場所が異なっていた乗車の地理的距離を計算する。
SELECT tipped, fare_amount, passenger_count,(trip_time_in_secs/60) as TripMinutes,
trip_distance, pickup_datetime, dropoff_datetime,
[dbo].[fnCalculateDistance](pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) AS direct_distance
FROM nyctaxi_sample
WHERE pickup_longitude != dropoff_longitude 
and pickup_latitude != dropoff_latitude 
and trip_distance = 0
ORDER BY trip_time_in_secs DESC
go

/************************************
レッスン3:T-SQL を使用して Python モデルをトレーニングし保存する
************************************/
--nyctaxi_sample テーブルのデータを 2 つに分割する。
ALTER PROCEDURE [dbo].[PyTrainTestSplit] (@pct int)
AS

	DROP TABLE IF EXISTS dbo.nyctaxi_sample_training
	SELECT * into nyctaxi_sample_training FROM nyctaxi_sample
	WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) < @pct

	DROP TABLE IF EXISTS dbo.nyctaxi_sample_testing
	SELECT * into nyctaxi_sample_testing FROM nyctaxi_sample
	WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) > @pct
go

--60%のデータがトレーニングセットに割り当てる
EXEC [dbo].[PyTrainTestSplit] 60
GO
--(1025684 行処理されました)→nyctaxi_sample_training
--(660029 行処理されました)→nyctaxi_sample_testing

--ロジスティック回帰[LogisticRegression]モデルの作成
--Pythonのscikit-learn[サイキット・ラーン]機械学習ライブラリを使って、チップ予測モデルを作成する
--Ref:https://techacademy.jp/magazine/17375
ALTER PROCEDURE [dbo].[PyTrainScikit] (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'
import numpy
import pickle
from sklearn.linear_model import LogisticRegression

##SciKit-Learnロジスティック回帰モデルを作成する
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

SKLalgo = LogisticRegression()
logitObj = SKLalgo.fit(X, y)

##モデルのシリアル化
trained_model = pickle.dumps(logitObj)',
@input_data_1 = N'
select 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_training',
@input_data_1_name = N'InputDataSet',
@params = N'@trained_model varbinary(max) OUTPUT',
@trained_model = @trained_model OUTPUT
END

--トレーニング済みのモデルをテーブル nyc_taxi_models に追加する
DECLARE @model VARBINARY(MAX)
EXEC [dbo].[PyTrainScikit] @model OUTPUT
INSERT INTO nyc_taxi_models (name, model) VALUES('SciKit_model', @model)
go

--トレーニング済みのモデルを確認する
SELECT *
FROM [dbo].[nyc_taxi_models]
go

/************************************
レッスン4:ストアド プロシージャで Python モデルを使用して潜在的な結果を予測する
************************************/

--予測のやり方は2種類があり、バッチスコアリング(複数レコード)と個別スコアリング(単一レコード)
--バッチスコアリング: 入力データの複数の行を指定するには、SELECT クエリを引数としてストアド プロシージャに渡します。 結果は、入力ケースに対応する観察のテーブルを返します。
--個別のスコアリング:個々のパラメーター値のセットを入力として渡します。 このストアド プロシージャは、1 つの行または値を返します

--バッチスコアリングのストアドプロシージャの定義
ALTER PROCEDURE [dbo].[PredictTipSciKitPy] (@model varchar(50), @inquery nvarchar(max))
AS
BEGIN
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'
import pickle;
import numpy;
from sklearn import metrics

mod = pickle.loads(lmodel2)
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

probArray = mod.predict_proba(X)
probList = []
for i in range(len(probArray)):
  probList.append((probArray[i])[1])

probArray = numpy.asarray(probList)
fpr, tpr, thresholds = metrics.roc_curve(y, probArray)
aucResult = metrics.auc(fpr, tpr)
print ("AUC on testing data is: " + str(aucResult))

OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',  
  @input_data_1 = @inquery,
  @input_data_1_name = N'InputDataSet',
  @params = N'@lmodel2 varbinary(max)',
  @lmodel2 = @lmodel2
WITH RESULT SETS ((Score float));
END

--トレニンーグのデータを使って、スコアリングのMLモデルを作ったことに対して、テスティングのデータを使って、予測していく
--(1025684 行処理されました)→nyctaxi_sample_training
--(660029 行処理されました)→nyctaxi_sample_testing

--テスティングのデータを確認する
select 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

--指定したMLモデルで予測する
DECLARE @query_string nvarchar(max)
SET @query_string='
select 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'
EXEC [dbo].[PredictTipSciKitPy] 'SciKit_model', @query_string
go

--個別のスコアリングの予測を計算するために、事前に、距離の計算も含めるようにパラメータを定義し、ファンクションをあらかじめに用意する
--fnEngineerFeatures	テーブル値関数 (table-valued function)	モデル トレーニング用の新しいデータ機能を作成する。
--この関数は、データ機能の作成、モデルのトレーニングと保存、ML(R/Python)モデルの運用に使用される
ALTER FUNCTION [dbo].[fnEngineerFeatures] (
@passenger_count int = 0,
@trip_distance float = 0,
@trip_time_in_secs int = 0,
@pickup_latitude float = 0,
@pickup_longitude float = 0,
@dropoff_latitude float = 0,
@dropoff_longitude float = 0)
RETURNS TABLE
AS
  RETURN
  (
  SELECT
    @passenger_count AS passenger_count,
    @trip_distance AS trip_distance,
    @trip_time_in_secs AS trip_time_in_secs,
    [dbo].[fnCalculateDistance](@pickup_latitude, @pickup_longitude, @dropoff_latitude, @dropoff_longitude) AS direct_distance
  )
  go

--個別のスコアリングの予測ストアドプロシージャの定義
ALTER PROCEDURE [dbo].[PredictTipSingleModeSciKitPy] (
  @model varchar(50), 
  @passenger_count int = 0,
  @trip_distance float = 0,
  @trip_time_in_secs int = 0,
  @pickup_latitude float = 0,
  @pickup_longitude float = 0,
  @dropoff_latitude float = 0,
  @dropoff_longitude float = 0)
AS
BEGIN
  DECLARE @inquery nvarchar(max) = N'
  SELECT * FROM [dbo].[fnEngineerFeatures]( 
    @passenger_count,
    @trip_distance,
    @trip_time_in_secs,
    @pickup_latitude,
    @pickup_longitude,
    @dropoff_latitude,
    @dropoff_longitude)
    '
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import pickle;
import numpy;

# Load model and unserialize
mod = pickle.loads(model)

# Get features for scoring from input data
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

# Score data to get tip prediction probability as a list (of float)
probList = []
probList.append((mod.predict_proba(X)[0])[1])

# Create output data frame
OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max),@passenger_count int,@trip_distance float,
    @trip_time_in_secs int ,
    @pickup_latitude float ,
    @pickup_longitude float ,
    @dropoff_latitude float ,
    @dropoff_longitude float',
    @model = @lmodel2,
    @passenger_count =@passenger_count ,
    @trip_distance=@trip_distance,
    @trip_time_in_secs=@trip_time_in_secs,
    @pickup_latitude=@pickup_latitude,
    @pickup_longitude=@pickup_longitude,
    @dropoff_latitude=@dropoff_latitude,
    @dropoff_longitude=@dropoff_longitude
WITH RESULT SETS ((Score float));
END

--指定したMLモデル+パラメータで予測する(2.5MI=4KM、631秒乗車)
EXEC [dbo].[PredictTipSingleModeSciKitPy] 'SciKit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

--(繰り返し)
--予測モデルのチューニング(98%のデータをトレーニング用とする)
EXEC [dbo].[PyTrainTestSplit] 98
GO

--トレーニング済みのモデルをテーブル nyc_taxi_models に追加する
DELETE nyc_taxi_models
go

DECLARE @model VARBINARY(MAX)
EXEC [dbo].[PyTrainScikit] @model OUTPUT
INSERT INTO nyc_taxi_models (name, model) VALUES('SciKit_model', @model)
go

--トレーニング済みのモデルを確認する
SELECT *
FROM [dbo].[nyc_taxi_models]
go

--複数レコード予測する
DECLARE @query_string nvarchar(max)
SET @query_string='
select 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'
EXEC [dbo].[PredictTipSciKitPy] 'SciKit_model', @query_string
go

--単一レコード予測する
EXEC [dbo].[PredictTipSingleModeSciKitPy] 'SciKit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

--チュートリアルURL

--Ref:https://docs.microsoft.com/ja-jp/sql/advanced-analytics/tutorials/sqldev-in-database-python-for-sql-developers?view=sql-server-ver15

--NCY TAXIのDBダウンロードURL:

--https://sqlmldoccontent.blob.core.windows.net/sqlml/NYCTaxi_Sample.bak

--リストアしてから、互換性レベルを150に変更する

USE [NYCTaxi_Sample]

--NYC TAXIのデータの確認

select top 100 *

from [NYCTaxi_Sample].[dbo].[nyctaxi_sample]

--元のデータセットは多くのファイルにまたがっており、非常に大きくなっていました。

--レコードをダウンサンプリングし、元のレコード数の 1% のみを取得しています。

--現在のデータテーブルには、1,703,957 行と 23 列が含まれています。

select count(1)

from [NYCTaxi_Sample].[dbo].[nyctaxi_sample]

/************************************

レッスン1:Python を使用したデータの探索および視覚化

************************************/

--matplotlibモジュールを使って、分析しようとするデータを分布図にする

--pandas DataFrameを使って、イメージ化にする

--イメージのデータストリームをバイナリにして、SQL Serverに保存する。

--Pythonに投げるデータセット

SELECT cast(tipped as int) as tipped, tip_amount, fare_amount FROM [dbo].[nyctaxi_sample]

--ストアドプロシージャの定義

ALTER PROCEDURE [dbo].[PyPlotMatplotlib]

BEGIN

SET NOCOUNT ON;

DECLARE @query nvarchar(max) =

N'SELECT cast(tipped as int) as tipped, tip_amount, fare_amount FROM [dbo].[nyctaxi_sample]'

EXECUTE sp_execute_external_script

@language = N'Python',

@script = N'

import matplotlib

matplotlib.use("Agg")

import matplotlib.pyplot as plt

import pandas as pd

import pickle

fig_handle = plt.figure()

plt.hist(InputDataSet.tipped)

plt.xlabel("Tipped")

plt.ylabel("Counts")

plt.title("Histogram, Tipped")

plot0 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])

plt.clf()

plt.hist(InputDataSet.tip_amount)

plt.xlabel("Tip amount ($)")

plt.ylabel("Counts")

plt.title("Histogram, Tip amount")

plot1 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])

plt.clf()

plt.hist(InputDataSet.fare_amount)

plt.xlabel("Fare amount ($)")

plt.ylabel("Counts")

plt.title("Histogram, Fare amount")

plot2 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])

plt.clf()

plt.scatter( InputDataSet.fare_amount, InputDataSet.tip_amount)

plt.xlabel("Fare Amount ($)")

plt.ylabel("Tip Amount ($)")

plt.title("Tip amount by Fare amount")

plot3 = pd.DataFrame(data =[pickle.dumps(fig_handle)], columns =["plot"])

plt.clf()

OutputDataSet = plot0.append(plot1, ignore_index=True).append(plot2, ignore_index=True).append(plot3, ignore_index=True)

@input_data_1 = @query

WITH RESULT SETS ((plot varbinary(max)))

END

--実行

EXEC [dbo].[PyPlotMatplotlib]

--PythonからSQLServerに接続して、ストアドプロシージャを実行した結果のイメージを書き出す

--★Demo memo:Open <plot1.py> with VS code.★

/************************************

レッスン2:カスタム SQL 関数を使用してデータ機能を作成する

************************************/

--元のデータで報告されている距離の値は報告されたメーター距離に基づき、必ずしも地理的距離や走行距離を表さない。

--そのため、ソースである NYC タクシーデータセットの座標を利用し、乗車地点と降車地点の間の直接距離を計算する必要がある。

--haversine fomula(半正矢関数)で2点間の距離を計算する(球面三角法)

--Ref:https://ja.wikipedia.org/wiki/%E7%90%83%E9%9D%A2%E4%B8%89%E8%A7%92%E6%B3%95

--Ref:https://paulownia.hatenablog.com/entry/2018/12/16/212203

--Ref:https://docs.microsoft.com/ja-jp/sql/advanced-analytics/tutorials/demo-data-nyctaxi-in-sql?view=sql-server-ver15

--距離計算のFunctionを定義する

--単一レコードが返される

--fnCalculateDistanceスカラー値関数 (scalar-valued function) 乗車と降車の場所間の直線距離を計算する。

--この関数は、データ機能の作成、モデルのトレーニングと保存、ML(R/Python)モデルの運用に使用される

ALTER FUNCTION [dbo].[fnCalculateDistance] (@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)

RETURNS float

BEGIN

DECLARE @distance decimal(28, 10)

-- Convert to radians

SET @Lat1 = @Lat1 / 57.2958

SET @Long1 = @Long1 / 57.2958

SET @Lat2 = @Lat2 / 57.2958

SET @Long2 = @Long2 / 57.2958

-- Calculate distance

SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))

--Convert to miles

IF @distance <> 0

BEGIN

SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance)

END

RETURN @distance

END

--メーター距離が0であったが、乗車場所と降車場所が異なっていた乗車の地理的距離を計算する。

SELECT tipped, fare_amount, passenger_count,(trip_time_in_secs/60) as TripMinutes,

trip_distance, pickup_datetime, dropoff_datetime,

[dbo].[fnCalculateDistance](pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) AS direct_distance

FROM nyctaxi_sample

WHERE pickup_longitude != dropoff_longitude

and pickup_latitude != dropoff_latitude

and trip_distance = 0

ORDER BY trip_time_in_secs DESC

/************************************

レッスン3:T-SQL を使用して Python モデルをトレーニングし保存する

************************************/

--nyctaxi_sample テーブルのデータを 2 つに分割する。

ALTER PROCEDURE [dbo].[PyTrainTestSplit] (@pct int)

DROP TABLE IF EXISTS dbo.nyctaxi_sample_training

SELECT * into nyctaxi_sample_training FROM nyctaxi_sample

WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license) as int)) % 100) < @pct

DROP TABLE IF EXISTS dbo.nyctaxi_sample_testing

SELECT * into nyctaxi_sample_testing FROM nyctaxi_sample

WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license) as int)) % 100) > @pct

--60%のデータがトレーニングセットに割り当てる

EXEC [dbo].[PyTrainTestSplit] 60

--(1025684 行処理されました)→nyctaxi_sample_training

--(660029 行処理されました)→nyctaxi_sample_testing

--ロジスティック回帰[LogisticRegression]モデルの作成

--Pythonのscikit-learn[サイキット・ラーン]機械学習ライブラリを使って、チップ予測モデルを作成する

--Ref:https://techacademy.jp/magazine/17375

ALTER PROCEDURE [dbo].[PyTrainScikit] (@trained_model varbinary(max) OUTPUT)

BEGIN

EXEC sp_execute_external_script

@language = N'Python',

@script = N'

import numpy

import pickle

from sklearn.linear_model import LogisticRegression

##SciKit-Learnロジスティック回帰モデルを作成する

X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

y = numpy.ravel(InputDataSet[["tipped"]])

SKLalgo = LogisticRegression()

logitObj = SKLalgo.fit(X, y)

##モデルのシリアル化

trained_model = pickle.dumps(logitObj)',

@input_data_1 = N'

select 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_training',

@input_data_1_name = N'InputDataSet',

@params = N'@trained_model varbinary(max) OUTPUT',

@trained_model = @trained_model OUTPUT

END

--トレーニング済みのモデルをテーブル nyc_taxi_models に追加する

DECLARE @model VARBINARY(MAX)

EXEC [dbo].[PyTrainScikit] @model OUTPUT

INSERT INTO nyc_taxi_models (name, model) VALUES('SciKit_model', @model)

--トレーニング済みのモデルを確認する

SELECT *

FROM [dbo].[nyc_taxi_models]

/************************************

レッスン4:ストアドプロシージャで Python モデルを使用して潜在的な結果を予測する

************************************/

--予測のやり方は2種類があり、バッチスコアリング(複数レコード)と個別スコアリング(単一レコード)

--バッチスコアリング: 入力データの複数の行を指定するには、SELECT クエリを引数としてストアドプロシージャに渡します。結果は、入力ケースに対応する観察のテーブルを返します。

--個別のスコアリング:個々のパラメーター値のセットを入力として渡します。このストアドプロシージャは、1 つの行または値を返します

--バッチスコアリングのストアドプロシージャの定義

ALTER PROCEDURE [dbo].[PredictTipSciKitPy] (@model varchar(50), @inquery nvarchar(max))

BEGIN

DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);

EXEC sp_execute_external_script

@language = N'Python',

@script = N'

import pickle;

import numpy;

from sklearn import metrics

mod = pickle.loads(lmodel2)

X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

y = numpy.ravel(InputDataSet[["tipped"]])

probArray = mod.predict_proba(X)

probList = []

for i in range(len(probArray)):

probList.append((probArray[i])[1])

probArray = numpy.asarray(probList)

fpr, tpr, thresholds = metrics.roc_curve(y, probArray)

aucResult = metrics.auc(fpr, tpr)

print ("AUC on testing data is: " + str(aucResult))

OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])

@input_data_1 = @inquery,

@input_data_1_name = N'InputDataSet',

@params = N'@lmodel2 varbinary(max)',

@lmodel2 = @lmodel2

WITH RESULT SETS ((Score float));

END

--トレニンーグのデータを使って、スコアリングのMLモデルを作ったことに対して、テスティングのデータを使って、予測していく

--(1025684 行処理されました)→nyctaxi_sample_training

--(660029 行処理されました)→nyctaxi_sample_testing

--テスティングのデータを確認する

select 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

--指定したMLモデルで予測する

DECLARE @query_string nvarchar(max)

SET @query_string='

select 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'

EXEC [dbo].[PredictTipSciKitPy] 'SciKit_model', @query_string

--個別のスコアリングの予測を計算するために、事前に、距離の計算も含めるようにパラメータを定義し、ファンクションをあらかじめに用意する

--fnEngineerFeatures テーブル値関数 (table-valued function) モデルトレーニング用の新しいデータ機能を作成する。

--この関数は、データ機能の作成、モデルのトレーニングと保存、ML(R/Python)モデルの運用に使用される

ALTER FUNCTION [dbo].[fnEngineerFeatures] (

@passenger_count int = 0,

@trip_distance float = 0,

@trip_time_in_secs int = 0,

@pickup_latitude float = 0,

@pickup_longitude float = 0,

@dropoff_latitude float = 0,

@dropoff_longitude float = 0)

RETURNS TABLE

RETURN

(

SELECT

@passenger_count AS passenger_count,

@trip_distance AS trip_distance,

@trip_time_in_secs AS trip_time_in_secs,

[dbo].[fnCalculateDistance](@pickup_latitude, @pickup_longitude, @dropoff_latitude, @dropoff_longitude) AS direct_distance

)

--個別のスコアリングの予測ストアドプロシージャの定義

ALTER PROCEDURE [dbo].[PredictTipSingleModeSciKitPy] (

@model varchar(50),

@passenger_count int = 0,

@trip_distance float = 0,

@trip_time_in_secs int = 0,

@pickup_latitude float = 0,

@pickup_longitude float = 0,

@dropoff_latitude float = 0,

@dropoff_longitude float = 0)

BEGIN

DECLARE @inquery nvarchar(max) = N'

SELECT * FROM [dbo].[fnEngineerFeatures](

@passenger_count,

@trip_distance,

@trip_time_in_secs,

@pickup_latitude,

@pickup_longitude,

@dropoff_latitude,

@dropoff_longitude)

DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);

EXEC sp_execute_external_script

@language = N'Python',

@script = N'

import pickle;

import numpy;

# Load model and unserialize

mod = pickle.loads(model)

# Get features for scoring from input data

X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

# Score data to get tip prediction probability as a list (of float)

probList = []

probList.append((mod.predict_proba(X)[0])[1])

# Create output data frame

OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])

@input_data_1 = @inquery,

@params = N'@model varbinary(max),@passenger_count int,@trip_distance float,

@trip_time_in_secs int ,

@pickup_latitude float ,

@pickup_longitude float ,

@dropoff_latitude float ,

@dropoff_longitude float',

@model = @lmodel2,

@passenger_count =@passenger_count ,

@trip_distance=@trip_distance,

@trip_time_in_secs=@trip_time_in_secs,

@pickup_latitude=@pickup_latitude,

@pickup_longitude=@pickup_longitude,

@dropoff_latitude=@dropoff_latitude,

@dropoff_longitude=@dropoff_longitude

WITH RESULT SETS ((Score float));

END

--指定したMLモデル+パラメータで予測する(2.5MI=4KM、631秒乗車)

EXEC [dbo].[PredictTipSingleModeSciKitPy] 'SciKit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

--(繰り返し)

--予測モデルのチューニング(98%のデータをトレーニング用とする)

EXEC [dbo].[PyTrainTestSplit] 98

--トレーニング済みのモデルをテーブル nyc_taxi_models に追加する

DELETE nyc_taxi_models

DECLARE @model VARBINARY(MAX)

EXEC [dbo].[PyTrainScikit] @model OUTPUT

INSERT INTO nyc_taxi_models (name, model) VALUES('SciKit_model', @model)

--トレーニング済みのモデルを確認する

SELECT *

FROM [dbo].[nyc_taxi_models]

--複数レコード予測する

DECLARE @query_string nvarchar(max)

SET @query_string='

select 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'

EXEC [dbo].[PredictTipSciKitPy] 'SciKit_model', @query_string

--単一レコード予測する

EXEC [dbo].[PredictTipSingleModeSciKitPy] 'SciKit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

import pyodbc
import pickle
import os
cnxn = pyodbc.connect('DRIVER=SQL Server;SERVER=<xxxxxx>;DATABASE=NYCTaxi_Sample;UID=sa;PWD=<xxxxxx>')
cursor = cnxn.cursor()
cursor.execute("EXECUTE [dbo].[PyPlotMatplotlib]")
tables = cursor.fetchall()
for i in range(0, len(tables)):
    fig = pickle.loads(tables[i][0])
    fig.savefig(str(i)+'.png')
print("The plots are saved in directory: ",os.getcwd())

import pyodbc

import pickle

import os

cnxn = pyodbc.connect('DRIVER=SQL Server;SERVER=<xxxxxx>;DATABASE=NYCTaxi_Sample;UID=sa;PWD=<xxxxxx>')

cursor = cnxn.cursor()

cursor.execute("EXECUTE [dbo].[PyPlotMatplotlib]")

tables = cursor.fetchall()

for i in range(0, len(tables)):

fig = pickle.loads(tables[i][0])

fig.savefig(str(i)+'.png')

print("The plots are saved in directory: ",os.getcwd())

Tagged with: Python • SQL Server 2019 • SQL Server ML Services