Načtení maloobchodních dat společnosti Contoso do vyhrazených fondů SQL ve službě Azure Synapse Analytics
V tomto kurzu se naučíte používat příkazy PolyBase a T-SQL k načtení dvou tabulek z maloobchodních dat Společnosti Contoso do vyhrazených fondů SQL.
V tomto kurzu:
- Konfigurace PolyBase pro načtení 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ženy 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 je už přístupný komukoli.
Pokud tento kurz používáte jako šablonu pro načítání vlastních dat, tento krok nepřekočte. Pokud chcete získat přístup k datům prostřednictvím přihlašovacích údajů, použijte následující skript k vytvoření přihlašovacích údajů 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 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, že kontejnery azure Blob Storage budou veřejné, nezapomeňte, že jako vlastník dat se vám budou účtovat poplatky za výchozí přenos dat, když data opustí datacentrum.
Konfigurace formátu dat
Data jsou uložená v textových souborech v úložišti objektů blob v Azure a každé pole je oddělené oddělovačem. V nástroji SSMS spusťte následující příkaz CREATE EXTERNAL FILE FORMAT a určete formát dat v textových souborech. Data společnosti Contoso jsou nekomprimovaná a oddělená svislou sadou.
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 Contoso do databáze, 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. Všechno, co tady děláte, je definování názvů sloupců a datových typů a jejich vazba na umístění a formát souborů úložiště objektů blob v Azure. 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 data 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. Pokud chcete načíst data do nových tabulek, použijte jeden příkaz CTAS na 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, nová tabulka bude replikou sloupců a datových typů v externí tabulce.
V tomto příkladu vytvoříme dimenzi i tabulku faktů jako tabulky s distribuovanými hodnotami 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 (DMV).
-- 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čtení nemusí být některé řádky dat komprimovány 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, aby index columnstore zkomprimuje všechny řádky.
SELECT GETDATE();
GO
ALTER INDEX ALL ON [cso].[DimProduct] REBUILD;
ALTER INDEX ALL ON [cso].[FactOnlineSales] REBUILD;
Další informace o údržbě indexů columnstore najdete v článku správa indexů columnstore.
Optimalizace statistiky
Nejlepší je vytvořit statistiky s jedním sloupcem hned po načtení. Pokud víte, že určité sloupce nebudou v predikátech dotazů, můžete přeskočit vytváření statistik pro tyto sloupce. Pokud pro každý sloupec vytváříte statistiky s jedním sloupcem, může opětovné sestavení všech statistik trvat delší dobu.
Pokud se rozhodnete vytvořit statistiky s jedním sloupcem pro každý sloupec každé tabulky, můžete použít vzorový kód prc_sqldw_create_stats uložené procedury v článku statistiky .
Následující příklad je dobrým výchozím bodem pro vytváření statistik. Vytvoří statistiku s jedním sloupcem pro každý sloupec v tabulce dimenzí a na každém spojovacím sloupci v tabulkách faktů. Statistiky s jedním nebo více sloupci můžete kdykoli později přidat do jiných sloupců tabulky faktů.
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 byl odemknut!
Úspěšně jste načetli veřejná data do datového skladu. Skvělá práce!
Teď můžete začít dotazovat tabulky, abyste prozkoumali svá data. Spuštěním následujícího dotazu zjistíte celkové tržby za značku:
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í kompletního maloobchodního datového skladu Contoso z úložiště ukázek Microsoft SQL Serveru.