Läs in Contosos detaljhandelsdata i dedikerade SQL-pooler i Azure Synapse Analytics
I den här självstudien lär du dig att använda PolyBase- och T-SQL-kommandon för att läsa in två tabeller från Contosos detaljhandelsdata till dedikerade SQL-pooler.
I den här självstudien kommer du att:
- Konfigurera PolyBase för att läsa in från Azure Blob Storage
- Läsa in offentliga data i databasen
- Utför optimeringar när belastningen är klar.
Innan du börjar
Om du vill köra den här självstudien behöver du ett Azure-konto som redan har en dedikerad SQL-pool. Om du inte har ett etablerat informationslager kan du läsa Skapa ett informationslager och ange brandväggsregel på servernivå.
Konfigurera datakällan
PolyBase använder externa T-SQL-objekt för att definiera platsen och attributen för externa data. De externa objektdefinitionerna lagras i dedikerade SQL-pooler. Data lagras externt.
Skapa en autentiseringsuppgift
Hoppa över det här steget om du läser in offentliga Contoso-data. Du behöver inte säker åtkomst till offentliga data eftersom de redan är tillgängliga för någon.
Hoppa inte över det här steget om du använder den här självstudien som mall för att läsa in dina egna data. Om du vill komma åt data via en autentiseringsuppgift använder du följande skript för att skapa en databasomfattande autentiseringsuppgift. Använd den sedan när du definierar platsen för datakällan.
-- 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
);
Skapa den externa datakällan
Använd kommandot CREATE EXTERNAL DATA SOURCE (SKAPA EXTERN DATAKÄLLA) för att lagra platsen för data och datatypen.
CREATE EXTERNAL DATA SOURCE AzureStorage_west_public
WITH
(
TYPE = Hadoop
, LOCATION = 'wasbs://contosoretaildw-tables@contosoretaildw.blob.core.windows.net/'
);
Viktigt
Om du väljer att göra dina Azure Blob Storage-containrar offentliga ska du komma ihåg att du som dataägare debiteras för utgående dataavgifter när data lämnar datacentret.
Konfigurera dataformatet
Data lagras i textfiler i Azure Blob Storage och varje fält avgränsas med en avgränsare. I SSMS kör du följande CREATE EXTERNAL FILE FORMAT-kommando för att ange formatet för data i textfilerna. Contoso-data är okomprimerade och röravgränsade.
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
)
);
Skapa schemat för de externa tabellerna
Nu när du har angett datakällan och filformatet är du redo att skapa schemat för de externa tabellerna.
Skapa ett schema för att skapa en plats där Contoso-data ska lagras i databasen.
CREATE SCHEMA [asb]
GO
Skapa de externa tabellerna
Kör följande skript för att skapa de externa tabellerna DimProduct och FactOnlineSales. Allt du gör här är att definiera kolumnnamn och datatyper och binda dem till platsen och formatet för Azure Blob Storage-filerna. Definitionen lagras i informationslagret och data finns fortfarande i Azure Storage Blob.
Parametern LOCATION är mappen under rotmappen i Azure Storage Blob. Varje tabell finns i en annan mapp.
--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
)
;
Läsa in data
Det finns olika sätt att komma åt externa data. Du kan fråga data direkt från de externa tabellerna, läsa in data i nya tabeller i informationslagret eller lägga till externa data i befintliga informationslagertabeller.
Skapa ett nytt schema
CTAS skapar en ny tabell som innehåller data. Skapa först ett schema för contoso-data.
CREATE SCHEMA [cso]
GO
Läsa in data i nya tabeller
Om du vill läsa in data från Azure Blob Storage till informationslagertabellen använder du instruktionen CREATE TABLE AS SELECT (Transact-SQL). När du läser in med CTAS används de starkt inskrivna externa tabeller som du har skapat. Om du vill läsa in data i nya tabeller använder du en CTAS-instruktion per tabell.
CTAS skapar en ny tabell och fyller den med resultatet av en select-instruktion. CTAS definierar att den nya tabellen ska ha samma kolumner och datatyper som resultatet av select-instruktionen. Om du markerar alla kolumner från en extern tabell blir den nya tabellen en replik av kolumnerna och datatyperna i den externa tabellen.
I det här exemplet skapar vi både dimensionen och faktatabellen som hash-distribuerade tabeller.
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] ');
Spåra belastningsstatusen
Du kan spåra belastningens förlopp med dynamiska hanteringsvyer (DMV:er).
-- 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;
Optimera kolumnlagringskomprimering
Som standard lagrar dedikerade SQL-pooler tabellen som ett grupperat columnstore-index. När en inläsning har slutförts kanske vissa datarader inte komprimeras till kolumnarkivet. Det finns olika orsaker till varför detta kan hända. Mer information finns i Hantera columnstore-index.
Om du vill optimera frågeprestanda och kolumnlagringskomprimering efter en inläsning återskapar du tabellen för att tvinga kolumnlagringsindexet att komprimera alla rader.
SELECT GETDATE();
GO
ALTER INDEX ALL ON [cso].[DimProduct] REBUILD;
ALTER INDEX ALL ON [cso].[FactOnlineSales] REBUILD;
Mer information om hur du underhåller columnstore-index finns i artikeln hantera columnstore-index .
Optimera statistik
Det är bäst att skapa statistik med en kolumn direkt efter en inläsning. Om du vet att vissa kolumner inte kommer att finnas i frågepredikat kan du hoppa över att skapa statistik för dessa kolumner. Om du skapar statistik med en kolumn för varje kolumn kan det ta lång tid att återskapa all statistik.
Om du väljer att skapa statistik med en kolumn för varje kolumn i varje tabell kan du använda kodexemplet prc_sqldw_create_stats för lagrad procedur i statistikartikeln .
Följande exempel är en bra utgångspunkt för att skapa statistik. Den skapar statistik med en kolumn för varje kolumn i dimensionstabellen och på varje sammanfogningskolumn i faktatabellerna. Du kan alltid lägga till statistik med en eller flera kolumner till andra faktatabellkolumner senare.
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]);
Achievement olåst!
Du har läst in offentliga data i informationslagret. Bra jobbat!
Nu kan du börja fråga tabellerna för att utforska dina data. Kör följande fråga för att ta reda på total försäljning per varumärke:
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]
Nästa steg
Om du vill läsa in den fullständiga datauppsättningen kör du exemplet för att läsa in det fullständiga Contoso-informationslagret från Microsoft SQL Server exempellagringsplatsen. Fler utvecklingstips finns i Designbeslut och kodningstekniker för informationslager.
Feedback
Kommer snart: Under hela 2024 kommer vi att fasa ut GitHub-problem som feedbackmekanism för innehåll och ersätta det med ett nytt feedbacksystem. Mer information finns i: https://aka.ms/ContentUserFeedback.
Skicka och visa feedback för