Sdílet prostřednictvím

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

A star schema organizes data into fact and dimension tables. Některé tabulky se používají pro integraci nebo pro staging dat před jejich přesunem do tabulek faktů nebo dimenzí. Při návrhu tabulky se rozhodněte, jestli data tabulky patří do faktu, dimenze nebo integrační tabulky. Toto rozhodnutí určuje vhodnou strukturu a distribuci tabulky.

  • 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, dim a int. Následující tabulka uvádí některé názvy schémat a tabulek pro WideWorldImportersDW.

Tabulka WideWorldImportersDW Typ tabulky Dedicated SQL pool
Město Dimenze wwi.DimCity
Objednávka Fact wwi.FactOrder

Trvalost tabulky

Tables store data either permanently in Azure Storage, temporarily in Azure Storage, or in a data store external to dedicated SQL pool.

Běžná tabulka

Běžná tabulka ukládá data ve službě Azure Storage jako součást vyhrazeného fondu SQL. The table and the data persist regardless of whether a session is open. 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í sezení. Dočasnou tabulku můžete použít k tomu, aby ostatní uživatelé neviděli dočasné výsledky, a zároveň tím 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. Pro seznam podporovaných datových typů viz datové typy v referenci 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. Dedicated SQL pool supports three methods for distributing data: round-robin (default), hash and replicated.

Distribuované tabulky podle hashování

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.

Round-robin tables

A round-robin table distributes table rows evenly across all distributions. Řádky se distribuují náhodně. Načítání dat do tabulky typu round-robin 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.

Table category Doporučená možnost distribuce
Fact Use hash-distribution with clustered columnstore index. Výkon se zlepší, když jsou dvě hashovací tabulky spojené ve stejném distribučním sloupci.
Dimenze Use replicated for smaller tables. Pokud jsou tabulky příliš velké na to, aby byly uchovávány na každém výpočetním uzlu, použijte hash distribuci.
Příprava Use round-robin for the staging table. The load with CTAS is fast. Jakmile jsou data v pracovní tabulce, použijte příkaz INSERT...SELECT k přesunutí dat 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.

Table partitions

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. You can also maintain the data through partition switching. 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 rozdělení. Při přepínání 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 odstraněna. The below code switches in the transformed daily data into the SalesFact overwriting any existing data.

ALTER TABLE SalesFact_DailyFinalLoad SWITCH PARTITION 256 TO SalesFact PARTITION 256 WITH (TRUNCATE_TARGET = ON);

Columnstore indexes

By default, dedicated SQL pool stores a table as a clustered columnstore index. Tato forma úložiště dat dosahuje vysoké komprese dat a výkonu dotazů u velkých tabulek.

The clustered columnstore index is usually the best choice, but in some cases a clustered index or a heap is the appropriate storage structure.

Tip

A heap table can be especially useful for loading transient data, such as a staging table which is transformed into a final table.

Seznam funkcí columnstore najdete v tématu Novinky u indexů columnstore. Pro zlepšení výkonu indexu columnstore si přečtěte 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ů. For example, update statistics after a load. Další informace naleznete v Průvodce statistikami.

Primární klíč a jedinečný klíč

PRIMARY KEY is only supported when NONCLUSTERED and NOT ENFORCED are both used. Omezení UNIQUE je podporováno pouze, pokud je použito NOT ENFORCED. Check Dedicated SQL pool table constraints.

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 Description
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 Populates a new table with the results of a select statement. Sloupce tabulky a datové typy jsou založené na výsledcích příkazu select. Při importu dat může 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é:

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
;

Table space summary

Tento dotaz vrátí řádky a mezeru podle tabulky. It allows you to see which tables are your largest tables and whether they are round-robin, replicated, or hash-distributed. 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. Pro prohlížení návodu k načítání se podívejte na Načítání dat do vyhrazeného fondu SQL a přezkoumání strategií načítání dat pro vyhrazený fond SQL ve službě Azure Synapse Analytics.