ML Services のサンプル

GPU 版 Tensorflow のテスト用クエリ.sql

 exec sp_execute_external_script @language =N'Python', 
 @script=N'

from tensorflow.examples.tutorials.mnist import input_data
mnist = input_data.read_data_sets("MNIST_data", one_hot=True)

import tensorflow as tf
sess = tf.InteractiveSession()

x = tf.placeholder(tf.float32, shape=[None, 784])
y_ = tf.placeholder(tf.float32, shape=[None, 10])

W = tf.Variable(tf.zeros([784,10]))
b = tf.Variable(tf.zeros([10]))

sess.run(tf.global_variables_initializer())

y = tf.matmul(x,W) + b

cross_entropy = tf.reduce_mean(tf.nn.softmax_cross_entropy_with_logits(labels=y_, logits=y))

train_step = tf.train.GradientDescentOptimizer(0.5).minimize(cross_entropy)

for _ in range(1000):
  batch = mnist.train.next_batch(100)
  train_step.run(feed_dict={x: batch[0], y_: batch[1]})

correct_prediction = tf.equal(tf.argmax(y,1), tf.argmax(y_,1))

accuracy = tf.reduce_mean(tf.cast(correct_prediction, tf.float32))

print(accuracy.eval(feed_dict={x: mnist.test.images, y_: mnist.test.labels}))

def weight_variable(shape):
  initial = tf.truncated_normal(shape, stddev=0.1)
  return tf.Variable(initial)

def bias_variable(shape):
  initial = tf.constant(0.1, shape=shape)
  return tf.Variable(initial)

def conv2d(x, W):
  return tf.nn.conv2d(x, W, strides=[1, 1, 1, 1], padding="SAME")

def max_pool_2x2(x):
  return tf.nn.max_pool(x, ksize=[1, 2, 2, 1], strides=[1, 2, 2, 1], padding="SAME")

W_conv1 = weight_variable([5, 5, 1, 32])
b_conv1 = bias_variable([32])

x_image = tf.reshape(x, [-1, 28, 28, 1])

h_conv1 = tf.nn.relu(conv2d(x_image, W_conv1) + b_conv1)
h_pool1 = max_pool_2x2(h_conv1)

W_conv2 = weight_variable([5, 5, 32, 64])
b_conv2 = bias_variable([64])

h_conv2 = tf.nn.relu(conv2d(h_pool1, W_conv2) + b_conv2)
h_pool2 = max_pool_2x2(h_conv2)

W_fc1 = weight_variable([7 * 7 * 64, 1024])
b_fc1 = bias_variable([1024])

h_pool2_flat = tf.reshape(h_pool2, [-1, 7*7*64])
h_fc1 = tf.nn.relu(tf.matmul(h_pool2_flat, W_fc1) + b_fc1)

keep_prob = tf.placeholder(tf.float32)
h_fc1_drop = tf.nn.dropout(h_fc1, keep_prob)

W_fc2 = weight_variable([1024, 10])
b_fc2 = bias_variable([10])

y_conv = tf.matmul(h_fc1_drop, W_fc2) + b_fc2

cross_entropy = tf.reduce_mean(tf.nn.softmax_cross_entropy_with_logits(labels=y_, logits=y_conv))
train_step = tf.train.AdamOptimizer(1e-4).minimize(cross_entropy)

correct_prediction = tf.equal(tf.argmax(y_conv, 1), tf.argmax(y_, 1))
accuracy = tf.reduce_mean(tf.cast(correct_prediction, tf.float32))

config = tf.ConfigProto(
    device_count={"GPU":0}, # GPUの数0に
    log_device_placement=True
)
with tf.Session(config=config) as sess:
    sess.run(tf.global_variables_initializer())
    for i in range(10):
      batch = mnist.train.next_batch(50)
      if i % 100 == 0:
        train_accuracy = accuracy.eval(feed_dict={x: batch[0], y_: batch[1], keep_prob: 1.0})
        print("step %d, training accuracy %g" % (i, train_accuracy))
      train_step.run(feed_dict={x: batch[0], y_: batch[1], keep_prob: 0.5})
      print("test accuracy %g" % accuracy.eval(feed_dict={x: mnist.test.images, y_: mnist.test.labels, keep_prob: 1.0}))

MicrosoftMLのGPU利用.py

import numpy
import pandas
from microsoftml import rx_neural_network, rx_predict
from revoscalepy.etl.RxDataStep import rx_data_step
from microsoftml.datasets.datasets import infert
from microsoftml import gpu_math

import sklearn
if sklearn.__version__ < "0.18":
    from sklearn.cross_validation import train_test_split
else:
    from sklearn.model_selection import train_test_split

infertdf = infert.as_df()
infertdf["isCase"] = infertdf.case == 1
data_train, data_test, y_train, y_test = train_test_split(infertdf, infertdf.isCase)

forest_model = rx_neural_network(
    formula=" isCase ~ age + parity + education + spontaneous + induced ",
    data=data_train,
    num_iterations=100,
    acceleration=gpu_math(cu_dnn=True))
#    acceleration={"name":"SseMath"})
# "AvxMath", "ClrMath", "GpuMath", "MklMath", "SseMath""
# RuntimeError: The type (RxTextData) for file is not supported.
while(True):
    score_ds = rx_predict(forest_model, data=data_test,
                     extra_vars_to_write=["isCase", "Score"])

# Print the first five rows
print(rx_data_step(score_ds, number_rows_read=5))

PREDICTを使用した予測.sql

USE NativeScoringTest;
GO
DROP TABLE IF EXISTS AirlineDemo;
GO
CREATE TABLE AirlineDemo (
  ArrDelay int,
  CRSDepTime float,
  DayOfWeek varchar(100)
);
GO

INSERT INTO AirlineDemo (ArrDelay, CRSDepTime, DayOfWeek)
EXECUTE sp_execute_external_script
@language = N'Python'
, @script = N'
import os
from revoscalepy import RxOptions, RxXdfData, rx_serialize_model, rx_lin_mod, rx_data_step
sample_data_path = RxOptions.get_option("sampleDataDir")
ds = RxXdfData(os.path.join(sample_data_path, "AirlineDemoSmall.xdf"))
OutputDataSet = rx_data_step(ds)
'
GO

SELECT TOP 100 * FROM AirlineDemo
GO

DROP TABLE IF EXISTS ml_models;
GO
CREATE TABLE ml_models ( 
model_name nvarchar(100) not null primary key, 
model_version nvarchar(100) not null, 
native_model_object varbinary(max) not null);
GO

DECLARE @model varbinary(max);
EXECUTE sp_execute_external_script
@language = N'Python'
, @script = N'
import os
from revoscalepy import RxOptions, RxXdfData, rx_serialize_model, rx_lin_mod, rx_summary

sample_data_path = RxOptions.get_option("sampleDataDir")
ds = RxXdfData(os.path.join(sample_data_path, "AirlineDemoSmall.xdf"))
linmod = rx_lin_mod("ArrDelay ~ CRSDepTime + DayOfWeek", ds)
# https://docs.microsoft.com/en-us/r-server/r-reference/revoscaler/airlinedemosmall
# ArrDelay : 到着の遅延時間 (分)
# CRSDepTime : 出発時刻
# DayOfWeek : 曜日

summary = rx_summary("ArrDelay ~ CRSDepTime + DayOfWeek", ds)
print(summary)

model = rx_serialize_model(linmod, realtime_scoring_only = True)
'
, @params = N'@model varbinary(max) OUTPUT'
, @model = @model OUTPUT

INSERT [dbo].[ml_models]([model_name], [model_version], [native_model_object])
VALUES('AirLineDemo','v1', @model) ;
GO

SELECT * FROM ml_models
GO

DECLARE @model varbinary(max) = (
SELECT native_model_object
FROM ml_models
WHERE model_name = 'AirLineDemo'
AND model_version = 'v1');

DROP TABLE IF EXISTS #tbl
CREATE TABLE #tbl(  
ArrDelay int,
CRSDepTime float,
DayOfWeek varchar(100)
)

INSERT INTO #tbl SELECT TOP 100 * FROM AirlineDemo ORDER BY CRSDepTime, DayOfWeek
SELECT d.*, p.*
FROM PREDICT(MODEL = @model, DATA = #tbl as d)
WITH(
ArrDelay_Pred float
) as p;
GO

モデルのダンプと GPU 利用.sql

DECLARE @model varbinary(max)
EXEC sp_execute_external_script 
@language=N'Python',
@script = N'
import numpy
import pickle
# import pandas
from microsoftml import rx_neural_network, rx_predict
from revoscalepy.etl.RxDataStep import rx_data_step
from microsoftml.datasets.datasets import infert
from revoscalepy import rx_serialize_model

import sklearn
if sklearn.__version__ < "0.18":
    from sklearn.cross_validation import train_test_split
else:
    from sklearn.model_selection import train_test_split

infertdf = infert.as_df()
infertdf["isCase"] = infertdf.case == 1
data_train, data_test, y_train, y_test = train_test_split(infertdf, infertdf.isCase)

forest_model = rx_neural_network(
    formula=" isCase ~ age + parity + education + spontaneous + induced ",
    data=data_train,
    num_iterations=20,
    acceleration={"name":"GpuMath"})

model = pickle.dumps(forest_model)
'
, @params = N'@model varbinary(max) OUTPUT'
, @model = @model OUTPUT

DROP TABLE IF EXISTS ml_models
CREATE TABLE ml_models ( 
model_name nvarchar(100) not null primary key, 
model_version nvarchar(100) not null, 
native_model_object varbinary(max) not null);

INSERT [dbo].[ml_models]([model_name], [model_version], [native_model_object])
VALUES('GPUModel','v1', @model) ;
GO

SET NOCOUNT ON
DROP TABLE IF EXISTS #tmp
SELECT 10 AS age, 2 AS education, 1 AS induced, 4 AS parity, 1 AS spontaneous  INTO #tmp
GO
INSERT INTO #tmp SELECT * FROM #tmp
GO 22
SELECT COUNT(*) FROM #tmp
GO

DECLARE @model varbinary(max) = (SELECT native_model_object FROM [dbo].[ml_models] WHERE model_name = 'GPUModel')

EXEC sp_execute_external_script
@language= N'Python',
@script = N'
import pickle
from microsoftml import rx_predict

forest_model = pickle.loads(model)

rx_predict(forest_model, data=InputDataSet,
                extra_vars_to_write=["isCase", "Score"])
',
@input_data_1 = N'SELECT * FROM #tmp' ,
@params = N'@model varbinary(max)',
@model = @model
GO 10