Návrh tabulek pomocí fondu Synapse SQL

Tento článek vysvětluje klíčové koncepty návrhu tabulek s vyhrazeným fondem SQL a bezserverovým fondem SQL ve službě Azure Synapse Analytics.

• Bezserverový fond SQL je dotazovací služba, která pracuje s daty ve vašem datovém jezeře. Nemá místní úložiště pro příjem dat.
• Vyhrazený fond SQL představuje kolekci analytických prostředků, které se zřizují při použití Synapse SQL. Velikost vyhrazeného fondu SQL je určena jednotkami Skladování Dat (DWU).

Následující témata jsou relevantní pro vyhrazený fond SQL a bezserverový fond SQL:

Téma	Vyhrazený fond SQL	Bezserverový fond SQL
Kategorie tabulky	Yes	No
Názvy schémat	Ano	Yes
Názvy tabulek	Yes	No
Trvalost tabulky	Yes	No
Běžná tabulka	Yes	No
Dočasná tabulka	Ano	Yes
Externí tabulka	Ano	Yes
Datové typy	Ano	Yes
Distribuované tabulky	Yes	No
Tabulky kruhového dotazování	Yes	No
Distribuované tabulky hash	Yes	No
Replikované tabulky	Yes	No
Běžné distribuční metody pro tabulky	Yes	No
Oddíly	Ano	Yes
Indexy Columnstore	Yes	No
Statistika	Ano	Yes
Primární klíč a jedinečný klíč	Yes	No
Příkazy pro vytváření tabulek	Yes	No
Zarovnání zdrojových dat s datovým skladem	Yes	No
Nepodporované funkce tabulek	Yes	No
Dotazy na velikost tabulky	Yes	No

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řechodem na tabulku 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 datového skladu. Například maloobchodní firma generuje prodejní transakce každý den a pak načte data do tabulky faktů datového skladu 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ématu

Schémata jsou dobrým způsobem, jak seskupit objekty, které se používají podobným způsobem. Následující kód vytvoří uživatelem definované schéma s názvem wwi.

CREATE SCHEMA wwi;

Názvy tabulek

Pokud migrujete více databází z místního řešení do vyhrazeného fondu SQL, osvědčeným postupem je migrovat všechny tabulky faktů, dimenzí a integrací do jednoho schématu fondu SQL. Můžete například uložit všechny tabulky v ukázkovém datovém skladu WideWorldImportersDW v jednom schématu s názvem wwi.

Pokud chcete zobrazit uspořádání tabulek ve vyhrazeném fondu SQL, můžete použít factdim, a int jako předpony názvů tabulek. 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 datový sklad.

Běžná tabulka

Běžná tabulka ukládá data ve službě Azure Storage jako součást datového skladu. Tabulka a data se zachovají 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. Použití dočasných tabulek také snižuje potřebu vyčištění. Dočasné tabulky využívají místní úložiště a ve vyhrazených fondech SQL můžou nabízet rychlejší výkon.

Bezserverový fond SQL podporuje dočasné tabulky, ale jejich použití je omezené, protože můžete vybrat z dočasné tabulky, ale nemůžete ji připojit k souborům v úložišti.

Další informace naleznete v tématu Dočasné tabulky.

Externí tabulka

Externí tabulky odkazují na data umístěná v objektu blob služby Azure Storage nebo v Azure Data Lake Storage.

Data z externích tabulek můžete importovat do vyhrazených fondů SQL pomocí příkazu CREATE TABLE AS SELECT (CTAS). Kurz načítání najdete v tématu Načtení datové sady New York Taxicab.

V případě bezserverového fondu SQL můžete pomocí příkazu CREATE EXTERNAL TABLE AS SELECT (CETAS) uložit výsledek dotazu do externí tabulky ve službě Azure Storage.

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 datovém typu. Další informace o používání datových typů najdete v tématu Datové typy tabulky ve službě Synapse SQL.

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:

• Tabulky kruhového dotazování (výchozí)
• Distribuované zatřiďovací tabulky (distribuce hodnot hash)
• Replikované tabulky

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 kruhového dotazování je rychlé, ale dotazy mohou vyžadovat větší přesun dat než jiné metody distribuce.

Další informace najdete v pokynech k návrhu distribuovaných tabulek.

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 obsahuje úplnou kopii tabulky dostupnou na každém výpočetním uzlu. 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.

Běžné distribuční metody pro tabulky

Kategorie tabulky často určuje optimální možnost pro distribuci tabulek.

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...SELECT k přesunutí dat do produkčních tabulek.

Oddíly

Ve vyhrazených fondech SQL se rozdělená tabulka ukládá a provádí operace na řádcích 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 vyhrazeném fondu SQL jsou již distribuovaná, může příliš mnoho oddílů zpomalit výkon dotazů. Další informace najdete v doprovodných materiálech k dělení.

Tip

Při přepínání oddílů na oddíly tabulky, které nejsou prázdné, zvažte použití TRUNCATE_TARGET možnosti v příkazu ALTER TABLE , pokud se stávající data mají zkrátit.

Následující kód přepne transformovaná denní data do oddílu SalesFact a přepíše všechna existující data.

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

V bezserverovém fondu SQL můžete omezit soubory nebo složky (oddíly), které váš dotaz načítá. Dělení podle cesty se podporuje pomocí filepath funkcí fileinfo popsaných v dotazování souborů úložiště. Následující příklad přečte složku s daty pro rok 2017:

SELECT
    nyc.filepath(1) AS [year],
    payment_type,
    SUM(fare_amount) AS fare_total
FROM  
    OPENROWSET(
        BULK 'https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=*/month=*/*.parquet',
        FORMAT='PARQUET'
    ) AS nyc
WHERE
    nyc.filepath(1) = 2017
GROUP BY
    nyc.filepath(1),
    payment_type
ORDER BY
    nyc.filepath(1),
    payment_type

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 v indexech columnstore. Pokud chcete zlepšit výkon indexu columnstore, přečtěte si téma Maximalizovat kvalitu 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ů. Synapse SQL podporuje automatické vytváření statistik.

Statistické aktualizace se neprovádí automaticky. Statistiky můžete aktualizovat po přidání nebo změně významného počtu řádků. Například po načtení aktualizujte statistiky. Další informace najdete v tématu Statistika ve službě Synapse SQL.

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

Pro vyhrazený fond PRIMARY KEY SQL se podporuje pouze v případě, NOT ENFORCED že NONCLUSTERED se obě používají. UNIQUE omezení se podporuje pouze v případech, kdy NOT ENFORCED se používá. Další informace najdete v tématu Primární klíč, cizí klíč a jedinečný klíč pomocí vyhrazeného fondu SQL.

Příkazy pro vytváření tabulek

Pro vyhrazený fond SQL můžete vytvořit tabulku 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 Storage.
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 Storage.

Zarovnání zdrojových dat s datovým skladem

Vyhrazené tabulky fondu SQL se naplní načtením dat z jiného zdroje dat. Aby bylo dosaženo úspěšného načtení, musí počet a datové typy sloupců ve zdrojových datech odpovídat definici tabulky v datovém skladu.

Poznámka:

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, můžete je přenést do datového skladu a uložit je do integrační tabulky. Jakmile jsou data v tabulce integrace, můžete k implementaci 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
• Počítané sloupce
• Indexovaná zobrazení
• Sekvence
• Řídké sloupce
• Náhradní klíče, implementace s využitím identity
• Synonyma
• Triggery
• Jedinečné indexy
• Uživatelem definované typy

Dotazy na velikost tabulky

Ve vyhrazeném fondu SQL je jedním z jednoduchých způsobů, jak identifikovat prostor a řádky spotřebované tabulkou v každé z 60 distribucí, použít DBCC PDW_SHOWSPACEUSED.

DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Mějte na paměti, že použití příkazů DBCC 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 následujícího 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]
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. Souhrn prostoru tabulky umožňuje zobrazit, které tabulky jsou největšími tabulkami. Můžete také zjistit, 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í tabulky pro datový sklad je dalším krokem načtení dat do tabulky.

• Kurz: Načtení dat do fondu SQL