תרגיל - יצירת טבלה חיצונית כ-select

הושלם

בתרגיל זה, אתה משתמש ב-CREATE TABLE EXTERNAL AS SELECT (CETAS) כדי:

• יצא טבלה כסוקט.
• העבר נתונים קרים ממסד נתונים לאחסון.
• צור טבלה חיצונית כדי לגשת לנתונים החיצוניים המיוצאים.
• השתמש בצפיות או חיפוש wildcard כאסטרטגיות שאילתה.
• הגבל שאילתות באמצעות הסרת תיקיות ומידע מטא-דאטה לשיפור הביצועים.

‏‫דרישות מוקדמות‬

• מופע SQL Server 2025 עם חיבור לאינטרנט ותכונת PolyBase Query Service for External Data הותקנו והופעלו כמו בתרגילים קודמים.
• מסד הנתונים לדוגמה של AdventureWorks2022 משוחזר לשרת שלך לשימוש כנתוני דוגמה.
• חשבון Azure Storage עם מיכל Blob Storage בשם data נוצר. כדי ליצור את האחסון, ראו הפעלה מהירה: העלה, הורדה ורשימה של בלובים עם פורטל Azure.
• תפקיד תורם נתוני האחסון מבוסס-תפקיד (RBAC) ב-Azure ב-Azure. למידע נוסף, ראו הקצאת תפקיד Azure לגישה לנתוני blob.
• טוקן SAS של מכולת blob עם הרשאות READ, WRITE,LISTו-CREATE לשימוש עבור CETAS. כדי ליצור את אסימון SAS, ראו יצירת אסימוני חתימת גישה משותפת (SAS) עבור מכולות האחסון שלך.

השתמש ב-CETAS לייצא טבלה כ-Parquet

דמיין שאתה עובד עם צוות אנליטיקה עסקית שרוצה לייצא נתונים ישנים מ-2012 מטבלת SQL Server ל-Azure Blob Storage Container. הם רוצים להריץ את שאילתות הדוחות שלהם על הנתונים המיוצאים במקום לשאול ישירות את SQL Server.

1. הפעל CETAS על מופע SQL Server.

   EXEC SP_CONFIGURE @CONFIGNAME = 'ALLOW POLYBASE EXPORT', @CONFIGVALUE = 1;
   

   

2. בצע את שאילתת חקר הנתונים הבאה כדי להבין אילו נתונים ברצונך לייצא. במקרה הזה, אתה מחפש נתונים מ-2012 או קודם לכן. אתה רוצה לייצא את כל הנתונים מ-2011 ו-2012.

   -- RECORDS BY YEARS
   SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
   FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
   GROUP BY DATEPART(YYYY, [DUEDATE])
   ORDER BY [YEAR]
   

   

3. צור מפתח ראשי למסד הנתונים, כפי שהיה בתרגילים הקודמים.

   Use AdventureWorks2022
   
   DECLARE @randomWord VARCHAR(64) = NEWID();
   DECLARE @createMasterKey NVARCHAR(500) = N'
   IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
       CREATE MASTER KEY ENCRYPTION BY PASSWORD = '  + QUOTENAME(@randomWord, '''')
   EXEC sp_executesql @createMasterKey;
   
   SELECT * FROM sys.symmetric_keys;
   

4. צור את פרטי המידע הממומנים במסד הנתונים ומקור נתונים חיצוני. החלף את <sas_token> ה-and <storageccount> זמנית בחשבון האחסון ואסימון SAS שיצרת ב-Azure.

   -- DATABASE SCOPED CREDENTIAL
   CREATE DATABASE SCOPED CREDENTIAL blob_storage
         WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
         SECRET = '<sas_token>';
   
   -- AZURE BLOB STORAGE DATA SOURCE
   CREATE EXTERNAL DATA SOURCE ABS_Data
   WITH
   (
    LOCATION = 'abs://<storageaccount>.blob.core.windows.net/data/chapter3'
   ,CREDENTIAL = blob_storage
   );
   

5. צור את פורמט הקובץ החיצוני ל-Parquet.

   -- PARQUET FILE FORMAT
   CREATE EXTERNAL FILE FORMAT ffParquet
   WITH (FORMAT_TYPE = PARQUET);
   

6. צור את הטבלה החיצונית באמצעות CETAS. השאילתה הבאה יוצרת טבלה חיצונית בשם ext_data_2011_2012 ומייצאת את כל הנתונים מ-2011 ו-2012 למיקום שצוין על ידי מקור ABS_Dataהנתונים .

   CREATE EXTERNAL TABLE ex_data_2011_2012
   WITH(
           LOCATION = 'data_2011_20122',
           DATA_SOURCE = ABS_Data,
           FILE_FORMAT = ffParquet
   )AS
   SELECT
   	[PurchaseOrderID]
         ,[PurchaseOrderDetailID]
         ,[DueDate]
         ,[OrderQty]
         ,[ProductID]
         ,[UnitPrice]
         ,[LineTotal]
         ,[ReceivedQty]
         ,[RejectedQty]
         ,[StockedQty]
         ,[ModifiedDate]
   FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
   WHERE YEAR([DUEDATE]) < 2013
   GO
   

7. בדוק את Azure Blob Storage שלך בפורטל Azure. עליך לראות את המבנה הבא שנוצר. SQL Server 2025 יוצר אוטומטית את שם הקובץ בהתאם לכמות הנתונים שהוא מייצא ולפורמט הקובץ.

   

8. עכשיו אפשר לגשת לטבלה החיצונית כמו טבלה רגילה.

   SELECT * FROM ex_data_2011_2012
   

   

הנתונים כעת מיוצאים ל-Parquet ונגישים בקלות דרך הטבלה החיצונית. צוות האנליטיקה העסקית יכול לשאול את הטבלה החיצונית או להפנות את כלי הדיווח שלו לקובץ Parquet.

השתמש ב-CETAS כדי להעביר נתונים קרים ממסד הנתונים

כדי לשמור על ניתנות לניהול, החברה שלך מחליטה להעביר נתונים ישנים יותר מ-2014 ממסד הנתונים SQL Server. עם זאת, כל הנתונים חייבים להיות נגישים.

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

קודם כל, שכפל את הטבלה המקורית, כי אתה רוצה לדמות ייצוא והסרת הנתונים אבל לא בהכרח רוצה למחוק את מקור הנתונים הנוכחי. הריץ את ההצהרה הבאה:

-- CLONE TABLE
SELECT * INTO [PURCHASING].[PURCHASEORDERDETAIL_2] FROM [PURCHASING].[PURCHASEORDERDETAIL]

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

1. כדי ליצור את הטבלאות החיצוניות, הרץ את הפקודות הבאות:

   CREATE EXTERNAL TABLE ex_2011
   WITH(
           LOCATION = '2011',
           DATA_SOURCE = ABS_Data,
           FILE_FORMAT = ffParquet
   )AS
   SELECT
       [PurchaseOrderID]
         ,[PurchaseOrderDetailID]
         ,[DueDate]
         ,[OrderQty]
         ,[ProductID]
         ,[UnitPrice]
         ,[LineTotal]
         ,[ReceivedQty]
         ,[RejectedQty]
         ,[StockedQty]
         ,[ModifiedDate]
   FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
   WHERE YEAR([DUEDATE]) = 2011;
   

   CREATE EXTERNAL TABLE ex_2012
   WITH(
           LOCATION = '2012',
           DATA_SOURCE = ABS_Data,
           FILE_FORMAT = ffParquet
   )AS
   SELECT
       [PurchaseOrderID]
         ,[PurchaseOrderDetailID]
         ,[DueDate]
         ,[OrderQty]
         ,[ProductID]
         ,[UnitPrice]
         ,[LineTotal]
         ,[ReceivedQty]
         ,[RejectedQty]
         ,[StockedQty]
         ,[ModifiedDate]
   FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
   WHERE YEAR([DUEDATE]) = 2012;
   

   CREATE EXTERNAL TABLE ex_2013
   WITH(
           LOCATION = '2013',
           DATA_SOURCE = ABS_Data,
           FILE_FORMAT = ffParquet
   )AS
   SELECT
       [PurchaseOrderID]
         ,[PurchaseOrderDetailID]
         ,[DueDate]
         ,[OrderQty]
         ,[ProductID]
         ,[UnitPrice]
         ,[LineTotal]
         ,[ReceivedQty]
         ,[RejectedQty]
         ,[StockedQty]
         ,[ModifiedDate]
   FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
   WHERE YEAR([DUEDATE]) = 2013;
   

2. לאחר ביצוע הפקודות האלה, רענן את SSMS Object Explorer. לאחר מכן פתח אתטבלאות>Databases>AdventureWorks2022>External Tables כדי לראות את הטבלאות החיצוניות.

   

3. אשר שהתיקיות הבאות מופיעות במיכל האחסון של Azure:

   

4. לאחר שהנתונים הקרים מיוצאים, אפשר למחוק אותם מהמיקום המקורי בטבלה.

   DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
   WHERE YEAR([DUEDATE]) < 2014
   

שאילתה בנתונים הכוללים את הטבלה החיצונית

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

השתמש בתצוגה כדי לשאול את הנתונים

עכשיו כשהנתונים הישנים מיוצאים ונמחקים מהמסד נתונים, אפשר להשתמש ב-T-SQL כדי ליצור תצוגה שבודקת את כל הטבלאות החיצוניות ואת הנתונים הנוכחיים במסד הנתונים שלך.

CREATE VIEW vw_purchaseorderdetail 
AS
SELECT * FROM ex_2011
UNION ALL
SELECT * FROM ex_2012
UNION ALL
SELECT * FROM ex_2013
UNION ALL
SELECT * FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 

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

SELECT  COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM vw_purchaseorderdetail 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

השתמש בחיפוש wildcard כדי לשאול את הנתונים

בדוגמה הקודמת, השתמשת בתצוגה עם הצהרות UNION כדי לחבר את שלוש הטבלאות החיצוניות. דרך נוספת להשיג את התוצאות הרצויות היא שימוש בחיפוש wildcard לסריקת מבנה התיקיות, כולל תתי-התיקיות, אחר נתונים מסוג מסוים.

הדוגמה הבאה ל-T-SQL משתמשת ב-OPENROWSET כדי לחפש בין ABS_Data מקורות הנתונים, כולל תתיקי המשנה שלו, אחר קבצי Parquet.

SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM OPENROWSET 
    (BULK '**'
    , FORMAT = 'PARQUET'
    , DATA_SOURCE = 'ABS_Data')
    AS [cc]
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

הסרת תיקיות ומידע על מטא-דאטה

גם טבלאות חיצוניות וגם OPENROWSET יכולות להשתמש בפונקציה לאיסוף filepath וסינון מידע בהתבסס על מטא-דאטה של קבצים. הפונקציה filepath מחזירה מסלולים מלאים, שמות תיקיות ושמות קבצים. ניתן להשתמש במידע הזה כדי לשפר את יכולות החיפוש של הטבלה החיצונית ושל פקודות OPENROWSET.

SELECT
    r.filepath(1) 'folder_name'
    ,r.filepath() 'full_path'
    ,r.filepath(2) 'file_name'
FROM OPENROWSET(
        BULK '*/*.parquet',
        DATA_SOURCE = 'ABS_Data',
        FORMAT = 'parquet'
    ) as [r]
GROUP BY
    r.filepath(2),r.filepath(1), r.filepath()
ORDER BY 
    r.filepath(2)

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

SELECT  *
FROM OPENROWSET(
 BULK '*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2011')

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

לדוגמה, בהינתן ארכיטקטורת התיקיות הבאה:

אתה יכול להשתמש בשאילתה הבאה:

SELECT  *
FROM OPENROWSET(
 BULK 'year=*/month=*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('<year>')
 r.filepath(2) IN ('<month>')

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

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

סיכום

בתרגיל הזה, השתמשת ב-CETAS כדי להעביר נתונים קרים ממסד נתונים ל-Azure Storage ולייצא טבלה בפורמט קובץ Parquet. למדת דרכים לשאול את הנתונים החיצוניים לחקירה ולייעל ביצועים.

אתה יכול להשתמש ב-CETAS כדי לשלב OPENROWSET, טבלאות חיצוניות, תצוגות, חיפוש wildcard ופונקציות Filepath. אתה יכול לגשת ולייצא נתונים ממסדי נתונים אחרים כמו SQL Server, Oracle, Teradata ו-MongoDB, או מ-Azure Blob Storage, Azure Data Lake Storage, או כל אחסון אובייקטים תואם S3. CETAS יכולה לעזור לך לעצב פתרונות יעילים, עמידים וניתנים להרחבה בכל מקורות הנתונים הנתמכים ב-PolyBase.