A Contoso kiskereskedelmi adatainak betöltése dedikált SQL-készletekbe az Azure Synapse Analyticsben

Ebben az oktatóanyagban a PolyBase és a T-SQL parancsokkal tölthet be két táblát a Contoso kiskereskedelmi adataiból dedikált SQL-készletekbe.

Ebben az oktatóanyagban a következőket fogja tudni:

A PolyBase konfigurálása az Azure Blob Storage-ból való betöltéshez
Nyilvános adatok betöltése az adatbázisba
A terhelés befejezése után optimalizálásokat végezhet.

Mielőtt elkezdené

Az oktatóanyag futtatásához szüksége lesz egy Azure-fiókra, amely már rendelkezik dedikált SQL-készletel. Ha nincs kiépítve adattárház, olvassa el az Adattárház létrehozása és a kiszolgálószintű tűzfalszabály beállítása című témakört.

Az adatforrás konfigurálása

A PolyBase T-SQL külső objektumokkal határozza meg a külső adatok helyét és attribútumait. A külső objektumdefiníciók dedikált SQL-készletekben vannak tárolva. Az adatok tárolása külsőleg történik.

Hitelesítő adatok létrehozása

Hagyja ki ezt a lépést , ha a Contoso nyilvános adatait tölti be. Nincs szüksége biztonságos hozzáférésre a nyilvános adatokhoz, mivel azok már elérhetők bárki számára.

Ne hagyja ki ezt a lépést , ha ezt az oktatóanyagot sablonként használja saját adatai betöltéséhez. Ha hitelesítő adatokon keresztül szeretne adatokat elérni, az alábbi szkripttel hozzon létre egy adatbázis-hatókörű hitelesítő adatot. Ezután használja az adatforrás helyének meghatározásához.

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

A külső adatforrás létrehozása

Ezzel a CREATE EXTERNAL DATA SOURCE paranccsal tárolhatja az adatok helyét és az adattípust.

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

Fontos

Ha úgy dönt, hogy nyilvánossá teszi az Azure Blob Storage-tárolókat, ne feledje, hogy adattulajdonosként az adatforgalomért díjat kell fizetnie, amikor az adatok elhagyják az adatközpontot.

Az adatformátum konfigurálása

Az adatok az Azure Blob Storage-ban szövegfájlokban tárolódnak, és minden mező elválasztójellel van elválasztva. Az SSMS-ben futtassa a következő CREATE EXTERNAL FILE FORMAT parancsot a szövegfájlokban lévő adatok formátumának megadásához. A Contoso-adatok tömörítetlenek, és a cső el van tagolva.

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

A külső táblák sémájának létrehozása

Most, hogy megadta az adatforrást és a fájlformátumot, készen áll a külső táblák sémájának létrehozására.

Ha létre szeretne hozni egy helyet a Contoso-adatok adatbázisban való tárolásához, hozzon létre egy sémát.

CREATE SCHEMA [asb]
GO

A külső táblák létrehozása

Futtassa a következő szkriptet a DimProduct és a FactOnlineSales külső táblák létrehozásához. Itt csak oszlopneveket és adattípusokat definiál, és azOkat az Azure Blob Storage-fájlok helyéhez és formátumához köti. A definíció az adattárházban van tárolva, és az adatok továbbra is az Azure Storage Blobban találhatók.

A LOCATION paraméter az Azure Storage Blob gyökérmappája alatti mappa. Minden tábla egy másik mappában található.

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

Az adatok betöltése

A külső adatok elérésének különböző módjai vannak. Közvetlenül a külső táblákból kérdezhet le adatokat, betöltheti az adatokat az adattárház új tábláiba, vagy külső adatokat adhat hozzá a meglévő adattárháztáblákhoz.

Új séma létrehozása

A CTAS létrehoz egy új táblát, amely adatokat tartalmaz. Először hozzon létre egy sémát a contoso-adatokhoz.

CREATE SCHEMA [cso]
GO

Adatok betöltése új táblákba

Ha adatokat szeretne betölteni az Azure Blob Storage-ból az adattárház táblába, használja a CREATE TABLE AS SELECT (Transact-SQL) utasítást. A CTAS-sel való betöltés az ön által létrehozott, erősen gépelt külső táblákat használja. Az adatok új táblákba való betöltéséhez táblánként egy CTAS-utasítást használjon.

A CTAS létrehoz egy új táblát, és kitölti egy kiválasztási utasítás eredményével. A CTAS úgy határozza meg az új táblát, hogy a kiválasztási utasítás eredményével megegyező oszlopokkal és adattípusokkal rendelkezzen. Ha egy külső tábla összes oszlopát kijelöli, az új tábla a külső tábla oszlopainak és adattípusainak replikája lesz.

Ebben a példában a dimenziót és a ténytáblát kivonatelosztott táblákként hozzuk létre.

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

A terhelés előrehaladásának nyomon követése

A terhelés előrehaladását dinamikus felügyeleti nézetek (DMV-k) használatával követheti nyomon.

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

Oszlopcentrikus tömörítés optimalizálása

A dedikált SQL-készletek alapértelmezés szerint fürtözött oszlopcentrikus indexként tárolják a táblát. A betöltés befejezése után előfordulhat, hogy egyes adatsorok nem lesznek tömörítve az oszloptárba. Ennek különböző okai lehetnek. További információ: oszlopcentrikus indexek kezelése.

A lekérdezési teljesítmény és az oszlopcentrikus tömörítés terhelés utáni optimalizálásához építse újra a táblát, hogy az oszlopcentrikus index az összes sort tömörítse.

SELECT GETDATE();
GO

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

Az oszlopcentrikus indexek karbantartásával kapcsolatos további információkért tekintse meg az oszlopcentrikus indexek kezeléséről szóló cikket.

Statisztikák optimalizálása

A legjobb, ha egyoszlopos statisztikákat hoz létre közvetlenül a betöltés után. Ha tudja, hogy bizonyos oszlopok nem lesznek lekérdezési predikátumokban, kihagyhatja a statisztikák létrehozását ezeken az oszlopokon. Ha minden oszlopon egyoszlopos statisztikát hoz létre, az összes statisztika újraépítése hosszú időt vehet igénybe.

Ha úgy dönt, hogy minden tábla minden oszlopában egyoszlopos statisztikákat hoz létre, a statisztikai cikkben szereplő tárolt eljáráskódmintát prc_sqldw_create_statshasználhatja .

Az alábbi példa jó kiindulópont a statisztikák létrehozásához. Egyoszlopos statisztikákat hoz létre a dimenziótábla minden egyes oszlopán, valamint a ténytáblák minden egyes összekapcsolt oszlopán. Később bármikor hozzáadhat egy- vagy többoszlopos statisztikákat más ténytáblák oszlopaihoz.

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

Az eredmény feloldva!

Sikeresen betöltötte a nyilvános adatokat az adattárházba. Remek!

Most már megkezdheti a táblák lekérdezését az adatok feltárásához. Futtassa a következő lekérdezést a márkánkénti összes értékesítés megkereséséhez:

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]

Következő lépések

A teljes adatkészlet betöltéséhez futtassa a példában a Teljes Contoso kiskereskedelmi adattárház betöltését a Microsoft SQL Server-minták adattárából.

Visszajelzés

Hasznosnak találta ezt az oldalt?

Last updated on 2024-12-29