Návrh tabulek pomocí Synapse SQL v Azure Synapse Analytics

Tento dokument obsahuje klíčové koncepty návrhu tabulek s vyhrazeným fondem SQL a bezserverovým fondem SQL.

Bezserverový fond SQL je dotazovací služba na data v data lake. 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 datového skladu (DWU).

Následující tabulka uvádí témata, která jsou relevantní pro vyhrazený fond SQL a pro bezserverový fond SQL:

Téma	vyhrazený fond SQL	Bezserverový fond SQL
Určení kategorie tabulky	Yes	No
Názvy schémat	Yes	Yes
Názvy tabulek	Yes	No
Trvalost tabulek	Yes	No
Běžná tabulka	Yes	No
Dočasná tabulka	Yes	Yes
Externí tabulka	Yes	Yes
Datové typy	Yes	Yes
Distribuované tabulky	Yes	No
Distribuované zatřiďovací tabulky (distribuce hodnot hash)	Yes	No
Replikované tabulky	Yes	No
Tabulky kruhového dotazování	Yes	No
Běžné metody distribuce pro tabulky	Yes	No
Oddíly	Yes	Yes
Indexy columnstore	Yes	No
Statistika	Yes	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

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řípravu dat před přechodem na tabulku 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čtou do datového skladu. Maloobchodní firma například každý den generuje prodejní transakce a pak data načte do tabulky faktů datového skladu pro účely analýzy.

• 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 tak 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

Schémata představují dobrý způsob, 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, je osvědčeným postupem 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 jako předpony názvů tabulek použít fact, dim a int. Následující tabulka obsahuje 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 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 mimo 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 zůstanou zachovaná 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ů. Použití dočasných tabulek také snižuje potřebu čištění. Dočasné tabulky využívají místní úložiště a ve vyhrazených fondech SQL můžou nabízet vyšší výkon.

Bezserverový fond SQL podporuje dočasné tabulky. Jeho použití je ale omezené, protože můžete vybírat z dočasné tabulky, ale nemůžete ji spojit se soubory v úložišti.

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

Externí tabulka

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

Importujte data z externích tabulek do vyhrazených fondů SQL pomocí příkazu CREATE TABLE AS SELECT . Kurz načítání najdete v tématu Načtení dat z úložiště objektů blob v Azure pomocí PolyBase.

V případě bezserverového fondu SQL můžete pomocí cetas uložit výsledek dotazu do externí tabulky ve službě Azure Storage.

Typy dat

Vyhrazený fond SQL podporuje nejčastěji používané datové typy. Seznam podporovaných datových typů najdete v části o datových typech v odkazu CREATE TABLE v příkazu CREATE TABLE. Další informace o 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 tabulky a pracovat s tabulkami napříč distribucemi. Vyhrazený fond SQL podporuje tři metody distribuce dat:

• Kruhové dotazování (výchozí)
• Hodnoty hash
• Replikované

Distribuované zatřiďovací tabulky (distribuce hodnot hash)

Distribuovaná tabulka hodnot hash distribuuje řádky na základě hodnoty v distribučním sloupci. Distribuovaná tabulka hodnot hash je navržená tak, aby dosáhla vysokého výkonu pro dotazy na velké tabulky. 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 dodatečné ú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í distribuuje řádky tabulky rovnoměrně napříč všemi distribucemi. Řádky jsou distribuovány náhodně. Načítání dat do tabulky kruhového dotazování je rychlé. Dotazy ale 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 optimální možnost pro distribuci tabulky.

Kategorie tabulky	Doporučená možnost distribuce
Fact	Použijte distribuci hodnot hash s clusterovaným indexem columnstore. Výkon se zlepší, když jsou dvě zatřiďovací tabulky spojené ve stejném distribučním sloupci.
Dimenze	Pro menší tabulky použijte replikované. Pokud jsou tabulky pro uložení na jednotlivých výpočetních uzlech příliš velké, použijte distribuovanou hodnotu hash.
Příprava	Pro pracovní tabulku použijte kruhové dotazování. Zatížení s CTAS je rychlé. Jakmile jsou data v pracovní tabulce, použijte insert... VÝBĚREM přesunete data do produkčních tabulek.

Oddíly

Ve vyhrazených fondech SQL rozdělovaná 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ů, které omezí 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 už jsou distribuovaná, může příliš mnoho oddílů zpomalit výkon dotazů. Další informace najdete v pokynech k dělení.

Tip

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 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/složky (oddíly), které bude dotaz číst. Dělení podle cesty se podporuje pomocí funkcí filepath a fileinfo popsaných v tématu 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 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, jako je pracovní tabulka, 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ů. Synapse SQL podporuje automatické vytváření statistik.

Statistická aktualizace neprovádí automaticky. Aktualizujte statistiky po přidání nebo změně významného počtu řádků. Například aktualizovat statistiky po načtení. Další informace najdete v článku s pokyny ke statistikám .

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

U vyhrazeného fondu SQL se PRIMÁRNÍ KLÍČ podporuje jenom v případě, že se použijí neclusterované i NEVYNUCOVANÉ. Omezení UNIQUE je podporováno pouze při použití funkce NOT VYNUCENO. Další informace najdete v článku Omezení tabulky 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íž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 Storage.
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 Storage.

Sladění zdrojových dat s datovým skladem

Tabulky vyhrazeného fondu SQL se naplní načtením dat z jiného zdroje dat. Aby se dosáhlo ú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

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, můžete je přenést do datového skladu a uložit je do tabulky integrace. Jakmile jsou data v tabulce integrace, můžete využít výkon vyhrazeného fondu SQL k implementaci transformačních operací. 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íč, zkontrolujte 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

Ve vyhrazeném fondu SQL je jedním jednoduchým způsobem, jak identifikovat místo a řádky spotřebované tabulkou v každé z 60 distribucí, použití 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í podrobnější informace než příkazy DBCC. Začněte vytvořením zobrazení níže.

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 mezery po tabulce. Souhrn prostoru tabulky umožňuje zjistit, které tabulky jsou největší. Uvidíte také, jestli se jedná o kruhové dotazování, replikaci nebo distribuci hodnoty 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 datový sklad 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.