Načtení maloobchodních dat společnosti Contoso do vyhrazených fondů SQL v Azure Synapse Analytics
V tomto kurzu zjistíte, jak pomocí příkazů PolyBase a T-SQL načíst dvě tabulky z maloobchodních dat společnosti Contoso do vyhrazených fondů SQL.
V tomto kurzu:
- Konfigurace PolyBase pro načítání z úložiště objektů blob v Azure
- Načtení veřejných dat do databáze
- Po dokončení načítání proveďte optimalizace.
Než začnete
Ke spuštění tohoto kurzu potřebujete účet Azure, který už má vyhrazený fond SQL. Pokud nemáte zřízený datový sklad, přečtěte si téma Vytvoření datového skladu a nastavení pravidla brány firewall na úrovni serveru.
Konfigurace zdroje dat
PolyBase používá externí objekty T-SQL k definování umístění a atributů externích dat. Definice externích objektů jsou uložené ve vyhrazených fondech SQL. Data se ukládají externě.
Vytvoření přihlašovacích údajů
Pokud načítáte veřejná data společnosti Contoso, tento krok přeskočte. Nepotřebujete zabezpečený přístup k veřejným datům, protože jsou už přístupná komukoli.
Tento krok nepřeskakujte , pokud tento kurz používáte jako šablonu pro načítání vlastních dat. Pokud chcete získat přístup k datům prostřednictvím přihlašovacích údajů, pomocí následujícího skriptu vytvořte přihlašovací údaje v oboru databáze. Pak ho použijte při definování umístění zdroje dat.
-- 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
);
Vytvoření externího zdroje dat
Tento příkaz CREATE EXTERNAL DATA SOURCE (VYTVOŘIT EXTERNÍ ZDROJ DAT ) slouží k uložení umístění dat a datového typu.
CREATE EXTERNAL DATA SOURCE AzureStorage_west_public
WITH
(
TYPE = Hadoop
, LOCATION = 'wasbs://contosoretaildw-tables@contosoretaildw.blob.core.windows.net/'
);
Důležité
Pokud se rozhodnete kontejnery azure Blob Storage nastavit jako veřejné, nezapomeňte, že jako vlastníkovi dat se vám budou účtovat poplatky za výchozí přenos dat, když data opustí datové centrum.
Konfigurace formátu dat
Data jsou uložená v textových souborech v úložišti objektů blob v Azure a jednotlivá pole jsou oddělená oddělovačem. V aplikaci SSMS spusťte následující příkaz CREATE EXTERNAL FILE FORMAT, který určí formát dat v textových souborech. Data společnosti Contoso jsou nekomprimovaná a jsou oddělená kanály.
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
)
);
Vytvoření schématu pro externí tabulky
Teď, když jste zadali zdroj dat a formát souboru, jste připraveni vytvořit schéma pro externí tabulky.
Pokud chcete vytvořit místo pro ukládání dat Společnosti Contoso v databázi, vytvořte schéma.
CREATE SCHEMA [asb]
GO
Vytvoření externích tabulek
Spuštěním následujícího skriptu vytvořte externí tabulky DimProduct a FactOnlineSales. Jediné, co tady děláte, je definování názvů sloupců a datových typů a jejich vytvoření vazby na umístění a formát souborů služby Azure Blob Storage. Definice je uložená v datovém skladu a data jsou stále v objektu blob služby Azure Storage.
Parametr LOCATION je složka v kořenové složce v objektu blob služby Azure Storage. Každá tabulka je v jiné složce.
--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
)
;
Načtení dat
Existují různé způsoby přístupu k externím datům. Data můžete dotazovat přímo z externích tabulek, načíst je do nových tabulek v datovém skladu nebo přidat externí data do existujících tabulek datového skladu.
Vytvoření nového schématu
CTAS vytvoří novou tabulku, která obsahuje data. Nejprve vytvořte schéma pro data contoso.
CREATE SCHEMA [cso]
GO
Načtení dat do nových tabulek
Pokud chcete načíst data z úložiště objektů blob v Azure do tabulky datového skladu, použijte příkaz CREATE TABLE AS SELECT (Transact-SQL). Načítání pomocí CTAS využívá externí tabulky silného typu, které jste vytvořili. K načtení dat do nových tabulek použijte jeden příkaz CTAS pro každou tabulku.
CTAS vytvoří novou tabulku a naplní ji výsledky příkazu select. CTAS definuje novou tabulku tak, aby měla stejné sloupce a datové typy jako výsledky příkazu select. Pokud vyberete všechny sloupce z externí tabulky, bude nová tabulka replikou sloupců a datových typů v externí tabulce.
V tomto příkladu vytvoříme dimenzi i tabulku faktů jako distribuované tabulky hash.
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] ');
Sledování průběhu načítání
Průběh načítání můžete sledovat pomocí zobrazení dynamické správy.
-- 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;
Optimalizace komprese columnstore
Ve výchozím nastavení vyhrazené fondy SQL ukládají tabulku jako clusterovaný index columnstore. Po dokončení načítání se některé řádky dat nemusí zkomprimovat do columnstore. K tomu může dojít z různých důvodů. Další informace najdete v tématu správa indexů columnstore.
Pokud chcete optimalizovat výkon dotazů a kompresi columnstore po načtení, znovu sestavte tabulku tak, aby index columnstore zkomprimovat všechny řádky.
SELECT GETDATE();
GO
ALTER INDEX ALL ON [cso].[DimProduct] REBUILD;
ALTER INDEX ALL ON [cso].[FactOnlineSales] REBUILD;
Další informace o správě indexů columnstore najdete v článku Správa indexů columnstore .
Optimalizace statistik
Nejlepší je vytvořit statistiku s jedním sloupcem hned po načtení. Pokud víte, že některé sloupce nebudou v predikátech dotazů, můžete vytváření statistik pro tyto sloupce přeskočit. Pokud vytvoříte statistiky s jedním sloupcem, může opětovné sestavení všech statistik trvat dlouho.
Pokud se rozhodnete vytvořit jednosloupkové statistiky pro každý sloupec každé tabulky, můžete použít ukázku prc_sqldw_create_stats kódu uložené procedury v článku o statistikách .
Následující příklad je dobrým výchozím bodem pro vytváření statistik. Vytvoří jednosloupkové statistiky pro každý sloupec v tabulce dimenzí a pro každý spojovací sloupec v tabulkách faktů. Do jiných sloupců tabulky faktů můžete později vždy přidat jednosloupkové nebo vícesloupkové statistiky.
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]);
Úspěch odemknut!
Úspěšně jste do datového skladu načetli veřejná data. Skvělá práce!
Teď můžete začít dotazovat tabulky a zkoumat data. Spuštěním následujícího dotazu zjistěte celkový prodej podle značky:
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]
Další kroky
Pokud chcete načíst úplnou sadu dat, spusťte příklad načtení celého maloobchodního datového skladu Contoso z úložiště ukázek Microsoft SQL Server. Další tipy pro vývoj najdete v tématu Rozhodování o návrhu a techniky kódování pro datové sklady.
Váš názor
Připravujeme: V průběhu roku 2024 budeme postupně vyřazovat problémy z GitHub coby mechanismus zpětné vazby pro obsah a nahrazovat ho novým systémem zpětné vazby. Další informace naleznete v tématu: https://aka.ms/ContentUserFeedback.
Odeslat a zobrazit názory pro