Contoso-retailgegevens laden in toegewezen SQL-pools in Azure Synapse Analytics

Artikel
06/01/2023

In deze zelfstudie leert u hoe u PolyBase- en T-SQL-opdrachten kunt gebruiken om twee tabellen uit de Contoso-retailgegevens in toegewezen SQL-pools te laden.

In deze zelfstudie gaat u:

PolyBase configureren om te laden vanuit Azure Blob Storage
Openbare gegevens laden in uw database
Optimalisaties uitvoeren nadat het laden is voltooid.

Voordat u begint

Als u deze zelfstudie wilt uitvoeren, hebt u een Azure-account nodig dat al een toegewezen SQL-pool heeft. Als u geen datawarehouse hebt ingericht, raadpleegt u Een datawarehouse maken en firewallregel op serverniveau instellen.

De gegevensbron configureren

PolyBase gebruikt externe T-SQL-objecten om de locatie en kenmerken van de externe gegevens te definiëren. De externe objectdefinities worden opgeslagen in toegewezen SQL-pools. De gegevens worden extern opgeslagen.

Een referentie maken

Sla deze stap over als u de openbare Contoso-gegevens laadt. U hebt geen beveiligde toegang tot de openbare gegevens nodig, omdat deze al voor iedereen toegankelijk zijn.

Sla deze stap niet over als u deze zelfstudie gebruikt als een sjabloon voor het laden van uw eigen gegevens. Als u toegang wilt krijgen tot gegevens via een referentie, gebruikt u het volgende script om een referentie met databasebereik te maken. Gebruik deze vervolgens bij het definiëren van de locatie van de gegevensbron.

-- 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
);

De externe gegevensbron maken

Gebruik deze opdracht CREATE EXTERNAL DATA SOURCE om de locatie van de gegevens en het gegevenstype op te slaan.

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

Belangrijk

Als u ervoor kiest om uw Azure Blob Storage-containers openbaar te maken, moet u er rekening mee houden dat als gegevenseigenaar kosten in rekening worden gebracht voor uitgaande gegevens wanneer gegevens het datacenter verlaten.

De gegevensindeling configureren

De gegevens worden opgeslagen in tekstbestanden in Azure Blob Storage en elk veld wordt gescheiden door een scheidingsteken. Voer in SSMS de volgende opdracht CREATE EXTERNAL FILE FORMAT uit om de indeling van de gegevens in de tekstbestanden op te geven. De Contoso-gegevens worden gedecomprimeerd en door sluistekens gescheiden.

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
                    )
);

Het schema voor de externe tabellen maken

Nu u de gegevensbron en bestandsindeling hebt opgegeven, bent u klaar om het schema voor de externe tabellen te maken.

Als u een locatie wilt maken voor het opslaan van de Contoso-gegevens in uw database, maakt u een schema.

CREATE SCHEMA [asb]
GO

De externe tabellen maken

Voer het volgende script uit om de externe tabellen DimProduct en FactOnlineSales te maken. U hoeft alleen maar kolomnamen en gegevenstypen te definiëren en deze te koppelen aan de locatie en indeling van de Azure Blob Storage-bestanden. De definitie wordt opgeslagen in het datawarehouse en de gegevens bevinden zich nog steeds in de Azure Storage-blob.

De parameter LOCATION is de map onder de hoofdmap in de Azure Storage-blob. Elke tabel bevindt zich in een andere map.

--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
)
;

De gegevens laden

Er zijn verschillende manieren om toegang te krijgen tot externe gegevens. U kunt gegevens rechtstreeks vanuit de externe tabellen opvragen, de gegevens laden in nieuwe tabellen in het datawarehouse of externe gegevens toevoegen aan bestaande datawarehousetabellen.

Een nieuw schema maken

CTAS maakt een nieuwe tabel die gegevens bevat. Maak eerst een schema voor de contoso-gegevens.

CREATE SCHEMA [cso]
GO

De gegevens in nieuwe tabellen laden

Als u gegevens uit Azure Blob Storage in de datawarehouse-tabel wilt laden, gebruikt u de transact-SQL-instructie CREATE TABLE AS SELECT (Transact-SQL). Laden met CTAS maakt gebruik van de sterk getypeerde externe tabellen die u hebt gemaakt. Als u de gegevens in nieuwe tabellen wilt laden, gebruikt u één CTAS-instructie per tabel.

CTAS maakt een nieuwe tabel en vult deze met de resultaten van een select-instructie. CTAS definieert de nieuwe tabel met dezelfde kolommen en gegevenstypen als de resultaten van de select-instructie. Als u alle kolommen uit een externe tabel selecteert, wordt de nieuwe tabel een replica van de kolommen en gegevenstypen in de externe tabel.

In dit voorbeeld maken we zowel de dimensie als de feitentabel als hash gedistribueerde tabellen.

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]        ');

De voortgang van het laden bijhouden

U kunt de voortgang van uw belasting bijhouden met behulp van dynamische beheerweergaven (DMV's).

-- 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;

Columnstore-compressie optimaliseren

Toegewezen SQL-pools slaan de tabel standaard op als een geclusterde columnstore-index. Nadat het laden is voltooid, worden sommige gegevensrijen mogelijk niet gecomprimeerd in de columnstore. Er zijn verschillende redenen waarom dit kan gebeuren. Zie columnstore-indexen beheren voor meer informatie.

Als u de queryprestaties en columnstore-compressie na het laden wilt optimaliseren, herbouwt u de tabel om de columnstore-index af te dwingen alle rijen te comprimeren.

SELECT GETDATE();
GO

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

Zie het artikel columnstore-indexen beheren voor meer informatie over het onderhouden van columnstore-indexen .

Statistieken optimaliseren

U kunt het beste statistieken met één kolom maken direct na het laden. Als u weet dat bepaalde kolommen niet in querypredicaten worden opgenomen, kunt u het maken van statistieken voor die kolommen overslaan. Als u statistieken met één kolom maakt voor elke kolom, kan het lang duren voordat alle statistieken opnieuw zijn opgebouwd.

Als u besluit om statistieken met één kolom te maken voor elke kolom van elke tabel, kunt u het codevoorbeeld prc_sqldw_create_stats voor de opgeslagen procedure gebruiken in het artikel Statistieken .

Het volgende voorbeeld is een goed uitgangspunt voor het maken van statistieken. Hiermee worden statistieken met één kolom gemaakt voor elke kolom in de dimensietabel en voor elke samenvoegingskolom in de feitentabellen. U kunt later altijd statistieken met één of meerdere kolommen toevoegen aan andere feitentabelkolommen.

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]);

Prestatie ontgrendeld!

U hebt openbare gegevens in uw datawarehouse geladen. Helemaal goed!

U kunt nu een query uitvoeren op de tabellen om uw gegevens te verkennen. Voer de volgende query uit om de totale verkoop per merk te achterhalen:

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]

Volgende stappen

Als u de volledige gegevensset wilt laden, voert u het voorbeeld uit om het volledige contoso-datawarehouse voor de detailhandel te laden vanuit de opslagplaats met Microsoft SQL Server voorbeelden. Zie Ontwerpbeslissingen en coderingstechnieken voor datawarehouses voor meer tips voor ontwikkeling.