تحميل بيانات البيع بالتجزئة في Contoso إلى مجموعات SQL مخصصة في Azure Synapse Analytics

في هذا البرنامج التعليمي، ستتعلم كيفية استخدام أوامر PolyBase وT-SQL لتحميل جدولين من بيانات البيع بالتجزئة في Contoso إلى مجموعات SQL مخصصة.

في هذا البرنامج التعليمي، ستتعرف علي ما يلي:

1. تكوين PolyBase للتحميل من تخزين Azure blob
2. تحميل البيانات العامة إلى قاعدة البيانات الخاصة بك
3. إجراء تحسينات بعد انتهاء التحميل.

قبل أن تبدأ

لتشغيل هذا البرنامج التعليمي، تحتاج إلى حساب Azure يحتوي بالفعل على مجموعة SQL مخصصة. إذا لم يكن لديك مستودع بيانات متوفر، فراجع إنشاء مستودع بيانات وتعيين قاعدة جدار الحماية على مستوى الخادم.

تكوين مصدر البيانات

تستخدم PolyBase عناصر T-SQL الخارجية لتحديد موقع البيانات الخارجية وسماتها. يتم تخزين تعريفات العناصر الخارجية في مجموعات SQL مخصصة. يتم تخزين البيانات خارجيًا.

إنشاء بيانات اعتماد

‏‏تخطَّ هذه الخطوة إذا كنت تقوم بتحميل بيانات Contoso العامة. لا تحتاج إلى وصول آمن إلى البيانات العامة نظرًا لأنه يمكن لأي شخص الوصول إليها بالفعل.

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

-- A: Create a master key.
-- Only necessary if one does not already exist.
-- Required to encrypt the credential secret in the next step.

CREATE MASTER KEY;


-- B: Create a database scoped credential
-- IDENTITY: Provide any string, it is not used for authentication to Azure storage.
-- SECRET: Provide your Azure storage account key.


CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
    IDENTITY = 'user',
    SECRET = '<azure_storage_account_key>'
;


-- C: Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure blob storage.
-- LOCATION: Provide Azure storage account name and blob container name.
-- CREDENTIAL: Provide the credential created in the previous step.

CREATE EXTERNAL DATA SOURCE AzureStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',
    CREDENTIAL = AzureStorageCredential
);

إنشاء مصدر بيانات خارجي

استخدم هذا الأمر CREATE EXTERNAL DATA SOURCE لتخزين موقع البيانات ونوع البيانات.

CREATE EXTERNAL DATA SOURCE AzureStorage_west_public
WITH
(  
    TYPE = Hadoop
,   LOCATION = 'wasbs://contosoretaildw-tables@contosoretaildw.blob.core.windows.net/'
);

هام

إذا اخترت جعل حاويات تخزين azure blob عامة، فتذكر أنه بصفتك مالك البيانات، ستتحمل رسوم خروج البيانات عند مغادرة البيانات لمركز البيانات.

تكوين تنسيق البيانات

يتم تخزين البيانات في ملفات نصية في تخزين Azure blob، ويتم فصل كل حقل بمحدد. في SSMS، قم بتشغيل الأمر CREATE EXTERNAL FILE FORMAT التالي لتحديد تنسيق البيانات في الملفات النصية. تكون بيانات Contoso غير مضغوطة ومفصولة بحرف التوجيه (|).

CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH
(   FORMAT_TYPE = DELIMITEDTEXT
,    FORMAT_OPTIONS    (   FIELD_TERMINATOR = '|'
                    ,    STRING_DELIMITER = ''
                    ,    DATE_FORMAT         = 'yyyy-MM-dd HH:mm:ss.fff'
                    ,    USE_TYPE_DEFAULT = FALSE
                    )
);

إنشاء مخطط للجداول الخارجية

الآن بعد أن حددت مصدر البيانات وتنسيق الملف، أصبحت جاهزًا لإنشاء مخطط للجداول الخارجية.

لإنشاء مكان لتخزين بيانات Contoso في قاعدة البيانات الخاصة بك، قم بإنشاء مخطط.

CREATE SCHEMA [asb]
GO

إنشاء الجداول الخارجية

قم بتشغيل البرنامج النصي التالي لإنشاء جداول DimProduct وFactOnlineSales الخارجية. كل ما تفعله هنا هو تعريف أسماء الأعمدة وأنواع البيانات، وربطها بموقع ملفات تخزين Azure blob وتنسيقها. يتم تخزين التعريف في مستودع البيانات والبيانات لا تزال في Azure Storage Blob.

معلمة LOCATION هي المجلد الموجود ضمن المجلد الجذر في Azure Storage Blob. يوجد كل جدول في مجلد مختلف.

--DimProduct
CREATE EXTERNAL TABLE [asb].DimProduct (
    [ProductKey] [int] NOT NULL,
    [ProductLabel] [nvarchar](255) NULL,
    [ProductName] [nvarchar](500) NULL,
    [ProductDescription] [nvarchar](400) NULL,
    [ProductSubcategoryKey] [int] NULL,
    [Manufacturer] [nvarchar](50) NULL,
    [BrandName] [nvarchar](50) NULL,
    [ClassID] [nvarchar](10) NULL,
    [ClassName] [nvarchar](20) NULL,
    [StyleID] [nvarchar](10) NULL,
    [StyleName] [nvarchar](20) NULL,
    [ColorID] [nvarchar](10) NULL,
    [ColorName] [nvarchar](20) NOT NULL,
    [Size] [nvarchar](50) NULL,
    [SizeRange] [nvarchar](50) NULL,
    [SizeUnitMeasureID] [nvarchar](20) NULL,
    [Weight] [float] NULL,
    [WeightUnitMeasureID] [nvarchar](20) NULL,
    [UnitOfMeasureID] [nvarchar](10) NULL,
    [UnitOfMeasureName] [nvarchar](40) NULL,
    [StockTypeID] [nvarchar](10) NULL,
    [StockTypeName] [nvarchar](40) NULL,
    [UnitCost] [money] NULL,
    [UnitPrice] [money] NULL,
    [AvailableForSaleDate] [datetime] NULL,
    [StopSaleDate] [datetime] NULL,
    [Status] [nvarchar](7) NULL,
    [ImageURL] [nvarchar](150) NULL,
    [ProductURL] [nvarchar](150) NULL,
    [ETLLoadID] [int] NULL,
    [LoadDate] [datetime] NULL,
    [UpdateDate] [datetime] NULL
)
WITH
(
    LOCATION='/DimProduct/'
,   DATA_SOURCE = AzureStorage_west_public
,   FILE_FORMAT = TextFileFormat
,   REJECT_TYPE = VALUE
,   REJECT_VALUE = 0
)
;

--FactOnlineSales
CREATE EXTERNAL TABLE [asb].FactOnlineSales
(
    [OnlineSalesKey] [int]  NOT NULL,
    [DateKey] [datetime] NOT NULL,
    [StoreKey] [int] NOT NULL,
    [ProductKey] [int] NOT NULL,
    [PromotionKey] [int] NOT NULL,
    [CurrencyKey] [int] NOT NULL,
    [CustomerKey] [int] NOT NULL,
    [SalesOrderNumber] [nvarchar](20) NOT NULL,
    [SalesOrderLineNumber] [int] NULL,
    [SalesQuantity] [int] NOT NULL,
    [SalesAmount] [money] NOT NULL,
    [ReturnQuantity] [int] NOT NULL,
    [ReturnAmount] [money] NULL,
    [DiscountQuantity] [int] NULL,
    [DiscountAmount] [money] NULL,
    [TotalCost] [money] NOT NULL,
    [UnitCost] [money] NULL,
    [UnitPrice] [money] NULL,
    [ETLLoadID] [int] NULL,
    [LoadDate] [datetime] NULL,
    [UpdateDate] [datetime] NULL
)
WITH
(
    LOCATION='/FactOnlineSales/'
,   DATA_SOURCE = AzureStorage_west_public
,   FILE_FORMAT = TextFileFormat
,   REJECT_TYPE = VALUE
,   REJECT_VALUE = 0
)
;

تحميل البيانات

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

إنشاء مخطط جديد

تنشئ CTAS جدولاً جديدًا يحتوي على بيانات. أولاً، قم بإنشاء مخطط لبيانات contoso.

CREATE SCHEMA [cso]
GO

تحميل البيانات إلى جداول جديدة

لتحميل البيانات من تخزين Azure blob إلى جدول مستودع البيانات، استخدم عبارة CREATE TABLE AS SELECT (Transact-SQL). يستفيد التحميل باستخدام CTAS من الجداول الخارجية محددة النوع بدقة التي قمت بإنشائها. لتحميل البيانات إلى جداول جديدة، استخدم عبارة CTAS واحدة لكل جدول.

تقوم CTAS بإنشاء جدول جديد وملئه بنتائج عبارة select. تعرّف CTAS الجدول الجديد ليكون له نفس الأعمدة وأنواع البيانات مثل نتائج عبارة select. إذا قمت بتحديد كل الأعمدة من جدول خارجي، فسيكون الجدول الجديد نسخة متماثلة من الأعمدة وأنواع البيانات في الجدول الخارجي.

في هذا المثال، نقوم بإنشاء كل من جدول الأبعاد وجدول الحقائق كجداول موزعة للتجزئة.

SELECT GETDATE();
GO

CREATE TABLE [cso].[DimProduct]            WITH (DISTRIBUTION = HASH([ProductKey]  ) ) AS SELECT * FROM [asb].[DimProduct]             OPTION (LABEL = 'CTAS : Load [cso].[DimProduct]             ');
CREATE TABLE [cso].[FactOnlineSales]       WITH (DISTRIBUTION = HASH([ProductKey]  ) ) AS SELECT * FROM [asb].[FactOnlineSales]        OPTION (LABEL = 'CTAS : Load [cso].[FactOnlineSales]        ');

تتبع تقدم التحميل

يمكنك تتبع تقدم التحميل باستخدام طرق عرض الإدارة الديناميكية (DMVs).

-- To see all requests
SELECT * FROM sys.dm_pdw_exec_requests;

-- To see a particular request identified by its label
SELECT * FROM sys.dm_pdw_exec_requests as r
WHERE r.[label] = 'CTAS : Load [cso].[DimProduct]             '
      OR r.[label] = 'CTAS : Load [cso].[FactOnlineSales]        '
;

-- To track bytes and files
SELECT
    r.command,
    s.request_id,
    r.status,
    count(distinct input_name) as nbr_files,
    sum(s.bytes_processed)/1024/1024/1024 as gb_processed
FROM
    sys.dm_pdw_exec_requests r
    inner join sys.dm_pdw_dms_external_work s
        on r.request_id = s.request_id
WHERE
    r.[label] = 'CTAS : Load [cso].[DimProduct]             '
    OR r.[label] = 'CTAS : Load [cso].[FactOnlineSales]        '
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files desc,
    gb_processed desc;

تحسين ضغط تخزين الأعمدة

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

لتحسين أداء الاستعلام وضغط تخزين الأعمدة بعد التحميل، أعد إنشاء الجدول لإجبار مؤشر تخزين الأعمدة على ضغط جميع الصفوف.

SELECT GETDATE();
GO

ALTER INDEX ALL ON [cso].[DimProduct]               REBUILD;
ALTER INDEX ALL ON [cso].[FactOnlineSales]          REBUILD;

لمزيد من المعلومات حول صيانة مؤشرات تخزين الأعمدة، راجع مقالة إدارة مؤشرات تخزين الأعمدة.

تحسين الإحصائيات

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

إذا قررت إنشاء إحصائيات ذات عمود واحد على كل عمود من كل جدول، فيمكنك استخدام نموذج التعليمات البرمجية للإجراء المُخزّن prc_sqldw_create_stats في مقالة الإحصائيات.

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

CREATE STATISTICS [stat_cso_DimProduct_AvailableForSaleDate] ON [cso].[DimProduct]([AvailableForSaleDate]);
CREATE STATISTICS [stat_cso_DimProduct_BrandName] ON [cso].[DimProduct]([BrandName]);
CREATE STATISTICS [stat_cso_DimProduct_ClassID] ON [cso].[DimProduct]([ClassID]);
CREATE STATISTICS [stat_cso_DimProduct_ClassName] ON [cso].[DimProduct]([ClassName]);
CREATE STATISTICS [stat_cso_DimProduct_ColorID] ON [cso].[DimProduct]([ColorID]);
CREATE STATISTICS [stat_cso_DimProduct_ColorName] ON [cso].[DimProduct]([ColorName]);
CREATE STATISTICS [stat_cso_DimProduct_ETLLoadID] ON [cso].[DimProduct]([ETLLoadID]);
CREATE STATISTICS [stat_cso_DimProduct_ImageURL] ON [cso].[DimProduct]([ImageURL]);
CREATE STATISTICS [stat_cso_DimProduct_LoadDate] ON [cso].[DimProduct]([LoadDate]);
CREATE STATISTICS [stat_cso_DimProduct_Manufacturer] ON [cso].[DimProduct]([Manufacturer]);
CREATE STATISTICS [stat_cso_DimProduct_ProductDescription] ON [cso].[DimProduct]([ProductDescription]);
CREATE STATISTICS [stat_cso_DimProduct_ProductKey] ON [cso].[DimProduct]([ProductKey]);
CREATE STATISTICS [stat_cso_DimProduct_ProductLabel] ON [cso].[DimProduct]([ProductLabel]);
CREATE STATISTICS [stat_cso_DimProduct_ProductName] ON [cso].[DimProduct]([ProductName]);
CREATE STATISTICS [stat_cso_DimProduct_ProductSubcategoryKey] ON [cso].[DimProduct]([ProductSubcategoryKey]);
CREATE STATISTICS [stat_cso_DimProduct_ProductURL] ON [cso].[DimProduct]([ProductURL]);
CREATE STATISTICS [stat_cso_DimProduct_Size] ON [cso].[DimProduct]([Size]);
CREATE STATISTICS [stat_cso_DimProduct_SizeRange] ON [cso].[DimProduct]([SizeRange]);
CREATE STATISTICS [stat_cso_DimProduct_SizeUnitMeasureID] ON [cso].[DimProduct]([SizeUnitMeasureID]);
CREATE STATISTICS [stat_cso_DimProduct_Status] ON [cso].[DimProduct]([Status]);
CREATE STATISTICS [stat_cso_DimProduct_StockTypeID] ON [cso].[DimProduct]([StockTypeID]);
CREATE STATISTICS [stat_cso_DimProduct_StockTypeName] ON [cso].[DimProduct]([StockTypeName]);
CREATE STATISTICS [stat_cso_DimProduct_StopSaleDate] ON [cso].[DimProduct]([StopSaleDate]);
CREATE STATISTICS [stat_cso_DimProduct_StyleID] ON [cso].[DimProduct]([StyleID]);
CREATE STATISTICS [stat_cso_DimProduct_StyleName] ON [cso].[DimProduct]([StyleName]);
CREATE STATISTICS [stat_cso_DimProduct_UnitCost] ON [cso].[DimProduct]([UnitCost]);
CREATE STATISTICS [stat_cso_DimProduct_UnitOfMeasureID] ON [cso].[DimProduct]([UnitOfMeasureID]);
CREATE STATISTICS [stat_cso_DimProduct_UnitOfMeasureName] ON [cso].[DimProduct]([UnitOfMeasureName]);
CREATE STATISTICS [stat_cso_DimProduct_UnitPrice] ON [cso].[DimProduct]([UnitPrice]);
CREATE STATISTICS [stat_cso_DimProduct_UpdateDate] ON [cso].[DimProduct]([UpdateDate]);
CREATE STATISTICS [stat_cso_DimProduct_Weight] ON [cso].[DimProduct]([Weight]);
CREATE STATISTICS [stat_cso_DimProduct_WeightUnitMeasureID] ON [cso].[DimProduct]([WeightUnitMeasureID]);
CREATE STATISTICS [stat_cso_FactOnlineSales_CurrencyKey] ON [cso].[FactOnlineSales]([CurrencyKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_CustomerKey] ON [cso].[FactOnlineSales]([CustomerKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_DateKey] ON [cso].[FactOnlineSales]([DateKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_OnlineSalesKey] ON [cso].[FactOnlineSales]([OnlineSalesKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_ProductKey] ON [cso].[FactOnlineSales]([ProductKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_PromotionKey] ON [cso].[FactOnlineSales]([PromotionKey]);
CREATE STATISTICS [stat_cso_FactOnlineSales_StoreKey] ON [cso].[FactOnlineSales]([StoreKey]);

تحقق الإنجاز!

لقد قمت بتحميل البيانات العامة إلى مستودع البيانات الخاص بك بنجاح. عمل رائع!

يمكنك الآن البدء في الاستعلام عن الجداول لاستكشاف البيانات. قم بتشغيل الاستعلام التالي لمعرفة إجمالي المبيعات لكل علامة تجارية:

SELECT  SUM(f.[SalesAmount]) AS [sales_by_brand_amount]
,       p.[BrandName]
FROM    [cso].[FactOnlineSales] AS f
JOIN    [cso].[DimProduct]      AS p ON f.[ProductKey] = p.[ProductKey]
GROUP BY p.[BrandName]

الخطوات التالية

لتحميل مجموعة البيانات الكاملة، قم بتشغيل نموذج تحميل مستودع بيانات البيع بالتجزئة في Contoso الكامل من مستودع نماذج Microsoft SQL Server. لمزيد من تلميحات التطوير، راجع قرارات التصميم وتقنيات الترميز لمستودعات البيانات.