Návrh tabulek s využitím vyhrazeného fondu SQL v Azure Synapse Analytics
Tento článek obsahuje klíčové úvodní koncepty návrhu tabulek ve vyhrazeném fondu SQL.
Určení kategorie tabulky
Schéma star uspořádá data do tabulek faktů a dimenzí. Některé tabulky se používají pro integraci nebo přípravu dat předtím, než se přesunou do tabulky faktů nebo dimenzí. Při návrhu tabulky se rozhodněte, jestli data tabulky patří do tabulky faktů, dimenzí 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čítají do vyhrazeného fondu SQL. Například maloobchodní firma každý den generuje prodejní transakce a pak načte data do vyhrazené tabulky faktů fondu SQL pro analýzu.
Tabulky dimenzí obsahují data atributů, která se můžou měnit, ale obvykle se mění jen zřídka. Například jméno a adresa zákazníka jsou uloženy v tabulce dimenzí a aktualizovány pouze v případě, že se změní profil zákazníka. Pokud chcete minimalizovat velikost velké tabulky faktů, nemusí být jméno a adresa zákazníka v 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 tyto dvě tabulky spojit a přidružit profil a transakce zákazníka.
Tabulky integrace 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ádět transformace dat v přípravné fázi a pak je vložit do produkční tabulky.
Názvy schémat a tabulek
Schémata představují dobrý způsob, jak seskupit tabulky, které se používají podobným způsobem, dohromady. Pokud migrujete více databází z místního řešení do vyhrazeného fondu SQL, je nejvhodnější 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 do jednoho schématu s názvem 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 fact, dim a int. V následující tabulce jsou uvedeny některé názvy schématu a tabulek pro WideWorldImportersDW.
| Tabulka WideWorldImportersDW | Typ tabulky | Vyhrazený fond SQL |
|---|---|---|
| City (Město) | Dimenze | Wwi. DimCity |
| Objednávka | Fact | Wwi. Pořadí faktů |
Trvalost tabulek
Tabulky ukládají data trvale ve službě Azure Storage, dočasně ve službě Azure Storage nebo v úložišti dat mimo 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 zůstanou zachovány 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, abyste ostatním uživatelům zabránili v zobrazení dočasných výsledků a také snížili potřebu čištění.
Dočasné tabulky využívají místní úložiště, které nabízí rychlý výkon. Další informace najdete 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. Při použití s příkazem CREATE TABLE AS SELECT naimportuje výběr z externí tabulky 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.
Typy dat
Vyhrazený fond SQL podporuje nejčastěji používané datové typy. Seznam podporovaných datových typů najdete v tématu o datových typech 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í), hash a replikované.
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 dosahovala vysokého výkonu u dotazů ve velkých tabulkách. Při výběru distribučního sloupce je potřeba vzít v úvahu několik faktorů.
Další informace najdete v tématu Pokyny k návrhu distribuovaných tabulek.
Replikované tabulky
Replikovaná tabulka má úplnou kopii tabulky dostupnou na každém výpočetním uzlu. Dotazy běží na replikovaných tabulkách rychle, protože spojení replikovaných tabulek nevyžadují přesun dat. Replikace ale vyžaduje další úložiště a není praktická pro velké tabulky.
Další informace najdete v tématu Pokyny k návrhu replikovaných tabulek.
Tabulky kruhového dotazování
Tabulka kruhového dotazování rovnoměrně distribuuje řádky tabulky napříč všemi rozděleními. Řádky jsou rozděleny náhodně. Načítání dat do tabulky kruhového dotazování je rychlé. Mějte na paměti, že dotazy můžou vyžadovat větší přesun dat než ostatní metody distribuce.
Další informace najdete v tématu Pokyny k návrhu distribuovaných tabulek.
Běžné metody distribuce pro tabulky
Kategorie tabulky často určuje, kterou možnost pro distribuci tabulky zvolit.
| Kategorie tabulky | Doporučená možnost distribuce |
|---|---|
| Fact | Použijte distribuci hodnot hash s clusterovaným indexem columnstore. Výkon se zvýší, když jsou dvě tabulky hash spojeny ve stejném distribučním sloupci. |
| Dimenze | Používejte replikované pro menší tabulky. Pokud jsou tabulky příliš velké na to, aby je bylo možné ukládat do jednotlivých výpočetních uzlů, použijte distribuovanou hodnotu hash. |
| Příprava | Pro pracovní tabulku použijte kruhové dotazování. Zatížení pomocí CTAS je rychlé. Jakmile jsou data v pracovní tabulce, použijte příkaz INSERT... VYBERTE a přesuňte data do produkčních tabulek. |
Poznámka
Doporučení k nejlepší strategii distribuce tabulek na základě vašich úloh najdete v tématu Azure Synapse SQL Distribution Advisor.
Oddíly tabulky
Dělená tabulka ukládá a provádí operace s řádky tabulky podle oblastí 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 pokynech k dělení. Při přepínání oddílů na oddíly tabulky, které nejsou prázdné, zvažte použití možnosti TRUNCATE_TARGET v příkazu ALTER TABLE , pokud chcete stávající data zkrátit. Následující kód přepne v transformovaných denních datech na SalesFact, který přepíše všechna existující data.
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 vhodnou strukturou úložiště clusterovaný index nebo halda.
Tip
Tabulka haldy může být zvlášť užitečná pro 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 Novinky v indexech columnstore. Pokud chcete zvýš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 provedení dotazu.
Pokud chcete zlepšit výkon dotazů, je důležité mít statistiky o jednotlivých sloupcích, zejména o sloupcích používaných ve spojeních dotazů. Vytváření statistik probíhá automaticky.
Statistiky se neaktualizují automaticky. Aktualizujte statistiky po přidání nebo změně významného počtu řádků. Například aktualizovat statistiku po načtení. Další informace najdete v doprovodných materiálech ke statistikám.
Primární klíč a jedinečný klíč
PRIMÁRNÍ KLÍČ se podporuje pouze v případech, kdy se používají neclusterované klíče i NEVYNUCOVANÉ. Omezení UNIQUE je podporováno pouze s použitím funkce NOT Vynucováno. Projděte si omezení tabulek vyhrazeného 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íže jsou uvedené 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žená ve vyhrazeném fondu SQL. Data tabulky se ukládají ve službě Azure Blob Storage nebo Azure Data Lake Store. |
| CREATE TABLE AS SELECT | Naplní novou tabulku výsledky příkazu select. Sloupce a datové typy tabulky 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 exportováním výsledků příkazu select do externího umístění. Umístěním je buď Azure Blob Storage, nebo Azure Data Lake Store. |
Sladění zdrojových dat s vyhrazeným fondem SQL
Tabulky vyhrazeného fondu SQL se naplní načtením dat z jiného zdroje dat. Pro úspěšné načtení musí počet a datové typy sloupců ve zdrojových datech odpovídat definici tabulky ve vyhrazeném fondu SQL. Zajištění sladění dat může být při návrhu tabulek nejtěžší.
Pokud data pocházejí z více úložišť dat, načtete je do vyhrazeného fondu SQL a uložíte je do tabulky integrace. Jakmile jsou data v tabulce integrace, můžete k provádění transformačních operací využí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í tabulky, ale ne všechny, které nabízejí jiné databáze. Následující seznam obsahuje některé funkce tabulky, které nejsou ve vyhrazeném fondu SQL podporované:
- Cizí klíč, kontrola omezení tabulky
- Vypočítané sloupce
- Indexovaná zobrazení
- Sequence
- Zhuštěné sloupce
- Náhradní klíče. Implementace s identitou
- Synonyma
- Triggery
- Jedinečné indexy
- Uživatelem definované typy
Dotazy na velikost tabulky
Poznámka
Pokud chcete získat přesné počty z dotazů v této části, ujistěte se, že údržba indexů probíhá v pravidelných intervalech a po změnách velkých dat.
Jedním jednoduchým způsobem, jak identifikovat prostor a řádky spotřebované tabulkou v každé z 60 distribucí, je použít DBCC PDW_SHOWSPACEUSED.
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
Použití příkazů DBCC však může být docela omezující. Zobrazení dynamické správy (DMV) zobrazují podrobnější informace 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 mezery po tabulce. Umožňuje zobrazit, které tabulky jsou největší a jestli se jedná o kruhové dotazování, replikaci nebo distribuci hodnot hash. V případě tabulek distribuovaných za hash dotaz 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
;
Další kroky
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 v tématu Strategie načítání dat pro vyhrazený fond SQL v Azure Synapse Analytics.