Návrh tabulek pomocí vyhrazeného fondu SQL ve službě Azure Synapse Analytics
Tento článek obsahuje klíčové úvodní koncepty návrhu tabulek ve vyhrazeném fondu SQL.
Určení kategorie tabulky
Hvězdicové schéma uspořádá data do tabulek faktů a dimenzí. Některé tabulky se používají pro integraci nebo přípravná data před přesunem do tabulky faktů nebo dimenzí. Při návrhu tabulky se rozhodněte, jestli data tabulky patří do faktu, dimenze nebo integrační tabulky. Toto rozhodnutí informuje příslušnou strukturu a distribuci tabulek.
Tabulky faktů obsahují kvantitativní data, která se běžně generují v transakčním systému, a pak se načtou do vyhrazeného fondu SQL. Například maloobchodní firma generuje prodejní transakce každý den a pak načte data do vyhrazené tabulky faktů fondu SQL pro účely analýzy.
Tabulky dimenzí obsahují data atributů, která se můžou změnit, ale obvykle se mění jen zřídka. Například jméno a adresa zákazníka se ukládají v tabulce dimenzí a aktualizují se jenom v případě, že se profil zákazníka změní. Aby se minimalizovala velikost velké tabulky faktů, nemusí být jméno a adresa zákazníka na každém řádku tabulky faktů. Místo toho může tabulka faktů a tabulka dimenzí sdílet ID zákazníka. Dotaz může spojit dvě tabulky a přidružit profil a transakce zákazníka.
Integrační tabulky poskytují místo pro integraci nebo přípravu dat. Integrační tabulku můžete vytvořit jako běžnou tabulku, externí tabulku nebo dočasnou tabulku. Můžete například načíst data do pracovní tabulky, provést transformace dat v přípravné fázi a pak je vložit do produkční tabulky.
Názvy schémat a tabulek
Schémata jsou dobrým způsobem, jak seskupit tabulky, které se používají podobným způsobem. Pokud migrujete více databází z místního řešení do vyhrazeného fondu SQL, je nejlepší migrovat všechny tabulky faktů, dimenzí a integrací do jednoho schématu ve vyhrazeném fondu SQL.
Můžete například uložit všechny tabulky v ukázkovém vyhrazeném fondu SQL WideWorldImportersDW v jednom schématu nazvaném wwi. Následující kód vytvoří uživatelem definované schéma s názvem wwi.
CREATE SCHEMA wwi;
Pokud chcete zobrazit uspořádání tabulek ve vyhrazeném fondu SQL, můžete jako předpony názvů tabulek použít fakt, neaktivní a int. Následující tabulka uvádí některé názvy schémat a tabulek pro WideWorldImportersDW.
| Tabulka WideWorldImportersDW | Typ tabulky | Vyhrazený fond SQL |
|---|---|---|
| City | Dimenze | Wwi. DimCity |
| Objednávka | Fakt | Wwi. FactOrder |
Trvalost tabulky
Tabulky ukládají data buď trvale ve službě Azure Storage, dočasně ve službě Azure Storage, nebo v úložišti dat externích pro vyhrazený fond SQL.
Běžná tabulka
Běžná tabulka ukládá data ve službě Azure Storage jako součást vyhrazeného fondu SQL. Tabulka a data se uchovávají bez ohledu na to, jestli je relace otevřená. Následující příklad vytvoří běžnou tabulku se dvěma sloupci.
CREATE TABLE MyTable (col1 int, col2 int );
Dočasná tabulka
Dočasná tabulka existuje pouze po dobu trvání relace. Dočasnou tabulku můžete použít k tomu, aby ostatní uživatelé viděli dočasné výsledky a zároveň snížili potřebu vyčištění.
Dočasné tabulky využívají místní úložiště k rychlému výkonu. Další informace naleznete v tématu Dočasné tabulky.
Externí tabulka
Externí tabulka odkazuje na data umístěná v objektu blob služby Azure Storage nebo v Azure Data Lake Store. Pokud se používá s příkazem CREATE TABLE AS SELECT, výběr z externí tabulky importuje data do vyhrazeného fondu SQL.
Externí tabulky jsou proto užitečné pro načítání dat. Kurz načítání najdete v tématu Použití PolyBase k načtení dat z úložiště objektů blob v Azure.
Datové typy
Vyhrazený fond SQL podporuje nejčastěji používané datové typy. Seznam podporovaných datových typů najdete v odkazu CREATE TABLE v příkazu CREATE TABLE. Pokyny k používání datových typů najdete v tématu Datové typy.
Distribuované tabulky
Základní funkcí vyhrazeného fondu SQL je způsob, jakým může ukládat a pracovat s tabulkami napříč distribucemi. Vyhrazený fond SQL podporuje tři metody distribuce dat: kruhové dotazování (výchozí), hodnota hash a replikace.
Distribuované zatřiďovací tabulky (distribuce hodnot hash)
Distribuovaná tabulka hash distribuuje řádky na základě hodnoty v distribučním sloupci. Distribuovaná tabulka hash je navržená tak, aby dosáhla vysokého výkonu dotazů ve velkých tabulkách. Při výběru distribučního sloupce je potřeba zvážit několik faktorů.
Další informace najdete v pokynech k návrhu distribuovaných tabulek.
Replikované tabulky
Replikovaná tabulka má úplnou kopii tabulky dostupnou na všech výpočetních uzlech. Dotazy běží rychle u replikovaných tabulek, protože spojení v replikovaných tabulkách nevyžadují přesun dat. Replikace ale vyžaduje dodatečné úložiště a není praktické pro velké tabulky.
Další informace najdete v pokynech k návrhu replikovaných tabulek.
Tabulky kruhového dotazování
Tabulka kruhového dotazování distribuuje řádky tabulky rovnoměrně napříč všemi rozděleními. Řádky se distribuují náhodně. Načítání dat do tabulky s kruhovým dotazem je rychlé. Mějte na paměti, že dotazy mohou vyžadovat větší přesun dat než jiné metody distribuce.
Další informace najdete v pokynech k návrhu distribuovaných tabulek.
Běžné distribuční metody pro tabulky
Kategorie tabulky často určuje, kterou možnost zvolit pro distribuci tabulky.
| Kategorie tabulky | Doporučená možnost distribuce |
|---|---|
| Fakt | Použijte distribuci hash s clusterovaným indexem columnstore. Výkon se zlepší, když jsou dvě hashovací tabulky spojené ve stejném distribučním sloupci. |
| Dimenze | Použijte replikaci pro menší tabulky. Pokud jsou tabulky pro ukládání na každý výpočetní uzel příliš velké, použijte hodnotu hash distribuovanou pomocí hodnoty hash. |
| Příprava | Pro přípravnou tabulku použijte kruhové dotazování. Zatížení pomocí CTAS je rychlé. Jakmile jsou data v pracovní tabulce, použijte insert... VÝBĚREM přesunete data do produkčních tabulek. |
Poznámka:
Doporučení pro nejlepší strategii distribuce tabulek, která se má použít na základě vašich úloh, najdete v poradci pro distribuci Sql pro Azure Synapse SQL.
Oddíly tabulky
Dělená tabulka ukládá a provádí operace s řádky tabulky podle rozsahů dat. Tabulka může být například rozdělená podle dne, měsíce nebo roku. Výkon dotazů můžete zlepšit odstraněním oddílů, což omezuje prohledávání dotazů na data v rámci oddílu. Data můžete také udržovat prostřednictvím přepínání oddílů. Vzhledem k tomu, že data ve fondu SQL jsou už distribuovaná, může příliš mnoho oddílů zpomalit výkon dotazů. Další informace najdete v doprovodných materiálech k dělení. Při přepínání oddílů do oddílů tabulky, které nejsou prázdné, zvažte použití možnosti TRUNCATE_TARGET v příkazu ALTER TABLE , pokud mají být existující data zkrácena. Následující kód přepne v transformovaných denních datech na SalesFact přepsání všech existujících dat.
ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);
Indexy Columnstore
Ve výchozím nastavení vyhrazený fond SQL ukládá tabulku jako clusterovaný index columnstore. Tato forma úložiště dat dosahuje vysoké komprese dat a výkonu dotazů u velkých tabulek.
Clusterovaný index columnstore je obvykle nejlepší volbou, ale v některých případech je clusterovaný index nebo halda odpovídající strukturou úložiště.
Tip
Tabulka haldy může být zvlášť užitečná při načítání přechodných dat, například pracovní tabulky, která se transformuje na konečnou tabulku.
Seznam funkcí columnstore najdete v tématu Co je nového pro indexy columnstore. Pokud chcete zlepšit výkon indexu columnstore, přečtěte si téma Maximalizace kvality skupiny řádků pro indexy columnstore.
Statistika
Optimalizátor dotazů používá statistiky na úrovni sloupců při vytváření plánu pro provádění dotazu.
Pokud chcete zvýšit výkon dotazů, je důležité mít statistiky o jednotlivých sloupcích, zejména sloupcích používaných ve spojeních dotazů. Vytváření statistik probíhá automaticky.
Aktualizace statistik se neprovádí automaticky. Aktualizujte statistiky po přidání nebo změně významného počtu řádků. Například po načtení aktualizujte statistiky. Další informace naleznete v tématu Statistiky doprovodné materiály.
Primární klíč a jedinečný klíč
PRIMÁRNÍ KLÍČ se podporuje jenom v případech, kdy se používají neclusterované a NEVYNUCOVANÉ. Omezení UNIQUE je podporováno pouze u funkce NOT VYNUCENO. Zkontrolujte omezení vyhrazené tabulky fondu SQL.
Příkazy pro vytváření tabulek
Tabulku můžete vytvořit jako novou prázdnou tabulku. Můžete také vytvořit a naplnit tabulku výsledky příkazu select. Následují příkazy T-SQL pro vytvoření tabulky.
| Příkaz T-SQL | Popis |
|---|---|
| CREATE TABLE | Vytvoří prázdnou tabulku definováním všech sloupců a možností tabulky. |
| VYTVOŘENÍ EXTERNÍ TABULKY | Vytvoří externí tabulku. Definice tabulky je uložena ve vyhrazeném fondu SQL. Data tabulky jsou uložená ve službě Azure Blob Storage nebo Azure Data Lake Store. |
| CREATE TABLE AS SELECT | Naplní novou tabulku výsledky příkazu select. Sloupce tabulky a datové typy jsou založené na výsledcích příkazu select. Pokud chcete importovat data, můžete tento příkaz vybrat z externí tabulky. |
| CREATE EXTERNAL TABLE AS SELECT | Vytvoří novou externí tabulku exportem výsledků příkazu select do externího umístění. Umístění je azure Blob Storage nebo Azure Data Lake Store. |
Zarovnání zdrojových dat s vyhrazeným fondem SQL
Vyhrazené tabulky fondu SQL se naplní načtením dat z jiného zdroje dat. Aby bylo možné provést úspěšné načtení, musí počet a datové typy sloupců ve zdrojových datech odpovídat definici tabulky ve vyhrazeném fondu SQL. Zarovnávání dat může být nejsnadnější součástí návrhu tabulek.
Pokud data pocházejí z více úložišť dat, načtete je do vyhrazeného fondu SQL a uložíte je do integrační tabulky. Jakmile jsou data v tabulce integrace, můžete k provádění transformačních operací použít výkon vyhrazeného fondu SQL. Jakmile jsou data připravená, můžete je vložit do produkčních tabulek.
Nepodporované funkce tabulek
Vyhrazený fond SQL podporuje mnoho funkcí tabulek nabízených jinými databázemi, ale ne všechny. Následující seznam obsahuje některé z funkcí tabulky, které nejsou ve vyhrazeném fondu SQL podporované:
- Cizí klíč, kontrola omezení tabulky
- Vypočítané sloupce
- Indexovaná zobrazení
- Sekvence
- Řídké sloupce
- Náhradní klíče. Implementujte s využitím identity.
- Synonyma
- Triggery
- Jedinečné indexy
- Uživatelem definované typy
Dotazy na velikost tabulky
Poznámka:
Pro přesné počty dotazů v této části se ujistěte, že údržba indexů probíhá pravidelně a po změně velkých dat.
Jedním z jednoduchých způsobů, jak identifikovat mezeru a řádky spotřebované tabulkou v každé z 60 distribucí, je použití DBCC PDW_SHOWSPACEUSED.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
Použití příkazů DBCC ale může být docela omezující. Zobrazení dynamické správy (DMV) zobrazují více podrobností než příkazy DBCC. Začněte vytvořením tohoto zobrazení:
CREATE VIEW dbo.vTableSizes
AS
WITH base
AS
(
SELECT
GETDATE() AS [execution_time]
, DB_NAME() AS [database_name]
, s.name AS [schema_name]
, t.name AS [table_name]
, QUOTENAME(s.name)+'.'+QUOTENAME(t.name) AS [two_part_name]
, nt.[name] AS [node_table_name]
, ROW_NUMBER() OVER(PARTITION BY nt.[name] ORDER BY (SELECT NULL)) AS [node_table_name_seq]
, tp.[distribution_policy_desc] AS [distribution_policy_name]
, c.[name] AS [distribution_column]
, nt.[distribution_id] AS [distribution_id]
, i.[type] AS [index_type]
, i.[type_desc] AS [index_type_desc]
, nt.[pdw_node_id] AS [pdw_node_id]
, pn.[type] AS [pdw_node_type]
, pn.[name] AS [pdw_node_name]
, di.name AS [dist_name]
, di.position AS [dist_position]
, nps.[partition_number] AS [partition_nmbr]
, nps.[reserved_page_count] AS [reserved_space_page_count]
, nps.[reserved_page_count] - nps.[used_page_count] AS [unused_space_page_count]
, nps.[in_row_data_page_count]
+ nps.[row_overflow_used_page_count]
+ nps.[lob_used_page_count] AS [data_space_page_count]
, nps.[reserved_page_count]
- (nps.[reserved_page_count] - nps.[used_page_count])
- ([in_row_data_page_count]
+ [row_overflow_used_page_count]+[lob_used_page_count]) AS [index_space_page_count]
, nps.[row_count] AS [row_count]
from
sys.schemas s
INNER JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.indexes i
ON t.[object_id] = i.[object_id]
AND i.[index_id] <= 1
INNER JOIN sys.pdw_table_distribution_properties tp
ON t.[object_id] = tp.[object_id]
INNER JOIN sys.pdw_table_mappings tm
ON t.[object_id] = tm.[object_id]
INNER JOIN sys.pdw_nodes_tables nt
ON tm.[physical_name] = nt.[name]
INNER JOIN sys.dm_pdw_nodes pn
ON nt.[pdw_node_id] = pn.[pdw_node_id]
INNER JOIN sys.pdw_distributions di
ON nt.[distribution_id] = di.[distribution_id]
INNER JOIN sys.dm_pdw_nodes_db_partition_stats nps
ON nt.[object_id] = nps.[object_id]
AND nt.[pdw_node_id] = nps.[pdw_node_id]
AND nt.[distribution_id] = nps.[distribution_id]
AND i.[index_id] = nps.[index_id]
LEFT OUTER JOIN (select * from sys.pdw_column_distribution_properties where distribution_ordinal = 1) cdp
ON t.[object_id] = cdp.[object_id]
LEFT OUTER JOIN sys.columns c
ON cdp.[object_id] = c.[object_id]
AND cdp.[column_id] = c.[column_id]
WHERE pn.[type] = 'COMPUTE'
)
, size
AS
(
SELECT
[execution_time]
, [database_name]
, [schema_name]
, [table_name]
, [two_part_name]
, [node_table_name]
, [node_table_name_seq]
, [distribution_policy_name]
, [distribution_column]
, [distribution_id]
, [index_type]
, [index_type_desc]
, [pdw_node_id]
, [pdw_node_type]
, [pdw_node_name]
, [dist_name]
, [dist_position]
, [partition_nmbr]
, [reserved_space_page_count]
, [unused_space_page_count]
, [data_space_page_count]
, [index_space_page_count]
, [row_count]
, ([reserved_space_page_count] * 8.0) AS [reserved_space_KB]
, ([reserved_space_page_count] * 8.0)/1000 AS [reserved_space_MB]
, ([reserved_space_page_count] * 8.0)/1000000 AS [reserved_space_GB]
, ([reserved_space_page_count] * 8.0)/1000000000 AS [reserved_space_TB]
, ([unused_space_page_count] * 8.0) AS [unused_space_KB]
, ([unused_space_page_count] * 8.0)/1000 AS [unused_space_MB]
, ([unused_space_page_count] * 8.0)/1000000 AS [unused_space_GB]
, ([unused_space_page_count] * 8.0)/1000000000 AS [unused_space_TB]
, ([data_space_page_count] * 8.0) AS [data_space_KB]
, ([data_space_page_count] * 8.0)/1000 AS [data_space_MB]
, ([data_space_page_count] * 8.0)/1000000 AS [data_space_GB]
, ([data_space_page_count] * 8.0)/1000000000 AS [data_space_TB]
, ([index_space_page_count] * 8.0) AS [index_space_KB]
, ([index_space_page_count] * 8.0)/1000 AS [index_space_MB]
, ([index_space_page_count] * 8.0)/1000000 AS [index_space_GB]
, ([index_space_page_count] * 8.0)/1000000000 AS [index_space_TB]
FROM base
)
SELECT *
FROM size
;
Souhrn prostoru tabulky
Tento dotaz vrátí řádky a mezeru podle tabulky. Umožňuje zobrazit, které tabulky jsou největšími tabulkami a jestli se jedná o kruhové dotazování, replikaci nebo distribuci hodnot hash. U tabulek distribuovaných pomocí hodnot hash se v dotazu zobrazí distribuční sloupec.
SELECT
database_name
, schema_name
, table_name
, distribution_policy_name
, distribution_column
, index_type_desc
, COUNT(distinct partition_nmbr) as nbr_partitions
, SUM(row_count) as table_row_count
, SUM(reserved_space_GB) as table_reserved_space_GB
, SUM(data_space_GB) as table_data_space_GB
, SUM(index_space_GB) as table_index_space_GB
, SUM(unused_space_GB) as table_unused_space_GB
FROM
dbo.vTableSizes
GROUP BY
database_name
, schema_name
, table_name
, distribution_policy_name
, distribution_column
, index_type_desc
ORDER BY
table_reserved_space_GB desc
;
Prostor tabulky podle typu distribuce
SELECT
distribution_policy_name
, SUM(row_count) as table_type_row_count
, SUM(reserved_space_GB) as table_type_reserved_space_GB
, SUM(data_space_GB) as table_type_data_space_GB
, SUM(index_space_GB) as table_type_index_space_GB
, SUM(unused_space_GB) as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY distribution_policy_name
;
Prostor tabulky podle typu indexu
SELECT
index_type_desc
, SUM(row_count) as table_type_row_count
, SUM(reserved_space_GB) as table_type_reserved_space_GB
, SUM(data_space_GB) as table_type_data_space_GB
, SUM(index_space_GB) as table_type_index_space_GB
, SUM(unused_space_GB) as table_type_unused_space_GB
FROM dbo.vTableSizes
GROUP BY index_type_desc
;
Souhrn distribučního prostoru
SELECT
distribution_id
, SUM(row_count) as total_node_distribution_row_count
, SUM(reserved_space_MB) as total_node_distribution_reserved_space_MB
, SUM(data_space_MB) as total_node_distribution_data_space_MB
, SUM(index_space_MB) as total_node_distribution_index_space_MB
, SUM(unused_space_MB) as total_node_distribution_unused_space_MB
FROM dbo.vTableSizes
GROUP BY distribution_id
ORDER BY distribution_id
;
Související obsah
Po vytvoření tabulek pro vyhrazený fond SQL je dalším krokem načtení dat do tabulky. Kurz načítání najdete v tématu Načítání dat do vyhrazeného fondu SQL a kontrola strategií načítání dat pro vyhrazený fond SQL ve službě Azure Synapse Analytics.