Vytvoření tabulek datového skladu
Teď, když rozumíte základním principům architektury pro schéma relačního datového skladu, pojďme se podívat, jak vytvořit datový sklad.
Vytvoření vyhrazeného fondu SQL
Pokud chcete vytvořit relační datový sklad ve službě Azure Synapse Analytics, musíte vytvořit vyhrazený fond SQL. Nejjednodušším způsobem, jak to udělat v existujícím pracovním prostoru Azure Synapse Analytics, je použít stránku Spravovat v nástroji Azure Synapse Studio, jak je znázorněno tady:
Při zřizování vyhrazeného fondu SQL můžete zadat následující nastavení konfigurace:
- Jedinečný název vyhrazeného fondu SQL
- Úroveň výkonu fondu SQL, která může být v rozsahu od DW100c do DW30000c a která určuje náklady na hodinu pro fond, když je spuštěný.
- Bez ohledu na to, jestli chcete začít s prázdným fondem, nebo obnovit existující databázi ze zálohy.
- Kolace fondu SQL, která určuje pořadí řazení a pravidla porovnání řetězců pro databázi. (Kolaci nemůžete po vytvoření změnit).
Po vytvoření vyhrazeného fondu SQL můžete řídit jeho stav spuštění na stránce Spravovat synapse Studio. Pozastavení, pokud není nutné, aby se zabránilo zbytečným nákladům.
Když je fond spuštěný, můžete ho prozkoumat na stránce Data a vytvořit skripty SQL, které se v něm spustí.
Důležité informace o vytváření tabulek
Pokud chcete vytvořit tabulky ve vyhrazeném fondu SQL, použijte CREATE TABLE příkaz jazyka CREATE EXTERNAL TABLETransact-SQL (nebo někdy také ). Konkrétní možnosti použité v příkazu závisí na typu vytvářené tabulky, která může zahrnovat:
- Tabulky faktů
- Tabulky dimenzí
- Pracovní tabulky
Poznámka:
Datový sklad se skládá z tabulek faktů a dimenzí , jak je popsáno dříve. Pracovní tabulky se často používají jako součást procesu načítání datových skladů do ingestování dat ze zdrojových systémů.
Při navrhování modelu hvězdicového schématu pro malé nebo střední datové sady můžete použít upřednostňovanou databázi, jako je Azure SQL. U větších datových sad můžete těžit z implementace datového skladu ve službě Azure Synapse Analytics místo SQL Serveru. Při vytváření tabulek ve službě Synapse Analytics je důležité pochopit některé klíčové rozdíly.
Omezení integrity dat
Vyhrazené fondy SQL ve službě Synapse Analytics nepodporují cizí klíč a jedinečná omezení, která se nacházejí v jiných relačních databázových systémech, jako je SQL Server. To znamená, že úlohy používané k načtení dat musí udržovat jedinečnost a referenční integritu klíčů, aniž by se museli spoléhat na definice tabulek v databázi.
Tip
Další informace o omezeních ve vyhrazených fondech SQL služby Azure Synapse Analytics najdete v tématu Primární klíč, cizí klíč a jedinečný klíč pomocí vyhrazeného fondu SQL ve službě Azure Synapse Analytics.
Indexy
I když vyhrazené fondy SQL Synapse Analytics podporují clusterované indexy, jak je nalezeno na SQL Serveru, výchozí typ indexu je clusterovaný columnstore. Tento typ indexu nabízí významnou výhodu výkonu při dotazování velkých objemů dat v typickém schématu datového skladu a měl by se použít tam, kde je to možné. Některé tabulky však mohou zahrnovat datové typy, které nelze zahrnout do clusterovaného indexu columnstore (například VARBINARY(MAX)), v takovém případě je možné místo toho použít clusterovaný index.
Tip
Další informace o indexování ve vyhrazených fondech SQL služby Azure Synapse Analytics najdete v tématu Indexy vyhrazených tabulek fondu SQL ve službě Azure Synapse Analytics.
Distribuce
Vyhrazené fondy SQL Služby Azure Synapse Analytics používají architekturu MPP (Massively Parallel Processing), a ne architekturu SMP (Symmetric Multiprocessing), která se používá ve většině databázových systémů OLTP. V systému MPP se data v tabulce distribuují ke zpracování napříč fondem uzlů. Synapse Analytics podporuje následující druhy distribuce:
- Hodnota hash: Deterministická hodnota hash se vypočítá pro zadaný sloupec a použije se k přiřazení řádku k výpočetnímu uzlu.
- Kruhové dotazování: Řádky se rovnoměrně distribuují napříč všemi výpočetními uzly.
- Replikováno: Kopie tabulky je uložená na každém výpočetním uzlu.
Typ tabulky často určuje, kterou možnost zvolit pro distribuci tabulky.
| Typ tabulky | Doporučená možnost distribuce |
|---|---|
| Dimenze | Při připojování k distribuovaným tabulkám používejte replikovanou distribuci pro menší tabulky, abyste se vyhnuli náhodnému náhodnému prohazování dat. Pokud jsou tabulky příliš velké, aby se ukládaly na každý výpočetní uzel, použijte distribuci hodnot hash. |
| Fakt | Distribuce hodnot hash s clusterovaným indexem columnstore slouží k distribuci tabulek faktů mezi výpočetní uzly. |
| Příprava | Distribuce kruhového dotazování slouží k rovnoměrné distribuci dat mezi výpočetní uzly. |
Tip
Další informace o strategiích distribuce pro tabulky ve službě Azure Synapse Analytics najdete v tématu Pokyny k návrhu distribuovaných tabulek pomocí vyhrazeného fondu SQL ve službě Azure Synapse Analytics.
Vytváření tabulek dimenzí
Při vytváření tabulky dimenzí se ujistěte, že definice tabulky obsahuje náhradní a alternativní klíče a sloupce pro atributy dimenze, které chcete použít k seskupení agregací. Často je nejjednodušší použít IDENTITY sloupec k automatickému vygenerování přírůstkového náhradního klíče (jinak je potřeba při každém načtení dat vygenerovat jedinečné klíče). Následující příklad ukazuje CREATE TABLE příkaz pro hypotetickou tabulku dimenzí DimCustomer .
CREATE TABLE dbo.DimCustomer
(
CustomerKey INT IDENTITY NOT NULL,
CustomerAlternateKey NVARCHAR(15) NULL,
CustomerName NVARCHAR(80) NOT NULL,
EmailAddress NVARCHAR(50) NULL,
Phone NVARCHAR(25) NULL,
StreetAddress NVARCHAR(100),
City NVARCHAR(20),
PostalCode NVARCHAR(10),
CountryRegion NVARCHAR(20)
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
Poznámka:
V případě potřeby můžete vytvořit konkrétní schéma jako obor názvů pro tabulky. V tomto příkladu se používá výchozí schéma dbo .
Pokud máte v úmyslu použít schéma sněhové vločky, ve kterém jsou tabulky dimenzí vzájemně propojené, měli byste do definice podřízené tabulky dimenzí zahrnout klíč nadřazené dimenze. Například následující kód SQL lze použít k přesunutí podrobností o zeměpisné adrese z tabulky DimCustomer do samostatné tabulky dimenzí DimGeography :
CREATE TABLE dbo.DimGeography
(
GeographyKey INT IDENTITY NOT NULL,
GeographyAlternateKey NVARCHAR(10) NULL,
StreetAddress NVARCHAR(100),
City NVARCHAR(20),
PostalCode NVARCHAR(10),
CountryRegion NVARCHAR(20)
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
CREATE TABLE dbo.DimCustomer
(
CustomerKey INT IDENTITY NOT NULL,
CustomerAlternateKey NVARCHAR(15) NULL,
GeographyKey INT NULL,
CustomerName NVARCHAR(80) NOT NULL,
EmailAddress NVARCHAR(50) NULL,
Phone NVARCHAR(25) NULL
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
Tabulky časových dimenzí
Většina datových skladů obsahuje tabulku časových dimenzí, která umožňuje agregovat data podle několika hierarchických úrovní časového intervalu. Například následující příklad vytvoří tabulku DimDate s atributy, které se vztahují ke konkrétním kalendářním datům.
CREATE TABLE dbo.DimDate
(
DateKey INT NOT NULL,
DateAltKey DATETIME NOT NULL,
DayOfMonth INT NOT NULL,
DayOfWeek INT NOT NULL,
DayName NVARCHAR(15) NOT NULL,
MonthOfYear INT NOT NULL,
MonthName NVARCHAR(15) NOT NULL,
CalendarQuarter INT NOT NULL,
CalendarYear INT NOT NULL,
FiscalQuarter INT NOT NULL,
FiscalYear INT NOT NULL
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
);
Tip
Běžným vzorem při vytváření tabulky dimenzí pro kalendářní data je použití číselného data ve formátu DDMMYYYY NEBO RRRRMMDD jako celočíselný náhradní klíč a datum jako DATE alternativní klíč nebo DATETIME datový typ.
Vytváření tabulek faktů
Tabulky faktů obsahují klíče pro každou dimenzi, ke které spolu souvisejí, a atributy a číselné míry pro konkrétní události nebo pozorování, které chcete analyzovat.
Následující příklad kódu vytvoří hypotetickou tabulku faktů s názvem FactSales , která se vztahuje k více dimenzím prostřednictvím klíčových sloupců (datum, zákazník, produkt a obchod).
CREATE TABLE dbo.FactSales
(
OrderDateKey INT NOT NULL,
CustomerKey INT NOT NULL,
ProductKey INT NOT NULL,
StoreKey INT NOT NULL,
OrderNumber NVARCHAR(10) NOT NULL,
OrderLineItem INT NOT NULL,
OrderQuantity SMALLINT NOT NULL,
UnitPrice DECIMAL NOT NULL,
Discount DECIMAL NOT NULL,
Tax DECIMAL NOT NULL,
SalesAmount DECIMAL NOT NULL
)
WITH
(
DISTRIBUTION = HASH(OrderNumber),
CLUSTERED COLUMNSTORE INDEX
);
Vytváření pracovních tabulek
Pracovní tabulky se používají jako dočasné úložiště pro data, která se načítají do datového skladu. Typickým vzorem je strukturování tabulky, aby bylo co nejefektivnější ingestovat data z externího zdroje (často souborů v datovém jezeře) do relační databáze a pak pomocí příkazů SQL načíst data z pracovních tabulek do dimenzí a tabulek faktů.
Následující příklad kódu vytvoří pracovní tabulku pro data produktů, která se nakonec načtou do tabulky dimenzí:
CREATE TABLE dbo.StageProduct
(
ProductID NVARCHAR(10) NOT NULL,
ProductName NVARCHAR(200) NOT NULL,
ProductCategory NVARCHAR(200) NOT NULL,
Color NVARCHAR(10),
Size NVARCHAR(10),
ListPrice DECIMAL NOT NULL,
Discontinued BIT NOT NULL
)
WITH
(
DISTRIBUTION = ROUND_ROBIN,
CLUSTERED COLUMNSTORE INDEX
);
Použití externích tabulek
V některých případech, pokud jsou data, která se mají načíst, nacházejí v souborech s odpovídající strukturou, může být efektivnější vytvořit externí tabulky, které odkazují na umístění souboru. Díky tomu je možné data číst přímo ze zdrojových souborů místo toho, aby byla načtena do relačního úložiště. Následující příklad ukazuje, jak vytvořit externí tabulku, která odkazuje na soubory v datovém jezeře přidružené k pracovnímu prostoru Synapse:
-- External data source links to data lake location
CREATE EXTERNAL DATA SOURCE StagedFiles
WITH (
LOCATION = 'https://mydatalake.blob.core.windows.net/data/stagedfiles/'
);
GO
-- External format specifies file format
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
FORMAT_TYPE = PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO
-- External table references files in external data source
CREATE EXTERNAL TABLE dbo.ExternalStageProduct
(
ProductID NVARCHAR(10) NOT NULL,
ProductName NVARCHAR(200) NOT NULL,
ProductCategory NVARCHAR(200) NOT NULL,
Color NVARCHAR(10),
Size NVARCHAR(10),
ListPrice DECIMAL NOT NULL,
Discontinued BIT NOT NULL
)
WITH
(
DATA_SOURCE = StagedFiles,
LOCATION = 'products/*.parquet',
FILE_FORMAT = ParquetFormat
);
GO
Poznámka:
Další informace o používání externích tabulek najdete v tématu Použití externích tabulek se službou Synapse SQL v dokumentaci ke službě Azure Synapse Analytics.