content top
הרצת קוד Python ב-SQL Server

הרצת קוד Python ב-SQL Server

גרסת SQL Server 2017 שכעת עדיין זמינה רק בגרסת בטא (CTP 2.0 בשבילכם), כוללת תמיכה בפייתון (כעת עוד לא ב-Azure).

בויקיפדיה מופיעה הפסקה הבאה (לקחת נשימה עמוקה):

 

“פייתון היא שפה מרובת־פרדיגמות, המאפשרת תכנות מונחה-עצמים, תכנות פרוצדורלי, ובמידה מסוימת גם תכנות פונקציונלי. לשפה ספרייה סטנדרטית גדולה וענפה, והיא תומכת באופן מובנה בהרחבה שלה אל שפות אחרות (בתלות במפרש: C‏, ++C,‏ Java‏ ו־#C).”

הבנתם? אז זהו שאני לא..

קצת שיחקתי עם פייתון לאחרונה, אני בוודאי לא המומחה שיתן הרצאת הקדמה עליה, אז אסתפק בתיאור החוויה האישית.

שפת התכנות הראשונה שהיכרתי, אי שם בראשית שנות ה-1980, הייתה בייסיק (Basic). הימים ימי ה-DOS העליזים, והדור שלי שגדל ללא מחשבים וללא סלולרים התוודע לראשונה לעולם המחשבים והתכנות דרך שפת בייסיק קלת הדעת, או דרך פסקל (Pascal) – המחונכת והמיושבת בדעתה. שמה של בייסיק הלך לפניה לשימצה ולשבח בשל אופציית ה-Goto שאיפשרה לדלג ממקום למקום בקוד, ועוד כל מיני הרגלים מגונים שקצרה היריעה..

שנות ה-1990 הביאו אין את Windgows וסביבת העבודה הגרפית, ואת בייסיק הטקסטואלית לגירסאותיה החליפה Visual Basic (ובקיצור VB) שלא היה לה שום קשר אבולוציוני לבייסיק (כלומר- זו לא הייתה התאמה או הרחבה של בייסיק לעולם ה-Windows), אבל היא יצגה אותה במה שמכונה “רוח המפקד”: שפה קלה, עם כל מיני אופציות לא חינוכיות, לפחות בהשוואה לאחותה הבוגרת והאחראית לבית מיקרוסופט Visual C (להלן VC), כמו למשל – האופציה לא להגדיר משתנים..
שלא תהיה אי הבנה: היה מדובר בשפה הפופולרית ביותר בשנות ה-1990 ולא בכדי, אבל היחס של אנשי ה-VC אליה היה כמו היחס של אנשי אוראקל לאנשי SQL Server בימינו: “בחייכם, זה לא זה, מה אתם משווים בכלל, לזה אתם קוראים שפת תכנות? נו באמת.. עשו לנו טובה!”.

שנות ה-2000 הביא איתם את בשורת ה-Dot Net: אנשי VC עברו בטבעיות ל-#C, ואנשי VB ניסו לעבור ל-VB.Net והלכו לאיבוד: VB.Net אולי נראתה כמו VB אבל דיברה ב-C.. המעבר מ-VB לשפה Object Oriented (להלן OOP) חייבה שינוי בכל שיטת המחשבה, והשוק הצביע ברגליים ועבר ל-#C.

כאנקדוטה אפשר לספר שבשנות ה-1990 היו במירקוסופט שתי קבוצות משתמשים (קהילות): VB ו-C. בשנות ה-2000 הן השתדרגו הדרגתית ל-Dot Net, אך לבסוף אוחדו לקבוצה אחת, שהיא #C עד עצם היום הזה.

למה כל ההקדמה הארוכה? מכיוון שכשהתחלתי ללמוד פייתון, שבה אלי אותה תחושה נוסטלגית מימי הבייסיק לפני עשרות שנים: שפה סלחנית, זורמת בלי יותר מדי משחקים מוקדמים, קלה ללימוד.. לא צריך להגדיר שם שום דבר, והקוד קצר וקומפקטי. מה הכוונה? יכול לבוא אלינו חבר ולספר בגאווה שפתר בעייה סבוכה בעזרת #C ב-10 שורות קוד בלבד. זה הכל? אנחנו מסתכלים בקוד ורואים שם כמה עשרות שורות. הכיצד? נו טוף: אם נוריד את שורות ה-Import בהתחלה, ואת ה-NameSpace ואת ה-Class ןאת המתודה, את הגדרות המשתנים ו{הסוגריים המסולסלים} בתחילת ובסוף כל בלוק – ישארו 10 שורות..
בפייתון 10 שורות הן 10 שורות (אולי 11..), ולמעשה הן אפילו פחות כי כל מיני כלים מאפשרים לקצר את הנטו של #C לפחות שורות.
שלא אובן לא נכון- פייתון אינה מתחרה ישירות ב-#C, היא עובדת אחרת, ומשמשת לצרכים אחרים. אני מדבר מנקודת מבטו של המתכנת-הלא-כך-כך-מקצועי. הנה, בתור חובב רקורסיות מושבע, ניגשתי לפתור את בעיית מגדלי האנוי:

def Hanoi(L,X,Y):

    Lz=list()

    if len(L)==1:

        print("move "+str(L[0]) +" from "+X+" to "+Y)

    else:

        Z=list(set(["P","Q","R"])-set([X,Y]))

        A=L[len(L)-1]

        L.remove(A)

        Hanoi(L[:],X,Z[0])

        Hanoi(Lz+list(str(A)),X,Y)

        Hanoi(L,Z[0],Y)

11 שורות, כולל שתי שורות להגדרת הפונקציה ולהגדרת list בהתחלה. אולי אפשר גם את זה לכווץ עוד קצת: אני די טירון בתחום.

ולענייננו: לא התכוונתי לעשות רילוקיישן מ-SQL לפייתון, אלא לדבר על השילוב בינהן: החל מגרסת 2017 יהיה ניתן לשלב קטעי קוד פייתון ב-TSQL. מכיוון שהקוד מופעל בעזרת Interpreter אין צורך לקמפל את הקוד מראש כמו במקרה של CLR שיש ליצור dll ואותו לייבא למערכת כדי להשתמש בו, וניתן לכתוב קוד באופן ישיר.

אני כבר מנמיך ציפיות: הביצועים גרועים. זה לא נועד להחליף את השימוש ב-CLR אלא לתמוך ב-Machine Learning, אך בכל זאת: יש תחושה נחמדה של צעצוע חדש, שעוד ימצא הזמן והמקום לעשות בו שימוש, ובינתיים רק נראה איך זה עובד.

הבעייה הגיע אלי משרון רימר (נאיה) והפתרון מבוסס בעיקר על תרומתו (הגדולה!) של איתן ליפובצקי (נאיה, איטורו): יש לנו רשימת כמויות לפי תאריכים, יש לסכום אותן (running sum) אבל בכל פעם שהסכום עובד סף מסויים – לאפס אותו (מבחינת המערכת- לבצע פעולה מסויימת ואז לאפס). למשל, במקום עבודתי באיטורו עוסקים במסחק בניירות ערך, החברה צריכה “לגדר” (edging) את פקודות הקנייה של הלקוחות, אך כדי לחסוך בעלויות מתבצע גידור רק כשסכום הקניות עבר מינימום מסויים ולא לכל קנייה וקנייה בנפרד ללא קשר לגודלה. דוגמה אחרת – מונית שירות יוצאת לדרך רק לאחר שהגיע מספר מינימלי של נוסעים. ודוגמה אחרונה – SQL Server מבצע checkpoint וכותב לדיסק רק לאחר שכמות ה-Dirty Pages עוברת סף מסויים.

למרבה הצער אין פתרון Set Based יעיל שמבוסס על Windows Functions (בניגוד ל-running sum פשוט), ולכן אפשר להשתמש ב-CTE רקורסיבי ומאוד לא יעיל שבכל שורה פונה מחדש לטבלה, או ב-Cursor בצורה כזו או אחרת. פתרון יעיל מחייב מעבר (scan) בודד על הטבלה תוך סכימה מותנית של הכמויות.

רשימת המצרכים – טבלה ובה תאריכים וכמויות אותן יש לסכום, ובכל פעם שהסכום מגיע ל-1000 – לאפס אותו:

Use    tempdb;

Go


If Object_ID('dbo.T_Data') Is Not Null Drop Table dbo.T_Data

Go


Create    Table T_Data

        ([Date] DateTime Primary Key,

        Quantity Int Not Null);

Go


Insert

Into    T_Data

Values    ('20170419 09:00', 200 ),

        ('20170419 09:15', 300 ),

        ('20170419 09:30', 600 ),--1100

        ('20170419 09:45', 500 ),

        ('20170419 10:00', 700 ),--1200

        ('20170419 10:15', 200 ),

        ('20170419 10:30', 200 ),

        ('20170419 10:45', 500 ),

        ('20170419 11:00', 700 ),--1600

        ('20170419 11:15', 200 ),

        ('20170419 11:30', 200 ),

        ('20170419 11:45', 700 ),--1100

        ('20170419 12:00', 1600 ),--1600

        ('20170419 12:15', 3100 ),--3100

        ('20170419 12:30', 600 ),

        ('20170419 12:45', 500 ),--1100

        ('20170419 13:00', 700 ),

        ('20170419 13:15', 200 ),

        ('20170419 13:30', 200 ),--1100

        ('20170419 13:45', 500 ),

        ('20170419 14:00', 700 ),--1200

        ('20170419 14:15', 200 ),

        ('20170419 14:30', 200 ),

        ('20170419 14:45', 700 ),--1100

        ('20170419 15:00', 1600 );--1600

Go

בקוד מצויינים הסכומים המצטברים בכל מקום בו הם הגיעו ל-1000 והתאפסו.

למשל- בשורה השלישית הסכום המצטבר של שלוש השורות הראשונות עובר לראשונה את 1000: 200+300+600=1100. כעת הסכום מתאפש ומתחילים לסכום מחדש, ובשורה החמישית הסכום שוב עובר את 1000: 500+700=1200, וכך הלאה.

מי שרוצה מוזמן לנסות את כוחו בפתרונות אחרים, ואם מישהו מצליח למצוא פתרון set based – אשמח לשמוע.. :-)

ולפתרון בעזרת פייתון דרך SQL Server 2017 גרסת CTP 2.0:

Declare @Treshold Int=1000;

Declare @Result VarChar(Max)='';


Exec    sp_execute_external_script

        @language=N'Python',

        @script =N'

N=0

T=InputDataSet

Total=0

for ID in T.index:

    Total+=T.ix[ID].Quantity

    N+=1

    if N==1:

        A={str(T.ix[ID].DateInt):{"Quantity": T.ix[ID].Quantity,"Total": Total}}

    else:

        A.update({str(T.ix[ID].DateInt):{"Quantity": T.ix[ID].Quantity,"Total": Total}})

    if Total>=Treshold :

        Total=0

Result=str(A)

',

        @input_data_1 = N'select DateDiff(Minute,0,[Date]) DateInt,Quantity From T_Data Order By [Date];',

        @params=N'@Treshold Int,@Result Varchar(Max) Output',

        @Treshold=@Treshold,

        @Result=@Result Output;


Select    @Result As Result;


Select    DateAdd(Minute,Cast(J1.[key] As Int),0) [Date],

        *

From    OpenJson(Replace(@Result,'''','"')) J1

Outer Apply (Select    Max(Iif([key]='Quantity',value,Null)) Quantity,

                    Max(Iif([key]='Total',value,Null)) Total

            From    OpenJson(J1.value)) J2

Order By [Date];

Go

sp_execute_external_script – פונקציית מערכת שנועדה להריץ קודים בפייתון וב-R.

script@– כפשוטו.

language@ – כפשוטו.

input_data_1@ – פרמטר המעביר לפייתון סט מתוך SQL Server. לא ניתן להעביר תאריכים בשלב זה, לכן הפכתי אותם לערכים מספריים (מספר הדקות מ-0), ותרגמתי חזרה לתאריכים את הפלט.

params@ – הפרמטרים של כמות הסף לאיפוס (העברת ערך מה-TSQL לפייתון), ופרמטר ה-Output שמאפשר לקבל חזרה ל-TSQL את הפלט מפייתון. לאחר מכן – שתי שורות של קישור הפרמטרים בסביבת ה-TSQL למשתנים בפייתון.

ניתן להעביר סט מה-TSQL לפייתון, אך לא להיפך (רק מספר או מחרוזת!); ולכן נהפוך את הסט ל-json, נעביר אותו כמשתנה טקסט חזרה ל-TSQL, ובעזרת OpenJson “נפרסר” אותו..

כיצד ניצור Json בתוך פייתון? לא צריך להמציא את הגלגל, הוא כבר שם: אובייקט dictionary בפייתון הוא בעצם .. Json, מוכן לשימוש מיידי. כל שורה בסט המקורי תהפוך לשורה נוספת ב-Dictionary (האובייקט A במקרה זה) שהמפתח שלה הוא המפתח המקורי – התאריך (מתורגם ל-integer המציין דקות), ומכיוון שיש בכל שורה שני ערכים – הכמות השוטפת (נתונה) והכמות המצטברת (מחושבת על ידי הפייתון), נשמור את שניהם ב-dictionary מקונן (ב-Dictionary יש צמדים של מפתח וערך, אבל הערך יכול להיות dictionary מקונן המכיל מספר שורות).

כדי “לפרסר” את ה-json ב-TSQL אני משתמש ב-OpenJson פעם אחת כדי להפריד את המפתח והערך,

ופעם שניה בעזרת Outer Apply כדי “לפרסר” את ה-Json המקונן ולבצע Group By כדי להפוך 2 שורות ל-2 עמודות.

להלן הפלט- בגריד העליון ה-Json (שימו לב שאינו ממויין בפנים!), ובתחתון הפלט “המפורסר” והממויין:

image

זמן הריצה אצלי כ-20 שניות, וזה בעיקר בגלל התקורה של הפעלת הפייתון, ולא בגלל כובד המשימה. גם הרצה של קוד Hello World מינימלי תימשך שניות ארוכות:

Exec    sp_execute_external_script

        @language=N'Python',

        @script =N'

print("Hello world")

';

ואף על פי כן נוע תנוע!


כתיבת תגובה

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

five + 16 =