content top
Machine learning ו R שלב אחר שלב ב SQL 2016 הלכה למעשה ל DBA ולמפתח, חלק ג

Machine learning ו R שלב אחר שלב ב SQL 2016 הלכה למעשה ל DBA ולמפתח, חלק ג

קוראים יקרים אנו נמשיך במסע שלנו עם SQL 2016 R services . המאמרים הקודמים נכתבו עבור גרסת הבטא של SQL וכלן עדכנתי את נושא ההתקנות וכלי הפיתוח . למאמר מצורף script R בשם SQL 2016 Decision tree with R Demo.r .

כלי הפיתוח

ב SQL 2016 אין יותר שימוש ב BID ( Business Intelligence development studio) .ה- DBA והמפתח צריכם להשתמש ב SQL Server Data Tools (SSDT) . הוא אינו מצריך התקנה של Visual Studio. התקנה של SSDT מתקינה Integrated Shell של VS 2015 מאפשר לפתח פרויקטים של SSIS , SSRS ו SSAS מסוג Tabular Model ו Multidimensional . כמו SSMS של SQL 2016 , ההתקנה של SSDT נפרדת מהתקנה של SQL 2016 כדי לאפשר עדכונים ברמה חודשית . גרסה עדכנית ניתן להוריד בכתובת

https://msdn.microsoft.com/en-us/mt186501.

SSDT מלבד SQL 2016 תומך בגרסאות SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 ו- Server 2014 . איך זה קורה ? נניח ופתחנו פרויקט מסוג Report Server Project ניגש ל ב project’s properties ונשנה את TargetServerVersion property לגרסה המתאימה . שימו לב שאם פתחנו פרויקט של SSRS עבור SQL 2016 , ושמתם אובייקטים של SSRS 2016 כמו Tree map ואח"כ נשנה את ונשנה את TargetServerVersion property ל SQL 2014 , הפרויקט יוצא הודעות שגיאה .

image

התקנות של R למפתח ועבור SQL 2016 שכולל R Server ואת הרכיב R services .

בשביל לעבוד עם R אלה ההתקנות הנדרשות בשרת SQL Server שכולל את R Services ובעמדת הקצה של המפתח .

· R For windows מתוך CRAN Time Machine ובקיצור MRAN של מיקרוסופט. מהקישור : https://mran.microsoft.com/timemachine/#. ה Packages ב CRAN , שהוא ה Repository של כול ה Packages של R באינטרנט משתנים ע"ב יומי . ל R יש אלפי Packages . ל Package יש בד"כ תלויות ב Packages אחרים , לפעמים תלות בעשרות packages . אם ה packages לא מאותה נקודת זמן יכולות להיות בעיות בהרצה של R . זו אחת מהבעיות הגדולות של R . מיקרוסופט לוקחים ל CRAN , snapshot ע"ב יומי ומכאן השם MRAN . ניתן לבחור snapshot של כל ה packages של R לנקודת זמן מסוימת מתוך ה MRAN ולהורידם (אגב גם בקוד עצמו) .

· Microsoft R open & MKL – MRO הוא open source וחינם עם תאימות מלאה אבל הרחבה של R לטובת ביצועים, תמיכה בפלטפורמות שונות . מהקישור : https://mran.microsoft.com/timemachine/#.

· R Studio שהוא סט כלים ו IDE שמאשפר לעבוד ביעלות מול R , הגרסה הבסיסית היא חינם . מהקישור : https://www.rstudio.com/

· R for Visual Studio – תוסף חינם שמאפשר עבודה עם R , מתוך VS . אופציונאלי אם מתכוונים לפתח מתוך VS . מהקישור : https://www.visualstudio.com/vs/rtvs/

· התקנה של R server אופציונאלי למפתח וחלק מהתקנה של SQL 2016 R services גרסה עדכנית : https://msdn.microsoft.com/en-us/library/mt695942.aspx . אם השרת מנותק מהאינטרנט יש לשים את הקבצים במחיצה כלשהי בשרת בעת ההתקנה

Data Mining עם SSDT

מיקרוסופט לא רוצה להמשיך ולתמוך בעבודה עם Data Mining דרך ה או SSDT אלא דרך ה AZURE ML . הנקודה היא שלא לכולם יש גישה Cloud . למרות זאת מניסיון בשלב הראשון נשתמש ב SSDT לצורך בנית פרויקט של data mining . למה ? הכלי הוא אינטואיטיבי , קל לשימוש , טוב למתחילים, מהיר ומשמש נקודת התחלה טובה. בתור דוגמא נבנה פרויקט עבור Decision Trees SSDT . כל השלבים בפירוט רב באחד המקורות הבאים :

1. בהרצאה שלי Practical SQL 2014/2016 Data Mining and introduction to R ב SQL Saturday 2016

2. ב utube בכתובת https://www.youtube.com/watch?v=x4hGjYGBfkc תחת http://globalhebrew.sqlpass.org .

 

הדוגמאות הן על SQL 2014 . אין בעיה להסב את הפרויקט ל SQL 2016 . בסיס הנתונים יהיה AdventureWorksDW2016CTP3 , אותו ניתן להוריד מ https://www.microsoft.com/en-us/download/details.aspx?id=49502. מזכיר לכם שב SQL 2016 אין יותר דוגמאות של AdventureWorks . את הדוגמאות נבצע ב SSDT בו נפתח פרויקט מסוג Analysis Services and Multidimensional and data Mining Project .

R Studio וסנכרון packages ללא חיבור לרשת האינטרנט

 

נפתח את R Studio . בתפריט בחר ב Tool->Global Options . נוודא שתחת Option עבור R Version הנתיב הוא

[Default] [64-bit] D:\Program Files\Microsoft\MRO\R-3.2.5. נלחץ כעל OK .image

נניח ואנו רוצים לסנכרן מחשבים מנותקי רשת עם packages וה packages התלויים. ברשת האינטרנט נשתמש install.packages והפרמטר dependencies = TRUE . ה package וכל ה packages התליים ירדו מתוך MRAN למחיצה library כקבציי ZIP ויפתחו . דוגמא : נבצע התקנה של ה Package בשם RODBC כולל ה packages שתלויים בו בעזרת הקוד הבא R מתוך R Studio .

> # Install RODBC library

> install.packages("RODBC", dependencies = TRUE)

1. במחשב ברשת המנותקת נשנה את שם המחיצה D:\Program Files\Microsoft\MRO\R-3.2.5\library למשל ל libarary_orig .

2. יש להעתיק את המחיצה D:\Program Files\Microsoft\MRO\R-3.2.5\library מהמחשב באינטרנט למחשב המנותק .

.

יצירת ODBC

בשלב הראשון ניצור DSN ע"י ODBC בשם ADW לבסיס הנתונים AdventureWorksDW2016CTP3 בשרת SQL . רצוי שההתחברות תהיה דרך ה login הייעודי עבור R (rdemo) , ראו את ההגדרה שלו בחלקים הקודמים .

עצי החלטה ב R מול SQL Server

לטובת תמיכה בעברית נוסיף את השורה הבאה :

Sys.setlocale(category = "LC_ALL", locale = "Hebrew_Israel.1255")

נתקין את הספרייה RODBC עם כול התלויות , במידת הצורך .

install.packages("RODBC", dependencies = TRUE)

נטען את ספריה RODB , נתחבר ל SQL Server דרך ה DSN , ע"י אוביקט מסוג odbcConnect בשם con .

library(RODBC)
con <- odbcConnect("ADW", uid="Rdemo", pwd="Password1!")

ניצור Data Frame Tree_Model_DF , נטען אליו את הנתונים מה view vTargetMail , ונסגור את הקשר שיצרנו מול ה DB

# Read data from vTargetMail view
Tree_Model_DF <- as.data.frame(sqlQuery(con,
"SELECT CustomerKey, MaritalStatus, Gender,
TotalChildren, NumberChildrenAtHome,
EnglishEducation AS Education,
EnglishOccupation AS Occupation,
HouseOwnerFlag, NumberCarsOwned, CommuteDistance,
Region, BikeBuyer
FROM dbo.vTargetMail"), stringsAsFactors = FALSE)

# Close connection
close (con)

ב R studio תוכלו לראות את הנתונים של ה Data frame בשם Tree_Model_DF בחלון שיפתח .

image

נוציא סיכום על הנתונים בעזרת הפקודה summary .

summary(Tree_Model_DF)

בפלט נוכל לראות מידע על כל העמודות ב view ערך מינימאלי ומקסימאלי , ממוצע (mean) , חציון (Median) עם מספר שווה של ערכים מעליו ומתחתיו , באותו אופן הרבעון ראשון ( 1st QU) שהוא ערך שעד אליו 1/4 ומעליו 3/4 מהמקרים , והרבעון השלישי ( 3st QU) .

image

נוציא סיכום על הנתונים בעזרת הפקודה str .

str(Tree_Model_DF)

נראה שיש לנו 18484 תצפיות ( 18484 obs) ו 14 משתנים (12 variables) שהם למעשה העמודות בטבלה . נוכל לראות שיש לנו שהם משתנים מסוג קטגוריה . Factor אומר כמה ערכים אפשריים עבור משתנה זה. למשל למשתנה MaritalStatus שני ערכים אפשריים : M או S . לרשימת הערכים האפשריים עבור משתנה קטגורי , R נותן ערך מספרי רץ אחד החל מ 1 . בדוגמא שלנו 1 עבור M ו 2 עבור הערך S .

image

במאמר הבא בהמשך נטפל בהרצה של קוד R דרך שרתי R ו SQL Server בצורה יעילה. כרגע עלינו להבין R בגרסה הפתוחה לא יודע לעבוד בצורה יעילה ע"י ניצול נכון של כל הליבות במחשב, עבודה בצורה מקבילית , קריאה של כל הנתונים לזיכרון במקום קריאה באצווה של מידע . אם ננסה להריץ עץ החלטה דרך R על כמויות נתונים גדולות מאוד , המחשב "ייחנק" ונקבל את הודאת השגיאה הבאה . דוגמא :

Error: cannot allocate vector of size 2.6 Gb
In addition: Warning messages:
1: In cbind(RET, tr[[i]]) :
Reached total allocation of 7986Mb: see help(memory.size)
….

בשביל למצוא את הממוצע של העמודה NumberCarsOwned נשתמש בפונקציה colMeans באופן הבא :

colMeans(Tree_Model_DF["NumberCarsOwned"])

פלט :

NumberCarsOwned
1.502705

בשביל לבצע  group by Gender, Education select customerKey from vTargetMail  נשתמש בפונקציה  aggregate באופן הבא :

aggregate(CustomerKey ~ Gender + Education, data=Tree_Model_DF, FUN=length)

פלט :

   Gender           Education CustomerKey
1       F           Bachelors        2628
2       M           Bachelors        2728
3       F     Graduate Degree        1611
4       M     Graduate Degree        1578
5       F         High School        1595
6       M         High School        1699
7       F     Partial College        2542
8       M     Partial College        2522
9       F Partial High School         757
10      M Partial High School         824

ב Machine Learning (ML) עלינו לקבוע חלק מהנתונים לבדיקה ופיתוח , חלק לאימון והשאר לטובת אימות של המודל . נקבע seed . בדוגמא 9850. הפונקציה nrow מחזירה את מספר הרשומות ב Tree_Model_DF , והוא גם מספר הרשומות ב view vTargetMail . נבצע הגרלה של מספרים כמספר הרשומות ב Tree_Model_DF לתוך משתנה בשם g ע"י הפונקציה runif . המספרים המוגרלים הם מ 0 עד 1 . נמיין את Data Frame Tree_Model_DF לפי g ע"י הפונקציה order ונציב אותם ב Data Frame חדש בשם Tree_Model_DFr . הפעולה הזו נקראת ערבוב (shuffle) . המטרה היא שהרשומות יהיו מפוזרות בצורה אקראית ולא למשל ע"פ סדר השליפה מ SQL Server .

set.seed(9850)
g<-runif(nrow(Tree_Model_DF))
Tree_Model_DFr<-Tree_Model_DF[order(g),]

נתקין את הספרייה party במידת הצורך . נטען את הספרייה party , ונריץ עליה את CTREE . משתנה המטרה הוא המשתנה קטגורי BikeBuyer כלומר האם הלקוח יקנה או לא יקנה אופניים. הסימון ~. . משמעות הסימון הכנס את כל שאר העמודות ב view vTargetMail כמשתני כניסה למודל עץ ההחלטה . אנחנו נשתמש ב 1000 הרשומות הראשונות בלבד לצורך בדיקה ולכן הפרמטר data של ctree מקבל Tree_Model_DFr[1:1000,] . הפונקציה plot תדפיס את עץ ההחלטה .

TM_DT <- ctree(BikeBuyer ~ . , data= Tree_Model_DFr[1:1000,])
# Show the results
plot(TM_DT, type = "simple")

נקבל את עץ ההחלטה הבא ב R Studio . העץ די קריא וברור .

image

הפונקציה view תאפשר לנו לראות אל כל הנתונים של Tree_Model_DF כשורות ב R studio .

View(Tree_Model_DF)

בדוגמא הבאה נגדיר כמשתני קלט את העמודות NumberCarsOwned ו Region קטע. במקרה זה לא משתמשים בסימון ~. ואנו רושמים את משתני הקלט עם הסימן + . בדוגמא הבאה משתני הקלט הם NumberCarsOwned ו Region . הפעם אנו מריצים את עץ ההחלטה על כל הנתונים כי data = Tree_Model_DFr .

> TM_DT1 <- ctree(BikeBuyer ~ NumberCarsOwned + Region,
+                 data = Tree_Model_DFr)
>
> # Show the results
> plot(TM_DT1, type = "simple"

אפשר לבצע דבר דומה עם Package בשם rpart ואלגוריתם בשם CART . אבל זה בהזדמנות אחרת .

הפעלה של Rattle

בכול מקרה , מי שרוצה לעבור עם ממשק גרפי עבור R לטובת ML ויצירת קוד ב R באופן דרך הכלי , יכול להשתמש ב R באופן הבא . גם על הכלי הזה יש הסבר במקורות מקודם .

 

 

 

 

 

 

 

 

 

library (rattle)
rattle()

image

סקריפט להורדה


כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

two × four =