content top
Server R Services הלכה למעשה ל DBA – חלק ב

Server R Services הלכה למעשה ל DBA – חלק ב

בחלק הזה נמשיך לעבוד ולהכיר את R וההתקנה שלו על SQL 2016 CTP 3.2 .

Data Frame הוא אובייקט שתפקידו להחזיק סט של נתונים כלומר dataset. הוא מורכב ממספר וקטורים בעלי אותו אורך , שמוצגים בצורה אופקית . כל הערכים ששיכים לאותה עמודה ב Data Frame חייבים להיות מאותו סוג . עמודות שונות יכולות להחזיק סוגי מידע שונים כמו מספרים או מחרוזות . Data Frame הוא אידיאלי להחזיק datasets כאשר כל עמודה מחזיקה משתנה והשורה את רשימת הערכים , ממש כמו טבלה ב RDBMS שבנויה מעמודות ושורות .

ניצור וקטור בשם X עם הערכים 1 עד 6 באופן הבא . שימו לב לרישום המקוצר : ערך מינימאלי ,נקודתיים וערך מקסימאלי . שימו לב R הוא case sensitive והשתמשתי ב Capital Letter עבור X .

X <-1:6

נציג אותו

> X

[1] 1 2 3 4 5 6

נוכל לחשב את סכום הערכים של X ע"י הפונקציה SUM : פתח סוגרים, שם הווקטור , סגור סוגריים .

> sum(X)

[1] 21

נגדיר משתנה בשם Y ונכניס לו את הערך 2 .

> Y<-2

נוכל לחשב את הסכום של X+Y באופן הבא

> X+Y

[1] 3 4 5 6 7 8

שימו לב שלמרות ש Y מכיל איבר 1 , R יבצע "השלמה" של וקטור Y למימד של וקטור X בפעולת החיבור , וישכפל את הערך 1 של Y שש פעמים , כי X מכיל שש איברים .

כאמור יצרנו וקטור בשם temperatures :

> temperatures<-c(3, 3.76, -0.35, 1.2, -5)

> temperatures

[1] 3.00 3.76 -0.35 1.20 -5.00

ניצור וקטור חדש בשם observation_day , ונכניס בו את ימי השבוע א עד ה .

> observation_day<-c("Su","Mo","Tu","We","Th")

> observation_day

[1] "Su" "Mo" "Tu" "We" "Th"

בשביל לתת שמות למשתנים , נשתמש בפונקציה names . בדוגמא נשנה את שמות כל 5 המשתנים ב dataset ששמו temperatures במקביל . השמות עבור המשתנים יילקחו מהווקטור observation_day.

> names(temperatures)<-observation_day;

אם נציג עכשיו את ה data frame temperatures נקבל את המידע בצורה טבלאית . שם משתנה ומתחתיו הערך המתאים .

> temperatures

Su Mo Tu We Th

3.00 3.76 -0.35 1.20 -5.00

הפונקציה names מציגה את שמות המשתנים עבור ה data frame temperatures באופן הבא :

> names(temperatures)

[1] "Su" "Mo" "Tu" "We" "Th"

יש להקפיד שמספר שמות המשתנים יהיה זהה למספר העמודות .

ניצור משתנה חדש בשםtotal_temp_sum שמכיל סיכום של כל התצפיות . נשתמש בפונקציה sum , פתח סוגריים , שם הווקטור temperatures , סגור סוגריים וחץ שמפנה לשם המשתנה החדש total_pos_temp_sum . הערך שיתקבל הוא 3.00ועוד 3.76ועוד -0.35 ועוד 1.20ועוד -5.00 . בסה"כ 2.61.

> total_temp_sum<-sum(temperatures)

> total_temp_sum

[1] 2.61

ניצור משתנה חדש בשם total_pos_temp_sum . הפעם נתחכם . הוא יהיה סיכום של כל התצפיות החיוביות כלומר : 3.00 ועוד 3.76 ועוד 1.20 בסה"כ 7.96 . שימו לב לסינטקס שמסננן את הערכים החיוביים ווקטור temperatures ומבצע עליהן סיכום .

> pos_temp_sum<-sum(temperatures[temperatures>0])

> pos_temp_sum

[1] 7.96

ל R יש dataset-ים מובנים לצורך תרגול . למשל Puromycin . את התוכן שלו נראה באופן הבא :

> Puromycin

בשביל לפנות למשתנה מסוים נשתמש בסימן $ ולאחריו שם המשתנה . דוגמא . הפלט יהיה הערך של העמודה rate ב dataset Puromycin .

> Puromycin$rate

[1] 76 47 97 107 123 139 159 152 191 201 207 200 67 51 84 86 98 115 131 124 144 158 160

בשביל לפנות לחלק מסוים ב data frame נשתמש בסוגריים מרובעים באופן הבא :

> dataset[r,c]

בשביל לבחור בשורה השישית , ובעמודה השנייה

> Puromycin[6,2]

[1] 139

אפשר לבחור בכול שורה 6 , כאשר מספר העמודה יהיה ריק באופן הבא :

> Puromycin[6,]

conc rate state

6 0.11 139 treated

בשביל לבחור בעמודה 2 בלבד , נשאיר את מספר השורה ריק הפעם :

> Puromycin[,2]

[1] 76 47 97 107 123 139 159 152 191 201 207 2

בשביל לבחור רק בעמודה 2 . אפשר לוותר על הפסיק ולרשום רק את מספר העמודה :

> Puromycin[2]

הנה צילום מסך של כל הפלט :

אפשר להכניס וקטורים בתור שמות של שורות ועמודות :

> rownum<-c(6,8,14)

> colnum<-2

> Puromycin[rownum,colnum]

[1] 139 152 51

או אפילו פונקציות :

> Puromycin[sqrt(25),]

אפשר לפנות לכניסה מתאימה ע"י שילוב של שם משתנה וסוגריים מרובעים . למשל נפנה לתצפית העשירית של העמודה $rate :

> Puromycin$rate[10]

[1] 201

צילום המסך המתאים :

מחיצת העבודה

בשביל לראות מה מחיצת העבודה נשתמש בפקודה

> getwd()

[1] "C:/Users/Documents"

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

> setwd("C:/SQL2016/RWS")

התקנה של Packages

כאמור ל R יש כמה אלפי packages , כמעט בכול תחום אפשרי שנכתבו ע"י הקהילה . יש לגשת לאתר של פרויקט R בכתובת www.r-project.org . ללחוץ על הקישור CRAN מצד שמאל . תגיעו לאתר שיציג את האתרים שמכילים את כל קבצי ההתקנה של ה packages . תבחר באחד האתרים, תלחצו אליו ותגיעו לדף עם רשימת כל ה packages , מסודרים לפי א ב . משם תבחרו package מתאים ולא תבצעו download .

בשביל לראות איזה packages מותקנים נשתמש בקטע קוד הבא . אנו משתמשים בפונקציה installed.packages() .

> packagematrix<-installed.packages()

> NamesOnly<-packagematrix[,1]

> NamesOnly

התקנה של packages תהיה ע"י הפונקציה install.packages במידה ומחוברים לאינטרנט . הנה דוגמא להתקנה של ה packages dplyr ו- ggplot2 שמותקנים ע"י של Revolution R Enterprise 7.5.0 , ואם הם חסרים יש להתקינם באופן הבא :

> install.packages("dplyr")

> install.packages("ggplot2")

אם אין חיבור לאינטרנט תתקבל ההודעה הבאה :

אם אין חיבור לאינטרנט , יש להוריד את קבצי ה packages מסוג zip ומתוך התפריט של R studio לבחור ב Packages->install packages(s) from local zip File ומשם נבחר את המחיצה בה יושבים ה packages .

הפלט של ההתקנה :

> utils:::menuInstallLocal()

package ‘ggplot2’ successfully unpacked and MD5 sums checked

> utils:::menuInstallLocal()

package ‘dplyr’ successfully unpacked and MD5 sums checked

שרטוט גרפים עם R

אחד החזקות של R הוא היכולת לשרטט גרפים מכול סוג בקלות ובאיכות טובה , יכול "קסטום" מלאה וגרפים סטטיסטיים בעזרת ה packages .

בכדי למשל לשרטט היסטוגרמה של עבור המשתנה Height של ה data frame לדוגמא בשם trees עם 15 מקלות נשתמש בפונקציה hist . פרמטר ראשון שם שם ה data frame והמשתנה . פרמטר שני בשם breaks עם הערך 15.

hist(trees$Height, breaks=15)

הגרף יראה באופן הבא . שימו לב שלכל פונקציה במיוחד של גרפים יש להיות עשרות פרמטרים ולכן אנו מציינים את שמם במפורש ואת הערך שלהם בקריאה לפונקציה .

קנפוג SQL Server לעבודה עם R

אם SQL Server לא "קונפג " לעבודה עם R נקבל את הודעת השגיאה הבאה בזמן הרצה של R מתוך SQL Server :

Msg 39021, Level 16, State 1, Line 243

Unable to launch runtime for 'R' script. Please check the configuration of the 'R' runtime.

Msg 39019, Level 16, State 1, Line 243

An external script error occurred:

Unable to launch the runtime. ErrorCode 0x80070490: 1168(Element not found.).

דרישות קדם לעבודה עם R ו SQL Server

ברמת שרת SQL יש לבצע את ההכנות הבאות לטובת הרצה של R , לפחות עבור גרסה 2016 CTP 3.2 :

התקנה של Revolution R Enterprise 7.5.0 ו Revolution R Open 3.2.2 for Revolution R Enterprise 7.5.0 , כפי שתואר בחלק הראשון של המאמר .

בזמן ההתקנה של SQL 2016 יש לבחור ברכיב Advanced Analytics Extensions כפי שאפשר לראות בצילום מסך הבא :

בזמן ההתקנה של SQL Server יש לבחור ב Authentication mode מסוג Mixed . לפחות בגרסת CTP 3.0 , R יודע לעבוד עם SQL Authentication בלבד .

אינטגרציה עם R עם T-SQL ואפשור של Advanced Analytics features נעשית ע"י Service חדש בשם MSSQLLaunchpad . יש לוודא שהוא במצב started ו automatic .

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

בשביל לאפשר הרצה של scripts חיצונים ל SQL Server , יש לאפשר את האופציה external scripts enabled :

sp_configure 'external scripts enabled', 1

GO

RECONFIGURE ;

GO

sp_configure

כעת בצע restart ל Service של SQL Server דרך SQL Server Configuration Manager ול LaunchPad service דרך services manager .

נבדוק אם advanced analytics מותקן בעזרת השאילתה הבאה . נקבל 1 אם התשובה חיובית .

SELECT convert(bit, ISNULL(ServerProperty('isadvancedanalyticsinstalled'), 0))

לאחר ההתקנה של SQL Server יש לפתוח cmd כ Adminstrator לבצע הרצה של registerRext.exe . עבור default instance באופן הבא :

"C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\registerRext.exe" /install

עבור named instance נשתמש בפרמטר /instance , נקודתיים ואחריו שם ה instance . בדוגמא : sql2016.

C:\>"C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2\library\RevoScaleR\rxLibs\x64\registerRext.exe" /install /instance:sql2016

הנה דוגמא לפלט עבור default instance:

ההרצה של registerRext.exe , מתחברת ל SQL Server ומייצרת role חדש בשם db_rrerole ב SQL Server , שיכול להריץ scripts של R את ה extended stored procedures הבאים של R :

ההרצה של registerRext.exe מייצרת את הקובץ rlauncher.config במחיצה C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn אם הוא לא קיים . rlauncher.config קובע קונפיגורציה של הרצת R בשרת ומשאבי הריצה . ריצה של פעולות אנליטיות מול שרת SQL Server יכולה להיות תובענית מאוד מבחינת משאבים "ולהפיל" את השרת . הקובץ הזה נראה כך . תוכלו לקרוא ב BOL פירוט של כל פרמטר . אני אתמקד על החשובים .

RHOME=C:\Program Files\RRO\RRO-3.2.2-for-RRE-7.5.0\R-3.2.2

MPI_HOME=C:\Program Files\Microsoft MPI

TRACE_LEVEL=1

JOB_CLEANUP_ON_EXIT=1

USER_POOL_SIZE=10

LOG_DIRECTORY=C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log

WORKING_DIRECTORY=C:\PROGRA~1\MICROS~1\MSSQL1~1.MSS\MSSQL\EXTENS~1

MEMORY_LIMIT_PERCENT=20

הפרמטר WORKING_DIRECTORY מגדיר את מחיצת העבודה . כרגע בגרסת CTP 3.2 , כל הרצה של script של R יוצרת תת מחיצה חדשה , ושינוי מחיצת העבודה ע"י שינוי הפרמטר WORKING_DIRECTORY לא עובד. מחיצת העבודה אפשר לקבל ע"י הפקודה getdw() כפי שראינו מקודם .

R וצריכת זיכרון

הקובץ rlauncher.config מגדיר כמה זיכרון R יכול לצרוך מסך הזיכרון המוקצה ל SQL Server . בררת המחדל היא 20 אחוז באופן הבא :

MEMORY_LIMIT_PERCENT=20

R הוא צרכן זיכרון רציני וההמלצה היא להגדיל את צריכת הזיכרון ל 40 אחרת מקבלים הודעות על "התעופפות" של הקוד ב R בגלל בעיית זיכרון .

MEMORY_LIMIT_PERCENT=40

יצירת user יעודי עבור R בבסיס הנתונים

ניצור Login יעודי עבור R בשם rdemo. למשתמש זה ה role db_rrerole להרצה של ה extended procedure עבור R. ניצור user עבור login זה בבסיס הנתונים , שעל המידע שבו ירוץ R וניתן ל users לו הרשאה קריאה של db_datareader והרשאה להריץ script חיצוני. בדוגמא שלנו בסיס הנתונים הוא AdventureWorks2016CTP3].

/* Create Demo user */

if SUSER_SID('rdemo') is null

create login rdemo with password = 'Password1!';

go

drop user if exists rdemo;

create user rdemo;

alter role db_rrerole add member rdemo;

go

use [AdventureWorks2016CTP3]

go

drop user if exists rdemo;

create user rdemo;

-- Grant permission to users to execute R scripts:

grant execute any external script to rdemo;

go

USE [AdventureWorks2016CTP3]

GO

ALTER ROLE [db_datareader] ADD MEMBER [rdemo]

GO

להתראת בחלק הבא בו נראה ממש איך מריצים קוד של R מתוך SQL Server תוך שימוש במידע ב DB , טוענים מידע ומצירים גרפים .


כתיבת תגובה

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

nine − six =