Návrh tabulek pomocí fondu Synapse SQL

Tip

Microsoft Fabric Data Warehouse je relační sklad v podnikovém měřítku na základu datového jezera s architekturou připravenou pro budoucnost, integrovanou AI a novými funkcemi. Pokud s datovými sklady začínáte, začněte pracovat s Fabric Data Warehouse. Stávající úlohy fondu dedikované SQL můžou upgradovat na Fabric a získat tak přístup k novým funkcím napříč datovou vědou, analýzou v reálném čase a reportováním.

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

  • Serverless SQL pool 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.
  • Dedikovaný SQL pool 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í témata jsou relevantní pro vyhrazený fond SQL a bezserverový fond SQL:

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

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í určuje příslušnou strukturu a rozložení 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ý databázový fond SQL
Město Dimenze wwi.DimCity
Objednávka Fakt wwi.FactOrder

Trvalost tabulky

Tabulky ukládají data buď trvale v Azure Storage, dočasně v Azure Storage, nebo v úložišti dat mimo datový sklad.

Běžná tabulka

Běžná tabulka ukládá data v 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. Můžete použít dočasnou tabulku, abyste zabránili ostatním uživatelům vidět 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ální tabulky ukazují na data umístěná v objektu blob Azure Storage nebo 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). Pro návod k načítání dat se podívejte na Načtení datové sady New York Taxicab.

Pro bezserverový fond SQL můžete použít CREATE EXTERNAL TABLE AS SELECT (CETAS) k uložení výsledku dotazu do externí tabulky v Azure Storage.

Datové typy

Vyhrazený fond SQL podporuje nejčastěji používané datové typy. Seznam podporovaných datových typů najdete v části CREATE TABLE v referenci. 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á skupina SQL podporuje tři způsoby distribuce dat:

  • Tabulky kruhového dotazování (výchozí)
  • Distribuované tabulky hash
  • Replikované tabulky

Tabulky kruhového dotazování

Round-robin tabulka distribuuje řádky rovnoměrně mezi všechny distribuce. Řádky se distribuují náhodně. Načítání dat do tabulky round-robin je rychlé, ale dotazy mohou vyžadovat více pohybů dat než jiné metody distribuce dat.

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

Distribuované tabulky 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 příliš velké na ukládání na každém výpočetním uzlu, použijte rozložení pomocí hashování.
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.

Diskové 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é spravovat pomocí 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 doporučení pro 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 je podporováno pomocí funkcí filepath a fileinfo, které jsou popsány 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 sloupcové

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ý sloupcový index 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á 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ů. Aktualizujte statistiky například po načtení. Další informace naleznete v tématu Statistika ve službě Synapse SQL.

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

Pro vyhrazený SQL fond je PRIMARY KEY podporováno pouze tehdy, když jsou použity obě NONCLUSTERED a NOT ENFORCED. 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
Vytvořit tabulku (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á v Azure Blob Storage nebo Azure Data Lake Storage.
VYTVOŘIT TABULKU JAKO VÝBĚR Naplní novou tabulku výsledky příkazu select. Sloupce tabulky a datové typy jsou založené na výsledcích příkazu select. Pro importování dat může tento příkaz vybírat z externí tabulky.
VYTVOŘIT EXTERNÍ TABULKU JAKO VÝBĚR Vytvoří novou externí tabulku exportem výsledků příkazu select do externího umístění. Umístění je buď úložiště objektů BLOB Azure, 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.

Note

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é.

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 tabulkového prostoru

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, zda se jedná o cyklickou, replikovanou nebo hash-distribuci. 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.

  • Last updated on