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.

• Azure Synapse SQL Distribution Advisor
• Architektura vyhrazeného fondu SQL (dříve SQL DW) v Azure Synapse Analytics
• Tahák k vyhrazenému fondu SQL (dříve SQL DW) ve službě Azure Synapse Analytics