content top
תמיכה ב JSON ב SQL Server 2016 הלכה למעשה המדריך המלא – חלק א

תמיכה ב JSON ב SQL Server 2016 הלכה למעשה המדריך המלא – חלק א

לקהל הקוראים היקר : במאמר ננוח קצת מ R , יש לי בעיה עם ההרצה של R מעל SQL 2016 CTP 3.3 חפשו : This is how to fix R Services after an in-place SQL Server 2016 CTP 3.2 upgrade . ברגע שתיפתר אני אחזור ואסיים את החלק השלישי . אני חייב לכם … עד אז נמשיך להעמיק בכול מיני יישומים ושילובים הלכה למעשה של ה Features החדשים של SQL 2016 .

בפרק זה ובפרקים הבאים זה נעסוק ב JSON , Temporal Data , Polybase ושילוב עם Big Data הלכה למעשה . אני אדגים יבוא של נתונים בפורמט JSON מ MongoDB , FaceBook ו ל SQL Server 2016 , ארכיבציה של הנתונים ע"י Temporal Data עבור כלי BI . יצוא נתונים בפורמט JSON ל Microsoft DocumentDB ולקינוח שאילות Polybase מול HDinsight . נדגים עבודה מול Microsoft DocumentDB . כל זאת בצורה פשוטה , מעשית וקולחת . למעשה נעבור תהליך של להיות גם DBA וגם BDA . בוא נתחיל עם JSON ……כרגיל ילווה את המאמר script עם תיעוד מלא בשם SQL2016_JSON_DEMO.sql .

אז מה זה JSON ? . במאמר זה אני אעסוק בשילוב של JSON , Temporal Data ו NoSQL ו Hadoop תוך דגש על החידושים של SQL 2016 CTP 3.3 וצפונה וההיבטים המעשיים של כוונון , נרמול וד-נרמול של מידע ע"י JSON

מה זה JSON

JSON קיצור של JavaScript Object Notation. משמש בעיקר לאחסון והחלפת מידע . JSON הוא פורמט "קליל" יותר מ XML וקל להבנה ולכן יכול לשמש לו כתחליף . ובאמת אם תסכלו ב AdventureWorks2016CTP3 תראו שיש למשל את הטבלה [Person].[Person] והטבלה המקבילה [Person].[Person_json]. ויש עוד דוגמאות .

בעולם ה Big Data JSON משמש לאינטגרציה של אפליקציות ושירותים שונים בקלות , הוא לא תלוי שפה ומשמש בעיקר להחלפת מידע בין שרתים לאפליקציות WEB , מנועי חיפוש כמו Elastic Search ואחסון מסמכים בבסיסי נתונים NoSQL בעיקר מסוג DB Document כמו : Micosoft DocumentDB MongoDB ו CoachBase . JSON מאפשר לבנות סכימה בצורה קלה לעומת הסכימה ב RDBMS עם התפשרות מסוימת . בתחום של ניתוח אנליטי ו Data Science ניתן לבצע Parse , שאילתות וניתוח של מסמכי JSON למשל ע"י R , עליו דיברתי במאמרים הקודמים .

SQL 2016 יכול לקבל JSON , לבצע לו parse ולאחסן אותו כמידע רלציוני . הוא מאפשר לייצא אותו גם כ JSON וכך לאפשר לסנכרן מידע רלציוני ולא רלציוני . אין צורך ב Data Type חדש . JSON מאוחסן כ NVARCHAR ומשתמש באינדקסים רגילים. JSON עובד עם In Memory Tables , row-level security , stretchdb , temporal ו- encryption עם כל סוגי Client Drivers ונתמך גם ב Azure SQL DB .

מערך ואובייקט של JSON

אובייקט הוא אוסף לא מסודר של אחד או יותר זוגות של name ו value , שסגורים בסוגריים מסולסלים הנה דוגמא :

אז הנה כמה דגשים : ה name , שגם נקרא key , הוא תמיד string . ה value יכול להיות string , ואז נסגור אותו במירכאות כפולות . הוא יכול להיות numeric , Boolean עם הערכים true או false או הערך null . במקרים הללו לא נשתמש במירכאות כפולות .

{"FirstName":"Terri", "Current":true, "Age":42, "Phone":null}

{"PersonID":285,"FirstName":"Syed",LastName":"Abbas","PhoneNumber":"926-555-0182","PhoneNumberType":"Work","Age":47 , "Available":true }

מערך הוא פשוט אוסף של ערכים בין סוגריים מרובעים , שיש ביניהם פסיק .

["Syed","926-555-0182",47,true]

FOR JSON AUTO

FOR JSON AUTO מיצר מסמך JSON שמבוסס על הטבלה ב T-SQL ולא ניתן לשלוט במבנה ה JSON . דומה מאוד ל FOR XML AUTO . עלינו להוסיף פשוט את הביטוי FOR JSON AUTO בסוף ה select . הנה דוגמא על ה view vSalesPerson :

USE [AdventureWorks2016CTP3]

GO

SELECT

    TOP 2

        BusinessEntityID AS 'PersonID',

        FirstName AS 'FirstName',

        MiddleName AS 'MiddleName',

        LastName AS 'LastName',

        PhoneNumber  As 'PhoneNumber',

        PhoneNumberType As 'PhoneNumberType'

FROM

        [Sales].[vSalesPerson]

FOR JSON AUTO

image

[

{"PersonID":285,"FirstName":"Syed","MiddleName":"E","LastName":"Abbas","PhoneNumbr":"926-555-0182","PhoneNumberType":"Work"},

{"PersonID":283,"FirstName":"David","MiddleName":"R","LastName":"Campbell","PhoneNumber":"740-555-0182","PhoneNumberType":"Work"}

]

הפלט : הוא מערך של שם וערך . אפשר לקחת את הפלט ולהכניס לכל מיני אתרים שמפרמטים את הפלט לפורמט JSON קריא יותר . למשל : https://jsonformatter.curiousconcept.com/.

בוא נראה עכשיו מה קורה כאשר מבצעים Join בין טבלאות . אפשר לראות שהמידע מהטבלאות להם מבצעים join , כלומר עם ה alias הבאים p , pp , pnt , ea , יחזרו כאובייקטים מקוננים בפלט .

SELECT TOP 2

    s.[BusinessEntityID]

    ,p.[Title]

    ,p.[FirstName]

    ,p.[MiddleName]

    ,p.[LastName]

    ,p.[Suffix]

    ,e.[JobTitle]

    ,pp.[PhoneNumber]

    ,pnt.[Name] AS [PhoneNumberType]

    ,ea.[EmailAddress]

FROM [Sales].[SalesPerson] s

    INNER JOIN [HumanResources].[Employee] e

    ON e.[BusinessEntityID] = s.[BusinessEntityID]

    INNER JOIN [Person].[Person] p

    ON p.[BusinessEntityID] = s.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[EmailAddress] ea

    ON ea.[BusinessEntityID] = p.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[PersonPhone] pp

    ON pp.[BusinessEntityID] = p.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt

    ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]

    FOR JSON AUTO;

GO

הפלט :

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[{"BusinessEntityID":285,"p":[{"Title":"Mr.","FirstName":"Syed","MiddleName":"E","LastName":"Abbas","e":[{"JobTitle":"Pacific Sales Manager","pp":[{"PhoneNumber":"926-555-0182","pnt":[{"PhoneNumberType":"Work","ea":[{"EmailAddress":"syed0@adventure-works.com"}]}]}]}]}]},{"BusinessEntityID":283,"p":[{"FirstName":"David","MiddleName":"R","LastName":"Campbell","e":[{"JobTitle":"Sales Representative","pp":[{"PhoneNumber":"740-555-0182","pnt":[{"PhoneNumberType":"Work","ea":[{"EmailAddress":"david8@adventure-works.com"}]}]}]}]}]}]

PATH

אופציה זו מאפשרת לשלוט בפלט . בצורה הבסיסית עלינו להוסיף פשוט את הביטוי FOR JSON PATH בסוף ה select .אם נעשה את זה עבור השאילתה הקודמת . הפלא יהיה פשוט 2 אובייקטים בתור מערך .אובייקט עבור כל רשומה . כל אובייקט אסף של שמות וערכים עבור כל רשומה .

SELECT TOP 2

    s.[BusinessEntityID]

    ,p.[Title]

    ,p.[FirstName]

    ,p.[MiddleName]

    ,p.[LastName]

    ,p.[Suffix]

    ,e.[JobTitle]

    ,pp.[PhoneNumber]

    ,pnt.[Name] AS [PhoneNumberType]

    ,ea.[EmailAddress]

FROM [Sales].[SalesPerson] s

    INNER JOIN [HumanResources].[Employee] e

    ON e.[BusinessEntityID] = s.[BusinessEntityID]

    INNER JOIN [Person].[Person] p

    ON p.[BusinessEntityID] = s.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[EmailAddress] ea

    ON ea.[BusinessEntityID] = p.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[PersonPhone] pp

    ON pp.[BusinessEntityID] = p.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt

    ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]

    FOR JSON PATH;

GO

פלט :

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[{"BusinessEntityID":285,"Title":"Mr.","FirstName":"Syed","MiddleName":"E","LastName":"Abbas","JobTitle":"Pacific Sales Manager","PhoneNumber":"926-555-0182","PhoneNumberType":"Work","EmailAddress":"syed0@adventure-works.com"},{"BusinessEntityID":283,"FirstName":"David","MiddleName":"R","LastName":"Campbell","JobTitle":"Sales Representative","PhoneNumber":"740-555-0182","PhoneNumberType":"Work","EmailAddress":"david8@adventure-works.com"}]

נוכל לשלוט במבנה של ה JSON ע"י כך שנגדיר aliases לעמודות ב select . הנה דוגמא :

SELECT TOP 2

    s.[BusinessEntityID]                                AS [SalesPerson.ID]

    ,p.[Title]                                            AS [SalesPerson.Title]

    ,p.[Suffix]                                            AS [SalesPerson.Suffix]

    ,e.[JobTitle]                                        AS [SalesPerson.JobTitle]

    ,p.[FirstName]                                        AS [Name.First]

    ,p.[MiddleName]                                        AS [Name.Middle]

    ,p.[LastName]                                        AS [Name.Last ]

    ,pp.[PhoneNumber]                                    AS [Phone.Number]

    ,pnt.[Name]                                            AS [Phone.Type]

    ,ea.[EmailAddress]                                    AS [EmailAddress]

FROM [Sales].[SalesPerson] s

    INNER JOIN [HumanResources].[Employee] e

    ON e.[BusinessEntityID] = s.[BusinessEntityID]

    INNER JOIN [Person].[Person] p

    ON p.[BusinessEntityID] = s.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[EmailAddress] ea

    ON ea.[BusinessEntityID] = p.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[PersonPhone] pp

    ON pp.[BusinessEntityID] = p.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt

    ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]

    FOR JSON PATH;

GO

פלט :

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[{"SalesPerson":{"ID":285,"Title":"Mr.","JobTitle":"Pacific Sales Manager"},"Name":{"First":"Syed","Middle":"E","Last ":"Abbas"},"Phone":{"Number":"926-555-0182","Type":"Work"},"EmailAddress":"syed0@adventure-works.com"},{"SalesPerson":{"ID":283,"JobTitle":"Sales Representative"},"Name":{"First":"David","Middle":"R","Last ":"Campbell"},"Phone":{"Number":"740-555-0182","Type":"Work"},"EmailAddress":"david8@adventure-works.com"}]

במקרה הנוכחי הגדרנו את האובייקט של SalesPerson שמכיל ID , Title ו JobTitle . האובייקט name שמכיל First , Middle ו Last. האובייקט Phone עם Number ו type . האובייקט Email .

שימו לב שעליכם לדאוג שכל ה alias לכול עמודה , חייבים להיות מסודרים בצורה "רציפה" וללא חזרה על aliases . בדוגמא ה aliases בסדר הבא : SalesPerson , Name , Phone ו Emailaddress .אחרת תקבלו את הודעת השגיאה הבאה .

Msg 13601, Level 16, State 1, Line 183

Property 'SalesPerson.Suffix' is invalid due to a conflict with another column name or alias.

הסיבה SalesPerson מופיעה בביטוי FROM [Sales].[SalesPerson] .

נדגים שימוש ב 2 אופציות של FOR JSON .

בשביל לציין מי ה root של ה JSON נוסיף את האופציה ROOT לביטוי FOR JSON . בדוגמא :

ROOT('SalesPersons_Details') .

כבררת מחדל לא נקבל אובייקטים של JSON שערכם הוא NULL . אפשר לשנות זאת ע"י שימוש באופציה INCLUDE_NULL_VALUES לביטוי FOR JSON . בפלט נקבל "Suffix":null עבור סוכני המכירות שערך זה עבורם הוא Null . כמובן שזה אופציונאלי . בדוגמא הבאה יש שימשו ב2 האפשרויות בו זמנית

SELECT TOP 2

    s.[BusinessEntityID]                                AS [SalesPerson.ID]

    ,p.[Title]                                            AS [SalesPerson.Title]

    ,p.[Suffix]                                            AS [SalesPerson.Suffix]

    ,e.[JobTitle]                                        AS [SalesPerson.JobTitle]

    ,p.[FirstName]                                        AS [Name.First]

    ,p.[MiddleName]                                        AS [Name.Middle]

    ,p.[LastName]                                        AS [Name.Last ]

    ,pp.[PhoneNumber]                                    AS [Phone.Number]

    ,pnt.[Name]                                            AS [Phone.Type]

    ,ea.[EmailAddress]                                    AS [EmailAddress]

FROM [Sales].[SalesPerson] s

    INNER JOIN [HumanResources].[Employee] e

    ON e.[BusinessEntityID] = s.[BusinessEntityID]

    INNER JOIN [Person].[Person] p

    ON p.[BusinessEntityID] = s.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[EmailAddress] ea

    ON ea.[BusinessEntityID] = p.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[PersonPhone] pp

    ON pp.[BusinessEntityID] = p.[BusinessEntityID]

    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt

    ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]

    FOR JSON PATH , ROOT('SalesPersons_Details'), INCLUDE_NULL_VALUES;

GO

image

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

{"SalesPersons_Details":[{"SalesPerson":{"ID":285,"Title":"Mr.","Suffix":null,"JobTitle":"Pacific Sales Manager"},"Name":{"First":"Syed","Middle":"E","Last ":"Abbas"},"Phone":{"Number":"926-555-0182","Type":"Work"},"EmailAddress":"syed0@adventure-works.com"},{"SalesPerson":{"ID":283,"Title":null,"Suffix":null,"JobTitle":"Sales Representative"},"Name":{"First":"David","Middle":"R","Last ":"Campbell"},"Phone":{"Number":"740-555-0182","Type":"Work"},"EmailAddress":"david8@adventure-works.com"}]}

דה – נרמול של טבלאות: משורות ל JSON

הטבלה Person. PersonPhone מחזיקה את כל מספרי הטלפון עבור ישות כלשהי. טבלת הקבועים Person. PhoneNumberType את סוג המספר : סלולארי , בית או עבודה . בטבלה Person. PersonPhone ניצור עמודה בשם PhoneNumbers מסוג NVARCHAR(MAX) אליה נכניס מערך JSON של מספר הטלפון וסוג הטלפון , ע"י שימוש בשאילתת join בין הטבלאות הנ"ל ו FOR JSON PATH. בצורה זו חסכנו את הטבלאות PersonPhone ו PhoneNumberType . הנה דוגמאות לערכים בעמודה זו, שהוא למעשה מערך מסוג JSON של name ו value :

"Phone":{"Number":"926-555-0182","Type":" Cell"}

"Phone":{"Number":"740-555-0182","Type":"Work"}

קטע הקוד הבא מוסיף עמודה PhoneNumbers_json מסוג NVARCHAR(MAX) שתכיל את אוביקט ה JSON . התנאי C_PhoneNumbers_JSON בודק בעזרת הפונקציה ISJSON ש PhoneNumbers_json הוא אכן אובייקט JSON בעזרת הפונקציה ISJSON . לבסוף נאכלס את העמודה PhoneNumbers_json ע"י ביצוע joins בין הטבלאות המתאימות .

ALTER TABLE  [Sales].[SalesPerson]

DROP

    CONSTRAINT IF EXISTS [C_PhoneNumbers_JSON],

    COLUMN IF EXISTS PhoneNumbers_json

GO


ALTER TABLE  [Sales].[SalesPerson]

ADD PhoneNumbers_json  NVARCHAR(MAX)

    CONSTRAINT [C_PhoneNumbers_JSON]

        CHECK (ISJSON(PhoneNumbers_json)>0)

GO



-- Populate  from PersonPhone/PhoneNumberType tables using FOR JSON

UPDATE Sales.SalesPerson

SET PhoneNumbers_json = (SELECT Person.PersonPhone.PhoneNumber, Person.PhoneNumberType.Name AS PhoneNumberType

                            FROM  Person.PersonPhone

                            INNER JOIN Person.PhoneNumberType ON Person.PersonPhone.PhoneNumberTypeID = Person.PhoneNumberType.PhoneNumberTypeID

                            WHERE Sales.SalesPerson.BusinessEntityID = Person.PersonPhone.BusinessEntityID

                        FOR JSON PATH)

GO

פלט לדוגמא :

select  Top 3 [BusinessEntityID] , [PhoneNumbers_json] from  [Sales].[SalesPerson]

BusinessEntityID PhoneNumbers_json

274 [{"PhoneNumber":"238-555-0197","PhoneNumberType":"Cell"}]

275 [{"PhoneNumber":"257-555-0154","PhoneNumberType":"Cell"}]

276 [{"PhoneNumber":"883-555-0116","PhoneNumberType":"Work"}]

הדוגמא הזו מדגימה עקרון חשוב של בסיסי נתונים Nosql מסוג Document DB . ניתן לוותר על שימוש בטבלאות בנים וקשרים בין טבלאות ע"י קינון של Document בתוך Document , לטוב ולרע . היכולה הזו מאפשרת לבצע הגירה גם ל RDBMS ע"י התמיכה ב JSON .

כמו ששמתם לב הפלט של FOR JSON PATH קצת "מעצבן" כי הוא מכיל key ו values , כאשר ה key חוזר על עצמו . דוגמא אם יש לנו שדה של כתובות דואר שמשורשרות כאובייקט JSON אין טעם לחזור על ה key EmailAddress כל הזמן . דוגמא :

{" EmailAddress ":[{"EmailAddress":"syed0@adventure-works.com"},{" EmailAddress":"david8@adventure-works.com"}]}

נשתמש בפונקציה ufnToRawJsonArray הבאה מהדוגמאות של מיקרוסופט ב Codeplex הבאה שמיצרת מהקלט מערך של ערכים בלבד . למשל : [1,2,"cell"] במקום אובייקט JSON שהוא אוסף של key ו value שהיה נראה כך :

[{"val":1,{"val":2},{"val":"cell"}] .

DROP FUNCTION IF EXISTS dbo.ufnToRawJsonArray

GO

CREATE FUNCTION dbo.ufnToRawJsonArray(@json nvarchar(max), @key nvarchar(400))

returns nvarchar(max)

as begin

return replace(replace(@json, FORMATMESSAGE('{"%s":', @key),''), '}','')

end

go

כעת נוסיף עמודה בשם EmailAddresses_json שתכיל מערך של כתובות דואר .

ALTER TABLE Sales.SalesPerson

ADD EmailAddresses_json NVARCHAR(MAX)

CONSTRAINT [C_EmailAddresses_json]

CHECK (ISJSON(EmailAddresses_json)>0)

GO

כעת נאכלס את עמודת ה JSON עם נתוני הדואר בעזרת השאילתה עם JSON PATH . בשביל ל"נקות" את ה key נעביר את התוצאה דרך הפונקציה ufnToRawJsonArray .

UPDATE Sales.SalesPerson

SET EmailAddresses_json =dbo.ufnToRawJsonArray((SELECT Person.EmailAddress.EmailAddress

                                                FROM Person.EmailAddress

                                                WHERE Sales.SalesPerson.BusinessEntityID = Person.EmailAddress.BusinessEntityID

                                                FOR JSON PATH), 'EmailAddress')

והנה פלט לדוגמא :

select Top 3 [BusinessEntityID] , [EmailAddresses_json]

from [Sales].[SalesPerson]

Output:

BusinessEntityID EmailAddresses_json

274 ["stephen0@adventure-works.com"]

275 ["michael9@adventure-works.com"]

276 ["linda3@adventure-works.com"]

להתראות בחלק הבא בוא נמשיך לעסוק ב JSON !


כתיבת תגובה

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

nine + eleven =