Microsoft SQL Server 2017 Datasheet ( 2 )

ในครั้งก่อนที่ผู้เขียนได้มาเล่าเกี่ยวกับ องค์ประกอบเด่น ๆที่เป็นตัวชูโรงให้ Microsoft SQL Server 2017 ไม่ว่าองค์ประกอบเหล่านั้นจะเป็นของใหม่แกะกล่อง หรือเป็นองค์ประกอบที่มีอยู่แล้วในเวอร์ชั่นก่อน ๆ ก็ตาม ผู้เขียนได้เขียนค้างไว้โดยยังไม่ได้อธิบายในส่วนที่วงสี่แดงไว้ในรูปภาะด้านบน ก็เลยเขียนบทความนี้เพื่อเล่าต่อให้จบ
Microsoft SQL Server 2017 Datasheet ( 2 )
ทักษะ (ระบุได้หลายทักษะ)
Data

เล่าถึง Microsoft SQL Server 2017 Datasheet พอสังเขป ตอนที่ 2

ในครั้งก่อนที่ผู้เขียนได้มาเล่าเกี่ยวกับ องค์ประกอบเด่น ๆ ที่เป็นตัวชูโรงให้ Microsoft SQL Server 2017 ไม่ว่าองค์ประกอบเหล่านั้นจะเป็นของใหม่แกะกล่อง
หรือเป็นองค์ประกอบที่มีอยู่แล้วในเวอร์ชั่นก่อน ๆ ก็ตาม ผู้เขียนได้เขียนค้างไว้โดยยังไม่ได้อธิบายในส่วนที่วงสี่แดงไว้ในรูปภาะด้านบน ก็เลยเขียนบทความนี้เพื่อเล่าต่อให้จบ

Advance Analytics at up 1M predictions/second

สร้างผลการทำนายได้รวดเร็วถึง 1 ล้านผลทำนายต่อวินาที

พอพูดถึงงานด้าน Machine Leaning ภาษา R และภาษา Python ก็จะเด้งขึ้นมาเป็นอันดับต้น ๆ
ผู้เขียนยังไม่กล้าฟันธงว่าภาษาใดดีกว่ากัน (จนกว่าจะทำความรู้จักกับทั้งสองภาษามากพอ)

ผู้เขียนสังเกตว่าบรรดา Developer, Data Scientist หรือ Data Engineer มักที่จะติดตั้ง R/Python Interpreter ไว้บนเครื่อง Desktop ของตนเอง
แล้วจึงเชื่อมต่อไปดึงข้อมูลจากแหล่งต่าง ๆ มา Binding ให้กับ Object ที่สร้างไว้ และแน่นอนว่า Microsoft SQL Server ก็เป็นหนึ่งในแหล่งข้อมูลที่นิยม

นั่นแสดงให้เห็นว่าผู้พัฒนา Script เหล่านั้นจะใช้เครื่อง Desktop ของตนประมวลผลข้อมูล
ซึ่งอาจมีทรัพยากรสำหรับประมวลผลจำกัด เช่นความเร็วและจำนวน Cores ของ CPU ขนาดความจุของ Memory และอัตรา IOPS  ที่จำกัด

นอกเหนือจากเรื่องทรัพยากรสำหรับประมวลผลแล้ว เรื่องความปลอดภัยของข้อมูลอีกล่ะ
การที่เราดึงข้อมูลออกจาก Microsoft SQL Server ไปพักไว้ที่อื่น ในรูปแบบใดก็ตาม ก็จะอยู่นอกเหนือการดูแลด้านความปลอดภัยของ Microsoft SQL Server โดยทันที

จากเหตุผลทั้งสอง Microsoft ได้เพิ่มให้มี R Interpreter มากลับ Microsoft SQL Server 2016 เลย ในชื่อของ Microsoft R Service
เพื่อให้ดึงข้อมูลจากตาราง แล้วส่งต่อไปให้ R Script ที่สามารถรันได้บน Database Engine เลย

ทรัพยากรในการประมวลผลไม่ว่าจะเป็น CPU, Memory และ IOPS ก็ใช้บนตัว Database Engine
ซึ่งแน่นอนว่าได้รับการจัดสรรอย่างดีและเพียงพอ อีกทั้งเป็นการประมวลผลภายในตัว Database Engine
การควบคุมด้านความปลอดภัยยังคงทำได้อย่างแน่นอน
 

พัฒนาการของ Microsoft R Service

พัฒนาการของ Microsoft R Service

นอกเหนือจากการผนวก R Interpreter เข้ากับ database engine ที่เรียกว่า R Service แล้ว
ยังสามารถติดตั้ง R Interpreter ที่ทำงานแบบ Server รองรับการสั่งรันแบบ Remote ในชื่อ Microsoft R Server

Microsoft SQL Server 2017 ได้เพิ่ม Python เป็นภาษาที่ 2 เป็นทางเลือกสำหรับงาน Data Analytics

สำหรับ Microsoft SQL Server 2017 นั้นได้เพิ่ม Python เป็นภาษาที่ 2 เป็นทางเลือกสำหรับงาน Data Analytics
โดยเปลี่ยนชื่อจาก Microsoft R Services ไปเป็น Microsoft Machine Learning Services
และเปลี่ยนชื่อจาก Microsoft R Server ไปเป็น Microsoft Machine Learning Server
ในปีนี้ Microsoft ได้ออกข่าวว่าจะทำให้ Azure SQL Database สามารถรัน R Script ได้อีกด้วย
 

เราใช้ภาษา R หรือ Python ทำอะไรกับข้อมูล

เราสามารถใช้ภาษาทั้งสองเพื่อทำ Exploration Data Analysis หรือการสำรวจข้อมูล
ภาษาทั้งสองมีฟังก์ชันให้เลือกใช้มากมายเพื่อนำมาอธิบายลักษณะข้อมูลของเราได้อย่างละเอียด หรือใช้เพื่อทำ Data Visualization

แม้ว่า Microsoft SQL Server หรือแม้แต่ Power BI จะมีเครื่องไม้เครื่องมือในการทำ Data Visualization อยู่แล้ว
แต่ภาษาทั้งสองอาจมีส่วนที่เครื่องไม้เครื่องมือเดิมของเราไม่มี หรือมีแต่ประสิทธิภาพแย่ ก็สามารถนำเอาการ Plot ในภาษาทั้งสองมาเสริมได้
และสุดท้ายเราใช้ความสามารถของภาษาทั้งสองในการสร้างโมเดลการทำนายขึ้นมาใช้งาน ซึ่งในจุดนี้เองที่เป็นส่วนสำคัญของ Machine Learning
 

ทดลองใช้ Microsoft Machine Learning Services บน Microsoft SQL Server 2017

  • ก่อนอื่นผู้เขียนได้ทำการเลือก Machine Learning Services (In-Database) แล้วเลือก Interpreter ของภาษาที่ต้องการใช้งานขณะทำการติดตั้ง Microsoft SQL Server 2017
Microsoft Machine Learning Services บน Microsoft SQL Server 2017
  • เมื่อติดตั้งเสร็จ ให้รัน Script ต่อไปนี้เพื่อเปิดให้สามารถเรียก External Scripts ได้  
  • USE master;
    GO
     
    EXEC sp_configure 'external scripts enabled', 1;
    RECONFIGURE WITH OVERRIDE;
     

             

  • เราสามารถตรวจสอบว่ามีการตั้งค่าตาม Script ข้างบนหรือยังโดย


 

  • USE master;
    GO
     
    SELECT name, description, value
    FROM sys.configurations
    WHERE name = 'external scripts enabled' ;
     


 

  • หากคอลัมน์ Value มีค่าเป็น 1 แสดงว่าเปิดคุณสมบัติแล้ว

  • ผู้เขียนจะทำการปรับปรุงและเพิ่ม Packages ของ R

  • เนื่องจาก Machine Learning Services (R Service) ได้ผนวกกับ database engine 
  • ดังนั้นจึงต้องเรียก Rgui จากภายใน Path ของ SQL Server Instance คือ C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\bin\x64
  • จากนั้นเรียก Rgui ขึ้นมาใช้งาน
ทำการปรับปรุงและเพิ่ม Packages ของ R

เลือกเมนู File>New Script แล้วนำ Script ต่อไปนี้วางลงใน R Editor

 


# ส่วนที่ 1 ตรวจสอบ path ของ package library
.libPaths()
# ส่วนที่ 2 ดู packages ที่ติดตั้งแล้ว
rownames(installed.packages())
# ส่วนที่ 3 ติดตั้ง packages จาก CRAN
packages <- c("ggplot2", "reshape2", "unbalanced")
for (p in packages)
    {
        if (!(p %in% rownames(installed.packages()))) { install.packages(p) }
    }
 


จากนั้นไฮไลท์ทีละส่วนแล้วคลิกขวาเลือก Run Line or Selection ดังภาพ

Script ใน R Editor

ผลลัพธ์จากการรันส่วนที่ 1 ควรเป็น Path ที่เก็บ Packages อยู่
และผลลัพธ์จากการรันส่วนที่ 2 จะแสดงรายการ Packages ที่ติดตั้งอยู่ ดังภาพ

พบ Packages หลายตัวที่เป็นของ Microsoft เอง

จะพบ Packages หลายตัวที่เป็นของ Microsoft เอง ตัวที่สำคัญมากคือ RevoScaleR
โดย Package นี้มีหลายกลุ่มของ Function ที่ออกแบบมาเพื่อเอื้อต่องาน Analytics
และสามารถทำงานได้ทั้งแบบ Locally และ Remote Compute

โดยมี Workflow ของการทำงานดังนี้

Workflow ของการทำงาน

โดยจัดแบ่งกลุ่มของฟังก์ชันได้ 10 กลุ่มดังนี้
 

  • Data source functions
  • Import and save-as
  • Data transformation
  • Graphing functions
  • Descriptive statistics
  • Prediction functions
  • Compute context functions
  • Distributed computing
  • Utility functions
  • Package management

ผู้อ่านสามารถติดตามอ่านได้จากลิงก์ของ Microsoft นี้
https://docs.microsoft.com/en-us/machine-learning-server/r-reference/revoscaler/revoscaler

ผลลัพธ์จากการรันส่วนที่ 3 จะมี Dialog มาถามว่าจะติดตั้ง Packages
โดยจะบันทึกลง Personal Library แทนให้คลิก Yes
หากไม่มีอยู่จะมี Dialog ขึ้นมาขอสร้าง Folder ให้คลิก Yes
 

  1. ลองทดสอบใช้ R บน Machine Learning Services (R Service)

โดยเปิด SQL Server Management Studio ขึ้นมาแล้ว New Query
หลังจากนั้นนำ Script ต่อไปนี้ไปวาง แล้ว Execute
 


USE master;
GO
 
EXEC sp_execute_external_script
              @language=    N'R'
       ,      @script=      N'
                                  Result<-(InputDataSet+5)/2+3
                                  OutputDataSet<-Result
                                  '
       ,      @input_data_1 = N'SELECT 5';
 

 
จะได้ผลลัพธ์เป็น 8

โดยการทำงานของ System Stored Procedure ชื่อ sp_execute_external_script
จะนำ Result Set ที่ได้จากพารามิเตอร์ชื่อ @input_data_1 ไป Binding ให้กับ Object ชนิดตัวเลขชื่อ InputDataSet ใน Script ภาษา R
จากนั้นเมื่อคำนวณเสร็จ ก็ส่งไปยัง Object ชนิดตัวเลขชื่อ Result
จากนั้นส่งต่อไปยัง Object ชื่อ OutputDataSet ก็จะคืน Result Set ที่เป็นคำตอบออกมา

ทดลองสร้าง Predictive Model บน Microsoft Machine Learning Services
ผู้เขียนพบ Tutorial ของ Microsoft พร้อมฐานข้อมูลตัวอย่าง
จากลิงก์ https://microsoft.github.io/sql-ml-tutorials/R/rentalprediction/step/3.html
จึงได้นำมาดัดแปลงเพื่อให้ง่ายต่อความเข้าใจ โดยผู้เขียนจะทำการแบ่งข้อมูลออกเป็นสองส่วน

ส่วนแรกใช้เป็น Training Data และส่วนที่สองจะเป็นส่วนที่นำมาทดสอบ

  1. ดาวน์โหลดไฟล์ Backup ของฐานข้อมูลตัวอย่างจากลิงก์ https://sqlchoice.blob.core.windows.net/sqlchoice/TutorialDB.bak

จากนั้นทำการ Restore ให้เรียบร้อย
เรียกดูข้อมูลจากตาราง dbo.rental_data ด้วยสคริปต์ต่อไปนี้
 

 
USE TutorialDB;
GO
 
SELECT *
FROM dbo.rental_data
ORDER BY Year,Month,Day;
 

 
จะได้ Result Set แบบนี้

ข้อมูลจากตาราง dbo.rental_data

ซึ่งเป็นข้อมูลปริมาณการเช่าอะไรซักอย่างในแต่ละวัน
โดยมีการเก็บสถิติเอาไว้ในคอลัมน์ต่อไปนี้
 

ชื่อคอลัมน์ความหมาย
RentalCountจำนวนการเช่า
WeekDayเลขวันในสัปดาห์
Holiday1=วันหยุด 0=วันทำงาน
Snow1=หิมะตก 0=หิมะไม่ตก

 
ส่วนคอลัมน์ที่ขึ้นต้น F คือ FWeekDay, FHoliday และ FSnow จะใช้สำหรับ Object ชนิด Factor ใน R Script
 

  1. ผู้เขียนจะทำการแบ่งข้อมูลออกเป็นสองตาราง

โดยตารางแรกให้ชื่อว่า dbo.rental_training_data เป็นข้อมูลของปี 2013-2014 ใช้เป็น Training Data
เพื่อสร้าง Model และอีกตารางให้ชื่อว่า dbo.rental_testing_data เป็นข้อมูลของปี 2015 ใช้เพื่อทดสอบ
โดยทำการรัน Script ต่อไปนี้

 
USE TutorialDB;
GO
 
SELECT *
INTO dbo.rental_training_data
FROM dbo.rental_data
WHERE Year<2015
ORDER BY Year,Month,Day;
GO
 
SELECT *
INTO dbo.rental_testing_data
FROM dbo.rental_data
WHERE Year=2015
ORDER BY Year,Month,Day;
GO
 
  1. สร้างตารางเพื่อเก็บข้อมูล Model ชื่อ dbo.rental_models โดยทำการรัน Script ต่อไปนี้
USE TutorialDB;
GO
 
CREATE TABLE dbo.rental_models
(
       model_name VARCHAR(30) NOT NULL DEFAULT('default model')
,      lang VARCHAR(30)
,      model VARBINARY(MAX)
,      native_model VARBINARY(MAX)
 
PRIMARY KEY (model_name, lang)
);
GO
 

 
4.จากนั้นสร้าง Stored Procedure สำหรับสร้าง Model โดยทำการรัน Script ต่อไปนี้


USE TutorialDB;
GO
 
CREATE PROCEDURE generate_rental_R_native_model
          @model_type varchar(30)
,         @trained_model varbinary(max) OUTPUT
AS
BEGIN
    EXECUTE sp_execute_external_script
                   @language = N'R'
    ,     @script =
                   N'
                   require("RevoScaleR")
 
                   rental_train_data$Holiday = factor(rental_train_data$Holiday);
                   rental_train_data$Snow = factor(rental_train_data$Snow);
                   rental_train_data$WeekDay = factor(rental_train_data$WeekDay);
 
                   if(model_type == "linear")
                             {
                             model_dtree <- rxDTree( RentalCount ~ Month+Day+WeekDay+Snow+Holiday
                                                   , data = rental_train_data
                                                                                     
                                                   );
                             trained_model<-rxSerializeModel(model_dtree, realtimeScoringOnly = TRUE);
                             }
 
                   if(model_type == "dtree")
                             {
                             model_linmod <- rxLinMod (
                                                           RentalCount ~ Month+Day+WeekDay+Snow+Holiday
                                                          , data = rental_train_data
                                                          );
                             trained_model<-rxSerializeModel(model_linmod, realtimeScoringOnly = TRUE);
                             }
                   '
 
    ,     @input_data_1 =
                             N'SELECT "RentalCount", "Year", "Month", "Day", "WeekDay", "Snow", "Holiday"
                             FROM dbo.rental_training_data'
    ,     @input_data_1_name = N'rental_train_data'
    ,     @params = N'@trained_model varbinary(max) OUTPUT, @model_type varchar(30)'
          ,         @model_type = @model_type
    ,     @trained_model = @trained_model OUTPUT;
END;
GO
 

 
จะเห็นว่าพารามิเตอร์ชื่อ  @input_data_1 นั้นดึงข้อมูลจากตาราง dbo.rental_training_data  ที่เตรียมไว้
 

  1. จากนั้นจึงทำการรัน Stored Procedure เพื่อนำ Training Data ไปสร้าง Model โดยทำการรัน Script ต่อไปนี้
USE TutorialDB;
GO
 
DECLARE @trained_model VARBINARY(MAX);
EXEC generate_rental_R_native_model 'linear', @trained_model OUTPUT;
INSERT INTO rental_models (model_name, native_model, lang)
VALUES('linear_model', @trained_model, 'R');
 
 
DECLARE @trained_model2 VARBINARY(MAX);
EXEC generate_rental_R_native_model 'dtree', @trained_model2 OUTPUT;
INSERT INTO rental_models (model_name, native_model, lang)
VALUES('dtree_model', @trained_model2, 'R');
 
  1. สืบค้นข้อมูลจากตาราง dbo.rental_models จะพบข้อมูล Model ที่สร้างเสร็จ หน้าตาแบบนี้
สืบค้นข้อมูลจากตาราง dbo.rental_models
  1. นำ Model  ทั้งสองไปทำนายข้อมูลทดสอบกัน โดยในการทดลองแรกจะใช้ Decision Tree Model ในการทำนายโดยทำการรัน Script ต่อไปนี้
 
USE TutorialDB;
GO
 
DECLARE @trained_model VARBINARY(MAX) = (
                                      SELECT native_model
                                      FROM dbo.rental_models
                                      WHERE model_name = 'dtree_model'
                                        );
SELECT d.*, p.* FROM PREDICT(MODEL=@trained_model, DATA=dbo.rental_testing_data AS d) WITH(RentalCount_Pred float) AS p;
GO
 


 

จาก Script จะเห็นว่าผู้เขียนทำการทดสอบกับตาราง dbo.rental_testing_data และผลลัพธ์ที่ได้คือ
 

 


 

ผลลัพธ์การทดสอบกับตาราง dbo.rental_testing_data

คอลัมน์ RantalCount คือข้อมูลจริงในปี 2015 ส่วนคอลัมน์ RantalCount_Pred ใช้ข้อมูลจากคอลัมน์ WeekDay,Holiday
และ Snow ไปผ่าน Model ที่ถูก Trained มาจากข้อมูลปี 2013-2014 โดย Model ที่ใช้คือ Decision Tree Model

  1. จากนั้นผู้เขียนทดลองใช้อีก Model คือ Linear Regression Model ในการทำนายโดยทำการรัน Script ต่อไปนี้
 
USE TutorialDB;
GO
 
DECLARE @trained_model VARBINARY(MAX) = (
                                      SELECT native_model
                                      FROM dbo.rental_models
                                      WHERE model_name = 'linear_model'
                                        );
SELECT d.*, p.* FROM PREDICT(MODEL=@trained_model, DATA=dbo.rental_testing_data AS d) WITH(RentalCount_Pred float) AS p;
GO
 


ผลลัพธ์ที่ได้ คือ
 

ทดลองใช้ Model Linear Regression

ดูจากสายตาคร่าว ๆ ผู้เขียนคิดว่า Linear Regression Model จะมีความแม่นยำกว่า Decision Tree Model
สำหรับการทดสอบในครั้งนี้
 

สรุปเกี่ยวกับ Machine Learning Services

Advance Analytics at up 1M predictions/second

ผู้เขียนคงไม่ทดลองบนภาษา Python ให้ดู เพราะลำดับของงานไม่ต่างกัน
จากที่ได้แสดงให้เห็นแล้วว่า R/Python Interpreter นั้นผนวกอยู่ใน database engine
เลยทำให้สามารถดึงข้อมูลจากฐานข้อมูลส่งให้กับ Script แล้วนำไปใช้ทำนายผลได้เลย

ข้อมูลทั้งที่นำมา Train และนำมา Test มักมีความซ้ำอยู่มาก

สำหรับข้อมูลทั้งที่นำมา Train และนำมา Test มักมีความซ้ำอยู่มาก ดังภาพข้างบน

หากเลือกที่จะจัดเก็บลงในตารางที่มีโครงสร้างแบบ Clustered Columnstore Index ก็ยิ่งจะเพิ่มความเร็วในการสืบค้นได้อีก 100 เท่า
และเมื่อได้ผลการทำนาย ก็นำผลการทำนายใส่ลงในตารางแบบ Memory-Optimized Table ก็จะทำความเร็วในการ Insert ได้อีก
การนำผลไปแสดงแบบ Visualize อาทิบน Power BI ก็ทันอกทันใจ

ยังเหลืออีกสองเรื่องที่ผู้เขียนยังไม่ได้พูดในบทความนี้ คือ Graph Data
และการนำเสนอรายงานที่หลากหลาย Platform มากขึ้น ขอยกไปเป็นตอนที่ 3 แทนครับ
 

บทความโดย

อาจารย์ภัคพงศ์ กฤตวัฒน์
  • วิทยากรผู้ดูแลและออกแบบหลักสูตร
    • กลุ่มวิชา SQL Server/Window Server
  • Microsoft SQL Server Specialist
  • Microsoft Certified Trainer (2002-Present)
  • Co-Founder at Data Meccanica Co., Ltd.
Tags