تمرين - إنشاء جدول خارجي ك SELECT

مكتمل

في هذا التمرين، يمكنك استخدام CREATE EXTERNAL TABLE AS SELECT (CETAS) من أجل:

• تصدير جدول ك Parquet.
• نقل البيانات الباردة من قاعدة البيانات إلى التخزين.
• إنشاء جدول خارجي للوصول إلى البيانات الخارجية المصدرة.
• استخدم طرق العرض أو البحث في أحرف البدل كاستراتيجيات استعلام.
• الحد من الاستعلامات باستخدام معلومات إزالة المجلدات وبيانات التعريف لتحسين الأداء.

المتطلبات الأساسية

• مثيل SQL Server 2025 مع اتصال بالإنترنت وميزة خدمة الاستعلام PolyBase للبيانات الخارجية المثبتة والممكنة كما هو الحال بالنسبة للتمارين السابقة.
• تمت استعادة نموذج قاعدة بيانات AdventureWorks2022 إلى الخادم الخاص بك لاستخدامها في نموذج البيانات.
• حساب Azure Storage مع حاوية Blob Storage المسماة data تم إنشاؤها. لإنشاء التخزين، راجع التشغيل السريع: تحميل وتنزيل وسرد الكائنات الثنائية كبيرة الحجم باستخدام مدخل Microsoft Azure.
• دور مساهم بيانات كائن ثنائي كبير الحجم للتخزين (RBAC) لعنصر التحكم في الوصول استنادا إلى الدور في Azure المعين في Azure. لمزيد من المعلومات، راجع تعيين دور Azure للوصول إلى بيانات كائن ثنائي كبير الحجم.
• رمز SAS المميز لحاوية كائن ثنائي كبير الحجم مع أذونات READ و WRITE و LIST و CREATE لاستخدامها في CETAS. لإنشاء رمز SAS المميز، راجع إنشاء رموز مميزة لتوقيع الوصول المشترك (SAS) لحاويات التخزين الخاصة بك.

استخدام CETAS لتصدير جدول ك Parquet

تخيل أنك تعمل مع فريق تحليلات الأعمال الذي يريد تصدير البيانات الأقدم من 2012 من جدول SQL Server إلى حاوية Azure Blob Storage. يريدون تشغيل استعلامات التقرير الخاصة بهم على هذه البيانات المصدرة بدلا من الاستعلام مباشرة عن 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> و <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 في مدخل Microsoft 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. ثم افتح قواعد البيانات>AdventureWorks2022>Tables>External Tables لمشاهدة الجداول الخارجية.

   

3. تأكد من ظهور المجلدات التالية في حاوية Azure Storage:

   

4. بعد تصدير البيانات الباردة، يمكنك حذفها من موقع الجدول الأصلي.

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

بيانات الاستعلام التي تتضمن الجدول الخارجي

يمكنك استخدام طريقة عرض أو بحث عن حرف بدل للاستعلام عن البيانات الخارجية المصدرة. كل أسلوب له مزايا وعيوب. يوصى باستخدام طريقة العرض للطلبات المتكررة لأنها عادة ما تعمل بشكل أفضل، ويمكن أيضا دمجها مع الجداول الفعلية. أسلوب البحث عن أحرف البدل أكثر مرونة وأسهل في الاستخدام لأغراض الاستكشاف.

استخدام طريقة عرض للاستعلام عن البيانات

الآن بعد أن تم تصدير البيانات القديمة وحذفها من قاعدة البيانات، يمكنك استخدام 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]

استخدام بحث حرف بدل للاستعلام عن البيانات

في المثال السابق، استخدمت طريقة عرض مع عبارات UNION للانضمام إلى الجداول الخارجية الثلاثة. هناك طريقة أخرى لتحقيق النتائج المرجوة وهي استخدام بحث عن أحرف البدل لفحص بنية المجلد، بما في ذلك المجلدات الفرعية، بحثا عن أي بيانات من نوع معين.

يستخدم مثال 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 والجداول الخارجية وطرق العرض والبحث في أحرف البدل ووظائف مسار الملف. يمكنك الوصول إلى البيانات وتصديرها من قواعد بيانات أخرى مثل SQL Server وOracle وTeradata وMongoDB أو من Azure Blob Storage أو Azure Data Lake Storage أو أي تخزين كائن متوافق مع S3. يمكن أن تساعدك CETAS في تصميم حلول ذات أداء دائم وقابلة للتطوير عبر جميع مصادر البيانات المدعومة من PolyBase.