· 8 years ago · Mar 20, 2018, 03:54 AM
1EXEC sp_configure 'external scripts enabled', 1
2RECONFIGURE WITH OVERRIDE
3
4EXEC sp_configure 'external scripts enabled'
5
6
7EXEC sp_execute_external_script @language =N'Python',
8
9@script=N'
10
11OutputDataSet = InputDataSet;
12
13',
14
15@input_data_1 =N'SELECT 1 AS hello'
16
17WITH RESULT SETS (([hello] int not null));
18
19GO
20
21RSetup.exe /install /component MLM /version 9.2.0.24 /language 1033 /destdir "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\Lib\site-packages\microsoftml\mxLibs"
22
23
24CREATE OR ALTER PROCEDURE [dbo].[get_sentiment](@text NVARCHAR(MAX)) AS
25
26AS
27BEGIN
28 DECLARE @script nvarchar(max);
29
30 --The Python script we want to execute
31/* @script = N'script' -> External language script specified as a literal or variable input. script is nvarchar(max). */
32 SET @script = N'
33## Here we importing the pandas data analysis library module for python to enable us to
34## carry out our entire data analysis workflow in Python. We are also importing the
35## rx_featurize and get_sentiment
36## MicrosoftML features that are useful for transforming data such as generating
37## count-based features and and analyzing data to create features
38## that are most useful for modeling.
39import pandas as p
40from microsoftml import rx_featurize, get_sentiment
41
42analyze_this = text
43
44## Create the data from the input text (analyze_this)
45
46text_to_analyze = p.DataFrame(data=dict(Text=[analyze_this]))
47
48## Get the sentiment scores
49## get_sentiment returns the probability that the sentiment of the input data is positive
50## rx_featurize transforms data from an input data set to an output data set
51## ml_transformations specifies the type of transformation
52
53sentiment_scores = rx_featurize(data=text_to_analyze,ml_transforms=[get_sentiment(cols=dict(scores="Text"))])
54
55## Lets translate the score to something more meaningful by determining the range of
56## values that will determine positivity
57
58sentiment_scores["Sentiment"] = sentiment_scores.scores.apply(lambda score: "Positive" if score > 0.6 else "Negative")
59';
60
61 EXECUTE sp_execute_external_script
62 @language = N'Python'
63 , @script = @script
64 , @output_data_1_name = N'sentiment_scores'
65 , @params = N'@text nvarchar(max)'
66 , @text = @text
67 WITH RESULT SETS (("Text" NVARCHAR(MAX),"Score" FLOAT, "Sentiment" NVARCHAR(30)));
68END
69GO
70
71
72EXECUTE [dbo].[get_sentiment] N'These are not a normal stress reliever. First of all, they got sticky, hairy and dirty on the first day I received them. Second, they arrived with tiny wrinkles in their bodies and they were cold. Third, their paint started coming off. Fourth when they finally warmed up they started to stick together. Last, I thought they would be foam but, they are a sticky rubber. If these were not rubber, this review would not be so bad.';
73GO
74
75
76EXECUTE [dbo].[get_sentiment] N'These are the cutest things ever!! Super fun to play with and the best part is that it lasts for a really long time. So far these have been thrown all over the place with so many of my friends asking to borrow them because they are so fun to play with. Super soft and squishy just the perfect toy for all ages.'
77GO
78
79USE [tpcxbb_1gb]
80GO
81DROP TABLE IF EXISTS [dbo].[models]
82GO
83CREATE TABLE [dbo].[models](
84 [language] [varchar](30) NOT NULL,
85 [model_name] [varchar](30) NOT NULL,
86 [model] [varbinary](max) NOT NULL,
87 [create_time] [datetime2](7) NULL DEFAULT (sysdatetime()),
88 [created_by] [nvarchar](500) NULL DEFAULT (suser_sname()),
89 PRIMARY KEY CLUSTERED
90 (
91 [language],
92 [model_name]
93 )
94)
95GO
96
97
98
99CREATE OR ALTER VIEW product_reviews_training_data
100AS
101SELECT TOP(CAST( ( SELECT COUNT(*) FROM product_reviews)*.9 AS INT))
102 CAST(pr_review_content AS NVARCHAR(4000)) AS pr_review_content,
103 CASE
104 WHEN pr_review_rating <3 THEN 1
105 WHEN pr_review_rating =3 THEN 2
106 ELSE 3
107 END AS tag
108FROM product_reviews;
109GO
110
111
112CREATE OR ALTER VIEW product_reviews_test_data
113AS
114SELECT TOP(CAST( ( SELECT COUNT(*) FROM product_reviews)*.1 AS INT))
115 CAST(pr_review_content AS NVARCHAR(4000)) AS pr_review_content,
116 CASE
117 WHEN pr_review_rating <3 THEN 1
118 WHEN pr_review_rating =3 THEN 2
119 ELSE 3
120 END AS tag
121FROM product_reviews;
122GO
123
124
125CREATE OR ALTER PROCEDURE [dbo].[create_text_classification_model]
126AS
127BEGIN
128 DECLARE @model varbinary(max)
129 , @train_script nvarchar(max);
130 --The Python script we want to execute
131 SET @train_script = N'
132##Import necessary packages
133from microsoftml import rx_logistic_regression,featurize_text, n_gram
134import pickle
135## Defining the tag column as a categorical type
136training_data["tag"] = training_data["tag"].astype("category")
137
138## Create a machine learning model for multiclass text classification.
139## We are using a text featurizer function to split the text in features of 2-word chunks
140
141#ngramLength=2: include not only "Word1", "Word2", but also "Word1 Word2"
142#weighting="TfIdf": Term frequency & inverse document frequency
143model = rx_logistic_regression(formula = "tag ~ features", data = training_data, method = "multiClass", ml_transforms=[
144 featurize_text(language="English",
145 cols=dict(features="pr_review_content"),
146 word_feature_extractor=n_gram(2, weighting="TfIdf"))])
147
148## Serialize the model so that we can store it in a table
149modelbin = pickle.dumps(model)';
150
151 EXECUTE sp_execute_external_script
152 @language = N'Python'
153 , @script = @train_script
154 , @input_data_1 = N'SELECT * FROM product_reviews_training_data'
155 , @input_data_1_name = N'training_data'
156 , @params = N'@modelbin varbinary(max) OUTPUT'
157 , @modelbin = @model OUTPUT;
158 --Save model to DB Table
159 DELETE FROM dbo.models WHERE model_name = 'rx_logistic_regression' and language = 'Python';
160 INSERT INTO dbo.models (language, model_name, model) VALUES('Python', 'rx_logistic_regression', @model);
161END;
162GO
163-- STEP 4 Execute the stored procedure that creates and saves the machine learning model in a table
164EXECUTE [dbo].[create_text_classification_model];
165--Take a look at the model object saved in the model table
166SELECT * FROM dbo.models;
167GO
168
169
170CREATE OR ALTER PROCEDURE [dbo].[predict_review_sentiment]
171AS
172BEGIN
173 -- text classifier for online review sentiment classification (Positive, Negative, Neutral)
174 DECLARE
175 @model_bin varbinary(max)
176 , @prediction_script nvarchar(max);
177
178 -- Select the model binary object from the model table
179 SET @model_bin = (select model from dbo.models WHERE model_name = 'rx_logistic_regression' and language = 'Python');
180
181
182 --The Python script we want to execute
183 SET @prediction_script = N'
184from microsoftml import rx_predict
185from revoscalepy import rx_data_step
186import pickle
187
188## The input data from the query in @input_data_1 is populated in test_data
189## We are selecting 10% of the entire dataset for testing the model
190
191## Unserialize the model
192model = pickle.loads(model_bin)
193
194## Use the rx_logistic_regression model
195predictions = rx_predict(model = model, data = test_data, extra_vars_to_write = ["tag", "pr_review_content"], overwrite = True)
196
197## Converting to output data set
198result = rx_data_step(predictions)';
199
200 EXECUTE sp_execute_external_script
201 @language = N'Python'
202 , @script = @prediction_script
203 , @input_data_1 = N'SELECT * FROM product_reviews_test_data'
204 , @input_data_1_name = N'test_data'
205 , @output_data_1_name = N'result'
206 , @params = N'@model_bin varbinary(max)'
207 , @model_bin = @model_bin
208 WITH RESULT SETS (("Review" NVARCHAR(MAX),"Tag" FLOAT, "Predicted_Score_Negative" FLOAT, "Predicted_Score_Neutral" FLOAT, "Predicted_Score_Positive" FLOAT));
209END
210GO
211
212-- STEP 6 Execute the multi class prediction using the model we trained earlier
213EXECUTE [dbo].[predict_review_sentiment]
214GO
215CREATE EXTERNAL FILE FORMAT PolybaseFormat
216WITH
217(
218 FORMAT_TYPE = DELIMITEDTEXT
219 , FORMAT_OPTIONS
220 (
221 FIELD_TERMINATOR = ','
222 )
223);