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

مقالة
05/02/2022
قراءة خلال 7 دقائق

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

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

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

قبل أن تبدأ

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

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

يستخدم PolyBase كائنات خارجية 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 AzureStorage_west_public
WITH
(  
    TYPE = Hadoop
,   LOCATION = 'wasbs://contosoretaildw-tables@contosoretaildw.blob.core.windows.net/'
);

هام

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

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

يتم تخزين البيانات في ملفات نصية في وحدة تخزين نقطة Azure، ويتم فصل كل حقل بمحدد. في SSMS، قم بتشغيل الأمر إنشاء تنسيق ملف خارجي التالي لتحديد تنسيق البيانات في الملفات النصية. بيانات 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 إلى جدول مستودع البيانات، استخدم العبارة إنشاء جدول كمحدد (SQL المعاملات). يؤدي التحميل باستخدام CTAS إلى الاستفادة من الجداول الخارجية المكتوبة بقوة التي أنشأتها. لتحميل البيانات في جداول جديدة، استخدم عبارة CTAS واحدة لكل جدول.

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

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

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 المخصصة بتخزين الجدول كفهرس columnstore متفاوت المسافات. بعد اكتمال التحميل، قد لا يتم ضغط بعض صفوف البيانات في columnstore. هناك أسباب مختلفة وراء حدوث ذلك. لمعرفة المزيد، راجع إدارة فهارس columnstore.

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

SELECT GETDATE();
GO

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

لمزيد من المعلومات حول الحفاظ على فهارس columnstore، راجع مقالة إدارة فهارس columnstore .

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

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

إذا قررت إنشاء إحصائيات أحادية العمود على كل عمود من كل جدول، فيمكنك استخدام نموذج 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. لمزيد من نصائح التطوير، راجع تصميم القرارات وتقنيات الترميز لمستودعات البيانات.